文章目录
前言
正确创建和使用索引是实现高性能查询的基础
一. 索引使用
- 一个索引使用一个B+Tree,索引让我们的查询可以快速定位和扫描我们需要的数据记录,加快查询速度
- 一个
select
查询语句在执行过程中一般最多使用一个二级索引,即使where
条件使用了多个二级索引
二. 高性能索引的创建
1. 索引列的类型尽量小
我们对表进行设计时,会涉及到显式的指定数据类型。
以整型为例,如tinyint,smallint,mediumint,int,bigint,它们占用的空间依次递增,能表示的数据类型也依次递增,若为某个整数类型字段创建索引,在表示整数范围允许的情况下,尽量让索引列使用较小的类型。
优先级选择如下:
tinyint (1) <- smallint(2) <- mediumint(3) <- int(4) <- bigint(8)
原理:
- 数据类型范围越小,代表索引占用的存储空间越小,从而查询时进行比较操作越快(CPU层面)。
- 数据类型的范围越小,一个数据页可放下更多的数据,从而减少磁盘IO的性能损耗。
- 数据类型范围越小,可以把更多的数据页缓存在内存中,从而加快读写效率。
这个策略对于主键来说更加适用。因为不仅是聚簇索引会存储主键值,其他所有的非聚簇索引的节点都会存储一份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. 多列索引
我们遇见常见创建索引的方式:
- 为每个单独的列创建索引
- 按照错误的顺序创建多列索引
正确顺序的多列索引依赖于使用该索引的查询条件,并同时需要考虑如何兼容排序或者分组。
在一个多列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存储结构:
所以是符合三星索引
- 指定了索引片,精确等值查询
- 排序字段是sex,精确查询的基础上,并且在组合索引中是索引自动排序好的
- 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存储结构
所以只符合二星索引:
- 第一颗星满足,虽然是like,但范围是确定的,加上sex精确等值。
- 第二颗星是不满足的,对于sex=1的情况下,city值,是按照四川,上海无序排序
- user_name符合覆盖索引