简单了解SQL优化

优化分析

一般步骤：
发现问题—》分析执行计划—》优化索引—》改写SQL—》数据库垂直、水平切分

问题发现渠道：
1.用户上报性能问题；
2.分析慢查询（在MySQL中执行成功并且符合一定条件的日志，可以通过指定一个时间阈值，只要超过这个值的SQL语句都会记录到日志中）日志发现问题的SQL；
3.数据库时实监控长时间运行的SQL

启动慢查询日志：
配置：
开启配置：

Set global slow_query_log = on/off;

指定日志输出位置：

Set global slow_query_log_file = ‘路径名+文件名’

设置条件：

Set global slow_query_time = xx.xxx秒（可以精确到微秒，设置为0可记录所有成功SQL）

未使用到索引的SQL都记录到慢查询日志中：

Set global log_queries_not_using_indexes = on/off

分析MySQL慢查询日志，这两个工具都可以抽取出SQL慢查询日志的特征：
官方提供：Mysqldumpslow [OPTS…] [LOGS…]

第三方插件（可以打印出慢查询日志中每个SQL的执行计划）：
Pt-query-digest[options] [files] [dsn]

使用percona工具来分析慢查询日志：
首先对慢查询日志进行配置：
1.进入mysql服务器
2.查看long_query_time参数：

show variables like 'long_query_time';`

Mysql默认是10秒以上的慢查询记录到日志中

3.对时间参数进行修改（记录所有SQL；一般工作中设置为0.001也差不多了）：

set global long_query_time = 0

4.通过show variables like ‘slow_query_log’;检测慢查询日志是否启动：

5.查询慢查询日志记录地点：

show variables like  'slow_query_log_file';

6.随便执行几个SQL语句查看慢查询日志的信息：

使用工具分析慢查询日志：

Mysqldumpslow 慢查询日志文件名;
如果出现命令未找到说明没有将命令连接到/usr/bin目录下，所以无法在任意地方执行

解决：
通过find / -name mysql -print命令查看mysql安装位置（或者自己知道安装在哪）

然后通过ln命令将放在Mysql安装目录下的bin目录中的Mysqldumpslow 命令映射一个链接到/usr/bin文件下：
ln -s mysql安装目/bin/mysqldumpslow /usr/bin

此时在执行：

使用pt分析慢查询结果：
pt-query-digest 日志文件名（返回的信息更加丰富）：

通过监控实时获取需要优化的SQL：
监控长时间运行的SQL（如果权限不够的话，只能查看自己这个账号执行的SQL）：

SQL执行计划：

一条SQL执行计划可以看出很多如何优化SQL的信息
通过一条执行计划可以知道：
1.SQL如何访问表中的数据（全表扫描，索引扫描，索引查找）
2.了解SQL如何使用表中的索引，是否使用到正确的索引
3.了解SQL查询类型，是否使用关联查询，子查询

获取SQL执行计划：
在所要分析的SQL语句之前，加入explain关键字；在MySQL8.0以后支持增删改查的分析，在5.6之前只支持对replace（与inserte类似，但如果要插入的数据与表中主键，唯一索引冲突时，replace会把原来的语句会替换成新数据）语句分析：

分析执行计划：

Id：
操作执行的顺序，取值为一组数字或null；如果为数字，说明查询中的SQL语句对数据库对象的操作顺序，ID相同时由上到下执行，ID不同时由大到小执行；如果为null，说明这行数据代表是由另外两个查询通过union操作后产生的结果集

例如下面执行关联查询，相同ID：

不同ID：

Select_type：
执行查询的查询类型，
可取的值：

Table：
数据由哪个表数据的；要么是表名，要么是别名，或者<unionM,N>由ID为M,N查询union产生的结果集，/由ID为N的查询产生的结果；这个ID为N的查询通常来说也是一个子查询，子查询产生的结果集也就一个临时表，所以就会显示/（由N查询出来的导出表）

最后一个查询是查询id为1的查询和查询id为2的查询union之后产生的结果集。

Partitions：
只有在查询分区表的时候，才有意义；当我们查询分区表时，这一列就显示查询分区表的ID；对于非分区表就显示null；

Type：
可以知道查询所使用连接的类型；mysql会认为每一个查询都是连接查询，就算只是一个查询，MySQL也会认为是连接查询；type取值也就可以理解成MySQL访问数据的一种方式，其按照性能从高到低排列；但是如果为null，意味着MySQL可以在优化阶段就取得查询结果，都不用在查询阶段去访问表或者索引，但是这种情况很少见：

Possible_Keys：
查询中可能用到哪些索引
Key：
MySQL在优化中实际所使用的索引；当key中有值，Possible_Keys是null时，表示使用了覆盖索引
Key_len：
索引使用的字节数；MySQL所能使用到的索引最大的可能长度；Key_len长度由表中定义的字段的长度来计算的，并不是实际长度
Ref：
表示哪些列或者常量被用于通过索引和过滤来进行数据过滤；没有使用到任何索引就是null
Rows：
MySQL根据统计信息预估的扫描的行数；关联查询中表示内嵌循环的次数，循环次数越多，性能越差
Filtered：
显示MySQL预估的返回条件的数据集占其所扫描数据行的百分比，在一定程度上评估查询成本，值越高，效果越好

Extra：

SQL优化手段：

优化SQL查询所涉及到的表中的索引；利用索引来获取信息，不用对整个表进行扫描。

优化SQL方法一索引：
告诉存储引擎如何快速的查找到所需要的数据；（相当于通过目录找到对应的章节，如果没有目录我们需要从头翻起）所以MySQL的索引是在存储引擎层实现的，这就决定了不同的存储引擎的索引工作方式是不同的。

Innodb支持的索引类型：
1.Btree索引：MySQL中最常用的索引类型，一般默认都是这个索引

特点：使用B+树来存储数据，每一个叶子节点中，都包含了指向下一个叶子节点的指针，方便进行叶子结点的便利；适用于全值匹配查询（在后面加一个条件判断where xxx = xxx）。
适合处理范围查找（between and < >）
从索引的最左侧列开始匹配查找列（针对复合索引），MySQL会自动调节顺序来适合索引，从而利用索引。
限制：
（1）只能从最左侧开始按索引键的顺序使用缩影，不能跳过索引键
（2）Not in 和 <>操作无法使用索引
（3）索引列上不能使用表达式或函数
2.自适应hash索引：这个索引是innodb为了优化查询性能而自动建立的
3.全文索引：主要用于字符串数据列，对中文支持不友好
4.空间索引：点切面，空间数据类型的索引

适合建立索引的列：
1、在where子句中的列建立索引，还要考虑这列是否有筛选性，即是否有很多的重复值，如果全是不相同的，说明筛选性很好；在筛选性不好的地方用索引用处不大，可以用explain进行测试，一般通过计算去重某个字段的数据除以总行数来计算某个字段的可筛选性，越大越好。
2、在order by、group by、distinct中的字段建立索引；可以提高排序的性能，避免使用临时表

但是也有使用要求：
1、索引的列的顺序和order by的顺序要完全一致，索引中列的方向（升降序）和order by规定的方向也要完全一致，在多个表的关联查询中，order by中的字段要全部在关联表中的第一张表中。
2、使用JOIN的关联列也要建立好索引。

选择符合索引建的顺序：
1、区分度最高的列放在联合索引的最左侧
2、使用最频繁的列放在联合索引的最左侧
3、尽量把字段长度小的列放在联合索引的最左侧

注意事项：
1、索引并不是越多越好；会降低插入和更新的效率，有多个索引，MySQL会对每个索引进行分析选择性能好的索引进行使用。
2、使用in列表查询可以用索引
3、查询过滤顺序不必同索引键顺序相同才可以使用到索引

优化SQL方法二改写SQL
改写原则：
1、使用outer join代替not in（MySQL8.0以后自动会将not in优化）
2、使用CTE代替子查询；简化SQL
3、拆分复杂的SQL为简单的小SQL
4、用计算列优化查询