MySQL查询分析与优化

一、关于EXPLAIN 关键字段介绍：

1.id
代表sql中查询语句的序列号，序列号越大则执行的优先级越高，序号一样的依次执行。id为null则最后执行。

2.select_type
查询类型，表示当前被分析的sql语句的查询的复杂度。这个字段有多个值。

SIMPLE：表示简单查询。eg:explain select * from t1

PRIMARY：表示复杂查询中的最外层的select查询语句。 eg:explain select (select 1 from t2 where id =1) from t1;

SUBQUERY：表是子查询语句 跟在select 关键字后面的select查询语句 eg:explain select (select 1 from t2 where id =1) from t1;

derived： 派生查询，跟在一个select查询语句的from关键字后面的select查询语句 eg:select (select 1 from t1 where id =1) from (SELECT * from t2 where id=1) ac;

3.table
表示当前访问的表的名称。

当from中有子查询时，table字段显示的是<derivedN> N为derived的id的值。

4.partitions
返回的是数据分区的信息，不常用 这里不做分析。

5.type
这个字段决定mysql如何查找表中的数据，查找数据记录的大概范围。这个字段的所有值表示的从最优到最差依次为：

system > const > eq_ref > ref > range > index > all;

一般来说优化到range就可以了 最好到ref。

type字段的值如果为null，那么表示当前的查询语句不需要访问表，只需要从索引树中就可以获取我们需要的数据； eg:explain select id from t1 where t1.id =1;

system/const:用户主键索引或者唯一索引查询时，只能匹配1条数据 一般可以对sql查询语句优化成一个常量，那么type一般就是system或者const，system是const的一个特例。
explain select * from (select * from t where id = 1) tmp;
eq_ref:在进行连接查询时，例如left join 时，如果是使用主键索引或者唯一索引连接查询 ，结果返回一条数据，则type的值为一般为eq_ref。

ref: 相比较eq_ref,不使用主键索引或者唯一索引，使用的是普通索引或者唯一索引的部分前缀，索引与一个值进行比较后可能获取到多个符合条件的行，不在是唯一的行了。
range:通常使用范围查找，例如between，in，<,>,>=等使用索引进行范围检索
index:扫描索引树就能获取到的数据，一般是扫描二级索引，并且不会从根节点扫描，一般直接扫描二级索引的叶子节点，速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键，所以二级索引还是比较小的，扫描速度相比All还是很快的。这里用到了覆盖索引，什么是覆盖索引：可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历name索引树就可以获取到主键id的值就是覆盖索引。
explain SELECT * from t1 left join t2 on t2.id = t1.id;

ALL:这是一种效率最低的type，需要扫描主键索引树的叶子节点，获取数据是表中其他列的数据，即全表扫描

7.key
sql执行中真正用到的索引字段。

9.ref
表示那些列或常量被用于查找索引列上的值

10.rows
表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据

 

二，优化

 

以员工表为例

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 

1.联合索引最左列原则
例1：

EXPLAIN SELECT * FROM employees WHERE name= '聪聪';
使用联合索引中的name字段索引。

例2：

EXPLAIN SELECT * FROM employees WHERE name= '聪聪' AND age = 18;
使用联合索引中的name和gae字段索引。

例3：

EXPLAIN SELECT * FROM employees WHERE name= '聪聪' AND age = 18 AND position ='manager';
使用联合索引中的name age position字段索引。

例4：

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
仅仅使用了联合索引中的name字段，因为中间age字段断了，所以position字段索引并未用到。

索引是一个有序的数据结构，也就是说使用索引时，需要索引保证有序，那么在联合索引中，
是先按照name排序，name相同情况下，在按照age排序，age相同情况下
在按照position排序，因此如果age不确定情况下，position是无序的，所以即使你是用position查询了 也无法走索引的。这就是最左列原则并且中间不能断。

例5：

EXPLAIN SELECT * FROM employees WHERE name= '聪聪' AND age > 18 AND position ='manager';
这个使用了联合索引中的name和age字段，没有使用position，原理其实和上面差不多。分析一波：

首先按照顺序 name->age->position,name已经确定了等于聪聪，那么age就是有序的了，所以检索age>18的就很容易了
因为age有序。但是age值其实是不确定的，age可以是23,24,25...等等，所以在age不确定情况下 position是无序的 因此是不走position索引字段的。

2.全值匹配
EXPLAIN SELECT * FROM employees WHERE name= '聪聪' AND age = 18 AND position ='manager';

3.不建议在索引列上做任何操作如使用函数或者计算等，否则索引会失效转而全表扫描
-- 查询name的最左变的1个字符为聪的行
EXPLAIN SELECT * FROM employees WHERE LEFT(name,1) = '聪';

4.尽量使用覆盖索引 不需要再回表查询了 效率较高

5.如使用!=或<>不等于查询时，会导致索引失效。
EXPLAIN SELECT * FROM employees WHERE name != '聪聪';

6.尽量不要使用 ‘or’，‘in’等关键字的操作，在某些情况下也会导致索引失效。

第一种情况：当表中只有两条数据 数据量很少的时候

explain SELECT * from employees where name in ('聪聪','军军');

使用in查询，没有走索引，进行了全表扫描，为什么？分析一波：

首先 如果使用索引的话，mysql大概会怎么操作？ 应该先在name索引树中定位到name=聪聪这个节点（最少一次I/O），然后定位到name=军军这个节点（一次I/O），然后分别拿到主键id，
在去主键索引树上扫描定位（最少又要两次I/O），总共4次I/O。

如果不使用索引，直接全表扫描，那么直接扫描主键索引树的叶子节点 只需要两次I/O即可（因为只有两条数据），所以mysql评估全表扫描效率可能会更高，就不会在走索引了。

第二种情况：当表中数据量很多，例如多条条数据

同样的sql查询

explain SELECT * from employees where name in ('聪聪','军军');
结果：走了索引

为什么会出现这种情况？再来分析一波：

首先走索引的话 大概需要4次I/O 上面已经分析过了。

那么不走索引的话 需要全表扫描 最坏的情况需要扫描n次，进行n次I/O,mysql评估一下发现全表扫描的效率可能是低于走索引的，所以就走了索引。

第三种情况：数据还是n条，但是我in查询时条件有8个

explain SELECT * from employees where name in ('聪聪','军军','qq','ww','ee','dd','ff','ggg');

也是不走索引了的，or查询的情况类似。

7.使用is null ，is not null无法导致索引失效

8.是用字符串查询 不建引号 索引也会失效
explain SELECT * from employees where name = 111;

9.针对范围查找的不走索引的优化。可以试着单独创建索引

-- 先给age加一个独立索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
-- 查询age 在1到2000分为内的数据

explain SELECT * from employees where age >1 and age < 2000 ;
结果：没有走索引 为什么？再来强势分析一波：

首先，我们脑海中要有一个age的索引树：

我们要找到1-200的数据，那么在这棵树书上怎么定位？

如果我来定位的话 我会定位一个age=2在树上的位置 再定位一个age=99树上的位置，
然后从age=2的节点开始取右边的节点，一直取下去 直到age=99为止，
但是如果表总只有几条数据，mysql觉得这样操作还没有全表扫描快，毕竟一共才几条数据全表扫描反而更快些，所以mysql就去全表扫描了。

怎么优化呢？

explain SELECT * from employees where age >1 and age <100
explain SELECT * from employees where age >101 and age < 200 ;
把一个大的范围拆成多个小的范围 可以利用索引查询。

10.like查询建议使用xxx%方式匹配，若使用%xxx或者%xxx%则会导致索引失效
EXPLAIN SELECT * FROM employees WHERE name like '%a';
EXPLAIN SELECT * FROM employees WHERE name like '%aa%';
结果：全表扫描
EXPLAIN SELECT * FROM employees WHERE name like 'a%'
结果：走了索引

11.字符集
utf8mb4_unicode_ci和utf8mb4_general_ci的对比：

准确性：
utf8mb4_unicode_ci是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
utf8mb4_general_ci没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。
但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。
性能
utf8mb4_general_ci在比较和排序的时候更快
utf8mb4_unicode_ci在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。