MySQL优化查阅总结

  笔者实习小白，公司让我优化一下数据库，这几天复习了一下数据库的相干常识并且查阅了一些材料，筹备过几天把高性能MySQL看一下。

MYSQL优化的次要形式之一：减少索引
一、索引：
1.定义：
  是一种数据结构。（残缺定义：数据自身之外，数据库还保护着一个满足特定查找算法的数据结构，这些数据结构以某种形式指向数据，这样就能够在这些数据的根底上实现高级查找算法，这种数据结构就是索引）

2.分类：
  BTREE索引（B+树是一个均衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针互相链接。）在B+树上的惯例检索，从根节点到叶子节点的搜寻效率根本相当，不会呈现大幅稳定，而且基于索引的程序扫描时，也能够利用双向指针疾速左右挪动，效率十分高。因而，B+树索引被广泛应用于数据库、文件系统等场景。

<center>图1 B+树</center>
哈希索引：只须要做等值比拟查问，而不蕴含排序或范畴查问的需要，适宜应用哈希索引。比方对于身份证号的匹配。哈希索引定义：哈希索引基于哈希表实现，只有准确匹配索引的所有列的查问才无效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保留指向每个数据行的指针。

<center>图2 哈希构造</center>
3.索引劣势：不用检索全表，进步检索效率，（应用相似二分查找的形式）升高数据库的IO老本；升高数据排序的老本，升高了CPU的耗费。

4.索引劣势：索引也是占空间的；尽管进步了查问速度，然而会升高表的更新速度，在表更新时，也会对索引更新；

5.须要索引的状况：
主键主动建设惟一索引；
频繁查找的字段；
查问中与其余表关联的字段，通过外键关系建设索引；
频繁更新的字段不要建设索引；
where条件里用不到的不要建设索引；

二、MYSQL优化查问语句EXPLAIN:
 MySQL 提供了一个 EXPLAIN 命令, 它能够对 SELECT 语句进行剖析, 并输入 SELECT 执行的详细信息, 以供开发人员针对性优化.
EXPLAIN 命令用法为：在 SELECT 语句前加上 EXPLAIN，例如:

<center>图2 EXPLAIN利用</center>
 EXPLAIN进去的信息有10列，别离是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,上面对这些字段呈现的可能进行解释：

1.id

它是SQL执行的程序的标识,从大到小的执行

2.select_type
示意查问中每个select子句的类型
(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(一个子查问的后果不能被缓存，必须从新评估外链接的第一行)

3.table
显示这一行的数据是对于哪张表的，有时不是实在表名字，subquery示意子查问的表。

4.type
示意MySQL在表中找到所需行的形式，又称“拜访类型”。
罕用的类型有： ALL, index, range, ref, eq_ref, const, system, 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在优化过程中合成语句，执行时甚至不必拜访表或索引，例如从一个索引列里选取最小值能够通过独自索引查找实现。

5.possible_keys
指出MySQL能应用哪个索引在表中找到记录，查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问应用。

6.Key
key列显示MySQL理论决定应用的键（索引）
如果没有抉择索引，键是NULL。要想强制MySQL应用或漠视possible_keys列中的索引，在在FROM 表名前面增加语句FORCE INDEX (索引名) 或者IGNORE INDEX (索引名)。

7.key_len
示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非理论应用长度，即key_len是依据表定义计算而得，不是通过表内检索出的）不损失精确性的状况下，长度越短越好

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

9.rows
示意MySQL依据表统计信息及索引选用状况，估算的找到所需的记录所须要读取的行数

10.Extra
该列蕴含MySQL解决查问的详细信息,有以下几种状况：
Using where：列数据是从仅仅应用了索引中的信息而没有读取理论的口头的表返回的，这产生在对表的全副的申请列都是同一个索引的局部的时候，示意mysql服务器将在存储引擎检索行后再进行过滤。
Using temporary：示意MySQL须要应用长期表来存储后果集，常见于排序和分组查问
Using filesort：MySQL中无奈利用索引实现的排序操作称为“文件排序”
Using join buffer：改值强调了在获取连贯条件时没有应用索引，并且须要连贯缓冲区来存储两头后果。如果呈现了这个值，那应该留神，依据查问的具体情况可能须要增加索引来改良能。
Impossible where：这个值强调了where语句会导致没有符合条件的行。
away：这个值意味着仅通过应用索引，优化器可能仅从聚合函数后果中返回一行。

三、实例：
<br/>mapper文件夹中xml文件局部sql语句为例：

SELECT        student.REAL_NAME stuName,        teacher.REAL_NAME AS realName,        r.EXPERIENCE_ID AS experienceId,        r.USER_ID AS userId,        r.EXPERIENCE_CONTENT AS experienceContent,        r.ASSESSOR_TYPE AS assessorType,        r.REASION AS reasion,        r.DEPART_COMMENT AS departComment,        r.DEPART_COMMENT_ID AS departCommentId,        r.DEPART_COMMENT_DATE AS departCommentDate,        r.UPDATE_TIME AS updateTime    FROM        tb_stu_teachexperience r    LEFT JOIN t_rygl_user student          ON r.USER_ID = student.USER_ID    AND student.IS_DEL = '0'        LEFT JOIN t_rygl_user teacher          ON r.DEPART_COMMENT_ID = teacher.USER_ID    AND teacher.IS_DEL = '0'    WHERE        r.IS_DEL = '0'         AND r.ASSESSOR_TYPE IN (4,8)        /*（此处能够针对tb_stu_teachexperience表的IS_DEL字段           和ASSESSOR_TYPE字段减少复合索引，进步检索速度）*/    AND r.USER_ID IN (        SELECT            rc.STUDENT_ID        FROM            t_rygl_choicestuandteach rc        WHERE            rc.TEACH_ID = #{userId}                    AND rc. STATUS = 2                     AND rc.IS_DEL = 0               /*（此处能够针对tb_stu_teachexperience表的         STATUS字段和IS_DEL字段减少复合索引，进步检索速度）*/                )          <if test="assessorType == null or assessorType == ''">        </if>        <if test="assessorType != null and assessorType != ''">            AND r.ASSESSOR_TYPE = #{assessorType}        </if>         <if test="stuName != null and stuName !=''">            AND student.REAL_NAME LIKE CONCAT('%',#{stuName,jdbcType=VARCHAR},'%')   /*（此处为全含糊搜寻，如果须要保留依照姓氏和依照名字都能搜寻的性能，能够思考    将t_rygl_user表中的REAL_NAME字段的长度缩小，原长度为50，能够缩小到15）*/        </if>        ORDER BY r.EXPERIENCE_ID DESC    /*（order by之后的字段也能够建设索引，然而此处字段为主键，所以无需优化）*/

未优化查问信息：

<center>图3 未优化sql语句</center>
减少索引界面：

<center>图4 表tb_stu_teachexperience增加索引</center>

<center>图5 表t_rygl_choicestuandteach增加索引</center>

优化后查问信息：

<center>图6 增加索引</center>
能够看到，后果信息中的type由原来的存在两个all类型（全表扫描），变成了当初的Ref类型；最初一行中rows（须要检索的行数）由原来的405变为389；