mysql-高性能索引策略

时间:2021-10-29 19:48:52

原文转自:http://www.cnblogs.com/happyflyingpig/p/7655762.html

独立索引:

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

  例1:

SELECT actor_id FROM actor WHERE actor_id+1=5 --这种写法,就算在actor_id上建立了索引,也不起效

  例2:

SELECT .... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10 --这也是一种错误的写法

 

多列索引(联合索引)&选择合适的索引列顺序:

  多列索引(Multiple-Column Indexes)也称为复合索引(composite index),也即同时对多个列建立索引。

  什么时候用多列索引?

  • 当出现服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
  • 当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回大量数据的时候。

  多列索引的生效规则:

  比如(a,b,c),abc都是拍好序的,在任意一段a的下面b都是排好序的,任何一段b下面c都是拍好序的。多列索引的生效原则是从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用;

还需注意:(a,b,c)多列索引和 (a,c,b)是不一样的,看上面的图也看得出来关系顺序是不一样的;

分析几个实际例子来加强理解

 

mysql-高性能索引策略
(0)select * from mytable where a=3 and b=5 and c=4;   --abc三个索引都在where条件里面用到了,而且都发挥了作用
(1)select * from mytable where  c=4 and b=6 and a=3;  --这条语句列出来只想说明 mysql没有那么笨,where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样
(2)select * from mytable where a=3 and c=7;  --a用到索引,b没有用,所以c是没有用到索引效果的
(3)select * from mytable where a=3 and b>7 and c=3;  --a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引
(4)select * from mytable where b=3 and c=4;  --因为a索引没有使用,所以这里 bc都没有用上索引效果
(5)select * from mytable where a>4 and b=7 and c=9;  --a用到了  b没有使用,c没有使用
(6)select * from mytable where a=3 order by b;  --a用到了索引,b在结果排序中也用到了索引的效果,前面说了,a下面任意一段的b是排好序的
(7)select * from mytable where a=3 order by c;  --a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort
(8)select * from mytable where b=3 order by a;  --b没有用到索引,排序中a也没有发挥索引效果
mysql-高性能索引策略

  对于如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。(参考①)

  当不需要考虑排序和分组时,将选择性最高的列放到前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找

 

前缀索引和索引的选择性:

  前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:

  1.不能再OORDER BY 或 GROUP BY 中使用前缀索引;

  2.也不能把他们用作覆盖索引(Covering index)。

  建立前缀索引的语法:

  ALTER TABLE table_name ADD KEY(column_name(prefix_length));

  示例:

  ALTER TABLE city ADD KEY(cityname(7));

  什么叫做索引的选择性呢?①

  所谓索引的选择性(Selectivity),是指不重复索引值(也叫作基数,Cardinality)与表记录数(#T)的比值

  Selectivity = Cardinality / #T

  显然选择性的取值范围为(0,1],选择性越高的索引值价值越大

mysql-高性能索引策略

   SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

  1. +-------------+
  2. | Selectivity |
  3. +-------------+
  4. | 0.0379      |
  5. +-------------+
mysql-高性能索引策略

 

  比如employees表只有一个索引<emp_no>,那么如果我们想按名字搜索一个人,就只能全表扫描了:

mysql-高性能索引策略

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra     |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1  | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

mysql-高性能索引策略

 

这样全表扫描效率很低,所以我们考虑到把名字建立索引,有两种选择,建<first_name>或<first_name,last_name>,看下两个索引的选择性:

mysql-高性能索引策略
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+
mysql-高性能索引策略

 

  从结果看显然<first_name>选择性太低,<first_name,last_name>选择性好。但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法?可以考虑用first_name和last_name的前几个字符建立索引,例如<first_name, left(last_name, 3)>,看看其选择性:

mysql-高性能索引策略
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+
mysql-高性能索引策略

 

  选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:

mysql-高性能索引策略
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+
mysql-高性能索引策略

 

  这时选择性已经很理想了,而这个索引的长度只有18,比<first_name, last_name>短了接近一半,我们把这个前缀索引 建上:

ALTER TABLE employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

 

  此时再执行一遍按名字查询,比较分析一下与建索引前的结果:

mysql-高性能索引策略
SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+
mysql-高性能索引策略

   性能的提升是显著的,查询速度提高了120多倍。

聚簇索引

覆盖索引

冗余索引和覆盖索引

未使用的索引

  应该删除未被使用的索引。有两个工具可以帮助定位未使用的索引。

  1.在Percona Server或者MariaDB中先打开userstates服务器变量(默认是关闭的),然后让服务器正常运行一段时间,再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。

  2.在Percona Toolkit中的pt-index-usage,该工具可以读取查询日志,并对日志中的每条查询进行EXPLAIN操作,然后打印出关于索引和查询的报告

索引和锁  

  InnoDB只有在访问行的时候才会对其枷锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量  

  InnoDB在二级索引上使用共享(读)锁,但访问主键索引需要排他(写)锁。这消除了覆盖索引的可能性,并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE 或非锁定查询要慢很多

InnoDB的主键选择与插入优化

  在使用InnoDB存储引擎时,如果没有特别的需要,请永远使用一个与业务无关的自增字段作为主键。为什么呢?

  因为InnoDB使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)。

  如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:

                            mysql-高性能索引策略

 

  这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

  如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置:

                          mysql-高性能索引策略

  此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

  因此,只要可以,请尽量在InnoDB上采用自增字段做主键。

参考文献:

 [1] Baron Schwartz等 著,宁海元等 译 ;《高性能MySQL》(第3版); 电子工业出版社 ,2013

 [2] 张洋blog, http://blog.codinglabs.org/articles/theory-of-mysql-index.html

 [3]匿名blog, http://www.cnblogs.com/codeAB/p/6387148.html