`
从此醉
  • 浏览: 1034619 次
  • 性别: Icon_minigender_1
  • 来自: US
社区版块
存档分类
最新评论

高性能MySql进化论(十):查询优化器的局限性

 
阅读更多

在“查询优化器常用的方式”一文中列出了一些优化器常用的优化手段。查询优化器在提供这些特性的同时,也存在一定的局限性,这些局限性往往会随着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]

    高性能MySQL_第3版_超清中文带目录版[PDF] 序 I 前言 III 第1章:MySQL架构 1 第2章:寻找瓶颈:基准测试(Benchmarking)与性能分析(Profiling) 32 第3章:架构优化和索引 80 第4章:查询性能优化 152 第5章:...

    高性能MySQL_ch04_查询性能优化.pdf

    高性能MySQL_ch04_查询性能优化.pdf

    mysql 性能优化

    mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化...

    高性能MySQL(第3版).part2

    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作为一款开源的关系型数据库管理系统...

    MySQL技术大全:开发、优化与运维实战实例源代码.rar

    MySQL技术大全:开发、优化与运维实战实例源代码.rar

    高性能MySQL.pdf

    高性能Mysql,高清PDF,带目录,mysql机构与历史,mysql基准测试

    高性能MySQL-中文第三版.pdf

    《高性能MySQL(第3版)》是...全书共分为16章和6个附录,内容涵盖MySQL架构和历史,基准测试和性能剖析,数据库软硬件性能优化,复制、备份和恢复,高可用与高可扩展性,以及云端的MySQL和MySQL相关工具等方面的内容。

    高性能MySQL(第3版).pdf

    高性能MySQL 绝对的msyql高性能优化的经典书籍, java程序员必读

    高性能mysql-中文

    《高性能MySQL》是MySQL ...全书共分为16章和6个附录,内容涵盖MySQL架构和历史,基准测试和性能剖析,数据库软硬件性能优化,复制、备份和恢复,高可用与高可扩展性,以及云端的MySQL和MySQL相关工具等方面的内容。

    高性能MySQL(第3版).Baron.Scbwartz_2

    第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基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf...

    高性能MySQL(第3版)

    第6章 查询性能优化 第7章 MySQL 高级特性 第8章 优化服务器设置 第9章 操作系统和硬件优化 第10章 复制 第11章 可扩展的MySQL 第12章 高可用性 第13章 云端的MySQL 第14章 应用层优化 第15章 备份与恢复 ...

    高性能MySQL

    高性能MySQL第二版,描述了索引使用,查询优化,系统监测配制优化等,想在MYSQL技术有提升的千万不要错过这本书~

    mysql性能优化.pdf

    mysql 性能 优化 pdf MySQL 介绍和优化分享

    高性能MySQL(第3版)中文版.pdf.zip

    高性能 Mysql免费下载,sql语句优化,索引建立,schema与数据库类型优化!

    高性能mysql电子书

    高性能mysql电子书 高性能mysql电子书 高性能mysql电子书

    MySQL数据库查询优化

    第3课 查询优化技术理论与MySQL实践(一)------子查询的优化(一) 第4课 查询优化技术理论与MySQL实践(二)------子查询的优化(二) 从理论看,子查询包括的内容和范围,建立清晰的概念 从实践看,MySQL的子查询...

    [[高性能MySQL(第3版)].Baron.Scbwartz等.扫描版[电子书.pdf

    [[高性能MySQL(第3版)].Baron.Scbwartz等.扫描版[电子书.pdf,主要内容包括:第一章mysql架构与历史;第二章mysql基准与测试;第三章服务器性能剖析;第四章schema与数据类型优化;第五章创建高性能索引;第六章...

Global site tag (gtag.js) - Google Analytics