(4)不推荐使用联合主键。由于InnoDB索引的数据结构都是B+tree,对包含联合主键的表做大量写入,会导致InnoDB为了维持B+tree而移动大量数据,降低性能。
(5)禁止外键。对性能损耗特别大,一般的做法是,在业务层设计专门的逻辑或解决方案来保证数据的一致性,以最终一致的时差来换取即使访问的性能问题。
3.4 索引设计原则(1)不允许存在和主键重复的索引。主键其实就是一个非空的唯一索引,所以再在该字段上添加一个索引完全是多此一举。
(2)业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。唯一索引的值是唯一的,可以更快速地通过该索引确定某条记录。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,必然有脏数据产生。
(3)考虑索引列值的分布。评估某一栏位是否值得建索引,是根据选择性(符合条件笔数/总笔数)*100%来判断,选择性越低代表越值得,惯用的百分比界线是20%。如果某个数据列用于记录性别(只有"M"和"F"两种值),并且值出现的几率几乎相等,那么无论搜索哪个值都可能得到一半的数据行,在这种情况下索引的用处就不大。因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。
(4)为经常需要排序、分组和关联的字段建立索引。
(5)为常作为查询条件的字段建立索引。
(6)使用短索引,不要索引大字段。如果对varchar字段进行索引,必须指定一个前缀长度,尽量使用前缀索引,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。 使用前缀索引,对列的某几个字符进行索引,可以提高检索效率。
(7)合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c),区分度最高的列在最左边。
(8)合理使用覆盖索引减少IO,避免排序。
(9)不要过度使用索引,单个表上的索引数量建议不要超过5个 。
(10)删除不再使用或者很少使用的索引。
3.5 数据库里不建议存放业务日志业务日志的写入量比较大,影响mysql的性能,建议存放到非关系型数据库中。
IV SQL设计规范 4.1 避免数据类型的隐式转换例如:SQL中的字符串类型数据应该统一使用单引号。特别对纯数字的字符串,必须用单引号,否则会导致隐式转换而引起性能问题或索引失效问题。
4.2 避免复杂SQL对于非常复杂的SQL,特别是有多层嵌套,带子句或相关子查询的,应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。
4.3 批量插入使用INSERT语句一定要给出插入值的字段列表,这样即使表加了字段也不会影响现有系统的运行。对于小批量插入,可以将多条记录合并为同一个SQL,使用INSERT INTO tablename (col1,col2,...) VALUES (value1, value2,...),(value1, value2,...),...; 插入多条数据只有一次提交,效率明显提高。对于大批量插入和文件的导入导出,避免使用insert .... select和create table…select的形式,可能会阻止对源表的并发更新,如果查询比较复杂,会造成严重的性能问题。推荐使用select...into outfile和load data infile的组合来实现,采用这种方式MySQL不会给source_tab 加锁,还可以大大缩短数据的导出导入时间。但是,由于这种方式存在一定的安全隐患,所以如果需要使用这种方式,必须提交DBA审批,审批通过以后才可执行。
4.4 数据更新推荐使用主键更新,其它维度条件的更新操作会造成页锁。对多个表进行关联update操作风险较大,尤其是当执行计划出现错误时,可导致多个表同时被锁住,应该尽量避免。不带条件的update会导致全表操作,耗时较长,如有此需求,请联系DBA评估、操作。
4.5 避免使用TRUNCATE TABLE