1.如何预防 SQL 注入？

1.1 何为 SQL 注入？

SQL 注入：是利用某些数据库的外部接口将用户数据插入到实际的数据库操作语句中，从而达到入侵数据库甚至操作系统的目的。

SQL 注入产生的主要原因：程序对用户输入的数据没有进行严格的过滤，导致非法数据库查询语句的执行。

SQL 注入具有很大的危害，可能会导致攻击者非法入侵系统，或者盗取数据，甚至清空数据等。

1.2 如何进行 SQL 注入攻击？

1.3 如何预防 SQL 注入？

控制输入变量的格式

增加了对输入信息的判断，过滤掉一些带特殊字符的输入。

转义特殊字符

有时，我们程序是允许输入信息带特殊字符的，这种情况就可以使用转义的方式，防止 SQL 注入。

2.主键是否需要设置为自增？

2.1 主键和聚簇索引的关系

  • 在 InnoDB 中，聚集索引不一定是主键，但是主键一定是聚集索引：原因是如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，如果也没有这样的唯一索引，InnoDB 会选择内置 6 字节长的 ROW ID
    作为隐含的聚集索引。
  • InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+Tree，它的叶子节点存放的是整行数据。
  • 每张 InnoDB 表都有一个聚簇索引，但是不一定有主键。

2.2 主键是否需要设置为自增？

通过上面的学习，我们知道了聚集索引是按照每张表的主键构造一颗 B+Tree 的，而 在B+Tree中，所有记录节点都是按键值的大小顺序存放在同一层叶子节点上。

如果每次插入的数据都是在聚簇索引树的后面，聚集索引不需要分裂就可以存入数据。

但是如果插入的数据值在聚簇索引树的中间部分，由于要保证插入后叶子节点中的记录依然排序，就可能需要聚簇索引树分裂来保证键值的有序性。

因此如果业务输入的主键都是随机数字，那么写入数据时很可能会导致数据页频繁分裂，从而影响写入效率。

而如果设置主键是自增，那么每一次都是在聚簇索引的最后增加，当一页写满，就会自动开辟一个新页，不会有聚簇索引树分裂这一步，效率会比随机主键高很多。这也是很多建表规范要求主键自增的原因。

当然也不是所有的情况主键都需要设置为自增，比如可以用程序写入增长的主键值，保证了新写入数据的主键值比之前大，也可以避免聚簇索引树频繁分裂。

3.MySQL 是否需要开启查询缓存？

从 MySQL4.1 开始，增加了查询缓存(Query Cache,简称QC)的功能，它会存储 select 语句的文本以及发送到客户端的结果，若下一次收到一个相同的查询，就会从查询缓存中获得查询结果。

3.1 认识 QC

QC 需要缓存最新数据结果，因此表数据发生任何变化（insert、update、delete 等操作时），都会导致 QC 被刷新。

查询缓存相关的参数主要有：

show variables like '%query_cache%';

img

这里解释一下上面几个参数:

have_query_cache：服务器在安装时是否已经配置了高速缓存

query_cache_limit：单条查询能够使用的缓存区大小

query_cache_min_res_unit：查询缓存分配内存块的最小单位

query_cache_size：缓存区的大小，单位为 MB

query_cache_type：缓存类型，有三个值可选：

  • 0 或者 off：关闭缓存
  • 1 或者 on：打开缓存
  • 2 或者 demand：只缓存带有 sql_cache 的 select 语句。

query_cache_wlock_invalidate：如果某个数据表被其它的连接锁住，是否仍然从查 询缓存中返回结果.

通过下面命令，可以监视查询缓存的使用情况：

show global status like "qcache%";

img

这里解释一下各个参数的意义

Qcache_free_blocks：查询缓存中的空闲内存块的数目

Qcache_free_memory：查询缓存的空闲内存总数

Qcache_hits：缓存命中次数

Qcache_inserts：被加入到缓存中的查询数目

Qcache_lowmem_prunes：因为缺少内存而从缓存中删除的查询数目

Qcache_not_cached：没有被缓存的查询数目

Qcache_queries_in_cache：在缓存中已注册的查询数目

Qcache_total_blocks：查询缓存中的块的总数目

3.2 QC 的优劣

优势：

  • 提高查询速度：使用查询缓存在单行数据的表中搜索要比不使用查询缓存快 238%（数据来源：MySQL 5.7 官方手册）。

劣势：

  • 比如执行的 SQL 都很简单（比如从只有一行的表中查询数据），但每次查询都不一样的话，打开 QC 后，额外的开销为 13% 左右；
  • 如果表数据发生了修改，使用该表的所有缓存查询都将失效，并且从缓存中删除；
  • QC 要求前后两次请求的 SQL 完全一样，不同数据库、不同协议版本或不同默认字符集的查询，都会被认为是不同的查询。甚至包括大小写，比如下面两条 SQL，查询缓存就会认为是两个不同的查询：
1
2
SELECT * FROM tbl_name;
select * from tbl_name;
  • 每次更新 QC 的内存块都需要进行锁定；
  • 可能会导致 SQL 查询时间不稳定

3.3 是否需要开启 QC？

通过上面讲解的 QC 优劣，对于是否需要开启 QC 这个问题，我们大概能总结出：

如果线上环境中 99% 以上都是只读，很少更新，可以考虑全局开启 QC，也就是设置 query_cache_type 为 1。很多时候，我们希望缓存的是几张更新频率很低的表，其它表不考虑使用查询缓存，就可以考虑将 query_cache_type 设置成 2 或者 DEMAND，这样就只缓存下面这类 SQL：

select sql_cache ...;

3.4 怎样开启和关闭 QC？

1.全局开启：

在配置文件 my.cnf 中设置：

  • query_cache_type = 1
  • query_cache_size = 50M

2.只开启部分表的 QC

在配置文件 my.cnf 中设置：

  • query_cache_type = 2
  • query_cache_size = 50M

3.关闭 QC

在配置文件 my.cnf 中设置：

  • query_cache_type = 0
  • query_cache_size = 0
  • 或者源码编译安装 MySQL 的话，编译时增加参数 --without-query-cache 即可。

3.5 开启 QC 的注意事项

如果要开启 QC，建议不要设置过大，通常几十兆就好。如果设置过大，会增加维护缓存所需要的开销。

另外要注意一些即使开启 QC 也不能使用 QC 的场景（这里参考的是 MySQL 5.7 官方手册第 8.10.3.1 节：How the Query Cache Operates ）：

  • 分区表不支持，如果涉及分区表的查询，将自动禁用查询缓存
  • 子查询或者外层查询
  • 存储过程、触发器中使用的 SQL
  • 读取系统库时
  • 类似下面 SQL 时： 
    1
    2
    select ... lock in share mode
    select ... for update
  • 用到临时表
  • 产生了 warning 的查询
  • 显示增加了 SQL_NO_CACHE 关键字的
  • 如果没有全部库、表的 select 权限，则也不会使用 QC
  • 使用了一些函数：比如 now()，user()，password() 等

4. 使用读写分离需要注意哪些？

对于高访问量的业务场景，MySQL 读写分离显得格外重要。通常我们所说的读写分离说的是：对于修改操作在主库上进行，对于查询操作，在从库上进行。主要目的是分担主库的压力。但是读写分离有时也会存在问题，比如：主从延迟时，读取的从库数据不是最新的。对应的业务场景如：

  • 你网购的一个商品，付完款之后，因为主从延迟，第一时间还查询不到订单（查询的从库），即使等一段时间能看到订单，但是相信这种情况很多用户是不能接受的。

通常情况下，读写分离都是依赖主从复制。

主从复制的原理:

4.1 MySQL 的主从复制

传统的 MySQL 主从复制是异步的，因此也称为异步复制，MySQL 异步复制的 原理如下：

  • 在主库开启 binlog 的情况下
  • 如果主库有增删改的语句，会记录到 binlog 中
  • 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中
  • 主库给客户端返回 commit 成功（这里不会管从库是否已经收到了事务的 binlog）
  • 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中

实现原理如下图：

img

在上图中，有一个地方不能忽视：

在主库上并行运行的更新 SQL，由于从库只有单个 SQL 线程去消化 relay log，因此更新的 SQL 在从库只能串行执行。这也是很多情况下，会出现主从延迟的原因。

当然，从 5.6 开始，MySQL 支持了每个库可以配置单独的 SQL 线程来消化 relay log，在 5.7 又增加了基于组提交的并行复制，大大改善了主从延迟的问题。

4.2 MySQL 半同步复制

在 MySQL 异步复制的基础上，又出现了一种改进的复制方式，称为：半同步复制。其原理如下：

  • 在主库开启 binlog 的情况下
  • 如果主库有增删改的语句，会记录到 binlog 中
  • 主库通过 IO 线程把 binlog 里面的内容传给从库的中继日志（relay log）中
  • 从库收到 binlog 后，发送给主库一个 ACK，表示收到了
  • 主库收到这个 ACK 以后，才能给客户端返回 commit 成功
  • 从库的 SQL 线程负责读取它的 relay log 里的信息并应用到从库数据库中

实现原理如下图：

img

跟传统的异步复制相比，半同步复制保证了所有给客户端发送过确认提交的事务，从库都已经收到这个日志了。

4.3 常见的读写分离方式

1.通过程序

开发通过配置程序来决定修改操作走主库，查询操作走从库。这种方式直连数据库，优点是性能会好点，缺点是配置麻烦。但是需要注意的是：从库需要设置为 read_only，防止配置错误在从库写入了数据。

这里提醒一点：

程序连接的用户建议不要给 super 权限，因为 super 权限的用户，即使整个库设置了 read_only，也能写入数据。

2.通过中间件

通过中间件实现读写分离，目前算是一种主流的方式。拿 MyCAT 举例：

在 schema.xml 文件中，dataHost 标签 balance 属性的值，决定了是否启用读写分离。

balance 各个值及对应的读写方法如下：

  • 0：不开启读写分离，读操作发送到 writehost
  • 1：全部的 readhost 与 stand by writehost 参与 select 语句的负载均衡
  • 2：所有读操作都随机在 writehost、readhost 上分发
  • 3：所有读请求随机分发到 writerhost 对应的 readhost 执行，writehost 不负担读压力

因此可以根据实际情况选择上面合适的读写分离策略。

什么情况下会出现主从延迟？

在本节的开始，我们说到，对于读写分离场景，最大的问题就是：主从延迟。那么在哪些情况下会出现主从延迟呢？这里大致总结一下可能导致主从延迟的场景：

  • 大表 DDL
  • 大事务
  • 主库 DML 并发大
  • 从库配置差
  • 表上无主键
  • …

因此，如果存在读写分离的情况，应尽量避免上述情况在业务高峰出现。当然，我们不能完全杜绝主从延迟。因此再介绍几种读写分离场景下应对延迟的方法。

读写分离如何应对主从延迟？

读写分离场景应该怎样应对主从延迟呢？这里来讨论一下几种常见的应对主从延迟的方法：

1.判断主从是否延迟

有些业务场景，如果所有请求都落在主库，主库压力会很大，但是在读写分离的情况，又不希望主从存在延迟的时候去读取从库。这种情况，就可以考虑查询时，先判断主从是否存在延迟，如果存在延迟，则查询落在主库，如果没延迟，则查询语句落在从库。

这里介绍几种判断主从延迟的方法：

第一种方法：判断 Seconds_Behind_Master 是否等于 0。

如果 Seconds_Behind_Master = 0，则查询从库，如果大于 0，则查询主库。

Seconds_Behind_Master: Seconds_Behind_Master 是在从库上执行 show slave status 时返回的其中一项，表示从库延迟的秒数。

其计算方法是：

从库服务器当前的时间戳与二进制日志中的事件的时间戳（在主库上的写入时间）相对比得到的。但是某些情况下，Seconds_Behind_Master 并不一定准确。比如网络中断时，Seconds_Behind_Master = 0，并不能代表主从无延迟。因此，有比这个更准确的一种方法：对比位点或 GTID。

第二种方法：对比位点或GTID

如果 Master_Log_File 跟 Relay_Master_Log_File 相等，并且 Read_Master_Log_Pos 跟 Exec_Master_Log_Pos 相等，则可以把读请求放到从库，否则读请求放到主库。

补充一下上面几个参数的意义：

几个参数均是通过 show slave status 返回的参数，用来查询主从复制的状态。

Master_Log_File：IO 线程正在读取的主库 binlog 文件名

Relay_Master_Log_File：SQL 线程最近执行的事务对应的主库 binlog 文件名

Read_Master_Log_Pos：IO 线程正在读取的主库 binlog 文件中的位点

Exec_Master_Log_Pos：SQL 线程最近读取和执行的事务对应的主库 binlog 文件中的位点

如果开启了 GTID 复制，则可以对比 Retrieved_Gtid_Set 和 Executed_Gtid_Set 是否相等，相等则把读请求放到从库，有差异则读请求放到主库。

同样补充下两个参数的意义：

前提是需要开启 GTID 两个参数才会有值，解释如下：

Retrieved_Gtid_Set：从库收到的所有日志的 GTID 集合

Executed_Gtid_Set：从库已经执行完的 GTID 集合

2.采用半同步复制

在本节的前面，我们讲解了半同步复制的原理，跟传统的异步复制相比，半同步复制保证了所有给客户端发送过确认提交的事务，从库都已经收到这个日志了。因此出现延迟的概率会小很多，当然实际生产应用时，建议结合上面讲的位点或 GTID 判断。

3.等待同步完成

依然采用 4.4.1 中介绍的几种判断是否有延迟的方法，只是应对方式不一样，比如存在延迟，则将情况反馈给程序，在前端页面提醒用户数据未完全同步，如果没有延迟，则查询从库。有人可能会觉得：这种方式谁会用啊？实际可以应用在内部人员看的报表业务上。因为报表可能涉及的 SQL 都比较复杂，存在延迟就考虑去查询主库，可能会对其它线上业务有影响，因此可以等待从库同步完成，再查询从库。

5. 哪些情况需要考虑分库分表？

MySQL 分库分表：把 MySQL 数据库物理地拆分到多个实例或者机器上去，从而降低单台 MySQL 实例的负载。

5.1 MySQL 分库分表拆分方法

MySQL 分库分表拆分方法分为：垂直拆分和水平拆分。

1.垂直拆分

垂直拆分一般是指下面几种情况：

  • 有多个业务，每个业务单独分到一个实例里面。
  • 在一个实例中有多个库，把这些库分别放到单独的实例中。
  • 在一个库中存在过多的表，把这些表拆分到多个库中。
  • 把字段过多的表拆分成多个表，每张表包含一部分字段。

比如一个电商网站，他的用户信息（userinfo）、商品信息（productinfo）、订单信息（orderinfo) 垂直拆分在三个实例中，如下图所示：

img

2.水平拆分

如果通过垂直拆分，表数据量仍然很大，那就可以考虑使用水平拆分了。

水平拆分：就是把同一张表分为多张表结构相同的表，每张表里存储一部分数据。而拆分的算法也比较多，常见的就是 取模、范围、和全局表 等。

还是拿上面电商网站的例子，比如业务会考虑做一次活动，预计注册用户会暴涨。那么就应该考虑对用户表进行水平拆分了，如下图：

img

这里使用的是取模分片，用户 ID 对 3 取模，如果余数是 0，则数据存放在userinfo01 库的 user 表中；如果余数是 1，则数据存放在 userinfo02 库的 user 表中；如果余数是 2，则数据存放在 userinfo03 库的 user 表中。

哪些情况需要考虑分库分表？

1.数据量过大，影响了运维的操作

如果数据量比较大，其对数据库影响也会比较大，最常见的比如：影响备份、大表 DDL 导致主从长时间延迟等。下面仔细讲讲这两种情况：

  • 备份：如果单张表或者单个实例数据量太大，那备份可能需要占用大量的 IO 和磁盘空间，并且持续时间还会比较久。曾经听说过有公司的单个实例的备份从凌晨持续到当天下午的情况，这种场景下，尽管在从库备份，如果开启了读写分离，对业务的影响也是比较大的。
  • DDL 导致主从长时间延迟：大表执行 DDL 不但会产生 MDL 写锁（MDL 的风险可以复习第 15 节），并且还会导致主从延迟。

直接执行大表 DDL，为什么会导致主从延迟？

原因是主库执行完 DDL 后，才会写入到 binlog 里，然后传输到从库执行，而又因为从库 SQL 线程是单线程的，因此，需要等到这条 DDL 在从库执行完成，其他事务才能继续执行，而从库执行 DDL 这段时间，主从都是延迟的。

2.把修改频繁的字段拆分出来

比如电商业务场景的用户表，这张表可能包含了用户唯一标识 ID，用户名，昵称，联系方式，性别，出生日期，注册时间，积分等等。这些字段中，其实也只有积分会变更的比较频繁，因此可以把积分字段独立出来，然后加上用户唯一标识 ID。这样，用户表的更新次数就大大降低了。

3.把大字段拆分出去

比如电商业务的商品表，表里可能包含了商品的价格，生产日期，产地，供应商，商品详情和使用说明等。我们会发现商品详情和使用说明的字段特别大，可能字段类型需要配置成 text 或 blob，类似的字段占用的磁盘空间也是比较大。维护起来会比较麻烦，因此这种情况下，建议把这类大字段水平拆分出来。

4.增长比较快的

还是拿电商业务举例，比如订单表，如果预估未来增长速度会比较快，那么可以考虑提前对订单表进行分库分表。防止单表增长过快。

5.降低不同库或者表的相互影响

电商业务会涉及多个模块，比如会员，订单，库存等。比如搞了一个活动，扫码送会员积分，如果会员相关的表和订单相关的表在一个库中，那么很可能会员的活动会影响到订单业务。因此可以考虑将这些模块对应的表拆分到不同的库中。避免不同业务表或者库的相互影响。

5.2 分库分表的实现

1.通过程序

如我已经通过垂直拆分，将不同业务表放在不同的业务库中。这种情况，程序只要每个业务配置不同的 database 即可。如果是水平拆分，业务可以通过程序实现，比如按照用户名首字母分，将不同首字母的名字过来的请求放在不同的库中。

2.通过数据库中间件

目前比较常见的就是通过数据库中间件，比如 MyCAT。这种对业务程序来说就比较友好了，某个业务库，程序端只要连逻辑库，而后端是通过 MyCAT，将这一个逻辑库指向多个物理库。然后通过分片字段，决定数据应该放在哪个分片里。如上面所讲的水平分割的示意图。

6. 如何安全高效地删除大量无用数据

为了防止单张表过大，很多情况下，我们需要对历史数据做归档或者删除操作。那么如何高效删除大量无用数据呢？这一节就来讨论这个问题。在聊数据删除前，我们先来看看 MySQL 的共享表空间和独立表空间。

6.1 共享表空间和独立表空间

InnoDB 的数据是按照表空间存放的，其表空间分为共享表空间和独立表空间。

1.共享表空间

共享表空间：表的数据放在系统共享表空间，也就是跟数据字典放一起。文件名为 ibdata1。可以通过参数 innodb_data_file_path 进行设置。在 my.cnf 中配置，如下：

1
2
[mysqld]
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend

表示用两个文件（ibdata1 和 ibdata2）组成表空间，文件 ibdata1 的大小为 1G，文件 ibdata2 的大小为 1G，autoextend 表示用完 1G 可以自动增长。

2.独立表空间

独立表空间：每个 InnoDB 表数据存储在一个以 .idb 为后缀的文件中。

参数 innodb_file_per_table 控制:

  • 设置为 on 表示使用独立表空间；
  • 设置为 off 表示使用共享表空间。

6.2 经验分享

一般情况下建议设置为独立表空间，原因是：如果某张表被 drop 掉，会直接删除该表对应的文件，如果放在共享表空间中，即使执行了 drop table 操作，空间还是不能回收。

6.3 几种数据删除形式

1.删除表

如果是某张表（假如表名为 t29）的数据和表结构都不需要使用了，那么可以考虑 drop 掉。出于安全考虑，建议的步骤如下：

  • 首先将表名改为 t29_bak_20191011；
  • 然后等待半个月，观察是否有程序因为找不到表 t29 而报错；
  • 如果没有跟表 t29 相关的报错，则半个月后直接 drop 掉 t29_bak_20191011。

2.清空表

如果是某张表（假如表名为 t29）的历史数据不需要使用了，要做一次清空，则可以考虑使用 truncate。

建议的步骤如下：

  • 创建一张与 t29 表结构相同的临时表：
    • create table t29_bak_20191011 like t29;
  • 并将数据拷贝到临时表：
    • insert into t29_bak_20191011 select * from t29;
  • 再清空该表：
    • truncate table t29;
  • 如果空间不够，观察半个月后，考虑转移 t29_bak_20191011 的数据到备份机器上。然后删除表 t29_bak_20191011：
    • drop table t29_bak_20191011;

经验分享

我不止一次遇到类似的情况：需要清空表而使用 delete from table_name，导致主从延迟和磁盘 IO 跑满的情况。原因是 binlog 为行模式的情况下，执行全表 delete 会生成每一行对应的删除操作，因此可能导致单个删除事务非常大。而 truncate 可以理解为 drop + create，在 binlog 为 row 模式的情况下，也只会产生一行 truncate 操作。所以，建议清空表时使用 truncate 而不使用 delete。

3.非分区表删除部分记录

实际更多的情况，我们是需要删除表中一部分数据，在没有配置分区表的情况下，就只能用 delete 了。也许我们有时会发现，在 delete 很多数据后，实际表文件大小没变化。这是什么原因呢？

原因是: 如果通过 delete 删除某条记录，InnoDB 引擎会把这条记录标记为删除，但是磁盘文件的大小并不会缩小。如果之后要在这中间插入一条数据，则可以复用这个位置，如果一直没有数据插入，就会形成一个 “空洞”。因此 delete 命令是不能回收空间的，这也是 delete 后表文件大小没变化的原因。

对于非分区表删除部分记录（比如删除 2017 年之前的数据，语句为：delete from table_name where date<‘2017-01-01’;），建议的步骤是：

  • 首先备份全表；
  • 确保 date 字段有索引，如果没有索引，则需要添加索引（目的是避免执行删除命令时，全表扫描）；
  • 如果要删除的数据比较多，建议写一个循环，每次删除满足条件记录的 1000 条（目的是避免大事务），删完为止
    • delete from table_name where date<'2017-01-01' limit 1000;
  • 最后重建表（目的是释放表空间，但是会锁表，建议在业务低峰执行）：
    • alter table table_name engine=InnoDB;
    • 或者 optimize table student;

4.分区表删除部分分区

从 MySQL 5.1 开始，支持分区。MySQL 分区是指将一张表按照某种规则（比如时间范围或者哈希等），划分为多个区块，各个区块所属的数据文件是相互独立的。相对慢慢 delete，这种方式删除历史数据效率高很多。因此，对于要经常删除历史数据的表，建议配置成分区表。以方便后续历史数据删除。

7. 使用 MySQL 时，应用层可以这么优化

对于数据库的使用，我们不仅仅要考虑 MySQL 本身的优化，还需要考虑应用层的优化。因为有些线上问题，就是由于应用层设置不合理导致的。本节就来聊聊几种应用层的优化方法。

7.1 使用连接池

MySQL 如果频繁创建和断开连接，那 MySQL 的开销会比较大，可能会占用过多的服务器内存资源，甚至导致响应时间变慢。此时就可以考虑使用连接池来改进性能。

连接池可以理解为：创建一些持久连接的“池”，新的请求可以使用这些连接池，减少创建和断开连接的次数。

其大致原理是：

  • 当进程启动时，创建相应的数据库连接池对象；
  • 如果程序需要请求数据库，则直接从连接池获取到一个连接；
  • 数据库请求完成后，释放数据库连接池。

连接池会不会导致服务器连接过多呢？

通常情况下，连接池不会导致服务器连接过多，因为它们会在进程间排队和共享连接。

在《高性能 MySQL》第 14 章：应用层优化中就提到：当遇到连接池完全占满时，应该将连接请求进行排队，而不是扩展连接池。这样可以避免将压力都转到 MySQL 上而导致 MySQL 连接数过多。

7.2 减少对 MySQL 的访问

避免对同一行数据做重复检索:

  • 增加 Redis 缓存层

在很多业务场景，Redis 充当着不可或缺的角色。这里介绍几种通过 Redis 缓解 MySQL 压力的场景：

  • 计数器
  • k-v 数据缓存
  • 消息队列
    • 我曾经工作的一家公司就使用 Redis 实现短信消息队列。如果用户在 APP 上点击注册用户名密码，需要填下手机号验证，程序会将该用户的手机号放在 Redis 的 list 中，然后另外一个程序一直去消费 list 中的手机号，取出手机后，则调用第三方短信接口，发送手机信息给用户。

7.3 单表过大及时归档

比如单张表过大，可能有下面这些影响：

  • 在修改表结构时导致长时间主从延迟；
  • 备份时间过久；
  • 查询速度可能也会变慢。

因此，可以考虑对历史数据归档（比如日志数据），控制单表的数据量。

7.4 代码层读写分离

在配置了 MySQL 主从环境的情况下，可以考虑使用读写分离，通过程序配置的这种方式，在专栏第 27 节有提到过。更新走主库，查询走从库。当然，主从同步可能因为大事务或者网络等原因导致同步延迟，在使用读写分离是也需要考虑到延迟这一点。

7.5 表的索引提前规划

在专栏第 10 节中，我们讲到了为什么添加索引能提高查询速度？因此，条件字段有索引显得格外重要。当开发或者 DBA 在创建新表时，就应该考虑在表的条件字段添加合适索引。这样可以避免业务上线后，数据量一上来就出现大量慢查询而导致 MySQL 服务器高负载。

8. MySQL 整体优化思路

8.1 硬件相关优化

8.2 系统层面优化

8.3 MySQL 层优化

1.参数优化

  • innodb_buffer_pool_size:

    • 该参数控制 InnoDB 缓存表和索引数据的内存区域大小。对性能影响非常大，建议设置为机器内存的 50-80%。

  • innodb_flush_log_at_trx_commit:

    • InnoDB 的 redo 日志刷新方式，对 InnoDB 的影响会很大。

  • sync_binlog:

    • 控制累积多少个事务后才将二进制日志 fsync 到磁盘。

  • innodb_file_per_table:

    • 开启独立表空间。

  • max_connection:

    • 最大连接数。不能设置的过小，防止客户端连接失败；也不能设置的过大，防止数据库内存资源过多消耗。

  • long_query_time:

    • 慢查询时间阀值。

  • query_cache_type/query_cache_size:

    • 建议这两个参数都设置为 0。

2.MySQL 设计优化

  • 使用 InnoDB 存储引擎，不建议使用 MyISAM 存储引擎；
  • 预估表数据量和访问量，如果数据量或者访问量比较大，则需要提前考虑分库分表；
  • 指定合适的数据库规范，在设计表、执行 SQL 语句时按照数据库规范来进行。（MySQL 操作规范将在下一节详细讲解）。

9. MySQL 操作规范

9.1 命名规范

1、表名建议使用有业务意义的英文词汇，必要时可加数字和下划线，并以英文字母开头；

2、库、表、字段全部采用小写；

  • MySQL 在 Linux 下默认是区分大小写的，而在 Windows 下不区分大小写。因此，防止出现问题，建议都设置为小写。

3、避免用 MySQL 的保留字，MySQL 保留字请参考官方手册：9.3 Keywords and Reserved Words；

4、命名（包括表名、列名）禁止超过 30 个字符；

5、临时库、表名必须以 tmp 为前缀，并以日期为后缀，如：tmp_shop_info_20190404；

6、备份库、表必须以 bak 为前缀，并以日期为后缀，如：bak_shop_info_20190404；

7、索引命名：

  • 非唯一索引必须按照”idx_字段名称”进行命名；
  • 唯一索引必须按照”uniq_字段名称”进行命名。

9.2 设计规范

1、主键：

  • 表必须有主键；
  • 不使用更新频繁的列做主键；
  • 尽量不选择字符串列做主键；
  • 不使用 UUID、MD5、HASH 做主键；
  • 默认使用非空的唯一键。

2、如无特殊要求，建议都使用 InnoDB 引擎；

3、默认使用 utf8mb4 字符集，数据排序规则使用 utf8mb4_general_ci；

  • 原因：utf8mb4 为万国码，无乱码风险；与 utf8 编码相比，utf8mb4 能支持 Emoji 表情。

4、所有表、字段都需要增加 comment 来描述此表、字段所表示的含义；

  • 比如：data_status TINYINT NOT NULL DEFAULT '1' COMMENT '1-代表记录有效，0-代表记录无效'。

5、如无说明，表必须包含 create_time 和 update_time 字段，即表必须包含记录创建时间和修改时间的字段；

6、用尽量少的存储空间来存储一个字段的数据：

  • 能用 int 的就不用 char 或者 varchar；
  • 能用 tinyint 的就不用 int；
  • 使用 UNSIGNED 存储非负数值；
  • 只存储年使用 YEAR 类型；
  • 只存储日期使用 DATE 类型。

7、存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE；

  • 原因：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。

8、尽可能不使用 TEXT、BLOB 类型；

  • 原因：会浪费更多的磁盘和内存空间，非必要的大量大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。如果实在有某个字段过长需要使用 TEXT、BLOB 类型，则建议独立出来一张表，用主键来对应，避免影响原表的查询效率。

9、禁止在数据库中存储明文密码；

10、索引设计规范：

  • 需要添加索引的字段
    • UPDATE、DELETE 语句的 WHERE 条件列；
    • ORDER BY、GROUP BY、DISTINCT 的字段（原因可复习第 6 节）；
    • 多表 JOIN 的字段（原因可复习第 8 节）。
  • 单表索引建议控制在 5 个以内；
  • 适当配置联合索引；
    • 比如方便查询能走覆盖索引，或者几个字段同时作为条件的概率很高时，当然还有其他很多种情况可以设置联合索引，具体可以复习第 13 节。
  • 业务上具有唯一性的字段，添加成唯一索引；
    • 遇到过几次字段在业务场景上要求唯一，但是该字段在数据库里的数据却出现了重复。因此在代码层考虑外，还需要在 MySQL 上的对应字段添加唯一索引。
  • 在 varchar 字段上建立索引时，建议根据实际文本区分度指定索引长度；
    • 原因：可以降低索引所占用的空间，并且很多时候，比如字符串基本是长度大于 20，但是只要建立长度为 20 的索引，就已经有很高的区分度了。可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
  • 索引禁忌：
    • 不在低基数列上建立索引，例如：性别字段。
    • 不在索引列进行数学运算和函数运算（原因，做函数操作可能会导致使用不了索引，具体可以复习第 4 节）

11、不建议使用外键；

  • 原因：外键会导致表与表之间耦合，update 与 delete 操作都会涉及相关联的表，十分影响 sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能损失。

12、禁止使用存储过程、视图、触发器、Event；

  • 原因：高并发的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性。

13、单表列数目建议小于 30；

9.3 SQL 语句规范

1、避免隐式转换；

  • 具体原因可以复习第 4 节中第 2 部分：隐式转换。

2、尽量不使用 select *,只 select 需要的字段 ；

  • 原因：读取不需要的列会增加 CPU、IO、NET 消耗，并且不能有效的利用覆盖索引。使用 SELECT * 容易在增加或者删除字段后导致程序报错。

3、禁止使用 INSERT INTO t_xxx VALUES (xxx)，必须显示指定插入的列属性；

  • 原因：容易在增加或者删除字段后导致程序报错。

4、尽量不使用负向查询；

  • 比如 not in/like。

5、禁止以 % 开头的模糊查询。

  • 原因：使用不了索引（具体例子可以复习第 4 节中的第 3 部分：模糊查询）。

6、禁止单条 SQL 语句同时更新多个表；

7、统计记录数使用 select count(*)，而不是 select count(primary_key)或者 select count(普通字段名)；

  • 原因：可能会导致走的索引不是最优的或者导致统计数字不准确。（具体例子可以复习第 9 节）。

8、建议将子查询转换为关联查询；

9、建议应用程序捕获 SQL 异常，并有相应处理；

10、SQL 中不建议使用 sleep()，如特殊需求需要用到 sleep()，请提前告知 DBA；

11、避免大表的 join。

9.4 行为规范

1、批量导入、导出数据必须提前通知 DBA 协助观察；

2、有可能导致 MySQL QPS 上升的活动，提前告知 DBA；

3、同一张表的多个 alter 合成一次操作；

4、不在业务高峰期批量更新、查询数据库；

5、删除表或者库要求尽量先 rename，观察几天，确定对业务没影响，再 drop。