我们之前说过 MySQL的优化。优化干什么呢？
- 系统的吞吐量瓶颈往往出现在数据库的访问速度上
- 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
- 数据是存放在磁盘上的，读写速度无法和内存相比（redis）
如果出现了，响应数据超时 我们怎么发现她呢？
慢查询 日志
MySQL的慢查询日志是 MySQL提供的一种日志记录，它用来记录在 MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time指的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行 10s以上的语句。
- 开启慢查询日志
- 进入 Mysql 查看慢查询日志是否开启
mysql -uroot -p
mysql> show variables like "%slow%";
+---------------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 | # 查询时间
| slow_query_log | ON | # 关闭状态
| slow_query_log_file | /var/lib/mysql/izbp1izjo7pl5ccghnbdiuz-slow.log | # 慢查询日志地址
+---------------------------+-------------------------------------------------+- 退出mysql，去mysql配置文件位置慢查询日志开启，默认的配置文件在/etc/my.cnf
mysql> exit;
vim /etc/my.cnf- vi my.cnf 执行命令进行编辑配置文件，在【mysqld】下增加
slow_query_log=1 # 开启慢查询日志，
long_query_time=1 # 查询超过多长时间记录，
log_queries_not_using_indexes=1 # 记录没有索引的查询。- 重启 MySQL
service mysqld restart- 查看 是否开启
mysql -uroot -p # 进入MySQL
mysql> show variables like "%slow%";
+---------------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON | # 开启了
| slow_query_log_file | /var/lib/mysql/izbp1izjo7pl5ccghnbdiuz-slow.log | # 慢查询日志地址
+---------------------------+-------------------------------------------------+- 查询一下慢查询的时间超过多少时间写入日志
mysql> show variables like "%long%";
+----------------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------------+----------+
| long_query_time | 1.000000 | # 刚才设置的
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+----------+- 接下来我们就可以通过 刚才检测到的 慢查询日志的地址来进行查看。
# 查看最后20条记录如下，成功记录了最后20条SQL执行语句。
tail -20 /var/lib/mysql/izbp1izjo7pl5ccghnbdiuz-slow.log
# Time: 190115 17:46:21
# User@Host: root[root] @ localhost []
# Query_time: 0.000329 Lock_time: 0.000166 Rows_sent: 40 Rows_examined: 40
SET timestamp=1547545581;
show tables;
# Time: 190115 17:46:36
# User@Host: root[root] @ localhost []
# Query_time: 0.054437 Lock_time: 0.000115 Rows_sent: 104 Rows_examined: 104
SET timestamp=1547545596;
select * from TABLES;
# Time: 190115 17:49:12
# User@Host: root[root] @ localhost []
# Query_time: 0.001611 Lock_time: 0.000705 Rows_sent: 21 Rows_examined: 21
SET timestamp=1547545752;
desc TAbles;
# Time: 190115 17:49:41
# User@Host: root[root] @ localhost []
# Query_time: 0.002142 Lock_time: 0.000056 Rows_sent: 104 Rows_examined: 104
SET timestamp=1547545781;
select VERSION from Tables;- 慢查日志的存储格式
# Time: 190115 17:54:44 执行时间
# User@Host: root[root] @ localhost [] 执行主机
# Query_time: 0.000073 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 执行信息
SET timestamp=1547546084; 时间戳
select database();
对于慢查询日志，比较重要的几个参数如下：
语句 | 描述 |
slow_query_log | 表示是否开启慢查询 |
long_query_time | 表示慢查询阈值，SQL执行时间超过该值，则会记录到慢查询日志中。SQL的执行耗时不包含锁等待时间。 |
slow_query_log_file | 表示慢日志所在的路径。 |
log_queries_not_using_indexes: | 没有使用索引的SQL也将被记录到慢查询日志中； |
log_throttle_queries_not_using_indexes: | 如果log_queries_not_using_indexes打开，没有使用索引的sql将会写入到慢查询日志中，该参数将限制每分钟写入的SQL数量； |
min_examined_row_limit: | 对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中； |
log_slow_admin_statements: | 管理语句执行时间大于阈值也将写入到慢查询日志中，管理语句包括alter table, check table等等； |
log_slow_slave_statements: | 从库应用binlog，如果binlog格式是statement，执行时间超过阈值时，将写入从库的慢查询日志， 对于ROW格式binlog，不管执行时间有没有超过阈值，都不会写入到从库的慢查询日志。 |
我们找到了 慢查询的语句，怎么优化？
- 语句优化 （语句优化是我们开发人员最好接触的）
优化 insert语句：一次插入多值 不要循环插入
应尽量避免在where 语句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描
应尽量避免在 where 语句中对字段进行 null值判断，否则将导致引擎放弃使用索引而进行全表扫描
优化嵌套查询：子查询可以被更有 效率的连接（Join）替代
很多时候使用 exists 代替 in ，in 代替 or 是一个好的选择
选择最有效率的表名顺序：数据库的解析器按照 从右到左的顺序处理 From 语句中的表名，From语句中卸载最后的表将被最先处理
select 语句避免使用 *- 索引优化：
建议再经常做查询选择的字段，经常做表连接的字段 以及经常出现在 order by、group by、distinct 后面的字段建立索引。但必须注意 以下会导致索引 失效的可能
以 "%(表示任意 0个或多个字符)" 开头的 LIKE 语句，模糊匹配
or语句前后没有同时使用索引
数据类型出现隐式转化 (如：varchar 不加单引号会转换为 int 类型)
对于多列索引，必须满足 最左匹配原则- 数据库表结构的优化
- 选择合适的数据类型
使用较小的数据类型解决问题
使用简单的数据类型(Mysql处理 int要比 varchar容易)
尽可能的使用 not null 定义字段
尽量避免使用 text类型，非用不可时 最好考虑分表- 表的范式优化
第一范式：字段原子性
第二范式：消除对主键的 部分依赖（可能主键不止一个）
最好是使用与业务无关的字段作为主键
第三范式：消除对主键的 传递依赖
高内聚，如商品表可以分为 商品简介表，商品详情表- 表的垂直拆分
竖着拆：商品表 商品详情表。表中的数据不完全一样 - 表的水平拆分
横着拆：一月份表，二月份表 …。表中的数据一摸一样
- 系统优化
- 增加 TCP支持的队列数
- InnoDB 缓存池设置(innodb_buffer_poolsize，推荐总内存的 75%)和缓存池的个数(innodb_buffer_pool_instances)
- 硬件优化：
CPU：核心数多并且主频高的
内存：增大内存
磁盘配置和选择：磁盘性能
通过 MySQL的慢查询日志，我们可以查询出执行的次数多占用的时间长的 SQL。我们也可以使用可视化工具来进行更好的 查看和管理。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
