`
252190908
  • 浏览: 225932 次
文章分类
社区版块
存档分类
最新评论

高性能MySql进化论(十一):常见查询语句的优化

 
阅读更多

总结一下常见查询语句的优化方式

1COUNT

1. COUNT的作用

·COUNT(table.filed)统计的该字段非空值的记录行数

·COUNT(*)或者是COUNT(not nullable field) 统计的是全表的行数

如果要是统计全表记录数,COUNT(*)效率会比COUNT(not nullable field)要高一点

2. MYISAM的COUNT

一般执行COUNT操作时需要扫描大量的记录,但是在MyISAM引擎的数据库中,数据库把表的记录数保存起来,所以COUN(*)会非常的快(前提是不包含where条件)

3. 当需要频繁的使用COUNT时,可以考虑使用汇总表的策略

4. 优化小例子
在MYISAM中进行范围查询时,可以减少检索行数的小技巧
原始的:select count(*) from dictionary where id>5.

优化后:select (select count(*) fromdictionary)-count(*) from dictionary where id<=5

减少查询次数

优化前:需要两条语句

Select count(*)from student where area=’SH’

Select count(*)from student where area=’BJ’

优化后:合并成一条

select count(area='SH') as shcount, count(area='BJ') as bjcount from student;

2优化关联查询

1. 确保ON或USING的字句上有索引

2. 一般情况下只需要在第二个表上创建索引

3. 尽量使 Group by/Order by的表达式中只包含一个表的字段

3优化子查询

尽量用关联代替子查询

4优化Group by 以及Distinct

1. 当对关联查询执行group by操作时,使用查询表的标识列作为分组条件效率会比较高

2. 当需要查询的非group by指定的字段时,正常情况下是无法执行的,可以通过inner join 的形式来弥补

select firstname, lastname

from actor

inner join(select  actor_id, count(*) as cnt from actor group by(actor_id))

using (actor_id)

3. group by默认会对查询的结果进行排序,数据量很大的时候可能会比较耗资源,如果你不关心查询结果的顺序,可以通过order by null 避免这种不必要的浪费

5LIMIT分页

在进行分页查询的时候往往是采用select * from table1 limit 100,20 的方式来提取数据,在处理的过程中会读取120条数据,然后扔掉100条的offset记录,最后返回20条记录给客户端。如果offset的值非常大,效率上可能会有影响,可以尝试

1. 可以通过覆盖索引+inner join的方式来重写sql

select field1,field2,field3

from table1

    inner join

(select id from table1 limit 100, 20) as temp

   using(id)


2. 如果可以计算出明确的开始点和结束点,可以转换成 between and 的方式,这种方式只会扫描指定的行数,效率比较高

Select * from table1 between 100 and 120.

3. 可以通过位置标签的方式,来减少需要检索的记录数

例如 从某个位置开始。 Select * from table1 whereid>100 limit 20

下图列出了三种方式的效率对比

在进行分页处理的时候往往需要知道记录的总数,然后用这些总数生成页码。获取总数往往是使用count或是伴随一次全表查询得到的,这个过程也是检索所有的记录,然后再丢掉。为了避免这种浪费可以采取两种策略

·把页码换成“下一页”的方式,这样就只需要去取固定的条数

·一次性读取1000条,当一千条使用完后,采用“获取更多记录”的方式再获取1000条

6UNION

·使用的时候要把每个优化手段下推到每个子集中(http://blog.csdn.net/eric_sunah/article/details/17290641

·Union操作会对处理后的结果执行distinct操作,这在很多时候是没有必要的。可以采用union all来避免这个问题

7自定义变量

合理灵活的使用自定义变量往往会给程序的性能带来意想不到的效果,但往往也会带来与其他数据库系统的兼容性问题。

下面列出几个自定义变量使用的小例子

·行号

mysql> set @rownumber:=0;

mysql> select mean, @rownumber:=@rownumber+1 from dictionary limit10;

·避免重复查询刚刚更新的数据

在更新完一条记录后,往往需要再次执行select查询刚刚更新过的记录

通过变量可以避免这种问题

Mysql>set @updaterow:=null;

mysql> update dictionary set mean='update get variable' where id=100and @updaterow:=now();

·统计更新和插入的数量

mysql> set @x:=0; //define avariable

mysql> insert into dictionary (id,mean) values(3,'duplicate') onduplicate key update mean=values(mean)+(0*(@x:=@x+1)); //insert a duplicaterecord

mysql> select @x; //get x value, it’s indicator duplicate times

8静态分析工具

有时候可以借助专门的查询分析工具来发现自己的问题,比如pt-query-advisor(http://www.percona.com/doc/percona-toolkit/2.1/pt-query-advisor.html)

分享到:
评论

相关推荐

    高性能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查询效率的10个SQL语句优化技巧.doc

    提升MYSQL查询效率的10个SQL语句优化技巧

    mysql常用性能查询语句

    mysql常用性能查询语句,自己总结的,如果又问题,请多包含

    32丨MySQL调优之SQL语句:如何写出高性能SQL语句?.html

    32丨MySQL调优之SQL语句:如何写出高性能SQL语句?.html

    MySQL数据库:流程控制语句while语句.pptx

    MySQL支持3条用来创建循环的语句:WHILE、REPEAT和LOOP语句。 WHILE语句语法格式为: [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label] 说明:语句首先判断search_condition是否为真...

    mysql 性能优化

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

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

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

    mysql-语句大全

    mysql语句大全 1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql&gt; SHOW DATABASES; 2:2、创建一个数据库MYSQLDATA mysql&gt; CREATE DATABASE MYSQLDATA; 3:选择你所创建的数据库 mysql&gt; USE MYSQLDATA; (按...

    MySQL查询语句大全集锦(经典珍藏).pdf

    MYSQL查询语句大全集锦 1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql&gt; SHOW DATABASES; 2:2、创建一个数据库 MYSQLDATA mysql&gt; CREATE DATABASE MYSQLDATA; 3:选择你所创建的数据库 mysql&gt; USE MYSQL...

    Effective MySQL之SQL语句最优化

    《Effective MySQL之SQL语句最优化》是由MySQL专家Ronald Bradford撰著,书 中提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧 做了详细的解释。本书希望能够通过一步步详细介绍SQL优化...

    Mysql高级性能优化思维导图

    配置调优:优化MySQL的配置参数,包括缓存大小、连接数、并发线程等,以最大程度地利用系统资源和提高性能。 表分区和分片:对于大型数据库,考虑使用表分区和分片技术,将数据分散到多个物理存储上,提高查询和...

    高性能MySQL.pdf

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

    高性能MySQL(第3版).part2

    6.2慢查询基础:优化数据访问196 6.2.1是否向服务器请求了不需要的数据196 6.2.2MySQL是否在扫描额外的记录198 6.3重构查询的方式201 6.3.1一个复杂查询还是多个简单查询201 6.3.2切分查询202 6.3.3分解关联...

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

    MySQL进阶:优化查询性能与高可用性的实践

    MySQL进阶:优化查询性能与高可用性的实践 随着互联网技术的快速发展,数据量呈现爆炸式增长,数据库作为存储和管理数据的关键工具,其性能和稳定性对企业的运营至关重要。MySQL作为一款开源的关系型数据库管理系统...

    高性能MySQL(第3版).pdf

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

    MySQL管理之道:性能调优、高可用与监控

    MySQL管理之道:性能调优、高可用与监控内容简介:《mysql管理之道:性能调优、高可用与监控》由资深mysql专家撰写,以最新的mysql版本为基础,以构建高性能mysql服务器为核心,从故障诊断、表设计、sql优化、性能...

Global site tag (gtag.js) - Google Analytics