范文健康探索娱乐情感热点
投稿投诉
热点动态
科技财经
情感日志
励志美文
娱乐时尚
游戏搞笑
探索旅游
历史星座
健康养生
美丽育儿
范文作文
教案论文

MySQL性能调优二

  覆盖索引尽量用
  覆盖索引是非常有用的工具,能够极大地提高性能,三星索引里最重要的那颗星就是宽索引星。考虑一下如果查询只需要扫描索引而无须回表,会带来多少好处:
  索引条目通常远小于数据行大小,所以如果只需要读取索引,那 MySQL就会极大地减少数据访问量。这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助,因为索引比数据更小,更容易全部放入内存中。
  因为索引是按照列值顺序存储的,所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
  由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。
  尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),不是必要的情况下减少select*,除非是需要将表中的全部列检索后,进行缓存。EXPLAIN  select * from order_exp_cut where insert_time="2021-03-22 18:34:55" and order_status=0 and expire_time="2021-03-22 18:35:04" ;
  使用具体名称取代*EXPLAIN  select expire_time,id from order_exp_cut where insert_time="2021-03-22 18:34:55" and order_status=0 and expire_time="2021-03-22 18:35:04" ;
  解释一下Extra中的Using index
  当我们的查询列表以及搜索条件中只包含属于某个索引的列,也就是在可以使用索引覆盖的情况下,在Extra列将会提示该额外信息。以上的查询中只需要用到u_idx_day_status而不需要回表操作:不等于要慎用
  mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描EXPLAIN  SELECT * FROM order_exp WHERE order_no <> "DD00_6S";
  解释一下Extra中的Using where当我们使用全表扫描来执行对某个表的查询,并且该语句的WHERE子句中有针对该表的搜索条件时,在Extra列中会提示上述额外信息。Null/Not 有影响
  需要注意null/not null对索引的可能影响
  表order_exp的order_no为索引列,同时不允许为null,
  explain SELECT * FROM order_exp WHERE order_no is null; explain SELECT * FROM order_exp WHERE order_no is not null;
  可以看见,order_no is null的情况下,MySQL直接表示Impossible WHERE(查询语句的WHERE子句永远为FALSE时将会提示该额外信息),对于 is not null直接走的全表扫描。
  表order_exp_cut的order_no为索引列,同时允许为null,explain SELECT * FROM order_exp_cut WHERE order_no is null; explain SELECT * FROM order_exp_cut WHERE order_no is not null;
  is null会走ref类型的索引访问,is not null;依然是全表扫描。所以总结起来:
  is not null容易导致索引失效,is null则会区分被检索的列是否为null,如果是null则会走ref类型的索引访问,如果不为null,也是全表扫描。
  但是当联合索引上使用时覆盖索引时,情况会有一些不同(order_exp_cut表的order_no可为空):explain SELECT order_status,expire_time FROM order_exp WHERE insert_time is null; explain SELECT order_status,expire_time FROM order_exp WHERE insert_time is not null;
  explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time is null; explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time is not null;
  根据system>const>eq_ref>ref>range>index>ALL 的原则,看起来在联合索引中,is not null的表现会更好(如果列可为null的话),但是key_len的长度增加了1。所以总的来说,在设计表时列尽可能的不要声明为null。
  Like查询要当心
  like以通配符开头("%abc..."),mysql索引失效会变成全表扫描的操作explain SELECT * FROM order_exp WHERE order_no like "%_6S";
  此时如果使用覆盖索引可以改善这个问题
  explain SELECT order_status,expire_time FROM order_exp_cut WHERE insert_time like "%18:35:09";
  字符类型加引号
  字符串不加单引号索引失效explain SELECT * FROM order_exp WHERE order_no = 6; explain SELECT * FROM order_exp WHERE order_no = "6";
  MySQL的查询优化器,会自动的进行类型转换,比如上个语句里会尝试将order_no转换为数字后和6进行比较,自然造成索引失效。使用or关键字时要注意explain SELECT * FROM order_exp WHERE order_no = "DD00_6S" OR order_no = "DD00_9S"; explain SELECT * FROM order_exp WHERE expire_time= "2021-03-22 18:35:09"  OR order_note = "abc";
  表现是不一样的,第一个SQL的or是相同列,相当于产生两个扫描区间,可以使用上索引。
  第二个SQL中or是不同列,并且order_note不是索引。所以只能全表扫描
  当然如果两个条件都是索引列,情况会有变化:explain  SELECT * FROM order_exp WHERE expire_time= "2021-03-22 18:35:09"  OR order_no = "DD00_6S";
  这也给了我们提示,如果我们将 SQL改成union allexplain SELECT * FROM order_exp WHERE expire_time= "2021-03-22 18:35:09"  union all SELECT * FROM order_exp WHERE order_note = "abc";
  当然使用覆盖扫描也可以改善这个问题:explain SELECT order_status,id FROM order_exp_cut WHERE insert_time="2021-03-22 18:34:55" or expire_time="2021-03-22 18:28:28";
  使用索引扫描来做排序和分组
  MySQL有两种方式可以生成有序的结果﹔通过排序操作﹔或者按索引顺序扫描施﹔如果EXPLAIN出来的type列的值为"index",则说明MySQL使用了索引扫描来做排序。
  扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢,尤其是在IO密集型的工作负载时。
  MySQL可以使用同一个索引既满足排序,又用于查找行。因此,如果可能,设计索引时应该尽可能地同时满足这两种任务,这样是最好的。
  只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有当0RDER BY子句引用的字段全部为第一个表时,才能使用索引做排序。排序要当心
  ASC、DESC别混用
  对于使用联合索引进行排序的场景,我们要求各个排序列的排序顺序是一致的,也就是要么各个列都是ASC规则排序,要么都是DESC规则排序。
  排序列包含非同一个索引的列
  用来排序的多个列不是一个索引里的,这种情况也不能使用索引进行排序explain SELECT * FROM order_exp order by order_no,insert_time;
  尽可能按主键顺序插入行
  最好避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是对于I/O密集型的应用。例如,从性能的角度考虑,使用UUID来作为聚簇索引则会很糟糕,它使得聚簇索引的插入变得完全随机,这是最坏的情况,使得数据没有任何聚集特性。
  最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好。
  注意到向UUID主键插入行不仅花费的时间更长,而且索引占用的空间也更大。这一方面是由于主键字段更长﹔另一方面毫无疑问是由于页分裂和碎片导致的。
  因为主键的值是顺序的,所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子是页大小的15/16,留出部分空间用于以后修改),下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满,这也正是所期望的结果。
  如果新行的主键值不一定比之前插入的大,所以InnoDB无法简单地总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置-—通常是已有数据的中间位置——并且分配空间。这会增加很多的额外工作,并导致数据分布不够优化。下面是总结的一些缺点:
  写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机IO。
  因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。
  所以使用InnoDB时应该尽可能地按主键顺序插入数据,并且尽可能地使用单调增加的聚簇键的值来插入新行。优化Count查询
  首先要注意,COUNT()是一个特殊的函数,有两种非常不同的作用:它可以统计某个列值的数量,也可以统计行数。
  在统计列值时要求列值是非空的(不统计NULL)。
  COUNT()的另一个作用是统计结果集的行数。常用的就是就是当我们使用COUNT(*)。实际上,它会忽略所有的列而直接统计所有的行数。
  select count(*) from test; select count(c1) from test;
  通常来说,COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果,因此是很难优化的。在MySQL层面能做的基本只有索引覆盖扫描了。如果这还不够,就需要考虑修改应用的架构,可以用估算值取代精确值,可以增加汇总表,或者增加类似Redis这样的外部缓存系统。优化limit分页
  在系统中需要进行分页操作的时候,我们通常会使用LIMIT加上偏移量的办法实现,同时加上合适的ORDER BY子句。
  一个非常常见又令人头疼的问题就是,在偏移量非常大的时候,例如可能是select * from order_exp limit 10000,10;
  这样的查询,这时MySQL需要查询10010条记录然后只返回最后10条,前面10 000条记录都将被抛弃,这样的代价非常高。
  优化此类分页查询的一个最简单的办法是
  会先查询翻页中需要的N条数据的主键值,然后根据主键值回表查询所需要的N条数据,在此过程中查询N条数据的主键id在索引中完成,所以效率会高一些。EXPLAIN SELECT * FROM (select id from order_exp limit 10000,10) b,order_exp 					a where a.id = b.id;
  从执行计划中可以看出,首先执行子查询中的order_exp表,根据主键做索引全表扫描,然后与a表通过id做主键关联查询,相比传统写法中的全表扫描效率会高一些。
  从两种写法上能看出性能有一定的差距,虽然并不明显,但是随着数据量的增大,两者执行的效率便会体现出来。
  上面的写法虽然可以达到一定程度的优化,但还是存在性能问题。最佳的方式是在业务上进行配合修改为以下语句:EXPLAIN select * from order_exp where id > 67 order by id limit 10;
  采用这种写法,需要前端通过点击More来获得更多数据,而不是纯粹的翻页,因此,每次查询只需要使用上次查询出的数据中的id来获取接下来的数据即可,但这种写法需要业务配合。
  关于Null的特别说明
  对于Null到底算什么,存在着分歧:
  1、有的认为NULL值代表一个未确定的值,MySQL认为任何和NULL值做比较的表达式的值都为NULL,包括selectnull=null和select null!=null;
  所以每一个NULL值都是独一无二的。
  2、有的认为其实NULL值在业务上就是代表没有,所有的NULL值和起来算一份;
  3、有的认为这NULL完全没有意义,所以在统计数量时压根儿不能把它们算进来。
  假设一个表中某个列c1的记录为(2,1000,null,null),在第一种情况下,表中c1的记录数为4,第二种表中c1的记录数为3,第三种表中c1的记录数为2。
  在对统计索引列不重复值的数量时如何对待NULL值,MySQL专门提供了一个innodb_stats_method的系统变量,
  这个系统变量有三个候选值:
  nulls_equal:认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。
  如果某个索引列中NULL值特别多的话,这种统计方式会让优化器认为某个列中平均一个值重复次数特别多,所以倾向于不使用索引进行访问。
  nulls_unequal:认为所有NULL值都是不相等的。
  如果某个索引列中NULL值特别多的话,这种统计方式会让优化器认为某个列中平均一个值重复次数特别少,所以倾向于使用索引进行访问。
  nulls_ignored:直接把NULL值忽略掉。
  而且有迹象表明,在MySQL5.7.22以后的版本,对这个innodb_stats_method的修改不起作用,MySQL把这个值在代码里写死为nulls_equal。也就是说MySQL在进行索引列的数据统计行为又把null视为第二种情况(NULL值在业务上就是代表没有,所有的NULL值和起来算一份),看起来,MySQL中对Null值的处理也很分裂。所以总的来说,对于列的声明尽可能的不要允许为null。

2022年新泰市泉沟镇基干民兵点验大会召开通讯员陈焕记者徐慧为表扬先进,评树典型,检验民兵战备状态,激发练兵热情,7月6日,泉沟镇组织召开2022年基干民兵点验大会,市人武部领导镇党委成员及全体基干民兵参加点验大会。会上宣四川93名高校青年教师决战全省教学竞赛来源工人日报客户端原标题四川93名高校青年教师决战全省教学竞赛工人日报中工网记者李娜7月6日,第六届四川省高校青年教师教学竞赛总结表彰会在四川师范大学举行,在为期两天的教学竞赛中,19年山东67岁大妈怀孕生女,称退休金过万不会麻烦子女,现在啥样文知心录编辑知心录2019年,医院里,一位大爷小心翼翼地扶着自己妻子走到了妇产科,他对着医生说医生,给我安排一个病床,我妻子快要生了。医生看着眼前六七十岁的老人,有些不敢置信,这么独家报告发布!二季度究竟有多少人拿稳了手中基金?文末有惊喜银华基金联合中国基金报推出的二季度基民投资行为调查报告出炉通过对17506份有效问卷进行分析调查结果显示想要实现成功的投资基民更看重基金本身的表现而长期投资的意识还有待加本土新增47331,多地调整风险等级点蓝色字关注中央广电总台中国之声7月7日024时,31个省(自治区直辖市)和新疆生产建设兵团报告新增新冠肺炎本土确诊病例47例(上海17例,安徽17例,内蒙古4例,福建3例,广东373年山东女强人凭实力成千万富翁,远嫁阿富汗生下4子,现在如何文一鸣惊人录编辑一鸣惊人录1999年,山东女孩刘彩香跟一位小她六岁的阿富汗帅小伙阿吉结婚了。婚后,两口子生下了四位可爱的混血宝宝。十多年里,两人辗转中阿两地,克服种种困难,经历了重韩国餐饮价格上涨率达近30年最高水平韩式炸酱面涨幅最大韩式炸酱面(推特截图)海外网7月7日电据韩国中央日报7日报道,2022年上半年,韩国餐饮物价激增,韩式炸酱面价格涨幅最大,一份卖到6262韩元(折合人民币约32元)。韩国统计厅称,蚌医一附院患者总满意率全省第五名门诊满意率全省第三名近日,根据关于2021年第四季度全省三级医疗机构满意度调查情况的通报,在全省91家医疗机构中,蚌医一附院在患者总满意率方面荣获第五名门诊满意率第三名的好成绩。近年来,蚌医一附院始终韩国检方调查两名文在寅任内情报主管韩国首尔中央地方检察厅7日宣布,受理国家情报院对前院长朴智元和徐薰的举报,开始调查这两名前任总统文在寅执政时的情报部门主管。朴智元说,国情院指控毫无依据。在野的共同民主党认为举报出疲态尽显?多多驿站补贴潮起潮落,众多加盟网店选择退出大数据杀熟,一直以来都让消费者们苦不堪言。而如今,一些多多买菜的快递网点也惨遭背刺。不少网点发现,新账号和老账号的待遇完全不同,老账号的补贴接连暴跌,让他们无以为继。今年年初,多多博士女婿在校嫖娼,遭岳父愤而举报,校方已开启调查7月7日,一则岳父举报博士在读女婿长期嫖娼的视频在网络中流传。视频中,举报人提供的门诊就诊记录显示其病史中有多次找小姐的字眼。举报人还称,其是拿着妻子给的生活费去嫖娼的。目前,学校
小白选手机如何不踩坑?老司机给出建议,看评价口碑很重要今年的3月份可以说是非常适合换手机的时候,不仅有多款新机陆续发布,而且无论是配置还是价格都很香,但是无论是入手2K价位的主流机型还是五六千元的旗舰机,大家在入手之前还是要认清自己的整理了9道土豆最好吃的做法,鲜香美味,营养下饭,大人孩子都爱土豆是我们生活中常见的蔬菜,土豆又叫马铃薯,原产于南美洲,土豆是营养价值十分全面,有丰富的维生素C,营养齐全,结构合理,吸收利用率更是达到了百分之百,土豆是接近全价值的蔬菜,有专家韦霍斯特整个生涯都在努力达到世界顶级水平,很自豪来到曼联直播吧3月18日讯在足总杯14决赛对阵富勒姆的比赛前,曼联前锋韦霍斯特在接受俱乐部官网采访时,谈到了自己目前的一些情况。想再次前往温布利?韦霍斯特当然,我们想去那里,联赛杯决赛在温防范大股东掏空上市公司,全国人大代表沙雁建议出台背信罪司法解释中国经济周刊记者伍素文丨全国两会报道今年全国两会,全国人大代表深交所总经理沙雁建议出台背信罪司法解释,强化对资金占用刑事追责。长期以来,由于违法违规成本偏低等原因,大股东通过资金占实现良好开局!前两个月河南省经济主要指标增速高于全国河南省统计局昨日发布前两个月全省经济运行分析。12月全省经济运行呈现稳定恢复回升态势,主要指标增速高于全国,实现良好开局。前两个月,河南省工业生产延续稳定恢复态势。12月全省规模以恐怖片排行榜前十名,公认高分超级恐怖电影(不建议一个人观看)恐怖电影的导演就是抓住人们对电影好奇的心理,然后拍摄的一些超乎人们认知的电影,里面运用的是恐怖的色彩和拍摄手段,能够让人们很好的带入电影里面,而基本上看恐怖电影的人是又菜又爱看,所滕州城事滕县保卫战85周年祭,不能忘却的记忆!每当我们漫步滕县保卫战战斗遗址时,瞻仰着纪念碑群雕的时候,脑海里浮现出民族英雄王铭章将军在滕州英勇殉国,面对凶猛的来犯之敌,第22集团军总司令孙震任命王铭章为第41军代军长,统一指几十款皮肤免费拿!登录就送3000点券,三名英雄调整体验服三月十七日王者荣耀体验服又进行了一次更新,这一次的更新内容包括登录送伴生皮肤宝箱3000点券以及两张皮肤优惠券三名英雄调整,一起来看看吧体验服版本,不代表最终上线数值及设计。一登录宣布停售!网友要涨价?3月17日上海迪士尼乐园年卡停售冲上微博热搜引发网友热议上海迪士尼乐园宣布为了更好地管理客流和保障乐园内游客体验,自2023年3月17日上午800起,上海迪士尼度假区将在各销售渠道刚吃过早饭,得知国内3大好消息,网友中企给力!外媒要慌了!今天刚吃过早饭,得知了国内3大好消息,就迫不及待地来跟大家分享!从下面三个消息上可以看出,在如今强强对决的局面中,中企逐渐占据有利地位,而外媒确实低估了中企的综合科技实力,看来真的玩了七年的王者排位赛这七款皮肤几乎从没见过是不是该删了重做不知不觉王者荣耀也已经玩了有七年的时间,但在这七年中,有那么几款皮肤几乎在排位赛上压根就没见过,真希望官方要么把这些皮肤删了,要么直接升级重做好了。第一款,阿轲的致命风华,这款皮肤