mysql调优(二)

Author： PrinceLei
发布时间：April 24, 2020
484views
No comments
5628 words
Categories：技术分享 mysql

# mysql调优(二)
## schema与数据类型优化
### 合理使用范式和反范式
范式
优点：
- 范式化的更新通常比反范式要快
- 当数据较好的范式化后，很少或者没有重复的数据
- 范式化的数据比较小，可以放在内存中，操作比较快

缺点:
- 通常需要进行关联

反范式
优点:
- 所有的数据都在同一张表中，可以避免关联
- 可以设计有效的索引

缺点:
- 表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

在企业中很少能做到严格意义上的范式或者反范式，一般需要混合使用
- 在一个网站实例中，这个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。  在user表和message表中都存储用户类型(account_type)而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效地获取数据。
- 另一个从父表冗余一些数据到子表的理由是排序的需要。
- 缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（类似论坛的），可以每次执行一个昂贵的自查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

案例
![范式设计1.png][1]

![反范式设计2.png][2]

### 主键的选择
- 代理主键: 与业务无关的，无意义的数字序列
- 自然主键: 事物属性中的自然唯一标识

推荐使用代理主键
- 它们不与业务耦合，因此更容易维护
- 一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本

### 字符集的选择
字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，所以通过使用合适的字符集，可以帮助我们尽可能减少数据量，进而减少IO操作次数。
1. 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
2. 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
3. MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。

### 存储引擎的选择
![存储引擎的对比.png][3]

### 适当的数据冗余
1. 被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。
2. 这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

### 适当拆分
当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

## mysql执行计划
在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

官网地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

### 执行计划中包含的信息

**id**
select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序
id号分为三种情况：
1. 如果id相同，那么执行顺序从上到下
    ```
    explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
    ```
2. 如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    ```
    explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
    ```
3. id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
    ```
    explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
    ```

**select_type**
主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

:-: | :-:
**`select_type` Value** | **Meaning**
SIMPLE | Simple SELECT (not using UNION or subqueries)
PRIMARY | Outermost SELECT
UNION | Second or later SELECT statement in a UNION
DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT | Result of a UNION.
SUBQUERY | First SELECT in subquery
DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query
DERIVED | Derived table
UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

```
--sample:简单的查询，不包含子查询和union
explain select * from emp;

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
 
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证
```
**table**
对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

**type**
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 
一般情况下，得保证查询至少达到range级别，最好能达到ref
```
--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
 
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

--ref：使用了非唯一性索引进行数据的查找
 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
 
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
```
**possible_keys** 
显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
```
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
```
**key**
实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
```
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
```
**key_len**
表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。
```
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
```
**ref**
显示索引的哪一列被使用了，如果可能的话，是一个常数
```
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
```
**rows**
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好
```
explain select * from emp;
```
**extra**
包含额外的信息。
```
--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:使用where进行条件过滤
explain select * from t_user where id = 1;

--using join buffer:使用连接缓存，情况没有模拟出来

--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;
```

[1]: https://www.princelei.club/usr/uploads/2020/04/2425915853.png
  [2]: https://www.princelei.club/usr/uploads/2020/04/1022329316.png
  [3]: https://www.princelei.club/usr/uploads/2020/04/1035892622.png

Last modification：June 11th, 2020 at 05:58 pm

mysql调优(二)

PrinceLei • 2020 年 04 月 24 日