logo头像
Snippet 博客主题

Mysql要学习的不仅仅是增删改查

Mysql引擎类型

Mysql数据库引擎该如何选择?

索引有什么副作用吗?

(1)索引是有大量数据的时候才建立的,没有大量数据反而会浪费时间,因为索引是使用二叉树建立.

(2)当一个系统查询比较频繁,而新建,修改等操作比较少时,可以创建索引,这样查询的速度会比以前快很多,同时也带来弊端,就是新建或修改等操作时,比没有索引或没有建立覆盖索引时的要慢。

(3)索引并不是越多越好,太多索引会占用很多的索引表空间,甚至比存储一条记录更多。
对于需要频繁新增记录的表,最好不要创建索引,没有索引的表,执行insert、append都很快,有了索引以后,会多一个维护索引的操作,一些大表可能导致insert 速度非常慢。

主键和外键

成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键

区别 主键 外键 索引
定义 唯一标识一条记录,不能有重复的,不允许为空 表的外键是另一表的主键, 外键可以有重复的, 可以是空值 该字段没有重复值,但可以有一个空值
作用 用来保证数据完整性 用来和其他表建立联系用的 是提高查询排序的速度
个数 主键只能有一个 一个表可以有多个外键 一个表可以有多个唯一索引

事务

事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。

  • 原子性Atomicity:原子性指的是事务是一个不可分割的,要么都执行要么都不执行。

  • 一致性Consistency:在一致性状态下,所有事务对同一个数据的读取结果都是相同的。

  • 隔离性Isolation:指的是一个事务的执行,不能被其他的事务所干扰。

  • 持久性Durability:持久性指的是一个事务一旦提交了之后,对数据库的改变就是永久的。

并发一致性问题

  • 读脏数据:T1 修改一个数据,T2 随后读取这个数据。如果 T1 撤销了这次修改,那么 T2 读取的数据是脏数据。
  • 不可重复读: T2 读取一个数据,T1 对该数据做了修改。如果 T2 再次读取这个数据,此时读取的结果和第一次读取的结果不同。
  • 幻影读:T1 读取某个范围的数据,T2 在这个范围内插入新的数据,T1 再次读取这个范围的数据,此时读取的结果和和第一次读取的结果不同。

事务隔离级别

  • 未提交读:事务中的修改,即使没有提交,对其它事务也是可见的。
  • 提交读:一个事务只能读取已经提交的事务所做的修改。换句话说,一个事务所做的修改在提交之前对其它事务是不可见的。
  • 可重复读:保证在同一个事务中多次读取同一数据的结果是一样的。
  • 可串行化:强制事务串行执行,这样多个事务互不干扰,不会出现并发一致性问题。该隔离级别需要加锁实现,因为要使用加锁机制保证同一时间只有一个事务执行,也就是保证事务串行执行。

聚集索引和非聚集索引的区别?

聚集索引表示表中存储的数据按照索引的顺序存储,检索效率比非聚集索引高,但对数据更新影响较大。

非聚集索引表示数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置,非聚集索引检索效率比聚集索引低,但对数据更新影响较小。

  • 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个
  • 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续
  • 聚集索引:物理存储按照索引排序;聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序
  • 非聚集索引:物理存储不按照索引排序;非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序.
  • 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。

设计三范式

  • 第一范式:强调的是列的原子性,列不能分成其他几列,第一范式就是无重复的域。
  • 第二范式:首先是在第一范式的基础上,另外包含两部分的内容,一是表必须有主键,二是没有包含在主键中的列必须完全依赖于主键,二不能只依赖于主键的一部分。
  • 第三范式:在第二范式的基础之上,非主键列必须直接依赖于主键不能存在传递依赖。

目的:消除冗余,单纯依赖关系。不允许数据库表出现冗余字段,不允许表之间多重依赖

MySql慢查询

MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句。

具体环境中,运行时间超过long_query_time值的SQL语句,则会被记录到慢查询日志中。

long_query_time的默认值为10,意思是记录运行10秒以上的语句。

默认情况下,MySQL数据库并不启动慢查询日志,需要手动来设置这个参数。

查询数据库比较慢可能是哪些原因造成的?

1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)
2、I/O吞吐量小,形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存不足
5、网络速度慢
6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)
7、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)
8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列
10、查询语句不好,没有优化

如何解决慢查询问题

要解决慢查询,就是优化这些查询缓慢的语句,或是重新组织自己的数据。
重新组织数据的表现形式是分表。对于成熟的业务系统而言,分表的代价是极高的。所以如何组织一张表仍然是建表的重要决策。
因此,优化语句才是解决慢查询的基本方法。

  • 加索引
  1. 使用LIKE关键字的查询语句
    在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置索引才会起作用。

  2. 使用多列索引的查询语句
    MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用。

  • 优化数据库结构
    合理的数据库结构不仅可以使数据库占用更小的磁盘空间,而且能够使查询速度更快。数据库结构的设计,需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
  1. 将字段很多的表分解成多个表
    对于字段比较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

  2. 增加中间表
    对于需要经常联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。

  • 分解关联查询
    将一个大的查询分解为多个小查询是很有必要的。很多高性能的应用都会对关联查询进行分解,就是可以对每一个表进行一次单表查询,然后将查询结果在应用程序中进行关联

  • Limit分页
    分页是一个常见的情景。分页通常会使用limit加上偏移量的方法实现,同时加上合适的order by 子句。如果有对应的索引,通常效率会不错,否则MySQL需要做大量的文件排序操作。
    但是,当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,MySQL需要查询10020条,但是只返回最后20条,这样的代价很高。
    优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

参考文献
常见Mysql的慢查询优化方式
MySQL中的慢查询
关于MySQL 通用查询日志和慢查询日志分析