浅谈Mysql（一）——索引、隔离级别、死锁等

一、索引

• 索引分类：主键索引，普通索引，复合索引，唯一索引
• 技术名词：回表，最左匹配，索引覆盖，索引下推

二、explain

之前已有文章讲解：优化器-SQL语句分析与优化

这里我再写一下。

2.1 id

id:select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序；

2.2 select_type：

• SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION；
• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary；
• DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里；
• SUBQUERY：在SELECT或WHERE列表中包含了子查询；
• DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询,子查询基于外；
• UNCACHEABLE SUBQUREY：无法被缓存的子查询；
• UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED；
• UNION RESULT：从UNION表获取结果的SELECT；

2.3 table：

table:显示这一行的数据是关于哪张表的；

2.4 type

• system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计；
• const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量；
• eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；
• ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体；
• range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
• index:Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）；
• all:Full Table Scan，将遍历全表以找到匹配的行；
• index_merge:在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中；
• ref_or_null:对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询；
• index_subquery:利用索引来关联子查询，不再全表扫描；
• unique_subquery :该联接类型类似于index_subquery。 子查询中的唯一索引；

备注：
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range(尽量保证) > index > ALL

常用type：

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

2.5 possible_keys

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

2.6 key

key：实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

2.7 key_len

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引。

2.8 ref

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

2.9 rows

rows：rows列显示MySQL认为它执行查询时必须检查的行数。越少越好。

2.10 Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息。

• Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中
  无法利用索引完成的排序操作称为“文件排序”
• Using temporary:使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和
  分组查询 group by。
• USING index:表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
  如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来
  读取数据而非利用索引执行查找。
• Using where:表明使用了where过滤；
• using join buffer:使用了连接缓存；
• impossible where:where子句的值总是false，不能用来获取任何元组；
• select tables optimized away:在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

三、索引失效

• 全值匹配我最爱；
• 最佳左前缀法则；
• 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描；
• 存储引擎不能使用索引中范围条件右边的列；
• 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *；
• mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
• is not null 也无法使用索引,但是is null是可以使用索引的；
• like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作；
• 隐式类型转换索引失效；
• 少用or,用它来连接时会索引失效；

四、MySQL/InnoDB 事务隔离级别分享

4.1 一条语句的执行过程

redo log写入拆成了两个步骤： prepare和commit，这就是大名鼎鼎的两阶段提交。

• 深色部分代表service层流程，浅色部分代表Innodb内部流程；
• 两阶段提交就是为了保证binlog内容和redo log内容一致；；
• redo log：保证了数据持久性；

备注：

很多业务只要做到【读已提交】就行了，不用做到【可重复读】。

4.2 事务+锁+日志：

略；见PDF

4.2.1 隔离性案例

RR级别下： 我们继续看一组案例：
开始时： status=1

结果：

1. session2里面查询的结果， status=3;
2. session1里面查询的结果， status=1;

结论与你了解的事务隔离是否矛盾？

原理:

• session2为什么是3，因为RR隔离级别下，都会使更新操作，最新值生效。（更新都是在最新的版本上更新的）；
• session1最终查到的status值是1，比较好理解，一直事务进来后，数据库是什么样，后面无论怎么查询，值都不变；

上述session2和session3，两条update语句，能同时更新吗？
——不能，update语句有锁的存在。

五、一些需要注意的事项

5.1 count(*),count(1),count(主键id),count(字段),到底用谁？

• count(字段):select from 表+where 条件判断：正常没有落在缓存，是在数据页上，在innodb引擎层里，读一条拿回来判断…；
• 对于count(*)和count(主键)是一样的，select这张表，符合记录的数就取回来，有一个从innodb取值的过程；（但是count(*)毕竟特殊）；

但是mysql对count(*)进行了特殊优化：count时候不取值，只加值；少了一次数据复制，这层复制就是innodb引擎向service层复制数据的过程，即使复制了一个小的字段，也是有io上的消耗的的。 所以count(*)性能最好。

count(字段)：第一：业务语义不一样；第二：涉及到判断空还是非空的问题，效率最慢。

5.2 普通索引和主键索引到底有没有区别？

innodb引擎，唯一索引和普通索引性能上谁更好？

• 普通索引：比方说查一个要索引值为2的数据，普通索引查到了，还要往下找；但是其实性能差距不大，基本等于和唯一索引性能没有区别，因为mysql是b+树，且有序的，在一页中普通索引往下查还挺快的，除非2在页的最后一个节点还有（也就是还要查下一页的数据）；
• 唯一索引：查一个要索引值为2的数据，查到了，就不会往下找了；

查询：唯一索引性能领先，但领先幅度不大；
插入或更新：唯一索引性能性能不如普通索引， 因为唯一索引有一个判断是否唯一的过程；

结论：唯一索引和普通索引性能只有在更新上性能有区别，普通索引性能更好；查询上性能区别不大；

5.3 很多时候第二次或者后面的多次查询同一条语句，速度变快了，是什么原因？

  有时候是因为mysql有查询缓存；
  也有可能是这样的：mysql的索引是一种数据结构，一开始是在磁盘里的，访问一次，会加载到内存当中的，这样就少了一次查询b+数的过程；

一般来说，生产当中，mysql服务器的内存都很大，为什么？
——就是为了存各种各样的buffer，有查询的缓存，有更新的缓存，有binlog刷日志的缓存，等等；

一页能存多少数据结构?
——略；
MySQL的一个数据页大小 mysql一页可以存储多少数据

5.4 其他

• redo log 主要节省的是随机写磁盘的IO(顺序写)；
• change buffer主要节省随机读磁盘的IO消耗；

就像5.3提到的buffer，更新时：

• 普通索引：先更新的缓存，就是change buffer。 更新时：先写change buffer，后写日志；所以这条数据就被持久化了，没丢；
• 唯一索引：没有经过change buffer，直接查库有没有值，所以上面说的更新性能就慢在这了。

5.5 我把我这个项目对应的mysql的进程全部kill掉，会怎么样？

• 有@Trancation注释的方法会回滚吗？——会；
• 应用服务器还会重新连接吗？应用要重启吗？——连接池是能重新建立连接的，不用重启；

六、处理问题一些技巧

1. 慢sql定位：开启慢日志;
2. 大事务处理：SELECT * FROM information_schema.INNODB_TRX;
3. 降低死锁概率：控制并发度；

七、死锁

7.1 场景

场景：

1. 用户A余额支付金额给商家B：update t set money = money-100 where user =‘A’;
2. 商家B余额增加:update t set money = money+100 where user =‘B’;
3. A生成订单日志:insert …

如何设计三条语句的顺序?

——应该是3>1>2;

分析：

1. 不容易被锁，因为买东西的用户A，同时买东西的情况很少；
2. 容易被锁，商家user=B，同时卖出东西的情况，肯定是多的；

应该把最容易锁的语句放到最后执行，尽可能让锁的时间变短；

这样是降低了锁的概率，但是还有一种情况避免不了的：死锁；

7.3 死锁不可避免

事务1：

事务2：

按顺序执行1，2，3，4步骤，在执行4后，会发生什么现象？
——死锁了。

死锁报错，执行4步骤后：

问题：deadlock之后，事务会自动关闭吗？如果不会，需要手动操作吗，怎么操作？

• mysql死锁不危险，不会导致mysql挂掉，但会有性能上的影响；mysql死锁避免不了；
• jvm死锁很危险，会导致jvm挂掉；

再回到上述场景，用户A在买了商户B的东西过程中，又买了其他商户的东西，这过程也有概率发生死锁的。

死锁检测：当mysq更新一条语句的时候，只有发现这条记录已经被别人拿到锁了，才会进行死锁检测。

上述场景减少死锁的方案：

1. 系统设计过程中，给商家开多个账号，在一张表中有多条账户记录，每次都随机商家账号，增加金额；
2. 让sql执行有序，用mq，或者分布式锁，都一定程度上能降低死锁概率；

7.4 MDL锁

alert table（MDL锁-写锁），尽量让dba执行，alert极容易产生锁，而且锁的时候不知道，而别的业务线程全部中断了；
写锁是等读锁释放的时候才会写，在写的时候，后面的线程又被锁住了，极其容易造成死锁。

八、select语句执行流程：

1. 如果数据库刚启动，缓存（不是查询缓存，查询缓存互联网公司一般关了，因为只要数据更新，就得维护它，麻烦）空空如也；——这里缓存，指的是MySQL的pull buffer（可以存索引数据），缓存没有，就找索引，找到索引了，就去硬盘上找数据记录，同时该索引所在的页，都别拿到内存中了（下次再查这条语句就很快了，因为拿到内存中了）。读到之后，数据一般不在缓存当中；
2. 拿到数据之后，如遇到order by，就涉及到排序，优先放到内存中排序，内存放不下，就放到硬盘中排序；如果order by 是索引，天生有序，无序额外操作；
3. 如果有limit，找到一条，判断合适不合适，合适-放到内存里，继续下一条…
4. 如果是join操作，上面3的步骤要重复笛卡尔积次；

九、一般大厂数据库规约

略；参考文章：待写；

9.1 建立组合索引，必须把区分度高的字段放在前面

解读：能够更加有效的过滤数据。

A字段和B字段查询时都会用到，建立组合索引，和分别个A和B都建立索引，在执行的时候，有啥不一样？
答：组合索引查询效率更高，一次性就检索出来了。而后者，mysql执行前会进行分析，最终可能只走了A索引，也可能只B索引，也可能两个索引都走了，最后取交接。
反正性能就是没有组合索引高；

9.2 order by

• 如果c是索引列：天生有序；
• 如果c不是索引列

9.2.1 Using filesort分析

用到了文件排序，用文件排序不一定慢，什么时候会慢？
——查询*的时候，会从数据列中拿出来，可能放到文件，也可能是在内存（【查询缓存】）。当数据少的时候，内存处理可以排序，那性能就还好；
但就是怕这条sql取的数据过大，【查询缓存】放不下，就会落到文件上（硬盘），这时候排序就慢了。

措施：

• order by 尽量用索引列。
• 索引缓存、排序缓存放大；——生产上，从库缓存可以配大一点；

9.3 不用join，用啥

——join最多的情况用在分页上。
字典表、配置表连连影响不大；
但是，如果是两张很大的表连表，影响就很大了，比如两张100w，产生的笛卡尔积：100w*100w。

解决方法1：先条件筛选，再进行连表:

select at.* (select * from a where a.xx=xx and ...) at
 join ( select b.* from b where b.xx=xx and ...) bt
 on at.xxx=b.xxx

解决方法2：
——数据冗余；

9.4 jdk stream的并发流——parallel()，慎用

jdk stream的并发流——parallel()，慎用！会有一些cpu的性能上的问题；