问十二：如何优化一个索引？

最新推荐文章于 2024-07-01 22:44:12 发布

问而知

最新推荐文章于 2024-07-01 22:44:12 发布

阅读量466

点赞数

分类专栏： MySQL数据库

MySQL数据库专栏收录该内容

16 篇文章 4 订阅

订阅专栏

1. 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

2. 多列索引

当查询条件有多个字段时，单列索引和多列索引有很大的区别。

如果使用多列索引，where条件中字段的顺序非常重要，需要满足最左前缀列。最左前缀：查询条件中的所有字段需要从左边起按顺序出现在多列索引中，查询条件的字段数要小于等于多列索引的字段数，中间字段不能存在范围查询的字段(<,like等)，这样的sql可以使用该多列索引。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actorWHERE actor_id = 1 AND film_id = 1;

实例：现在我们想查出满足以下条件的用户id： SELECT ｀uid｀ FROM people WHERE lname｀='Liu' AND ｀fname｀='Zhiqun' AND ｀age｀=26

A、单列索引： ALTER TABLE people ADD INDEX lname (lname); 将lname列建索引，这样就把范围限制在lname='Liu'的结果集1上，之后扫描结果集1，产生满足fname='Zhiqun'的结果集2，再扫描结果集2，找到 age=26的结果集3，即最终结果。由于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需要的。

B、多列索引：ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age); 为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

注意：项目里面查询使用了两个条件，因此如果要优化，必须使用多列索引，而且username字段要比applicationname字段的选择性强，同时使用最频繁，因此它应该位于最左边。 C.最左前缀：最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

3. 索引列的顺序

让选择性最强（where子句中使用最频繁的一列，且离散度大的）的索引列放在前面，索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,

COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,

COUNT(*)FROM payment;

staff_id_selectivity: 0.0001

customer_id_selectivity: 0.0373

COUNT(*): 16049

4. 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符，因为MySQL不允许索引这些列的完整长度。对于前缀长度的选取需要根据索引选择性来确定。缺点：mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

5. 覆盖索引

索引包含所有需要查询的字段的值。即只需扫描索引而无须回表。

具有以下优点：

1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。 2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。 3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。 4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)。

5.覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。