尽量控制单表数据量的大小,建议控制在 500 万以内
- 500 万并不是 MySQL 数据库的限制,过大会造成修改表结构,备份,恢复都会有很大的问题。
- 可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小
谨慎使用 MySQL 分区表
-
分区表在物理上表现为多个文件,在逻辑上表现为一个表;
-
谨慎选择分区键,跨分区查询效率可能更低;
-
建议采用物理分表的方式管理大数据。
经常一起使用的列放到一个表中
- 避免更多的关联操作。
禁止在表中建立预留字段
- 预留字段的命名很难做到见名识义。
- 预留字段无法确认存储的数据类型,所以无法选择合适的类型。
- 对预留字段类型的修改,会对表进行锁定。
禁止在数据库中存储文件(比如图片)这类大的二进制数据
- 在数据库中存储文件会严重影响数据库性能,消耗过多存储空间。
- 文件(比如图片)这类大的二进制数据通常存储于文件服务器,数据库只存储文件地址信息。
不要被数据库范式所束缚
- 一般来说,设计关系数据库时需要满足第三范式,但为了满足第三范式,我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询,有时为了提高查询效率,会降低范式的要求,在表中保存一定的冗余信息,也叫做反范式。但要注意反范式一定要适度。
禁止在线上做数据库压力测试
禁止从开发环境,测试环境直接连接生产环境数据库
- 安全隐患极大,要对生产环境抱有敬畏之心!
单表不要包含过多字段
- 如果一个表包含过多字段的话,可以考虑将其分解成多个表,必要时增加中间表进行关联。
限制每张表上的索引数量,建议单张表索引不超过 5 个
-
索引并不是越多越好!索引可以提高效率同样可以降低效率。
-
索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。
-
因为 MySQL优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加MySQL 优化器生成执行计划的时间,同样会降低查询性能。
禁止使用全文索引
- 全文索引不适用于 OLTP 场景。
禁止给表中的每一列都建立单独的索引
- 5.6 版本之前,一个 sql 只能使用到一个表中的一个索引,5.6 以后,虽然有了合并索引的优化方式,但是还是远远没有使用一个联合索引的查询方式好。
每个 InnoDB 表必须有个主键
-
InnoDB
是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。 -
每个表都可以有多个索引,但是表的存储顺序只能有一种。
-
InnoDB 是按照主键索引的顺序来组织表的
-
不要使用更新频繁的列作为主键,不使用多列主键(相当于联合索引)
-
不要使用 UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长)
-
主键建议使用自增 ID 值
常见索引列建议
- 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
- 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
- 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
- 多表 join 的关联列
尽量不在数据库做运算,复杂运算需移到业务应用里完成
- 尽量不在数据库做运算,复杂运算需移到业务应用里完成。这样可以避免数据库的负担过重,影响数据库的性能和稳定性。数据库的主要作用是存储和管理数据,而不是处理数据。
优化对性能影响较大的 SQL 语句
- 要找到最需要优化的 SQL 语句。要么是使用最频繁的语句,要么是优化后提高最明显的语句,可以通过查询 MySQL
的慢查询日志来发现需要进行优化的 SQL 语句。
充分利用表上已经存在的索引
- 避免使用双%号的查询条件。如:a like ‘%123%’,(如果无前置%,只有后置%,是可以用到列上的索引的)
- 一个 SQL 只能利用到复合索引中的一列进行范围查询。如:有 a,b,c 列的联合索引,在查询条件中有 a 列的范围查询,则在 b,c
列上的索引将不会被用到。 - 在定义联合索引时,如果 a 列要用到范围查找的话,就要把 a 列放到联合索引的右侧,使用 left join 或 not exists
来优化 not in 操作,因为 not in 也通常会使用索引失效。
禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
-
SELECT * 会消耗更多的 CPU。
-
SELECT * 无用字段增加网络带宽资源消耗,增加数据传输时间,尤其是大字段(如 varchar、blob、text)。
-
SELECT * 无法使用 MySQL 优化器覆盖索引的优化(基于 MySQL
优化器的“覆盖索引”策略又是速度极快,效率极高,业界极为推荐的查询优化方式) -
SELECT <字段列表> 可减少表结构变更带来的影响
避免数据类型的隐式转换
- 隐式转换会导致索引失效
避免使用子查询,可以把子查询优化为 join 操作
- 通常子查询在 in 子句中,且子查询中为简单 SQL(不包含
union
、group by
、order by
、limit
从句)时,才可以把子查询转化为关联查询进行优化。
对应同一列进行 or 判断时,使用 in 代替 or
- in 的值不要超过 500 个,in 操作可以更有效的利用索引,or 大多数情况下很少能利用到索引
WHERE 从句中禁止对列进行函数转换和计算
- 对列进行函数转换或计算时会导致无法使用索引
明显不会有重复值时使用 UNION ALL 而不是 UNION
-
UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
-
UNION ALL 不会再对结果集进行去重操作