文章目录
查询语句的优化
查询分析语句
{ EXPLAIN | DESCRIBE | DESC }查询语句; -- 分析查询语句的执行计划
explain
- id：是一个查询序列号。
- table：表示与查询结果相关的表的名称。
- partition：表示查询访问的分区。
- key：表示优化器最终决定使用的索引是什么。
- key_len：表示优化器选择的索引字段按字节计算的长度。如果没有使用索引，这个值就 是空。
- ref：表示哪个字段或者常量被用来与索引字段比对，以读取表中的记录。如果这个值 是“func”，就表示用函数的值与索引字段进行比对。
- rows：表示为了得到查询结果，必须扫描多少行记录。
- filtered：表示查询筛选出的记录占全部表记录数的百分比。
- possible_key：表示 MySQL 可以通过哪些索引找到查询的结果记录。如果这里的值是 空，就说明没有合适的索引可用。你可以通过查看 WHERE 条件语句中使用的字段，来 决定是否可以通过创建索引提高查询的效率
- Extra：表示 MySQL 执行查询中的附加信息。
- type：表示表是如何连接的。 例如 ALL 表示全部扫描 相对于没有使用索引
- extra 和 type 的详解
select_type。它表示查询的类型，主要有 4 种取值:
- SIMPLE：表示简单查询，不包含子查询和联合查询。
- PRIMARY：表示是最外层的查询。
- UNION：表示联合查询中的第二个或者之后的查询。
- DEPENDENTUNION：表示联合查询中的第二个或者之后的查询，而且这个查询受外查 询的影响。
- 根据上表分析 可以发现没有 索引 ， 解决就是加上索引!
2 种查询优化的方法
使用关键字“LIKE”
like 通常搭配 通配符 % 来筛选 符合条件的记录:
- WHERE字段 LIKE ‘%aa’，表示筛选出所有以字段以“aa”结尾的记录；
- WHERE字段 LIKE ‘aa%’，表示筛选出所有以“aa”开始的记录；
只有这个可以使用索引！！ - 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 '%海鲜菇%';
总结
- 先分析查看执行计划 ， 再优化 ，通常就是没有使用索引造成的,
- 子查询的执行效率不高。因 为 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'; -- 启用所有事件的监控 。
- NAME：表示事件的名称；
- ENABLED：表示是否启用了对这个事件的监控；
- TIMED：表示是否收集事件的时间信息
setup_instruments表中的数据指定了是否对某一事件进行监控，而setup_consumers这个表保存的数据，则指定了是否保存监控事件发生的信息 (setup_consumers 被称为 消费者)
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' ; -- 启动所有种类事件信息的保存
SELECT * FROM performance_schema.setup_consumers ; -- 查询
- 如果查询中出现问题，那么，异常信息就会 被记录下来。接下来，我们就利用系统监控到的信息来诊断一下问题
利用监控信息诊断问题
- 第 1 个表是 performance_schema.events_statements_current，表中记录的是当前系统中的查询事件。表中的每一行对应一个进程，一个进程只有一 行数据，显示的是每个进程中被监控到的查询事件。
- 第 2 个表是 performance_schema.events_statements_history，表中记录了系统中所有进程中最近发生的查询事件。这个表中包含的查询事件都是已 经完成了的。另外，表中可以为每个进程保存的最大记录数由系统变量决定。
查看最大记录数
show variables like '%performance_schema_events_statements_history_size';
--下图： 这个表中为每个进程保留最多 10 条记录
- 第 3 个表，是
performance_schema.events_statements_history_long,表中可以保存的记录数由系统变量决定.
show variables like '%performance_schema_events_statements_history_long'; -- 默认 10000 条记录。
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：表示事件编号。
总结
- 系统参数 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 保存的数据用来控制保存哪些事件的信息。
- 利用监控信息 判断问题