MySQL基础、优化以及注意事项

一、数据库引擎

• innoDB：支持行锁和表锁
• MyISAM：只支持表锁

二、定位低效率、优化SQL语句的手段

• 慢查询日志：日志文件中查找
• show processlist：实时查看正在进行的慢SQL语句，包括线程的状态、是否锁表等
• show profiles：可以使用该命令分析SQL语句所经历的耗时
• trace：使用trace可以查看SQL优化器的信息
• explain：该命令可以查出SQL语句在执行过程。比如是否使用了索引、执行顺序、执行的条件等等。这个是分析SQL语句非常重要的手段

三、事务的隔离级别

• 查看当前会话隔离级别

  select @@tx_isolation;
  

• 查看系统当前隔离级别

  select @@global.tx_isolation;
  

MySQL默认的隔离级别是repeatable read。

• 设置当前会话隔离级别

  set session transaction isolation level repeatable read;
  

• 设置系统当前隔离级别

  set global transaction isolation level repeatable read;
  

1. 读未提交（Read Uncommitted）

读取到其它事务的数据，然后其它事务回滚了，导致前后数据不一样，数据丢失、脏读。

2. 读已提交（Read Committed）

无法对修改（update）进行限制（加锁），即在该隔离级别下可以读取到别人修改或新增或者删除后提交的数据，这种情况称之为不可重复读（在于update和delete），也会出现幻读。

3. 可重复读（Repeatable-Read）

无法对其它事务的新增进行限制，当前事务更新了其它事务插入的数据后再次读取的时候发现多了数据，称之为幻读（在于insert）。

特别注意
只要事务A对其它事务新增的那条数据进行修改操作（更新、插入、删除）时是可以感知到的，如果没有涉及到其它事务的那条数据，则不会显示其它事务新增的数据。

举例说明，事务A新增了一条数据（比如id为8，age字段为88）并提交了；事务B执行了查询操作（不管是查询所有还是范围查询），其结果不会展示id为8的数据，紧接着执行了对id为8的修改操作（不管是有意还是无意），更新age字段的值为99，然后再执行查询操作，你会发现其结果中多出了一个id为8的数据并且age字段的值被你修改为99了。

到底可重复读隔离级别下，解决了幻读问题没有？
了解过MVCC的同学，肯定知道或听说过当前读（加锁了）和快照读（未加锁某一时刻的）。（不知道的同学，可以查找相关资料了解下，当然后续我也会有文章专门介绍MVCC)。首先要知道的是MVCC 就InnoDB 秒级建立数据快照的能力。 快照读就是读取数据的时候会根据一定规则读取事务可见版本的数据。

• 而当前读就是读取最新版本的数据，什么情况下使用的是快照读（快照读，不会加锁）？

  一般的 select * from … where … 语句都是快照读

• 什么情况下使用的是当前读（当前读，会在搜索的时候加锁）？

  select * from … where … for update;
  select * from … where … lock in share mode;
  update … set … where …
  delete from. . where …

如果事务中都使用快照读，那么就不会产生幻读现象，但是快照读和当前读混用就会产生幻读。（此段落参考：https://zhuanlan.zhihu.com/p/103580034?utm_source=wechat_session）

总结：可重复读隔离级别下，一个事务中只使用当前读，或者只使用快照读都能避免幻读。

上图中如果没有第六步更新操作，则读不到ID为5的记录。

4. 串行化（Serializable）

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁（表锁），所以会导致其它连接（不管是否开启事务）进行修改操作时发生阻塞，从而会超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

总结：事实上事务隔离级别由MVCC（多版本并发控制）做的，它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并发性能的一种机制。它的实现依赖于隐式字段、undo日志、快照读&当前读、Read View。

更多详细资料看（很有必要）：https://juejin.cn/post/6844904115353436174

三、锁

3.1 行锁

• 共享锁（读锁、Shared Locks即S锁）

  -- 加锁
  begin/start transaction; 
  select * from table where id = 1 LOCK IN SHARE MODE;
  

  -- 释放锁
  commit/rollback;
  

• 排它锁（写锁、Exclusive Locks即X锁）
  加锁：begin/start transaction; delete、update、insert默认加上了排它锁，或者使用select * from table for update手动加上了。
  释放锁：commit或者rollback

3.1.1 行锁的3种

• 记录锁（Record Locks）
  也叫行锁，等值查询、精准匹配，只会作用在主键和唯一索引上

• 间隙锁（Gap Locks）
  会锁住某个区间的中的某条记录的前区间（不包含）和后区间（包含）的数据，哪怕该区间的值不存在也是如此。

1. 基于主键或唯一索引（ID为主键）
   比如现在有1、4、7、9、10、11这4条数据，现在查询条件为id > 4 and id <= 7 for update，会锁住(4, 7]和(7, 9]这两个区间的数据（即使id不存在的也会被锁住）。原因是因为查询条件用了两个值，并且是范围查询，那么根据间隙锁前开后开的原则，以及相等值决定此区间。接下来看下实验：

   开启事务，查询当前表的数据。注意左边的数据中4至44这个区间不是连续的，我们就以上述的id > 4 and id <= 7 for update进行试验，看会出现什么结果。
   
   对id为4的数据进行修改是没问题的。
   对id为5、7的数据进行修改，是无法修改的。但对id为6的数据是不会出现阻塞，因为当前数据中没有id为6的数据，如果存在也无法修改。
   
   对id为9的数据进行修改，发现也是无法修改的。这是为什么呢？原因就出在id > 4 and id <=7这个条件中，因为查询条件中也会判断id为7的间隙范围，会从前到后查找，直到截至到id为9为止。
   
   紧接着分别插入id为10、11、12等不在id > 4 and id <=7范围内的数据都是没有问题的。
   
   总结：对主键的数据进行修改，如果只是对某一个主键或唯一键修改，则只会锁住当前ID的一条数据；如果是对主键或唯一键范围修改，就会存在间隙锁，其示例中的范围是(4, 9]，但如果查询条件是id >= 4 and id <= 7的话，则范围是[4, 9]。由此可知，如果是范围修改，则是左闭右闭，其间隙锁的范围固定在查询条件内。另外，需要注意的是默认情况下主键ID（设置自增长）生成后是从小到大排序的，但是如果你人为删除或者人为插入不按顺序插入的数据，在后续加速上是有影响的。这一点要特别注意，否则出现不可思议的事情。

2. 基于普通索引修改
   指定某行数据修改：普通索引会锁住指定的索引前后范围内的数据，其范围是“左闭右开”。看下图中对id为7的数据修改，你会发现[5, 9)范围的数据无法修改也无法插入。
   
   指定某个范围（id >= 4 and id <= 7）修改：你会发现一个很有意思的事情，就连id为3的那条记录都无法修改，而id为9的那条记录却可以！由此可以得出结论其间隙锁的范围是[3, 9)，依旧是“左闭右开”，但左闭的截至位置是查询条件最小的那个的前一个值（示例中的3）。
   
   总结：普通索引与主键、唯一键不同，它一旦加锁就会产生间隙锁，即使你指定某一行数据也是如此。其间隙锁的范围是左闭右开，但左闭的截至位置是查询条件最小的那个的前一个值（示例中的id为3）。

3. 对没有索引的数据修改
   如果表没有任何索引，你这么写sql语句select * from b where id = 7 for update，那将是表锁，任何修改（更新、插入）都无法进行。因此一个没有索引的表将是灾难！
   
   总结：主键、唯一索引与普通索引的机制不一样。在普通索引列上，不管是何种修改只要加锁，都会产生间隙锁；而如果对主键或唯一索引范围修改时也会产生间隙锁。这种现象称之为Next-Key Lock，有些地方翻译成临键锁。另外，需要注意的是本示例中是以int类型作为示例，其存在一定的顺序规则的。

其它参考（其中第二篇文章，某些存在错误的描述，需大家斟酌）：
MySQL的锁机制
Innodb锁机制：Next-Key Lock 浅谈

3.2 表锁

innoDB支持行锁和表锁，MyISAM只支持表锁。如果表字段没有设置索引的话，那么在修改或者for update（排它锁）的时候使用的是表锁。

注意：意向锁是数据库引擎自己维护的，用户无法手动操作意向锁。提升加锁的效率。

• 意向共享（读）锁（IS）
  表示事务准备给数据行加入共享（读）锁，也就是说一个数据行加共享锁前必须先取得该表的意向共享锁。
• 意向排它（写）锁（IX）
  表示事务准备给数据行加入排它（读）锁，说明事务在一个数据行加排它锁前必须先取得该表的意向排它锁。

举例：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

更详细的参考：https://juejin.cn/post/6844904115353436174#heading-22

四、索引

索引的本质是空间换时间 ，通过索引这个缓存来提高数据查询的效率。 在MySQL 中， 每一个索引在InnoDB里面对应一棵B+树， 即MySQL的InnoDB 索引数据结构是 B+树，主键索引叶子节点存储的就是MySQL的整个数据行，普通索引的叶子节点存储的是索引列和主键值。

4.1 聚簇索引和非聚簇索引

简单的来说，找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引；找到了索引但没找到数据，需要根据索引上的值（主键）再次回表查询，这种索引就是非聚簇索引。聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

总结：

• 主键
  存储一条完整的数据。使用主键就能查到一条完整的数据。
• 普通索引
  只存储主键。通过普通索引查询主键叫覆盖索引；通过普通索引查询主键再查下其它数据叫回表查询。

此章节参考了：http://blog.itpub.net/26736162/viewspace-2692249/

例如，下面 的SQL 创建了一个学生表 ：

create table lhrdb.student (
    id bigint,
    no varchar(20) ,
    name varchar(20) ,
    address varchar(20) ,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_no` (`no`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 直接根据主键查询，能获取所有字段数据，此时主键是聚簇索引，因为主键对应的索引叶子节点存储了id=1 的所有字段的值：

   select * from lhrdb.student where id = 1;
   

2. 根据编号no字段（编号本身是一个唯一索引）查询编号和名称，但查询的列包含了学生编号和学生名称，该索引的节点的数据存储的是主键ID，需要根据主键ID重新查询一次，所以这种查询下no索引不是聚簇索引：

   select no,name from student where no = 'test';
   

3. 根据编号查询编号，这种查询命中编号索引时，直接返回编号，因为所需要的数据就是该索引，不需要回表查询，这种场景下no索引是聚簇索引：

   select no from student where no = 'test';
   

4.2 索引优化

以下通过explain命令（执行计划）分析得出：

id：选择标识符
select_type：表示查询的类型。
table：输出结果集的表
partitions：匹配的分区
type：表示表的连接类型（性能优化的最重要标准）
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较
rows：扫描出的行数（估算的行数，值越小越好）
filtered：按表条件过滤的行百分比（值越大性能越好）
Extra：执行情况的描述和说明

总结
explain（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息，对日常生产过程中调优十分有用：

• id
  select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。
• type
  主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。
• key
  实际被使用的索引列。
• ref
  关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。
• Extra
  额外信息，最好的情况是该值是空的。使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。

1. 如果字符串没有使用引号（单引号或者双引号），那么设置的索引会失效。原因是因为SQL内部做了隐式转换导致索引失效。
2. 索引字段使用范围查询，会导致返回查询后的索引字段失效，因此要禁止这种情况，调整索引字段的查询顺序
3. 索引字段使用运算、函数等，也会导致索引失效，因此要禁止这种用法。
4. 使用or后，or后面的查询条件会导致索引失效，哪怕使用了索引。
5. 查询字段尽量使用设置了索引的字段，否则需要进行回表查询（Using index condition），这种称之为覆盖索引。严禁使用select * from table的用法。
6. 使用like模糊查询时，如果是以%开头，则会导致索引失效。但如果%在后面的话，则不会影响。那么如果必须要以%开头怎么办，答案是可以在查询条件中使用索引字段就可以（必须只包含索引字段）。
7. 复合索引，必须满足最左原则。比如有一个复合索引使用name、status、address这三个字段（注意顺序），如果查询条件中没有使用name这个字段，那么就没有使用索引。如果使用了name字段，但没有使用status字段，则只是用了name字段的索引。这个就好比上楼梯一样，必须先先从1楼开始，然后是2楼、3楼。只要哪一楼没有，那就就不会往上走了。
8. 当MySQL发现使用索引，比全表扫描还要慢时，则使用全表扫描。这个主要原因是因为你要查询的数据量太大，使用索引没什么意义。
9. is null和is not null有时候会使用索引，但在某些场景不会使用。这个跟第8条类似，即当查询的数据比较多的时候是不会使用索引。
10. 尽量使用in，不要使用not in，会导致索引失效。
11. 尽量使用复合索引，少使用单列索引。原因是如果是三个单列索引，在查询的时候如果都使用这三个字段，那么实际上MySQL只会选择一个最优的索引（即辨识度高、哪个数据量最少），并不会全部使用。
12. order by null：默认情况下，mysql对所有group by的字段进行排序，但有时候我们并不需要排序，通过order by null可以禁止排序，同时Extra中就没有Using filesort。

批量导入数据

• 使用load命令导入，如果使用的是innodb引擎，需要注意是主键必须是有序的，否则效率低，每个字段的值使用逗号分隔。

优化insert语句

• 即一个insert语句使用多个values，但需要注意一条语句长度不能太长，否则MySQL会抛出异常
• 当数据量比较大时，使用分段事务提交
• 主键有序排列，跟上面的批量导入数据类似

优化order by语句

在MySQL中排序有Using filesort和Using index两种情况。

• 当使用select * from不指定索引字段，使用的是Using filesort，这种效率低。但如果既包含索引字段，也包含非索引字段，还是使用的Using filesort，因此尽量全部使用索引字段，有助于提高查询效率。
• 复合索引时，如果是多字段排序时，尽量使用相同排序方式（asc或者desc），否则后面的字段会按照Using filesort。
• 复合索引时，排序的字段应该按照索引定义的顺序来排序，否则后面的字段会按照Using filesort。

优化group by语句

group by默认做了order by排序

• 如果不想对分组字段进行排序，可以使用order by null，取消排序
• 如果分组效率不高，可以考虑对分组字段建立索引

优化嵌套（子）查询

• 尽量使用多表关联查询，少用in查询

优化or语句

• 强烈建议使用Union关键字进行组合查询，有效提高查询效率
• 使用or语句后，所有or语句连接的都有索引时，才会使用索引。
• 如果几个查询条件都是复合索引（比如没有主键索引），那么复合索引也是无效的。

优化limit分页

当分页随着层级越来越深时，比如limit 1000000, 10，效率则越来越低

• 可以先在索引上（比如主键、索引字段）完成排序分页，然后与当前表关联查询，这样有助于查询效率

  select a.id, a.name, a.age from user, (select id from user a where name = '张三' limit 100000, 10) b where a.id = b.id;
  

• 采用查询条件先过滤，然后再分页，这样的效率很高。但是该方式适用于主键自增，以及主键是连续的。

  select id, name, age from user where id > 1000000 limit 10;	
  

索引提示

• use index：提供希望MySQL去参考索引列表，这样MySQL不再考虑其他可用的索引了，注意只是参考。在某些时候MySQL还是不会使用索引，参考上述的索引优化的第8小节。
• force index(index_name1, index_name2)：强制使用索引，这就跟use index比较就不一样了。
• ignore index：忽略一个或多个索引。