网站建设外包项目,下载公众号,网站链接怎么做,企业网站建设找智恒网络索引优化与查询优化 文章目录 索引优化与查询优化1. 概述2. 索引失效案例3. 关联查询优化3.1 Join语句原理3.2 Simple Nested-Loop Join#xff08;简单嵌套循环连接#xff09;3.3 Index Nested-Loop Join#xff08;索引嵌套循环连接#xff09;3.4 Block Nested-Loop Jo…索引优化与查询优化 文章目录 索引优化与查询优化1. 概述2. 索引失效案例3. 关联查询优化3.1 Join语句原理3.2 Simple Nested-Loop Join简单嵌套循环连接3.3 Index Nested-Loop Join索引嵌套循环连接3.4 Block Nested-Loop Join块嵌套循环连接3.5 Hash Join3.6 小结 4. 子查询优化5. 排序优化6. GROUP BY 优化7. 分页查询优化8. 字符串的前缀索引9. 索引下推10. 普通索引和唯一索引10.1 更新过程10.2 使用场景 11. 其它查询优化策略11.1 EXISTS 和 IN 的区分11.2 count(具体字段)的使用11.3 关于select *11.4 多使用commit 12. 推荐的主键策略 1. 概述
虽然 SQL 查询优化的技术有很多但是大方向上完全可以分成 物理查询优化 和 逻辑查询优化 两大块
物理查询优化是通过 索引 和 表连接方式 等技术来进行优化这里重点需要掌握索引的使用逻辑查询优化就是通过 SQL 等价变换 提升查询效率直白一点就是说换一种查询写法执行效率可能更高
2. 索引失效案例
MySQL 提升性能最有效的方式就是 设计合理的索引但是否用索引都由优化器决定。
优化器是基于 开销(CostBaseOptimizer)它不是基于 规则(Rule-BasedOptimizer)也不是基于 语义。怎么开销小就怎么来。另外SQL语句是否使用索引跟数据库版本、数据量、数据选择度都有关系。
索引失效情况
最佳左前缀法则对于MySQL多列索引过滤条件要使用索引必须按照索引建立时的顺序依次满足一旦跳过某个字段索引后面的字段都无法被使用。计算、函数、类型转换自动或手动都会导致索引失效范围条件右边的列索引失效多列索引中过滤条件中索引左边有范围查询的话则对应列右边的列索引将失效 应用开发中金额和日期查询往往是范围查询建立联合索引时务必把涉及范围查询的字段放在索引后面 不等于(! 或者 )索引失效is null可以使用索引is not null无法使用索引 最好在设计表的时候就将字段设置为not null约束并给定默认值int为0字符串为’’ like 以通配符 %开头将无法使用索引or 前后存在非索引的列索引失效数据库和表的字符集统一不同字符集进行比较前需要进行 转换会造成索引失效
一般性建议
对于单列索引尽量选择针对当前query过滤性更好的索引在选择组合索引的时候当前query中过滤性最好的字段在索引字段顺序中位置越靠前越好在选择组合索引的时候尽量选择能够包含当前query中的where子句中更多字段的索引在选择组合索引的时候如果某个字段可能出现范围查询时尽量把这个字段放在索引次序的最后面
3. 关联查询优化
3.1 Join语句原理
驱动表就是主表被驱动表就是从表、非驱动表。Join连接时无索引时需遍历主表逐个与被驱动表比对嵌套循环遍历被驱动表。MySQL5.5后的版本引入了BNLJ算法来优化嵌套执行。MySQL8.0.18版本前在被驱动表有索引的情况下运用了INLJ算法无索引时采用BNLJ算法。从MySQL的8.0.20版本开始将废弃BNLJ因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
3.2 Simple Nested-Loop Join简单嵌套循环连接
算法从驱动表A中取出一条数据1遍历被驱动表B将匹配到的数据放入result…以此类推驱动表A中的每一条记录与驱动表B的记录进行判断算法复杂度为OA * B效率很低。MySQL当然不会这么粗暴的进行表的连接。
3.3 Index Nested-Loop Join索引嵌套循环连接
Index Nested-Loop Join优化的主要思路是 减少被驱动表数据的匹配次数所以要求被驱动表上必须 有索引才行。通过驱动表匹配条件直接与被驱动表索引进行匹配避免和被驱动表的每条记录比较使得算法复杂度为O(A * LogB)其中因为索引的结构是B树LogB的大小一般不会超过4。如果在B上的索引不是主键还需要进行回表操作所以如果被驱动表的索引是主键索引的话效率会更高
3.4 Block Nested-Loop Join块嵌套循环连接
该算法不是按SNLJ算法一样逐条获取驱动表的数据而是一块一块的获取引入了 join buffer缓冲区将驱动表join相关的部分数据列大小受join buffer的限制缓存到join buffer中然后全表扫描被驱动表被驱动表的每一条记录一次性和join buffer缓冲区中的所有驱动表记录进行匹配内存中操作将简单嵌套循环中的多次比较合并为一次降低了被驱动表的访问频率。 注意这里缓存的不只是关联表的列select 后面的列也会缓存起来。 在一个有N个join关联的sql中会分配N-1个join buffer所以查询的时候尽量减少不必要的字段可以让join buffer中可以存放更多的列 参数设置:
block_nested_loop 通过 show variables like %optimizer_switch%查看block_nested_loop状态。默认是开启的 join_buffer_size默认值256k
3.5 Hash Join
Nested Loop对于被连接的数据子集较小的情况嵌套循环还是个较好的选择Hash Join是做 大数据集连接 时的常用方式优化器使用两个表中较小(相对较小)的表利用 Join Key 在内存中建立 散列表然后扫描较大的表并探测散列表找出与Hash表匹配的行内存设置还是join_buffer_size 这种方式适用于较小的表完全可以放于内存中的情况这样总成本就是访问两个表的成本之和在表很大的情况下并不能完全放入内存这时优化器会将它分割成 若干不同的分区不能放入内存的部分就把该分区写入磁盘的临时段此时要求有较大的临时段从而尽量提高l/O 的性能它能够很好的工作于没有索引的大表和并行查询的环境中并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1 B.COL2)这是由Hash的特点决定的
3.6 小结 整体效率INLJ BNLJ SNLJ 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是 表行数 * 每行大小而不是单纯指行数) -- STRAIGHT_JOIN就是在内连接中使用而强制使用左表来当驱动表所以这个特性可以用于一些调优强制改变mysql的优化器选择的执行计划select t1.b,t2.* from t1 straight_join t2 on (t1.bt2.b) where t2.id100; #推荐,t2取了所有字段相对来说join buffer里能存的数据更多select t1.b,t2.* from t2 straight_join t1 on (t1.bt2.b) where t2.id100; #不推荐为被驱动表匹配的条件增加索引(减少被驱动表的循环匹配次数) 增大join_buffer_size的大小 (一次缓存的数据越多那么被驱动表的扫表次数就越少) 减少驱动表不必要的字段查询 (字段越少join buffer 所缓存的数据行数就越多)
4. 子查询优化
执行子查询时需要建立撤销临时表且临时表还是磁盘都不会有索引在MySQL中建议使用Join查询来替代子查询尽量不用not in 或in 函数
select a.* from
(select a.* ,b.field from tabname as a left outer join tabname as b on a.field b.field
) x where field is null5. 排序优化
在MySQL 中支持两种排序方式分别是 FileSort 和 Index 排序。
Index 排序中索引可以保证数据的有序性不需要再进行排序效率更高FileSort 排序则一般在 内存中 进行排序占用 CPU 较多。如果待排结果较大会产生临时文件 /0 到磁盘进行排序的情况效率较低
优化建议
FileSort 排序不一定比索引慢。但总的来说我们还是要避免以提高查询效率尽量使用Index 完成ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列;如果不同就使用联合索引如果靠过滤条件能过滤掉大部分数据则使用 FileSort也能接受order by 在数据过滤后剩余数据量还比较大的时候才会考虑使用索引否则会优先使用成本低的FileSort无法使用Index 时考虑对 FileSort 方式进行调优
filesort的两种排序方式
双路排序从磁盘取排序列排序完再从磁盘取出对应数据输出单路排序从磁盘读取查询需要的所有列排序后输出需要更大内存空间如果待排数据量很大每次只能取sort_fuffer的容量进行排序再合并排完再取会导致大量的I/O操作反而得不偿失
FileSort 优化策略
提高 sort_buffer_size提升排序时可用的内存容量大小innodb存储引擎默认值是1MB尝试提高 max_length_for_sort_data如果需要返回的列的总长度大于max_length_for_sort_data使用双路算法否则使用单路算法1024~8192字节之间调整。默认1024字节即提高后会增加使用单路算法的阈值
注意
order by 时 select * 是大忌最好只取需要的字段原因
当查询的字段大小总和小于 max_length_for_sort_data时而且排序字段不是TEXT|BLOB类型时会用改进后的排序——单路排序否则使用多路排序两种算法的数据都有可能超过 sort_buffer_size 的容量超出之后会创建tmp文件进行合并排序导致多次I/O但是用单路排序算法的风险会更大一些所以要提高 sort_buffer_size
6. GROUP BY 优化
where效率高于having能写在where限定的条件就不要写在having中减少使用order by和业务沟通能不排序就不排序或将排序放到程序端去做。Order by、 group by、distinct这些语句较为耗费CPU数据库的CPU资源是极其宝贵的包含了order by、group by、distinct这些查询的语句where条件过滤出来的结果集请保持在1000行以内否则SQL会很慢
7. 分页查询优化
优化思路一在索引上完成排序分页操作最后根据主键关联回原表查询所需要的其他列内容
SELECT * FROM student t,(SELECT id FRON student ORDER BY id LIMIT 2000000,10) aWHERE t.id a.id;优化思路二该方案适用于主键自增的表可以把Limit 查询转换成某个位置的查询
SELECT * FROM student WHERE d 2000000 LIMIT 10:优化思路三游标方式适合单调递增的数据每次查询时传入上次查询的末尾的值需与前端一起配合和方案二类似但无法跳页查询
8. 字符串的前缀索引
如果不指定索引长度则索引会包含整个字符串 index1指定长度则为前缀索引 index2
mysql alter table teacher add index index1(email);
#或
mysql alter table teacher add index index2(email(6));如果使用的是index1即email整个字符串的索引结构执行顺序是这样的
从index1索引树找到满足索引值是’ zhangssxyzxxx.com ’的这条记录取得ID2的值到主键上查到主键值是ID2的行判断email的值是正确的将这行记录加入结果集取index1索引树上刚刚查到的位置的下一条记录发现已经不满足email’ zhangssxyzxxx.com ’的 条件了循环结束。
这个过程中只需要回主键索引取一次数据所以系统认为只扫描了一行。
如果使用的是index2即email(6)索引结构执行顺序是这样的
从index2索引树找到满足索引值是’zhangs’的记录找到的第一个是ID1到主键上查到主键值是ID1的行判断出email的值不是’ zhangssxyzxxx.com ’这行记录丢弃取index2上刚刚查到的位置的下一条记录发现仍然是’zhangs’取出ID2再到ID索引上取整行然 后判断这次值对了将这行记录加入结果集重复上一步直到在idxe2上取到的值不是’zhangs’时循环结束。
也就是说使用前缀索引定义好长度就可以做到既节省空间又不用额外增加太多的查询成本。前面已经讲过区分度区分度越高越好。因为区分度越高意味着重复的键值越少。 注意使用前缀索引就用不上覆盖索引对查询性能的优化了这也是在选择是否使用前缀索引时需要考虑的一个因素 9. 索引下推
在二级索引或联合索引中判断条件中如果还有对应索引中字段则先判断再回表
select * from student where name a and sno like %20; -- index(name, sno)上述查询会通过联合索引找到 name a’的数据的同时判断 sno like ‘%20’之后再进行回表如无icp(索引下推)则找到 name a’的数据就会回表查出数据后再判断sno like ‘%20’
10. 普通索引和唯一索引
普通索引和唯一索引应该怎么选择其实这两类索引在查询能力上是没差别的主要考虑的是对 更新性能 的影响。所以建议 尽量选择普通索引但要考虑业务正确性优先
10.1 更新过程
为了说明普通索引和唯一索引对更新语句性能的影响这个问题介绍一下change buffer。
当需要更新一个数据页时如果数据页在内存中就直接更新而如果这个数据页还没有在内存中的话在不影响数据一致性的前提下 InooDB会将这些更新操作缓存在change buffer中 这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候将数据页读入内存然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将change buffer中的操作应用到原数据页得到最新结果的过程称为 merge 。除了 访问这个数据页 会触发merge外系统有 后台线程会定期 merge。在 数据库正常关闭shutdown 的过程中也会执行merge操作。
如果能够将更新操作先记录在change buffer减少读磁盘 语句的执行速度会得到明显的提升。而且数据读入内存是需要占用 buffer pool 的所以这种方式还能够 避免占用内存 提高内存利用率。唯一索引的更新就不能使用change buffer 实际上也只有普通索引可以使用
10.2 使用场景
首先 业务正确性优先 。我们的前提是“业务代码已经保证不会写入重复数据”的情况下讨论性能问题。如果业务不能保证或者业务就是要求数据库来做约束那么没得选必须创建唯一索引。
然后在一些“ 归档库 ”的场景。比如线上数据只需要保留半年然后历史数据保存在归档库。这时候归档数据已经是确保没有唯一键冲突了。要提高归档效率可以考虑把表里面的唯一索引改成普通索引
innodb_change_buffer_max_sizeChange Buffer的最大大小默认为25%的缓冲池大小innodb_change_bufferingChange Buffer的开启状态默认为all表示所有插入、更新和删除操作都会使用Change Buffer
11. 其它查询优化策略
11.1 EXISTS 和 IN 的区分
遵守小表驱动大表
select * from a where cc in (select cc from b) -- a表比b表大select * from a where exist(select cc from b where b.cc a.cc) -- a表比b表小11.2 count(具体字段)的使用
使用innodb存储引擎的情况下如果使用count(具体字段)要尽量选择二级索引因为主键是聚簇索引需要把所有数据加载到内存中。
11.3 关于select *
MySQL在解析的过程中会通过 查询数据字典将 *按序转换为列名无法使用 覆盖索引
11.4 多使用commit
commit所释放的资源
回滚段上用于恢复数据的信息被程序语句获得的锁redo / undo log buffer 中的空间管理上述3种资源中的内部花费
12. 推荐的主键策略
非核心业务对应表的主键自增ID如警告、日志、监控等信息
核心业务主键设计至少应该是全局唯一且是单调递增的
最简单的主键设计有序的UUID将UUID时间高位与低位交换且去除无意义的“-”字符串
UUID 时间 UUID版本(16字节) - 时钟序列4字节 - MAC地址(12字节)MySQL8.0提供的uuid_to_bin函数实现上述功能
select uuid_to_bin(uuid(), true);在当今的互联网环境中非常不推荐自增ID作为主键的数据库设计。更推荐类似有序UUID的全局唯一的实现。 另外在真实的业务系统中主键还可以加入业务和系统属性如用户的尾号机房的信息等。这样的主键设计就更为考验架构师的水平了。
