惠来建设局网站,网站开发用户登录前 登录后,网站域名备案更改,网上买保险网站一、表、列、索引设计误区
1、现象#xff1a;在线业务系统出现了三张表以上的关联查询
建议#xff1a;说明业务逻辑在表设计上的实现不合理#xff0c;需要进行表结构调整#xff0c;或进行列的冗余#xff0c;或进行业务改造。 2、现象#xff1a;大表拆成多张小表之…一、表、列、索引设计误区
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条件上建立索引就行了不用管查询列
正解:如果可以建立覆盖索引就建立覆盖索引;如果能打出三星就一定要打出三星索引。
二、SQL开发误区
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 ftype1 order by id desc limit 99999999, 100; ---à select * from (select * from t1 where id ( select id from t1 where ftype1 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 ccA.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 ccB.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 | derived2 | ALL | NULL | NULL | NULL | NULL | 15 | Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
| 1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |
----------------------------------------------------------------------------------------------------------------------------------------- 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 createtimecurrent_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.category1 or
t1.category3) AND t1.rank_subtype 2 , (select row : ifnull(max(rank_no),0) from t_rank_result where version_id 43797777) t3
WHERE t.del_flag0 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 尽量不要在线上业务使用 1insert into .... select ...; 2repalce into ... select ...; 3load data .... 20.批量更新(update/delete)很随意,不用考虑数据库是不是有问题
正解批量更新(update/delete)一定记得批量做不要一次一条sql直接update或delete 全表/超过10w行数据导致数据库负载直接飙高性能受到影响。
同理线上业务不要使用大事务大事务产生长时间的锁等待导致从库延迟严重会导致binlog的单个事务超过1G从库复制中断。 delete from t_ssp_non_audit_ad_info where launch_time ‘${sixHourBefore}’;
