通用技术 mysql 亿级数据优化

rocl · 2018年08月28日 · 最后由 初学狗 回复于 2018年10月25日 · 4796 次阅读
本帖已被设为精华帖!

无意中看到,觉得写的不错,搜索了另外一篇文章,把以下两篇文章合并(没有实践调查),以备需要时查询
https://blog.csdn.net/zhanglingge/article/details/48414419
https://www.toutiao.com/a6594314336913588743/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1&timestamp=1535418075&app=news_article&utm_source=weixin&iid=41524942220&utm_medium=toutiao_android&group_id=6594314336913588743

第一阶段:

  1. 一定要正确设计索引

  2. 一定要避免SQL语句全表扫描,所以SQL一定要走索引(如:一切的 != 等等之类的写法都会导致全表扫描)

  • 存储过程,游标,触发器,都是不推荐的,可以忽略。

  • in是走索引的。

  • 大于号小于号应该是可以走索引的(经过测试,并不一定会导致全表扫描,可能会走索引)。

  • 建议先查一下explain。

  1. 一定要避免 limit 10000000,20 这样的查询

  2. 一定要避免 LEFT JOIN 之类的查询,不把这样的逻辑处理交给数据库

  3. 每个表索引不要建太多,大数据时会增加数据库的写入压力

  4. 应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

  5. 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

  6. 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
    select id from t where num is null
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
    select id from t where num=0

  7. 尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
    select id from t where num=10 or num=20
    可以这样查询:

    select id from t where num=10
    union all
    select id from t where num=20
  8. 下面的查询也将导致全表扫描:(不能前置百分号)
    select id from t where name like ‘%c%’
    若要提高效率,可以考虑全文检索。

  9. in 和 not in 也要慎用,否则会导致全表扫描,如:
    select id from t where num in(1,2,3)
    对于连续的数值,能用 between 就不要用 in 了:
    select id from t where num between 1 and 3

  10. 如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
    select id from t where num=@num
    可以改为强制查询使用索引:
    select id from t with(index(索引名)) where num=@num

  11. 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
    select id from t where num/2=100
    应改为:
    select id from t where num=100*2

  12. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
    select id from t where substring(name,1,3)=’abc’–name以abc开头的id
    select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

    应改为:
    select id from t where name like ‘abc%’
    select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

  13. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

  14. 在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。

  15. 不要写一些没有意义的查询,如需要生成一个空表结构:
    select col1,col2 into #t from t where 1=0
    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
    create table #t(…)

  16. 很多时候用 exists 代替 in 是一个好的选择:
    select num from a where num in(select num from b)
    用下面的语句替换:
    select num from a where exists(select 1 from b where num=a.num)

  17. 并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

  18. 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

  19. 应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

  20. 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

  21. 尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

  22. 任何地方都不要使用 select * from t,用具体的字段列表代替“*”,不要返回用不到的任何字段。

  23. 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

  24. 避免频繁创建和删除临时表,以减少系统表资源的消耗。

  25. 临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使 用导出表。

  26. 在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

  27. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

  28. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

  29. 使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

  30. 与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

  31. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

  32. 尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

  33. 尽量避免大事务操作,提高系统并发能力。

第二阶段:

  1. 采用分表技术(大表分小表)

    1. 垂直分表:将部分字段分离出来,设计成分表,根据主表的主键关联
    2. 水平分表:将相同字段表中的记录按照某种Hash算法进行拆分多个分表
  2. 采用mysql分区技术(必须5.1版以上,此技术完全能够对抗Oracle),与水平分表有点类似,但是它是在逻辑层进行的水平分表

第三阶段(服务器方面):

  1. 采用memcached之类的内存对象缓存系统,减少数据库读取操作

  2. 采用主从数据库设计,分离数据库的读写压力

  3. 采用Squid之类的代理服务器和Web缓存服务器技术

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
共收到 23 条回复 时间 点赞
恒温 将本帖设为了精华贴 08月28日 09:27

我刚刚了解为啥要加索引,以前都是想加就加……

这个可以作为mysql优化的checklist👍 . 一个小小的想法:如果LZ可以加上优化的步骤如使用Explain 语句检查问题原因,检查Process List,检查最大连接数等类似过程,那就更完美了。

这个写的很棒,楼主可接受公众号转载?

  1. “并且应尽可能的让字段顺序与索引顺序相一致。”,这个是不必要的,mysql会自动排序。
  2. 存储过程,游标,触发器,都是不推荐的,可以忽略。
  3. in是走索引的。
  4. “一切的 > < != 等等之类的写法都会导致全表扫描” 大于号小于号应该是可以走索引的。

建议先查一下explain。
另外,去搜索一下otter,类似的技术你这里没提。
你引用的技术帖子已经很老了,觉得能落后5年,建议多看看。

第19条:上周刚刚出了一个线上事故,就是因为这个原因,mysql有自动优化的机制,会自动根据数据量而采用它认为的最有效的索引。解决办法:是设置mysql强制采用我们设置的索引。

易寒 回复

mysql认为最有效的索引跟你们设置的索引,有哪些具体的不同吗?为什么会引发线上事故啊?

zyanycall 回复

感谢指正。 @rocl 楼主可以更新下。

zyanycall 回复

1、5.7前两天刚验证过,顺序是有影响的,导引列的重要性一直在被强调
3、in是可以走索引,但是相比exists和少量key的union,可以推荐放弃用in
4、我一直理解 =、>=、<=存在会走索引,但是纯粹的> < !=是不行的,刚查了一下跟数据分布是有关系的,感觉mysql越来越像oracle了,统计信息在执行计划里面起的作用会越来越多

另外,除此之外,还有个隐式转换最坑爹了,估计基本所有关系数据库通杀吧~

槽神 回复

1.真不知道你在这强调什么,还是就没看懂问题。
https://www.zhihu.com/question/36996520
https://tech.meituan.com/mysql_index.html

“顺序是有影响的”,把实锤放出来。最左前缀匹配原则是有mysql的内部实现算法支撑的,你的“顺序是有影响的”,依据是什么?

槽神 回复

还有,你这么牛,你去文章中找其他问题去,别这里有个人回复了,你就来喷来找茬,你搞点儿新鲜的。
我的回复不是给你划重点让你深刻思考的。
你这样的还没有实锤的,纯蹭热度。

zyanycall 回复

大佬,我错了,我没描述清楚,我是想表达建索引的字段顺序……并不是来diss你,纯探讨而已,截图也是用来表明如果不是你们的提示我也没有认真去查清楚个中究竟……
以后你的帖子我一概不回了便是,免得被帽子压死~

我这次比较聪明就看看和点了赞。

zailushang 回复

本来我们设置的索引是 A(店铺),B(订单状态),常规情况下,mysql自己会用B来过滤订单数据,这是他自己算法决定的。但是突然有一天晚上,explain出来以后,他先以A来查询订单,查出来几百多w条数据。查询速度达到了6s左右,上游已经超时返回了。而且该接口时系统的发起入口,该接口失败,后面调度就走不了啦。订单一直无法指派给骑士。影响了几个小时的订单指派。

感觉oneapm和 听云可以重金买你的文章了,这些都是创业公司经常踩的坑。他们的apm产品也是经常抓这些数据的。

看你们讨论得这么热烈,我只能默默的拿起mysql的书看看

这不是反讽吧?😁 都是常见的开发规范,能不能落实得好是另一回事……

写的很棒!有几条在压测时有体会,但大部分没有想过,学习了

易寒 回复

实打实的经验教训啊。。。

我们的mysql

rocl #21 · 2018年08月30日 作者

收到,会根据大家的意见重新更新 !我本来也是备查的

在查询之前应该用expain select 语句来看看查询效率

一定要避免SQL语句全表扫描,所以SQL一定要走索引(如:一切的 > < != 等等之类的写法都会导致全表扫描)
mysql 在8.0之前是没有针对字段的统计直方图的,所以走索引不是一定会快

mysql承载量1~2kw还好,,达到5kw就要想其他得方法了,分库 分表 分块,,在索引以及sql上做一些手脚固然有效,但是一旦过了5kw(个人认为,之前公司有张表到了5kw....),就会很影响性能了,索引对查询友好,但是对插入也是负担.最后还是裁开 无论是落实到什么方案,亿级别的mysql,,,真是不敢想象.宛如一颗定石炸弹

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册