MySql数据库3【优化2】sql语句的优化

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

1、SELECT语句优化

1）、利用LIMIT 1取得唯一行【控制结果集的行数】
有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的WHERE子句。在这种情况下，增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。

2）、不要使用BY RAND()命令

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。

3）、尽量避免SELECT * 命令
从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。始终指定你需要的列，这是一个非常良好的习惯。

4）、保证连接的索引是相同的类型

如果应用程序中包含多个连接查询，你需要确保你链接的列在两边的表上都被索引。这会影响MySQL如何优化内部联接操作。此外，加入的列，必须是同一类型。例如，你加入一个DECIMAL列，而同时加入另一个表中的int列，MySQL将无法使用其中至少一个指标。即使字符编码必须同为字符串类型。

5）、宁可集中批量操作，避免频繁读写

系统里包含了积分部分，学生和老师通过系统做了操作都可以获得积分，而且积分规
则很复杂，限制每类操作获得积分不同，每人每天每类积分都有上限。比如登录，一次登录就可以获得1分，但是不管你登录多少次，一天只能累积一个登录积分。
这个还是简单的，有的积分很变态，比如老师积分中有一类是看老师判作业的情况，规则是：老师判了作业，发现学生有错的，学生改过了，老师再判，如果这时候学生都对了，就给老师加分，如果学生还是错的，那就接着改，知道学生都改对了，老师都判完了，才能给老师加分。如果用程序来处理，很可能每个功能都会额外
的写一堆代码来处理这个鸡肋似的积分。不仅编程的同事干活找不到重点，还平白给数据库带来了很大的压力。经过和需求人员的讨论，确定积分没有必要实时累积，于是我们采取后台脚本批量处理的方式。夜深人静的时候，让机器自己玩去吧。

6）、尽量避免在WHERE子句中对字段进行函数或表达式操作

这将导致引擎放弃使用索引而进行全表扫描，如： SELECT * FROM T1 WHERE F1/2=100

应改为 SELECT * FROM T1 WHERE F1=100*2
。即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边

7）、通过查询缓冲提高查询速度

一般我们使用SQL语句进行查询时，数据库服务器每次在收到客户端发来SQL后，都会执行这条SQL语句。但当在一定间隔内（如1分钟内），接到完全一样的 SQL语句，也同样执行它。虽然这样可以保证数据的实时性，但在大多数时候，数据并不要求完全的实时，也就是说可以有一定的延时。如果是这样的话，在短间内执行完全一样的SQL就有些得不偿失。幸好MySQL为我们提供了查询缓冲的功能（只能在MySQL 4.0.1及以上版本使用查询缓冲）。我们可以通过查询缓冲在一定程度上提高查询性能。
我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲。设置也非常简单，只需要将query_cache_type设为1即可。在设置了这个属性后，MySQL在执行任何SELECT语句之前，都会在它的缓冲区中查询是否在相同的SELECT语句被执行过，如果有，并且执行结果没有过期，那么就直接取查询结果返回给客户端。但在写SQL语句时注意，MySQL的查询缓冲是区分大小写的。如下列的两条SELECT语句：

SELECT * from TABLE1
SELECT * FROM TABLE1

上面的两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格（因为，查询缓冲并不自动截取首尾空格）。
虽然不设置查询缓冲，有时可能带来性能上的损失，但有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）。这样就需要把缓冲关了。当然，这可以通过设置query_cache_type的值来关闭查询缓冲，但这就将查询缓冲永久地关闭了。在MySQL 5.0中提供了一种可以临时关闭查询缓冲的方法：SQL_NO_CACHE。
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

以上的SQL语句由于使用了SQL_NO_CACHE，因此，不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。

我们还可以将my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。
SELECT SQL_CALHE * FROM TABLE1

8）、mysql_unbuffered_query() 无缓冲的查询

正常的情况下，当你在当你在你的脚本中执行一个SQL语句的时候，你的程序会停在那里直到没这个SQL语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为上面那句话翻译过来是说，mysql_unbuffered_query()发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且，mysql_num_rows()
或 mysql_data_seek() 将无法使用。所以，是否使用无缓冲的查询你需要仔细考虑。

9）、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符

因为这会使系统无法使用索引,而只能直接搜索表中的数据。例: SELECT
id FROM employee WHERE id != "B%" 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

10）、合理使用EXISTS,NOT EXISTS子句。如下所示：

1.SELECT
SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)
两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如：
IF (SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’)可以写成：

IF EXISTS
(SELECT * FROM table_name WHERE column_name = ‘xxx’)

12）、尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。

见如下例子：

SELECT * FROM
T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作，不要习惯性的使用 ‘%L%’这种方式(会导致全表扫描)，如果可以使用`L%’相对来说更好;

13）、能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源
在跨多个不同的数据库时使用UNION是一个有趣的优化方法，UNION从两个互不关联的表中返回数据，这就意味着不会出现重复的行，同时也必须对数据进行排序，我们知道排序是非常耗费资源的，特别是对大表的排序。
UNION ALL可以大大加快速度，如果你已经知道你的数据不会包括重复行，或者你不在乎是否会出现重复的行，在这两种情况下使用UNION ALL更适合。此外，还可以在应用程序逻辑中采用某些方法避免出现重复的行，这样UNION ALL和UNION返回的结果都是一样的，但UNION ALL不会进行排序。

14）、程序中如果一次性对同一个表插入多条数据，比如以下语句：

insert into
person(name,age) values(‘xboy’, 14);
insert into person(name,age) values(‘xgirl’, 15);
insert into person(name,age) values(‘nia’, 19);
把它拼成一条语句执行效率会更高.
insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);

15）、不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by。

SELECT
id,title,content,cat_id FROM article WHERE cat_id = 1;

上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引，那作用就相当大了。

16）．能不用order by就不用，如果一定要用order by可通过以下手段进行语句的mysql优化：

a. order by + limit组合的索引优化。如果一个sql语句形如：

select [column1],[column2],…. from [table] order by [sort] limit
[offset],[limit];

这个sql语句优化比较简单，在[sort]这个栏位上建立索引即可。

b. where + order by + limit组合的索引优化，形如：

select
[column1],[column2],…. from [table] where [columnx] = [value] order by
[sort]limit [offset],[limit];

这个语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引(columnx,sort)

c.where+order by多个栏位+limit，比如:

select *
from [table] where uid=1 order x,y limit 0,10;

对于这个语句，大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由mysql处理排序的机制造成的。

17）、必要时强制查询优化器使用某个索引

SELECT *
FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45) 改成：

SELECT *
FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)则查询优化器将会强行利用索引IX_ProcessID 执行查询。

18）、尽量不要用SELECT INTO语句。SELECT INTO
语句会导致表锁定，阻止其他用户访问该表。

2、UPDATE语句优化

更改查询被优化为有一个写开销的一个SELECT查询。写速度依赖于被更新数据大小和被更新索引的数量。使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表，做一行一行地很多更改比一次做一个快。

a) 尽量不要修改主键字段。
b) 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。
c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
d) 避免UPDATE将要复制到其他数据库的列。
e) 避免UPDATE建有很多索引的列。
f) 避免UPDATE在WHERE子句条件中的列。

3、INSERT语句优化

加快插入的一些方法：

·如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

·如果你从不同客户插入很多行，你能通过使用INSERT DELAYED语句得到更高的速度。

·注意，用MyISAM，如果在表中没有删除的行，能在SELECT:s正在运行的同时插入行。

·当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。

·当表有很多索引时，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程：

1）、有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。

2）、执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。

3）、使用myisamchk --keys-used=0 -rq
/path/to/db/tbl_name。这将从表中删除所有索引的使用。

4）、用LOAD DATA INFILE把数据插入到表中，这将不更新任何索引，因此很快。

5）、如果你有myisampack并且想要压缩表，在它上面运行myisampack。

6）、用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。

7）、执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。

这个过程将被构造进在MySQL的某个未来版本的LOAD DATA INFILE。

·你可以锁定你的表以加速插入

mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1，23)，(2，34)，(4，33);
mysql> INSERT INTO a VALUES (8，26)，(6，29);
mysql> UNLOCK TABLES;

主要的速度差别是索引缓冲区仅被清洗到磁盘上一次，在所有INSERT语句完成后。一般有与有不同的INSERT语句那样夺的索引缓冲区清洗。如果你能用一个单个语句插入所有的行，锁定就不需要。锁定也将降低多连接测试的整体时间，但是对某些线程最大等待时间将上升(因为他们等待锁)。例如：

thread 1 does 1000 inserts
thread 2， 3， and 4 does 1 insert
thread 5 does 1000 inserts

如果你不使用锁定，2、3和4将在1和5前完成。如果你使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。因为INSERT， UPDATE和DELETE操作在MySQL中是很快的，通过为多于大约5次连续不断地插入或更新一行的东西加锁，你将获得更好的整体性能。如果你做很多一行的插入，你可以做一个LOCK TABLES，偶尔随后做一个UNLOCK TABLES(大约每1000行)以允许另外的线程存取表。这仍然将导致获得好的性能。当然，LOAD
DATA INFILE对装载数据仍然是更快的。

为了对LOAD DATA INFILE和INSERT得到一些更快的速度，扩大关键字缓冲区。

4、DELETE语句优化

删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录，你可以增加索引缓存的大小。

从一个表删除所有行比删除行的一大部分也要得多。

索引对有效装载数据的影响

如果表是索引的，则可利用批量插入（LOAD
DATA 或多行的
INSERT 语句）来减少索引的开销。这样会最小化索引更新的影响，因为索引只需要在所有行处理过时才进行刷新，而不是在每行处理后就刷新。

·如果需要将大量数据装入一个新表，应该创建该表且在未索引时装载，装载数据后才创建索引，这样做较快。一次创建索引（而不是每行修改一次索引）较快。

·如果在装载之前删除或禁用索引，装入数据后再重新创建或启用索引可能使装载更快。
·如果想对数据装载使用删除或禁用策略，一定要做一些实验，看这样做是否值得（如果将少量数据装入一个大表中，重建和索引所花费的时间可能比装载数据的时间还要长）。

可用DROP INDEX和CREATE INDEX 来删除和重建索引。

另一种可供选择的方法是利用 myisamchk 或 isamchk 禁用和启用索引。这需要在 MySQL 服务器主机上有一个帐户，并对表文件有写入权。为了禁用表索引，可进入相应的数据库目录，执行下列命令之一：

shell>myisamchk --keys-used=0 tbl_name
shell>isamchk --keys-used=0 tbl_name

对具有 .MYI 扩展名的索引文件的 MyISAM 表使用 myisamchk，对具有 .ISM 扩展名的索引文件的 ISAM 表使用 isamchk。在向表中装入数据后，按如下激活索引：

shell>myisamchk --recover --quick --keys-used=0 tbl_name
shell>isamchk --recover --quick --keys-used=0 tbl_name

n 为表具有的索引数目。可用 --description 选项调用相应的实用程序得出这个值：

shell>myisamchk --discription tbl_name
$isamchk --discription tbl_name

如果决定使用索引禁用和激活，应该使用第13章中介绍的表修复锁定协议以阻止服务器同时更改锁（虽然此时不对表进行修复，但要对它像表修复过程一样进行修改，因此需要使用相同的锁定协议）。

五、延迟更新

我们经常需要给某些数据表添加一些需要经常更新的统计字段，例如用户的积分、文件的下载次数等等，而当这些数据更新的频率比较频繁的时候，数据库的压力也随之增大不少，我们可以利用高级模型的延迟更新功能缓解。

延迟更新功能是指我们可以给统计字段的更新设置一个延迟时间，在这个时间段内所有的更新会被累积缓存起来，然后定时地统一更新数据库。这比较适合某个字段经常需要递增或者递减，并且对实时性要求没有那么严格的情况。

如果我们需要给会员累积积分，可以使用

1. $User = D("User"); // 实例化User对象
2. $User->where('id=3')->setInc("score",10);// 用户的积分加10
3. $User->where('id=3')->setInc("score",30);// 用户的积分加30

上面的操作更新了两次用户积分，并且都实时保存到数据库。如果我们使用延迟更新方法，例如下面对用户的积分延迟更新60秒

1. $User->where('id=3')->setLazyInc("score",10,60);
2. $User->where('id=3')->setLazyInc("score",30,60);
3. $User->where('id=3')->setLazyInc("score",10,60);

那么60秒内执行的所有积分更新操作都会被延迟，实际会在60秒后统一更新积分到数据库，而不是每次都更新数据库。临时积分会被累积并缓存起来，最后到了延迟更新时间，再统一更新。

六、Sql语句优化工具

1）、慢日志

如果发现系统慢了，又说不清楚是哪里慢，那么就该用这个工具了。只需要为mysql配置参数，mysql会自己记录下来慢的sql语句。配置很简单，参数文件里配置：

slow_query_log=d:/slow.txt

long_query_time = 2

就可以在d:/slow.txt里找到执行时间超过2秒的语句了，根据这个文件定位问题吧。

·mysqldumpslow.pl

慢日志文件可能会很大，让人去看是很难受的事。这时候我们可以通过mysql自带的工具来分析。这个工具可以格式化慢日志文件，对于只是参数不同的语句会归类类并，比如有两个语句select * from a where
id=1 和select
* from a where id=2，经过这个工具整理后就只剩下select
* from a where id=N，这样读起来就舒服多了。而且这个工具可以实现简单的排序，让我们有的放矢。

2）、Explain

现在我们已经知道是哪个语句慢了，那么它为什么慢呢?看看mysql是怎么执行的吧，用explain可以看到mysql执行计划，下面的用法来源于手册

借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的统计(例如关键字集的势)，这样会影响优化器进行的选择。

还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不只是SELECT开头。

EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接(single-sweep
multi-join)的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。

当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。

从EXPLAIN的输出包括下面列：

·table
输出的行所引用的表。

· type
联结类型。各种类型的信息在下面给出。
不同的联结类型列在下面，以最好到最差类型的次序： system const eq_ref ref range index ALL possible_keys

· key
key列显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

· key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。

· ref
ref列显示哪个列或常数与key一起用于从表中选择行。

· rows
rows列显示MySQL相信它必须检验以执行查询的行数。

·Extra
如果Extra列包括文字Only index，这意味着信息只用索引树中的信息检索出的。通常，这比扫描整个表要快。如果Extra列包括文字where used，它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。

通过相乘EXPLAIN输出的rows行的所有值，你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。

例如