Mysql索引优化的方法

• 前缀索引优化

        使用某个字段中字符串的前几个字符建立索引，为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。        

        局限性：order by无法使用前缀索引；无法把前缀索引用作覆盖索引。

• 覆盖索引优化

        指一个索引包含了查询所需的所有列，而不仅仅是索引列本身。当一个查询可以直接从索引中获取到所需的数据，而无需通过回表操作访问数据行，就称之为覆盖索引。

        索引本身已经包含了查询所需的所有列的值，因此查询可以直接从索引中获取到所需的数据，无需进行回表操作，这样可以减少IO开销，提高查询性能和响应速度。

• 主键索引最好是自增的

        InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的。

        如果我们使用自增主键每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

        非自增主键，由于每次插入主键的索引值是随机的，可能回导致导量内存碎片，导致索引结构不紧凑，影响查询效率。

• 索引最好设置为 NOT NULL

        索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，索引统计的时候，count会省略值为NULL的行；

        NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来存储空间的问题。

• 防止索引失效

索引失效：

        1. 对索引使用做左右模糊匹配，也就是使用like语句： like %xx 或者 like %xx% 这两种方式都会造成索引失效。

        因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

        所以%如果放在左边就会导致不知道从哪个索引值开始比较，只能进行全表扫描的方式。

        2. 对索引使用函数 ，MySQL 自带的函数

         因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了，从 MySQL 8.0 开始，索引特性增加了函数索引

        3. 对索引进行表达式计算

        因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，同使用函数。

        4. 对索引隐式类型转换

        MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较

        5. 联合索引的最左匹配

        对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。

        那么多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。联合索引 (x, y, z) 和 (z, y, x) 在使用的时候会存在差别。最左匹配原则，左优先的方式进行索引匹配。

        6. where子句中的or

         在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。