索引优化分析，必备掌握知识⚡⚡⚡（熬夜3个晚上写的，建议必读）

<select_list>
from
	<left_table><join_type>
join <right_table> on <join_condition>
where
	<where_condition>
group by
	<group_by_list>
having
	<having_condition>
order by
	<order_by_condition>
limit <limit_number>

2）机读SQL

1 from <left_table>
2 on <join_condition>
3 <join_type> join <right_table> 
4 where <where_condition>
5 group by <group_by_list>
6 having <having_condition>
7 select
8 distinct <select_list>
9 order by <order_by_condition>
10 limit <limit_number>

Join图

如图，可以看出有7种join连接的方式，分别对应着sql语句，不明白可以试着写一下，理解理解。

三、⚡<索引>

索引是什么?

1）Mysql官方对索引的定义为：索引（Index）是帮助Mysql高效获取数据的数据结构

2）可以简单理解为“排好序的快速查找数据结构”。

详解：在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

为了加快Col2的查找，可以维护一个右边所示的二叉树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉树查找在一定的复杂度内获取到响应数据，从而快速的检索出符合条件的记录。

3）一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在硬盘上

4）我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复核索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

优势

1.提高数据检索效率，降低数据库的IO成本

2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

1.占用空间（实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的）

2.会降低更新表的速度。（虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加索引列的字段）

3.索引知识提高效率的一个因素，如果Mysql有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询

Mysql索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

基本语法

-- 创建索引语法   注：如果是char、varchar类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length
create [unique] index indexName on mytable(columnname(length));
alter mytable add [unique] index [indexName] on (columnname(length));

-- 删除索引语法
drop index [indexName] on mytable;

-- 查看索引语法
show index from table_name;


-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null。
alter table tbl_name add primary key(column_list);

-- 该语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
alter table tbl_name add unique index_name(column_list);

-- 添加普通索引，索引值可出现多次
alter table tbl_name add index index_name(column_list);

-- 该语句指定了索引为FULLTEXT，用于全文索引
alter table tbl_name add fulltext index_name(column_list);

Mysql索引结构

BTree索引、Hash索引、FULLTEXT全R-文索引、R-Tree索引

哪些情况需要创建索引？（面试重点）

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引

3.查询中与其他表关联的字段，外键关系建立索引

4.频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引）

5.Where条件里用不到的字段不创建索引

6.单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

8.查询中统计或分组字段

哪些情况不要创建索引？（面试重点）

1.表记录太少

2.经常增删改的表（提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE.因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件）

3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。

注：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

四、⚡<性能分析>

MySQL Query Optimizer （MySQL 查询优化器）

1.Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见是DBA认为是最优的，这部分最耗费时间）

2.当客户端向Mysql请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySql Query Optimizer时，Mysql Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

1.CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候

2.IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

3.服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain

1）Explain是什么？（查看执行计划）

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理你的SQL语句。官网介绍-查看Optimization。

2）Explain能干嘛？

a.表的读取顺序、

b.数据读取操作的操作类型、

c.哪些索引可以使用、

d.哪些索引被实际使用、

e.表之间的引用、

f.每张表有多少行被优化器查询

3)Explain怎么玩？

语法: Explain + SQL语句；

执行计划包含息：如图

名字段解释

1）id

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

三种情况：

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

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

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

2）select_type

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

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

3）table

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

4）type

Explain_type图，如下：

显示查询使用了何种类型，从最好到最差依次是：

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

类型	说明	示例
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，将遍历全表以找到匹配的行

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

5）possible_keys

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

6）key

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

查询中若使用了覆盖索引，则该索引仅出现在key列表中。

7）key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好；

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

8）ref

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

9）rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

10）Extra

包含不适合在其他列中显示但十分重要的额外信息。注：下图中的展示方式是因为" \G "改变了展示方式。

名称	说明	示例
1.Using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySql中无法利用索引完成的排序操作称为“文件排序” （遇到这种情况危险，九死一生）
2.Using temporary	使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。（遇到这种情况危险，十死没生）
3.Using index	表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。（遇到这种情况好事）
4.Using where	表明使用了where过滤	暂无
5.using join buffer	使用了连接缓存	暂无
6.impossible where	where子句的值总是false，不能用来获取任何元组
7.select tables optimized away	在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT()操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。（了解即可）*	暂无
8.distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值得动作	暂无