mysql高性能索引
独立索引:
独立索引是指索引列不能是表达式的一部分,也不能是函数的参数
例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)是不一样的,看上面的图也看得出来关系顺序是不一样的;
分析几个实际例子来加强理解
(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也没有发挥索引效果
对于如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。(参考①)
当不需要考虑排序和分组时,将选择性最高的列放到前面通常是很好的。这时候索引的作用只是用于优化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],选择性越高的索引值价值越大
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
- +-------------+
- | Selectivity |
- +-------------+
- | 0.0379 |
- +-------------+
比如employees表只有一个索引<emp_no>,那么如果我们想按名字搜索一个人,就只能全表扫描了:
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 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
这样全表扫描效率很低,所以我们考虑到把名字建立索引,有两种选择,建<first_name>或<first_name,last_name>,看下两个索引的选择性:
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 | +-------------+
从结果看显然<first_name>选择性太低,<first_name,last_name>选择性好。但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法?可以考虑用first_name和last_name的前几个字符建立索引,例如<first_name, left(last_name, 3)>,看看其选择性:
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees; +-------------+ | Selectivity | +-------------+ | 0.7879 | +-------------+
选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees; +-------------+ | Selectivity | +-------------+ | 0.9007 | +-------------+
这时选择性已经很理想了,而这个索引的长度只有18,比<first_name, last_name>短了接近一半,我们把这个前缀索引 建上:
ALTER TABLE employees ADD INDEX `first_name_last_name4` (first_name, last_name(4));
此时再执行一遍按名字查询,比较分析一下与建索引前的结果:
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' | +----------+------------+---------------------------------------------------------------------------------+
性能的提升是显著的,查询速度提高了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不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此,只要可以,请尽量在InnoDB上采用自增字段做主键。