MySQL高级第二章——索引优化分析

一、SQL性能下降原因

　　1.等待时间长？执行时间长？

　　　　可能原因：

　　　　　　查询语句写的不行

　　　　　　索引失效（单值索引、复合索引）

　　　　　　　　CREATE INDEX index_user_name ON user(name);　（底层做了一个排序）

　　　　　　　　CREATE INDEX index_user_nameEmail ON user(name,email);

　　　　　　技术原理参见：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

　　　　　　　索引入门知识参见：http://blog.csdn.net/xluren/article/details/32746183

　　　　　　 查询关联join太多太乱

　　　　　 服务器调优参数的设置

　　　　　　...

二、常见join连接查询

　　由上文知道，join查询也是SQL性能下降的原因

　　1.SQL执行顺序

　　　　手写SQL

　　　　

　　　　//逐步向下的顺序

　　　　机读SQL

　　　　

　　　　//从FROM开始读

　　　　执行顺序小结：

　　

　　//注意前后关系与同级分支的选择关系

　　2.JOIN理论

　　七种JOIN图：

　　　　

　　　　内连接：两张表同时存在的满足连接条件的记录

　　　 左外连接：两表同时存在满足连接条件的加上A独有的（B以null补足对齐）

　　　 右外连接：与左对称

　　　只要A独有：与左外连接对比，独有的就是左外连接部分中B为null的列

　　　只要B独有：与上对称

　　　全外连接：全部的外连接，也就是左外右外的合体

　　完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。 

　　　要A、B各自的独有：由外连接的概念，可以知道，独有就是有一方没有匹配，只能以null补足的那一部分。

　　SQL演示：　

#内连接
SELECT * FROM tb_empt a INNER JOIN tb_dept b ON a.deptId = b.id;
#左外连接（右连接对称，左表补null）
SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId = b.id;
#左外连接中只要A独有的,只需在ON连接条件后加上WHERE条件（B独有为对称）
SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId = b.id WHERE b.id = NULL
#全外连接(按我们思路写的SQL在MySQL中是不支持的，我们需要手动进行结果集合并：UNION 合并并去重)
--SELECT * FROM tb_empt a FULL OUTER JOIN tb_dept b ON a.deptId = b.id;
SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId = b.id
UNION
SELECT * FROM tb_empt a RIGHT JOIN tb_dept b ON a.deptId = b.id
#只要A、B独有的部分（同样，根据合并结果集的启发，合并两个只要独有的部分）
SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId = b.id WHERE b.id = NULL
UNION
SELECT * FROM tb_empt a RIGHT JOIN tb_dept b ON a.deptId = b.id WHERE a.deptId = NULL

 三、索引简介

　　不仅仅是说像字典前面的目录，方便查找那么简单。

　　1.是什么？

　　【官方】：索引（INDEX）是帮助MySQL高效获取数据的数据结构。

　　　　　　也就是说，是一种排好序的数据结构，目的在于提高查找效率。

　　　　　　它会影响到SQL中ORDER BY 和 WHERE

在数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些结构以某种方式指向数据（类比指针）；
这样，就可以在这些数据结构基础之上，实现高效查找算法，这种数据结构就是索引。

　　可能的数据结构：

　　

 

 　　//之前接触的del_flag，使用逻辑删除，一来是保留数据进行数据分析，二来是如上图所示，为了保证索引的完整不失效。

　　所以说，频繁删改的字段不适合做索引。

　　详细的索引数据的介绍，请参见：http://blog.jobbole.com/86594/

　　索引相关的数据结构知识请参见：http://blog.csdn.net/bitboss/article/details/53219945

 　　2.优势与劣势

　　　　优势——排序和查找

　　　　　　提高检索效率，降低IO成本

　　　　　　降低排序成本，减少CPU消耗

　　　　劣势

　　　　　　　实际上，索引也是一张表，保存主键与索引字段，指向实体记录。过多的索引占用系统空间。

　　　　　　大大提高查询效率，但同时降低了更新的操作（更新数据还需要更改索引）

　　　　　　索引需要根据实际情况变更调整（删了建，建了删）

　　3.MySQL索引分类

　　　　单值索引

　　　　　　包含单列的普通索引

　　　　唯一索引

　　　　　　与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。

　　　　复合索引

　　　　　　　　多个单列索引与单个多列索引的查询效果不同，因为： 执行查询时，MySQL只能使用一个索引，

　　　　　　　会从多个索引中选择一个限制最为严格的索引。遵循 最左前缀 原则。

　　 复合索引参考：https://blog.csdn.net/weixin_45123696/article/details/111314179

　　　基本语法：

（1）创建索引，例如CREATE INDEX 索引的名字 ON tablename (列名1，列名2,...); 
（2）修改表，例如ALTER TABLE tablename ADD INDEX 索引的名字 (列名1，列名2,...); 
（3）创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX 索引的名字 (列名1，列名 2,...) ); 

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name

show index from tblname;
show keys from tblname;

　　4.索引结构

　　　　BTree索引——（多路搜索树，不是二叉树！）

　　　　　　详细B-Tree知识请参见：http://www.cnblogs.com/tgycoder/p/5410057.html

　　　　　　大致原理图如下：

　　　　　　　　

 

　　　　　（以下3类目前作了解）　

　　　　Hash索引

　　　　full text全文索引

　　　　R-Tree索引

　　5.建索引与否的情况分析

　　　　适合创建：

　　　　

　　　　不合适创建：

　　　　

 四、性能分析

　　1.Mysql Query optimizer 

　　　　mysql查询优化器，它所在的位置是我们前文提到过的第三层

查询优化器的任务是发现执行SQL查询的最佳方案

　　　　更多优化器介绍，请参见：http://www.cnblogs.com/hellohell/p/5718238.html

　　2.常见瓶颈

　　　　

　　3.Explain

　　　　是什么？

　　　　　　

 

　　　　　　简称查询计划

　　　　能干什么？

　　　　　　表的读取顺序　　数据读取操作的操作类型　　哪些索引可以使用　　哪些索引被实际使用

　　　　　　表之间的引用　　每张表与多少行被优化器查询

　　　　怎么干？

　　　　　　　EXPLAIN +SQL语句

　　　　

　　　　//EXPLAIN包含的计划信息

　　　EXPLAIN各字段解释

　　　　详细解释可以参见：http://www.cnblogs.com/xuanzhi201111/p/4175635.html

　　　　　　　　　　　　http://www.cnblogs.com/xiaoboluo768/p/5400990.html

　　　　　　id：查询中执行SELECT子句或操作表的顺序（永远是id越大，优先级越高）

　　　　　　

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

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

　　3.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

 　　　　　　//NULL是最后执行（如合并结果集）

　　　　　　select_type：显示查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询

 

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

　　　　table：显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果，也就是前面id的序号)

　　　　type：访问类型，也就是找到需要行的方式 

　　　　　　性能从上到下，依次增加（即ALL性能最差，NULL性能最好）

 

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

　　常见如下：（针对的是所有的查询类型）

　　　　

 　　举例分析可以参见：http://blog.csdn.net/zhuxineli/article/details/14455029

　　　　　　　　　　：http://blog.jobbole.com/103058/

　　　　possible_keys：显示可能应用在这张表上的索引，存在一个或者多个。（但不一定被实际使用）

　　　　Key：显示MySQL实际决定使用的键（索引）（如果为NULL可能是没有索引或建了没用，即索引失效）

　　　　　　　注意：　

　　　　　　　　　　　　意思就是SELECT 后的列与索引列吻合（个数与顺序）。理论上mysql认为用不到索引，实际上是全索引扫描。

　　　　　　可能存在的情况：（以你结婚宴请宾客为例）

　　　　　　　　理论上可能用到的索引，实际上也用到了。（理论宴请的应当到的人，实际也到了）

　　　　　　　　理论上可能用到的索引，实际上没用到。（宴请了，本该到场，实际没到场）

　　　　　　　　理论上没用到的索引，实际上也没用到。（没宴请，也没来）

　　　　　　　　理论上没用到的索引，实际上用到了。（没宴请的人，不请自来）

　　　　key_len：索引中使用的字节数，可以由此计算出索引的长度。（显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

　　　　ref：显示索引的哪一列被使用了，如果可能的话，是一个常数（表示上述表的连接匹配条件，即哪些列（例如test.t1.ID，表示test库的t1表的ID）或常量被用于查找索引列上的值）

　　　　rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。（越小越好）

　　　　Extra：该列包含MySQL解决查询的详细信息,有以下几种情况：

 

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

　　　　

　　覆盖索引的理解：

　　

五、索引优化

　　　　（左连接加右表，这是由左连接的特性决定的；因为左连接左边的一定都有，关键在于右表记录的搜索，所以右表必须建立索引）

　　1.索引失效的情况

　　　　

　　　　其中，索引的最左前缀原则，通俗来说：带头大哥不能死（否则无法使用索引），中间兄弟不能断（例如1，3只能使用1的索引，部分使用）

　　　　失效的实例分析请参见：http://www.jb51.net/article/50649.htm

　　　　　　　　　　　　　　http://blog.csdn.net/zmx729618/article/details/52701370

　　　Note:

　　　　LIKE后使用%abc%形式的模糊查询，索引失效，解决方案是覆盖索引！

　　　　　　其中：WHERE a = 3 AND b LIKE 'kk%' AND c = 2　　——使用索引abc

　　　　　　　　   WHERE a = 3 AND b LIKE '%kk%' AND c = 2　　——使用索引a（b就已经失效）

　　　　　　　　　WHERE a = 3 AND b LIKE 'k%kk%' AND c = 2　　——使用索引abc（定值开头，使用索引，与>的范围是不同的）

　　　　字符串类型不加单引号导致索引失效的原因是MySQL底层完成了一次类型转换，由第3条即可知道原因。

　　　　关于全值匹配我最爱中组合索引出场顺序的疑问，请参见：http://www.cnblogs.com/Yiran583/p/6697996.html 　　（Mysql查询优化器的功劳）

　　　　含有ORDER BY的例如：WHERE a = 3 AND b=2 ORDER BY c3，会用到索引，不会有filesort（不过不会被统计，因为索引的功能有两个：排序和查找）,一般而言，和索引顺序不一致的排序：ORDER BY c,b都会出现 filesort，只有在排序字段已经为定值（WHERE b = 2 ORDER BY c,b）情况下不会。

　　　　含有GROUP BY的，表面上是分组，实则分组前必排序。顺序错乱后会产生临时表（use temporary use filesort）必须优化！

　　小结的口诀如下：