目录
MySQL explain 用法
一, explain 命令应用
二, Extra(扩展)
三. 建立索引的原则 (规范)
总结:
MySQL explain 用法
一, explain 命令应用
查询数据的方式
1. 全表扫描
1) 在 explain 语句结果中 type 为 ALL
2) 什么时候出现全表扫描?
2.1 业务确实要获取所有数据
2.2 不走索引导致的全表扫描
? 2.2.1 没索引
? 2.2.2 索引创建有问题
? 2.2.3 语句有问题
生产中, MySQL 在使用全表扫描时的性能是极其差的, 所以 MySQL 尽量避免出现全表扫描
2. 索引扫描
2.1 常见的索引扫描类型:
1)index 全索引扫描
2)range 范围查询时会达到 range 级别
3)ref 使用非唯一索引扫描或者唯一索引的前缀扫描
- )eq_ref
- )const
- )system
- )null
从上到下, 性能从最差到最好, 我们认为至少要达到 range 级别
一般我们说, 只要一条 SQL 语句, 达到 range 级别, 我们会认为索引的效率是 OK 的
全表扫描:
MySQL> explain select * from student2;
index: 全 索引扫描, index 与 ALL 区别为 index 类型只遍历索引树.
- MySQL> explain select cno from course;
- +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
- | 1 | SIMPLE | course | index | NULL | PRIMARY | 8 | NULL | 2 | Using index |
- +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
range: 范围查询的时候会达到 range 级别
- MySQL> explain select * from city where population>30000000;
- +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
- | 1 | SIMPLE | city | range | idx_city | idx_city | 4 | NULL | 1 | Using index condition |
- +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
ref: 使用非唯一索引扫描或者唯一索引的前缀扫描, 返回匹配某个单独值的记录行.
- MySQL> explain select * from city where countrycode='chn';
- +----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------
- | 1 | SIMPLE | city | ref | CountryCode | CountryCode | 3 | const | 363 | Using index condition |
- +----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------
- 1 row in set (0.00 sec)
- #union all 比 in 的速度快很多
- MySQL> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
- +----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
- | 1 | PRIMARY | city | ref | CountryCode | CountryCode | 3 | const | 363 | Using index condition |
- | 2 | UNION | city | ref | CountryCode | CountryCode | 3 | const | 274 | Using index condition |
- | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
- +----+--------------+------------+------+---------------+-------------+---------+-------+------+-----------------------+
- 3 rows in set (0.00 sec)
eq_ref: 类似 ref, 区别就在使用的索引是唯一索引, 对于每个索引键值, 表中只有一条记录匹配, 简单来说, 就是多表连接中使用 primary key 或者 unique key 作为关联条件 A
- join B
- on A.sid=B.sid
- MySQL> explain select score.mark,student.sname from score join student on score.sno=student.sno;
const,system: 当 MySQL 对查询某部分进行优化, 并转换为一个常量时, 使用这些类型访问.
如将主键置于 where 列表中, MySQL 就能将该查询转换为一个常量
MySQL> explain select * from course where cno=1;
NULL:MySQL 在优化过程中分解语句, 执行时甚至不用访问表或索引, 例如从一个索引列里选取最小值可以通过单独索引查找完成.
- # 查询超出范围
- MySQL> explain select * from course where cno>66666666666666666666666;
- +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
- | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
- +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
- 1 row in set (0.00 sec)
二, Extra(扩展)
Using temporary Using filesort 使用了默认的文件排序 (如果使用了索引, 会避免这类排序)
Using join buffer
如果出现 Using filesort 请检查 order by ,group by ,distinct,join 条件列上没有索引
MySQL> explain select * from city where countrycode='CHN' order by population;
当 order by 语句中出现 Using filesort, 那就尽量让排序值在 where 条件中出现
- MySQL> explain select * from city where population>30000000 order by population;
- MySQL> select * from city where population=2870300 order by population;
- +------+-------------------+-------------+----------+------------+
- | ID | Name | CountryCode | District | Population |
- +------+-------------------+-------------+----------+------------+
- | 1899 | Nanking [Nanjing] | CHN | Jiangsu | 2870300 |
- +------+-------------------+-------------+----------+------------+
- 1 row in set (0.00 sec)
key_len: 越小越好
前缀索引去控制
rows: 越小越好
三. 建立索引的原则 (规范)
为了使索引的使用效率更高, 在创建索引时, 必须考虑在哪些字段上创建索引和创建什么类型的索引.
那么索引设计原则又是怎样的?
1, 尽量使用唯一索引
唯一性索引的值是唯一的, 可以更快速的通过该索引来确定某条记录.
例如:
学生表中学号是具有唯一性的字段. 为该字段建立唯一性索引可以很快的确定某个学生的信息.
如果使用姓名的话, 可能存在同名现象, 从而降低查询速度.
主键索引和唯一键索引, 在查询中使用是效率最高的.
- # 查看行数
- MySQL> select count(*) from world.city;
- +----------+
- | count(*) |
- +----------+
- | 4079 |
- +----------+
- 1 row in set (0.01 sec)
- # 去重后的行数
- MySQL> select count(distinct countrycode) from world.city;
- +-----------------------------+
- | count(distinct countrycode) |
- +-----------------------------+
- | 232 |
- +-----------------------------+
- 1 row in set (0.00 sec)
- MySQL> select count(distinct countrycode,population ) from world.city;
- +-----------------------------------------+
- | count(distinct countrycode,population ) |
- +-----------------------------------------+
- | 4052 |
- +-----------------------------------------+
- 1 row in set (0.01 sec)
注意: 如果重复值较多, 可以考虑采用联合索引
2.为经常需要排序, 分组和联合操作的字段建立索引
例如:
经常需要 ORDER BY,GROUP BY,DISTINCT 和 UNION 等操作的字段, 排序操作会浪费很多时间.
如果为其建立索引, 可以有效地避免排序操作
3.为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件, 那么该字段的查询速度会影响整个表的查询速度.
因此, 为这样的字段建立索引, 可以提高整个表的查询速度.
3.1 经常查询
3.2 列值的重复值少
注: 如果经常作为条件的列, 重复值特别多, 可以建立联合索引
4.尽量使用前缀来索引
如果索引字段的值很长, 最好使用值的前缀来索引. 例如, TEXT 和 BLOG 类型的字段, 进行全文检索
会很浪费时间. 如果只检索字段的前面的若干个字符, 这样可以提高检索速度.
5.限制索引的数目
索引的数目不是越多越好. 每个索引都需要占用磁盘空间, 索引越多, 需要的磁盘空间就越大.
修改表时, 对索引的重构和更新很麻烦. 越多的索引, 会使更新表变得很浪费时间.
6.删除不再使用或者很少使用的索引
表中的数据被大量更新, 或者数据的使用方式被改变后, 原有的一些索引可能不再需要. 数据库管理
员应当定期找出这些索引, 将它们删除, 从而减少索引对更新操作的影响.
-----------------------------------------------------------------------------------------------------------------------------------------------------
重点关注:
1. 没有查询条件, 或者查询条件没有建立索引, 不走索引
- # 全表扫描
- select * from table;
- select * from tab where 1=1;
在业务数据库中, 特别是数据量比较大的表, 是没有全表扫描这种需求.
1) 对用户查看是非常痛苦的.
2) 对服务器来讲毁灭性的.
3)SQL 改写成以下语句:
- # 情况 1
- # 全表扫描
- select * from table;
- # 需要在 price 列上建立索引
- selec * from tab order by price limit 10;
- # 情况 2
- #name 列没有索引
- select * from table where name='zhangsan';
1, 换成有索引的列作为查询条件
2, 将 name 列建立索引
2. 查询结果集是原表中的大部分数据, 应该是 25%以上
- MySQL> explain select * from city where population>3000 order by population;
- MySQL> explain select * from city where population>3000 limit 10;
1) 如果业务允许, 可以使用 limit 控制.
2) 结合业务判断, 有没有更好的方式. 如果没有更好的改写方案就尽量不要在 MySQL 存放这个数据了, 放到 Redis 里面.
3. 索引本身失效, 统计数据不真实
索引有自我维护的能力.
对于表内容变化比较频繁的情况下, 有可能会出现索引失效.
重建索引就可以解决
4. 查询条件使用函数在索引列上或者对索引列进行运算, 运算包括 (+,-,* 等)
- MySQL> explain select * from student2 where sid-1=8;
- # 例子
错误的例子: select * from test where id-1=9;
正确的例子: select * from test where id=10;
5. 隐式转换导致索引失效. 这一点应当引起重视. 也是开发中经常会犯的错误
- MySQL> create table test (id int ,name varchar(20),telnum varchar(10));
- MySQL> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
- MySQL> explain select * from test where telnum=120;
- MySQL> alter table test add index idx_tel(telnum);
- MySQL> explain select * from test where telnum='120';
6. <> ,not in 不走索引
- MySQL> select * from tab where telnum <> '1555555';
- MySQL> explain select * from tab where telnum <> '1555555';
单独的 >,<,in 有可能走, 也有可能不走, 和结果集有关, 尽量结合业务添加 limit
or 或 in 尽量改成 union . 推荐使用 union all 联合查询.
- explain select * FROM teltab WHERE telnum IN ('110','119');
- # 改写成
- explain select * FROM teltab WHERE telnum='110'
- union all
- select * FROM teltab WHERE telnum='119'
7.like "%_" 百分号在最前面不走
- # 走 range 索引扫描
- explain select * from teltab WHERE telnum like '31%';
- # 不走索引
- explain select * from teltab WHERE telnum like '%110';
%Linux% 类的搜索需求, 可以使用 Elasticsearch -------> ELK(底层是搜索引擎, 百度, 谷歌都是用的这种)
8. 单独引用联合索引里非第一位置的索引列
- CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
- ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
- DESC t1
- SHOW INDEX FROM t1
- # 走索引的情况测试
- explain select name,age,sex,money from t1 where money=30 and age=30 and sex='m';
- # 部分走索引
- explain select name,age,sex,money from t1 where money=30 and age=30;
- explain select name,age,sex,money from t1 where money=30 and sex='m';
- # 不走索引
- explain select name,age,sex,money from t1 where age=20
- explain select name,age,sex,money from t1 where age=30 and sex='m';
- explain select name,age,sex,money from t1 where sex='m';
总结:
如果一个 SQL 语句, 是慢查询, 检查顺序:
1. 有没有创建索引
2. 查看数据类型, 和查询语句是否一致
3. 查询语句中, 是否使用字段做运算
4. 查询出来的结果集很大, limit
5. 查询语句中是否使用 <> 或者 not in
6. 查询语句中是否使用模糊查询, 且 % 在前面
7. 如果使用联合索引, 请按照创建索引的顺序查询
8. 索引损坏
来源: http://www.bubuko.com/infodetail-3282257.html