索引高性能优化
1. 使用独立的索引列
错误的查询语句会使得索引无效…
select user_id from user where user_id-1=4;
- 第一个例子中,对索引列user_id进行了一次方程运算,然后进行查询,然而这样的操作MySQL无法自动识别,导致主键索引失效了。
select date_col from t where TO_DAYS(CURRENT_DATE) -TO_DAYS(data_col)<=15;
select date_col from t where TO_DAYS(CURRENT_DATE) -15>=TO_DAYS(data_col);
-
第二个例子中,目的是获取最近15天的日期数据,对索引列data_col进行了运算,两种不同的表达式方式,前一种导致索引失效,而后一种则正常使用了索引。
-
综合上面两种错误使用,不难看出,为了防止索引失效,不能将索引作为表达式的一部分,而是应该单独放在表达式符号的一侧。(作为独立的列参与语句)
2. 索引的选择性
在介绍前缀索引的优化原则之前,我们先来介绍B+树索引中的前缀查找:
-
以B+树为数据结构的索引,都具有以下几种前缀索引匹配规则
- 匹配最左前缀:当一个索引为多列索引(联合索引),即由多个字段构成时,如有一个3列索引(col1,col2,col3),则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引,即优先使用索引的第一列。
- 匹配列前缀:指的是可以将索引列字段的开头的一部分进行匹配。
-
索引的选择性:
- 不重复的索引值(cardinality)数据表中的记录的总数的比值称为索引的选择性。
- 索引的选择性越高则查询效率越高,由此可以推出,唯一索引的选择性是1。
-
前缀索引中选择性优化
前面我们说,B+树的前缀索引由匹配列前缀实现,那么对于一些如TEXT等类型的VARCHAR列来说,我们使用前缀索引来查询其部分字符。
-
选择性计算:使用DISTINCT函数获取不重复的索引值,除以COUNT(*)记录总数,
select COUNT(DISTINCT city)/COUNT(*) FROM tcity;
-
限制匹配长度:根据上面获取的选择性值,用LEFT()函数试验不同的长度限制取得的选择性,找到最接近最佳选择性的限制数量。
SELECT COUNT(DISTINCT LEFT(city,4)/COUNT(*)) FROM tcity; SELECT LEFT(city,3) FROM tcity;
-
-
平均选择性优化的劣势
-
当数据分布很不平均的时候,我们使用平均选择性的值作为匹配手段效果会很差。
-
使用前缀索引,MySQL无法形成自带的ORDER BY 和GROUP BY操作,一定程度上来说,这削减了索引的功能。
-
3. 使用多列索引时选择合适的索引列顺序
-
索引合并策略的糟糕效率:
- 索引合并:指同一个表上多个但列索引进行定位查询,然而其带来的效率却差强人意。很多时候单列索引间的AND与OR算法操作,会给CPU、内存资源在算法缓存,排序及合并上消耗大量资源。
- 当出现多个单列索引做相交操作(多个AND):说明需要一个多列索引包括这些单列字段,而不是多个单列索引。
-
当使用多列索引的时候,同样遵循最左列原则,即先按照最左列排序,其次是第二列。那么一般情况下,适应选择性高的列作为多列索引的最左列,可以提高查询效率。
-
然而整体查询性能,还着重受影响于值的分布,WHERE字句的排序,分组和范围约束条件等。
4. 使用聚簇索引和覆盖索引
- 聚簇索引的概念上篇中已经详细描述了,在这里仅补充一些聚簇索引使用的劣势:
- 聚集索引的插入速度严重依赖插入顺序:当插入顺序按照主键顺序时,插入效率自然很高,这是由聚簇索引的叶子节点按照键值大小顺序排序的结构导致的。然而不按这个顺序插入的时候,效率就会降低,因此全部数据加载完成后,应该使用OPTIMIZE TABLE进行重新组织。
- 更新聚簇索引的代价很高:这会导致每个被更新的行强制移动到对应位置。
- “页分裂”问题:当聚簇索引的主键值要求某行插入到一个已满的页时,会导致页分裂,之前介绍过,页分裂会导致页的利用率**降低至50%**左右。
- 聚簇索引可能导致全表扫描变慢:尤其是行比较稀疏,或者由页分裂导致的数据不连续情况下。
- 聚簇索引的二级索引有时体量可能过大。
- 覆盖索引:
- 定义一个索引中包含了要查询的字段的值,为索引覆盖。
- 优势:
- 加快读取速度,减少数据访问量:我们知道索引的数目远远小于数据的条数,只需要读取索引意味着减少了缓存的负载。
- 减少内存访问次数:因为索引按照顺序存储,对于I/O密集型范围查询时,从较小的索引文件中读取数据,会比从磁盘读取数据的I/O代价小得多。
- 对于InnoDB中的聚簇索引,如果二级索引能够作为覆盖索引,那么避免了对主键索引的回表问题。
- 只有B+树索引支持覆盖索引
- MySQL不支持对索引进行LIKE操作,只能进行简单的比较操作
5. 前缀压缩索引
MyISAM通过前缀压缩可以减少索引的大小,从而让更多的索引进入内存。
-
前缀索引压缩默认只压缩字符串,可以通过设置参数来对整数做压缩。
-
压缩方法如下:
-
先完全保存索引块的第一个值。
-
然后将后续的值与其比较
-
得到相同的前缀字节数和不同的后缀
-
将其按照“字节数,不同后缀"的形式存储
-
6. 去除冗余和重复索引
-
重复索引:按照相同的排列顺序,对相同的列设置了同样类型的索引。
CREATE TAVLE T(ID INT NOT NULL PRIMARY KEY, UNIQUE(ID), INDEX(ID) ) ENGINE=InnoDB;
这种情况下就是创建了三个重复索引,因为用户对ID字段的唯一限制和主键限制实际是都是通过隐式创建索引实现的。
-
冗余索引:在创建索引(col1,col2)的基础上,又创建了索引(col1),此时(col1)被称为冗余。当然,也需要注意,如果对(col1,col2)创建索引(col2)则不是冗余,这里对应最左前缀原则。