MySQL 高性能索引创建

前言

正确创建和使用索引是实现高性能查询的基础

一. 索引使用

1. 一个索引使用一个B+Tree，索引让我们的查询可以快速定位和扫描我们需要的数据记录，加快查询速度
2. 一个select查询语句在执行过程中一般最多使用一个二级索引，即使where条件使用了多个二级索引

二. 高性能索引的创建

1. 索引列的类型尽量小

我们对表进行设计时，会涉及到显式的指定数据类型。

以整型为例，如tinyint，smallint，mediumint，int，bigint，它们占用的空间依次递增，能表示的数据类型也依次递增，若为某个整数类型字段创建索引，在表示整数范围允许的情况下，尽量让索引列使用较小的类型。

优先级选择如下：

tinyint （1） <- smallint（2） <- mediumint（3） <- int（4） <- bigint（8）

原理：

1. 数据类型范围越小，代表索引占用的存储空间越小，从而查询时进行比较操作越快（CPU层面）。
2. 数据类型的范围越小，一个数据页可放下更多的数据，从而减少磁盘IO的性能损耗。
3. 数据类型范围越小，可以把更多的数据页缓存在内存中，从而加快读写效率。

这个策略对于主键来说更加适用。因为不仅是聚簇索引会存储主键值，其他所有的非聚簇索引的节点都会存储一份bookmark来记录主键值，如果主键使用更小的数据类型，那么会节省更多的存储空间和更高的IO。

2. 索引的选择性/离散性

创建索引需选择离散性高的列。

离散性：不重复索引值（也称基数，cardinality） 与 数据表总数（N）比值。

离散性的范围：[1/N, 1]

索引的离散性越高，查询效率越高，因为离散性高的索引可以让MySQL在查询时过滤更多的行。就比如唯一索引的离散性就是1，这是最好的索引离散性，性能也是最好的。

最差的情况时索引离散性时列中数据重复度很高。如状态类型，性别等，若为这些创建索引，那么出现重复的概率是很大的。

计算索引的离散性

select count(distinct a)/count(*) from test_table

3. 前缀/后缀索引

• 前缀索引：

针对blob，text，很长的varchar字段，mysql不支持索引它们的全部长度，需建立前缀索引。

-- 表：test_table
-- idx_a：索引名称
-- a(x)：a字段取x长度作为索引
alter table test_table add index idx_a (a(x));

缺陷：前缀索引是一种使索引更小，更快的一种方式，但MySQL无法使用前缀索引做order by和group by，也无法使用前缀索引作为覆盖索引。

那前缀索引的数值，如何科学的选取呢？

-- 进行左前缀取数，多个值之间进行离散性比较
-- 选取的数值结果越趋近于1的，可选择该数值作为前缀的数值
-- 越趋近1，选择度越高
select count(distinct left(a,1))/count(*) as n1,\
count(distinct left(a,2))/count(*) as n2,
count(distinct left(![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/83cc24fe90f54bc4bbfefe7eb9e1ad8d.png)
a,3))/count(*) as n3,
count(distinct left(a,4))/count(*) as n4
from test_table;

• 后缀索引：MySQL不支持反向索引。但可以把需要的后缀字符串反转后存储。并基于此建立前缀索引。也可以单独创建个字段列，存储后缀值（如邮箱尾缀）

4. 只为用于搜索，排序或分组的列创建索引

如

select * from t1,t2 where t1.a = t2.z

create index on t1 (a);
create index on t2 (b);

select * from text_table order by a,b,c

create index on test_table (a,b,c);

select * from text_table gourp by e,d,

create index on test_table (e,d,f);

5. 多列索引

我们遇见常见创建索引的方式：

1. 为每个单独的列创建索引
2. 按照错误的顺序创建多列索引

正确顺序的多列索引依赖于使用该索引的查询条件，并同时需要考虑如何兼容排序或者分组。

在一个多列B+Tree的索引中，索引列的顺序代表索引首先按照最左列进行排序，其次是第二列，等等。所以索引可以按升序或者降序进行扫描，以满足精确复合列顺序的order by ，group by和 distinct等子句的查询需求。

如何选择多列索引的法则：当不需要考虑排序和分组时，将离散性最高的列顺序的列放到最前列，剩下的列依次类推。这样设计的索引能最快的过滤出需要的行，对于查询条件只使用了钱最列的查询来说选择性也更高。

6. 三星索引

三星索引满足条件：

• ⭐️ 索引将相关的记录放到一起（范围星，比重27%）
• ⭐️ 索引中的数据顺序和查询排列顺序一致（排序星，23%）
• ⭐️ 索引中的列包含了查询中需要的全部列（宽索引星，50%）

第三颗星是最重要的，这涉及若不是覆盖索引，会带来更多的磁盘随机读，也就是回表操作。第一颗星和第二颗星重要性差不多，在大部分情况下，会优先考虑第一颗星，但也会根据业务情况调整优先度。

一星：如果一个查询相关的索引行时相邻的或者至少相距足够靠近的话，扫描的索引片宽度就会缩至最短。换句话说，让索引片尽量变窄，也就是我们所说的索引扫描范围越小越好。

二星：当查询需要排序或分组。那么查询所需的顺序与索引需要保持一致（索引本身也是有序的），一般来说排序是影响性能的关键。

三星：索引中包含查询所需的所有列（包含where查询条件和select中所需的列，也就是覆盖索引），这样查询不需要回表，减少了查询步骤和IO请求次数，性能几乎可以提高一倍。

7. 三星索引练习

7.1 网页工具&SQL准备

Data Sttructure Visualization（usfca.edu）

SQL

-- 删表
DROP TABLE test_table;
-- 创建表
CREATE TABLE test_table ( 
	id INT PRIMARY KEY AUTO_INCREMENT, 
	user_name CHAR (10), 
	nick_name VARCHAR (10), 
	sex TINYINT (2), 
	city VARCHAR (100) 
);

-- 数据
INSERT INTO `test`.`test_table` (`id`, `user_name`, `nick_name`, `sex`, `city`) VALUES (1, '张', '老大', 1, '四川');
INSERT INTO `test`.`test_table` (`id`, `user_name`, `nick_name`, `sex`, `city`) VALUES (2, '李二', '老二', 2, '浙江');
INSERT INTO `test`.`test_table` (`id`, `user_name`, `nick_name`, `sex`, `city`) VALUES (3, '张小猪', '老三', 1, '上海');
INSERT INTO `test`.`test_table` (`id`, `user_name`, `nick_name`, `sex`, `city`) VALUES (4, '李四', '老四', 2, '四川');
INSERT INTO `test`.`test_table` (`id`, `user_name`, `nick_name`, `sex`, `city`) VALUES (5, '张', '老大', 2, '四川');
INSERT INTO `test`.`test_table` (`id`, `user_name`, `nick_name`, `sex`, `city`) VALUES (6, '王六', '老六', 1, '上海');

7.2 示例一

索引创建

-- 创建索引
create index idx_index on test_table (city, user_name, sex)

该语句是否符合三星索引：

select city,user_name, sex from test_table where city = '四川' and user_name = '张' order by sex;

执行计划：

走了索引，没有回表

B+TREE存储结构：

所以是符合三星索引

1. 指定了索引片，精确等值查询
2. 排序字段是sex，精确查询的基础上，并且在组合索引中是索引自动排序好的
3. city，user_name，sex符合覆盖索引

7.3 示例二

创建索引

-- 删掉示例一中的索引
drop index idx_index on test_table;

-- 创建索引
create index idx_index on test_table (user_name, sex, city)

该语句是否符合三星索引：

select city,user_name, sex from test_table where user_name like '张%' and sex = 1 order by city

执行计划

B+Tree存储结构

所以只符合二星索引：

1. 第一颗星满足，虽然是like，但范围是确定的，加上sex精确等值。
2. 第二颗星是不满足的，对于sex=1的情况下，city值，是按照四川，上海无序排序
3. user_name符合覆盖索引