【MySQL】之性能优化-索引+查询优化

索引

A、索引真的去了解

正确的创建合适的索引是提升数据库的查询性能的基础。
索引是为了加速对表中数据的检索而创建的一种分散存储的数据结构。是什么

为什么
1、极大地减少存储引擎需要扫描的数据量。
2、把随机io编程顺序io。
3、帮助我们在分组、排序等操作时避免使用临时表。

B、索引为什么选B+Tree？

二叉树到平衡二叉树再到B-Tree、B+Tree。

平衡二叉树

B-Tree（多路平衡查找树）
解决的上边的两个问题。
注意：索引不要建太多，增删改时会慢。为了满足绝对平衡，做些复杂的操作。

B+Tree（增强版）
优点

区别

左闭合、数据在叶子上、顺序性。

C、在mysql中B+Tree的表现形式

在Myisam中：索引放在.MYI中，数据放在.MYD中。


在Innodb中：索引和数据都放在.IBD一个文件中。


在Innodb和Myisam两个引擎中呈现的区别。

在Innodb中索引和数据放在一个文件中，当以辅助索引查询时先找到对应的主键再按照主键索引找到数据。Innodb的初衷是这样，修改表儿时为了不对辅助索引进行整理。

D、索引知识补充

列的离散性

对于索引来讲列的离散性越大越好。

最左匹配原则

对索引中关键字进行计算(对比)，一定是从左往右依次进行， 且不可跳过。

复合索引

单列索引 节点中关键字[name]
联合索引 节点中关键字[name,phoneNum]
单列索引是特殊的联合索引

联合索引列选择原则
1，经常用的列优先 【最左匹配原则】
2，选择性(离散度)高的列优先【离散度高原则】
3，宽度小的列优先【最少空间原则】

覆盖索引

如果查询列可通过索引节点中的关键字直接返回，则该索引为覆盖索引。
覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能

E、索引了解回馈

索引列的数据长度能少则少。
索引一定不是越多越好，越全越好，一定是建合适的。
匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引;
Where 条件中 not in 和 <>操作无法使用索引;
匹配范围值，order by 也可用到索引;？
多用指定列查询，只返回自己想到的数据列，少用select *;
联合索引中如果不是按照索引最左列开始查找，无法使用索引;
联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引;
联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引;

哈哈，索引又多懂了一大截。
每次收获完都觉得自己明白了，真到了再一次学习的时候发现还能发现有成长的空间。

Mysql体系结构

Client Connectors
接入方 支持协议很多

Management Serveices & Utilities
系统管理和控制工具，mysqldump、 mysql复制集群、分区管理等

Connection Pool
连接池:管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求
SQL Interface
SQL接口:接受用户的SQL命令，并且返回用户需要查询的结果

Parser解析器，SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的

Optimizer
查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化

Cache和Buffer(高速缓存区)
查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

pluggable storage Engines
插件式存储引擎。存储引擎是MySql中具体的与文件打交道的子系统

file system
文件系统，数据、日志(redo，undo)、索引、错误日志、查询记录、慢查询等

MySQL查询优化详解

查询执行的路径

查询优化-查询执行的路径

1、mysql 客户端/服务端通信

Mysql客户端与服务端的通信方式是“半双工”;
全双工:双向通信，发送同时也可以接收。
半双工:双向通信，同时只能接收或者是发送，无法同时做操作。
单工:只能单一方向传送。

半双工通信:
在任何一个时刻，要么是有服务器向客户端发送数据，要么是客户端向服务端发 送数据，这两个动作不能同时发生。所以我们无法也无需将一个消息切成小块进 行传输。

特点和限制:
客户端一旦开始发送消息，另一端要接收完整个消息才能响应。
客户端一旦开始接收数据没法停下来发送指令。

查看状态

对于一个mysql连接，或者说一个线程，时刻都有一个状态来标识这个连接正在做什么
查看命令 show full processlist / show processlist https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html (状态全集)

Sleep
线程正在等待客户端发送数据
Query
连接线程正在执行查询
Locked
线程正在等待表锁的释放
Sorting result
线程正在对结果进行排序
Sending data
向请求端返回数据
可通过kill {id}的方式进行连接的杀掉

2、查询缓存–Mysql默认关闭

写在前边–默认关闭的原因：

1. 查前检查是否命中缓存，浪费计算资源。
2. 查完，没在缓存中存入缓存中，带来额外的系统消耗。
3. 对表写或修改时表所有的缓存失效。
4. 查询缓存很大或碎片很多时，系统消耗。

工作原理：

1. 缓存SELECT操作的结果集和SQL语句;
2. 新的SELECT语句，先去查询缓存，判断是否存在可用的记录集;

判断标准:

1. 与缓存的SQL语句，是否完全一样，区分大小写

设置缓存：

1. query_cache_type
2. 值:0 -– 不启用查询缓存，默认值;
3. 值:1 -– 启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集 都可以缓存起来，供其他客户端使用，加上 SQL_NO_CACHE将不缓存
4. 值:2 -– 启用查询缓存，只要查询语句中添加了参数:SQL_CACHE，且符合查询 缓存的要求，客户端的查询语句和记录集，则可以缓存起来，供其他客户端使用
5. query_cache_size
6. 允许设置query_cache_size的值最小为40K，默认1M，推荐设置 为:64M/128M;
7. query_cache_size
8. 允许设置query_cache_size的值最小为40K，默认1M，推荐设置 为:64M/128M;
9. query_cache_limit
10. 限制查询缓存区最大能缓存的查询记录集，默认设置为1M
11. show status like ‘Qcache%’ 命令可查看缓存情况

#### 不会缓存的情况

1. 加上 SQL_NO_CACHE将不缓存。
2. 当查询语句中有一些不确定的数据时，则不会被缓存。如包含函数NOW()，CURRENT_DATE()等类似的函数，或者用户自定义的函数，存储函数，用户变 量等都不会被缓存。
3. 当查询的结果大于query_cache_limit设置的值时，结果不会被缓存
4. 对于InnoDB引擎来说，当一个语句在事务中修改了某个表，那么在这个事务 提交之前，所有与这个表相关的查询都无法被缓存。因此长时间执行事务， 会大大降低缓存命中率
5. 查询的表是系统表
6. 查询语句不涉及到表

适用场景

以读为主的业务，数据生成之后就不常改变的业务
比如门户类、新闻类、报表类、论坛类等

3、查询优化处理

31、查询优化处理的三个阶段:

1. 解析sql

通过lex词法分析,yacc语法分析将sql语句解析成解析树
https://www.ibm.com/developerworks/cn/linux/sdk/lex/

2. 预处理阶段

根据mysql的语法的规则进一步检查解析树的合法性，如:检查数据的表和列是否存在，解析名字和别名的设置。还会进行权限的验证。

3. 查询优化器

优化器的主要作用就是找到最优的执行计划

32、查询优化器如何找到最有执行计划？

1. 使用等价变化规则
2. 5=5anda>5 改写成a>5
3. a<b and a=5 改写成b>5 and a=5
4. 基于联合索引，调整条件位置等
5. 优化count 、min、max等函数
6. min函数只需找索引最左边
7. max函数只需找索引最右边
8. myisam引擎count(*)
9. 覆盖索引扫描
10. 子查询优化
11. 提前终止查询
12. 用了limit关键字或者使用不存在的条件
13. IN的优化–or匹配所有
14. 先进性排序，再采用二分查找的方式

Mysql的查询优化器是基于成本计算的原则。他会尝试各种执行计划。 数据抽样的方式进行试验(随机的读取一个4K的数据块进行分析)
**

33、执行计划

a、id

select****查询的序列号，标识执行的顺序

1. id相同，执行顺序由上至下。
2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
3. id相同又不同即两种情况同时存在，id如果相同，可以认为是一组，从上往下顺序 执行;在所有组中，id值越大，优先级越高，越先执行。
   

b、select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等

1. SIMPLE:简单的select查询，查询中不包含子查询或者union
2. PRIMARY:查询中包含子部分，最外层查询则被标记为primary
3. SUBQUERY/MATERIALIZED:SUBQUERY表示在select 或 where列表中包含了子查询。MATERIALIZED表示where 后面in条件的子查询
4. UNION:若第二个select出现在union之后，则被标记为union;
5. UNION RESULT:从union表获取
6. 结果的select

c、table

查询涉及到的表

1. 直接显示表名或者表的别名
2. <unionM,N> 由ID为M,N 查询union产生的结果
3. 由ID为N查询生产的结果
   

d、type

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是:
system > const > eq_ref > ref > range > index > ALL
**

1. system:表只有一行记录(等于系统表)，const类型的特例，基本不会出现，可以忽略不计

2. const:表示通过索引一次就找到了，const用于比较primary key 或者 unique索引

3. eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描

4. ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质是也是一种索引访问

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

6. index:Full Index Scan，索引全表扫描，把索引从头到尾扫一遍

7. ALL:Full Table Scan，遍历全表以找到匹配的行

e、possible_keys

查询过程中有可能用到的索引

f、key

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

g、rows

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

h、filtered

它指返回结果的行占需要读到的行(rows列的值)的百分比 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

i、Extra–十分重要的额外信息

1. **Using filesort **:mysql对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。
2. Using temporary: 使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于**order by **或 group by。
3. Using index:
   表示相应的select操作中使用了覆盖索引(Covering Index)，避免了访问表的数据行，效率高。
4. Using where : 使用了where过滤条件。
5. select tables optimized away: 基于索引优化MIN/MAX操作或者MyISAM存储引擎优化**COUNT(*)**操作，不必等到执行阶段在进行计算，查询执行 计划生成的阶段即可完成优化。

4、查询执行引擎

调用插件式的存储引擎的原子API的功能进行执行计划的执行

5、返回客户端

• 有需要做缓存的，执行缓存操作

• 增量的返回结果:

  • 开始生成第一条结果时,mysql就开始往请求方逐步返回数据
  • 好处: mysql服务器无须保存过多的数据，浪费内存。用户体验好，马上就拿到了数据

定位慢sql

1、业务驱动2、测试驱动3、慢查询日志

A、慢查询日志配置

show variables like 'slow_query_log’
set global slow_query_log = on
set global slow_query_log_file = ‘/var/lib/mysql/gupaoedu-slow.log’ set global log_queries_not_using_indexes = on
set global long_query_time = 0.1 (秒)

B、日志分析

Time :日志记录的时间
User@Host:执行的用户及主机

Query_time:查询耗费时间 Lock_time 锁表时间 Rows_sent 发送给请求方的记录条数Rows_examined 语句扫描的记录条数
**
SET timestamp 语句执行的时间点
select … 执行的具体语句

C、日志分析工具