前言

前一阵光忙着做项目了，然后先写了项目的一些技术点总结。依旧是烂大街的项目，黑马点评最牛的就是里面的秒杀业务，苍穹外卖里面就是频繁的增删改查练习。但是其实你会发现还是会有很多东西不会，所以这周我们不搞啃源码那种硬核的，我们就来看看继MVCC后我们MySQL的另一个重要的点——索引

正文

索引解决的需求

索引加快了查找速度，MySQL底层的索引底层使用了B+树+自适应Hash结合的方式，让查找和排序的速度大大加快。缺点就是这里索引需要占用空间，而且这里的增删改的效率会降低。但是我们一般的业务场景比如电商，CSDN，知乎这种都是大家查询的比较多，所以这里索引也是我们结合业务做出取舍的结果

为什么使用B+树？

首先数据结构中，谁最适合磁盘存储，那必然是树了。但是树的缺点就是一次节点就有一个数据，然后就有了B树，一次可以查询多个数据。但是B树的查询需要回退，进去，这个时候我们把B树线索化，非叶子节点不存储数据而是下一个节点的位置，叶子节点存储数据，这个时候只要找到叶子节点就可以遍历一个树的数据，这个时候B+树就突出重围了。

为什么使用自适应hash？

hash是一个极其厉害的数据结构，通过一个特定的key进行hash运算之后就对应一个value值，所以hash只需要计算就可以匹配到一个值，不需要通过这里的进入和回退操作，而MySQL的InnoDB引擎使用的自适应hash会根据这里访问量（默认17次）来进行hash索引的建设，提高热点数据的查询效率。但是hash的缺点也很明显，这里的key要是特定的，所以说这里的模糊匹配和范围查询都是不可以的，所以B+树才占据了大头

关于聚簇索引和二级索引（索引分类的一种）

聚簇索引和二级索引

• 在InnoDB引擎中，这里每一个表默认都会有一个聚簇索引，往往体现为主键索引，如果这里没有主键索引，InnoDB也会指定一个隐藏列作为主键索引
• 二级索引：二级索引就是我们自己建立的索引，和聚簇索引的关系就是，聚簇索引里面存储行数据，而二级索引存储聚簇索引的主键值，二级索引和聚簇索引分别是一棵独立的B+树

二级索引的查询流程（回表查询）

这里既然说二级索引存储的是聚簇索引的主键值，所以说这里对二级索引的查找就是查找到对应的值，然后去聚簇索引里面拿对应的行数据

索引分类

前缀索引

这里解决的业务就是比如你的电子邮件或者别的比较长的字符串，可能后面都是一样的，但是不一样的就是前面的，所以查询电子邮件就是查询前缀，我们可以建立前缀索引，可以减少占用空间，提高查找效率

单列索引

就是一个只有一列的索引

联合索引

这个索引可以有多个列，也就是一个B+树上有多个字段，对应单列索引一堆B+树

SQL性能分析工具

这里的话我感觉你知道有什么工具做什么事就可以了，因为现在agent很强大，也有无数很厉害的开发者帮你封装了一些功能

1. 查看SQL执行频率
2. 使用慢查询日志（这里就是记录执行慢的SQL，可以通过这个日志来优化SQL）
3. 使用profile（这个可以查询SQL运行的时间和时间都耗费在哪里了）
4. 使用explain（这个可以看SQL的执行计划）

SQL索引执行规则

执行规则

最左前缀法则

1. 如果这里使用联合索引，必须按照联合索引创建的顺序从左到右查询，如果说不按顺序，从不按顺序的那个点索引开始失效，比如创建联合索引是abc，但是查询的时候是acb，这里会把符合a条件的查询出来，然后对这个临时表进行全表查询查询符合bc的
2. 就是这里如果说现在按照顺序了，但是最后一个字段缺失，我们照样可以查询，和上面的查询方式一样

索引失效场景

范围查询（使用黑马的图）

如果在SQL查询条件中使用>或者<，右边的索引将失效
注意

1. 这里只有大于和小于才会让这个查询右侧的列索引失效，这里大于等于和小于等于不会
2. 把范围查询放最右边就可以解决这个问题
3. 在引入MyBatis之后还可以使用XML 里的转义字符：>就是大于号< 就是 小于号
   

在索引列上进行计算

就是在where条件里面进行运算，这里索引就会失效

• 注意：这里和前缀索引不一样，前缀索引的计算不是在where条件而是在select后
  

字符串不加引号

顾名思义

前面模糊查询

只要这里是前面的模糊匹配，这里的索引就会失效

• 注意：但是你会发现在苍穹外卖里面MyBatis里面的xml文件里面经常会有这个concat(‘%’,#{ },‘%’)，在这种情况下，索引是会失效的
  

or连接带来的索引问题

这里只要左右条件有一个没有索引，那就是没有索引

mysql自行评估

• 这里假如每个数据都比1779990005大，这里mysql会评估然后不走索引，使用全表扫描
  

索引优化和SQL优化

索引优化

这里就是形成覆盖索引：尽可能让select包括order by和group by后面的字段都在索引里面（注意可不是where了，我刚刚还写的where），这样的查询效率最高，因为不需要回表查询

SQL优化

对insert的优化

1. 这里的优化策略就是在500-1000条批量插入这个量级使用批量插入，因为如果逐条插入就会有一次次的连接，这里就会出现问题
2. 再大一点的量级我们就要使用load命令了

对主键的优化

InnoDB操作页策略

首先我们需要知道innoDB存储引擎操作的最小单位是页，由此引申出三种对页的操作策略

1. 页分裂策略
   • 顺序插入：这里就是很正常，如果这里一个页不够就新增一个页来进行存储
   • 乱序插入：这里就是如果是乱序插入，比如一个页里面有1389，然后现在插入一个7，然后直接分裂，如果没有再插入的话，这里的空间利用率不会很高
2. 页合并策略
   这个策略和上面的页分裂策略不是冲突的，甚至可能是一起发生的
   • 如果说这里有一个元素删除，这里会使用顺序表的那种删除方式，不是物理删除，但是是标记这个位置表示这个空间可以使用，然后不再查询这个数值
   • 等到这里的数据达到了MERGE_THRESHOLD（默认是50%），这个时候就会触发页合并，然后这里提高空间利用率，所以说上面的页分裂可以通过这种策略来把空间利用率抬高
   • 但是会带来合并分裂震荡问题，如果这里两个页是50%，这里先合并，但是这里分裂的阈值是98%，这个时候就会不断分裂合并，所以这里建议配置减少MERGE_THRESHOLD这个值到30%-40%

实际优化策略

核心点：这里就是针对这里的长度和乱序问题进行优化

1. 这里尽量减少主键的长度
2. 然后另外一个点就是这里尽量不要使用乱序UUID什么的，尽量都是顺序插入自增主键

对order by优化

核心点：这里就是在多字段查询的时候尽量使用联合索引

实际优化策略

1. 遵循最左前缀法则
2. 尽量使用覆盖索引
3. 需要混合顺序查询的时候需要构造一个混合顺序的联合索引，比如where条件后面name 是asc ，age desc，这个时候就构造一个联合索引顺序不一样（默认顺序一样都是asc）
4. 每次排序都有一个排序缓冲区存在，在内存排序速度快的一批，所以这里尽量排序的数据量大小不要超过数据缓存区大小（sort_buffer_size:默认256K），如果超过，就增大缓冲区大小，这里如果不增大就会分块来进行排序放到磁盘，最后在合并，会增加磁盘IO开销

对group by优化

核心点：尽量使用索引

实际优化策略

所以这里其实根据情况建立索引或者使用对应就行，注意最左前缀法则

对limit优化

核心点：尽量使用覆盖索引，给一个场景吧

• 这里创建子查询的覆盖索引，这里快速查询子查询覆盖索引里面2000000条数据，然后最后10条进行回表查询（因为这里查询的是*），所以这里原先庞大的查询被优化
  

对count优化

核心点：这里的性能瓶颈就在判断是否为空里面，尽量使用count 1和count*

• 这里引入一个面试题：count 1和count * 的底层

1. 这里count 1就是底层直接变成 count *
2. count * 就是取出一个最小的二级索引（因为只有字段和主键值），然后按行累加，如果没有就按照聚簇索引
   

对update优化

核心点：这里就是避免全表扫描，所以建立索引就行
原因：
这里就是规避行锁升级为表锁，比如我们现在执行第二个SQL语句（前提是这里没有建立过name索引），然后这个时候这里的update语句会全表扫描，给所有的行都加一个锁，其实相当于这里就加了一个表锁，这个表就被锁住了，我们就无法再执行第一个SQL语句对这个表进行操作了

总结

索引还是比较重要的，这里涉及到好多SQL的优化，我第一次学的时候觉得一般，如果深挖的话还是有很多底层，只能说还是太权威了好吧。