SQL优化 - 索引
索引
分类
- 单值索引:单列;一张表可以有多个索引
- 唯一索引:被标记的列数据不能重复
- 复合索引:多个列构成的索引
- 主键索引:特殊的唯一索引,不能为null;
创建索引
方式一:create 索引类型 索引名 on 表(字段)
- 单值:create index index_age on user(age);
- 唯一:create unique index index_name on user(name);
- 复合:create index index_name_age on user(name, age);
方式二:alert table 表名 索引类型 索引名(字段)
- 单值:alert table user add index index_age(age);
- 唯一:alert table user add unique index index_name(name);
- 复合:alert table user add index index_name_age(name, age);
查询、删除索引
查询索引:show index from 表名;
eg:show index from user;
删除索引:drop index 索引名 on 表名;
eg:drop index index_age on user;
SQL 问题分析
分析SQL 执行计划:
使用方式:explain sql语句;
eg:explain select * from t_sys_account ;
±—±------------±--------------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±--------------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
| 1 | SIMPLE | t_sys_account | NULL | ALL | NULL | NULL | NULL | NULL | 230 | 100.00 | NULL |
±—±------------±--------------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+
解析字段 | 解析字段名 | 备注 |
---|---|---|
id | 编号 | 值相同:1、从上到下,顺序执行 2、多表查询会因为表的数据改变而改变:取表的数据少的优先计算 ;值不同(一般有子查询):值越大优先执行 |
select_type | 查询类型 | primary:SQL 中的最外层(子查询最外层)查询;subquery:包含 子查询中的子查询;simple:简单查询(不包含子查询和union查询);derived/union/union result:衍生查询(查询的时候用到了临时表:1、from子查询单张表,这单表就是衍生查询 2、from 子查询多张表,如 子查询是 table1 union table2,则 table1 就是衍生表,table2 是union表),union result 就是 derived 和 union 表的结果集; |
table | 表名 | 表名;可能出现 derived,union 字样;就是 衍生表加上Id 组成的临时表 |
partitions | ||
type | 类型 | system > const > eq_ref > ref > range > index > all ;system:1、只有一条数据的系统表或衍生表只有一条数据的主查询,是理想状态的优化;const :只能查询出一条数据,必须用于primary key 和 unique key 索引标识的列;eq_ref:唯一性索引,对于索引的查询,返回匹配的唯一一行数据(查询的结果和数据的条数一致); ref:非唯一性索引,对于索引的查询,返回匹配的所有行数据;range:范围查询,一般指where 后面的条件是范围(between,<,>) |
possible_keys | 预测用到的索引名 | |
key | 实际使用的索引 | |
key_len | 实际使用的索引长度 | |
ref | 表之间的引用 | 常见的有两个:const(常量);使用到另一张表的另一个字段 |
rows | 第二列文本居右 | 被索引优化后的查询的数据条数; |
filtered | 通过索引查询到的数据量 | |
Extra | 额外信息 | 提示信息:use filesort:性能消耗大,需要一次额外的查询、排序,一般出现在order by 中;use temporary:性能消耗大,使用到了临时表,一般出现在group by 中;use index :性能提升(索引覆盖),出现此提示没有读取数据文件,只读取索引文件;use where:需要在数据文件中查询;impossible where:where 子句永远是false,情况少见 |
小结:
单索引:如果查找和排序是同一个字段则不会出现user filesort;否则就会出现
复合索引:不能跨列,可最佳左前缀;
use filesort:where和order by联用的时候,必须按照复合索引的顺序,不要跨列或无序使用
use temporary:where和order by联用的时候,不要跨列使用
案例:
create table test03(
a1 int(4) not null,
a2 int(4) not null,
a3 int(4) not null,
a4 int(4) not null
)
alter table test03 add index index_a1_a2_a3_a4(a1,a2,a3,a4);
-- 正常的
-- (1)推荐写法:索引的顺序和符合索引的位置一致
explain select a1,a2,a3,a4 from test03 where a1 = 1 and a2 =2 and a3 = 3 and
a4 = 4;
-- 通过下图(1)中 key_length 的长度(一个字段4个长度),发现SQL 使用了全部的复合索引
-- (2)换个写法:这里MySQL 查询优化器做了处理
explain select a1,a2,a3,a4 from test03 where a4 = 1 and a3 =2 and a2 = 3 and
a1 = 4;
-- (3)继续:加入order by
explain select a1,a2,a3,a4 from test03 where a1 = 1 and a2 = 2 and a4 = 3 ORDER BY a3;
-- 通过下图(3)中 extra 中 using index 表示部分通过索引直接查询,因为 SQL中 a1 和 a2 符合复合索引的位置,所以可以通过索引查询,索引长度 key_length 是 4;a4 跨了索引列,所以索引失效,需要通过表数据查询,此处通过 using where表示
-- (4)
explain select a1,a2,a3,a4 from test03 where a1 = 1 and a4 = 3 ORDER BY a3;
-- 通过下图(4)中 extra 中 using filesort,此处性能消耗比较大
-- 介于 (3)和(4)发现,为啥少了个 and a2 = 2,就多出 using filesort(文件内排序,查询多了一次):因为 跨列判断主要是通过 where 和 order by 拼接符合复合索引的前缀匹配。这样看的话,(3)中的SQL :a1->a2->a4->a3,这里 a4 跨列 失效,去掉不看则为 a1->a2->a3,还是符合复合索引的位置;而(4)中的SQL: a1->a4->a3,这里 a3 和 a4 都跨列,所以出现usign filesort;
-- (5)优化(4)的SQL
explain select a1,a2,a3,a4 from test03 where a1 = 1 and a4 = 3 ORDER BY a2, a3;
(1)的图片
(2)的图片
(3)的图片
(4)的图片
(5)的图片
若复合索引 和使用的顺序全部一致,则 复合索引 全部使用;若部分使用,则使用 部分索引
MySQL 查询优化器干扰
……没研究好,暂时不写