查询mysql中执行效率低的sql语句的方法___

当前位置: 数据库>mysql

查询mysql中执行效率低的sql语句的方法

来源: 互联网发布时间：2014-10-08

本文导语: 一些小技巧1. 如何查出效率低的语句？在MySQL下，在启动参数中设置 --log-slow-queries=[文件名]，就可以在指定的日志文件中记录执行时间超过long_query_time（缺省为10秒）的SQL语句。你也可以在启动配置文件中修改long query的时间，...

一些小技巧
1. 如何查出效率低的语句？
在MySQL下，在启动参数中设置 --log-slow-queries=[文件名]，就可以在指定的日志文件中记录执行时间超过long_query_time（缺省为10秒）的SQL语句。你也可以在启动配置文件中修改long query的时间，如：

代码如下:

# Set long query time to 8 seconds
long_query_time=8

2. 如何查询某表的索引？
可使用SHOW INDEX语句，如：

代码如下:

SHOW INDEX FROM [表名]

3. 如何查询某条语句的索引使用情况？
可用EXPLAIN语句来看一下某条SELECT语句的索引使用情况。如果是UPDATE或DELETE语句，需要先转换为SELECT语句。
4. 如何把导出INNODB引擎的内容到错误日志文件中?
我们可以使用SHOW INNODB STATUS命令来查看INNODB引擎的很多有用的信息，如当前进程、事务、外键错误、死锁问题和其它一些统计数据。如何让该信息能记录在日志文件中呢？只要使用如下语句创建innodb_monitor表，MySQL就会每15秒钟把该系统写入到错误日志文件中：

代码如下:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

如果你不再需要导出到错误日志文件，只要删除该表即可：

代码如下:

DROP TABLE innodb_monitor;

5. 如何定期删除庞大的日志文件？
只要在启动配置文件中设置日志过期时间即可：

代码如下:

expire_logs_days=10

经验教训
1. 重点关注索引
下面以表TSK_TASK表为例说明SQL语句优化过程。TSK_TASK表用于保存系统监测任务，相关字段及索引如下：

    ID：主键；
    MON_TIME：监测时间；建了索引；
    STATUS_ID：任务状态；与SYS_HIER_INFO.ID建立了外键关系。

注MySQL自动会为外键建立索引，在本次优化过程中，发现这些自动建立的外键索引会对SQL语句的效率产生不必要的干扰，需要特别注意！

首先，我们在日志文件中查到下面语句的执行比较慢，超过10秒了：

代码如下:

# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23';

哦，原来在88143条记录中要查出符合条件的295条记录，那当然慢了。赶紧用EXPLAIN语句看一下索引使用情况吧：

    +----+-------------+----------+------+----------------------------------------------------------+------------------------------------+---------+-------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------+------+----------------------------------------------------------+------------------------------------+---------+-------+--------+-------------+
    | 1 | SIMPLE | TSK_TASK | ref | FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME | FK_task_status_id_TO_SYS_HIER_INFO | 9 | const | 276168 | Using where |
    +----+-------------+----------+------+----------------------------------------------------------+------------------------------------+---------+-------+--------+-------------+

可以看出，有两个索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME，而最终执行语句时采用了STATUS_ID上的外键索引。

再看一下TSK_TASK表的索引情况吧：

在Oracle或其他关系数据库下，WHERE条件中的字段顺序对索引的选择起着很重要的作用。我们调整一下字段顺序，把STATUS_ID放在后面，再EXPLAIN一下：

代码如下:

EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;

但是没什么效果，MySQL还是选用系统建立的STATUS_ID外键索引。

仔细分析一下，看来Cardinality属性（即索引中的唯一值的个数）对索引的选择起了极其重要的作用，MySQL选择了索引值唯一值个数小的那个索引作为整条语句的索引。

针对这条语句，如果使用FK_task_status_id_TO_SYS_HIER_INFO做索引，而TSK_TASK表中存放很多天数据的话，那扫描的记录数会很多，速度较慢。可以有以下几个优化方案：

如果一天的任务数不多的话，我们删除索引FK_task_status_id_TO_SYS_HIER_INFO，那MySQL会使用索引TSK_TASK_KEY_MON_TIME，然后在该天的数据中在扫描STATUS_ID为1064的记录，那速度也不慢；
如果一天的任务数多的话，我们需删除索引FK_task_status_id_TO_SYS_HIER_INFO和TSK_TASK_KEY_MON_TIME，然后再建立STATUS_ID,MON_TIME的联合索引，这样效率肯定会很高。

因此建议，对那些记录数多的表，建议不要使用外键，以避免造成性能效率的严重降低。
2. 尽量控制每张表的记录数
当一张表的记录数很大时，管理和维护就会很麻烦，如索引维护就会占用很长时间，从而会给系统的正常运行造成很大的干扰。

对随时间推移数据量不断增长的表，我们可以根据时间来区分实时数据和历史数据，可以使用后台服务程序定期移动实时表中的数据到历史表中，从而控制实时表的记录数，提高查询和操作效率。但注意每次移动的时间要足够短，不要影响正常程序的数据写入。如果占用时间太长，可能会造成死锁问题。
3. 数据散列(partition)策略
当客户数达到一定规模后，单个数据库将无法支撑更高的并发访问，此时可以考虑把客户数据散列(partition)到多个数据库中，以分担负载，提高系统的整体性能与效率。

数据散列可以考虑采用federeated或sharded方式，网上有不少这方面的资料。
一些小技巧
1. 如何查出效率低的语句？
在MySQL下，在启动参数中设置 --log-slow-queries=[文件名]，就可以在指定的日志文件中记录执行时间超过long_query_time（缺省为10秒）的SQL语句。你也可以在启动配置文件中修改long query的时间，如：

代码如下:

# Set long query time to 8 seconds
long_query_time=8

2. 如何查询某表的索引？
可使用SHOW INDEX语句，如：

代码如下:

SHOW INDEX FROM [表名]

代码如下:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

如果你不再需要导出到错误日志文件，只要删除该表即可：

代码如下:

DROP TABLE innodb_monitor;

5. 如何定期删除庞大的日志文件？
只要在启动配置文件中设置日志过期时间即可：

代码如下:

expire_logs_days=10

经验教训
1. 重点关注索引
下面以表TSK_TASK表为例说明SQL语句优化过程。TSK_TASK表用于保存系统监测任务，相关字段及索引如下：

    ID：主键；
    MON_TIME：监测时间；建了索引；
    STATUS_ID：任务状态；与SYS_HIER_INFO.ID建立了外键关系。

注MySQL自动会为外键建立索引，在本次优化过程中，发现这些自动建立的外键索引会对SQL语句的效率产生不必要的干扰，需要特别注意！

首先，我们在日志文件中查到下面语句的执行比较慢，超过10秒了：

代码如下:

# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23';

哦，原来在88143条记录中要查出符合条件的295条记录，那当然慢了。赶紧用EXPLAIN语句看一下索引使用情况吧：

可以看出，有两个索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME，而最终执行语句时采用了STATUS_ID上的外键索引。

再看一下TSK_TASK表的索引情况吧：

在Oracle或其他关系数据库下，WHERE条件中的字段顺序对索引的选择起着很重要的作用。我们调整一下字段顺序，把STATUS_ID放在后面，再EXPLAIN一下：

代码如下:

    EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;

但是没什么效果，MySQL还是选用系统建立的STATUS_ID外键索引。

代码如下:

    # Set long query time to 8 seconds
    long_query_time=8

2. 如何查询某表的索引？
可使用SHOW INDEX语句，如：

代码如下:

SHOW INDEX FROM [表名]

代码如下:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

如果你不再需要导出到错误日志文件，只要删除该表即可：

代码如下:

DROP TABLE innodb_monitor;

5. 如何定期删除庞大的日志文件？
只要在启动配置文件中设置日志过期时间即可：

代码如下:

expire_logs_days=10

经验教训
1. 重点关注索引
下面以表TSK_TASK表为例说明SQL语句优化过程。TSK_TASK表用于保存系统监测任务，相关字段及索引如下：

    ID：主键；
    MON_TIME：监测时间；建了索引；
    STATUS_ID：任务状态；与SYS_HIER_INFO.ID建立了外键关系。

注MySQL自动会为外键建立索引，在本次优化过程中，发现这些自动建立的外键索引会对SQL语句的效率产生不必要的干扰，需要特别注意！

首先，我们在日志文件中查到下面语句的执行比较慢，超过10秒了：

代码如下:

    # Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
    select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23';

哦，原来在88143条记录中要查出符合条件的295条记录，那当然慢了。赶紧用EXPLAIN语句看一下索引使用情况吧：

可以看出，有两个索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME，而最终执行语句时采用了STATUS_ID上的外键索引。

再看一下TSK_TASK表的索引情况吧：

在Oracle或其他关系数据库下，WHERE条件中的字段顺序对索引的选择起着很重要的作用。我们调整一下字段顺序，把STATUS_ID放在后面，再EXPLAIN一下：

代码如下:

    EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;

但是没什么效果，MySQL还是选用系统建立的STATUS_ID外键索引。

代码如下:

    # Set long query time to 8 seconds
    long_query_time=8

2. 如何查询某表的索引？
可使用SHOW INDEX语句，如：

代码如下:

SHOW INDEX FROM [表名]

代码如下:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

如果你不再需要导出到错误日志文件，只要删除该表即可：

代码如下:

DROP TABLE innodb_monitor;

5. 如何定期删除庞大的日志文件？
只要在启动配置文件中设置日志过期时间即可：

代码如下:

expire_logs_days=10

经验教训
1. 重点关注索引
下面以表TSK_TASK表为例说明SQL语句优化过程。TSK_TASK表用于保存系统监测任务，相关字段及索引如下：

    ID：主键；
    MON_TIME：监测时间；建了索引；
    STATUS_ID：任务状态；与SYS_HIER_INFO.ID建立了外键关系。

注MySQL自动会为外键建立索引，在本次优化过程中，发现这些自动建立的外键索引会对SQL语句的效率产生不必要的干扰，需要特别注意！

首先，我们在日志文件中查到下面语句的执行比较慢，超过10秒了：

# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23';

哦，原来在88143条记录中要查出符合条件的295条记录，那当然慢了。赶紧用EXPLAIN语句看一下索引使用情况吧：

可以看出，有两个索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME，而最终执行语句时采用了STATUS_ID上的外键索引。

再看一下TSK_TASK表的索引情况吧：

在Oracle或其他关系数据库下，WHERE条件中的字段顺序对索引的选择起着很重要的作用。我们调整一下字段顺序，把STATUS_ID放在后面，再EXPLAIN一下：

代码如下:

    EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;

但是没什么效果，MySQL还是选用系统建立的STATUS_ID外键索引。

数据散列可以考虑采用federeated或sharded方式，网上有不少这方面的资料。
配置my.cnf/my.ini，增加 --log-slow-queries 配置，记录所有的slow query，然后挨个优化
本文来源于 WEB开发网

代码如下:

select @a=DRClass1, @b=DRClass2, @c=DRClass3, @d=DRClass4, @e=DRClass5 from Teacher Where TeacherID = @TeacherID

create table classname(classname char(50))
insert into classname (classname) values (@a)
if (@b is not null)
begin
insert into classname (classname) values (@b)

if (@c is not null)
begin
insert into classname (classname) values (@c)

if (@d is not null)
begin
insert into classname (classname) values (@d)
if (@e is not null)
begin
insert into classname (classname) values (@e)
end
end
end
end

select * from classname

以上这些SQL语句能不能转成一个存储过程？我自己试了下

代码如下:

ALTER PROCEDURE Pr_GetClass

@TeacherID int,
@a char(50),
@b char(50),
@c char(50),
@d char(50),
@e char(50)
as

select @a=DRClass1, @b=DRClass2, @c=DRClass3, @d=DRClass4, @e=DRClass5 from Teacher Where TeacherID = @TeacherID
DROP TABLE classname
create table classname(classname char(50))

insert into classname (classname) values (@a)
if (@b is not null)
begin
insert into classname (classname) values (@b)

if (@c is not null)
begin
insert into classname (classname) values (@c)

if (@d is not null)
begin
insert into classname (classname) values (@d)
if (@e is not null)
begin
insert into classname (classname) values (@e)
end
end
end
end

select * from classname

但是这样的话，这个存储过程就有6个变量，实际上应该只提供一个变量就可以了

主要的问题就是自己没搞清楚 @a,@b,@C,@d 等是临时变量，是放在as后面重新做一些申明的，而不是放在开头整个存储过程的变量定义。

(标准化越来越近了):namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

实战SQL语句收集(不断更新中--)

前言：这里将我编程实践中遇到的有价值的sql语句一路记下来，一方面方便自己查用，一方面也夯实下即将遗忘的回忆。整个过程中我会不断更新，直到不能再加为止，同时，这里只记录最实用的咚咚，不效仿学院派的那一套。

一、常用SQL语句荟萃

1，查询：

1.1，简单查询：select * from table where

1.2，连接查询：

什么是连接查询？顾名释义，就是查询时涉及多个表的查询。是以说到连接，废话一下，要知道连接还是关系数据库的主要特点呢。

连接查询分为三种：外连接(OUTER JOIN)，内连接(INNER JOIN)，交叉连接(CROSS JOIN)。

(标准化越来越近了):namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />1.2.1，内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

1.2.2，外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

1.2.3，交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。

1.2.4，无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：

SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)

1.2.5，使用WHERE子句设置查询条件

WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据：

代码如下:

SELECT *

FROM usertable

WHERE age>20

WHERE子句可包括各种条件运算符：

比较运算符(大小比较)：>、>=、=、 @z
print 'y > z'
else print 'z > y'

--CASE
use pangu
update employee
set e_wage =
case
when job_level = '1' then e_wage*1.08
when job_level = '2' then e_wage*1.07
when job_level = '3' then e_wage*1.06
else e_wage*1.05
end

--WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印变量x 的值
while @y < 3
   begin
    select @c = 100*@x + @y
    print @c --打印变量c 的值
    select @y = @y + 1
   end
select @x = @x + 1
select @y = 1
end

--WAITFOR
--例等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay '01:02:03'
select * from employee
--例等到晚上11 点零8 分后才执行SELECT 语句
waitfor time '23:08:00'
select * from employee

***SELECT***

   select *(列名) from table_name(表名) where column_name operator value
   ex:(宿主)
select * from stock_information where stockid   = str(nid)
     stockname = 'str_name'
     stockname like '% find this %'
     stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
     stockname like '[^F-M]%'   --------- (^排除指定范围)
     --------- 只能在使用like关键字的where子句中使用通配符)
     or stockpath = 'stock_path'
     or stocknumber < 1000
     and stockindex = 24
     not stocksex = 'man'
     stocknumber between 20 and 100
     stocknumber in(10,20,30)
     order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
     order by 1,2 --------- by列号
     stockname = (select stockname from stock_information where stockid = 4)
     --------- 子查询
     --------- 除非能确保内层select只返回一个行的值，
     --------- 否则应在外层where子句中用一个in限定符
select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
                                      --------- group by 将表按行分组,指定列中有相同的值
          having count(*) = 2 --------- having选定指定的组

select *
from table1, table2
where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
     table1.id =* table2.id -------- 右外部连接

select stockname from table1
union [all] ----- union合并查询结果集，all-保留重复行
select stockname from table2

***insert***

代码如下:

insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
              value (select Stockname , Stocknumber from Stock_table2)---value为select语句

***update***

代码如下:

update table_name set Stockname = "xxx" [where Stockid = 3]
         Stockname = default
         Stockname = null
         Stocknumber = Stockname + 4

***delete***

代码如下:

delete from table_name where Stockid = 3
truncate table_name ----------- 删除表中所有行，仍保持表的完整性
drop table table_name --------------- 完全删除表

***alter table*** --- 修改数据库表结构

代码如下:

alter table database.owner.table_name add column_name char(2) null .....
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ......... ----- 实现删除列的方法（创建新表）
alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束
 

***function(/*常用函数*/)***

----统计函数----
AVG    --求平均值
COUNT   --统计数目
MAX    --求最大值
MIN    --求最小值
SUM    --求和

代码如下:

--AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id

--MAX
--求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
from employee)

--STDEV()
--STDEV()函数返回表达式中所有数据的标准差

--STDEVP()
--STDEVP()函数返回总体标准差

--VAR()
--VAR()函数返回表达式中所有值的统计变异数

--VARP()
--VARP()函数返回总体变异数

----算术函数----

/***三角函数***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
        --返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
                       --把弧度转换为角度返回与表达式相同的数据类型可为
        --INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
        --INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) --返回表达式的指数值
LOG(float_expression) --返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10 为底的对数值
SQRT(float_expression) --返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为
        --INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression)    --返回