单表优化及总结

索引

分类

• 单值索引：单列；一张表可以有多个索引
• 唯一索引：被标记的列数据不能重复
• 复合索引：多个列构成的索引
• 主键索引：特殊的唯一索引，不能为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 查询优化器干扰

……没研究好，暂时不写