在“查询优化器常用的方式”一文中列出了一些优化器常用的优化手段。查询优化器在提供这些特性的同时,也存在一定的局限性,这些局限性往往会随着MYSQL版本的升级而得到改善,所以本文会列出一些常见的局限性,且不包含所有的。
1.1 关联子查询
描述:
因为select …from table1 t1 where t1.id in(select t2.fk from table2 t2 wheret2.id=’…’) 类型的语句往往会被优化成 select …. From table1 t1 where exists (select* from table2 t2 where t2.id=’…’ and t2.fk=t1.id), 由于在进行tabl2查询时, table1的值还无法确定, 所以会对table1进行全表扫描
解决方案:
尽量用 INNER JOIN 替代 IN(),重写成 select * from table1 t1 inner jointable2 t2 using (id) where t2.id=’…’
1.2 UNION的限制
描述:
UNION操作不会把UNION外的操作推送到每个子集
解决方案:
为每个子操作单独的添加限制条件
例如 学生表有10000条记录,会员表有10000表记录,如果想按照姓名排序取两个表的前20条记录,如果在各个子查询中添加limit的话,则最外层的limit操作将会从40条记录中取20条,否则是从20000条中取20条
(select name from student order by name limit 20) union all (select name from memberorder by member limit 20) limit 20
1.3 等值传递
在进行查询操作的时候 IN,ON,Using,等操作往往会把一个列表的值在多个表之间共享,而优化器为了优化的方便会把列表里的值为每个相关表都拷贝一份,如果这个列表非常的大,会对性能造成一定的影响.
目前为止还没有好的策略应对这个问题
1.4 并行执行
目前為止,MYSQL不支持
1.5 哈希关联
目前MYSQL唯一支持的是循环嵌套关联,不支持HASH关联
1.6 松散索引扫描
描述:所谓的松散索引就是当对表进行扫描是,可以智能的跳过一些记录,以此来减少需要扫描的记录行数.为了更清楚的说明这个问题,举个例子来说明松散索引扫描的好处,例如table1表上有索引(a,b),执行 select * from tabl1 where b between2 and 3时,支持/不支持松散扫描的表扫描方式分别如下
由于B列是按照顺序排列的,所以只需要在固定的区间内查找就可以了,其余的记录可以跳过
B不是索引的第一字段,所以只能从第一条找到最后一条
上面两个图可以很明显的说明松散索引的好处,但是Mysql对这个特性的支持不是很好,只针对某些特殊的查询才提供此优化,具体的要看各个版本的手册
1.7 Max()/MIN()
问题描述:
当执行 select max(id) from table1 where name=’sun’ 时,如果name没有建立相应的索引,MYSQL会进行全表扫描
解决方案:
将SQL等同的转化为
select id from table1 use index(PRIMARY) wherename=’sun’ limit 1.
这样的语句会尽可能少的扫描表记录
1.8 同一个表的查询以及更新
问题描述:
不能在查询某个表的同时对表进行更新
Update table1t1 set t1.cnt=(select count(*) fromtable1)
否则会抛出异常: ERROR 1093 (HY000): You can'tspecify target table 'ftsexchangerate' for update in FROM clause
解决办法: 转化成关联表的形式
update ftsexchangerate
inner join(
select currency,count(*) as cnt from ftsexchangerate group by (currency) ) as innusing(currency)
set ftsexchangerate.description=inn.cnt ;
分享到:
相关推荐
高性能MySQL_第3版_超清中文带目录版[PDF] 序 I 前言 III 第1章:MySQL架构 1 第2章:寻找瓶颈:基准测试(Benchmarking)与性能分析(Profiling) 32 第3章:架构优化和索引 80 第4章:查询性能优化 152 第5章:...
高性能MySQL_ch04_查询性能优化.pdf
mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化...
6.5MySQL查询优化器的局限性223 6.5.1关联子查询223 6.5.2UNION的限制228 6.5.3索引合并优化228 6.5.4等值传递229 6.5.5并行执行229 6.5.6哈希关联229 6.5.7松散索引扫描229 6.5.8最大值和最小值优化231 ...
MySQL进阶:优化查询性能与高可用性的实践 随着互联网技术的快速发展,数据量呈现爆炸式增长,数据库作为存储和管理数据的关键工具,其性能和稳定性对企业的运营至关重要。MySQL作为一款开源的关系型数据库管理系统...
MySQL技术大全:开发、优化与运维实战实例源代码.rar
高性能Mysql,高清PDF,带目录,mysql机构与历史,mysql基准测试
《高性能MySQL(第3版)》是...全书共分为16章和6个附录,内容涵盖MySQL架构和历史,基准测试和性能剖析,数据库软硬件性能优化,复制、备份和恢复,高可用与高可扩展性,以及云端的MySQL和MySQL相关工具等方面的内容。
高性能MySQL 绝对的msyql高性能优化的经典书籍, java程序员必读
《高性能MySQL》是MySQL ...全书共分为16章和6个附录,内容涵盖MySQL架构和历史,基准测试和性能剖析,数据库软硬件性能优化,复制、备份和恢复,高可用与高可扩展性,以及云端的MySQL和MySQL相关工具等方面的内容。
第1章 mysql 架构与历史 1 第2章 mysql 基准测试 35 第3章 服务器性能剖析 67 第4章 schema 与数据类型优化 111 第5章 创建高性能的索引 141 第6章 查询性能优化 195 第7章 mysql 高级特性 259 第8章 优化服务器设置...
高性能mysql PDF 中英文合集 高性能mysql PDF 中英文合集
基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf...
第6章 查询性能优化 第7章 MySQL 高级特性 第8章 优化服务器设置 第9章 操作系统和硬件优化 第10章 复制 第11章 可扩展的MySQL 第12章 高可用性 第13章 云端的MySQL 第14章 应用层优化 第15章 备份与恢复 ...
高性能MySQL第二版,描述了索引使用,查询优化,系统监测配制优化等,想在MYSQL技术有提升的千万不要错过这本书~
mysql 性能 优化 pdf MySQL 介绍和优化分享
高性能 Mysql免费下载,sql语句优化,索引建立,schema与数据库类型优化!
高性能mysql电子书 高性能mysql电子书 高性能mysql电子书
第3课 查询优化技术理论与MySQL实践(一)------子查询的优化(一) 第4课 查询优化技术理论与MySQL实践(二)------子查询的优化(二) 从理论看,子查询包括的内容和范围,建立清晰的概念 从实践看,MySQL的子查询...
[[高性能MySQL(第3版)].Baron.Scbwartz等.扫描版[电子书.pdf,主要内容包括:第一章mysql架构与历史;第二章mysql基准与测试;第三章服务器性能剖析;第四章schema与数据类型优化;第五章创建高性能索引;第六章...