共计 4296 个字符，预计需要花费 11 分钟才能阅读完成。
笔者实习小白，公司让我优化一下数据库，这几天复习了一下数据库的相干常识并且查阅了一些材料，筹备过几天把高性能 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；
