高性能索引策略

最新推荐文章于 2024-07-09 13:47:59 发布

木村牧村

最新推荐文章于 2024-07-09 13:47:59 发布

阅读量196

点赞数

分类专栏：跟我学mysql 文章标签： mysql

本文链接：https://blog.csdn.net/a303549861/article/details/83864445

版权

跟我学mysql 专栏收录该内容

25 篇文章 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就是我们需要的前缀字符长度。