数据库系列：MySQL慢查询分析和性能优化
数据库系列：MySQL索引优化总结（综合版）
数据库系列：高并发下的数据字段变更
数据库系列：覆盖索引和规避回表
数据库系列：数据库高可用及无损扩容
数据库系列：使用高区分度索引列提升性能
1 背景
有时候我们需要在字符类型的字段上建设索引，但是如果该字段的值都普遍比较大的话，会让索引变得大而且慢。
根据我们之前的了解，每个磁盘块（disk）存储的内容是有限的（InnoDB存储引擎中页的大小为16KB），如果一个页中可以存储的索引记录越多，那么查询效率就会提高，因为查找次数、查找深度会变少。
但是索引整个字符列会让我们索引内容特别大，会导致单个页存储的索引记录减少，就需要更多的页去存储，B+Tree的树深度变深，查询效率大幅降低。
比较有效的一种办法是指定索引的字段长度，比如可以索引开始的部分字符，这样可以大大节约索引空间（每个页），从而提高索引效率。
# enp_name 如果值普遍太长会导致索引空间爆棚
create index idx_emp_empname on emp(emp_name)
# 可能合适的做法
create index idx_emp_empname on emp(emp_name(5))
2 关于索引选择性
索引的选择性是指不重复的索引值（也称为基数，cardinality) 和 数据表的记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
以下图为例，字段emp_name的索引长度设置为4。如果不设置长度，一旦有大量长字符串元素，会占据大量的空间，而单个Disk的空间是有限的。
就会导致树的层级很高，搜索的IO次数变多，索引性能降低。
★ 说明：#T 指数据表的记录总数
★ 参考书籍《高性能MySQL》 的5.3.2小节的第3段 ：
一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
3 如何计算索引的选择性
3.1 分析
提供一个具有500w数据的雇员表emp，准备在雇员姓名 emp_name 字段在做索引。
emp_name有些值还是挺长的，如果全部拿来做索引，那势必导致索引表很庞大。我们先来检索下这个emp_name，看看有没有什么特征。
1 mysql> select distinct emp_name from emp;
2 +----------+
3 | emp_name |
4 +----------+
5 | ali_user |
6 | brandy_user |
7 | cancys_user |
8 | davide_user |
9 | elasne_user |
10 | finest_user |
11 | ......... |
12 | gagnannan_user |
13 | halande_user |
14 | ivil_user |
15 | kikol_user |
16 | Cecilia_user |
17 | Abigail_user |
18 | Blanche_user |
19 | Elizabeth_user |
20 | ....... |
21 +----------+
22 4999850 row in set
从上面的数据进行分析，可以得到以下结论：
- 索引长度肯定不能太短（比如2或者3），前6个字符的区分度非常高，如果选择2或者3，会扫出大量的数据，增加计算成本。
- 不能不设置索引的长度，后缀 _user 是一致的，没有任何的区分度优势，反而会占用很多无效的存储空间，增加 I/O次数。
- 目前看，最合适的可能是6或者7，前几个字符的区分度很高，这只是推断，需要用算法判定。
3.2 前缀索引的长度的判断方法
判断的算法：
select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;
可以套入emp表进行计算，如下：
SELECT
count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
FROM
emp
3.3 添加前缀索引
下面是添加带有索引长度的前缀索引语法：
ALTER TABLE t_name ADD KEY (c_name[(lenth)]);
测试emp_name最合适的长度，因为empname的长度基本维持在6个字符左右，少数量超过6长度，所以指定empname索引长度时6是最建议的
mysql> SELECT
count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
FROM
emp;
+--------+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 | sel7 |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set
我们可以使用 不同的长度来测试检索效率
当长度为2的时候，匹配度低于 0.0012，检索效率自然比较慢
mysql> create index idx_emp_empname on emp(emp_name(2));
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | emp_name | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN | 1 | 2021-01-23 16:46:03 | 2000 | 400 | 106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set (1.793 sec)
当长度为6的时候，匹配度低于 0.1713，检索效率就比较高
mysql> create index idx_emp_empname on emp(emp_name(6));
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id | empno | emp_name | job | mgr | hiredate | sal | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN | 1 | 2021-01-23 16:46:03 | 2000 | 400 | 106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set （0.003 sec）
4 总结
选择索引长度应该要选择足够长的前缀以保证较高的选择性，同时又不能太长，以便节约空间。
最好的要求就是前缀的基数（cardinality）接近于完整列的基数，以达到足够优秀的性能。