六安网站制作费用多少,简述网页布局的几种方法,搭配网站开发的开题报告,营销培训学院文章目录 一、 前言二、explain相关三、索引优化相关联合索引索引下推排序和分组相关优化分页优化表关联优化嵌套循环连接 Nested-Loop Join(NLJ) 算法in和exsits优化 一、 前言
近几年看了很多和mysql相关的书#xff0c;文章或视频#xff0c;但仍然有一些点#xff0c;看… 文章目录 一、 前言二、explain相关三、索引优化相关联合索引索引下推排序和分组相关优化分页优化表关联优化嵌套循环连接 Nested-Loop Join(NLJ) 算法in和exsits优化 一、 前言
近几年看了很多和mysql相关的书文章或视频但仍然有一些点看过之后又忘记了这里做一些笔记来总结一下。
二、explain相关
explain解析后id列越大执行优先级越高id相同则从上往下执行id为NULL最后执行。select_type列代表的是对应行是简单还是复杂查询。 1simple简单查询。查询不包含子查询和union 2primary复杂查询中最外层的 select 3subquery包含在 select 中的子查询不在 from 子句中 4derived包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中也称为派生表derived的英文含义当type是index的时候代表扫描全索引就能拿到结果一般是扫描某个二级索引这种扫描不会从索引树根节点开始快速查找而是直接对二级索引的叶子节点遍历和扫描速度还是比较慢的这种查询一般为使用覆盖索引二级索引一般比较小所以这种通常比ALL快一些。可以通过key_len来判断使用了是吗索引char(n)和varchar(n)中n代表的是字符数而不是字节数。 varchar(n)如果存汉字则长度是 3n 2 字节加的2字节用来存储字符串长度因为varchar是变长字符串。
三、索引优化相关
联合索引
联合索引第一个字段就用范围查找不会走索引mysql内部可能觉得第一个字段就用范围结果集应该很大回表效率不高还不如就全表扫描。当然可以使用forceidex强制走索引但是效率不一定高。可以用覆盖索引优化。in和or在表数据量比较大的情况会走索引在表记录不多的情况下会选择全表扫描。
索引下推
**好处:**索引下推主要是为了减少回表次数并且只能用于二级索引innodb主键索引保存的是全行数据索引下推不会减少回表次数。**原理:**比如对于辅助的联合索引(name,age,position)正常情况按照最左前缀原则 SELECT * FROM employees WHERE name like LiLei% AND age 22 AND position manager 这种情况只会走name字段索引因为根据name字段过滤完得到的索引行里的age和position是无序的无法很好的利用索引。 在MySQL5.6之前的版本这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引然后拿这些索引对应的主键逐个回表到主键索引上找出相应的记录再比对age和position这两个字段的值是否符合。 MySQL 5.6引入了索引下推优化可以在索引遍历过程中对索引中包含的所有字段先做判断过滤掉不符合条件的记录之后再回表可以有效的减少回表次数。使用了索引下推优化后上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后同时还会在索引里过滤age和position这两个字段拿着过滤完剩下的索引对应的主键id再回表查整行数据。
排序和分组相关优化
在order by和group by中也能使用到索引但不会显示在key_len字段中会在extra中有显示比如当某个字段用在排序那么额外字段里没有using filesort。对于排序来说多个相等条件(in)也是范围查询也会出现using filesort。
分页优化
EXPLAIN select * from employees ORDER BY name limit 90000,5;
对于深度分页比如limit 10000,10mysql会查询出前面10010条数据并舍弃掉10000条数据只要后面的10条数据这样效率很低。如果是连续的可以用 id10000 limit 5实现。但弊端很大要保证数据连续还要保证如果排序了排序的时候是按照主键排序的。扫描整个索引并查找到没索引的行可能要遍历多个索引树)的成本比扫描全表的成本更高所以优化器放弃使用索引。如何优化 其实关键是让返回的数据尽可能少所以可以让排序和分页操作先查出主键然后根据主键查到对应的记录SQL改写如下 select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id ed.id;
这样避免了filesort文件排序还走了索引。
表关联优化
执行计划中首先执行的是驱动表后执行的是被驱动表驱动表一般数量级少。 使用 inner join 时排在前面的表并不一定就是驱动表。当使用left join时左表是驱动表右表是被驱动表当使用right join时右表时驱动表左表是被驱动表当使用join时mysql会选择数据量比较小的表作为驱动表大表作为被驱动表。
嵌套循环连接 Nested-Loop Join(NLJ) 算法
前提t2表100行数据t1表1万行数据。
一次一行循环地从第一张表称为驱动表中读取行在这行数据中取到关联字段根据关联字段在另一张表被驱动表里取出满足条件的行然后取出两张表的结果合集。
如果被驱动表的关联字段没索引使用NLJ算法性能会比较低mysql会选择Block Nested-Loop Join算法。 如果上面使用 Nested-Loop Join那么扫描行数为 100 * 10000 100万次这个是磁盘扫描。 很显然用BNL磁盘扫描次数少很多相比于磁盘扫描BNL的内存计算会快得多。
从表 t2 中读取一行数据如果t2表有查询过滤条件的用先用条件过滤完再从过滤结果里取出一行数据 从第 1 步的数据中取出关联字段 a到表 t1 中查找 取出表 t1 中满足条件的行跟 t2 中获取到的结果合并作为结果返回给客户端 重复上面 3 步。
被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢 用BNL磁盘扫描次数少很多相比于磁盘扫描BNL的内存计算会快得多。 因此MySQL对于被驱动表的关联字段没索引的关联查询一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法有索引的情况下 NLJ 算法比 BNL算法性能更高。
in和exsits优化
原则小表驱动大表即小的数据集驱动大的数据集应建立索引 in当B表的数据集小于A表的数据集时in优于exists
select * from A where id in (select id from B) 先执行BB相当于小表B表数据越少执行越快
