数据库设计和表创建时就要考虑性能
设计表时要注意
字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null |
---|
尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好 |
使用枚举或整数代替字符串类型 |
尽量使用TIMESTAMP而非DATETIME |
单表不要有太多字段,建议在20以内 |
text字段分表存储 |
合理的字段属性长度,固定长度的表会更快。使用char而不是varchar |
索引优化
要根据查询有针对性的创建 |
---|
尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描 |
值分布很稀少的字段不适合建索引,例如"性别"这种只有两三个值的字段 |
字符字段只建前缀索引 |
sql编写优化
避免select *,将需要查找的字段列出来 |
---|
使用连接(join)来代替子查询 |
不做列运算:SELECT id WHERE age + 1 = 10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等 |
OR改写成IN:OR的效率是n级别,IN的效率是log(n)级别,in的个数建议控制在200以内 |
不用函数和触发器,在应用程序实现 |
避免%xxx式查询 |
少用JOIN |
尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描 |
对于连续数值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5 |
引擎对比
引擎对比 | MyISAM | InnoDB |
---|---|---|
行锁 | 不支持(读取时对需要读到的所有表加锁,写入时则对表加排它锁) | 支持 |
事务 | 不支持 | 支持 |
外键 | 不支持 | 支持 |
崩溃后的安全恢复 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
延迟更新索引 | 支持(极大提升写入性能 ) | 不支持 |
MyISAM适合SELECT密集型的表,而InnoDB适合INSERT和UPDATE密集型的表
分区
优点 | 缺点 |
---|---|
可以让单表存储更多的数据 | 一个表最多只能有1024个分区 |
分区表的数据更容易维护 | 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来 |
部分查询能够从查询条件确定只落在少数分区上,速度会很快 | 分区表无法使用外键约束 |
分区表的数据还可以分布在不同的物理设备上,从而利用多个硬件设备 | NULL值会使分区过滤无效 |
避免某些特殊瓶颈 | 所有分区必须使用相同的存储引擎 |
可以备份和恢复单个分区 |
分区 | 详情 |
---|---|
RANGE | 基于属于一个给定连续区间的列值,把多行分配给分区 |
LIST | LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择 |
HASH | 基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。 |
KEY | 类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值 |