2021-07-14

学习MySQL优化记录

//检查mysql 是否开启慢查询日志

show variables like 'slow_query_log';

Variable_name | Value

slow_query_log | OFF

//og_queries_not_using_indexes 这个变量是否开启(这个开启不走索引的sql将记录在慢查询日志中)；

show variables like '%log%';

| log_queries_not_using_indexes | OFF

//记录为不使用索引的sql 就是把上面这个变量改为on

set global log_queries_not_using_indexes=on;

//超过多长的时间的sql记录在慢查询日志中

show variables like 'long_query_time';

set global long_query_time=20; ps 单位为秒；

//开启慢查询日志

set global slow_query_log=on;

//查看慢查询日志的位置

show variables like 'slow%';

//慢查询日志地址（我的）

/usr/local/mysql/data/jiangs-MacBook-Pro-slow.log

//使用cat 查看慢查询日志

cat /usr/local/mysql/data/jiangs-MacBook-Pro-slow.log

//出现Permission denied 权限不够 需要授权

$ sudo chmod -R xxx 某一目录，注意：777权限谨慎使用 为高权限。

ps 注意授权的是目录不是文件；

//慢查询分析工具

1：mysql自带 mysqldumpslow

mysqldumpslow -h;可以查看参数有哪些

//查询前三行的慢sql

mysqldumpslow -t 3 /usr/local/mysql/data/jiangs-MacBook-Pro-slow.log |more;

//我们可以发现日志的Time 和系统的时间不对，慢查询日志时间与系统时间相差8小时；

//查看mysql 默认时间格式

show variables like 'log_timestamps';
UTC大家都知道是世界统一时间，而我现在的系统时间是东八区，比UTC早了8个小时，这就对上了。查看官方文档看一下官网的解释；
借鉴地址https://www.yisu.com/zixun/1420.html

//设置SYSTEM格式

SET GLOBAL log_timestamps = SYSTEM;

//第二个分析工具pt-query-disgest slow-log

//如何通过慢查询日志发现有问题的sql?

1.查询的次数并且每次查询的时间长的sql

通常为pt-query-digest分析的前几个查询

2：io大的sql

注意pt-query-digest分析中的Rows examine项

3：没有命名索引的sql

注意pt-query-digest 分析中的Rows examine 和Rows Send 的对比。

//使用explain查询sql的执行计划

举个例子：explain select * from user;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

变量解释：

table:显示这一行的数据是关于那张表的

type:显示使用何种的类型，const,eq_reg,ref,range,index,和All

Const:常数查找,一般是主键，唯一的索引；

eq_reg:是一种范围的查找，一般是主键，唯一的索引的范围查找。

Ref:一般出现在连接的出现中；

Range:一般是索引的范围的查找；

index:一般是索引的扫描；

all:一般是表扫描；

Possible_keys:显示可能应用在这张表的索引，如果为空，没有可能的索引。

key:实际使用索引，如果为null，没有使用索引。

key_len:使用索引的长度。在不损失精确性的情况下，长度越短越好。

Ref:显示索引的那一列被使用，如果可能的话，是一个常数。

Rows:mysql认为必须检查的用来返回的请求的行数。

//explain返回各列的含义

Using fillesort:看到这个的时候，查询就需要优化了，mysql需要进行额外的步骤发现如何对返回的行进行排序，他根据连接的连接的类型以及存储排序建值和匹配条件的全部行的行指针来排序全部所有的行；

Using temporary 看到这个的时候，查询需要优化，这里mysql 需要创建一个临时的表存储结果，通常这个发生在对不同的列集进行ordey by 上而不是group by 上；

优化max（）

给这个字段创建索引举个例子 create index index_paydate on user(name);

explain select * from user;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

对于count 的优化select COUNT(name=‘jia1ng’ OR NULL) AA,COUNT(name=‘jiang’ OR NULL) AA FROM user;