mysql--执行计划及sql优化

一、如何查看执行计划

MySQL 使用 explain + sql 语句查看 执行计划，该执行计划不一定完全正确但是可以参考。

以这条sql为例

SELECT * FROM `dy_push_source` where hotel_id = '60255778'

执行计划：

 二、执行计划字段说明

1.select_type

查询数据的操作类型

SIMPLE	简单查询
PRIMARY	最外层查询
SUBQUERY	映射为子查询
DERIVED	子查询
UNION	联合
UNION RESULT	使用联合的结果

 2.table

正在访问的表名

3.type

表的连接类型，其值、性能由低到高排列如下

后5种情况都是理想的索引的情况。通常优化至少到range级别，最好能优化到ref。

type	说明
ALL	全数据表扫描
index	全索引表扫描
RANGE	对索引列进行范围查找
INDEX_MERGE	合并索引，使用多个单列索引搜索
REF	根据索引查找一个或多个值
EQ_REF	搜索时使用primary key 或 unique类型
CONST	常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
SYSTEM	系统，表仅有一行(=系统表)。这是const联接类型的一个特例。

4.possible_keys 

显示可能应用到这张表的索引，一个或者多个，查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询实际使用

5.key 

实际使用到的索引，如果为null，则没有使用索引,查询中如果使用了覆盖索引，则该索引仅出现在key列表中

6.key_len

索引字节的长度

7.rows

mysql 预估为了找到所需的行而要读取的行数

8.extra

extra	说明
Using index	此值表示mysql将使用覆盖索引，以避免访问表。
Using where	mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
Using temporary	mysql 对查询结果排序时会使用临时表。
Using filesort	mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
Range checked for each record(index map: N)	没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的

 三、sql语句优化

1.like语句

使用 like 语句时，%在右边才会使用索引。最左匹配原则

//不可以触发
like '%sada'
//不可以触发
like '%dasda%'
//可以触发
like 'sdaas%'

2.or语句

or 条件中存在未建立索引的列会导致索引失效

//不触发索引
select * from 表 where 建索引的字段1 = ''  or  未建索引的字段2 = ''

3.条件的类型不一致

//不触发索引
select * from 表 where varchar类型的字段 =  整数1

 4.!= 号

使用不等于号时不会触发索引，但是如果是主键，则会走索引，

 5.> 号或<号

如果是主键或索引是整数类型，则会走索引，否则则不会走

如果字段为时间类型使用比较判断，索引有时候会失效的原因是：当你要查询出来的数据/表总数据 >30% 时候索引就会失效

 6.order by

如果 order by 是主键或索引是整数类型，则会走索引

7.组合索引

最左匹配原则

# 若 name 和 email 组成组合索引
create index ix_name_email on user(name,email);

name and email -- 使用索引
email and name -- 不使用索引
name           -- 使用索引
email          -- 不使用索引

8.覆盖索引

查询时，查询的字段最好和查询的条件保持一致或者少于，不然会触发回表，细节看之前关于回表介绍的文章mysql--索引_zhang09090606的博客-CSDN博客

9.索引列使用参数参数

如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描

select id from t where num = @num

10.索引列使用了函数和表达式

如果where条件中的列中使用了计算或者函数，则不会使用该索引

如：

    SELECT `sname` FROM `t_stu` WHERE `age`=20;-- 会使用索引
    SELECT `sname` FROM `t_stu` WHERE `age`+10=30;-- 不会使用索引！！因为所有索引列参与了计算
    SELECT `sname` FROM `t_stu` WHERE `age`=30-10;-- 会使用索引

 

四、表设计优化：

1.不留NULL

设计时最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库. 

2.合适的字段类型和长度

一般公司都会限制varchar的长度

3.三大范式

第一范式：数据表的每一列都要保持它的原子特性，也就是列不能再被分割。

第二范式：属性必须完全依赖于主键。

第三范式：所有的非主属性不依赖于其他的非主属性

尽量保证三大范式，但是在很多业务场景下也应该保证适当设计冗余