mysql索引优化，更好的创建和使用索引

我们如何更好的创建和使用索引呢？

大家记住以下这个8个方案就OK啦。

1、只为用于搜索、排序或分组的列创建索引

我们只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。仅出现在查询列表中的列就没必要建立索引了。

2、考虑索引列中不重复值的个数

我们知道在通过二级索引+回表的方式执行查询时，某个扫描区间中包含的二级索引记录数量越多，就会导致回表操作的代价越大。==我们在为某个列创建索引时，需要考虑该列中不重复值的个数占全部记录条数的比例。==如果比例太低，则说明该列包含过多重复值，那么在通过二级索引+回表的方式执行查询时，就有可能执行太多的回表，导致性能下降。

对二级索引+回表的这种查询方式还不是太了解的同学请看这篇文章
mysql 回表的代价（InnoDB）

3、索引列的类型尽量小

在定义表结构时，要显示的指定列的类型。以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT这几种，他们占用的存储空间大小依次递增。
如果想对某个整数类型的列建立索引，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，如果能使用INT就不使用BIGINT，因为数据类型越小，索引占用的存储空间就越少，在一个数据页就能存放更多的记录，磁盘IO的性能损耗也就越小，一次页面IO可以将更多的记录加载到内存。

4、为列前缀建立索引

我们知道一个字符串其实是由若干个字符构成的，如果在mysql中使用utf8字符集来存储字符串，则需要1~3个字节来编码一个字符。假如字符串很长，那么存储这个字符串就需要占用很大的存储空间。在需要为这个字符串所在的列建立索引的时候，就意味着在对应的B+树中的记录中，需要把该列的完整的字符串记录下来。字符串越长，在索引中占用的空间也就越大。
其实索引列的字符串前缀也是排好序的，设计索引的人员提出了一个方案，只将字符串的前几个字符存放到索引中，也就是说在二级索引的记录中只保留字符串的前几个字符。创建语句如下：

ALTER TABLE demo_table ADD INDEX idx_key1(key1(10)); //创建的字符串索引只保留前10个字符

5、覆盖索引

为了彻底告别回表操作带来的性能损耗，建议最好在查询的列表中只包含索引列，例如：

SELECT key1,id FROM demo_table WHERE key1 > 'a' AND key1 < 'c'; 

由于我们只查询 key1,id列的值，所以在使用idx_key1索引来扫描(‘a’,‘c’)区间中的二级索引记录时，可以直接从获取到的二级索引记录中读出key1列和id列的值，而不需要执行回表操作，我们把这种索引中已经包含所有需要读取的列的查询方式称为覆盖索引。

排序操作也优先使用覆盖索引进行查询，比如：

SELECT key1 FROM demo_table ORDER BY key1;

如果我们的业务中没有必要使用索引列以外的列，或者没有必要使用全部的列，我们最好比需要的列放到查询中，而不是用 select * 代替。

6、让索引列以列名的形式在搜索条件中单独出现

如下的方式并不会使用到索引，而是全表扫描：

SELECT * FROM demo_table WHERE key2 * 2 < 4; 

如下的方式可以使用到索引：

SELECT * FROM demo_table WHERE key2 < 4 * 2; 

所以，如果想让某个查询使用索引来执行，请让索引列以列名的形式单独出现在搜索条件中。

7、新插入记录时主键大小对效率的影响

我们知道，对应InnoDB存储引擎来说，在没有显示的创建索引时，表中的数据实际上是存储在聚簇索引的叶子节点中，而且B+树的每一层数据页以及页面中的记录都是按主键值从小到大排序的。如果新插入的主键值是依次增大的话，则每插满一个数据页就换到下一个数据页继续插入，如果新插入的主键值忽大忽小，就很麻烦了。

假如某个数据页的聚簇索引的记录已经满了，他的存储主键值在1-100之间， 我们再插入一条主键值为9的记录，这个时候新插入的记录会到哪里？存储引擎会把当前页面分裂成两个页面，把本页中的一些记录移动到新页中。页面分裂就意味着性能损耗
所以我们一般都把表中的主键设置为自动递增 AUTO_INCREMENT

8、冗余和重复索引

比如某个列是在某个组合索引中，然后我们又为这个列建立了一个单独的索引，这个是没有必要的，或者我们为主键列单独建立了索引，也是没有必要的，因为主键本来就自带索引。

创作不易，点个赞或者加个收藏吧~👍

最后的最后送大家一句话

白驹过隙，沧海桑田

与君共勉

文章持续更新，可以关注下方公众号或者微信搜一搜「 最后一支迷迭香 」第一时间阅读，获取更完整的链路资料。