MySQL~索引的优缺点是什么？有哪些优化索引的方法？

1.索引的优缺点

优点：提高查询记录的速度。

缺点：

• 需要占用空间，索引是一种用空间换时间的做法
• 创建索引和维护索引都需要消耗时间，会降低表的增删查改效率，因为每次进行增删查改，都需要对索引进行维护，需要消耗时间

什么情况下可以使用索引？

• 经常作为查询条件的字段，如果需要同时查找多个字段，可以建立联合索引
• 经常放到GROUP BY或者ORDER BY后面的字段，进行GROUP BY或者ORDER BY都会对数据进行排序，这些字段就可以建立一个索引，这样就不需要每次都进行排序了

什么情况下不可以使用索引？

• 不经常作为查询条件的字段
• 不经常放到GROUP BY或者ORDER BY后面的字段
• 重复率高的字段，比如性别，建立索引并不会明显提高查询效率，毕竟索引也需要占空间。
• 表数据量很少的时候，全表查也非常快，你创建维护索引反倒需要开销。
• 经常需要更新的字段，如果建立了索引，索引也需要频繁维护（B+树是要保证数据有序的），会影响数据更新的效率。

2.如何优化索引？

前缀索引优化：对一个字符类型的字段使用前缀索引，前缀索引指的是只为前几个字符建立索引，而不是整个字段建立索引，这样可以减小索引的大小。

• 适用场景：对于一些字符串很长的字段可以使用前缀索引
• 局限性：1.order by无法使用前缀索引；2.无法把前缀索引用作覆盖索引

覆盖索引优化：建立适当的联合索引，如果要查询的字段就在联合索引中，就可以避免回表，提高查询效率

主键最好是自增的：因为MySQL中索引底层采用的是B+树的结构，它会对索引进行排序；

        如果主键不是自增的，插入新的记录就很可能导致页分裂的情况，导致数据页的记录需要发生移动以此来维护索引的有序性，会影响到数据的插入效率。同时也可能会导致内存空间的浪费，影响查询效率。

        如下所示：假设数据页中存有1,3,5,9的索引数据，此时数据页已满，插入索引数据7就会导致页分裂，需要移动元素，同时后续左边的数据页只有小于7的数据才能使用了，大于7的数据就无法使用。

        如果主键是自增的，每插入一条记录，在数据页中都是追加操作，一个页不够了就开辟一个新的页，就不会发生页分裂的情况，插入效率更高。同时也不会有内存空间的浪费。

索引最好是not null

        如果索引列存在null会导致MySQL更加难以优化，会影响到查询的效率（比如进行统计的时候，count会省略为null的行）。

        null虽然是空，但是需要额外的一个字节来标识这个null值，更占用空间

防止索引失效

        有时候加上了索引，但是查询的时候可能会导致索引失效，没有使用到索引，所以我们要尽量避免索引失效。

1.使用左模糊匹配或者左右模糊匹配会导致索引失效，因为索引底层是B+树实现的，索引是有序的，只能根据前缀进行比较，在前缀相等的情况下，后续的字符才是有序的。所以无法用在左模糊和左右模糊查询中。

2.对索引列进行计算，函数，类型转换等操作，会导致索引失效。

• 对于函数和计算，索引保存的是字段原先的值，而不是计算后的值。因此计算后的值无法使用索引。
• 对于类型转换，比如MySQL在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较，此时实际上就是对字符串的列使用了CAST 函数，所以也会导致索引失效。

3.联合索引不遵循最左匹配原则，比如建立了联合索引(a, b)，查询条件为where b = 1;，此时索引失效。原因：联合索引是先根据第一列排序，再根据第二列排序。你如果无法确定第一列，第二列就是无序的，自然就无法使用索引。

4.WHERE子句中，OR前后有一个不是索引列，会导致索引失效。因为OR的含义是两者满足一个即可，所以只有一个列有索引是没有意义的，都会进行全表扫描。