MySQL学习(二)

一. Mysql优化SQL步骤

1.查看SQL执行频率

show status like 'Com_______'; # 这些参数对于所有存储引擎的表操作都会进行累计。 
show status like 'Innodb_rows_%'; # 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。 

2.定位低效率执行SQL

1.慢查询日志 :: 通过慢查询日志定位那些执行效率较低的 SQL 语句;(详细讲解)
2.show processlist :令查看当前MySQL在进行的线程，包括线程的状态、是否 锁表等;

3. explain分析执行计划

字段	含义
id select	查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、 PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语 句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

3.1 explain 之id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
id 情况有三种 :

1. id 相同表示加载表的顺序是从上到下。
2. id 不同id值越大，优先级越高，越先被执行。
3. id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越 大，优先级越高，越先执行。

3.2 explain 之 select_type

表示 SELECT 的类型，常见的取值:

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查 询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子 查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

3.4 explain 之 table

展示这一行的数据是关于哪一张表的

explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数 据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的多表关联查询，关联查询出的记录只有一条。常见 于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某 个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

性能：

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

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

一般来说， 我们需要保证查询至少达到 range 级别， 好达到ref 。

3.5 explain 之 key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。

key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

key_len : 表示索引中使用的字节数， 该值为索引字段大可能长度，并非实际使用长度，在不损失精确性的前 提下， 长度越短越好

3.6 explain 之 rows

扫描行的数量.

3.7explain 之 extra

其他的额外的执行计划信息，在该列展示 。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index	表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

4. show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时 帮助我们了解时间都耗费到哪里去了。
首先，我们可以执行一系列的操作，如下图所示：

show databases; 
use db01;
show tables; 
select * from tb_item where id < 5; 
select count(*) from tb_item;

执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

show profiles ;

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间.

5.trace分析优化器执行计

…

---

二. 索引使用

1. 全值匹配

全值匹配 ，对索引中所有列都指定具体值。
该情况下，索引生效，执行效率高。

2. 最左前缀匹配

如果索引了多列，要遵守左前缀法则。指的是查询从索引的左前列开始，并且不跳过索引中的列。

比如，对表table的A，B，C建立联合索引，

CREATE INDEX index_A_B_C ON table(A，B，C)

走索引的情况大致有：

# 走索引(A，B，C)中的A
select * from table where a=？；
# 走索引(A，B，C)中的A,B
select * from table where a=？and b= ？ ；
# 走索引(A，B，C)中的A,B,C
select * from table where a=？and b= ？ and c =？；
# 走索引(A，B，C)中的A,B,C 和顺序无关，只看是否包含最左边的列
select * from table where b= ？ and c =？and a=？； 
# 走索引(A，B，C)中的A
select * from table where  a=？ and  c =？；

3. 索引失效的情况：

3.1 范围查询

范围查询右边的列，不能使用索引

# 走索引(A，B，C)中的A,B
select * from table where a=？ and b>? and c >?；

3.2 字段运算

不要在索引列上进行运算操作， 索引将失效。

3.3 字符串不加单引号

字符串不加单引号，造成索引失效。
没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效.

3.4 select *造成回表查询

尽量使用覆盖索引，避免select *
尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

# explain会提示using index condition 
select * from  table where a = ?

如果查询列，超出索引列，也会降低性能(回表查询)。

# explain会提示 using index ; using where 
select a,b,c from  table where a = ?

TIP :
using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据

3.5 or连接列导致不走索引

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

explain select * from tb_seller where name='张三' or createtime = '2088-01-01 12:00:00'G; 

3.6 以%开头的Like模糊查询

#走索引
select * from table where a like 'zhangsan%';
#不走索引
select * from table where a like '%zhangsan';
#不走索引
select * from table where a like '%zhangsan%';

如何解决这种问题：通过覆盖索引来解决

#不走索引 
select id，A，B，C from table where a like '%zhangsan%';

3.7 如果MySQL评估使用索引比全表更慢，则不使用索引

mysql评估全表扫描快的话，会不走索引。

3.8 is NULL ， is NOT NULL 有时索引失效

可以用3.7来理解，如果mysql底层评估全表扫描快的话，会不走索引。
如果数据库中该字段null值占绝大部分，用is not null 来作为查询条件，是走索引的；
如果数据库中该字段null值占极小部分，用is null 来作为查询条件，是走索引的；

3.9 in走索引，not in索引失效

3.10 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ：
name
name + status
name + status + address

创建单列索引:

create index idx_seller_name on tb_seller(name); 
create index idx_seller_status on tb_seller(status); 
create index idx_seller_address on tb_seller(address);

数据库会选择一个优的索引（辨识度高索引）来使用，并不会使用全部索引 。
如何评价一个字段辨识度高不高：
比如性别字段辨识度就是低的，而身份证号辨识度是高的。

3.11 查看索引使用情况

show status like 'Handler_read%';   
show global status like 'Handler_read%'; 

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低 越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的 性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列， 该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。 你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应 该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说 明你的表索引不正确或写入的查询没有利用索引。