1.优化SQL语句的一般步骤

1.1 查看各种SQL的执行频率

查看服务器状态信息

  1. show [session|global] status

session：当前连接（默认）
global：自数据库上次启动至今

1.2 定位执行效率很低的语句

1.2.1日志查询

通过慢查询日志定位哪些执行效率较低的语句，在查询结束以后才记录

1.2.2 命令查询当前的线程

在执行效率出现问题的时候查询慢查询日志并不能定位问题，则使用 show processlist命令查看当前MySQL进程，包括线程的状态，是否锁表 等等
image.png

1.3通过EXPLAIN分析低效SQL的执行计划

1.3.1 EXPLAIN详讲

当查询到效率低的SQL的语句后，通过EXPLAIN或者DESC 命令获取MySQL如何执行select语句的信息
image.png

select_type：表示select类型，
SIMPLE
PRIMARY
UNION
SUBQUEERY
table：输出结果的表
type：MySQL在表中找到所需行的方式，或者叫访问类型
ALL：全表扫面，MySQL遍历全表来找到匹配的行
index：索引全扫描，MySQL遍历整个索引来查询匹配的行
range：索引范围扫描，常见于 < > between 等等
ref：使用非唯一索引扫描或唯一索引的前缀（可能会重复）扫描，返回匹配某个单独值的记录，
eq_ref：类似ref，区别是使用的索引是唯一索引（不能重复），对于每个索引的键值，表中只有一条记录（值是唯一的），返回唯一的值；
const,system：单表中最多一个匹配行，查询非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前的查询中当作常量来处理，如主键和唯一索引 当表中只有一条数据时，type就是system
NULL：MySQL不用访问表或者索引直接，直接得到结果
image.png
性能从上到下，性能由最差到最好

possible_keys：表示查询时可能用到的索引

key：表示实际使用的索引

key_len：使用到索引字段的长度

rows：扫面行的数量

Extra：执行情况的说明和描述

1.3.2 explain extended命令

利用explain extended的结果来迅速地获取一个更清晰易读的SQL
image.png

1.3.3 explain partitios命令

查看SQL所访问的分区
image.png

1.4 通过show prifile分析MySQL

能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

1.4.1 查看是否支持

image.png

1.4.2 执行的详细资源占用信息

show profiles
#列表,显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由变量
#profiling_history_size 控制,默认15条. 


show profile 
#展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列.

image.png

1.4.3 查看进程的信息

执行过程中线程的每个状态和消耗的时间

show profile for query Query_ID;

image.png

在获取到最消耗的线程后，MySQL支持进一步选择all CPU … 等明细类型来查看MySQL在使用什么资源耗费了过高的时间
image.png

1.5 通过trace分析优化器如何选择执行计划

打开 trace

set optimizer_trace="enabled=on",end_markers_in_json=on;

检查

select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE

最后会输出一个跟踪文件

1.6 确定问题并采取相应措施

2.索引问题

  1. Hash索引：只有 Memory/Heap 引擎支持hash索引，Hash索引不适用范围查询，
  2. B-Tree索引：

2.1MySQL如何使用索引

2.1.1MySQL中能够使用索引的典型案例

1. 匹配全值：对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件

image.png

2. 匹配值的范围查询：对索引的值能够进行范围查找

3. 匹配最左前缀：仅仅使用索引中的最左边列进行查找

image.png

4. 仅仅对索引进行查询：当查询的列都在索引的字段中，查询的效率更高

Extar多了index 表示查询使用了覆盖索引扫描
image.png

5. 匹配列前缀

SQL优化 - 图13

6. 能够实现索引匹配部分精确而其他部分进行范围匹配

7.如果列名是索引，那么使用column_name is null就会使用索引

image.png

8.MySQL5.6 引入的 index Condition Pushdown的特性，进一步优化了查询

5.6之前，复合索引在对一个筛选后再返回表进行第二个筛选，而5.6之后，直接就在筛选阶段全部筛选好了，不需要在返表筛选，表示MySQL使用了ICP来进一步优化查询，在检索的时候，把条件的过滤操作推给存储引擎层来完成，这样能够降低不必要的io访问

2.2.2存在索引但不能使用索引的典型场景

1.以%开头的LIKE查询不能够利用B-Tree索引

下面例子就是很好解决方法，先通过覆盖索引查询查出主键，在通过主键去回表查出所有的数据

EXPLAIN SELECT * from (SELECT actor_id from actor where last_name like '%NI%') a,
            actor b WHERE a.actor_id = b.actor_id

image.png

2.数据类型出现隐式转换的时候也不会使用索引

image.png

3.复合索引的情况下，查询条件不包含索引列最左部分

这样是不会使用复合索引
image.png

4.MySQL使用索引比全表扫描更慢

例如查询 like ‘s%’ 预估索引扫描出数据量占比很高，这样还不如全面扫描快
筛选性越高的，越倾向于索引扫描
通过trace清晰地看到优化器选择的过程

5.用or分割开的条件，or前的条件有索引，后无索引

那么涉及的索引都不会被用到，因为后面的无索引，那么查询肯定要全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描

2.3查看索引使用情况

image.png

handler_read_key：代表一个行被索引值读的次数，值越低，表明增加的索引得到的性能改善不高，
handler_read_rnd_next：是在数据文件中读下一行的请求数。 值越高，代表查询运行低效，并且应该建立索引补救

3.两个简单实用的优化方法

3.1定期分析表和检查表

3.1.1分析表

用于分析和存储表的关键分布，分许的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。 在分析的期间，会对表加一个只读锁

analyze [local|no_mrite_to_binlog] table tbl_name [,tbl_name]...

image.png

3.1.2检查表

检查一个或多个表是否有错误，对MYISAM和InnoDB表有作用，对于MYISAM表，关键字统计数据被更新
在检查的期间，会对表加一个只读锁

check table tbl_name [,tbl_name]...[option]..option={quick|fast|medium|extended|changed}

image.png

3.2定期优化表

将表中的空间碎片进行合并，并且可以消除由于删除和更新造成的空间浪费 只对MYISAM和InnoDB BDB起作用
OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。
OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。

optimize [local|no_mrite_to_binlog] table tbl_name [,tbl_name]...

image.png

对于innodb引擎来说，通过设置innodb_file_per_table参数，这样每个数据库会生成一个独立储存表的数据和索引的ibd文件，来减轻InnoDB表的空间回收问题。
在删除大量数据后，InnoDB表可以通过alter table 但是不修改引擎的方式回收不用的空间 再次期间回对表进行锁定
image.png

4.常用的SQL优化

前面描述都是查询优化，现在我们谈谈除了查询以外的优化

4.1大批量插入数据

当使用load 命令导入数据的时候，适当的设置可以提高导入的速度

4.1.1对于MYISAM引擎

这种方法将构建索引放在数据完全载入表中之后，避免了数据载入过程中的检查，加快了数据载入。并且，这个时候构建索引可以通过排序来构建更加紧凑的索引树。 

alter table tbl_name disable keys;
#关闭MYISAM表非唯一索引的更新

alter table tbl_name enable keys;
#打开MYISAM表非唯一索引的更新

4.1.2对于InnoDB引擎

1. 第一种方法

因为InnoDB类型的表进行数据导入是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列来提高导入数据的效率

2. 第二种方法

在导入数据执行前执行 set unique_checks=0,关闭唯一性校检，导入后在打开

3. 第三种方法

在导入数据执行前执行 set AUTOCOMMIT=0,关闭自动校检，导入后在打开

4.2 优化insert语句

  1. 当同一客服插入很多行时，尽量使用多个值的insert的语句
  2. 如果不同用户插入很多行，可以通过使用 insert delayed 语句得到更高的速度

使用INSERT DELAYED有以下几个约束：
INSERT DELAYED只适用于MYISAM，MEMORY表，ARCHIVE表，BLACKHOLE表。
如果INSERT DELAYED的表被LOCK TABLE那么，会发生错误。

  1. 将索引文件和数据文件分在不同的磁盘上保存
  2. 如果进行批量插入，可以增加 bulk_insert_buffer_size变量值的方法来提高速度，只能多MYISAM表使用
  3. 当一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多insert语句块20倍

4.3 优化 ORDER BY 语句

4.3.1 MySQL有两种排序方式

1.通过有序索引顺序扫描直接返回有序数据

image.png

c1 为一个有序的索引
image.png

2.通过对返回数据进行排序

所有不是通过索引直接返回的排序结果都叫做 filesort排序
虽然是通过了c1有序索引排列 但是需要返回所有的值，需要全表扫描
image.png

c1 为有序索引，但是c2不是 ，虽然用到了索引，但是在索引上发生了一次排序操作
image.png

where 和 order by 使用相同索引时，就能够不会查询之后在排序！ 我们所做的就是为了一点，就是在查询索引阶段直接做好排序，不应该将数据查询出来在做排序，所以当where和order by同时操作的时候，保证他们两个用到同一个索引就行。如果不是一个索引那就是先查出来，然后再通过email排序就很亏
image.png


image.png


image.png

image.png

4.3.2 Filesort的优化

在某些情况下，不能让filesort消失，那就需要想办法加快 filesort的操作

  1. 两次扫描算符
  2. 一次扫描算法

通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小来判断使用哪种排序算法。如果 max_length_for_sort_data更大，那么使用第二种优化算法，否则使用第一种
适当加大sort_buffer_size 排序区，尽量让排序在内存中完成

4.4 优化 GROUP BY语句

默认情况下，MySQL对所有的字段都进行排序。如果查询包括group by但用户想要避免排序结果的消耗，则可以使用：
order by null 禁止排序

4. 5 优化镶嵌查询

https://www.cnblogs.com/fudashi/p/7491039.html
在MySQL5,5 包括5,5 子查询的效率还是不如关联查询（jion），因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询计划

4.6 MySQL如何优化 OR条件

对于含有or的查询子句，如果要利用索引，则or之间的每个条件都必须用到索引；如果没有索引则应该考虑增加索引。

4.7 优化分页查询

查询 limit 1000,20 此时MySQL排序出前1020条记录后仅仅需要返回第1001到1020条记录，前1000条舍弃，代价高

4.7.1第一种优化思路

在索引上完成排序分页的操作（索引查找需查的相关的索引），最后根据主键 关联回原表查询所需要的其他列内容（主键匹配相对应的主键，在回表查询其他列的信息）。

4.7.2第二种思路

把limit查询转换成某个位置的查询。
将上一页的最后一行的数据加到select语句
将 rental_id 先排序在 范围查询取多少个值
where rental_id<’’ order by rental_id limit ..;

4.8 使用SQL提示

select sql_buffer_results * from ...

这个语句将强制MySQL生成一个临时结果集。只要临时结果集生成后，所有表上的锁定均被释放。

4.8.1 USE INDEX

在查询语句中表名后加添加 USE INDEX 来提供希望MySQL去参考的索引列表，就可以让MySQL不参考其他的索引

4.8.2 IGNORE INDEX

和USE IDNEX 相反 忽略一个或多个索引

4.8.3 FORCE INDEX

强制使用索引
当大部分的数据大于1的时候，并设置该为普通索引，where id>1 时，会默认进行 全表扫描。

5.常用SQL技巧

5.1 正则表达式的使用

通过正则表达式 匹配你所需要的格式

5.2 巧用RANG（）提取随机行

eg： ORDER BY RANG（） 能把数据随机排序

5.3利用GROUP BY 的WITH ROLLUP 子句

不仅能检索出各组的聚合信息，还能检索出本组类的整体聚合信息

with rollup 要在语句的最后面，加在staff_id 则以id相同来统计staff_id。
image.png

5.4用BIT GROUP FUNCTIONS做统计

bit_or：或 有就取一
bit_and：与 相同则取一

5.5 数据库名、表名大小写问题

5.6 使用外键需要注意的问题

外键只对InnoDB引擎的表起作用