索引规约以及索引常见问题

参考链接

https://www.jianshu.com/p/749f03ff0264

规约分类

与B+树有关：

1. 最左匹配
2. 区分度最高用作第一个索引
3. Varchar字段选定长度（区分度与长度区别）
4. 禁止左模糊匹配或全模糊

防止索引失效：隐式类型转换，无法使用索引的情况：

1. 使用不等于或者not in 的时候无法使用索引导致全表扫描
2. is null，not null也无法使用索引
3. 字符串不加单引号容易索引失效(如整形会隐式类型转换)
4. 用or分割条件，若or前后只要有一个列没有索引，就都不会用索引，尽量用union。？？？
5. 以%开头的like查询

参考链接：
https://blog.csdn.net/weixin_40475396/article/details/105465303
https://blog.csdn.net/weixin_36586564/article/details/79641748?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_baidulandingword-0&spm=1001.2101.3001.4242

orderby注意索引顺序

其他类型：

1. 3个表join + 多表查询用索引
2. 唯一特性字段一定要做唯一索引
3. 覆盖索引，避免回表（索引覆盖就是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。其实就是不用回表
   表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句SELECT * FROM tbl WHERE a=1这样不会产生回表，因为所有的数据在a的索引树中均能找到SELECT * FROM tbl WHERE b=1这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，这就叫回表。）

优化目标：最低range级别，目标ref级别以上，最好constan级别

索引规约

4、5实际上是最左匹配原则

7：？

不使用索引的场景

1. 经常增删改查的列不要建索引，因为修改数据的同时，索引也是要修改的。
2. 有大量重复的列不要创建索引。mysql查询结果要小于30%才会使用索引，即查询量达到总表的30%以上时，索引就会失效。不然会使用全表扫描。mysql优化器认为全表扫描的成本小于索引，所以放弃索引，这是很多情况下没使用索引的原因。下面会具体介绍。所以具有唯一性或者重复性很少的列建立索引会非常有效
3. 表记录太少不要建立索引，只有当数据库已经有列足够多的数据时，它的性能才会有实际参考价值。只有当记录超过1000条数据时，数据总理也超过了mysql服务器上的内存总量时，数据库的性能测试结果才有意义。

其他优化建议

优化建议
最左前缀匹配
索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。
如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。也就是最左前缀匹配原则。

=、in自动优化顺序
不需要考虑=、in等的顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。
如有索引(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。

索引列不能参与计算
有索引列参与计算的查询条件对索引不友好（甚至无法使用索引），如from_unixtime(create_time) = ‘2014-05-29’。
原因很简单，如何在节点中查找到对应key？如果线性扫描，则每次都需要重新计算，成本太高；如果二分查找，则需要针对from_unixtime方法确定大小关系。
因此，索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29’语句应该写成create_time = unix_timestamp(‘2014-05-29’)。

能扩展就不要新建索引
如果已有索引(a)，想建立索引(a, b)，尽量选择修改索引(a)为索引(a, b)。
新建索引的成本很容易理解。而基于索引(a)修改为索引(a, b)的话，MySQL可以直接在索引a的B+树上，经过分裂、合并等修改为索引(a, b)。

不需要建立前缀有包含关系的索引
如果已有索引(a, b)，则不需要再建立索引(a)，但是如果有必要，则仍然需考虑建立索引(b)。

https://juejin.cn/post/6844903924307247111