Mysql 工作笔记

一、运维相关

1.1、binlog日志相关

1.1.1、删除binlog日志

-- 删除7天以前的bin_log日志
mysql -uroot -p12345678 -e '`date -d "6 day ago" +"%Y-%m-%d 00:00:00"`'

1.2、慢日志相关

1.2.1、开启慢日志监控

1. 临时设置：更改系统变量

-- 确认开启慢SQL日志
set global slow_query_log='ON';
show variables like '%slow_query_log%';

-- 设置慢SQL阈值long_query_time，默认：10s
-- 某些版本设置global对新连接的客户端有效，对当前session的long_query_time失效。
show variables like '%long_query_time%';
set global long_query_time=2;
set session long_query_time=2;

-- 查询扫描行数小于此参数，且耗时大于 long_query_time，才会被记录在慢日志。
show variables like '%min_examined_row_limit%';
set global min_examined_row_limit=2000;

2. 永久设置：更改 my.cnf 配置文件，重启服务器

[mysqld]
## 开启慢查询日志的开关
slow_query_log=ON 
## 慢查询日志的目录和文件名信息，默认目录：数据文件夹下；默认文件名：hostname-slow.log
log_output=FILE
slow_query_log_file=/var/lib/mysql/mysql-slow.log 
## 设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
long_query_time=3 
min_examined_row_limit=2000

1.2.2、关闭慢日志监控

1. 临时设置：更改系统变量

SET GLOBAL slow_query_log=off;

2. 永久设置：更改 my.cnf 配置文件，重启服务器

[mysqld]
slow_query_log=OFF

1.2.3、重新生成慢日志

-- 1. 执行命令 来重新生成查询日志文件，如还需旧的慢日志文件，须自行事先备份
mysqladmin -uroot -p flush-logs slow 

1.3、慢SQL排查

1.3.1、 排查数据库服务器的性能参数

SHOW [GLOBAL|SESSION] STATUS LIKE'参数';
-- Connections：连接MySQL服务器的次数。
-- Uptime：MySQL服务器的上线时间。
-- Slow_queries：慢查询的次数。默认十秒以上
-- Innodb_rows_read：Select查询返回的行数
-- Innodb_rows_inserted：执行INSERT操作插入的行数
-- Innodb_rows_updated：执行UPDATE操作更新的行数
-- Innodb_rows_deleted：执行DELETE操作删除的行数
-- Com_select：查询操作的次数。
-- Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
-- Com_update：更新操作的次数。
-- Com_delete：删除操作的次数。

1.3.2、排查必要的缓存是否生效

1.3.3、慢SQL排查优化

1.3.3.1、排查SQL查询成本（last_query_cost）

原理：

1. 位置决定效率：数据页从缓冲池的加载效率明显高于从磁盘的加载效率；
2. 批量决定效率：批量对相邻页的读取效率高于对一个个单页的随机读取效率。

SELECT student_id, class_id FROM student_info WHERE id between 90001 and 100001 ;

-- 执行慢SQL语句后，可查看当前会话的 last_query_cost 变量值得到该 SQL 语句需要读取的页的数量。
SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   |   Value   |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+

1.3.3.2、排查SQL日志（slow_log）

1. 需要开启慢日志监控
2. 查看慢SQL查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

3. 使用工具 mysqldumpslow 分析慢查询日志

## 命令格式： mysqldumpslow  [参数]  慢日志文件
## 具体参数说明
### -a: 不将数字抽象成N，字符串抽象成S
### -s: 是表示按照何种方式排序：
###   c: 访问次数
###   l: 锁定时间
###   r: 返回记录
###   t: 查询时间
###   al:平均锁定时间
###   ar:平均返回记录数
###   at:平均查询时间 （默认方式）
###   ac:平均查询次数
### -t: 即为返回前面多少条的数据；
### -g: 后边搭配一个正则匹配模式，大小写不敏感的

## eg_1：按查询时间排序，查看前五条 SQL 语句
mysqldumpslow -s t -t 5 /var/lib/mysql/mysql-slow.log 

## eg_2：查看返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log 

## eg_3：查看访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log 

## eg_4：查看按时间排序、前10条里含左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log 

## 可使用 | 和 more 的方式对结果分页查看 ，防止爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log  | more

优化方向：表结构优化、查询语句优化、索引优化。
详细参考：https://gitee.com/MingZii/learn_db/blob/master/mysql/learn_mysql_ksf/第09章_性能分析工具的使用.md

二、 索引相关

2.1、索引创建

1. 在区分度高的字段上创建索引；
   索引区分度 = count(distint 记录) / count(记录)。

2. 巧用组合索引实现** Index Condition Pushdown(ICP)索引下推**，避免回表。

--  先对 name 匹配索引找到 主键，在回表根据主键 找到数据记录后 判断 sex 条件
select count(id) from test1 a where name like 'cookie%' and sex = 1
-- 创建联合索引：(name,sex)，通过索引数据即可完成整个条件过滤，避免回表。

3. 巧用组合索引优化排序。

-- 使用 user_id 索引匹配到数据后，回表找到所有数据 在排序
select user_id, order_time from order where user_id = 'cookie'  order by order_time
--  创建联合索引：(user_id, order_time)，通过user_id检索的数据已按order_time升序，将结果翻转即可得到降序。

4. 联合索引注意最左匹配原则：按照从左到右的顺序匹配，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
   比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d 用不到索引，如果建立(a,b,d,c)在都可以使用索引，a,b,d 的顺序可任意调整。

2.2、索引使用

1. 使用between、>、<等区间查询，若范围太大，跨度page过多，速度也会较慢；

2. in 检索字段使用索引不一定比关联慢；

3. 当多个条件中有索引的时候，并且关系是and的时候，会走索引区分度高的；

4. 谨慎使用*，最小查询。尽量去利用索引覆盖，可以减少 回表 操作 。；

5. 查询数据都在索引树中时，出现索引覆盖，避免回表；

2.3、索引失效

1. 对于 '%cookie'、'_cookie' 类型的模糊查询，索引失效；

2. 索引字段为字符串，条件值为数字（如：name=1），索引失效；

3. 索引字段使用了函数（如：concat(a.name,'1') ='cookie1'），索引失效；

4. 索引字段使用了运算符（如： id + 1 < 2）,索引失效；