0. 目的
- 了解索引内部实现原理,进而知道如何设计索引
- 针对具体的 case 进行慢 SQL 优化讲解,涉及到每一个优化项的讲解,比如 expain 的关键指标
1. 索引结构
1.1 B+ 树结构
为了故事发展,我们先简单创建一个表如下。
mysql> CREATE TABLE index_demo( -> c1 INT, -> c2 INT, -> c3 CHAR(1), -> PRIMARY KEY(c1) -> ) ROW_FORMAT = Compact; Query OK, 0 rows affected (0.03 sec)
- 多叉树 + 叶子节点存储数据 + 双向链表
- 非叶子节点存储的是主键(索引) + 页指针
- 只有叶子节点存储数据,数据是按照主键(索引)从小到大一次排列。
- 目录页 和 数据页 结构一样(复用), 不同的是
-
- 目录页的 record_type = 1,目录页的列值中存放的是索引+页号。
- 数据页的 record_type = 0,数据页中列值中存放具体列信息。
1.2 页结构
- 页结构主要想说明每一个页里面的数据内容,双向箭头为了更清晰的解释他们的连接方式。
- File Header,记录上一页和下一页的编号,一次组成了双向链表,从而实现 asc 和 desc 的索引效果。
- Page Directory, 页中记录相对位置,用于快速定位元素在页中的具体位置。下面的图会详细解释。
- User Records, 就是真实的记录部分,单链表结构,便于快速追加元素。
1.3 页记录结构
- 左边是 Page Directory 部分 ,通过数组结构,定位到页以后,可以通过二分查找,快速定位槽的位置,里面记录的是记录部分的偏移量。
- 右边是 User Records 部分,使用单项链表串起来,目的是可以实现快速的插入和追加元素。
1.4 记录结构
- 记录结构就是每一个 User Record 记录的结构,左边是额外信息,右边是每一列的信息。
- 变长字段列表,varchar、text 等类型需要提前记录一下具体的长度,所以有了可变长度这个类型,可变长度一般是1-2个字节,所以变长字段的占用的字节和是 变长列 * 2
- null值列表,为了节省空间,如果列值为空,会统一存在 null 值列表,null 值列表使用一个字节=8bit 来存储,通过 b 的 0/1 表示当前列是否为null,不够一个字节自动补全,所以null 值列表占用的字节数是,一行记录 null 和数量 / 8 向上取整。具体点如果 7 个列是 null,那么一个字节 8 位就可以,如果 9 个列是 null,就需要 16 个位,两个字节。
- 记录头信息,固定的5个字节组成。5个字节也就是 40 个二进制位,用于标记当前记录的类型,比如上文说的当前类型是叶子节点还是非叶子节点。
1.5 记录头结构
- 头信息是非常重要的内容,他用来定义记录的属性,数量和指针等除了基本列值以外的内容,同样也区分当前的类型是页索引,还是页数据,第一节有说 record_type。
- delete_mask 表示这个记录是否被删除,因为磁盘的整理也需要时间和性能的消耗,所以删除的内容不会马上释放空间,而是由MySQL 统一维护了一个垃圾链表,这时候如果有新的数据插入进来会替换原来的位置,这样就可以磁盘重用了。
1.6. 总结
上面说了这么多数据结构,我们可以做什么呢?或者说了解了有什么帮助呢?
细节一点说,我们了解了记录结构的存储方式,就可以知道每一个数据结构占用的数据字节不同,应该以最小力度设计,对照表如下。
点击查看 https://www.runoob.com/mysql/mysql-data-types.html
具体点
1byte = 8B = -127~+127 = 0~255
所以如果类型在 127 范围内,就可以选择使用 tinyint,假如你设计了一个枚举类型,原本你可以存储 int 到数据库,你却直接把 String 存储到了数据库 (varchar 10)。
我们假设平均长度是 5 的枚举,那么每一条数据占用的字节数是 5(实际占用) + 2(变长列表) = 7 ,每一条多占用的字节数就是 7 -1 = 6,如果 100W 调数据,多占用的字节就是
10000000 * 6 / 1024 / 1024 = 60M
这仅仅是一列的占用,那么 10 列呢?10 个表呢?
10 * 10 * 60 = 6G 如果 10 个表,每个表有 10 列没有注意字符的大小最小力度,那么就多浪费了 6G 的存储。
2. 插入过程
故事继续发展,为了好理解,我又把原来的表放到这里了。
mysql> CREATE TABLE index_demo( -> c1 INT, -> c2 INT, -> c3 CHAR(1), -> PRIMARY KEY(c1) -> ) ROW_FORMAT = Compact; Query OK, 0 rows affected (0.03 sec)
假设:一个页只能存 3 个 UserRecord,一般一个页能存储 200 左右的 UserRecord。
- 我们先插入 c1 为 1、3、5 三个数据,这时会生成一个页
- 再插入 4 看效果。
2.1 页分裂
总结:
a.如果我们创建的索引不是递增,就会频繁出现页分裂(上面这种需要先裂变成两个页,再插入数据,如果是N个页连在一起,是不是数据的移动会非常多?),从而导致性能下降。这也是为什么不推荐使用 UUID 作为主键而是雪花算法 和 TDDL 的 senquence 递增的分布式主键方案。
3. 查询过程
3.1 聚簇索引查询过程
为什么叫聚簇索引?主键索引不好听吗?因为主键索引都是叶子节点存放数据,看起来像是聚簇在一起的鲜花,所以形象的称之为聚簇索引。
故事继续发展,为了好理解,我又把原来的表放到这里了。
mysql> CREATE TABLE index_demo( -> c1 INT, -> c2 INT, -> c3 CHAR(1), -> PRIMARY KEY(c1) -> ) ROW_FORMAT = Compact; Query OK, 0 rows affected (0.03 sec)
具体实例:通过查询 C1 = 8 推演。
解释每个页里面数据对应的都是什么
- 通过根节点找到目录页(B+树的根节点会存在内存里)
- 通过目录页定位到数据页
- 在真实存储用户记录的页中定位到具体的记录。
-
- 通过二分法查找槽[Page Directory],快速定位槽
- 通过遍历一次连接的单项链表[User Records]快速定位元素位置
- 返回
3.2 二级索引查询过程
故事继续发展,为了好理解,我又把原来的表放到这里了。不过这次有一些变化,我们创建了 c2 为二级索引。
mysql> CREATE TABLE index_demo( -> c1 INT, -> c2 INT, -> c3 CHAR(1), -> PRIMARY KEY(c1) -> ) ROW_FORMAT = Compact; ALTER TABLE index_demo ADD INDEX idx_c2(c2);
具体实例:通过查询 C2 = 7 推演。1(查询) + 2(回表)
- 因为二级索引叶子节点只存储主键ID,就是一级索引 C1 的内容,所以结果如下
- 确定目录项记录页
- 通过目录项记录页确定用户记录真实所在的页。
- 在真实存储用户记录的页中定位到具体的记录。
-
- 通过二分法查找槽,快速定位槽
- 通过遍历一次连接的单项链表快速定位二级索引和聚簇索引的内容
总结:
a. 通过二级索引我们可以发现,如果是需要一个索引和主键配合使用的话,不需要显性的创建主键索引,因为他会自动写进去。所以得到一个结论
如果主键是 id,如果你想创建 user_id + id 这样一个联合索引,那么只需要创建 user_id 即可
3.3 联合索引查询过程
故事继续发展,为了好理解,我又把原来的表放到这里了。不过这次有一些变化,我们创建了 c2,c3 为联合索引。
mysql> CREATE TABLE index_demo( -> c1 INT, -> c2 INT, -> c3 CHAR(1), -> PRIMARY KEY(c1) -> ) ROW_FORMAT = Compact; ALTER TABLE index_demo ADD INDEX idx_c2_c3(c2,c3);
首先理解联合索引的意思,比如 c2 和 c3 是联合索引,上面蓝色部分是c2,红色部分是 c3,橙色部分是主键
- 先把所有记录按照 c2 列进行排序。
- 在 c2 记录相同的情况下,采用 c3 列进行排序。
- 比如上图的页 50 和 55 里面的三个4,下面红色部分的顺序分别是 aou
心得:
a. 因为索引是先通过 c2 排序,然后通过 c3 排序,根据左匹配原则,这个联合索引等于如下多个索
① c2
② c2 + c3
③ c2 + c3 + c1
b. 思考题:思考用不到索引的场景,查询条件只有 c3 是否可以使用索引?思考10秒钟
思考完了,我们开始新的故事,创建一个 person_info 表,里面有一个联合索引。
CREATE TABLE person_info( id INT NOT NULL auto_increment, name VARCHAR(100) NOT NULL, birthday DATE NOT NULL, phone_number CHAR(11) NOT NULL, country varchar(100) NOT NULL, PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name, birthday, phone_number) );
创建完成以后结构如下
3.3.1 思考题
故事就是 12 个思考题,分析下面的 SQL 哪一条可以使用索引。
1. SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239'; 2. SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27'; 3. SELECT * FROM person_info WHERE name = 'Ashburn'; 4. SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow'; 5. SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000'; 6. SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01'; 7. SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10; 8. SELECT * FROM person_info ORDER BY name asc, birthday DESC LIMIT 10; 9. SELECT * FROM person_info WHERE birthday = '1989-09-12' ORDER BY name, phone_number LIMIT 10; 10. SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number 11. SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10; 12. SELECT * FROM person_info where name = 'A' and id = 1 ;
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
3.3.2 答案揭晓
- 1、2、3、4、7 、10 全部命中
- 5 命中 name 和 birthday
- name = 'Ashburn',对name列进行精确查找,当然可以使用B+树索引了。
- birthday > '1980-01-01' AND birthday < '2000-12-31',由于name列是精确查找,所以通过name = 'Ashburn'条件查找后得到的结果的name值都是相同的,它们会再按照birthday的值进行排序。所以此时对birthday列进行范围查找是可以用到B+树索引的。
- phone_number > '15100000000',通过birthday的范围查找的记录的birthday的值可能不同,所以这个条件无法再利用B+树索引了,只能遍历上一步查询得到的记录。
- 6 命中 name,name 是区间的, birthday 是跳跃的,所以只能用到 name 索引,扫描列也就是所有匹配 name 的列数。
- 8 使用联合索引排序必须是相同方向的,要么都 asc 要么都 desc,类似这种索引肯定是不工作的
- 9 未命中任何索引全表扫描,因为和 7 不同,order by 必须按照顺序来
- 10 分组也是和排序是一个道理,只要是从前往后就可以依次命中索引,下面是简述分组的过程。
- 先把记录按照name值进行分组,所有 name 值相同的记录划分为一组。
- 将每个 name 值相同的分组里的记录再按照 birthday 的值进行分组,将birthday值相同的记录放到一个小分组里,所以看起来就像在一个大分组里又化分了好多小分组。
- 再将上一步中产生的小分组按照phone_number的值分成更小的分组,所以整体上看起来就像是先把记录分成一个大分组,然后把大分组分成若干个小分组,然后把若干个小分组再细分成更多的小小分组。
- 11 不能使用索引,索引必须是原字段,使用任何公式以后都不支持索引。
- 12 究竟是否可以用索引呢?留一个疑问我们下面说,卖个官司。
3.4 连表
集团非常不推荐连表,尤其是分库分表场景,所以我们这里只做概念讲解。
连表其实就是笛卡尔积,为了故事发展,我们还是创建两个表,插入 6 条数据。
mysql> SELECT * FROM t1; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t2; +------+------+ | m2 | n2 | +------+------+ | 2 | b | | 3 | c | | 4 | d | +------+------+ 3 rows in set (0.00 sec)
用实例推导
SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
心得:
a. 所以我们在做连表查询的时候,左边的叫做驱动表,右边的叫做被驱动表,选取代价最低的单表访问方法来执行对驱动表的单表查询,这样得到的笛卡尔积结果集就会小。
为什么驱动表选择很重要,并且阿里不推荐呢?你可以简单算一下 两个 1W 的表连表就是 1亿 条,何等可怕,稍微处理不好就……
3.5 总结与扩展
讲了这么多?那么有没有点扩展的内容?那么就是为什么要分库分表?很多时候我们只知道数据量大了要分表,那么如何评价数据量大,大到什么程度算是大?这就是我们下面要讨论的问题。
通过 记录结构,我们知道的如何计算每一条记录占用的空间,数据本身、预留字段和各种头,由此可以评估出来我们的 table 设计可以估算出来不同数据量下面的磁盘占用。
通过 页结构 + 索引查询过程,我们知道了聚簇索引和二级索引的查找过程,二级索引的回表机制,同时知道了聚簇索引和二级索引 叶子节点存储 的内容。聚簇索引叶子节点存储的是数据,二级索引叶子存储的是二级索引 + 聚簇索引
为了继续讲故事,再来一个表。
create table tbl(id int primary key, c1 int, index(c1), c2 varchar(128))
因为每页默认 16KB,所以具体的推算内容如下。
聚簇索引长度:4字节;
非聚集索引 c1 长度: 4字节;
二级索引长度: 8 字节 (4+4)
指针长度 8 字节
推导:
- 聚簇索引非叶子节点: 聚簇索引+指针=12 字节
- 聚簇索引叶子节点: 数据+主键+指针 128 + 4 + 4 + 8 = 144 字节
- 二级索引非叶子节点: 二级索引+指针=12 字节
- 二级索引叶子节点:二级索引+指针+聚簇索引 = 16 字节
一般每页的使用最大比例 70%。
则:
每个 Page 非叶子节点平均行数:16384 * 70% / 12 ≈ 1000
聚簇索引叶子节点平均行数:16384 * 70% / 144 ≈ 80
二级索引叶子节点平均行数:16384 * 70% / 16 ≈ 700
则聚集索引的高度和行数关系粗略为:
索引高度 | 聚集索引最大记录数 | 非聚集索引最大记录数 |
2 | 1000*80 = 8W | 1000*700 = 70w |
3 | (1000)^2*80 = 8000w | (1000)^2*700 = 7亿 |
总结:
a.索引的高度控制在 3 以内(含3)最合适,每多增加一层,对应的就是一次 IO 读取,随机 IO 读取。
b.索引列的总长度越长,索引的高度可能越大。SQL的性能就越差,所以未必是所以创建的越全性能越好。
c.使用二级索引的时候如果不需要除了索引以外的字段,直接不要写 * 这样可以提高很多性能,主要考虑回表的问题,具体分析参考下面的例子。
4. 优化关键字解释
mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.01 sec)
列名 | 描述 |
id | 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
select_type | SELECT关键字对应的那个查询的类型 |
table | 表名 |
partitions | 匹配的分区信息 |
type | 针对单表的访问方法 |
possible_keys | 可能用到的索引 |
key | 实际上使用的索引 |
key_len | 实际使用到的索引长度 |
ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
rows | 预估的需要读取的记录条数 |
filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
Extra | 一些额外的信息 |
4.1 关键字 type
简单点说,就是当前 SQL 的是通过哪种类型执行的
- const 命中索引,常数级,通常可以忽略的情况,只有聚簇索引和 uniq 的二级索引才是这种方式
- ref 是等值匹配的意思,比如二级索引有多个相等的值,二级索引对于null 的数量不会限制,所以如果 sql 里面有 is null 等预计,也是 ref 的形式,性能相比 const 是差很多的。
- ref_or_null,两个内容,使用 or 相连的情况,如果都是一个索引那么会去分别查询索引最后合并。
- range ,比如 a > 3 and a < 8 这就是一个 range 查询,利用索引进行区间访问的情况,称之为 range
- index,通过遍历二级索引查询的方式称为 index,简单点说就是扫描所有索引。我们平时看到的 sql,type index 是命中索引了,但是是扫面了所有索引,所以还是有很大的优化空间。
- all 就是全表扫描
- index_merge,Intersection,索引合并,假设下面的的 sql,key1 和 key3 分别的索引,那么 MySQL 的设计也不会让你只命中一个索引,而是分别命中,取交集,然后回表。只有全部二级索引是等值查询,并且没有区间搜索的情况下菜可以进行索引合并,或者主键索引的区间搜索和二级索引的等值查询。所以合并可以解决问题,但是还是推荐使用联合索引,因为联合索引查询次数还是远远小于索引合并的。解答了上面的问题。
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
- Nested-Loop Join,从 3.4 我们了解到驱动表遍历一次,被驱动表就是 N 次,像是一个嵌套的循环,所以这种方式命中的查询名字就叫做 Nested-Loop Join。遇到这种情况,就说明关联查询出问题了。
- eq_ref,关联表中如果被驱动表恰好使用了聚簇索引或者唯一的二级索引,那么就可以使用 const 的命中索引了,这种情况再关联表中称之为 eq_ref,如果非要使用关联查询,需要优化到这个程度。
- Block Nested-Loop Join,扫描被驱动表太大了,每次需要加载到内存里面,但是放不下,所以需要先加载一部分,匹配完成以后再释放内存,然后再加载一部新的,这种性能太差,于是 MySQL 引入了一个 join buffer 的概念,把所有的结果集都放在一个内存块里面,这样节省了很大的性能开销。
4.2 关键字 possible_key & key
这个就非常简单了possible_key 就是可能的 key,key 就是最终使用的key
4.3 关键字 rows & filtered
rows 表示扫描的行数,比如如下查询语句,key1是索引,通过 key1 扫描了 266 行,然后 common_field = 'a' 的只有 26 行,所以 filtered 是 10%,所以我们做优化的时候 filtered 也是一个非常重要的指标。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 266 | 10.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec)
Using index condition; Using where 是什么?就要说到 Extra 关键字了。
4.4 关键字 Extra
- using index,直接使用覆盖索引内容进行检索,覆盖索引不需要回表,性能非常好。这就是判断是否需要回表的依据。
- using index condition 在命中索引的情况下,不需要回表的判断逻辑,虽然有性能的提升,还是仍然有很大提升空间。具体例子可以参考下面的 SQL
SELECT * FROM s1 WHERE key > 'z' AND key LIKE '%a';
- using where ,性能非常差,命中索引但是有非索引的搜索条件或者全表扫描,解释了上面的问题,出现 using where 是必须要优化的 SQL。
- Using join buffer (Block Nested Loop),关联查询没有命中索引,使用内存块排序,这种情况一般是有可优化空间的,优化的目标就是优化到 type = eq_ref
Using where; Using join buffer (Block Nested Loop)
比如我们常见的这种情况,表示既使用的 join buffer 排序,还有没有命中索引的需要逐一过滤
- Using intersect,说明使用了索引合并,这时候key一定会是两个值,优化方向是使用联合索引,减少索引的查询次数。
- Using filesort,没有命中索引的排序就会使用这种方式,会在内存中进行排序,俗称文件排序,然后limit,非常消耗性能,优化目标是 using index。通常使用 order by 和 group by 却没有命中索引会出现这个情况。
- Using temporary,比如sum,max,count 等需要借助临时表来操作,这个情况算是最糟糕的了,我们优化的目标也是 using index。
5. 优化案例
5.1 索引选择
开始索引正式优化案例之前再做两个思考题,思考下面两个 SQL 分别如何执行,是否会命中索引
先看一下建表语句,还是最初我们分析联合索引的表。
CREATE TABLE person_info( id INT NOT NULL auto_increment, name VARCHAR(100) NOT NULL, birthday DATE NOT NULL, phone_number CHAR(11) NOT NULL, country varchar(100) NOT NULL, PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name, birthday, phone_number) );
SELECT * FROM person_info ORDER BY name, birthday, phone_number;
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
我是分隔符,不让看答案
答案:
- 聚簇索引(顺序IO) + filesort,第一个不会走索引,回表代价太大
- 联合索引(顺序IO) + 回表(随机IO),走索引,回表代价小
5.2 案例 1
通过索引查看全部命中了,但是还是没有选中索引,是因为回表的问题,MySQL 在优化索引的时候,会整体计算扫描次数,同时包括回表的次数,因为这个是二级索引,所以如果使用第一个索引需要查询完成以后进行一次回表,而第二个索引不需要回表反而次数会扫描的少,修改方案
增加新的索引,store_id,quantity,sc_item_id,因为 biz_code 区分度非常低,所以可以忽略
5.3 案例 2
创建索引需要充分考虑列的基数,比如当前列是 a ,只有 3 个a,区分度很低即便是命中了索引排序意义也不大。
解决方案
- 使用 force index 指定索引
- 修改 index ,调整 a 和 b 的位置 或者索引直接是 a + c
主要原因:a 比 b 的区分度高,所以优先选择 a,说到区分度还是最终回到了索引的选择?那么索引选择背后 MySQL 究竟做了什么呢?他怎么知道我的数据应该怎么选择索引呢?
5.4 MySQL 索引的选择
很多时候一个 SQL 会有很多 possible_key,这时候有需要用到了索引优化,MySQL 的索引选择方式非常多,这也是 MySQL 设计者绞尽脑汁去做的,我就选一个最简单的地方讲下。
mysql> SHOW TABLE STATUS LIKE 'single_table'\G *************************** 1. row *************************** Name: single_table Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 9693 Avg_row_length: 163 Data_length: 1589248 Max_data_length: 0 Index_length: 2752512 Data_free: 4194304 Auto_increment: 10001 Create_time: 2018-12-10 13:37:23 Update_time: 2018-12-10 13:38:03 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec)
虽然出现了很多统计选项,但我们目前只关心两个:
Rows ,表示当前表一共有多少条记录。
Data_length,当前表占用的字节数。所以我们这里可以做一个推断:
聚簇索引的页面数量 = 1589248 / 16 / 1024 = 97 (页)
mysql> SHOW INDEX FROM single_table; +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | single_table | 0 | PRIMARY | 1 | id | A | 9693 | NULL | NULL | | BTREE | | | | single_table | 0 | idx_key2 | 1 | key2 | A | 9693 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key1 | 1 | key1 | A | 968 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key3 | 1 | key3 | A | 799 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key_part | 1 | key_part1 | A | 9673 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key_part | 2 | key_part2 | A | 9999 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key_part | 3 | key_part3 | A | 10000 | NULL | NULL | YES | BTREE | | | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 7 rows in set (0.01 sec)
虽然出现了很多统计选项,我们选一个重要的看下
Cardinality ,以 idx_key1 为例,它的Rows值是9693,它对应索引列key1的Cardinality值是968,所以我们可以计算key1列平均单个值的重复次数就是:
9693 ÷ 968 ≈ 10(条)
这就是上文中的案例2 MySQL 如何知道区分度来选中索引的
最终得出的结论:
MySQL 在选择索引的时候会根据表的数据、索引的数据配合 IO 的读取次数,估算一个最优的索引,这也是为什么有的时候 MySQL 索引自己会选错的原因。