高性能索引策略

逃离我推掉我的手 2022-04-18 04:48 210阅读 0赞

## 1.隔离列 ##

如果在査询中没有隔离索引的列， MYSQL通常不会使用索引。“隔离”列意味着它不是表达式的一部分，也没有位于函数中。

例如，下面的查询不能使用 actor id上的索引

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

应该简化WHERE子句的，这样就会把被索引的列单独放在比较运算符的一边 .

下面是另外一种常见的错误:

mysql > SELECT ... where to_days(current_date) - to_days(date_col) <= 10;

这个查询将会查找 date\_col值离今天不超过10天的所有行，但是它不会使用索引，因为使用了to\_days函数

下面是一种较好的方式：

mysql>SELECT ... where date_col >= DATE_SUB(current_date,interval 10 day);

这个查询就可以使用索引，但是还可以改进，因为current\_date会阻止查询缓存把结果缓存起来，可以用常量来替换。

mysql>SELECT ... where date_col >= DATE_SUB('2018-11-01',interval 10 day);

## 2.前缀索引和索引选择性 ##

有时需要索引很长的字符列，它会使索引变大并且变慢。一个策略就是模拟哈希索引。但是有时这也不够好，那么应该怎么办呢?

通常可以索引开始的几个字符，而不是全部值，以节约空间并得到好的性能。这使索引需要的空间变小，但是也会降低选择性。索引选择性(Index Selectivity) 是不重复的索引值(也叫基数(Cardinality)) 和表(\#T)中所有行的比值，它的值在1/\#T和1之间。高选择性的索引有好处，因为它使MySQL在查找匹配的时候可以过滤掉更多的行。唯一索引的选择率为1，为最佳值。

列前缀通常可以提供高性能所需的足够选择性。如果索引BLOB和TEXT列，或者很长的VARCHAR列，就必须定义前缀索引，因为MySQL不允许索引它们的全文。

矛盾在于选择足够长的前缀会得到好的选择性，但是短的前缀会节约空间。前缀应该足够长，使它的选择性能够接近于索引整个列。换句话说，前缀的基数性应该接近于全列的基数性。

如果 count(distinct left(col,n))/count(*)更加接近于 count(distinct col) / count(*)，那么就满足了最高的选择性，同时保证了内存最小，n就是我们需要的前缀字符长度。

## 3.聚集索引 ##

聚集索引不是一种单独的索引类型，而是一种存储数据的方式。

一些数据库服务器可以选择聚集的列，但是写作本书的时候，没有任何MySQL的存储引擎能做到这点。InnoDB按照主键(Primary Key)进行聚集。这意味着图3-3中“被索引的列”其实是主键列。

如果没有定义主键，InnoDB 会试着使用唯一的非空索引(Unique Nonullable Index)来代替。如果没有这种索引，InnoDB 就会定义隐藏的主键然后在上面进行聚集(注11)。InnoDB只聚集在同一页面中的记录。包含相邻键值的页面也许会相距甚远。

聚集主键有助于性能，但是它也能导致严重的性能问题。因此应该仔细思索聚集，特别是把表的存储引擎在InnoDB和其他引擎互换的时候。

**聚集的数据有如下优点:**

可以把相关数据保存在一起。例如，当实现电子邮箱的时候，可以按照user\_ id进行聚集，这样从磁盘上提取几个页面的数据就能把某个用户的邮件全部抓取出来。如果没有使用聚集，读取每个邮件都会访问磁盘。

数据访问快。聚集索引把索引和数据都保存到了同一棵B-Tree中，因此从聚集索引中取得数据通常比在非聚集索引进行查找要快。

使用覆盖索引的查询可以使用包含在叶子节点中的主键值。