性能优化及数据表的设计 Explain工具使用

数据库表设计

第一范式：1NF - 确保原子性

第二范式：2NF - 确保表中每列都和主键相关

第三范式：3NF - 确保每列都和主键直接相关，而不是间接相关

SQL语句优化

show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。

show status like 'Com_%';

重点注意：Com_select,Com_insert,Com_update,Com_delete 通过这几个参数，可以了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

show status like 'Connections';show status like 'Uptime';show status like 'Slow_queries';

Connections：试图连接MySQL服务器的次数Uptime：服务器工作的时间（单位秒）Slow_queries：慢查询的次数 (默认是慢查询时间10s)

查询MySQL的慢查询时间

show variables like 'long_query_time';

修改MySQL慢查询时间

set long_query_time=2;

定位慢查询

show variables like '%query%';

注意:如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

Explain中的列

id 列

id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且id的顺序是按 select 出现的顺序递增的。

id列越大执行优先级越高，id相同则从上往下执行，id为 NULL 最后执行。

select_type 列--------------------------------重点

select_type 表示对应行是简单还是复杂的查询。

simple ：简单查询。查询不包含子查询和union

primary ：复杂查询中最外层的 select

subquery ：包含在 select 中的子查询（不在 from 子句中）

derived ：包含在 from 子句中的子查询。MySQL 会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

table 列

这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查 询，于是先执行 id=N 的查询。

 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type 列----------------------------------------重点

依次从最优到最差分别为：

system > const > eq_ref > ref > range > index > ALL

system: 表中只有一条数据. 这个类型是特殊的 const 类型.

const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.

例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.

explain select * from user_info where id = 2

eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.

EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id

ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询,所有有匹配索引值的行

例如下面这个例子中, 就使用到了 ref 类型的查询:

EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_sinfo.user_id AND order_info.user_id = 5

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.

当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

EXPLAIN SELECT * FROM user_info WHERE id BETWEEN 2 AND 8 

index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.index 类型通常出现在: 

所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.

EXPLAIN SELECT name FROM  user_info 

Extra 列

Using index ：使用覆盖索引,而不需要回表查询；

Using where ：使用 where 语句来处理结果，查询的列未被索引覆盖,需要回表查询。

Using index condition ：查询的列不完全被索引覆盖，where 条件中是一个前导列的范围

Using temporary ：MySQL 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

possible_keys 列

这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key 列

这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引，则该列是 NULL。

如果想强制mysql使用或忽视possible_keys列中的索 引，在查询中使用 force index、ignore index。

key_len 列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些 列。

 举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成， 并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列:film_id列来执 行索引查找。

ref 列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常 量)，字段名(例:film.id)

rows 列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

sql的编写过程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

解析过程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

索引的失效：

mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

is null、is not null 也无法使用索引

like以通配符开头('%abc...')mysql索引失效会变成全表扫描操作

字符串不加单引号索引失效

少用or或in