MySQL：配置调优（三）

数据库范式：在设计数据表时，需要尽可能地遵循数据库范式，以确保数据的完整性和一致性。常见的数据库范式包括第一范式（1NF）、第二范式（2NF）、第三范式（3NF）等。
数据库关系：在设计数据表时，需要考虑不同数据表之间的关系，包括一对一关系、一对多关系和多对多关系等。需要合理地设计数据表之间的关系，以便能够方便地进行数据查询和操作。
数据库索引：在设计数据表时，需要考虑哪些字段需要建立索引，以便能够加快数据查询的速度。需要根据实际的查询需求，合理地选择建立索引的字段。
数据库性能：在设计数据表时，需要考虑如何提高数据库的性能。这包括合理地选择数据类型、避免使用过多的触发器和存储过程，设计合适长度等。

2.2.1.命名规范

命名规范对于代码的可读性、可维护性和可扩展性都非常重要。以下是一些常见的MySQL表设计的命名规范：

1）表名、字段名、索引名称必须使用小写字母或者数字，禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写。

2）表名单词之间可以使用下划线（_）分隔

3）表名、字段名、索引名称应该具有描述性，能够清晰地表达该表所存储的数据内容。

4）表名、字段名、索引名称应该尽量避免使用MySQL关键字作为表名，以免引起命名冲突

5）主键字段名应该以 "id" 结尾，例如 "user_id

6）主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名为idx_字段名。

2.2.2.选择合适的数据类型

数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适？
1）使用可以存下你的数据的最小的数据类型；

2）使用简单的数据类型，int要比varchar类型在mysql处理上简单;

3）尽可能的使用not nulls定义字段，

4）尽量少用text类型，非用不可时最好考虑分表。

5）小数类型，比如金额选择decimal

6）尽量使用varchar代替 char：因为首先变长字段存储空间小，可以节省存储空间

2.2.3.去除无用的索引--ptduplicatekey_checker工具的使用

此工具可以分析选定的 database 中的所有表中建立的index 中可能重复的索引，并给出了删除建议

2.2.4.数据库表的设计范式(三范式&反范式)

表的范式化和反范式化：范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。

数据库三范式（3NF):

第一范式：对属性的原子性，要求属性具有原子性，不可再分解。
第二范式：对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖。
第三方式：对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

我们设计表及其字段之间的关系，应尽量满足第三范式。但是有时候，可以适当冗余，来提高效率。比如以下这张表：

商品名称	商品型号	单价	数量	总金额
手机	华为	8000	5	40000

以上这张存放商品信息的基本表。总金额这个字段的存在，表明该表的设计不满足第三范式，因为总金额可以由单价*数量得到，说明总金额是冗余字段。但是，增加总金额这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

当然，这只是个小例子，大家开发设计的时候，要结合具体业务进行分析

2.2.5.选择合适的字段长度

在 mysql 中，varchar和char类型表示字符长度，而其他类型的则都表示字节长度。比如char(10)表示字符长度是 10，而bigint（4）表示显示长度是4个字节，但是因为 bigint 实际长度是8个字节，所以bigint（4）的实际长度就是 8 个字节。

在设计表的时候，需要充分考虑一个字段的长度，比如一个用户名字段（它的长度 5~20 个字符），你觉得应该设置多长呢？可以考虑设置为 username varchar（32）。字段长度一般设置为 2 的幂（也就是2 的 n次方）。

例如需要存储一个长度为10的字符串，可以将字段长度设置为16（2的4次幂），而不是设置为10或者其他不是2的幂次方的数字。这样可以更有效地使用内存，并提高数据库的性能。

在MySQL中，一个汉字通常占用两个字节的存储空间，而一个英文字符（包括数字和标点符号）通常只占用一个字节的存储空间

2.2.6.每个表都需要添加几个通用字段

一般来说，每个表都会具备以下这几个字段：

id：主键，一个表必须得有主键，必须。
create_time：创建时间，必须。
modifed_time/update_time：修改时间，必须，更新记录时，需要更新它。
version：数据记录的版本号，用于乐观锁，非必须。
remark ：数据记录备注，非必须。
modified_by：修改人，非必须。
creator ：创建人，非必须。

阿里开发手册也提到这个点，如图

2.2.7.一张表的字段不宜过多

一张表的字段不宜过多，一般尽量不要超过 20 个字段

2.2.8.尽可能使用 not null 定义字段

如果没有特殊的理由，一般都建议将字段定义为NOT NULL 。

NULL列在计算和比较的时候，NOT NULL 可以防止出现空指针问题。
NOT NULL列更节省空间，NULL值存储也需要额外的空间，它也会导致比较运算更为复杂，使优化器难以优化 SQL。
NULL值有可能会导致索引失效。
如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑，那就可以将这个字段设置为NOT NULL。

2.2.8.避免使用mysql保留字

如果库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号来引用属性名称，这将使得 SQL 语句书写、SHELL 脚本中变量的转义等变得非常复杂。

因此，我们一般避免使用MySQL保留字，如select、interval、desc等等

2.2.9.时间类型选择

我们设计表的时候，一般都需要加通用时间的字段，如create_time、modified_time等等。

那对于时间的类型，我们该如何选择呢？

对于 MySQL 来说，主要有date、datetime、time、timestamp 和 year。

date ：表示日期值，格式yyyy-mm-dd，范围1000-01-01 到 9999-12-31，3 字节。
time ：表示时间值，格式 hh:mm:ss，范围-838:59:59 到 838:59:59，3 字节。
datetime：表示日期时间值，格式yyyy-mm-dd hh:mm:ss，范围1000-01-01 00:00:00 到 9999-12-31 23:59:59，8 字节，跟时区无关。
timestamp：表示时间戳值，格式yyyymmddhhmmss，范围1970-01-01 00:00:01 到 2038-01-19 03:14:07，4 字节，跟时区有关。
year：表示年份值，格式yyyy，范围1901 到 2155，1 字节。

推荐优先使用datetime类型来保存日期和时间，因为存储范围更大，且跟时区无关

2.2.10.选择合适统一的字符集

数据库库、表、开发程序等都需要统一字符集，通常中英文环境用utf8。

MySQL 支持的字符集有utf8、utf8mb4、GBK、latin1等。

utf8：支持中英文混合场景，国际通用，3 个字节长度。
utf8mb4: 完全兼容 utf8，4 个字节长度，一般存储 emoji表情需要用到它。
GBK ：支持中文，但是不支持国际通用字符集，2 个字节长度。
latin1：MySQL 默认字符集，1 个字节长度。

补充：MySQL 8.0以后默认连接字符集从latin1改成了utf8mb4，字符序从latin1_swedish_ci改成了utf8mb4_0900_ai_ci

2.3.开发规范

MySQL开发规范是为了保证MySQL数据库系统的稳定性、可靠性和安全性而制定的一系列规范和标准。遵循MySQL开发规范可以提高数据库系统的运行效率和可维护性，同时也可以减少出错的可能性

2.3.1.写完SQL先explain查看执行计划（SQL性能优化）

日常开发写SQL的时候，尽量养成这个好习惯：写完SQL后，用explain分析一下，尤其注意走不走索引。通过show profile 来查看SQL对系统资源的损耗情况

explain select * from user where userid =10086 or age =18;

执行上述SQL语句，MySQL会返回一张表格，其中包含了查询语句的执行计划信息，包括访问类型、使用的索引、扫描的行数、排序方式等

id这一列代表sql语句执行的顺序，id值越大的行，越先执行，id值相同的情况下，执行顺序就从上往下执行

select_type

这个从字面意思来看呢。就是查询类型，它主要就是来区别普通查询、联合查询、子查询等

Partitions

分区，也就是查询的表所在的分区，如果是NULL的话，就代表该表没有被设置分区

type

该行查询所使用的访问类型，他的值有十多种，但是这些我们没有必要全部知道，这里我列出来最常见的八种，这八种类型的效率就代表从好到差：

System:这个是效率最高的，比如像where id=xxxx，但是在这里有个点我要说一下，就是啥吧，在MySQL5.7版本之前使用这类sql是可以出现System类型的，但是在5.7版本之后呢，就不会出现这个System类型了，转而是以Const类型来替代，也就是下面这种。
Const：mysql能对查询部分进行优化并将其转化为常量，用于primary key或者unique key的所有列与常数比较时，所以表最多只有一个匹配行，读取一次，速度比较快，

对primary key或者unique key字段进行的查询，就是const

select * from (select *from film where id=1)tmp;
eq_ref：primary key或者unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录，这是在const之外最好的连接类型了，简单的select查询不会出现这种type

使用join进行连表查询时，对unique key或者primary key字段进行关联条件

explain SELECT * from student left join project on student.project_id=project.id
Ref：代表在非唯一性索引或者非主键上进行的查询。

EXPLAIN SELECT * from student where name=‘name1’
Ref_or_null，与上面类似，但是可以搜索值为null的行

EXPLAIN SELECT * from student where name=‘namepro’ or name is null
Range：范围扫描，一般在in、between、>、<、>=等情况下使用，使用一个索引来检索给定范围的行。

EXPLAIN SELECT * from student where id>1
Index：即使全表扫描，我们在表中也设置有主键索引，此时会走索引，

select count(*) from student
All：全表扫描

EXPLAIN SELECT * from student

一般保证查询至少达到range级别，最好能达到ref。

possible_keys

表示该行查询可能使用到的查询索引，它是理论上的，某些情况下，是与实际用的索引不同的

key

上面是可能使用到的索引，而这个呢，就是该行实际使用到的索引。

ref

显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

rows

rows列代表该行查询所涉及到多少行数据，而非最终的结果。

Extra

这一列展示的是额外信息。

Using index：表示使用索引，但是select的字段需要设置索引，如果有order by的话，那么order by里面的字段也要设置了索引。

explain select name from student order by name

Using where：使用where语句。

explain select * from student where id > 1;

Using temporary：使用临时表

explain select distinct project_id from student；这种就是Using temporary，因为表中没有distinct project_id这种字段，因此是先建立个临时表，然后对这个临时表来进行去重，这种一般是需要优化的，首先可以使用索引来进行优化。

Using filesort：采用文件扫描对结果进行计算排序，效率很差，对于排序，只有select字段与order by字段都被覆盖的话，才允许使用Using index

Explain select * from student order by name（Using filesort）

总结：列几个个人觉得相对重要的属性：id、type、key、ref、extra。如果需要对sql进行优化的话，着重关注这几个就可以

2.3.2.操作delete或者update语句，加个limit

在执行删除或者更新语句，尽量加上limit，以下面的这条 SQL 为例吧：

delete from euser where age > 30 limit 200;

因为加了limit 主要有这些好处：

降低写错SQL的代价, 你在命令行执行这个SQL的时候，如果不加limit，执行的时候一个不小心手抖，可能数据全删掉了，如果删错了呢？加了limit 200，就不一样了。删错也只是丢失200条数据，可以通过binlog日志快速恢复的。
SQL效率很可能更高，你在SQL行中，加了limit 1，如果第一条就命中目标return，没有limit的话，还会继续执行扫描表。
避免了长事务，delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
数据量大的话，容易把CPU打满 ,如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢的。

2.3.3. SQL书写格式，关键字大小保持一致，使用缩进。

正例：

SELECT stu.name, sum(stu.score)
FROM Student stu
WHERE stu.classNo = '1班'
GROUP BY stu.name

反例：

SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '1班' group by stu.name.

显然，统一关键字大小写一致，使用缩进对齐，会使你的SQL看起来更优雅~

2.3.4. INSERT语句标明对应的字段名称

反例：

insert into Student values ('666','捡田螺的小男孩','100');

正例：

insert into Student(student_id,name,score) values ('666','捡田螺的小男孩','100');

2.3.5.变更SQL操作先在测试环境执行，写明详细的操作步骤以及回滚方案，并在上生产前review

变更SQL操作先在测试环境测试，避免有语法错误就放到生产上了。
变更Sql操作需要写明详细操作步骤，尤其有依赖关系的时候，如：先修改表结构再补充对应的数据。
变更Sql操作有回滚方案，并在上生产前，review对应变更SQL。

2.3.6.写完SQL语句，检查where,order by,group by后面的列，多表关联的列是否已加索引，优先考虑组合索引。（SQL性能优化）

反例：

select * from user where address ='深圳' order by age ;

正例：

添加索引
alter table user add index idx_address_age (address,age)

2.3.7.修改或删除重要数据前，先备份

如果要修改或删除数据，在执行SQL前一定要先备份要修改的数据，万一误操作，还能吃口后悔药

2.3.8. where后面的字段，留意其数据类型的隐式转换

反例：

//userid 是varchar字符串类型
select * from user where userid =123;

正例：

select * from user where userid ='123';

理由：

因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较，最后导致索引失效

2.3.9.修改或者删除SQL，先写WHERE查一下，确认后再补充 delete 或 update

尤其在操作生产的数据时，遇到修改或者删除的SQL，先加个where查询一下，确认OK之后，再执行update或者delete操作

2.2.10.减少不必要的字段返回，如使用select <具体字段> 代替 select *

反例：

select * from employee;

正例：

select id，name from employee;

理由：

节省资源、减少网络开销。
可能用到覆盖索引，减少回表，提高查询效率。

2.2.11.所有表必须使用Innodb存储引擎

Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好，所以呢，没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎

2.2.12. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

这个点，是阿里开发手册中，Mysql的规约。你的字段，尤其是表示枚举状态时，如果含义被修改了，或者状态追加时，为了后面更好维护，需要即时更新字段的注释。

2.2.13. SQL修改数据，养成begin + commit 事务的习惯；

正例：

begin;
update account set balance =1000000
where name ='捡田螺的小男孩';
commit;

反例：

update account set balance =1000000
where name ='捡田螺的小男孩';

2.2.14. WHERE从句中不对列进行函数转换和表达式计算

假设loginTime加了索引

反例：

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

正例：

explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

索引列上使用mysql的内置函数，索引失效

2.2.15.如果修改\更新数据过多，考虑批量进行。

反例：

delete from account  limit 100000;

正例：

for each(200次)
{
 delete from account  limit 500;
}

理由：

大批量操作会会造成主从延迟。
大批量操作会产生大事务，阻塞。
大批量操作，数据量过大，会把cpu打满。

2.2.16.索引列不参与计算

索引进行函数操作或者表达式计算会导致索引失效

2.2.17.子查询优化多分页

利用子查询优化超多分页场景。比如 limit offset , n 在MySQL是获取 offset + n的记录，再返回n条。而利用子查询则是查出n条，通过ID检索对应的记录出来，提高查询效率

4.2.18.事物内简洁高效

在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间（比如在事务内需要插入&&修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待行锁释放）

4.2.19.隔离级别

一般用用的是Read Commit（读已提交），MySQL默认用的是Repeatable read（可重复读）。选用什么隔离级别，主要看应用场景，因为隔离级别越低，事务并发性能越高。

4.2.20、update更新时where条件使用索引

在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住。

因此，当在数据量非常大的数据库表执行 update 语句时，如果没有使用索引，就会给全表的加上 next-key 锁，那么锁就会持续很长一段时间，直到事务结束，而这期间除了 select ... from 语句，其他语句都会被锁住不能执行，业务会因此停滞，接下来等着你的，就是老板的挨骂。

那 update 语句的 where 带上索引就能避免全表记录加锁了吗？并不是。

关键还得看这条语句在执行过程中，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

又该如何避免这种事故的发生？

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

官方的解释：

If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

大致的意思是，当 sql_safe_updates 设置为 1 时。

update 语句必须满足如下条件之一才能执行成功：

使用 where，并且 where 条件中必须有索引列；
使用 limit；
同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足如下条件之一才能执行成功：

使用 where，并且 where 条件中必须有索引列；
同时使用 where 和 limit，此时 where 条件中可以没有索引列；

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

总结

执行 update 语句的时候，确保 where 条件中带上了索引列，并且在测试时确认该语句是否走的是索引扫描，防止因为扫描全表，而对表中的所有记录加上锁。

我们可以打开 MySQL sql_safe_updates 参数，这样可以预防 update 操作时 where 条件没有带上索引列。

如果发现即使在 where 条件中带上了列索引列，优化器走的还是全标扫描，这时我们就要使用 force index([index_name]) 可以告诉优化器使用哪个索引。

4.3.运维优化

查询慢:
- 索引
- 走索引查询慢:数据轻量化处理
- 在查询之前，直接走一层缓存（Redis维护起来不方便）
- 字符串检索的场景导致查询低效
- 聚合表：生产请求就查询聚合表的数据，不走原表：根据需要把多条数据汇总为一条：比如，用户下单后，有一份订单明细，而订单明细表的量级太大。但在产品侧(前台)透出的查询功能是以「天」维度来展示的，那就可以将每个用户的每天数据聚合起来，在聚合表就是一个用户一天只有一条汇总后的数据
数据库写入慢
- 主从架构:实现读写分离:主库接收写请求，从库接收读请求。从库的数据由主库发送的binlog进而更新，实现主从数据一致（在一般场景下，主从的数据是通过异步来保证最终一致性的）
- 分库分表：分布式ID生成的方式，有借助MySQL自增的，有借助Redis自增的，有基于「雪花算法」自增的。具体使用哪种方式，那就看公司的技术栈了，一般使用Redis和基于「雪花算法」实现用得比较多。步骤如下：
  - 增量的消息各自往新表和旧表写一份
  - 将旧表的数据迁移至新库
  - 迟早新表的数据都会追得上旧表（在某个节点上数据是同步的）
  - 校验新表和老表的数据是否正常（主要看能不能对得上）
  - 开启双读（一部分流量走新表，一部分流量走老表），相当于灰度上线的过程
  - 读流量全部切新表，停止老表的写入七、提前准备回滚机制，临时切换失败能恢复正常业务以及有修数据的相关程序。