1. 为什么是B+树
1.1 哈希索引
哈希胜在查找单条数据快，只需要计算哈希函数就可以定位到索引数组下标，继而得到值，ps：索引存储的不是直接的数据库中的值，而是地址，通过索引找到地址然后就可以取数据库中地址对应的值了。
但是如果使用哈希表做索引，是无序的（30 % 29 = 1会在2的前面），但是数据库经常需要范围查询，大于小于，排序等操作，因此哈希索引不合适
1.2 AVL树
平衡二叉树作索引，查询效率是O(logn)，但是有两个缺点
- 树越高查询速度越慢
- 中间的某个id比如0005去到了叶子节点，那么要查找id大于0005的就需要回旋查找0006
1.3 B树
一个节点可以存多个值，解决了AVL树高度过高的问题，但是回旋查找的问题依然存在
1.4 B+树
B+树就是为了解决回旋查找的问题，每个根节点出现过的元素都会在叶子节点出现一次（非叶子节点只存key，叶子节点还存了地址，用于在数据库中查找），最底下的叶子节点层直接指向了下一个节点，这样查询大于某个id的时候就可以先通过树找到对应的节点，然后通过链表一直往后查询要找的数据
最后贴上这个数据结构可视化网站，好像是旧金山大学的网站
1 | https://www.cs.usfca.edu/~galles/visualization/Algorithms.html |
2. explain查看索引使用情况
只需要在select语句前面加上explain关键字即可解释查询
1 | explain select * from table_name where id > 9 |
关注几个字段
type：代表了索引的级别，有以下几个级别
- system：查找只有一条数据的系统表，基本不可能达到
- const：查主键（where id = 1）因为主键是唯一的，而且mysql自带主键索引
- eq_ref：建立了唯一索引/主键索引+要求查出来的索引列是唯一的（where 身份证号 = “123”）
- ref：对应列建立了索引（可以是联合索引）+在索引列上查询（where name = “张三”），是一般sql优化期望达到的级别
- range：对应列建立了索引+在索引列上范围查找（between、in、>、<、>=等），要注意in只有在数据量大的时候才生效
- index：也叫覆盖索引，对应列建立了索引+查询结果返回字段限定（select name比select *好的原因），要注意如果name列没有建立索引是不可以的！这种情况下实际还是遍历了所有数据，但是遍历的是索引树（内存），而不是数据库（硬盘）
- all：没有用到索引
possible_keys：代表可能用到的索引
key：代表实际用到的索引
rows：实际扫描的行数，越小越好，说明过滤的越彻底
ref：索引各个字段的级别（因为有可能是复合索引但不是所有都同一个级别）
key_len：索引占用的空间，越小越好，因为有可能你用到了索引，但该索引可能不是最优（多了不必要的字段）
实战环节！
环境：1000条数据，表结构如下
2.1 无索引效果
1 | SELECT SQL_NO_CACHE * FROM `test_user` WHERE phone = "13542831871" AND lan_id = 702 AND region_id = 94 |
加上SQL_NO_CACHE指定不使用缓存，查询更接近真实值，当然我觉得还是有缓存的，第一次5s，第二次开始稳定在3.7s左右
解释执行，可以看到type为all，也就是没有使用索引，扫描了全表
2.2 有索引效果
1 | ALTER TABLE `test_user` ADD INDEX idx_phone_lan_region(phone, lan_id, region_id) |
随后再解释查询，第一次执行1.3s，随后稳定在0.03s，可以看到提升还是很大的
可以看到type为ref，代表使用了索引，key说明最终使用的索引名称，ref为const表示三个字段都用到了索引，rows为1表示共扫描1条。这就是最好的状态了，能用的索引都用上了，精准定位！
3. SQL调优
整个流程是这样的
使用explain查看sql执行计划并排除缓存干扰
analyze table和force index强制走索引
检查是否能优化SQL中的函数和计算
检查是否存在类型/编码转换导致的索引失效
检查like左边是否有%导致索引失效
order by文件内排序优化
检查是否能使用覆盖索引代替select *
检查是否能通过联合索引减少回表次数
检查是否能优化最左匹配法则的顺序
长字段使用前缀索引优化
普通索引和唯一索引的选择优化
考虑MySQL5.6以上版本带来的索引下推优化
防止像我这种啥也不懂的菜鸡乱加索引导致的索引失效问题(●’◡’●)
其实根本原因就是前面说的B+树的结构，结构决定性质，能根据B+树找到对应的节点，那么就不会失效，下面一一介绍。
3.1 函数
如使用了count(*)、sum()、round()会导致失效，注意：计算/函数导致的失效指的是“select后”和“=前”的字段，因为=后面的函数是能用到索引的比如where lan_id = 1 + 1和where name = lower(s)
3.2 计算字段后面的字段
计算字段：+、-、*、/、!=、<、>、is null、is not null、or
先来看这个range，说明索引列+>是可以用到range级别的索引的
接着把这个>后面的region_id字段去掉
这就说明实际上复合索引的最后一列region_id是没有被用到的，只用到了前两列。
另外就是id + 1 = 1000改成id = 1000 - 1才能走索引
3.3 类型转换
id = “1”本来是数字，虽然也能查，但是索引失效（5.7之前不失效）
都会显示如下
也就是看到查询phone字段说明可能会用到索引，但实际上用不到
3.4 编码不一致
utf8mb4和utf8两张表联查时，会自动转换成utf8mb4，如果索引在utf8的表中，这样是用不到索引的，究其原因是二叉树查找也是得判断是否相等，编码不一致找不到
3.5 like的失效和优化
like左边有%是用不到索引的
只有右边有%可以用range级别的索引
由于模糊搜索业务场景通常需要两边%，这时候如果只搜索这个字段，我们可以使用覆盖索引（前面介绍的index）优化，也就是select phone而不是select *，可以看到也是有索引的
如果需要全部字段，根据本次的phone结果再搜索一次=，总体会比全表扫描快
另一种优化方案是建立一个反向列，很不错的思路，可以参考这篇文章
https://blog.csdn.net/weixin_38106322/article/details/106583450
mysql5.7后可以建立虚拟列，参考这篇文章
https://blog.csdn.net/JiSoBeautiful/article/details/96433283
也可以直接上es，可以进行隔字查询，适应更多业务场景
3.6 order by的失效和优化
order by会使索引失效
先对create_time列建立索引
1 | ALTER TABLE `test_user` ADD INDEX idx_create_time(create_time) |
查询时间5s+
解释查询，可以看到type为all，另外要看extra列，using filesort说明使用了文件内排序：意思是它在内存中开辟了一个空间，将数据复制到内存里面，进行排序再返回，这是很恐怖的一件事情，浪费了大量的内存
优化方案：使用覆盖索引select create_time
时间如下
覆盖索引虽然速度提升明显，但是只查一列很难满足业务场景，如果结果为一条，那么还好，可以根据这一条再查一遍=，但是如果有多条，使用in不一定能带来很大的提升，时间和解释如下图
因此，不如读取到java中进行排序来的快
3.7 最左匹配法则
最左匹配法则：我们的索引字段依次是phone, lan_id, region_id，因此能匹配的组合实际有：
phone, lan_id, region_id
phone, lan_id
phone
我们试一下查询条件只有第一个和第三个
可以从type和key中看出，依然使用了这个复合索引，但是从ref看到破坏了最左匹配法则情况下，只有第一个字段phone使用了索引。时间的话由于结果都只有1条数据，所以相似，参考意义不大。
可以简单的想象成几座连续的桥，只有过了第一座桥才能过第二座桥。当然知其然也要知其所以然，要知道究竟为什么还是得自己心里有B树( •̀ ω •́ )
联合索引的树节点是这样的，data里面存了一个元组（三个就是三元组）
因为在建树和查找的过程中需要比较大小（中文也支持），是按照索引的顺序来的：先比较第一个，第一个相同再比较第二个…以此类推，因此只有在前面的字段有序（能找到）的情况下后一个字段才有序（能找到），比如上图只有在字段a为1的情况下b才是有序的，总体看b是没有序的。因此这就是为什么出现最佳左前缀法则的根本原因。
3.8 前缀索引
简单说就是对于长字段，比如descirption，或者短字段但想节省索引长度，可以截取字段的一部分作为索引，从而节省索引key_len。因为索引越长，占用磁盘越大，一个数据页存的索引就越少，会降低效率，举个例子：
邮箱xxxx@qq.com，我们索引可以只做xxxx
网址www.abc.com，我们可以截取第一个.后面的来建立索引
身份证前面是区号，区分度不高，可以reverse后再截取
那么如何评价区分度，前缀需要截取多少位呢？参考这篇博客
1 | -- 计算出完整字符串的选择性(比如0.3) |
3.9 唯一索引和普通索引的选择
这里涉及到一个概念就是change buffer：
MySQL查询的时候会将数据页加载进内存中，更新的话就可以直接更新内存中的数据，而不是直接刷写硬盘（这是最耗时的）。如果没查询，那么内存中没有数据，更新操作缓存在change buffer中，下次查询的时候再将数据页读入内存，然后执行change buffer中与这个页有关的操作，这个过程叫做merge。
唯一索引的劣势：需要进行唯一性检查，那么会读取数据页到内存，无法使用change buffer
唯一索引的优势：因为是唯一的，查询的时候找到即可，不需要往右继续查是否相同
综上所述
3.10 索引下推
5.6之后mysql自带的优化，看下面这个语句，假设建立了索引(name, size)
1 | select * from itemcenter where name like '张%' and size=22; |
5.6之前，在B+树中找到10个张%，那么会将这10条数据的id进行回表找到size=22
5.6之后，在B+树中找到10个张%，先判断索引中的size=22，过滤后可能剩2条，再根据id回表
参考
本文参考
IT老哥公众号和bilibili视频（https://space.bilibili.com/526653251?from=search&seid=7511781974100514678）
三太子敖丙公众号MySQL系列文章
SCDN博客：https://blog.csdn.net/u013295276/article/details/79105163