mysql数据库开发规范【推荐】(2)-木庄网络博客

当前第2页返回上一页

(1)不在低基数列上建立索引，例如性别、类型。但有一种情况，idx_feedbackid_type (f_feedback_id,f_type)，如果经常用 f_type=1 比较，而且能过滤掉90%行，那这个组合索引就值得创建。有时候同样的查询语句，由于条件取值不同导致使用不同的索引，也是这个道理。

(2)索引选择性计算方法（基数 ÷ 数据行数）

Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname，越接近1说明col1上使用索引的过滤效果越好

(3)走索引扫描行数超过30%时，改全表扫描

5.最左前缀原则

(1)mysql使用联合索引时，从左向右匹配，遇到断开或者范围查询时，无法用到后续的索引列
比如索引idx_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引，where条件包含上面三种情况的字段比较则可以用到索引，但像 where c1=a and c3=c 只能用到c1列的索引，像 c2=b and c3=c等情况就完全用不到这个索引

(2)遇到范围查询(>、<、between、like)也会停止索引匹配，比如 c1=a and c2 > 2 and c3=c，只有c1,c2列上的比较能用到索引，(c1,c2,c3)排列的索引才可能会都用上

(3)where条件里面字段的顺序与索引顺序无关，mysql优化器会自动调整顺序

6.前缀索引

(1)对超过30个字符长度的列创建索引时，考虑使用前缀索引，如 idx_cs_guid2 (f_cs_guid(26))表示截取前26个字符做索引，既可以提高查找效率，也可以节省空间

(2)前缀索引也有它的缺点是，如果在该列上 ORDER BY 或 GROUP BY 时无法使用索引，也不能把它们用作覆盖索引(Covering Index)

(3)如果在varbinary或blob这种以二进制存储的列上建立前缀索引，要考虑字符集，括号里表示的是字节数

7.合理使用覆盖索引减少IO

INNODB存储引擎中，secondary index(非主键索引，又称为辅助索引、二级索引)没有直接存储行地址，而是存储主键值。
如果用户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据列，从而避免回表进行二次查找，节省IO因此效率较高。

例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升。

8.尽量不要在频繁更新的列上创建索引

如不在定义了 ON UPDATE CURRENT_STAMP 的列上创建索引，维护成本太高（好在mysql有insert buffer，会合并索引的插入）

五. SQL设计

1.杜绝直接 SELECT * 读取全部字段

即使需要所有字段，减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响

2.能确定返回结果只有一条时，使用 limit 1

在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。

3.小心隐式类型转换

(1)转换规则

a. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换

b. 两个参数都是字符串，会按照字符串来比较，不做类型转换

c. 两个参数都是整数，按照整数来比较，不做类型转换

d. 十六进制的值和非数字做比较时，会被当做二进制串

e. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp

f. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较

g. 所有其他情况下，两个参数都会被转换为浮点数再进行比较。

(2)如果一个索引建立在string类型上，如果这个字段和一个int类型的值比较，符合第 g 条。如f_phone定义的类型是varchar，但where使用f_phone in (098890)，两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的，最糟的是string转换后的float，mysql无法使用索引，这才导致了性能问题。如果是 f_user_id = ‘1234567' 的情况，符合第 b 条,直接把数字当字符串比较。

4.禁止在where条件列上使用函数

(1)会导致索引失效，如lower(email)，f_qq % 4。可放到右边的常量上计算

(2)返回小结果集不是很大的情况下，可以对返回列使用函数，简化程序开发

5.使用like模糊匹配，%不要放首位

会导致索引失效，有这种搜索需求是，考虑其它方案，如sphinx全文搜索

6.涉及到复杂sql时，务必先参考已有索引设计，先explain

(1)简单SQL拆分，不以代码处理复杂为由。

(2)比如 OR 条件： f_phone='10000' or f_mobile='10000'，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者union all。

(3)先explain的好处是可以为了利用索引，增加更多查询限制条件

7.使用join时，where条件尽量使用充分利用同一表上的索引

(1)如 select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4 ，如果t1.c与t2.c字段相同，那么t1上的索引(b,c)就只用到b了。此时如果把where条件中的t2.c=4改成t1.c=4，那么可以用到完整的索引

(2)这种情况可能会在字段冗余设计（反范式）时出现

(3)正确选取inner join和left join

8.少用子查询，改用join

小于5.6版本时，子查询效率很低，不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化

9.考虑使用union all，少使用union，注意考虑去重

(1)union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all

(2)如果UNION结果中有使用limit，在2个子SQL可能有许多返回值的情况下，各自加上limit。如果还有order by，请找DBA。

10.IN的内容尽量不超过200个

超过500个值使用批量的方式，否则一次执行会影响数据库的并发能力，因为单SQL只能且一直占用单CPU，而且可能导致主从复制延迟

11.拒绝大事务

比如在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。

12.避免使用is null, is not null这样的比较

13.order by .. limit

这种查询更多的是通过索引去优化，但order by的字段有讲究，比如主键id与f_time都是顺序递增，那就可以考虑order by id而非 f_time 。

14.c1 < a order by c2

与上面不同的是，order by之前有个范围查询，由前面的内容可知，用不到类似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。

15.分页优化

建议使用合理的分页方式以提高分页效率，大页情况下不使用跳跃式分页

假如有类似下面分页语句:

SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;

这种分页方式会导致大量的io，因为MySQL使用的是提前读取策略。

推荐分页方式：

SELECT FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10

即传入上一次分页的界值

SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000，10) as t2 ON t1.id=t2.id

16.count计数

(1)首先count()、count(1)、count(col1)是有区别的，count()表示整个结果集有多少条记录，count(1)表示结果集里以primary key统计数量，绝大多数情况下count()与count(1)效果一样的，但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count()

(2)大数据量count是消耗资源的操作，甚至会拖慢整个库，查询性能问题无法解决的，应从产品设计上进行重构。例如当频繁需要count的查询，考虑使用汇总表

(3)遇到distinct的情况，group by方式可能效率更高。

17.delete,update语句改成select再explain

select最多导致数据库慢，写操作才是锁表的罪魁祸首

18.减少与数据库交互的次数，尽量采用批量SQL语句

(1)INSERT ... ON DUPLICATE KEY UPDATE ...，插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE，如果不重复则直接插入，影响1行。

(2)REPLACE INTO类似，但它是冲突时删除旧行。INSERT IGNORE相反，保留旧行，丢弃要插入的新行。

(3)INSERT INTO VALUES(),(),()，合并插入。

19.杜绝危险SQL

(1)去掉where 1=1 这样无意义或恒真的条件，如果遇到update/delete或遭到sql注入就恐怖了

(2)SQL中不允许出现DDL语句。一般也不给予create/alter这类权限，但阿里云RDS只区分读写用户

六. 行为规范