如何设计高性能的mysql

1.0 高性能表设计规范

1. 数据字符集建议使用utf8mb4（支持表情符，mysql8.0 默认字符集）
2. 推荐使用innodb引擎，排序规则utf8_general_ci
3. 命名规划：a-z,0-9和_(下划线，用小写)，不能使用特殊字符；
4. 字段命名：不同表之间，同字段属性，要一样，如商品id：product_id （用小写）；
5. 库名，表名，字段名长度不超过32个字符，禁止使用mysql保留字；
6. 新增表，字段，需要补充字段备注信息；
7. 表必备字段：主键id，创建时间，更新时间，创建人，修改人；
8. 单实例表控制在2000之内，分库分表控制在1024（32*32）之内，单表字段数量控制在50之内
9. 必须要有主键，尽量别使用自增，要使用业务主键id（百度UUID，美团uuid等），别使用外键
10. 时间字段禁用TIMESTAMP类型，ip使用int类型存储
11. 金额建议用long类型（单位为分）
12. 固定字符串建议使用char
13. bool/枚举字段建议使用tinyint
14. 禁止使用“%”前进行查询
15. 单张表不建议超过千万数据，请做好分库分表解决方案
16. 设计一些范式/反范式，缓存，统计表
17. 字段尽量简单，避免null字段等等
18. 乐观锁字段表需加version字段，初始化为0；

2.0 高性能索引如何设计

1. 主键索引，唯一索引
2. 前缀索引（字段的前部分字段）和索引选择性
3. 联合索引（合适的前后索引顺序），联合索引建议不能超过5个字段
4. 覆盖索引：例如根据名称索引查询ID，和名称，只要这级索引查询就可以，避免回表
5. 避免索引重复和冗余索引：冗余：例如创建A,B联合索引，在创建A索引就是冗余；
6. 合理创建相关索引，单张表中索引数量不超过5个（索引最大数量16个，总索引字段不得超过256个字节），索引过多容易导致索引膨胀（新增，修改，删除响应）
7. 不在低基数上建索引，如性别
8. 使用索引扫描来做排序
9. 索引命名规则是idx_表名缩写_列名缩写_n(表示第几个索引),索引字段不能为null；
   10.等等

3.0 查询优化

1. 是否向数据库请求不需要的数据/字段，例如明明需要十条数据，而你全部拿出来，做软分页，影响性能
2. 是否存在扫描额外的记录；尽量用一些覆盖索引之类的，还有一些业务，拿到数据，尽量在业务上去过滤，不要用msql过滤
3. 重构查询方法，例如1：一个复杂的查询拆解成多个查询
4. 查询尽量控制在一张表上，连表查询请勿超过3张表
5. 优化group by（最好是索引字段排序），limit （尽量用索引ID大于取10，如果用limit 100000,10性能很慢），Union（这种要是有排序，是先拿到全部，在去limit；要是可以在各自加上limit性能好些）
6. 多表关联时，尽量用小表驱动大表
7. 模糊搜索全表扫描，建议使用es/solr
8. 尽量使用批查询（减少io），使用in（log n），代替or（O（n）），单次查询少于1000个数据
9. 大数据操作时，建议分配操作，5000数据一次；
10. 当只需要一条数据的时候，使用limit 1；
11. 尽量使用inner join ，避免 left join ，让查询优化器来自动选择小表驱动大表
12. Union的优化，Union 是将两张表存放在一张表上，在取limit 20； 这样可以改造成子表，都取20，合成40的临时表，再取20，性能提升
13. 避免索引失效的情况
14. 等等

3.1 索引失效的几种情况

1. like 以% 开头，索引无效，后缀是% 有效
2. or语句前后没有同时使用索引
3. 联合索引，不是使用第一列索引，索引无效
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
5. 在索引列上使用 is null 或is not null 操作，索引失效； 只要是索引字段，建议不能为空
6. 索引字段上使用 not ,<>,!=,，索引失效
7. 对索引字段进行计算操作，字段上使用函数，索引失效
8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效
9. 等等

4.0 如何突破单库性能瓶颈

1. 硬件cpu：服务器选择高主频多核CPU类型，同时开启最大性能和关闭CPU CIE和C status 。高频加速mysql执行，多核解决并发问题。
2. 内存：服务器选择大内存，同时开启最大性能和关闭NUMA
3. 磁盘：优先SSD机械盘使用高转速硬盘
4. 参数优化：适当增大buffer poll，redo log buffer 相关缓冲层资源大小；单机单实例能超过物理内存的60-80%，单机多实例（按照多个mysql），不能超过物理内存的80%

5.0 mysql 高可用

5.1 基础软硬件避免单点

1. 数据库集群节点不在同一机柜，不在同一电源，不在同一交换机（避免机柜掉电，交换机故障，电源老化导致数据库集群全局故障）；建议数据库集群跨机房部署
2. 避免光纤被挖，机房布线（避免单一网络供应商，单个公有云，单个供电传送网）；建议跨云同步，跨云双活

5.2 mysql架构

1. keepalived+VIP+Mysql 主从/双主
2. MMM
3. MHA
4. Galera
5. mysql InnoDB Cluster

6.0 分库分表升级（shardjdbc，mycat）

saas 系统—租户id定义，如商品信息，常用两种分库分表算法，取模/冷热划分
1：商品信息就可以根据租户id进行取模，进行分库分表
2：冷热数据，如订单，按时间划分，把一年前的冷数据，存放到elasticsearch，或者放到时间表上。
3：地区划分等等

7.0 mysql基准测试工具

1. mysqlslap
2. sysbench