[TOC]
## 分析SQL查询慢的方法
1. 记录慢查询日志
分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费
时间和精力,可以使用pt- query- digest工具进行分析
2. 使用 show profile
`set profiling=1`;开启,服务器上执行的所有语句会检测消耗的时
间,存到临时表中
```
set profiling=1; //开启 profile
show profiles
show profile for query临时表ID
```
demo
```
mysql > set profiling=1;s
mysql > select from a;
mysql> show profiles;
```
3. 使用 show status
`show status`会返回一些计数器, `show global status`查看服务器级别的所有计数有时根据这些计数,可以猜测出哪些操作代价较高或者消耗时间多
4. 使用 show processlist
观察是否有大量线程处于不正常
5. 使用 explain
分析单条SQL语句
`mysql> explain select * from fq_order\G;`
## 优化查询过程中的数据访问
- 访问数据太多导致查询性能下降
- 确定应用程序是否在检索大量超过需要的数据,可能是太多行或列
- 确认 MYSQL服务器是否在分析大量不必要的数据行
- 查询不需要的记录,使用 limit解决
- 多表关联返回全部列,指定Aid,A.name,Bage
- 总是取出全部列, `SELECT*`会让优化器无法完成索引覆盖扫描的优化
- 重复查询相同的数据,可以缓存数据,下次直接读取缓存
## 是否在扫描额外的记录
使用 explain来进行分析,如果发现查询需要扫描大量的数据但只返回少数的行,可以通过如下技巧去优化:
使用索引覆盖扫描,把所有用的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果
## 一个复杂查询好与多个简单查询
- MysαL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多
- 使用尽可能少的查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要
## 切分查询
将一个大的查询分为多个小的相同的查询一次性删除1000万的数据要比一次删除1万,暂停一会的方案更加损耗服务器开销
## 分解关联查询
- 可以将一条关联语句分解成多条SQL来执行
- 让缓存的效率更高
- 执行单个查询可以减少锁的竞争
- 在应用层做关联可以更容易对数据库进行拆分
- 查询效率会有大幅提升
- 较少冗余记录的查询
## 优化 count()查询
- `count(*`)中的`*`会忽略所有的列,直接统计所有列数,因此不要使用 count(列名)
- MYISAM中,没有任何 WHERE条件的` count(*)`非常快,当有 WHERE条件, MYISAM的 count统计不一定比其他表引擎快
- 可以使用 explain查询近似值,用近似值替代 count()
- 增加汇总表
- 使用缓存
## 优化关联查询
- 确定ON或者 USING子句的列上有索引
- 确保 GROUP BY和 ORDER BY中只有一个表中的列,这样 MYSQL才有可能使用索引
## 优化子查询
尽可能使用关联查询来替代
## 优化 GROUP BY和 DISTINCT
- 这两种查询均可使用索引来优化,是最有效的优化方法
- 关联查询中,使用标识列进行分组的效率会更高
- 如果不需要 ORDER BY,进行 GROUP BY时使用 ORDER BY NULL, MYSQL不会再进行文件排序
- WITH ROLLUP超级聚合,可以挪到应用程序处理
> 分类聚合后的结果进行汇总 [参考用法简书 ](https://www.jianshu.com/p/5d2f700b0a31)
## 优化UNON查询
UNION ALL的效率高于 UNION
## 分区表的原理
对用户而言,分区表是一个独立的逻辑表,但是底层 MYSQL将其分成了多个物理子表,这对用户来说是透明的,每一个分区表都会使用一个独立的表文件 .
创建表时使用 partition by子句定义每个分区存放的数据,执行查询时,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询只需要查询所需数据在的分区即可
分区的主要目的是将数据按照一个较粗的粒度分在不同的表中,这样可以将相关的数据存放在一起,而且如果想一次性删除整个分区的数据也很方便
### 适用场景
1. 表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据
2. 分区表的数据更易维护,可以对独立的分区进行独立的操作
3. 分区表的数据可以分布在不同的机器上,从而高效使用资源
4. 可以使用分区表来避免某些特殊的瓶颈
5. 可以备份和恢复独立的分区
### 限制
1. 一个表最多只能有1024个分区
2. 5.1版本中,分区表表达式必须是整数,5.5可以使用列分区
3. 分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来
4. 分区表中无法使用外键约束
5. 需要对现有表的结构进行修改
6. 所有分区都必须使用相同的存储引擎
7. 分区函数中可以使用的函数和表达式会有一些限制
8. 某些存储引擎不支持分区
9. 对于 MYISAM的分区表,不能使用 load index into cache
10. 对于 MYISAM表,使用分区表时需要打开更多的文件描述符
## 分库分表的原理
### 工作原理
通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分
### 适用场景
1. 单表记录条数达到百万到千万级别时
2. 解决表锁的问题
### 分表方式
#### 水平分割
表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度
使用场景
1. 表中的数据本身就有独立性,例如表中分别记录各个地区的数据或者不同时期的数据,特别是有些数据常用,有些不常用
2. 需要把数据存放在多个介质上
水平分表缺点
1. 给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需 `UNION` 操作
2. 在许多数据库应用中,这种复杂性会超过它带来的优点,查询时会增加读一个索引层的磁盘次数
### 垂直分表
把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中,就是多一张详情表
使用场景
1. 如果一个表中某些列常用,而另外一些列不常用
2. 可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数
垂直分表缺点
1. 管理冗余列,查询所有数据需要JoIN操作
## MYSQL的复制原理及负载均衡
### MYSQL主从复制工作原理
在主库上把数据更改记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志中的事件,将其重放到从库数据中
### MYSQL主从复制解决的问题
数据分布:随意停止或开始复制,并在不同地理位置分布数据备份
负载均衡:降低单个服务器的压力
高可用和故障切换:帮助应用程序避免单点失败
升级测试:可以使用更高版本的 MYSQL作为从库
### 解题方法
充分掌握分区和分表的工作原理和适用场景,在面试中,此类题通
常比较灵活,会给一些现有公司遇到问题的场景,大家可以根据分
区和分表以及Mysα复制、负载均衡的适用场景来根据情况进行
回答。
