MYSQL explain和索引使用

前几天抓了城市数据精确到街道,然后做了一个五级城市联动,但是有73万条数据,查询的时候明显慢了好多,因为数据格式已经定死了,

数据又多 就没有去改,谷歌下发现了explain这个语句,记录怎么使用这个语句

下面是我创建的表 这个表是没有索引

CREATE TABLE `citymore` (
`loc_id` varchar(36) DEFAULT NULL,
`loc_name` varchar(192) DEFAULT NULL,
`paddr` varchar(765) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

这个表里面有73万条数据,我们用explain来分析下一个模糊查询

EXPLAIN SELECT loc_id,loc_name FROM citymore WHERE loc_id LIKE ‘11%’

然后为表添加上唯一索引 ALTER TABLE `citymore` ADD UNIQUE INDEX `loc_id` (`loc_id`);  再重新查询一遍

等下再回来看这些字段值,先来解释下各个字段是什么意思

1. id

SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序

2.select_type

它有以下几种值

2.1 simple: 它表示简单的select,没有union和子查询

2.2 primary :最外面的select,在有子查询的语句中,最外面的select查询就是primary,下图中就是这样

2.3 union: union语句的第二个或者说是后面那一个

2.4 dependent union:    UNION中的第二个或后面的SELECT语句,取决于外面的查询

2.5 union result  : UNION的结果

2.6:DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

2.7:DERIVED:导出表的SELECT(FROM子句的子查询)

3 table

输出的行所用的表

4 type(这个很重要)

连接类型 是使用Explain命令分析性能瓶颈的关键项之一。有多个参数

结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。

4.1 const

表最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快

记住一定是用到primary key 或者unique  表加上索引

我用city表来测试

loc_id是主键,所以使用了const。所以说可以理解为const是最优化的

4.2  ALL  对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下差。

通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

4.3 range 给定范围内的检索,使用一个索引来检查行。需要为loc_id添加索引

5 possible_keys :提示使用哪个索引会在该表中找到行

6 keys :MYSQL使用的索引,简单且重要,如果没有选择索引,键是NULL

7 key_len: MYSQL使用的索引长度

8 ref   ref列显示使用哪个列或常数与key一起从表中选择行。

9 rows 显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引

10 Extra  该列包含MySQL解决查询的详细信息。

10.1 Distinct  : 一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

10.2:Using filesort::看 到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。

它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行

10.3 range checked for each record: 没有找到合适的索引

10.4 using index: 只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的信息。这个比较容易理解,就是说明是否使用了索引

10.5 using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,

如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。

10.6Not exists: MYSQL 优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了

10.7Using temporary:看到这个的时候,查询需要优化了。这 里,MYSQL需要创建一个临时表来存储结果,

这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上

看完这些字段后,我们再回去看看最上面的两个图,可以发现有索引的快好多

索引

索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。
表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。

索引是什么

你面前有本词典,数据就是书的正文内容,你就是那个cpu,而索引,则是书的目录

索引越多越好?

大多数情况下索引能大幅度提高查询效率,但:

  • 数据的变更(增删改)都需要维护索引,因此更多的索引意味着更多的维护成本
  • 更多的索引意味着也需要更多的空间 (一本100页的书,却有50页目录?)
  • 过小的表,建索引可能会更慢哦 :)  (读个2页的宣传手册,你还先去找目录?)

 like 不能用索引?

  • 尽量减少like,但不是绝对不可用,”xxxx%” 是可以用到索引的,

你在看一本成语词典,目录是按成语拼音顺序建立,查询需求是,你想找以 “一”字开头的成语(”一%“),和你想找包含一字的成语(“%一%”)

  • 除了like,以下操作符也可用到索引:

<,<=,=,>,>=,BETWEEN,IN

<>,not in ,!=则不行

什么样的字段不适合建索引?

  • 一般来说,列的值唯一性太小(如性别,类型什么的),不适合建索引
  • 太长的列,可以选择只建立部分索引,
  • 更新非常频繁的数据不适宜建索引

哪些常见情况不能用索引?

  • like “%xxx”
  • not in , !=
  • 对列进行函数运算的情况(如 where md5(password) = “xxxx”)
  • WHERE index=1 OR A=10
  • 存了数值的字符串类型字段(如手机号),查询时记得不要丢掉值的引号,否则无法用到该字段相关索引,反之则没关系

 

如何查看索引信息,如何分析是否正确用到索引?

show index from tablename;
explain select ……;

explain可以帮助你选择更好的索引和写出更优化的查询语句。

合适索引可以加速你的查询

当你的服务器上msyql占用cpu过高时可以用 show processlist命令来分析哪条sql语句占用资源