高性能MySQL-笔记3-索引等等

18 篇文章 0 订阅
10 篇文章 0 订阅

高性能MySQL-笔记3-索引等等

关于索引使用及限制

B-Tree索引

匹配条件:(复合多列的索引)

  • 全值匹配
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精确匹配第一列并范围匹配第二列【必须是第一第二列】

限制条件:(复合多列的索引)

  • 不按最左第一个开始查找,则无法使用索引。
  • 必须是顺序条件,不能跳过中间索引列。【第二列排序是在精确匹配第一列情况下,第三列匹配是在精确匹配到第二列的情况下,生效的】
  • 范围查询后的所有列都无法使用索引优化。
其他索引
  • 哈希索引:Memory引擎表才支持,略过。【InnoDB有自适应哈希索引,自动的不管了】
  • 空间数据索引R-Tree,MyISAM引擎支持,略过。
  • 全文索引,特定需求下使用,另外再系统的学习。
  • 聚簇索引,这个是InnoDB表的主键索引。╮(╯_╰)╭
  • 覆盖索引,对InnoDB很有用的,需要索引覆盖到所有使用的数据,则会很大程度提升性能。【比如覆盖主键id,提高全表count的性能】【栗子:P171-P174】

使用策略

  • 使用独立的列,不对列数据进行函数或其他计算处理。
  • 对长数据做前缀索引,索引长度需要测试,限制是无法使用此索引排序与分组。
  • 非主键顺序插入大量的数据后,使用OPTIMIZE TABLE进行一次重新组织表。
  • 利用索引进行排序的时候,需要order by 的顺序与索引规则一致。

一个栗子

栗子对的是一张表 【P183】

  • 不要建立太多个索引,重复冗余的。【一个是空间的开销,一个是选择优化的开销】
  • 范围查询列尽量放在索引的最后列,避免索引无效。
  • 避免多个范围查询【书上的解决是使用逻辑绕过方案,我觉得直接保留筛选率最高的在前即可,因为业务上的范围查询可能很多,无法考虑到所有的绕过方案】
  • 最后总结:都是没用的东东,完全不切实际,因为通常的查询条件都呀的是动态的,有N个。╮(╯_╰)╭

一些其他信息

  • MySQL在5.0+ 引入了“索引合并”的策略,好像可以使用多个索引的样子 。【如果看到这个,其实说明需要优化索引了,据书上说,性能不高,使用或略索引语句:optimizer_switch 或者 IGNORE INDEX


关于查询性能优化【干货】

一些的建议

  • 关联查询禁止使用*返回所有关联表字段。通常使用table.*

  • 大量关联查询可以进行拆分多个查询进行。【比如十几张表的关联╮(╯_╰)╭ ,最后在子查询可以有固定值的情况下拆分】

  • 使用show full PROCESSLIST查看数据库线程状态

  • 一个对in(...)大量数据查询的优化 (╯‵□′)╯︵┻━┻ 这个在5.6的版本已经优化了 参考文章

    select * from table_a where table_a.col in (select col from table_b where ...)
    -- 以上这种写法,的效率会很低,因为mysql并不会先进行in内部的只查询,而是变成exists(),这样就是扫描table_a全表了。╮(╯_╰)╭  
    -- 这里的优化可以有以下两种   
    -- ****这个在5.6的版本已经优化了,已经优化为第二种写法了 (╯‵□′)╯︵┻━┻ *****
    -- ****  亲测5.7.2的版本中是一样的效果了 就都没差了各种写法*****
    
    -- 第一种这个方法据说效率更快,我还没试过
    select * from table_a where table_a.col in (select group_concat(col) from table_b where ... )
    -- 以上这种方法无法使用,in里面没办法使用group_concat,绕过的方法为分两次查询,或在以下方法
    select * from table_a where find_in_set(table_a.col,(select group_concat(col) from table_b where ... ))
    
    -- 第二种方法是常用的方法了
    select * from table_a inner join table_b on table_b.col = table_a.col where ... 
    
    -- group_concat的长度限制
     show variables like 'group_concat_max_len';
     SET GLOBAL group_concat_max_len=4294967295 ;
     SET SESSION group_concat_max_len=4294967295 ;
    
  • 获取特定条件最小id

    -- 第一个是正常的获取最小id值,第二个为调整后的,执行计划是一样的,真实使用的时候,确实会快一些的。如果有超大数据量测试的话,估计还是比较明显的吧。
    EXPLAIN select MIN(id) FROM merchant_trade where merchant_name = '白**' 
    EXPLAIN select id FROM merchant_trade use index(primary) where  merchant_name = '王**'  limit 1
    
  • 查询优化器提示,有很多比如:使用或忽略特定索引,是否缓存排序,等等。如没有特殊需要不要使用,因为我不会╮(╯_╰)╭ 【主要这里的版本是5.5 ,现在用的都是5.6~5.7+的了,谁知道有的是不是已经优化改了 ε=(´ο`*)))唉 】【P232~P235】

  • 关联表的索引建立在第二个表的关联字段上。栗子:A join B using© ,则索引在B.C。

  • 在做地理位置范围查询的时候(经纬度),先过滤正方形最值条件,再计算具体的距离限制。

    -- 北京 经纬度(A,B)
    -- 纬度变化一度,球面南北方向距离变化:πR/180 ........111.7km
    -- 经度变化一度,球面东西方向距离变化:πR/180*cosB ....111.7*cosB
    -- B = 40、cosB = 0.766,经度变化1度,则东西方向距离变化 85.567km
    -- 以上可以作为最值过滤条件,减少数据扫描,并在数据A,B上加上索引加快范围查询
    -- 想要更快的的话,可以增加AB的近似值作为字段,而后范围条件改为IN()条件,完整用上了索引。
    

不一样的说法

  • MySQL中的in查询会进行排序之后使用二分法进行判断,其复杂度为O(log n),而不是等价于or的写法O(n),所以在in()列表中有大量值的时候,处理速度并不会下降很多,反而可能很快。

  • 连接关联的方式:join … on ,join…using , table a,table b where a=b 这三种写法最终所有的查询都被转成了 Theta 风格。除了写法不同外,没什么区别。【文章参考

  • MySQL没有全连接的语法,的原因可能就是,关联表使用的规则为“嵌套循环关联”,所有连接转换为左连接,进行循环递归嵌套查询,但外层循环值为空的情况下无法进行后续处理,所以无法完成全连接。【就是说关联必须有一个主表,保证主表是有值的】【P215】

  • 关于COUNT:按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以尽量使用 count(*)

  • UNION默认情况下会去重的,等效于distinct,而通常使用UNION ALL 。毕竟去重也是有性能开销的。

  • 在手动查询一些数据的情况下,可以使用自定义变量进行复杂数据的查询,还是很好用的。【P247】【虽然看着挺好用的,但是在系统中是用是做不到的,限制太多了。P244】

    -- 这里有个用法挺不错的 
    -- union 查询,查询一个值的时候,当第一个没查到数据的时候 再查询第二个条件
    SELECT GREATEST(@found := -1,id) FROM `app_user` where id = 666
    union all
    select id from app_user_his where @found is null and id = 666   
    

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小_杭

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值