`
javandroid
  • 浏览: 25359 次
  • 性别: Icon_minigender_1
文章分类
社区版块
存档分类
最新评论

SQL优化

 
阅读更多

SQL优化的一般步骤
先查询mysql数据库运行状况。然后定位慢查询,再分析sql的执行过程,然后进行优化

1.使用show status查询数据库的运行状况

//显示数据库运行状态
SHOW STATUS
//显示数据库运行总时间
SHOW STATUS LIKE 'uptime'
//显示连接的次数
SHOW STATUS LIKE 'connections'
//显示执行CRUD的次数
SHOW STATUS LIKE 'com_select'
SHOW STATUS LIKE 'com_insert'
SHOW STATUS LIKE 'com_update'
SHOW STATUS LIKE 'com_delete'

2.开启并记录慢查询

mysql设置的默认的慢查询时间为10s。通常这个数值过大,可改为1s。

//显示慢查询次数
SHOW STATUS LIKE 'slow_queries'
//显示慢查询时间,默认为10s
SHOW VARIABLES LIKE 'long_query_time'

可以修改慢查询时间
①.在my.ini文件中修改(global级别,永久生效)
修改my.ini配置文件中的long_query_time参数即可。修改后需重启,永久生效。
以下是本人机器上安装的percona(基于mysql的开源数据库)有关慢查询的配置信息

//开启慢查询
slow-query-log=1
//慢查询的文件路径
slow_query_log_file="D:/Program Files/MySQL/Log/mysql-slow.log"
//慢查询时间。默认为10秒,可以根据具体需求修改,通常改为1s。
long_query_time=10

②.使用命令修改(session级别,临时生效)

//修改慢查询时间
SET long_query_time = 1(sssion级别,关闭窗口后会还原)

3.使用explain分析sql语句执行过程

mysql会将慢查询记录到慢查询日志中,这时我们就可以针对这些慢查询的sql进行分析和优化。需要用到explain命令。

expain的用法:
只需在select语句前加一个explain即可。
这里写图片描述
每一个数据项的含义:

数据项 含义
id SELECT识别符,SELECT的查询序列号;
select_type SELECT类型。可以是SIMPLE(简单查询)、PRIMARY(最外面的select),UNION(UNION中的第二个或后面的查询语句),等;
table 用到的表
type 联接类型
possible_keys 可用索引列
key 实际用到的索引列
key_len 键长度
ref 使用哪个列或常数与key一起从表中选择行
rows 检查的行数
Extra 该列包含MySQL解决查询的详细信息

4.使用show profile分析sql

Mysql从5.0.37开始增加了对show profiles和show profile语句的支持。
默认profile是关闭的。可以通过set开启session级别的profiling:

//查看是否支持profile
SELECT @@have_profiling
//开启profile
set profiling=1;

①执行select语句
②show profiles,查询该sql语句的Query ID.
③通过show profile for query语句能看到执行中线程的每个状态和消耗的时间。

show profile for query [上面的query id]

5.sql优化

索引

索引分类:
1.主键索引(primary key):
创建表时,将某列设为主键,即为主键索引。
也可以在创建表后再单独添加:

alter table 表名 add primary key(列名)

2.唯一索引(unique)
当表的某列被指定为unique约束时,这列就是一个唯一索引。

create table ddd(       
   id int primary key auto_increment ,          
   name varchar(32) unique      
);

3.全文索引(full text)
①.全文索引仅在MyISAM引擎中支持。(mysql5.6版本中已开始支持全文索引了)
②.全文索引只能用于英文,如果出现中文,还是应该使用Lucene等第三方开源框架来处理。

4.普通索引(index)

//创建索引
create index 索引名 on 表名(列名)
//查询索引
show index(es) from 表名

索引的注意点:
1.索引会占用存储空间。
2.适合添加索引的字段。
①频繁作为查询条件的字段应该创建索引。也就是where后面的字段
②唯一性太差的字段不适合单独创建索引

select * from emp where sex='男' 

③频繁变化的字段不应该创建索引

select * from emp where logincount=1

3.能够使用索引的典型场景【未完成】

4.存在索引但不使用索引的典型场景
①以%开头的LIKE查询不能够利用B-tree索引。

select * from emp where last_name like '%NI%'(不会用索引)

这种情况,推荐使用全文索引(fulltext)。
②数据类型中出现隐式转换时不会用到索引。特别是当列类型为字符串时,一定要在where条件中将字符串常量值用引号引起来,否则不会用到索引。

select * from actor where last_name= 1 (不会用索引)
select * from actor where last_name='1'(会用到索引)

③复合索引的情况下,假如查询条件不包含索引列最左边部分,即不满足最左原则(Leftmost),也不会用到复合索引的。

select * from payment where amount=3.98 and last_update='2006-01-15 22:12:32'(不会用索引)

④用or分割开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及的索引都不会被用到(前面的索引也不会用到)。
原因:因为or后面的条件没有索引,必然会走全表扫描,在全表扫描情况下,就没有必要多一次索引扫描增加IO访问,一次全表扫描过滤条件就已经足够了。

select * from payment where customer_id=203 or amount = 3.1(不会用到索引。其中customer_id建立了索引)

⑤如果Mysql估计使用索引比全表扫描更慢,则不使用索引。

常用的sql优化

上面总结了通过索引来优化查询。对于INSERT、GROUP BY等常用的操作,也有相应的优化方法。

1.大批量插入数据

对于MyISAM表,当用load命令导入数据时,可以有如下几种方式提高导入效率。

ALTER TABLE 表名 DISABLE KEYS;//关闭非唯一索引的更新
……
导入数据
……
ALTER TABLE 表名  ENABLE KEYS;//打开非唯一索引的更新

在导入大量数据到一个非空的MyISAM表时,通过设置这两个命令可提高导入效率。
在导入大量数据到一个空的MyISAM表时,默认就是先导入数据然后才创建索引,所以不用进行设置。

对于Innodb表,有如下几种方式提高导入效率
①.因为Innodb类型的表是按照主键的顺序来保存的,所以将导入的数据按照主键的顺序排序,可以有效的提高导入数据的效率。

SET UNIQUE_CHECKS=0(关闭唯一性校验) 
SET UNIQUE_CHECKS=1(开启唯一性校验)

③.导入数据前,关闭自动提交。导入完成后,再开启。

SET AUTOCOMMIT=0(关闭自动提交) 
SET AUTOCOMMIT=1(开启自动提交)

2.优化INSERT语句

①如果从同一客户端批量插入多条记录,使用以下的插入。

insert into test values(1,2),(1,3),(1,4)……

②如果从不同客户端批量插入多条记录,可以使用INSERT DELAYED语句得到更高的速度。
DELAYED的含义是让INSERT语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘,这比每条语句分别插入要快的多。LOW_PRIORITY刚好相反,在所有其他用户对表的读写完成后才进行插入。
③将索引文件和数据文件存放在不同磁盘上。(利用建表中的选项)
④如果批量插入,可以通过增加bulk_insert_buffer_size变量值来提高速度(仅对MyISAM表有效)
⑤当从一个文本文件装载一个表时,使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。

3.优化ORDER BY语句

Mysql中有两种排序方式
1.通过有序的索引顺序扫描直接返回有序数据。不需要额外的排序,操作效率高。
2.通过返回数据进行排序(称为Filesort排序)
Filesort排序是否使用磁盘文件或临时表,取决于Mysql服务器对排序参数的设置和需要排序数据的大小。

尽量减少额外的排序,通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引,并且ORDER BY的顺序和索引顺序相同,并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作,这样就出现了Filesort。

4.优化GROUP BY语句

5.优化嵌套查询

6.优化OR条件

对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须建立索引。如果没有索引,则应该考虑增加索引。

7.优化分页查询

<script type="text/javascript"> $(function () { $('pre.prettyprint code').each(function () { var lines = $(this).text().split('\n').length; var $numbering = $('<ul/>').addClass('pre-numbering').hide(); $(this).addClass('has-numbering').parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($('<li/>').text(i)); }; $numbering.fadeIn(1700); }); }); </script>
分享到:
评论

相关推荐

    SQL优化 SQL优化软件 SQL优化工具

    SQL优化是数据库管理中的关键环节,它涉及到提升查询性能、减少资源消耗以及改善系统整体效率。SQL优化软件和工具能够帮助数据库管理员(DBA)和开发人员找出性能瓶颈,优化查询逻辑,从而提高数据库系统的响应速度...

    基于案例学习SQL优化

    在“基于案例学习SQL优化”的课程中,我们主要探讨如何提升数据库性能,特别是针对SQL查询的优化技巧。DBA(数据库管理员)作为关键角色,需要掌握这些技能来确保系统的高效运行。以下是根据课程标题和描述提炼出的...

    收获不止SQL优化

    第2章 风驰电掣——有效缩短SQL优化过程 24 2.1 SQL调优时间都去哪儿了 25 2.1.1 不善于批处理频频忙交互 25 2.1.2 无法抓住主要矛盾瞎折腾 25 2.1.3 未能明确需求目标白费劲 26 2.1.4 没有分析操作难度乱调优...

    收获,不止SQL优化--抓住SQL的本质1

    - **全书总结**:本书不仅是一本关于SQL优化的技术书籍,更是引导读者进入SQL优化世界的指南。通过丰富的案例、实战经验和深入的技术探讨,帮助读者建立起从宏观到微观的优化思路,并最终达到“爽”的境界。 - **...

    mysql数据库sql优化

    ### MySQL数据库SQL优化 #### 一、SQL优化 在MySQL数据库管理中,SQL查询的性能直接影响到系统的响应时间和资源消耗。通过合理的SQL优化,可以显著提高数据处理速度,降低服务器负载,提升用户体验。 ##### 1.1 ...

    《基于Oracle的SQL优化》PDF版本下载.txt

    根据提供的文件信息,本文将对《基于Oracle的SQL优化》这一主题进行深入解析,包括但不限于SQL优化的重要性、Oracle数据库的特点以及具体的SQL优化方法等。 ### SQL优化的重要性 SQL(Structured Query Language)...

    基于案例学SQL优化

    本主题"基于案例学SQL优化"将深入探讨如何通过实际案例来理解和实践SQL优化的策略和技术。 首先,我们要明确SQL优化的重要性。当数据库规模增大,查询复杂度增加时,未优化的SQL语句可能导致响应时间过长,影响用户...

    收获,不止SQL优化 PDF 带书签 第三部分

    随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题,...

    Oracle_SQL优化脚本_完整实用资源

    Oracle SQL优化是数据库管理员和开发人员提升系统性能的关键技能之一。这个"Oracle_SQL优化脚本_完整实用资源"压缩包包含了一系列工具和方法,旨在帮助你优化在Oracle数据库上运行的SQL查询,从而提高数据库的响应...

    《收获,不止SQL优化》一书的代码

    《收获,不止SQL优化》是一本专注于数据库性能优化的书籍,尤其关注Oracle数据库系统的SQL调优。这本书通过实例和深入的解析,帮助读者理解和掌握如何提升SQL查询的效率,从而优化整个数据库系统的性能。在阅读这...

    Oracle 高性能SQL引擎剖析SQL优化与调优机制详解

    深入揭示OracleSQL优化与调优的原理、核心技术与思想方法 盖国强鼎力推荐! Oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是SQL性能问题。本书是作者十年磨一剑的成果之一...

    sql优化书籍大全

    本书籍集合了丰富的SQL优化知识,旨在帮助读者深入理解并掌握MySQL SQL优化技巧。 首先,我们要明白SQL优化的基本原则:减少查询次数、减小数据量、合理设计索引以及优化查询语句结构。这四个原则贯穿于整个SQL优化...

    基于Oracle的SQL优化2

    基于Oracle的SQL优化

    关于SQL优化的电子书

    尽管给定描述并未提供具体的信息,但从标题“关于SQL优化的电子书”及标签“sql优化”,结合部分内容可以看出,此电子书聚焦于SQL应用的优化技术。以下将深入解析与SQL优化相关的专业知识点: ### SQL优化的核心...

    【整理】数据库面试题索引sql优化+数据库SQL优化总结之百万级数据库优化

    本文将深入探讨数据库面试中的常见问题,特别是关于SQL优化和针对大规模数据库的优化策略。首先,我们来看看"数据库面试题索引sql优化.pdf"可能涵盖的内容。 1. **SQL基础与语法**:面试通常会涉及到SQL的基本概念...

    基于SQL Server的SQL优化.pdf

    在SQL Server数据库管理系统中,SQL优化是提升系统性能的关键环节。SQL优化涉及到多个层面,包括查询设计、索引策略、存储过程优化、执行计划分析以及资源管理等。本篇文章将深入探讨这些方面,帮助读者理解如何针对...

    大道相通,得鱼忘筌 - 从Oracle的SQL优化到MySQL的SQL优化.pdf

    在数据库管理与优化领域,SQL优化是至关重要的一环,它涉及到数据库性能的提升以及系统资源的有效利用。在不同数据库管理系统(DBMS)之间,如从Oracle迁移到MySQL,虽然两个系统有着各自的特性,但是优化的核心思想...

    SQL优化工具下载,语句优化

    本文将深入探讨SQL优化工具及其对提升数据库性能的影响。 首先,SQL优化工具是专门设计用来帮助数据库管理员和开发人员识别和改进低效SQL查询的软件。这些工具通过分析SQL语句、执行计划和系统资源使用情况,提供...

Global site tag (gtag.js) - Google Analytics