JavaGuide知识点整理——MySQL高性能优化建议

数据库命名规范

• 所有数据库对象名称必须使用小写字母并用下划线分割
• 所有数据库对象名称禁止使用mysql保留关键字
• 数据库对象的命名要做到见名知意，并且最好不要超过32个字符
• 临时表必须以tmp_为前缀并且日期为后缀。备份表必须以bak_为前缀日期为后缀。
• 所有存储相同数据的列名和列类型必须一致(一般作为关联列，如果查询时类型不一致会自动进行隐式类型转换，造成列上的索引失效，导致查询效率降低)。

数据库基本设计规范

所有表必须使用InnoDB存储引擎

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

数据库和表的字符集统一使用UTF8

兼容性更好，同意字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前要进行转换会造成索引失效，如果数据库中有存储emoji表情的需要，字符集可以采用utf8mb4字符集。

所有表和字段都需要添加注释

表和列都需要注释，从一开始就要进行数据字典的维护。

尽量控制单表数据量在500w以内

500w并不是mysql数据库的限制，过大会造成修改表结构，备份，恢复都有很大的问题。
可以用历史数据归档，分库分表等手段来控制数据量大小

谨慎使用MySQL分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表
谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据。

尽量做到冷热数据分离，减小表宽度

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节。
减少磁盘IO,保证热数据的内存缓存命中率(表越宽，把表装载进内存缓冲池所占用的内存也就越大，会消耗更多的IO)
更有效的利用缓存，避免读入无用的冷数据。
经常一起使用的列放到一个表中。

禁止在表中建立预留字段

预留字段的命名很难做到见名知意。预留字段也无法确认存储的数据类型。而对预留字段类型的修改，会对表进行锁定。

禁止在数据库中存储图片，文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。
建议文件，图片存储专门的服务器中，数据库只存储文件地址信息。

禁止在线上做数据库压力测试

禁止从开发环境，测试环境直接连生产环境数据库

数据库字段设计规范

优先选择符合存储 需求的最小的数据类型

列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点数量也就越少。在遍历时所需要的io次数也就越多。索引的性能也就越差。

避免使用text,bolb数据类型，最常见的text类型可以存储64k的数据

建议把blob或者text列分离到单独的扩展表中。
MySQL内存临时表不支持text，blob这样的大数据类型，如查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，MySQL还是要进行二次查询，使得sql性能很差，但不是说一定不能使用这样的数据类型。
如果一定要使用建议把blob或者text列分离到单独的扩展表中，查询时一定不要使用select *而只取出必要的列。
因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且text列上是不能有默认值的。

避免使用ENUM类型

修改 ENUM值需要使用alter语句。enum类型的排序操作效率低，需要额外操作。禁止使用数据作为enum的枚举值

尽可能把所有列定义为not null

因为索引null列需要额外的空间来保存，所以要占用更多的空间。进行比较和计算时也要对null值做特别的处理。

使用timestamp(4个字节)或者datetime(8个字节)存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
TIMESTAMP 占用 4 字节和 INT 相同，但比 INT 可读性高
超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储

注意不要用字符串存储时间，因为字符串存储日期要占用更多的空间，而且无法用日期函数进行计算和比较。

同财务相关的金额必须使用decimal类型

因为Decimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每四个字节可以存储九位数字，并且小数点要占用一个字节。Decimal可以用来存储比bigint更大的整形数据。

索引设计规范

限制每张表上的索引数量，建议单表索引不超过5个

索引并不是越多越好，索引可以提高效率同样可以降低效率
所以可以增加查询效率，但是会降低插入和更新的效率。甚至某些情况也会降低查询效率。
因为MySQL优化器在选择如何查询的时候，会根据统一信息，对每个可以用到的索引进行评估，以生成一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加MySQL优化器生成执行计划的时间，同样降低查询性能。

禁止给表中的每一列都建立单独的索引

MySQL5.6之前，一个sql只能使用一个索引，5.6之后虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。

每个InnoDB表必须有个主键

InnoDB是一种索引组织表，数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。
InnoDB是按照主键索引的顺序来组织表的

• 不要使用更新频繁的列作为主键，不适用多列主键
• 不要使用UUID,MD5,HASH,字符串作为主键
• 主键建议使用自增ID值，或者雪花id

常见索引列建议

• 出现select,update,delete语句的where从句中的列
• 包含order by,group by,distinct中的字段
• 并不要将符合1，2中的字段的列都建立在一个索引，同查将1，2中的字段建立联合索引效果更好
• 夺标join的关联列

如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机IO,增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

• 区分度最高的放在联合索引的最左侧
• 尽量把字段长度小 的列放在联合索引的最左侧
• 使用最频繁的列放到联合索引的左侧

避免建立冗余索引和重复索引

比如把id设为主键索引，唯一索引，非空索引 这就算是重复索引
然后把a,b,c三列设为联合索引，a,b设为联合索引，a设为索引，这样算是冗余索引。

对于频繁的查询优先考虑使用覆盖索引

覆盖索引就是包含了所有查询字段(where,select,order by,group by包含的字段)的索引。
覆盖索引的好处：

• 避免InnoDB表进行索引的二次查询：InnoDB是以聚集索引的顺序来存储的，对于InnoDB来说，二级索引在叶子节点中所保存的是行的主键信息，如果使用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取到我们真是所需要的数据，而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
• 可以把随机IO变成顺序IO加快查询效率：由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少很多。因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

索引SET规范

尽量避免使用外键约束

• 不建议使用外键约束，但是一定在表与表之间的关联键上建立索引
• 外键可用于保证数据的完整性，但是建议在业务端实现
• 外键会影响父表和子表的写操作从而降低性能

数据库sql开发规范

建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少sql编译所需要的时间，还可以解决动态sql所带来的sql注入问题。
只传参数，比传递sql语句更高效，相同语句可以一次解析，多次使用，提高处理效率。

避免数据类型的隐式转换

隐式转会导致索引失效。比如id是整数，sql如下：select * from user where id = '1'

充分利用表上已经存在的索引

避免使用双%进行模糊查询。如果只有后置%，无前置%，是可以用到列上的索引的。
一个sq只能利用到复合索引中的一列进行范围查询。在定义联合索引的时候，如果a列要用到范围查询，就把a列放到联合索引的右侧
使用left join或者not exists来优化not in。

数据库设计时，应该对以后扩展进行考虑

程序连接不同的数据库使用不同的账号，禁止跨库查询

• 为数据库迁移和分库分表留出余地
• 降低业务耦合
• 避免权限过大而产生的安全风险

禁止使用select * ，必须使用select 字段列表查询

• select *消耗更多的CPU和IO以及网络带宽资源
• 无法使用覆盖索引
• 可减少表结构变更带来的影响

禁止使用不含字段列表的insert语句

哪怕是全字段插入，也不可以省略字段列表，如：

insert into t values ('a','b','c');

应使用：

insert into t(c1,c2,c3) values ('a','b','c');

避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中。且子查询中为简单的sel(不含union，group by,order by,limit等)时，才可以把子查询转化为关联查询进行优化。
子查询性能差的原因：
子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

避免使用JOIN关联太多的表

对于MySQL来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。
在MySQL中，对于同一个sql多关联一个表，就会多分配一个关联缓存。如果在一个sql中关联的表越多，所占用的内存也就越大。
如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的话，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。
同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL最多允许关联61个表，但是建议不要超过5个。

减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

对应同一列进行 or 判断时，使用in 代替or

in的值不要超过500个，in操作可以更有效利用索引。or大多数情况下很少能用到索引。

禁止使用order by rand()进行随机排序

order by rand()会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序。并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO以及内存资源。
推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

where从句禁止对列进行函数转换和计算

对列进行函数转换或者计算时会导致无法使用索引
不推荐：

where date(create_time)='20190101'

推荐：

where create_time >= '20190101' and create_time < '20190102'

在明显不会有重复值时使用union all 而不是union

• union 会把两个结果集所有的数据放到临时表中后再去进行去重操作
• union all不会再对结果集进行去重操作

拆分复杂的大sql为多个小sql

• 大sql逻辑上比较复杂，需要占用大量CPU进行计算的sql
• MySQL中，一个sql只能使用一个CPU计算
• SQL拆分后可以通过并行执行来提高处理效率

数据库操作行为规范

超过100w行的批量增删改操作，要分批多次进行操作

大批量操作可能会造成严重的主从延迟
主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
binlog日志为row格式时会产生大量的日志
大批量写操作会产生大量日志。特别是对于row格式二进制数据而言。由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志里也就会越多。日志的传输和恢复所需要的时间也就越长。这也是造成主从延迟的一个原因。
避免产生大事务操作
大批量修改数据，一定是在一个事务中进行的。这就会造成表中大批数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响。
特别是长时间的阻塞会占满所有数据库的可用链接，这会使生产环境中 的其他应用无法连接到数据库，因此一定要注意大批量增删改操作要进行分批。

对于大表使用pt-online-schema-change修改表结构

• 避免大表修改产生的主从延迟
• 避免在对表字段进行修改时进行锁表

对于大量数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。
pt-online-schema-change 它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在所有数据复制完成之后，把新表命名为原表，原表删除。把原来的一个DDL操作，分解成多个小的批次进行。

禁止为程序使用的账号赋予super权限

• 当达到最大连接数限制时，还运行一个由super权限的用户连接
• super权限只能留给dba处理问题的账号使用

对于程序连接数据库账号，遵循权限最小原则

• 程序使用数据库账号只能在一个DB下使用，不准跨库
• 程序使用的账号原则上不准有drop权限

本篇笔记就记到这里，如果稍微帮到你了记得点个喜欢点个关注，也祝大家工作顺顺利利，生活愉快！