1：库表设计
- 库名、表名、字段名采用小写字母,并采用下划线分割;对相关功能的表应该使用相同的前缀，如member_xxx,前缀通常为库名或依赖主实体对象;
- 所有的表以及字段都必须有备注、详细说明表以及字段的含义
- 涉及货币金额或其他精度敏感的数据必须使用定点数Decimal来替代float和double
- 库名、表名、字段名禁止使用mysql保留字,如data、like、desc等
- 控制表字段数,单表不超过50个纯int/20个varchar(10)字段等同存储体积的字段数,上限控制在20~50
2：索引设计
基本规则：索引不是越多越好,能不添加索引尽量不添加索引,过多的索引会严重降低插入和更新的效率，并带来读写冲突和死锁
- 索引名称必须使用小写，普通索引按照idx_字段名_字段名[_字段名]进行命名,唯一索引按照 uniq_字段名_字段名[_字段名]进行名
- 表必须有主键，推荐使用独立业务的AUTO_INCREMENT列或全局ID生成器做主键，禁止使用多字段做联合主键
- 不使用UUID/MD5/HASH等函数生成无规则值做主键,效率极差
- 索引数量控制
- 单张表中索引数量不超过5个
- 单个索引中的字段数不超过5个
- 对字符串使用前缀索引,索引长度不超过10个字符
- 索引字段的顺序需要考虑每个字段去重以后的数量,区分度最大的[个数最多的]放在前面
- 合理创建联合索引(避免冗余),符合最左前缀原则：（a,b,c）相对于（a）、(a,b)、(a,b,c)
- 可能需要添加索引的字段
- Order by，group by，distinct的字符按需要添加在索引的后面
- update,delete语句需要根据where条件添加索引
- 对于join操作,需要join字段上添加索引
- 索引使用禁忌
- 不使用%前导的查询,如like ‘%ab’
- 不使用负向查询，如 not in/not lilke/<>
- 不在区分度的列上建立索引,如性别
- 不在索引列进行数学运算和函数运算
示例：假设在表table中id建立索引
- select col_A,col_B from table where id+1>100 不会使用索引
- select col_A,col_B from table where id>100+1 会使用索引
3：sql优化
- 线上尽量少使用 大sql,可能一条大sql就把整个数据库堵死，将复杂sql拆分为多条简单的sql,化繁为简
- 一条sql只能在一个cpu运算,如果sql比较复杂执行效率会非常低
- 简单sql缓存命中率更高
- 减少锁表时间
- 充分利用多核cpu，提高并发效率
- 减少mysql端的数学运算和逻辑判断，避免sql语句出现md5()、order by rand()等
- 过滤表记录合并不去重的情况，改写union为union all
- 减少使用拼接sql,使用预编译语句，降低sql注入的概率
- where 条件中的非等值条件(in between < <= > >=)会导致使用不了联合索引的后续字段,注意避免
- where条件比较,字段类型和传入值必须保持类型一直,避免隐式转换
示例
字段：code varchar(50) not null coment '编码' #code 上建立了索引
- select * from table_name where code=10001；不会使用索引
- select * from table_name where code='10001'；会使用索引
- Limit 分页优化
- 传统分页
select * from table limit 1000,10
limit原理：
LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目
limit 1000,10//检索记录行1001-1010,偏移量越大则越慢
- 推荐分页
- 分页方式1：
Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;
id是主键查询的速度比较快
- 分页方式2：
-
程序取ID: Select id from table limit 10000,10;
-
Select * from table WHERE ID in(123,456…);