数据库高级知识——索引优化分析（二）

4.性能分析

4.1 MySQL常见瓶颈

CPU :SQL中对大量数据进行比较、关联、排序、分组
IO：实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。查询执行效率低，扫描过多数据行。
锁:不适宜的锁的设置，导致线程阻塞，性能下降。死锁，线程之间交叉调用资源，导致死锁，程序卡住。
服务器硬件的性能瓶颈：top,free, iostat和vmstat来查看系统的性能状态

4.2 Explain

1.Explain是什么(查看执行计划)

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。
分析你的查询语句或是表结构的性能瓶颈

官网介绍

2. Explain能干嘛

表的读取顺序
哪些索引可以使用
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

3.explain怎么玩

Explain + SQL语句

mysql> select * from students;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhao1 |
|    2 | zhao2 |
|    3 | zhao3 |
+------+-------+
3 rows in set (0.00 sec)

mysql> explain select * from students;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

执行计划包含的信息

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

建表脚本
 CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
 
 
 INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
 
 INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
  
 INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
    
 INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

4.各字段解释

4.1 id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：

1.id相同，执行顺序由上至下

id相同，执行顺序由上至下  
此例中 先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id 。
而t2.id 的结果建立在 t2.id=t3.id 的基础之上。

2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3.id相同不同，同时存在

id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行
 
衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，
因为衍生的表没有建立索引）

4.2 select_type

select_type有哪些?

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

类型	说明
SIMPLE	简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生) 。MySQL会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY	在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUREY	无法被缓存的子查询
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

4.3 table

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

4.4 type 访问类型

显示查询使用了何种类型，
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： 
 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。

类型	说明
system	表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const	表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range	只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
index	Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
all	Full Table Scan，将遍历全表以找到匹配的行

4.5 possible_keys

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

4.6 key

实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引和查询的select字段重叠

对比下图两个 sql 语句。和 key 的值：当查询具体某一字段时，且那个字段有索引时，key 值会显示为索引。 

4.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

EXPLAIN SELECT * FROM emp WHERE emp.deptno=109 AND emp.`ename`='AvDEjl'

如何计算

总结一下：char(30) utf8 --> key_len = 30*3 +1  表示 utf8 格式需要  *3 (跟数据类型有关)   
                 允许为 NULL  +1  ，不允许 +0
                 动态类型 +2  (动态类型包括 : varchar , detail text() 截取字符窜)

第一组：key_len=deptno(int)+null + ename(varchar(20)*3+动态  =4+1+20*3+2= 67
第二组：key_len=deptno(int)+null=4+1=5 

key_len字段能够帮你检查是否充分的利用上了索引

4.8 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

4.9 rows

rows列显示MySQL认为它执行查询时必须检查的行数。

4.10 Extra

包含不适合在其他列中显示但十分重要的额外信息

5.查询优化

5.1 索引失效

索引的创建：

mysql> CREATE TABLE staffs (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
    ->   age INT NOT NULL DEFAULT 0 COMMENT '年龄',
    ->   pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
    ->   add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
    -> ) CHARSET utf8 COMMENT '员工记录表' ;#staffs表的创建
mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW()); 
mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
mysql> INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());

mysql> ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);#索引的创建

索引的失效：

全值匹配我最爱

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

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

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

is not null 也无法使用索引,但是is null是可以使用索引的

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

少用or,用它来连接时会索引失效

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

索引失效总结：

 假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b后断了
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

5.2 索引优化

单表查询优化

关联查询优化

1、保证被驱动表的join字段已经被索引，被驱动表  join 后的表为被驱动表  (需要被查询)

2、left join 时，选择小表作为驱动表，大表作为被驱动表。
	但是 left join 时一定是左边是驱动表，右边是被驱动表
3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
    mysql 自动选择。小表作为驱动表。因为 驱动表无论如何都会被全表扫描？。所以扫描次数越少越好
4、子查询尽量不要放在被驱动表，有可能使用不到索引。

子查询优化

有索引的情况下 用  inner join 是最好的  其次是 in  ，exists最糟糕
 
无索引的情况下用 
小表驱动大表 因为join 方式需要distinct ，没有索引distinct消耗性能较大 
所以  exists性能最佳 in其次  join性能最差？
 
无索引的情况下大表驱动小表
in 和 exists 的性能应该是接近的  都比较糟糕  exists稍微好一点 超不过5%     
但是inner join 优于使用了 join buffer 所以快很多
如果left join 则最慢

order by关键字优化

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

分页查询的优化—limit

GROUP BY关键字优化
group by实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了。

去重优化

尽量不要使用 distinct 关键字去重：优化

详细内容：索引优化