1、现象:在线业务系统出现了三张表以上的关联查询
建议:说明业务逻辑在表设计上的实现不合理,需要进行表结构调整,或进行列的冗余,或进行业务改造。
2、现象:大表拆成多张小表之后,表之间通过ID关联,需要关联查询的时候,根据ID到表中再取出对应的值
建议:可在子表适当冗余主表的字段,避免回表查询。
3、误区:表数据一多了,就要拆分表
正解:不能为了拆表而拆表,要与业务切合,我们的设计永远是以符合业务发展为第一出发点。
讨论:是否可以归档,建立历史库?数据是否符合冷热分离? 数据是否可以直接删除?
4、现象:单行太长,即列太多
建议:行长不要超过8K, innodb_page_size的一半,性能最佳,不跨页。
5、现象:非常核心且数据量不大且并发度不高的表,可以建立外键
建议:外键所保证的数据一致性应该由程序来保障,没必要建立外键,外键带来的维护成本很高,也会产生性能问题。
6、现象:两表关联查询,字符集不一致
正解:两表字符集,全库字符集必须保持一致。
7、误区:所有int类型直接使用bigint就行了
正解:同理,如果可能尽量使用TINYINT、SMALLINT、MEDIUM_INT,且加上UNSIGNED。
讨论:是否一刀切会更好?比如两表关键查询,一边是tinyint,一边是int,无法使用索引,而且表join会按照数据类型申请内存。
8、误区:大量使用enum字段,提升性能
正解:enum的兼容性不好,容易插入期望之外的数字,突然出错。使用tinyint代替之 讨论:其实这里最大的出发点是担心开发对此字段的使用不好,产生期望之外的数据错误。
9、误区: timestamp性能更优,时间使用timestamp,而不是datetime
正解:除非有国际化需要,否则不要使用timestamp。且两者性能差异已不明显, datetime多占一倍的存储空间也可以接受,但是datetime比如受控, timestamp如果建表不注意引起数据错乱 。
10、误区:所有字段都使用varchar
正解:定长的字符使用char,性能提升不止一倍。比如身份证号、MD5值 。
11、误区:可以提前预留字段,以防备用
正解:严禁预留字段,无法从流程还是规范上都说不过去,修改列类型更是得不偿失。
12、误区:ID使用uuid
正解:随机字符串会任意分布在很大的空间,导致INSERT和SELECT语句变得很慢,性能也不如整型。
13、现象:关联表的列类型不一致
正解:一定要一致,避免隐式转换。
讨论:其实在一些情况下,是不需要一致的,但为了避免情况复杂化,一刀切有时候更有效。
14、误区:数值类型尤其是涉及资金的列使用float或double类型无所谓
正解:一定要使用decimal类型,避免数值在运算中丢失。
15、误区:表每列都单独建立索引,一列一索引
正解:每个表在查询中只能使用一个索引,这个可以从执行计划中看的出来。
16、现象:一个索引包含的列太多
建议:一个索引包含的列一般不超过3个,最多不超过5个。
17、误区:只需要在where条件上建立索引就行了,不用管查询列
正解:如果可以建立覆盖索引,就建立覆盖索引;如果能打出三星,就一定要打出三星索引。
1、误区:较多表进行关联查询
正解:严禁超过三以上的表进行关联查询。
2、误区:对更新特别频繁的表进行count(*)
正解:非DA不要对更新频繁的表进行count(*)操作,若不需要特别精确,可以从数据字典表取值。
3、现象:很多返回的结果集其实是不需要排序的
建议:如果order by的列上没有索引,可以加上order by null,提升性能。
4、误区:count(column) 和 count(*) 是一样的
正解:count(column) 是表示结果集中有多少个column字段不为空的记录,而count(*) 是表示整个结果集有多少条记录。
5、误区:大量使用or或union
正解:or的性能比较低下,如果可能,推荐使用union或union all来代替。而如果肯定or的左右结果集没 有交集,可直接使用union all来代替union。
6、误区:子查询和join的性能一样没区别
正解:虽然join性能不佳(相对于Oracle而言),但仍然强于子查询,优先使用等价join的。
update operation o set status = 'applying' where o.id in (select id from (select o.id, o.status from operation o where o.group = 123 and o.status not in ( 'done' ) order by o.parent, o.id limit 1) t); +----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+ |1 |PRIMARY |o |index| |PRIMARY|8 | |24 |Using where;Using temporary | | 2 | DEPENDENT SUBQUERY | | | | | | | | Impossible WHERE noticed after reading const tables | |3 |DERIVED |o |ref |idx_2,idx_5 |idx_5 |8 |const|1 |Using where;Using filesort | +----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+ ----- update operation o join (select o.id, o.status from operation o where o.group = 123 and o.status not in ( 'done' ) order by o.parent, o.id limit 1) ton o.id = t.id set status = 'applying'; +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+ | 1 | PRIMARY | | | | | | | | Impossible WHERE noticed after reading const tables | |2 |DERIVED |o |ref |idx_2,idx_5 |idx_5|8 |const|1 |Using where;Using filesort | +----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+ |
7、误区:使用比较多在分页limit,且offset非常大
正解:将数据一次性load到程序中进行排序,让计算的工作交给程序。非用不可,使用子查询代替之。
select *from t1 where ftype=1 order by id desc limit 99999999, 100;
---à select * from (select * from t1 where id > ( select id from t1 where ftype=1 order by id desc limit 99999999, 1) limit 100) t order by id desc;
8、误区: exists性能in高
正解:MySQL中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的。这个是要看情 况的。如果查询的两个表大小相当,那么用in和exists差别不大。而对于not in 和not exists来说,如果查询语 句使用了not in 那么内外表都进行全表扫描,没有用到索引而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
select * from A where cc in (select cc from B) ----效率低,用到了A表上cc列的索引
select * from A where exists(select cc from B where cc=A.cc) ----效率高,用到了B表上cc列的索引
select * from B where cc in (select cc from A) ----效率高,用到了B表上cc列的索引
select * from B where exists(select cc from A where cc=B.cc) ----效率低,用到了A表上cc列的索引
9、现象:一条SQL返回不知道大约会预期回返回多少行结果,或根本就不需要返回那么多行
建议:很多情况下,可在SQL后面加上limit n,如果明确知道只会返回一行结果,加limit 1。
10、现象:在复杂SQL的最后才加入where条件
建议:子查询中提前加入where条件,提前过滤掉比较多的数据。
select * from my_order o left join my_userinfo u on o.uid = u.uidleft join my_productinfo p on o.pid = p.pid where ( o.display = 0 ) and ( o.ostaus = 1 ) order by o.selltime desc limit 0, 15; +----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+ | 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL | | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+select * from (select * from my_order o where ( o.display = 0 ) and ( o.ostaus = 1 ) order by o.selltime desc limit 0, 15) o left join my_userinfo u on o.uid = u.uid left join my_productinfo p on o.pid = p.pid order by o.selltime desc limit 0, 15; +----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+ | 1 | PRIMARY | |
11、现象:in (....)中的值的个数过多
建议:不要超过500个,超过500个性能会剧烈下降
12、误区:无脑进行order by,不管列是否可以使用其他的列代替
正解:可以使用具备等同业务逻辑含义的、已经建立索引的列代替。比如order by adtime; 但是在adtime列此时没有加入索引,现在的order by 必须要引起非常大的磁盘排序,但是有时候我们的ID列是具备时间属性的,因此可以使用order by primaryid; 来代替之。
13、现象:不了解order by中null的前面顺序
正解:Oracle和MySQL对比一下:
Oracle:
order by colum asc 时,null默认被放在最后
order by colum desc 时,null默认被放在最前
nulls first 时,强制null放在最前,不为null的按声明顺序[asc|desc]进行排序
nulls last 时,强制null放在最后,不为null的按声明顺序[asc|desc]进行排序
MySQL:
order by colum asc 时,null默认被放在最前
order by colum desc 时,null默认被放在最后
ORDER BY IF(ISNULL(update_date),0,1) null被强制放在最前,不为null的按声明顺序[asc|desc]进行排序
ORDER BY IF(ISNULL(update_date),1,0) null被强制放在最后,不为null的按声明顺序[asc|desc]进行排序
14、现象:大量使用not in
建议:使用left join代替
原SQL: select col1, col2 from table where col3 not in (select col4 from table2);
改写成:select col1, col2 from table a left join table2 b where a.col3 = b.col4 and b.col4 is null;
15、现象:SQL中时间范围不合理
建议:很久之前的时间其中明显不应该再存在有用的数据,比如每天跑批,如果有,早就应该跑出来了。
可以纪录一下上次成功的时间点,把此点带进去,减少扫描范围
select * from abc where change_time <‘2017-08-01’;
-----> select * from abc where change_time <‘2017-08-01’and change_time >‘2017-07-01’;
16、误区:无脑使用between或范围区间
正解:这样容易导致索引失效,有的时候,between可以转换为in。比如索引建在id,a列上。
原SQL: select * from abc where a=‘b’and id between 7 and 10;
改写成 :select * from abc where a=‘b’and id in (7,8,9,10);
17、误区:防止隐式转换只能通过修改列的类型转换完成
正解:如果不方便修改列类型,可以通过函数在等式右边完成转换。
18、查询语句只要完成功能就行,带不带条件不影响
正解:查询语句要带上条件
SELECT `model`,`hwversion`,`version`,`day`,`createtime` FROM `rpt_upgrade_model_ver_daily` ORDER BY createtime DESC LIMIT 1; |
上面的查询查询createtime最新的一条,没有带where条件,表数据行1千多万行,查询耗时20s左右,这种一定要加上where createtime>current_time-10*60(最近10分钟)。
19.批量插入没什么影响
正解:批量插入使用不当会长时间的锁等待。
insert into t_rank_result (id,version_id,rank_no,intervention_rank_no,res_id,begin_time,end_time,intervention_time,final_rank_no ) SELECT NULL,43797777,(@row := @row + 1),t1.rank_no AS intervention_rank_no,t.res_id,t1.begin_time,t1.end_time, t1.intervention_time, ifnull(t1.rank_no,@row) FROM t_resource_sort_mapping t LEFT JOIN t_rank_intervention t1 ON t.res_id = t1.res_id and t1.rank_type = 2 AND (t1.category=1 or t1.category=3) AND t1.rank_subtype = '2' , (select @row := ifnull(max(rank_no),0) from t_rank_result where version_id = 43797777) t3 WHERE t.del_flag=0 AND t.sort_id = 2 and not exists(select 1 from t_rank_result t2 where version_id = 43797777 and t.res_id = t2.res_id); |
上面这个是insert inot ..... 复杂查询; 这种是典型的"Bulk Insert",会导致表t_rank_result自增锁长时间等待。
以下几种bulk insert 尽量不要在线上业务使用:
1)insert into .... select ...;
2)repalce into ... select ...;
3)load data ....
20.批量更新(update/delete)很随意,不用考虑数据库是不是有问题
正解:批量更新(update/delete)一定记得批量做,不要一次一条sql直接update或delete 全表/超过10w行数据,导致数据库负载直接飙高,性能受到影响。
同理:线上业务不要使用大事务,大事务产生长时间的锁等待,导致从库延迟,严重会导致binlog的单个事务超过1G,从库复制中断。
delete from t_ssp_non_audit_ad_info where launch_time < ‘${sixHourBefore}’; |