1. 为什么要了解MySQL执行计划
我们经常需要使用数据库进行增删改查操作,当数据库表的数据量比较大的时候,每次操作都会消耗大量的时间,当处于高并发的环境时,就肯容易出现延迟现象。
2. 单表sql语句
为了更加详细的了解我们编写的sql语句的执行情况,我们可以通过添加explain关键字进行查看
EXPLAIN SELECT * FROM `subject` WHERE id = 453639012033536000
在编写sql语句的时候最好不要使用 * 号,写上具体的字段名可以提高运行效率。
这是一个简单查询语句,通过图中type字段可以看出这条语句性能极好,type的类型排序:system > constan > eq_ref > ref >range > index>All。key字段表示具体使用的索引,通过索引可以极大的提升查询的效率,可以看出这条语句使用的是主键索引。
EXPLAIN SELECT * FROM `subject` WHERE subject = '编程题93'
这是另一条未使用索引的普通查询语句,key字段为null,它的type字段为all,即进行了全表扫描,这是效率最低的方式。在进行查询的时候要尽可能避免出现全表扫描,尽量使用索引查询。
3. 多表sql语句
EXPLAIN SELECT * FROM `t_paper_subject_answer` a
LEFT JOIN t_paper_subject b on a.subject_id= b.id
LEFT JOIN t_paper c on b.paper_id = c.id
WHERE c.id = 466786033686716416
使用多表联合需要注意的是小表驱动大表,在没有过滤条件的情况下,外表有多少行就会被加载多少次,而加载次数越多效率越低,所以尽量使用数据少的表去驱动其它表。
这是三张表的联合查询,在关键字段上都添加了索引,它们的type最低都是ref。虽然看上去执行效率很高,但是使用join的时候会把所有符合条件的数据关联起来,而我们查询的时候并不需要这么多数据,假如每张张表有1000条数据,那么最后数据最大可能有100010001000,这样会大大拉低查询效率。
EXPLAIN SELECT * FROM `t_paper_subject_answer` a
where a.subject_id in (SELECT id from t_paper_subject b
where b.paper_id in (SELECT id from t_paper c
WHERE c.id = 466786033686716416))
这是另一种写法的查询语句,两者得到的结果是相同的,但是这里不需要进行多表关联,在有些时候可以通过子查询来替代多表关联。
4. 索引失效
- 查询条件中使用or,且有部分条件不带索引。那么即使其它条件带了索引,那么也会全表扫描。
EXPLAIN SELECT * FROM `subject` WHERE id = 453639012033536000 or subject = '编程题93'
这条语句中id是主键,但是subject没有建立索引,最后type为All,进行了全表扫描。
- 使用复合索引时,条件中为按照创建索引时的顺序进行查询。假设新建一个复合索引
CREATE INDEX name ON `table`(a,b,c) ;
在进行查询是where里面的条件要是
a= ?;
a = ? and b = ?;
a = ?and c = ?;
a = ?and b = ? and c = ?
这几种形式才会生效,如果顺序错误那么就不会使用索引。
- like查询是以%开头
- 条件使用的字段进行了数据类型转换。例如字符串类型未使用引号。
- 条件查询的左边进行了数学运算或者函数
- 使用了is null 或者 not is null
- 全表扫描效率高于索引
- 使用>, <比较符
5. 合理建立索引
并不是索引越多越好,需要合理建立
- 频繁更新的字段不适合建立索引,它更新时也会更新索引
- 未经常使用的字段无需建立索引
- 经常进行增删改的表不适合建立索引
- 包含大量重复数据的字段索引效果不大
- 表中数据量较少时无需使用索引