网站如何安装dedecms,网址大全查询ip地址,帮公司做网站的外包公司,六盘水seo目录 1. sql explain每个字段对应的含义1.1. id1.2. select_type1.3. table1.4. partitions1.5. type1.6. possible_keys1.7. key1.8. key_len1.9. ref1.10. rows1.11. Extra 索引实践联合索引最左列原则全值匹配不建议在索引列上做任何操作, 否则索引会失效转而全表扫描尽量使… 目录 1. sql explain每个字段对应的含义1.1. id1.2. select_type1.3. table1.4. partitions1.5. type1.6. possible_keys1.7. key1.8. key_len1.9. ref1.10. rows1.11. Extra 索引实践联合索引最左列原则全值匹配不建议在索引列上做任何操作, 否则索引会失效转而全表扫描尽量使用覆盖索引 不需要再回表查询了 效率较高再试用 ! 或 不等于查询时, 会导致索引失效。尽量不要使用 or, in 操作, 在某些情况下也会导致索引失效。is null, is not null 一般情况下也无法使用索引是用字符串查询 不见引号 索引也会失效针对范围查找的不走索引的优化like 查询建议使用 xxx% 方式匹配, %xxx 或者 %xxx% 索引失效 1. sql explain
使用 Explain 可以查看 sql 的性能瓶颈信息, 并根据结果进行 sql 的相关优化。在 select 语句前加上 explain 关键字, 执行的时候并不会真正执行 sql 语句, 而是返回 sql 查询语句对应的执行计划信息。
当然如果 select 语句的 from 后面有一个子查询的话, 就会执行子查询了并把结果放到一个临时表中。
有三张表:
-- 演员表CREATE TABLE actor (id INT ( 11 ) NOT NULL,name VARCHAR ( 45 ) DEFAULT NULL,update_time datetime DEFAULT NULL,PRIMARY KEY ( id )
) ENGINE INNODB DEFAULT CHARSET utf8;INSERT INTO actor (id, name, update_time) VALUES (1,a,2017-12-22 15:27:18), (2,b,2017-12-22 15:27:18), (3,c,2017-12-22 15:27:18);-- 电影表
CREATE TABLE film (id INT ( 11 ) NOT NULL AUTO_INCREMENT,name VARCHAR ( 10 ) DEFAULT NULL,PRIMARY KEY ( id ),KEY idx_name ( name )
) ENGINE INNODB DEFAULT CHARSET utf8;INSERT INTO film (id, name) VALUES (3,film0),(1,film1),(2,film2);-- 演员和电影中间表
CREATE TABLE film_actor (id INT ( 11 ) NOT NULL,film_id INT ( 11 ) NOT NULL,actor_id INT ( 11 ) NOT NULL,remark VARCHAR ( 255 ) DEFAULT NULL,PRIMARY KEY ( id ),KEY idx_film_actor_id ( film_id, actor_id )
) ENGINE INNODB DEFAULT CHARSET utf8;INSERT INTO film_actor (id, film_id, actor_id) VALUES (1,1,1),(2,1,2),(3,2,1);执行 explain select * from actor;
结果:
mysql explain select * from actor;
-----------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)根据返回的信息可以分析 sql 的性能瓶颈从而进行优化。
下面分析其中每个字段对应的含义。
每个字段对应的含义
1.1. id
代表 sql 中查询语句的序列号, 序列号越大则执行的优先级越高, 序号一样谁在前谁先执行。id 为 null 则最后执行。
1.2. select_type
查询类型, 表示当前被分析的 sql 语句的查询的复杂度。这个字段有多个值。
SIMPLE: 表示简单查询。
mysql explain select * from actor;
-----------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)PRIMARY: 表示复杂查询中的最外层的 select 查询语句。 SUBQUERY: 表是子查询语句 跟在 select 关键字后面的 select 查询语句;
mysql explain select (select 1 from film where id 1) from actor;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | actor | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using index |
| 2 | SUBQUERY | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)derived: 派生查询, 跟在一个 select 查询语句的 from 关键字后面的 select 查询语句 例如:
mysql set session optimizer_switchderived_mergeoff; -- 关闭 mysql5.7 新特性对衍生表的合并优化
Query OK, 0 rows affected (0.00 sec)mysql explain select (select 1 from actor where id 1) from (SELECT * from film where id1) ac;
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived3 | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 3 | DERIVED | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | actor | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------------
3 rows in set, 1 warning (0.00 sec)1.3. table
表示当前访问的表的名称。
当 from 中有子查询时, table 字段显示的是 derivedN N 为 derived 的 id 的值。
1.4. partitions
返回的是数据分区的信息, 不常用 这里不做分析。
1.5. type
这个字段决定 mysql 如何查找表中的数据, 查找数据记录的大概范围。这个字段的所有值表示的从最优到最差依次为:
system const eq_ref ref range index all;
一般来说我们优化到 range 就可以了, 最好到 ref。
null: type 字段的值如果为 null, 那么表示当前的查询语句不需要访问表, 只需要从索引树中就可以获取我们需要的数据;
一般如果是主键索引的话 , 查询主键字段或者唯一索引的话 查询主键字段 type 字段的值就为 null。
mysql explain select id from actor where id 1;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | actor | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)system/const: 用户主键索引或者唯一索引查询时, 只能匹配 1 条数据 一般可以对 sql 查询语句优化成一个常量, 那么 type 一般就是 system 或者 const, system 是 const 的一个特例。
mysql explain select * from (select * from film where id 1) tmp;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived2 | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | film | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)eq_ref: 在进行连接查询时, 例如 left join 时, 如果是使用主键索引或者唯一索引连接查询 , 结果返回一条数据, 则 type 的值为一般为 eq_ref。
mysql explain SELECT * from film_actor left join film on film.id film_actor.film_id;
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film_actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | film | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mysql.film_actor.film_id | 1 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.01 sec)分析下这个 sql, 首先我们需要查询的是 film_actor 中间表 且这个表是与 film 表进行主键关联的, 索引 film_actor 表中的 film_id 字段在 film 表中只有一个唯一值, 所以: eq_ref
那么, 反过来在看一下
mysql explain SELECT * from film left join film_actor on film_actor.film_id film.id;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
| 1 | SIMPLE | film_actor | NULL | ALL | idx_film_actor_id | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.01 sec)film 表和 film_actor 中间表关联查询, 根据 film 电影表中的主键 id 和 film_actor 表中的 film_id 字段进行关联的。电影表中的主键 id 在 film_actor 中并不是唯一的。所以: index ALL
对于 film 需要确定查询 id 从索引树中就可以获取值 所以是 index。对于 film_actor 就是全表扫描了。
ref: 相比较 eq_ref, 不使用主键索引或者唯一索引, 使用的是普通索引或者唯一索引的部分前缀, 索引与一个值进行比较后可能获取到多个符合条件的行, 不在是唯一的行了。
简单查询, name 是普通索引
mysql explain select * from film where name film1;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | ref | idx_name | idx_name | 33 | const | 1 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)复杂查询, film_actor 有联合索引 idx_film_actor_id(film_id,actor_id) 这里使用了联合索引的左前缀 film_id
mysql explain select fa.film_id from film f left join film_actor fa on fa.film_id f.id;
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | f | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
| 1 | SIMPLE | fa | NULL | ref | idx_film_actor_id | idx_film_actor_id | 4 | mysql.f.id | 1 | 100.00 | Using index |
-----------------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)range: 通常使用范围查找, 例如 between, in, , , 等使用索引进行范围检索。
mysql explain select * from film where id 2;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)index: 扫描索引树就能获取到的数据, 一般是扫描二级索引, 并且不会从根节点扫描, 一般直接扫描二级索引的叶子节点, 速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键, 所以二级索引还是比较小的, 扫描速度相比 All 还是很快的。这里用到了覆盖索引, 什么是覆盖索引: 可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历 name 索引树就可以获取到主键 id 的值就是覆盖索引。
mysql explain select id from film;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)ALL: 这是一种效率最低的 type, 需要扫描主键索引树的叶子节点, 获取数据是表中其他列的数据, 即全表扫描。
和 index 有什么区别呢?
拿 film 电影表举例: 添加一个 remark 影评字段, film 表结构如下:
CREATE TABLE film (id int(11) NOT NULL AUTO_INCREMENT,name varchar(10) DEFAULT NULL,remark varchar(255) DEFAULT NULL,PRIMARY KEY (id),KEY idx_name (name)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8;表中建了两个索引: id 主键索引 idx_name(name) 二级索引。
那么:
mysql explain select id,name from film ;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 1 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)上述 sql 查询 id, name 两个字段, 分析 mysql 索引数据结构, 以及 mysql 优化后一般扫描二级索引, 索引会扫描 idx_name 索引树的叶子节点, 那么根据 BTree 树的结构, 叶子节点保存的是 name 字段的索引值 和 data 数据(主键 id)。而正好我们只需要查询 id 和 name 两个字段, 我们查询的字段被索引(二级索引)给覆盖了 这就是覆盖索引, 因此 type 的类型就是 index。
再来:
mysql explain select remark from film ;
-----------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)比较上一个 sql, 这个 sql 只查询了一个字段: remark, 经过上面分析, 这个字段是不在 idx_name 索引树的叶子节点上的, 所以 mysql 不会在扫描 idx_name 索引树了, 直接扫描主键索引的叶子节点, 即进行全表扫描, 这个时候 type 类型为 ALL。
1.6. possible_keys
这个字段显示的是 sql 在查询时可能使用到的索引, 但是不一定真的使用, 只是一种可能。
如果在进行 explain 分析 sql 时, 发现这一列有值, 但是 key 列为 null, 因为 mysql 觉得可能会使用索引, 但是又因为表中的数据很少, 使用索引反而没有全表扫描效率高, 那么 mysql 就不会使用索引查找, 这种情况是可能发生的。
如果该列是 NULL, 则没有相关的索引。在这种情况下, 可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能, 然后用 explain 查看效果。
1.7. key
sql 执行中真正用到的索引字段。
1.8. key_len
用到的索引字段的长度, 通过这个字段可以显示具体使用到了索引字段中的哪些列(主要针对联合索引): 计算公式如下
字符串 char(n): n 字节长度varchar(n): 如果是 utf-8, 则长度 3n 2 字节, 加的 2 字节用来存储字符串长度 数值类型 tinyint: 1 字节smallint: 2 字节int: 4 字节bigint: 8 字节 时间类型 date: 3 字节timestamp: 4 字节datetime: 8 字节 如果字段允许为 NULL, 需要 1 字节记录是否为 NULL
索引最大长度是 768 字节, 当字符串过长时, mysql 会做一个类似左前缀索引的处理, 将前半部分的字符提取出来做索引。
1.9. ref
表示那些列或常量被用于查找索引列上的值
1.10. rows
表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。
1.11. Extra
展示一些额外信息。
索引实践
以下实践以 employees 表为例。一个主键索引 一个联合索引
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 职位,hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入职时间,PRIMARY KEY (id),KEY idx_name_age_position (name,age,position) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT11 DEFAULT CHARSETutf8 COMMENT员工记录表;联合索引最左列原则
例 1:
EXPLAIN SELECT * FROM employees WHERE name LiLei;使用联合索引中的 name 字段索引。
例 2:
EXPLAIN SELECT * FROM employees WHERE name LiLei AND age 22;使用联合索引中的 name 和 gae 字段索引。
例 3:
EXPLAIN SELECT * FROM employees WHERE name LiLei AND age 22 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 LiLei AND age 22 AND position manager;这个使用了联合索引中的 name 和 age 字段, 没有使用 position, 为什么? 原理其实和上面差不多。分析一波:
首先按照顺序 name-age-position,name 已经确定了等于 LiLei, 那么 age 就是有序的了, 所以检索 age22 的就很容易了 因为 age 有序。但是 age 值其实是不确定的, age 可以是 23,24,25… 等等, 所以在 age 不确定情况下 position 是无序的 因此是不走 position 索引字段的。
全值匹配
mysql EXPLAIN SELECT * FROM employees WHERE name LiLei AND age 22 AND position manager;
-----------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)不建议在索引列上做任何操作, 否则索引会失效转而全表扫描
-- 查询 name 的最左变的两个字符为 Li 的行
mysql EXPLAIN SELECT * FROM employees WHERE LEFT(name,2) Li;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)尽量使用覆盖索引 不需要再回表查询了 效率较高
再试用 ! 或 不等于查询时, 会导致索引失效。
mysql EXPLAIN SELECT * FROM employees WHERE name ! LiLei;
-----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)尽量不要使用 or, in 操作, 在某些情况下也会导致索引失效。
第一种情况: 当表中只有两条数据 数据量很少的时候
mysql explain SELECT * from employees where name in (LiLei,abc);
-----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)使用 in 查询, 没有走索引, 进行了全表扫描, 为什么? 分析一波:
首先 如果使用索引的话, mysql 大概会怎么操作? 应该先在 name 索引树中定位到 nameLiLei 这个节点(最少一次 I/O), 然后定位到 nameabc 这个节点(一次 I/O), 然后分别拿到主键 id, 在去主键索引树上扫描定位(最少又要两次 I/O), 总共 4 次 I/O。
如果不使用索引, 直接全表扫描, 那么直接扫描主键索引树的叶子节点 只需要两次 I/O 即可(因为只有两条数据), 所以 mysql 评估全表扫描效率可能会更高, 就不会在走索引了。
第二种情况: 当表中数据量很多, 例如 7 条数据
同样的 sql 查询
mysql explain SELECT * from employees where name in (LiLei,abc);
-----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)结果: 走了索引
为什么会出现这种情况? 再来分析一波:
首先走索引的话 大概需要 4 次 I/O 上面已经分析过了。
那么不走索引的话 需要全表扫描 最坏的情况需要扫描 7 次, 进行 7 次 I/O,mysql 评估一下发现全表扫描的效率可能是低于走索引的, 所以就走了索引。
第三种情况: 数据还是 7 条, 但是我 in 查询时条件有 8 个
mysql explain SELECT * from employees where name in (LiLei,abc,cde,asc,ssw,2dff,wsa,sda);
-----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)看下结果:
为啥又不走索引了呢? 经过上面的两波强势分析, 这里也很容知道原因, 就不过多的赘述了。or 查询的情况类似。
is null, is not null 一般情况下也无法使用索引
是用字符串查询 不见引号 索引也会失效
mysql explain SELECT * from employees where name 1324;
-----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------------------
1 row in set, 3 warnings (0.00 sec)针对范围查找的不走索引的优化
首先看个例子:
-- 先给 age 加一个独立索引
mysql ALTER TABLE employees ADD INDEX idx_age (age) USING BTREE ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0-- 查询 age 在 1 到 2000 分为内的数据
mysql explain SELECT * from employees where age 1 and age 2000 ;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 1 | 100.00 | Using index condition |
-----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)显然并没有走索引 为什么? 再来强势分析一波:
首先, 我们脑海中要有一个 age 的索引树: 我们要找到 1-2000 的数据, 那么在这棵树书上怎么定位?
如果我来定位的话 我会定位一个 age2 在树上的位置 在定位一个 age1999 在树上的位置, 然后从 age2 的节点开始取右边的节点, 一直取下去 直到 age1999 为止, 但是我们表总只有 7 条数据, mysql 觉得这样操作还没有全表扫描快, 毕竟一共才几条数据全表扫描反而更快些, 所以 mysql 就去全表扫描了。
怎么优化呢?
mysql explain SELECT * from employees where age 1 and age 1000 ;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 1 | 100.00 | Using index condition |
-----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql explain SELECT * from employees where age 1001 and age 2000 ;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 1 | 100.00 | Using index condition |
-----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)把一个大的范围拆成多个小的范围 可以利用索引查询。
like 查询建议使用 xxx% 方式匹配, %xxx 或者 %xxx% 索引失效
mysql EXPLAIN SELECT * FROM employees WHERE name like %Lei;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql EXPLAIN SELECT * FROM employees WHERE name like %Lei%;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)结果: 全表扫描
思考下在索引树上 name 的排序规则, 先按照第一个字符比较然后第二个字符依次向后比较, 如果是用 %xxx, 字符串前面的字符不确定, 怎么在树上定位呢? 显然没法按照顺序定位, 只能一个一个遍历比较 所以不会走索引。%xxx% 也一样。
在看下面这个例子
mysql EXPLAIN SELECT * FROM employees WHERE name like Lei%;
---------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 1 | 100.00 | Using index condition |
---------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)结果: 走了索引
其实 Lei% 匹配相当于范围查询, 只要 name 的值的前三个字符为 Lei 符合条件, 等价于查找前三个字符 Lei 的字符串, 这个在索引树上是有序的, 当然可以使用索引定位。
总结:
使用 explain 关键字, 可以分析出 sql 的性能瓶颈并加以优化了解 explain 返回的各字段值代表的意义, 结合索引数据结构有助于我们对 sql 的查询效率的分析和优化列举部分可能不会进行索引检索的情况, 例如 !, , is null, like 的某些情况, or 或者 in 的某些情况, 字符串不加引号等对某些不走索引查询的情况作了一些比较详细的分析
