mysql高级--索引优化分析

【笔记于学习尚硅谷课程所作】

SQL优化步骤

1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. showprofile查询SQL在Mysq1服务器里面的执行细节和生命周期情况
4. SQL数据库服务器的参数调优。

1、索引简介

1.1 索引是什么？

​  MySQL官方对索引的定义为:索引(Index) 是帮助MySQL高效获取数据的数据结构。

​ 索引的本质:索引是数据结构

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

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

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

​  我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不- -定是二叉的)结构组织的索引。

​  索引的分类：单值索引、唯一索引、复合索引

​  索引的结构：BTree索引、Hash索引、full-text全文索引、R-Tree索引

1.2 基本语法

创建索引1
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

创建索引2
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

删除
DROP INDEX [indexName] ON mytable;

查看
SHOW INDEX FROM table_ name\G

1.3 需要建立索引的情况

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题(在高并发下倾向创建组合索引)
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

2、性能分析（Explain）

2.1 Explain简介

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

​ 具体功能：

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

如何使用：Explain+SQL语句

2.2 Explain各字段解释

字段	解释
id	select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
select_type	查询的类型
table	显示这一行的数据是关于哪张表的
type	显示查询使用了哪种访问类型
possible_keys	显示可能应用在这张表中的索引，一个或多个。（不一定被实际使用）
key	实际使用的索引。如果为NULL,则没有使用索引。（查询中若使用了覆盖索引，则该索引和查询的select字段重叠）
key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。（显示的值为索引字段的最大可能长度，并非实际使用长度，即key_ len是根据表定义计算而得，不是通过表内检索出的）
ref	显示索引的哪一列被使用了， 如果可能的话。是一个常数。哪些列或常量被用于查找索引列上的值。
rows	根据表统计信息及索引选用情况，大致估算IB找到所需的记录所需要读取的行数。
Extra	包含不适合在其他列中显示但十分重要的额外信息

2.2.1 id

• id相同，执行顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id相同不同，同时存在

2.2.2 select_type

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

id	select_type	含义
1	SIMPLE	简单的select查询,查询中不包含子查询或者UNION
2	PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为
3	SUBQUERY	在SELECT或WHERE列表中包含了子查询
4	DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生)。MySQL会递归执行这些子查询，把结果放在临时表里。
5	UNION	若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中，外层SELECT将被标记为: DERIVED
6	UNION RESULT	从UNION表获取结果的SELECT

2.2.3 type

​ 显示查询使用的访问类型。

​ 从好到坏依次是：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快，因为索引文件通常比数据文件小。(也就是说虽然ll和Index都是读全表，但index 是从索引中读取的，而all是 从硬盘中读的)
all	Full Table Scan,将遍历全表以找到匹配的行

2.2.4 Extra

1. Using flesort：（文件内排序【坏】）说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序”。
2. Using temporary：（使用临时表【坏】）使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by.
3. USING index：（使用了覆盖索引【好】）表示相应的select操作中使用了覆盖索引(Covering Index)， 避免访问了表的数据行(如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现usingwhere,表明索引用来读取数据而非执行查找动作)
4. Using where：表明使用了where过滤
5. using join buffer：使用了连接缓存
6. …

3、索引优化

3.1 索引失效问题

• 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))， 减少select*
• mysql在使用不等于(!=或者<> )的时候无法使用索引会导致全表扫描
• is null ,is not null也无法使用索引
• like以通配符开头(%b…)mysq|索引失效会变成全表扫描的操作（使用覆盖索引解决，%like%索引失效问题）
• 字符串不加单引号索引失效
• 少用or,用它来连接时会索引失效

口诀：

全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖索引不写星;
不等空值还有or，索引失效要少用;
VAR引号不可丢，SQL高级也不难;

3.2 一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的