网易MySQL微专业学习笔记（十一）-MySQL业务优化与设计

前言

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记，本篇为其“MySQL业务优化与设计”中的MySQL数据类型相关笔记。 所有笔记可能不定期更新，发布时不一定为最终版。

正文

索引查询优化

什么是索引

索引的意义-快速定位要查找的数据

二分查找法

B+tree

创建及索引

单列索引

create index idx_test1 on tb_student(name);

联合索引

create index idx_test2 on tb_student(name,age);

索引中现根据name排序，name相同的情况下，根据age排序

索引维护

索引维护有数据库自动完成

插入/修改/删除每一个索引行都编程一个内部封装的事务

索引越多，事务越长，代价越高

索引越多对标的插入和索引字段修改就越慢。

控制表上的索引数量！切忌胡乱添加无用索引

如何使用索引

依据where查询条件创建索引

select a,b from tab_a where c - ?

idx_c©

select a,b from tab_a where c = ? and d = ?

idx_cd (c,d)

排序ORDER BY,GROUP BY,DISTINCT字段添加索引

select * from tb_a order by a;

select  a,count(*) from tb_a group by a;

idx_a(a)

select * from tb_a order by a,b;

idx_a_b(a,b)

select *from tb_a where c=?order by a;

idx_c_a(c,a)

索引与字段选择性

某个字段其值的重复度

id选择性极好

name 选择性较好

gender 选择性很差

选择性很差的字段通常不适合创建单列索引

男女比例相仿的表中性别不适合创建单列索引

如果男女比例极不平衡，要查询的又是少数方（理工院校查女生）可以考虑使用索引

联合查询索引中选择性好的字段应该排在前面

select * from tab_a where gender = ? and name = ?

idx_a1(name,gender)

联合索引与前缀查询

联合索引能为前缀单列，复列查询提供帮助

idx_smp(a,b,c)

where a = ?

where a=? and b=?;

where a=? and c=?;(部分ok)

5.5以前部分ok，a是前缀部分,c此时不是 索引 ，a可以加速，但ac之间隔了个b，所以c不会被作为索引加速，5.6以后引入Index Condition Pushdown，故而可以对两者索引。

合理创建联合索引，避免冗余

（a）,(a,b),(a,b,c)X

(a,b,c)√

长字段上的索引

在非常长的字段上建立索引影响性能

InnoDB索引单子段（utf8）只能去前767bytes

对长字段处理的方法

Email类，建立前缀索引

Mail_addr varchar(2048)

idx_mailadd(Mail_addr(30))

住址类，分拆字段

Home_address varchar(2048)

idx_mailadd(Mail_addr(30)) ?-很可能前半段都是相同的省市区街道名称

Province varchar(1024),City varchar(1024),District varchar(1024),Local_address varchar(1024)建立联合索引或单列索引√

索引覆盖扫描

最核心sql考虑索引覆盖

SELECT Name FROM tb_user WHERE UserID = ?

KEY IDX_UID_NAME(UserID,Name);

不需要回表获取name字段，IO最小，效率最高；

日常应用最高的是username,password；

无法使用索引的情况

索引列进行数学运算或函数运算

where id+1=10 x

where id = (10-1) √

year(col)<2007 x

col<'2007-01-01’√

未含复合索引的前缀字段

Idx_abc(a,b,c)

where b = ? and c = ?

(b,c)

前缀通配，“_”和“%”通配符

LIKE “%xxx%” x

LIKE “xxx%” √

模糊匹配要不忍受全表扫描的很慢速度，要不拿出数据库放在全文搜索服务中

where条件使用NOT,<>,!=

字段类型匹配

并不绝对，但是无法预测的会造成问题，不要使用

a int(11),idx_a(a)

where a = '123’x

where a = 123√

利用索引排序

idx_a_b(a,b)

能够使用索引帮助排序的查询

order by a

a = 3 order by b

order by a,b

order by a desc,bdesc

a>5 order by a

不能使用索引帮助凭虚的查询

先根据a,后根据b，b不一定是有序的

order by b

a>5 order by b

a in(1,3) order by b

order by a asc,b desc-----a的用到索引，b未用到

当前缀部分为非等值操作，后缀部分将无法利用索引。

查看索引是否使用了索引

explain是确定一个查询如何走索引最简便有效的方法

explain select * from tb_test;

关注的项目：

type：查询access的方法，若为All则为全表查询，index索引扫描

ref等值查询，range范围查询

Key：本次查询最终选择使用哪个索引，null为未使用索引

key_len：选择索引使用的前缀长度或整个长度

rows：可以理解为查询逻辑读，需要扫描过的记录行数。越小越好，越小代表扫描字段越小，io越少，效率越好

extra：额外信息，主要指的fetch data的具体方法

Mysql数据库设计

什么是Schema设计

设计数据库的表，索引，以及表和表的关系

在数据建模的基础上将关系模型转化为数据库表

满足业务模型需要基础上根据数据库和应用特点优化表结构

为什么Schema需要设计

Schema关系到应用程序功能与性能

满足也饿无功能需要

同性能密切相关

数据库扩展性

满足周边需求（统计，迁移等）

关系型数据库修改Schema经常是高危操作

Schema设计要体现一定的前瞻性

完全由开发者主导的Schema设计

着眼于实现当前功能

完全基于功能的设计可能存在一些隐患

不合理的表结构或索引设计造成性能问题

没有合理评估到数据量的增长造成空间紧张而且难以维护

需求频繁修改造成表结构经常变更

业务重大调整导致数据经常需要重构订正

。。。。。

基于性能的表设计

根据查询需要设计好索引

根据核心查询需求，适当调整表结构

基于一些特殊业务需求，调整实现方式

索引

正确使用索引

更新尽可能使用主键或唯一索引

逐渐尽可能使用自增ID字段

核心查询覆盖扫描

用户登录需要根据用户名返回密码用户验证

create index idx_uname_passwd on tb_user(username,password)

建立联合索引避免回表取数据

反范式，冗余必要字段

针对核心sql保留查询结果所必需的的冗余字段，避免频繁join

拆分大字段

拆分大字段到单独表中，避免范围扫描代价大

例：博文表拆分两份，标题表只保留标题和内容缩略部分，用于快速批量返回标题列表。正文表保重大段博文内容，用于点开文章单个读取。

避免过多字段或过长行

根据SQL必要返回设计字段，有必要就拆表，避免过多字段

一次没必要获取那么多列数据

行过长导致表数据也记录变少，范围扫描性能降低

更新数据也代价增大

16k页最少放2行，可能出现行迁移

三四十列或更多列可考虑拆分。

分页查询

避免limit +offset过大

offset 1w，会扫描1w零一行数据

–随着offset增大，io消耗越大

应该使用自增主键ID模拟分页

第一页，直接查

得到第一页的max(id)=123（一般是最后一条记录）

第二页，带上id>123查询：where id>123 limit 100

这样每次只需扫描100条数据

要求业务上禁止查询xx页之后的数据

热点读数据特殊处理

根据数据获取的频率或数据不同对热点数据做特殊处理

热点写数据特殊处理

根据数据获取的频率或数据不同对热点数据做特殊处理

准实时统计

对不需要精确结果的技术等统计要求，建立定期更新结果表

实时统计改进1-触发器实时统计

对需要精确统计的计数利用数据库触发器维护统计表

实时统计改进2-缓存实时统计

对需要精确统计的计数利用前端缓存实施维护计数

实时统计改进3-最大自增ID获取总数

很多逻辑可以利用自增ID最大值直接做总数

可扩展性设计

可扩展性

硬件资源增长有极限的情况下处理尽可能久的线上业务

数据分级，冷数据归档与淘汰

可以不断释放空间供新数据使用

微数据分布式做准备

分库分表

水平拆分

牺牲一定的关系模型支持

分区表与数据淘汰

range分区

适合数据需要定期过期的大表

单个分区表扫描迁移数据到历史库避免全表扫描IO开销

删除单个分区非常高效

如，按年份分区，删除两年前的，只需删除对应的老分区。

分区表与垂直分区

list分区

石化将来可能要给予地区，类目等方式垂直拆分数据的方式

清理节点上不要的数据非常高效

如按照地域的，如各地都有一些仓库，可分成东南西北几个区。

分区表与水平分区

hash分区

适合将来需要做水平拆分的表

清理节点上不要的数据非常高效

如，当一台机器满足不了用户表或用户订单数据量的时候，将用户放到不同节点。

Mysql分区表的局限

主键或唯一键必须包含在分区字段内

分区字段必须是整数类型，或者加上返回整数的函数

满足周边需求

为周边需求额外增加表设计

为后台统计任务增加特殊索引

微数据迁移或同级需求增加时间戳

统计和后台需求

统计运行SQL往往和线上有很大不同

利用Mysql一主多从，主从复制可以建不同索引特性将统计分流到特定从库

包括一些特殊用户批量查询等，所有对线上有IO亚罗的查询都要读写分离。

自动更新戳

统计需求经常要求从线上读走增量数据

表的第一个timestamp类型字段在写入时如果不填值，会自动写入系统时间戳

表的第一个timestamp类型字段每次记录发生更新后都会自动更新

在update_time字段上建索引用于定时导出增量数据

Schema设计与前瞻性

基于历史经验教训，预防和解决同类问题

把折腾DBA够呛的所有Schema改造的原因记录并分析总结

例：

1、业务为了用户信息加密做了大改造

数据库结果大量改动，增加了加密字段，验证策略表，所有表重新订正数据等等

是否所有用到用户信息管理的应用都有去上线就用密文？

2、程序Bug误删数据，线上风险大

改造业务流程，不再删除数据，加入is_deleted标记站位，经常给各种表加

今后的类似表是否一上线就都用标记位的方式，并加上修改原因字段？

3、支付类应用后期做了风控改造

对线上订单大表改造，加了限额，终端类型等字段

遇到支付类应用，是否一上线就提示业务是否需要考虑风控并留好相关字段

SQL业务审核与优化

什么是业绩审核

类似code review

评审业务schema和SQL设计

偏重关注性能

是业务优化的主要入口之一

审核提前发现问题，进行优化

上线后通过监控或巡检发现问题，进行优化

表和字段命名是否合规

字段类型，长度设计是否适当

表关联关系是否合理

主键，更新时间保留字段等是否符合要求

约束，默认值等配置是否恰当

了解业务，表数据量，增长模式

数据访问模式，均衡度

根据也无需求，表是否需要分区，是否有数据什么周期

SQL语句审核

SQL语句的执行频率

表上是否有合适的索引

单次执行的成本

执行模式，锁情况分析

关注事务上下文