MySQL 优化思路与工具
1 优化思路
2 连接——配置优化
第一个环节是客户端连接到服务端,连接这一块有可能会出现什么样的性能问题? 有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error 1040: Too many connections 的错误。
可以从两个方面来解决连接数不够的问题:
1、从服务端来说,我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库,连接数不够的时候,我们可以:
(1)修改配置参数增加可用连接数,修改 max_connections 的大小:
show variables like 'max_connections'; -- 修改最大连接数,当有多个应用连接的时候
(2)或者,或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时 间都是 28800 秒,8 小时,我们可以把这个值调小。
show global variables like 'wait_timeout'; --及时释放不活动的连接,注意不要释放连接池还在使用的连接
2、从客户端来说,可以减少从服务端获取的连接数,如果我们想要不是每一次执行 SQL 都创建一个新的连接,可以引入连接池,实现连接的重用。
3 缓存——架构优化
3.1 缓存
在应用系统的并发数非常大的情况下,如果没有缓存,会造成两个问题:一方面是 会给数据库带来很大的压力。另一方面,从应用的层面来说,操作数据的速度也会受到 影响。
我们可以用第三方的缓存服务来解决这个问题,例如 Redis。
3.2 集群,主从复制
如果单台数据库服务满足不了访问需求,那我们可以做数据库的集群方案
做了主从复制的方案之后,我们只把数据写入 master 节点,而读的请求可以分担到 slave 节点。我们把这种方案叫做读写分离。
读写分离可以一定程度低减轻数据库服务器的访问压力,但是需要特别注意主从数 据一致性的问题。
3.3 分库分表
垂直分库,减少并发压力。水平分表,解决存储瓶颈
垂直分库的做法,把一个数据库按照业务拆分成不同的数据库:
水平分库分表的做法,把单张表的数据按照一定的规则分布到多个数据库。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MhAsWbIb-1604052054302)(C:\Users\17361\AppData\Local\Temp\1591971361773.png)]
4 优化器——SQL 语句分析与优化
我们的服务层每天执行了这么多 SQL 语句,它怎么知道哪些 SQL 语句比较慢呢?
第一步,我们要把 SQL 执行情况记录下来。
4.1 慢查询日志 slow query log
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
4.1.1 打开慢日志开关
因为开启慢查询日志是有代价的(跟 bin log、optimizer-trace 一样),所以它默 认是关闭的:
show variables like 'slow_query%';
除了这个开关,还有一个参数,控制执行超过多长时间的 SQL 才记录到慢日志,默 认是 10 秒。如果改成 0 秒的话就是记录所有的 SQL。
show variables like '%long_query%';
可以直接动态修改参数(重启后失效)。
set @@global.slow_query_log=1; -- 1 开启,0 关闭,重启后失效
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒,另开一个窗口后才会查到最新值
show variables like '%long_query%';
show variables like '%slow_query%';
或者修改配置文件 my.cnf。
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。
slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log
模拟慢查询:
select sleep(10);
查询 user_innodb 表的 500 万数据(没有索引)。
SELECT * FROM `user_innodb` where phone = '136';
4.1.2 慢日志分析
1、日志内容
show global status like 'slow_queries'; -- 查看有多少慢查询
show variables like '%slow_query%'; -- 获取慢日志目录
cat /var/lib/mysql/ localhost-slow.log
2、mysqldumpslow
https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
MySQL 提供了 mysqldumpslow 的工具,在 MySQL 的 bin 目录下。
mysqldumpslow --help
例如:查询用时最多的 10 条慢 SQL:
mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log
Count 代表这个 SQL 执行了多少次;
Time 代表执行的时间,括号里面是累计时间;
Lock 表示锁定的时间,括号是累计;
Rows 表示返回的记录数,括号是累计。
4.2 SHOW PROFILE
https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
SHOW PROFILE 是谷歌高级架构师 Jeremy Cole 贡献给 MySQL 社区的,可以查看 SQL 语句执行的时候使用的资源,比如 CPU、IO 的消耗情况。
在 SQL 中输入 help profile 可以得到详细的帮助信息。
4.2.1 查看是否开启
select @@profiling;
set @@profiling=1;
4.2.2 查看 profile 统计
show profiles;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gu0GJGzm-1604052054304)(https://i.loli.net/2020/06/12/jShOiRgHAun6k2m.png)]
查看最后一个 SQL 的执行详细信息,从中找出耗时较多的环节(没有 s)。
show profile;
6.2E-5,小数点左移 5 位,代表 0.000062 秒。
也可以根据 ID 查看执行详细信息,在后面带上 for query + ID。
show profile for query 1;
4.2.3 其他系统命令
分析 Server 层的运行信息,可以用 show status
show status 服务器运行状态
说明:https://dev.mysql.com/doc/refman/5.7/en/show-status.html
详细参数:https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
SHOW STATUS 用于查看 MySQL 服务器运行状态(重启后会清空)。
SHOW GLOBAL STATUS ;
可以用 like 带通配符过滤,例如查看 select 语句的执行次数。
SHOW GLOBAL STATUS LIKE 'com_select'; -- 查看 select 次数
show processlist 运行线程
如果要分析服务层的连接信息,可以用 show processlist:
https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html
show processlist;
这是很重要的一个命令,用于显示用户运行线程。 如果说其中的某个线程有问题,可以根据 id 号 kill 线程。
也可以查表,效果一样:
select * from information_schema.processlist;
线程命令:https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
线程状态:https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
show engine 存储引擎运行信息
https://dev.mysql.com/doc/refman/5.7/en/show-engine.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-standard-monitor.html
show engine 用来显示存储引擎的当前运行信息,包括事务持有的表锁、行锁信息; 事务的锁等待情况;线程信号量等待;文件 IO 请求;buffer pool 统计信息。
例如查看 InnoDB:
show engine innodb status;
MySQL 提供了一个执行计划的工具(在架构中我们有讲到,优化器最终生成的就是 一个执行计划),其他数据库,例如 Oracle 也有类似的功能。
通过 EXPLAIN 我们可以模拟优化器执行 SQL 查询语句的过程,来知道 MySQL 是 怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。
4.3 EXPLAIN 执行计划
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
EXPLAIN输出列
我们先创建三张表。一张课程表,一张老师表,一张老师联系方式表(没有任何索 引)。
4.3.1 id
id 是查询序列编号,每张表都是单独访问的,一个 SELECT 就会有一个序号。
id 值不同
id 值不同的时候,先查询 id 值大的(先大后小)。
-- 查询mysql课程的老师手机号
EXPLAIN SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (
SELECT tcid
FROM teacher t
WHERE t.tid = (
SELECT c.tid
FROM course c
WHERE c.cname = 'mysql'
)
);
查询顺序:course c——teacher t——teacher_contact tc。
先查课程表,再查老师表,最后查老师联系方式表。子查询只能以这种方式进行, 只有拿到内层的结果之后才能进行外层的查询。
id 值相同(从上往下)
id 值相同时,表的查询顺序是从上往下顺序执行。例如这次查询的 id 都是 1(说明 子查询被优化器转换成了连接查询),查询的顺序是 teacher t(3 条)——course c(4 条)——teacher_contact tc(3 条)。
在连接查询中,先查询的叫做驱动表,后查询的叫做被驱动表,我们肯定要把小表 放在前面查询,因为它的中间结果最少。 (即小表驱动大表)
既有相同也有不同
如果 ID 有相同也有不同,就是 ID 不同的先大后小,ID 相同的从上往下。
4.3.2 select type 查询类型
这里并没有列举全部(其它:DEPENDENT UNION、DEPENDENT SUBQUERY、 MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION)。
下面列举了一些常见的查询类型:
-
SIMPLE
简单查询,不包含子查询和关联查询 union。
再看一个包含子查询的案例:
-- 查询 mysql 课程的老师手机号
EXPLAIN SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (
SELECT tcid
FROM teacher t
WHERE t.tid = (
SELECT c.tid
FROM course c
WHERE c.cname = 'mysql' )
);
-
PRIMARY
子查询 SQL 语句中的主查询,也就是最外面的那层查询。
-
SUBQUERY
子查询中所有的内层查询都是 SUBQUERY 类型的。
-
DERIVED
派生查询,表示在得到最终查询结果之前会用到临时表。例如:
EXPLAIN SELECT cr.cname FROM ( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2 ) cr;
对于关联查询,先执行右边的 table(UNION),再执行左边的 table,类型是 DERIVED。
-
UNION
用到了 UNION 查询(UNION 会用到内部的临时表)。同上例。 UNION ALL 不需要去重,因此不用临时表。
-
UNION RESULT
主要是显示哪些表之间存在 UNION 查询。代表 id=2 和 id=3 的查询 存在 UNION。
EXPLAIN SELECT cr.cname FROM ( SELECT * FROM course WHERE tid = 1 UNION ALL SELECT * FROM course WHERE tid = 2 ) cr;
4.3.3 type 访问方法
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
所有的连接类型中,上面的最好,越往下越差。
在常用的链接类型中:system > const > eq_ref > ref > range > index > all
这 里 并 没 有 列 举 全 部 ( 其 他 : fulltext 、 ref_or_null 、 index_merger 、 unique_subquery、index_subquery)。 以上访问类型除了 all,都能用到索引。
-
const
主键索引或者唯一索引与常数进行等值匹配,只能查到一条数据的 SQL。
-
system
system 是 const 的一种特例,只有一行满足条件,对于 MyISAM、Memory 的表, 只查询到一条记录,也是 system。
例如:只有一条数据的系统表。
-
eq_ref
通常出现在多表的 join 查询,被驱动表通过唯一性索引(UNIQUE 或 PRIMARY KEY)进行访问,此时被驱动表的访问方式就是 eq_ref。
eq_ref 是除 const 之外最好的访问类型。
先删除 teacher 表中多余的数据,teacher_contact 有 3 条数据,teacher 表有 3 条数据。
DELETE FROM teacher where tid in (4,5,6); commit; -- 备份 INSERT INTO `teacher` VALUES (4, 'james', 4); INSERT INTO `teacher` VALUES (5, 'tom', 5); INSERT INTO `teacher` VALUES (6, 'seven', 6); commit;
为 teacher_contact 表的 tcid(第一个字段)创建主键索引。
-- ALTER TABLE teacher_contact DROP PRIMARY KEY; ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
为 teacher 表的 tcid(第三个字段)创建普通索引。
-- ALTER TABLE teacher DROP INDEX idx_tcid; ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
执行以下 SQL 语句:
select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
此时的执行计划(teacher_contact 表是 eq_ref):
小结: 以上三种 system,const,eq_ref,都是可遇而不可求的,基本上很难优化到这个 状态。
-
ref
查询用到了非唯一性索引。
例如:使用 tcid 上的普通索引查询:
explain SELECT * FROM teacher where tcid = 3;
-
range
对索引进行范围扫描。
如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些,type 类型 就为 range。
不走索引一定是全表扫描(ALL),所以先加上普通索引。
-- ALTER TABLE teacher DROP INDEX idx_tid; ALTER TABLE teacher ADD INDEX idx_tid (tid);
执行范围查询(字段上有普通索引):
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EAjgbI8X-1604052054311)(https://i.loli.net/2020/06/13/uTVM4Fyaviq85PQ.png)]
IN 查询也是 range(字段有主键索引)
EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);
-
index
Full Index Scan,查询全部索引中的数据(比不走索引要快)。
EXPLAIN SELECT tid FROM teacher;
-
ALL
Full Table Scan,如果没有索引或者没有用到索引,type 就是 ALL。代表全表扫描。
小结:
一般来说,需要保证查询至少达到 range 级别,最好能达到 ref。
ALL(全表扫描)和 index(查询全部索引)都是需要优化的。
4.3.4 possible_key、key
可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。
possible_key 可以有一个或者多个,比如查询多个字段上都有索引,或者一个字段 同时有单列索引和联合索引。
能用到的索引并不是越多越好。可能用到索引不代表一定用到索引。
如果通过分析发现没有用到索引,就要检查 SQL 或者创建索引。
4.3.5 key_len
索引的长度(使用的字节数)。跟索引字段的类型、长度有关。
表上有联合索引:KEY comidx_name_phone
(name
,phone
)
explain select * from user_innodb where name ='青山';
key_len =1023,为什么不是 255+11=266 呢?
这里的索引只用到了 name 字段,utf8mb4 编码 1 个字符 4 个字节。所以是 255*4=1020。使用变长字段 varchar 需要额外增加 2 个字节,允许 NULL 需要额外增 加 1 个字节。一共是 1023。
4.3.6 rows
MySQL 认为扫描多少行(数据或者索引)才能返回请求的数据,是一个预估值。一 般来说行数越少越好。
4.3.7 filtered
这个字段表示存储引擎返回的数据在 server 层过滤后,剩下多少满足查询的记录数 量的比例,它是一个百分比。
如果比例很低,说明存储引擎层返回的数据需要经过大量过滤,这个是会消耗性能 的,需要关注
4.3.8 ref
使用哪个列或者常数和索引一起从表中筛选数据,可以参考一下。
4.3.9 Extra
执行计划给出的额外的信息说明。
-
using index
属于覆盖索引的情况,不需要回表。 正好我们查询的tid是辅助索引,直接返回不需要回表
EXPLAIN SELECT tid FROM teacher ;
-
using where
使用了 where 过滤,表示存储引擎返回的记录并不是所有的都满足查询条件,需要 在 server 层进行过滤(跟是否使用索引没有关系)。
EXPLAIN select * from user_innodb where phone ='13866667777';
-
using Index Condition
-
using filesort
不能使用索引来排序,用到了额外的排序(跟磁盘或文件没有关系)。需要优化。 (复合索引的前提)
ALTER TABLE user_innodb DROP INDEX comidx_name_phone; ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
执行 SQL:
EXPLAIN select * from user_innodb where name ='青山' order by id;
(order by id 引起)
-
using temporary
在查询的时候,需要做去重、排序之类的工作的时候,可能会用到临时表。
1、distinct 非索引列
EXPLAIN select DISTINCT(tid) from teacher t;
2、group by 非索引列
EXPLAIN select tname from teacher group by tname;
3、使用 join 的时候,group 任意列
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;
Using Temporary 需要优化,例如创建复合索引。
总结一下:
模拟优化器执行 SQL 查询语句的过程,来知道 MySQL 是怎么处理一条 SQL 语句的。 通过这种方式我们可以分析语句或者表的性能瓶颈。
如果需要具体的 cost 信息,可以用: EXPLAIN FORMAT=JSON。
如果觉得 EXPLAIN 还不够详细,可以用开启 optimizer trace。
4.4 SQL 与索引优化
SQL 语句的优化的目标,大部分时候都是用到索引。
对于每一种具体的 SQL,也有相应的优化方案,官网:
sql优化;https://dev.mysql.com/doc/refman/5.7/en/statement-optimization.html
索引优化;https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html
5 存储引擎
5.1 存储引擎的选择
为不同的业务表选择不同的存储引擎,例如:查询插入操作多的业务表,用 MyISAM。 临时数据用 Memeroy。常规的并发大更新多的表用 InnoDB。
5.2 字段定义
原则:使用可以正确存储数据的最小数据类型。
为每一列选择合适的字段类型。
5.2.1 整数类型
INT 有 8 种类型,不同的类型的最大存储范围是不一样的。 性别?用 TINYINT,因为 ENUM 也是整数存储
5.2.2 字符类型
变长情况下,varchar 更节省空间,但是对于 varchar 字段,需要一个字节来记录长 度。
固定长度的用 char,不要用 varchar。
5.2.3 不要用外键、触发器、视图
降低了可读性;
影响数据库性能,应该把把计算的事情交给程序,数据库专心做存储;
数据的完整性应该在程序中检查。
5.2.4 大文件存储
不要用数据库存储图片(比如 base64 编码)或者大文件;
把文件放在 NAS 上,数据库只需要存储 URI(相对路径),在应用中配置 NAS 服 务器地址。
5.2.5 表拆分或字段冗余
将不常用的字段拆分出去,避免列数过多和数据量过大。
比如在业务系统中,要记录所有接收和发送的消息,这个消息是 XML 格式的,用 blob 或者 text 存储,用来追踪和判断重复,可以建立一张表专门用来存储报文。
6 总结:优化体系
所以,如果在面试的时候再问到这个问题“你会从哪些维度来优化数据库”,你会 怎么回答?
除了对于代码、SQL 语句、表定义、架构、配置优化之外,业务层面的优化也不能 忽视。举两个例子:
1)在某一年的双十一,为什么会做一个充值到余额宝和余额有奖金的活动,例如充 300 送 50? 因为使用余额或者余额宝付款是记录本地或者内部数据库,而使用银行卡付款,需 要调用接口,操作内部数据库肯定更快。
2)在去年的双十一,为什么在凌晨禁止查询今天之外的账单? 这是一种降级措施,用来保证当前最核心的业务。 3)最近几年的双十一,为什么提前个把星期就已经有双十一当天的价格了? 预售分流。
在应用层面同样有很多其他的方案来优化,达到尽量减轻数据库的压力的目的,比 如限流,或者引入 MQ 削峰,等等等等。
为什么同样用 MySQL,有的公司可以抗住百万千万级别的并发,而有的公司几百个 并发都扛不住,关键在于怎么用。所以,用数据库慢,不代表数据库本身慢,有的时候 还要往上层去优化
当然,如果关系型数据库解决不了的问题,我们可能需要用到搜索引擎或者大数据 的方案了,并不是所有的数据都要放到关系型数据库存储。
这是优化的层次,如果说遇到的一个具体的慢 SQL 的问题,我们又应该怎么去优化 呢?比如有人给你发来一条 SQL,你应该怎么分析?
一、分析查询基本情况
1、涉及到表结构,字段的索引情况、每张表的数据量、查询的业务含义。 这个非常重要,因为有的时候你会发现 SQL 根本没必要这么写,或者表设计是有问 题的。
二、找出慢的原因
1、查看执行计划,分析 SQL 的执行情况,了解表访问顺序、访问类型、索引、扫描行 数等信息。
2、如果总体的时间很长,不确定哪一个因素影响最大,通过条件的增减,顺序的调整, 找出引起查询慢的主要原因,不断地尝试验证。
找到原因:比如是没有走索引引起的,还是关联查询引起的,还是 order by 引起的。 找到原因之后
三、对症下药
1、创建索引或者联合索引
2、改写 SQL,这里需要平时积累经验,例如:
-
使用小表驱动大表
-
用 join 来代替子查询
-
not exist 转换为 left join IS NULL
-
or 改成 union
-
使用 UNION ALL 代替 UNION,如果结果集允许重复的话
-
大偏移的 limit,先过滤再排序。
如果 SQL 本身解决不了了,就要上升到表结构和架构了。
3、表结构(冗余、拆分、not null 等)、架构优化。
4、业务层的优化,必须条件是否必要。
赶集网 mysql 开发 36 军规
(一)核心军规
- 不在数据库做运算
cpu计算务必移至业务层;
- 控制单表数据量
int型不超过1000w,含char则不超过500w;
合理分表;
限制单库表数量在300以内;
- 控制列数量
字段少而精,字段数建议在20以内;
- 平衡范式与冗余
效率优先;
往往牺牲范式;
- 拒绝3B
拒绝大sql语句:big sql
拒绝大事物:big transaction
拒绝大批量:big batch
(二)字段类军规
- 用好数值类型
tinyint(1Byte)
smallint(2Byte)
mediumint(3Byte)
int(4Byte)
bigint(8Byte)
bad case:int(1)/int(11)
- 字符转化为数字
用int而不是char(15)存储ip
- 优先使用enum或set
例如:sex
enum (‘F’, ‘M’)
- 避免使用NULL字段
NULL字段很难查询优化;
NULL字段的索引需要额外空间;
NULL字段的复合索引无效;
bad case:
name
char(32) default null
age
int not null
good case:
age
int not null default 0
- 少用text/blob
varchar的性能会比text高很多;
实在避免不了blob,请拆表;
- 不在数据库里存图片
这个我不能理解!
但这是赶集网的经验,求detail!
(三)索引类军规
- 谨慎合理使用索引
改善查询、减慢更新;
索引一定不是越多越好(能不加就不加,要加的一定得加);
覆盖记录条数过多不适合建索引,例如“性别”;
- 字符字段必须建前缀索引
- 不在索引做列运算
!!!不只是索引,都不能做列运算吧!!!
bad case:
select id where age +1 = 10;
- innodb主键推荐使用自增列;
主键建立聚簇索引;
主键不应该被修改;
字符串不应该做主键;
如果不指定主键,innodb会使用唯一且非空值索引代替;
- 不用外键
请由程序保证约束;
(四)sql类军规
- sql语句尽可能简单
一条sql只能在一个cpu运算;
大语句拆小语句,减少锁时间;
一条大sql可以堵死整个库;
- 简单的事务
事务时间尽可能短;
bad case:
上传图片事务
- 避免使用trig/func
触发器、函数不用;
客户端程序取而代之;
- 不用select *
消耗cpu,io,内存,带宽;
这种程序不具有扩展性;
- OR改写为IN()
or的效率是n级别;
in的消息时log(n)级别;
in的个数建议控制在200以内;
select id from t where phone=’159′ or phone=’136′;
=>
select id from t where phone in (’159′, ’136′);
- OR改写为UNION
mysql的索引合并很弱智
select id from t where phone = ’159′ or name = ‘john’;
=>
select id from t where phone=’159′
union
select id from t where name=’jonh’
- 避免负向%
- 慎用count(*)
- limit高效分页
limit越大,效率越低
select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;
- 使用union all替代union
union有去重开销
- 少用连接join
- 使用group by
分组;
自动排序;
- 请使用同类型比较
- 使用load data导数据
load data比insert快约20倍;
- 打散批量更新
- 新能分析工具
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona);
58到家MySQL军规升级版
一、基础规范
(1)必须使用InnoDB存储引擎
解读:支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
(2)必须使用UTF8字符集
解读:万国码,无需转码,无乱码风险,节省空间
(3)数据表、数据字段必须加入中文注释
解读:N年后谁tm知道这个r1,r2,r3字段是干嘛的
(4)禁止使用存储过程、视图、触发器、Event
解读:高并发大数据的互联网业务,架构设计思路是“解放数据库CPU,将计算转移到服务层”,并发量大的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就加性能”。数据库擅长存储与索引,CPU计算还是上移吧
(5)禁止存储大文件或者大照片
解读:为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统,数据库里存URI多好
二、命名规范
(6)只允许使用内网域名,而不是ip连接数据库
(7)线上环境、开发环境、测试环境数据库内网域名遵循命名规范
业务名称:xxx
线上环境:gp.xxx.db
开发环境:gp.xxx.rdb
测试环境:gp.xxx.tdb
从库在名称后加-s标识,备库在名称后加-ss标识
线上从库:gp.xxx-s.db
线上备库:gp.xxx-sss.db
(8)库名、表名、字段名:小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用
(9)表名t_xxx,非唯一索引名idx_xxx,唯一索引名uniq_xxx
三、表设计规范
(10)单实例表数目必须小于500
(11)单表列数目必须小于30
(12)表必须有主键,例如自增主键
解读:
a)主键递增,数据行写入可以提高插入性能,可以避免page分裂,减少表碎片提升空间和内存的使用
b)主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率
c) 无主键的表删除,在row模式的主从架构,会导致备库夯住
(13)禁止使用外键,如果有外键完整性约束,需要应用程序控制
解读:外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响sql 的性能,甚至会造成死锁。高并发情况下容易造成数据库性能,大数据高并发业务场景数据库使用以性能优先
四、字段设计规范
(14)必须把字段定义为NOT NULL并且提供默认值
解读:
a)null的列使索引/索引统计/值比较都更加复杂,对MySQL来说更难优化
b)null 这种类型MySQL内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多
c)null值需要更多的存储空,无论是表还是索引中每行中的null的列都需要额外的空间来标识
d)对null 的处理时候,只能采用is null或is not null,而不能采用=、in、<、<>、!=、not in这些操作符号。如:where name!=’shenjian’,如果存在name为null值的记录,查询结果就不会包含name为null值的记录
(15)禁止使用TEXT、BLOB类型
解读:会浪费更多的磁盘和内存空间,非必要的大量的大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能
(16)禁止使用小数存储货币
解读:使用整数吧,小数容易导致钱对不上
(17)必须使用varchar(20)存储手机号
解读:
a)涉及到区号或者国家代号,可能出现±()
b)手机号会去做数学运算么?
c)varchar可以支持模糊查询,例如:like“138%”
(18)禁止使用ENUM,可使用TINYINT代替
解读:
a)增加新的ENUM值要做DDL操作
b)ENUM的内部实际存储就是整数,你以为自己定义的是字符串?
五、索引设计规范
(19)单表索引建议控制在5个以内
(20)单索引字段数不允许超过5个
解读:字段超过5个时,实际已经起不到有效过滤数据的作用了
(21)禁止在更新十分频繁、区分度不高的属性上建立索引
解读:
a)更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能
b)“性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似
(22)建立组合索引,必须把区分度高的字段放在前面
解读:能够更加有效的过滤数据
六、SQL使用规范
(23)禁止使用SELECT *,只获取必要的字段,需要显示说明列属性
解读:
a)读取不需要的列会增加CPU、IO、NET消耗
b)不能有效的利用覆盖索引
c)使用SELECT *容易在增加或者删除字段后出现程序BUG
(24)禁止使用INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性
解读:容易在增加或者删除字段后出现程序BUG
(25)禁止使用属性隐式转换
解读:SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描,而不能命中phone索引,猜猜为什么?(这个线上问题不止出现过一次)
(26)禁止在WHERE条件的属性上使用函数或者表达式
解读:SELECT uid FROM t_user WHERE from_unixtime(day)>=‘2017-02-15’ 会导致全表扫描
正确的写法是:SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00’)
(27)禁止负向查询,以及%开头的模糊查询
解读:
a)负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描
b)%开头的模糊查询,会导致全表扫描
(28)禁止大表使用JOIN查询,禁止大表使用子查询
解读:会产生临时表,消耗较多内存与CPU,极大影响数据库性能
(29)禁止使用OR条件,必须改为IN查询
解读:旧版本Mysql的OR查询是不能命中索引的,即使能命中索引,为何要让数据库耗费更多的CPU帮助实施查询优化呢?
(30)应用程序必须捕获SQL异常,并有相应处理
总结:大数据量高并发的互联网业务,极大影响数据库性能的都不让用,不让用哟。
=【完】=
再议数据库军规
上一篇《58到家数据库30条军规解读》引发了广泛的讨论,某些军规部分同学有疑惑,补充一文说明。
军规:必须使用UTF8字符集
和DBA负责人确认后,纠正为“新库默认使用utf8mb4字符集”。
这点感谢网友的提醒,utf8mb4是utf8的超集,emoji表情以及部分不常见汉字在utf8下会表现为乱码,故需要升级至utf8mb4。
默认使用这个字符集的原因是:“标准,万国码,无需转码,无乱码风险”,并不“节省空间”。
一个潜在坑:阿里云上RDS服务如果要从utf8升级为utf8mb4,需要重启实例,所以58到家并没有把所有的数据库升级成这个字符集,而是“新库默认使用utf8mb4字符集”。
自搭的Mysql可以完成在线转换,而不需要重启数据库实例。
军规:数据表、数据字段必须加入中文注释
这一点应该没有疑问。
不过也有朋友提出,加入注释会方便黑客,建议“注释写在文档里,文档和数据库同步更新”。这个建议根据经验来说是不太靠谱的:
(1)不能怕bug就不写代码,怕黑客就不写注释,对吧?
(2)文档同步更新也不太现实,还是把注释写好,代码可读性做好更可行,互联网公司的文档管理?呆过互联网公司的同学估计都清楚。
军规:禁止使用存储过程、视图、触发器、Event
军规:禁止使用外键,如果有外键完整性约束,需要应用程序控制
军规:禁止大表使用JOIN查询,禁止大表使用子查询
很多网友提出,这些军规不合理,完全做到不可能。
如原文所述,58到家数据库30条军规的背景是“并发量大、数据量大的互联网业务”,这类业务架构设计的重点往往是吞吐量,性能优先(和钱相关的少部分业务是一致性优先),对数据库性能影响较大的数据库特性较少使用。这类场景的架构方向是“解放数据库CPU,把复杂逻辑计算放到服务层”,服务层具备更好的扩展性,容易实现“增机器就扩充性能”,数据库擅长存储与索引,勿让数据库背负过重的任务。
关于这个点,再有较真的柳岩小编就不回复了哈,任何事情都没有百分之百,但58到家的数据库使用确实没有存储过程、视图、触发器、外键、用户自定义函数,针对业务特性设计架构,等单库吞吐量到了几千上万,就明白这些军规的重要性啦。
军规:只允许使用内网域名,而不是ip连接数据库
这一点应该也没有疑问。
不只是数据库,缓存(memcache、redis)的连接,服务(service)的连接都必须使用内网域名,机器迁移/平滑升级/运维管理…太多太多的好处,如果朋友你还是采用ip直连的,赶紧升级到内网域名吧。
军规:禁止使用小数存储国币
有朋友问存储前乘以100,取出后除以100是否可行,个人建议“尽量少的使用除法”。
曾经踩过这样的坑,100元分3天摊销,每天摊销100/3元,结果得到3个33.33。后来实施对账系统,始终有几分钱对不齐,郁闷了很久(不是几分钱的事,是业务方质疑的眼神让研发很不爽),最后发现是除法惹的祸。
解决方案:使用“分”作为单位,这样数据库里就是整数了。
案例:SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描,而不能命中phone索引
这个坑大家没踩过么?
phone是varchar类型,SQL语句带入的是整形,故不会命中索引,加个引号就好了:
SELECT uid FROM t_user WHERE phone=’13812345678’
军规:禁止使用负向查询NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描
此军规争议比较大,部分网友反馈不这么做很多业务实现不了,稍微解释一下:
一般来说,WHERE过滤条件不会只带这么一个“负向查询条件”,还会有其他过滤条件,举个例子:查询沈剑已完成订单之外的订单(好拗口):
SELECT oid FROM t_order WHERE uid=123 AND status != 1;
订单表5000w数据,但uid=123就会迅速的将数据量过滤到很少的级别(uid建立了索引),此时再接上一个负向的查询条件就无所谓了,扫描的行数本身就会很少。
但如果要查询所有已完成订单之外的订单:
SELECT oid FROM t_order WHERE status != 1;
这就挂了,立马CPU100%,status索引会失效,负向查询导致全表扫描。
末了,除了《58到家数据库30条军规解读》中提到的基础规范、命名规范、表设计规范、字段设计规范、索引设计规范、SQL使用规范,还有一个行为规范的军规:
(31)禁止使用应用程序配置文件内的帐号手工访问线上数据库
(32)禁止非DBA对线上数据库进行写操作,修改线上数据需要提交工单,由DBA执行,提交的SQL语句必须经过测试
(33)分配非DBA以只读帐号,必须通过VPN+跳板机访问授权的从库
service)的连接都必须使用内网域名,机器迁移/平滑升级/运维管理…太多太多的好处,如果朋友你还是采用ip直连的,赶紧升级到内网域名吧。
军规:禁止使用小数存储国币
有朋友问存储前乘以100,取出后除以100是否可行,个人建议“尽量少的使用除法”。
曾经踩过这样的坑,100元分3天摊销,每天摊销100/3元,结果得到3个33.33。后来实施对账系统,始终有几分钱对不齐,郁闷了很久(不是几分钱的事,是业务方质疑的眼神让研发很不爽),最后发现是除法惹的祸。
解决方案:使用“分”作为单位,这样数据库里就是整数了。
案例:SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描,而不能命中phone索引
这个坑大家没踩过么?
phone是varchar类型,SQL语句带入的是整形,故不会命中索引,加个引号就好了:
SELECT uid FROM t_user WHERE phone=’13812345678’
军规:禁止使用负向查询NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描
此军规争议比较大,部分网友反馈不这么做很多业务实现不了,稍微解释一下:
一般来说,WHERE过滤条件不会只带这么一个“负向查询条件”,还会有其他过滤条件,举个例子:查询沈剑已完成订单之外的订单(好拗口):
SELECT oid FROM t_order WHERE uid=123 AND status != 1;
订单表5000w数据,但uid=123就会迅速的将数据量过滤到很少的级别(uid建立了索引),此时再接上一个负向的查询条件就无所谓了,扫描的行数本身就会很少。
但如果要查询所有已完成订单之外的订单:
SELECT oid FROM t_order WHERE status != 1;
这就挂了,立马CPU100%,status索引会失效,负向查询导致全表扫描。
末了,除了《58到家数据库30条军规解读》中提到的基础规范、命名规范、表设计规范、字段设计规范、索引设计规范、SQL使用规范,还有一个行为规范的军规:
(31)禁止使用应用程序配置文件内的帐号手工访问线上数据库
(32)禁止非DBA对线上数据库进行写操作,修改线上数据需要提交工单,由DBA执行,提交的SQL语句必须经过测试
(33)分配非DBA以只读帐号,必须通过VPN+跳板机访问授权的从库
34)开发、测试、线上环境隔离