《高性能|高性能 MySQL（七）（11个高性能的索引策略） mysql|数据库|sql|搜索引擎|MySQ

文章图片

?? 个人主页：水滴的博客_CSDN博客
系列专栏：高性能 MySQL
支持水滴：点赞+收藏?+留言

大家好，我是水滴~~
正确地创建和使用索引，是实现高性能查询的基础。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。
接下来介绍一些索引策略，希望能帮助你理解如何高效地使用索引。
1. 独立的列

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面查询中，索引列age是表达式的一部分，则无法使用该列索引：

select * from `user` where age + 1 = 19;

凭肉眼很容易看出，条件表达式其实等价于 age = 18，但 MySQL 无法自动解析这个过程。所以，我们应该养成简化where条件的习惯。
另外，下面查询中，索引列create_time是函数的参数，则无法使用该列索引：

select * from `user` where LEFT(`name`, 1) = '张'

2. 前缀索引

有时候需要索引列的字符很长，这会让索引变得大且慢。通常可以使用前缀来索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

对于 BLOB、TEXT 或很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度。
3. 多列索引

很多人对多列索引（联合索引）的理解不够，认为“将where条件后面的列都建上索引”就可以了。其实不然，这样虽然能起到一定的作用，但决不是最优的。

例如，下面这条语句，为age和sex建立了单列索引。那么该语句在执行时，会先通过age列索引找出40岁的用户，然后再从这个结果集中找到sex为女的用户。

select * from `user` where age = 40 and sex = '女';

如果使用多列索引，会通过该索引一次性找到正确的结果。
4. 索引顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，这样依次类推。所以多列索引的列顺序至关重要。

那么如何选择索引的列顺序呢？有一个经验法则：将选择性最高的列放到索引最前列。
例如，在下面查询中，我们是创建一个(age, sex)索引还是应该颠倒一下顺序呢？

select * from `user` where age = 40 and sex = '女';

我们可以通过一些查询来看表中值的分页情况，并确定哪个列的选择性更高：

文章图片
)
通过查询结果可以看出，age列的数量更小，所以选择性更高，应该将索引列age放到前面。
5. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。通过聚簇索引访问行很快，因为索引直接指向包含行数据的页面。

当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页中，无法同时把数据行存放在两个不同的地方，所以一个表中只能有一个聚簇索引。

InnoDB 通过主键聚集数据，如果没有定义主键，会使用第一个唯一索引（并且是非空的）作为聚簇索引。如果两者都没有，InnoDB 会生成一个隐藏的聚簇索引。

对于主键 MySQL 推荐使用 AUTO_INCREMENT 自增序列，这样可以保证数据行是顺序写入的，通过主键进行查询时性能会更好。
最好避免使用随机的或不连续的值做为主键，比如 UUID，它让聚簇索引的插入变得完全随机，使得数据没有任何聚集特性。
6. 覆盖索引

如果一个索引包含所有需要查询的字段值，我们就称之为“覆盖索引”。

覆盖索引能够极大地提高性能，因为索引中就包含我们想要的数据，这样就能直接找到结果数据，不用再加表查询。
例如，有一个索引是(age, name)，如果执行下面查询时，可以通过索引直接获取name数据，而无需回表查询。

select name from `user` where age = 40;

7. 通过索引来排序扫描索引本身是很快，因为索引是顺序存储，只需从一条索引记录移动到下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都加表查询一次对应的行。这基本上都是随机 I/O，因此按索引顺序读取数据的速度，通常比顺序地全表扫描慢。
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方法都一样时，MySQL 才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。
8. 压缩（前缀压缩）索引 MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。
压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值。所以 MyISAM 查找时无法在索引块使用二分查找，而只能从头开始扫描。
9. 重复和冗余索引

重复索引是指在相同的列上，按照相同的顺序创建相同类型的索引。

【《高性能|高性能 MySQL（七）（11个高性能的索引策略）】MySQL 允许在相同列上创建多个索引，并且会单独维护这些索引，优化器在优化查询的时候也会逐个进行考虑，这会影响性能。应该避免创建重复索引，发现以后也应该立即移除。
有时会在不经意间创建了重复索引，例如下面代码：

create table test( id INT NOT NULL PRIMARY KEY, a INT NOT NULL, b INT NOT NULL, UNIQUE(id), INDEX(id) ) ENGINE=InnoDB;

该代码先将id列标记为主键，再标记为唯一索引，又加上了索引。事实上，MySQL 的唯一索引和主键都是通过索引实现的，因此，上面的写法实际上就是创建了三个重复的索引。

冗余索引和重复索引有一些不同。如果创建了索引(A, B)，再创建索引(A)就是冗余索引，因为(A)只是(A, B)的前缀索引。因此索引(A, B)也可以当作索引(A)来使用。但是如果再创建索引(B, A)或(B)，则不是冗余索引。

10. 未使用的索引

除了冗余索引和重复索引，可能还会有一些永远不用的索引，这样的索引完全是累赘，建议删除。

删除前也需斟酌，比如唯一索引，虽然该索引一直没有被查询使用，却可能是用于避免产生重复数据的。
11. 索引和锁

索引可以让查询锁定更少的行。

InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。
如果索引无法过滤掉无效的行，那么在 InnoDB 检索到数据并返回给服务器层以后，MySQL 服务器才能应用where子句。这时已经无法避免锁定行了，虽然已经锁定了这些行，但会在服务器端过滤掉行后就释放锁。