查询语句的优化

查询分析语句

{ EXPLAIN | DESCRIBE | DESC }查询语句;  -- 分析查询语句的执行计划
1

explain

1. id：是一个查询序列号。
2. table：表示与查询结果相关的表的名称。
3. partition：表示查询访问的分区。
4. key：表示优化器最终决定使用的索引是什么。
5. key_len：表示优化器选择的索引字段按字节计算的长度。如果没有使用索引，这个值就 是空。
6. ref：表示哪个字段或者常量被用来与索引字段比对，以读取表中的记录。如果这个值 是“func”，就表示用函数的值与索引字段进行比对。
7. rows：表示为了得到查询结果，必须扫描多少行记录。
8. filtered：表示查询筛选出的记录占全部表记录数的百分比。
9. possible_key：表示 MySQL 可以通过哪些索引找到查询的结果记录。如果这里的值是 空，就说明没有合适的索引可用。你可以通过查看 WHERE 条件语句中使用的字段，来 决定是否可以通过创建索引提高查询的效率
10. Extra：表示 MySQL 执行查询中的附加信息。
11. type：表示表是如何连接的。 例如 ALL 表示全部扫描 相对于没有使用索引
12. extra 和 type 的详解

select_type。它表示查询的类型，主要有 4 种取值:

• SIMPLE：表示简单查询，不包含子查询和联合查询。
• PRIMARY：表示是最外层的查询。
• UNION：表示联合查询中的第二个或者之后的查询。
• DEPENDENTUNION：表示联合查询中的第二个或者之后的查询，而且这个查询受外查 询的影响。
  
  
• 根据上表分析 可以发现没有 索引 ， 解决就是加上索引!

2 种查询优化的方法

使用关键字“LIKE”

like 通常搭配 通配符 % 来筛选 符合条件的记录:

1. WHERE字段 LIKE ‘%aa’，表示筛选出所有以字段以“aa”结尾的记录；
2. WHERE字段 LIKE ‘aa%’，表示筛选出所有以“aa”开始的记录； 只有这个可以使用索引！！
3. WHERE字段 LIKE ‘%aa%’，表示所有字段中包含“aa”的记录。

使用关键字“OR”

|| or 一个成立整个表达式就成立

• 只有当条件语句中只有关键字“OR”，并且“OR”前后的表达式中的字 段都建有索引的时候，查询才能用到索引。

-- 这个语句就用到了索引
EXPLAIN SELECT * FROM demo.trans 
 WHERE barcode LIKE '6953150%'  OR itemnumber = 1;


-- 这个语句没有使用到索引  表达式2没有创建索引
EXPLAIN SELECT * FROM demo.trans 
 WHERE barcode LIKE '6953150%'  OR goodsname LIKE '%海鲜菇%';  
1
2
3
4
5
6
7
8

总结

• 先分析查看执行计划 ， 再优化 ，通常就是没有使用索引造成的,
• 子查询的执行效率不高。因 为 MySQL 会用临时表把子查询的结果保存起来，然后再使用临时表的内容完成查询。这 样一来，查询就多了一个创建临时表的过程，执行效率没有连接查询高。针对这种情况， 建议你把子查询转换成连接查询，这样可以进一步提高查询的效率

表的优化

数据类型优化

• 第一种方法：对整数类型数据进行优化，还要考虑到系统整体的效率。 例如 INT 是为了保证系统的稳定性， 如果项目允许的情况下，尽量采用小的数据类型来定义。
• 第二种方法：就是既可以使用文本类型也可以使用整数类型的字段，要使用整数类型，而不要用文本类型。 因为 Text类型占用的字节数 = 时间字符串长度 + 2. 如果text 是20字节的话，对比bigint 占用空间 8字节， 占用的存储空间会更少， 节省空间。
  
• 查询的效率也更高一些

合理增加冗余字段以提高效率

通常针对的是 查询频繁，并且需要用到连接的时候，就可以增加冗余字段。

• 对goodsmaster 表加上 goodsname 的字段 就可以做到不用连接
  
• 增加冗余字段的俩个条件： 第一个是，这个冗余字段不需要经常进行修改；第二个 是，这个冗余字段查询的时候不可或缺

拆分表

• 把 1 个包含很多字段的表拆 分成 2 个或者多个相对较小的表。 一般用于 表中存在操作频率很高和频率很低的字段，这样就可以把频率高以及频率低的表进行拆分。
• 查询 合并 就得用到连接了

尽可能使用非空约束

• 在设计字段的时候，如果业务允许，我建议你尽量使用非空约束。
• 可以省去判断是否为空的开销，提高存储效率。
• 非空字段也容易创建索引。使用非空约 束，甚至可以节省存储空间（每个字段 1 个比特）。
  

总结

• 修改数据类型以节省存储空间； 需要考虑取值范围
• 在利大于弊的情况下增加冗余字段； 确保数据的一致性
• 把大表中查询频率高的字段和查询频率低的字段拆分成不同的表；意味着需要使用连接，从而增加开销和运维的成本。
• 尽量使用非空约束。

充分利用系统资源

磁盘读写需要计算位置、发出读写指令等，这些都要消耗 CPU 资源。“先把数据放在内存，然后集中写入磁盘”的办法，可以节省 CPU 资源和磁盘读取的时间，但是也会面临系统故障时会丢失数据的风险；相反，如果每次都写入磁盘，数据最安全，但是频繁的磁盘读写，会导致系统效率低下

优化系统资源配置

• 系统参数控制着资源的配置， 调整系统参数的值，可以帮助我们提升资源的利用效率。
• 系统的三个参数 : InnoDB_flush_log_at_trx_commit、InnoDB_buffer_pool_size、 InnoDB_buffer_pool_instances。

1. 调整系统参数 InnoDB_flush_log_at_trx_commit

这个参数适用于 InnoDB 存储引擎， 这个参数存储在 MySQL 的配置文件 my.ini 里面 。

• 默认的值是 1，意思是每次提交事务的 时候，都把数据写入日志，并把日志写入磁盘。这样做的好处是数据安全性最佳，不足之处在于每次提交事务，都要进行磁盘写入的操作。
• 0 表示每隔 1 秒将数据写入日 志，并将日志写入磁盘；
• 2 表示，每次提交事务的时候都将数据写入日志，但是日志每间 隔 1 秒写入磁盘。
• 0 的效率比 2好 但是安全性 2更佳。

2. 调整系统参数 InnoDB_buffer_pool_size

• InnoDB 存储引擎使用缓存来存储索引和数据。这个值越大，可以加 载到缓存区的索引和数据量就越多，需要的磁盘读写就越少。

3. 调整系统参数 InnoDB_buffer_pool_instances

• 将 InnoDB 的缓存区分成几个部分,就可以提高系统的并 行处理能力，因为可以允许多个进程同时处理不同部分的缓存区。
• InnoDB_buffer_pool_instances 的值修改为 64，意思就是把 InnoDB 的缓存区分 成 64 个分区，这样就可以同时有多个进程进行数据操作，CPU 的效率就高多了
• 修改后保存配置文件my.ini 然后重启服务器才生效 ， my.ini 保存的格式必须是ANSI才行.记事本保存文件默认的码制是 UTF-8，但配置文件的码制必须是 ANSI 才行
  
• 遇到 CPU 资源不足的问题，可以从 2 个思路去解决：1. 疏通拥堵路段，消除瓶颈，让等待的时间更短；(修改读写模式) 2. 开拓新的通道，增加并行处理能力（分区）。

监控服务器工具使用

MySQL的一个工具：Performance Schema， 这是一种专门用来监控服务器执行情况的存储引擎，它会把监控服务器执行情况的数据记 录在系统自带的数据库 performance_schema 中。我们可以利用监控的数据，对服务器中 执行查询的问题进行诊断。

启用系统监控

• 系统数据库 performance_schema 中的表 setup_instruments 和 setup_consumers 中 的数据，是启用监控的关键。
• setup_instruments 保存的数据，表示哪些对象发生的事件可以被系统捕获（在 MySQL 中，把这些事件称作信息生产者）。

查看当前MySQL会监控哪些事件的信息

SELECT NAME,ENABLED,TIMED FROM performance_schema.setup_instruments LIMIT 1,10; -- 查询

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'; -- 启用所有事件的监控 。 
1
2
3

• NAME：表示事件的名称；
• ENABLED：表示是否启用了对这个事件的监控；
• TIMED：表示是否收集事件的时间信息
  
• setup_instruments 表中的数据指定了是否对某一事件进行监控，而 setup_consumers 这个表保存的数据，则指定了是否保存监控事件发生的信息 (setup_consumers 被称为 消费者)

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' ; -- 启动所有种类事件信息的保存
SELECT * FROM performance_schema.setup_consumers ; -- 查询 
1
2

• 如果查询中出现问题，那么，异常信息就会 被记录下来。接下来，我们就利用系统监控到的信息来诊断一下问题

利用监控信息诊断问题

• 第 1 个表是 performance_schema.events_statements_current，表中记录的是当前系统中的查询事件。表中的每一行对应一个进程，一个进程只有一 行数据，显示的是每个进程中被监控到的查询事件。
• 第 2 个表是 performance_schema.events_statements_history，表中记录了系统中所有进程中最近发生的查询事件。这个表中包含的查询事件都是已 经完成了的。另外，表中可以为每个进程保存的最大记录数由系统变量决定。
  查看最大记录数

show variables like '%performance_schema_events_statements_history_size';
--下图： 这个表中为每个进程保留最多 10 条记录
1
2

• 第 3 个表，是 performance_schema.events_statements_history_long,表中可以保存的记录数由系统变量决定.

show variables like '%performance_schema_events_statements_history_long'; -- 默认 10000 条记录。
1

• history、history_long可以通过参数设置大小,因为是内存表，如果太大会占用很多内存.

-- 查询运行时间最长的倆行SQL查询语句
SELECT 
TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS duration, sql_text, EVENT_ID 
FROM performance_schema.events_statements_history_long 
WHERE TRUNCATE(TIMER_WAIT / 1000000000000, 6) <> 0 
AND sql_text IS NOT NULL 
ORDER BY TRUNCATE(TIMER_WAIT / 1000000000000, 6) DESC 
LIMIT 1,2; 
--字段 TIMER_WAIT：表示这个查询消耗了多少时间，单位是微微秒，也就是万亿分之一 秒。
--TRUNCATE(X,D) 函数：表示给 X 保留 D 位小数，注意这个函数是直接截取，没有四舍 五入。
--字段 sql_text：表示执行的 SQL 语句的内容。 
--EVENT_ID：表示事件编号。
1
2
3
4
5
6
7
8
9
10
11
12

总结

• 系统参数 InnoDB_flush_log_at_trx_commit 适用于 InnoDB 存储引擎。默认的值是 1，意思是每次提交事务的时候，都把数据写入日志，并把日志写入磁盘。0 表示每隔 1 秒将数据写入日志，并将日志写入磁盘。2 表示每次事务提交的时候，将数据写入日 志，但是日志每隔 1 秒写入磁盘。
• 系统参数 InnoDB_buffer_pool_size 表示 InnoDB 存储引擎使用多少缓存来存储索引和 数据。这个值越大，可以加载到缓存区的索引和数据量就越多，需要的磁盘读写就越少。
• 系统参数 InnoDB_buffer_pool_instances 的意思是，将 InnoDB 的缓存区分成几个部 分，可以提高系统的并行处理能力。
• setup_instruments 保存的数据，表示哪些对象发生的事件可以被系统捕获。 setup_consumers 保存的数据用来控制保存哪些事件的信息。
• 利用监控信息 判断问题