# MySQL

**Repository Path**: wqj7915/my-sql

## Basic Information

- **Project Name**: MySQL
- **Description**: MySQL调优
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-06-30
- **Last Updated**: 2021-07-15

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# MySQL

#### 介绍
MySQL相关内容

#### 调优
##### 一、开启慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

- 查看是否开启：  ```show variables like '%slow_query_log%' ```;
- 开启慢查询日志（默认不开启OFF）：set global slow_query_log=1; (重启会失效)

![默认不开启OFF](https://images.gitee.com/uploads/images/2021/0630/112933_88340e10_5735118.png "屏幕截图.png")


##### 标题开启了慢查询日志后，什么样的SQL才会记录到查询日志里面？
这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为10秒。
查看命令： `这里输入代码`show variables like 'long_query_time%';

![慢查询sql满足条件默认10秒](https://images.gitee.com/uploads/images/2021/0630/112112_3f874375_5735118.png "慢查询条件.png")

注： 永久设置慢查询日志开启，以及设置慢查询日志时间临界点（不建议）
linux中，mysql配置文件一般默认在 /etc/my.cnf 更改对应参数即可

##### 慢查询日志修改阈值
- 设置阀值命令： set global long_query_time=3 （修改为阀值到3秒钟的就是慢sql）
- 设置后需要开启新会话查看效果：show global variables like 'long_query_time';

![查询新设置的超时参数](https://images.gitee.com/uploads/images/2021/0630/112745_83bbf2df_5735118.png "屏幕截图.png")

##### 查看慢查询日志：
- 从show variables like '%slow_query_log%';可以获取日志位置。
- linux系统：cat -n /data/mysql/mysql-slow.log
- windows系统： 直接打开文本即可

![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/113458_8d2290d4_5735118.png "屏幕截图.png")
##### 查看有多少条慢查询记录： 
show global status like '%Slow_queries%';
![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/113704_c8ee7fd3_5735118.png "屏幕截图.png")
##### 日志分析工具mysqldumpslow

- s: 是表示按照何种方式排序
- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间
- t:即为返回前面多少条的数据
- g:后边搭配一个正则匹配模式，大小写不敏感的

###### 工作常用参考:

- 得到返回记录集最多的10个SQL： mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
- 得到访问次数最多的10个SQL： mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log
- 得到按照时间排序的前10条里面含有左连接的SQL： 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

#### 二、详细sql优化
##### 1.sql执行顺序
- Sql语句的一个基本执行顺序，总结一下就是：from-where-group by-having-select-order by-limit。

![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/133429_638a8032_5735118.png "屏幕截图.png")
![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/133510_5c5dc278_5735118.png "屏幕截图.png")
##### 2.7种join
![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/133659_2ea09c95_5735118.png "屏幕截图.png")
##### 3.索引
###### 3.1索引初步了解
- 是什么： 排好序的快速查找数据结构
- 两个主要的索引结构： B+tree 索引和哈希索引。
- 如何建： 1. ALTER TABLE table_name ADD INDEX index_name (column_list); 2. CREATE INDEX index_name ON table_name (column_list);
- 优点：类似大学图书馆建书目索引，提高了检索效率，降低了数据库IO，同时还可以通过索引进行排序，降低数据排序的成本，降低了CPU的消耗
- 缺点： 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 insert、update和delete，因为更新表时不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。
###### 3.1索引分类
1. 这里是列表文本主键索引：主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键
`ALTER TABLE table_name ADD PRIMARY KEY (column_list)`
2. 唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空 
`ALTER TABLE table_name ADD UNIQUE (column_list)`
3. 普通索引：基本的索引类型，值可以为空，没有唯一性的限制
`ALTER TABLE table_name ADD INDEX index_name (column_list);`
4. 全文索引： 全文索引的索引类型为 FULLTEXT，全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。查询数据量较大的字符串类型字段时，使用全文索引可以提高查询速度
`ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_list);`
###### 3.1索引建与不建
- 需要创建索引
1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
5. 查询中统计或者分组字段
- 不需要建索引
1. 频繁更新的字段不适合创建索引
1. where 条件用不到的字段不适合创建索引
1. 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
#### 4.Explain性能分析
Explain 简称执行计划，使用 Explain 关键字可以模拟优化器执行SQL查询语句
- 用法：explain + SQL
![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/135619_3ab87aea_5735118.png "屏幕截图.png")
- 这里不再赘述所有列作用，自行百度，说应用核心： 针对explain命令生成的执行计划，这里有一个查看心法。我们可以先从查询类型type列开始查看，如果出现all关键字，后面的内容就都可以不用看了，代表全表扫描。再看key列，看是否使用了索引,null代表没有使用索引。然后看rows列，该列用来表示在SQL执行过程中被扫描的行数，该数值越大，意味着需要扫描的行数越多，相应的耗时越长，最后看Extra列，在这列中要观察是否有Using filesort 或者Using temporary 这样的关键字出现，这些是很影响数据库性能的。
1. type显示查询使用了何种类型
- 从好到坏，system > const > eq_ref > ref > range > index > all
- system：表只有一行记录（等于系统表），这是const 类型的特列，平时不会出现，这个也可以忽略不计
- const：表示通过索引一次就找到了，const 用于比较 primary key或者unique索引
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问
- range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了 between、<、>、in等的查询
- index：Full Index Scan，index与 all 区别为 index 类型只遍历索引树
- all：Full Table Scan，将遍历全表以找到匹配的行
2. key实际使用的索引，如果为null，则没有使用索引。
3. rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
4. extra：包含不适合在其他列中显示但十分重要的额外信息
-  **Using filesort**  (劣)： mysql 会对数据使用一个外部的索引排序（文件排序），而不是照表内的索引顺序进行读取
![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/140832_c6e4b002_5735118.png "屏幕截图.png")
-  **Using temporary**  (劣)：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
![输入图片说明](https://images.gitee.com/uploads/images/2021/0630/140940_1f4b7492_5735118.png "屏幕截图.png")
- Using index (优)：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
- Using where：表明使用了where 过滤
- Using join buffer：表明使用了连接缓存
- impossible where：where子句的值总是false，不能用来获取任何数据
- select tables optimized away：select操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了
- distinct：在select部分使用了distinc关键字

> 索引优化 [https://www.cnblogs.com/dwlovelife/p/11110561.html](https://www.cnblogs.com/dwlovelife/p/11110561.html)