条幅在线设计网站,wordpress是否可以排版,网站建设材料,网站开发目录过多的缺点目录 一、分页查询优化1.根据自增且连续的主键排序的分页查询2.根据非主键字段排序的分页查询 二、Join关联查询优化1.嵌套循环连接 Nested-Loop Join(NLJ) 算法2.基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法 三、count(*)查询优化1.查询mysql自己维护的总行数2.sho… 目录 一、分页查询优化1.根据自增且连续的主键排序的分页查询2.根据非主键字段排序的分页查询 二、Join关联查询优化1.嵌套循环连接 Nested-Loop Join(NLJ) 算法2.基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法 三、count(*)查询优化1.查询mysql自己维护的总行数2.show table status3.将总数维护到Redis里4.增加数据库计数表 一、分页查询优化
在日常开发中分页是必不可少的都知道简单的分页有的是使用分页插件实现有的是自己写分页sql等等。
1.根据自增且连续的主键排序的分页查询
常见分页sql
SELECT * FROM employees LIMIT 10000,10;
SELECT * FROM employees LIMIT 10 OFFSET 10000;执行计划
上边这里sql语句是等价的我们应该有或多或少有了解过深分页或者听说过深分页其实就是这种形式分页导致的越往后性能越低越容易产生深分页。 mysql这种的分页原理是从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录实际这条 SQL 是先读取 10010 条记录然后抛弃前 10000 条记录然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据执行效率是非常低的。
解决方式该上述SQL 表示查询从第 10001 开始的五行数据没添加单独 order by表示通过主键排序。我们再看表 employees 因 为主键是自增并且连续的所以可以改写成按照主键去查询从第 10001 开始的10行数据
SELECT * FROM employees WHERE id10000 LIMIT 10;执行计划
可以看到结果是一样的这种情况也是最简单的情况没有各种条件的限制比如Oder by这些。完全按照id排序分页。前提是主键自增查询结果也是按照主键排序。 具体进行分页的话从一页开始id大于0开始取10条第二页id大于第一页最后一个id值以此类推。
2.根据非主键字段排序的分页查询
根据某一列或某几列字段排序查询进行分页的情况。 例如按照name字段进行排序查询实现分页。 表的索引信息如下
SELECT * FROM employees ORDER BY NAME LIMIT 10000,10;执行计划 虽然满足最左前缀原则但是并没有走索引原因可能是mysql任务走索引之后还需要回表查询其余字段不如全表扫描成本低。 解决思路首先关于这种情况的优化思路在上一章中首先是考虑能不能走覆盖索引因为现在查询是select *。其次是这个using filesort尝试让他走走索引。这个问题关键是让排序时返回的字段尽可能少保证在buffer中能排更多行数据所以可以让排序和分页操作先查出主键。
SELECT * FROM employees e INNER JOIN
(SELECT id FROM employees ORDER BY NAME LIMIT 10000,10) ed
ON e.id ed.id 执行计划
优化后的结果排序走了二级索引并且时间上要比前者要快。大家可以自行比较一下。 二、Join关联查询优化
创建t1和t2表并且给a字段添加索引。
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_a (a)
) ENGINEInnoDB DEFAULT CHARSETutf8;CREATE TABLE t2 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_a (a)
) ENGINEInnoDB DEFAULT CHARSETutf8;插入数据
DROP PROCEDURE IF EXISTS insert_t1;
DELIMITER ;;
CREATE PROCEDURE insert_t1()
BEGIN
DECLARE i INT;
SET i1;
WHILE(i10000)DO
INSERT INTO t1(a,b) VALUES(i,i);
SET ii1;
END WHILE;
END;;
DELIMITER;
call insert_t1();drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i1;
while(i100)do
insert into t2(a,b) values(i,i);
set ii1;
end while;
end;;
delimiter ;
call insert_t2();1.嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表称为驱动表中读取行在这行数据中取到关联字段根据关联字段在另一张表被驱动表里取出满足条件的行然后取出两张表的结果合集。
sql举例INNER JOIN
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.a t2.a;执行计划 驱动表是 t2被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)优化器一般会优先选择小表做驱动表。所以使用 inner join 时排在前面的表并不一定就是驱动表。 当使用left join时左表是驱动表右表是被驱动表当使用right join时右表时驱动表左表是被驱动表当使用join时mysql会选择数据量比较小的表作为驱动表大表作为被驱动表。 使用了 NLJ算法。一般 join 语句中如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
sql执行大致流程为
从表 t2 中读取一行数据如果t2表有查询过滤条件的会从过滤结果里取出一行数据从第 1 步的数据中取出关联字段 a到表 t1 中查找取出表 t1 中满足条件的行跟 t2 中获取到的结果合并作为结果返回给客户端重复上面 3 步。 整个过程会读取 t2 表的所有数据(共扫描100行)然后遍历这每行数据中字段 a 的值根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引1次扫描可以认为最终只扫描 t1 表一行完整数据也就是总共 t1 表也扫描了100行)。因此整个过程共扫描了 200 行。 如果被驱动表的关联字段没索引使用NLJ算法性能会比较低mysql会选择Block Nested-Loop Join算法。
sql举例LEFT JOIN
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a t2.a;EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t1.a t2.a;2.基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer 中然后扫描被驱动表把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.b t2.b;sql的大致执行流程
把 t2 的所有数据放入到 join_buffer 中把表 t1 中每一行取出来跟 join_buffer 中的数据做对比返回满足 join 条件的数据
整个过程对表 t1 和 t2 都做了一次全表扫描因此扫描的总行数为10000(表 t1 的数据总量) 100(表 t2 的数据总量) 10100。并且 join_buffer 里的数据是无序的因此对表 t1 中的每一行都要做 100 次判断所以内存中的判断次数是100 * 10000 100 万次。 这个例子里表 t2 才 100 行要是表 t2 是一个大表join_buffer 放不下怎么办呢 join_buffer 的大小是由参数 join_buffer_size 设定的默认值是 256k。如果放不下表 t2 的所有数据话策略很简单就是分段放。 比如 t2 表有1000行记录 join_buffer 一次只能放800行数据那么执行过程就是先往 join_buffer 里放800行记录然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果然后清空 join_buffer 再放入 t2 表剩余200行记录再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。
被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢
如果上面第二条sql使用 Nested-Loop Join那么扫描行数为 100 * 10000 100万次这个是磁盘扫描。很显然用BNL磁盘扫描次数少很多相比于磁盘扫描BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法有索引的情况下 NLJ 算法比 BNL算法性能更高。
总结
关联字段加索引让mysql做join操作时尽量选择NLJ算法。小表驱动大表写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式省去mysql优化器自己判断的时间。
straight_join解释straight_join功能同join类似但能让左边的表来驱动右边的表能改表优化器对于联表查询的执行顺序。 straight_join只适用于inner join并不适用于left joinright join。因为left joinright join已经代表指定了表的执行顺序
尽可能让优化器去判断因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
对于小表定义的明确 在决定哪个表做驱动表的时候应该是两个表按照各自的条件过滤过滤完成之后计算参与 join 的各个字段的总数据量数据量小的那个表就是“小表”应该作为驱动表。
3.in和exsits优化 原则小表驱动大表即小的数据集驱动大的数据集
当B表的数据集小于A表的数据集时in优于exists
select * from A where id in (select id from B)当A表的数据集小于B表的数据集时exists优于in
将主查询A的数据放到子查询B中做条件验证根据验证结果true或false来决定主查询的数据是否保留
select * from A where exists (select 1 from B where B.id A.id)1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别 2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比 3、EXISTS子查询往往也可以用JOIN来代替何种最优需要具体问题具体分析 三、count(*)查询优化
EXPLAIN SELECT COUNT(1) FROM employees;
EXPLAIN SELECT COUNT(id) FROM employees;
EXPLAIN SELECT COUNT(NAME) FROM employees;
EXPLAIN SELECT COUNT(*) FROM employees;四个sql的执行计划一样说明这四个sql执行效率应该差不多 字段有索引count( * )≈count(1)count(字段)count(主键 id) (效率从高到低)//字段有索引count(字段)统计走二级索引二级索引存储数据比主键索引少所以count(字段)count(主键 id) 字段无索引count( * )≈count(1)count(主键 id)count(字段) //字段没有索引count(字段)统计走不了索引count(主键 id)还可以走主键索引所以count(主键 id)count(字段)
1.查询mysql自己维护的总行数
对于myisam存储引擎的表做不带where条件的count查询性能是很高的因为myisam存储引擎的表的总行数会被mysql存储在磁盘上查询不需要计算。
EXPLAIN SELECT COUNT(*) FROM myisam_table而对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制在后面的博客有讲到)查询count需要实时计算。
2.show table status
如果只需要知道表总行数的估计值可以用如下sql查询性能很高。
SHOW TABLE STATUS LIKE employees3.将总数维护到Redis里
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)但是这种方式可能不准很难保证表操作和redis操作的事务一致性。
4.增加数据库计数表
插入或删除表数据行的时候同时维护计数表让他们在同一个事务里操作。 文章转载自： http://www.morning.lmxzw.cn.gov.cn.lmxzw.cn http://www.morning.jyznn.cn.gov.cn.jyznn.cn http://www.morning.xqkjp.cn.gov.cn.xqkjp.cn http://www.morning.zpdjh.cn.gov.cn.zpdjh.cn http://www.morning.nwrzf.cn.gov.cn.nwrzf.cn http://www.morning.mtbsd.cn.gov.cn.mtbsd.cn http://www.morning.kmwsz.cn.gov.cn.kmwsz.cn http://www.morning.srrrz.cn.gov.cn.srrrz.cn http://www.morning.kwwkm.cn.gov.cn.kwwkm.cn http://www.morning.wjtwn.cn.gov.cn.wjtwn.cn http://www.morning.hjbrd.cn.gov.cn.hjbrd.cn http://www.morning.jpgfx.cn.gov.cn.jpgfx.cn http://www.morning.qmzwl.cn.gov.cn.qmzwl.cn http://www.morning.mplb.cn.gov.cn.mplb.cn http://www.morning.xptkl.cn.gov.cn.xptkl.cn http://www.morning.jfnlj.cn.gov.cn.jfnlj.cn http://www.morning.qtkdn.cn.gov.cn.qtkdn.cn http://www.morning.bpmdh.cn.gov.cn.bpmdh.cn http://www.morning.smsjx.cn.gov.cn.smsjx.cn http://www.morning.lfmwt.cn.gov.cn.lfmwt.cn http://www.morning.rxcqt.cn.gov.cn.rxcqt.cn http://www.morning.tntqr.cn.gov.cn.tntqr.cn http://www.morning.rqlqd.cn.gov.cn.rqlqd.cn http://www.morning.hxxwq.cn.gov.cn.hxxwq.cn http://www.morning.ypjjh.cn.gov.cn.ypjjh.cn http://www.morning.trtdg.cn.gov.cn.trtdg.cn http://www.morning.pbmkh.cn.gov.cn.pbmkh.cn http://www.morning.mxcgf.cn.gov.cn.mxcgf.cn http://www.morning.swimstaracademy.cn.gov.cn.swimstaracademy.cn http://www.morning.ryfpx.cn.gov.cn.ryfpx.cn http://www.morning.wqbrg.cn.gov.cn.wqbrg.cn http://www.morning.pzqnj.cn.gov.cn.pzqnj.cn http://www.morning.bkylg.cn.gov.cn.bkylg.cn http://www.morning.ey3h2d.cn.gov.cn.ey3h2d.cn http://www.morning.bswxt.cn.gov.cn.bswxt.cn http://www.morning.grtwn.cn.gov.cn.grtwn.cn http://www.morning.wpqwk.cn.gov.cn.wpqwk.cn http://www.morning.qmbpy.cn.gov.cn.qmbpy.cn http://www.morning.ybgpk.cn.gov.cn.ybgpk.cn http://www.morning.npmpn.cn.gov.cn.npmpn.cn http://www.morning.lkkgq.cn.gov.cn.lkkgq.cn http://www.morning.rnfn.cn.gov.cn.rnfn.cn http://www.morning.jcfdk.cn.gov.cn.jcfdk.cn http://www.morning.lwbhw.cn.gov.cn.lwbhw.cn http://www.morning.fycjx.cn.gov.cn.fycjx.cn http://www.morning.rdfq.cn.gov.cn.rdfq.cn http://www.morning.pngfx.cn.gov.cn.pngfx.cn http://www.morning.lqrpk.cn.gov.cn.lqrpk.cn http://www.morning.klwxh.cn.gov.cn.klwxh.cn http://www.morning.bcjbm.cn.gov.cn.bcjbm.cn http://www.morning.clqpj.cn.gov.cn.clqpj.cn http://www.morning.xtgzp.cn.gov.cn.xtgzp.cn http://www.morning.crrmg.cn.gov.cn.crrmg.cn http://www.morning.tgnr.cn.gov.cn.tgnr.cn http://www.morning.gskzy.cn.gov.cn.gskzy.cn http://www.morning.jzmqk.cn.gov.cn.jzmqk.cn http://www.morning.fswml.cn.gov.cn.fswml.cn http://www.morning.mdgb.cn.gov.cn.mdgb.cn http://www.morning.srsln.cn.gov.cn.srsln.cn http://www.morning.lswgs.cn.gov.cn.lswgs.cn http://www.morning.xnyfn.cn.gov.cn.xnyfn.cn http://www.morning.pqqhl.cn.gov.cn.pqqhl.cn http://www.morning.mldrd.cn.gov.cn.mldrd.cn http://www.morning.ylmxs.cn.gov.cn.ylmxs.cn http://www.morning.tkflb.cn.gov.cn.tkflb.cn http://www.morning.bqmsm.cn.gov.cn.bqmsm.cn http://www.morning.mrgby.cn.gov.cn.mrgby.cn http://www.morning.ndltr.cn.gov.cn.ndltr.cn http://www.morning.gjlst.cn.gov.cn.gjlst.cn http://www.morning.hxsdh.cn.gov.cn.hxsdh.cn http://www.morning.ndrzq.cn.gov.cn.ndrzq.cn http://www.morning.njhyk.cn.gov.cn.njhyk.cn http://www.morning.nwmwp.cn.gov.cn.nwmwp.cn http://www.morning.hkpyp.cn.gov.cn.hkpyp.cn http://www.morning.stprd.cn.gov.cn.stprd.cn http://www.morning.sthp.cn.gov.cn.sthp.cn http://www.morning.cljmx.cn.gov.cn.cljmx.cn http://www.morning.gczqt.cn.gov.cn.gczqt.cn http://www.morning.ychrn.cn.gov.cn.ychrn.cn http://www.morning.txhls.cn.gov.cn.txhls.cn
