Skip to main content

Mysql索引、查询计划、优化方向

索引

Innodb索引(聚集索引)(聚簇表分布)

屏幕快照_2016-10-15_上午11.14.50 Innodb是索引组织表,其结构是B+Tree。其表索引结构是聚簇表分布。
其叶子节点保存了Key+Row Data(聚集索引,按主键排序),其Key是主键或非空的唯一索引。而其二级索引是非主键或者非空的唯一索引。即Innodb表会把主键作为聚集索引,如果没有主键,会选择非空的唯一索引作为聚集索引。如果非空的唯一索引也没有的情况下,Innodb表会默认生成一个6bytes的id,默认作为主键成为聚集索引。

在创建表的时候要自己指定一个没有业务含义的主键,便于快捷插入
默认6字节的数据,到达数据上限时,不会报错,而是会直接覆盖数据

Myisam索引(非聚集索引)(非聚簇表分布)

屏幕快照_2016-10-15_上午11.26.18 Myiasm不管是主键索引还是二级索引,永远存储的是Key,数据则单独在表中。即非聚集索引,查询的时候要先查询索引,然后查询数据。

Innodb优化方向和注意事项

创建索引

需要注意的是创建索引不是越多越好,要注意基数和选择性。
基数:

一列数据,只有True和False两个结果。则这一列的基数是2

选择性:

列唯一键与行数的比值,在0-1之间。他的取值越小越好。选择性的值是根据表中值的数据而不一样的。基数为2的情况下,就有2个选择性的结果。
计算方法:相同值的数据总量➗表中数据的总量
如果Mysql中经常查询的方法是使用此列中选择性小的数据进行筛选和取出,那么可以对此建立索引提高效率。反之,如果经常使用选择性比较高的数据进行筛选,则不适合对此列建立索引,而应先考虑业务上的查询优化。

优化

禁止使用select *

使用select * 会查询大量数据并读取,不利于使用索引覆盖技术。

字符串查询

字符串查询的时候最好不要在开头进行模糊查询,从开头进行模糊查询难以使用索引快捷查找。

创建表的时候要有业务无关的自增主键

如果不使用自增主键的时候,系统默认的主键会有覆盖数据而不警告的风险。

大批量导入数据

如果大批量插入数据的时候,最好先将数据导入,然后再创建索引,同时进行批量提交而非单条提交。

对排序字段创建索引

order by高频调用的字段,对齐创建索引有助于快速排序。

避免使用limit a,b

Limit a,b 会取出a+b条记录。所以应该尽量避免,而使用limit a,并排序后限制>id。

执行计划

explain SQL Command  

Example:

mysql> explain select ID,post_author,post_date,post_title,post_status,post_name from wp_posts;  
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | wp_posts | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   56 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)  

数据的查看方法

注意几点:
1. ID越大,优先级越高,越先被执行
2. ID相同,从上至下,按顺序执行

字段解释

ID

SQL语句的执行顺序

possible_keys

指出Mysql可以利用哪些索引在表中找到Row,如果查询的字段中存在索引,则会被列出在这个地方,如果没有的话就不会列出,如果有的话也并不代表会被使用。

key

Mysql实际在查询中使用到的索引,如果没有使用到索引,则不会被列出。

type

Mysql找到所需数据(Row)所使用的方式,称之为访问类型,按照查询的性能由差到好如下所示:

Type Desc
ALL Full Table Scan,Mysql会遍历整张表找到所需数据
index Full Index Scan,Mysql只遍历索引树
range 索引范围扫描,Mysql对索引的扫描开始于某一点而非全部,常见于匹配值域(between,in, >, etc...)
ref 非唯一索引扫描,返回匹配某个单独值的所有数据。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找(=)
eq_ref 唯一索引扫描,即每个索引键只有一条记录与之匹配,常见于主键和唯一键
const,system
null

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
keylen显示的值为索引字段的最大可能长度,并非实际使用长度。即keylen是根据表定义计算而得,不是通过表内检索出的

rows

Mysql根据表统计信息和索引状况,估算的影响到的数据的行数

ref

表示表的连接匹配条件

Extra

显示比较重要的额外信息

Using Index

表示Mysql在操作中使用了覆盖索引(Covering Index)

覆盖索引
MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件。包含所有满足查询需要的数据的索引称为覆盖索引。
覆盖索引切不可使用select *,否则会造成索引文件过于庞大,导致查询性能下降。

Using Where

表示Mysql使用Where字句过滤结果

Using Temporary

表示Mysql需要使用临时表来存储结果,一般见于排序和分组中

Using Filesort

表示Mysql无法利用索引完成排序,需要使用文件排序。