mysql 优化小计

 Count()优化

1、COUNT()的作用

1、COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值时非空的（不统计NULL）。如果在COUNT()的括号中指定了列或列的表达式，统计的就是这个表达式有值的结果数。因为很多人对NULL理解有问题，所以这里很容易产生误解。如果想了解更多关于SQL语句中NULL的含义，建议阅读一些关于SQL语句基础的书籍。（关于这个话题，互联网上的一些信息是不够精确的）

2、COUNT()的另外一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用 COUNT(*)的时候，这种情况下通配符*并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

1、利用count（）来做一个简单的优化

有时候可以使用MyISAM在COUNT()全表非常快的这个特性，来加速一些特定条件COUNT()的查询。在下面的例子中，我们使用标准数据库world来看看如何快速查找到所有ID大于5的城市。可以像下面这样来写这个查询：

mysql>SELECT COUNT(*) FROM world.city WHERE ID > 5;

通过SHOW STATUS的结果可以看到该查询需要扫描4097行数据。如果将条件反转一下，先查找ID小于等于5的城市数，然后用总城市数一减就能得到同样的结果，却可以将扫描的行数减少到5行以内：

mysql>SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) FROM world.city WHERE ID <= 5;

这样做可以大大减少需要扫描的行数，是因为在查询优化阶段会将其中的子查询直接当成一个常数来处理，我们可以通过EXPLAIN来验证这点。

在邮件组和IRC聊天频道中，通常会看到这样的问题：如果在同一个查询中统计同一个列的不同值的数量，以减少查询的语句量。例如，假设可能需要通过一个查询返回各种不同颜色的商品数量，此时不能使用OR语句，因为这样做就无法区分不同颜色的商品数量。下面的查询可以在一定程度上解决这个问题。

mysql>SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;

也可以使用COUNT()而不是SUM()实现同样的目的，只需要将满足条件设置为真，不满足条件设置为NULL即可：

mysql>SELECT COUNT(color = 'blue' OR NULL) AS blue,COUNT(color = 'red' OR NULL) AS red FROM items;

 

Max优化

1、max()是什么？

Max() 函数是用来找出记录集中最大值的记录。

2、max()优化示例：

注：在执行SQL语句前加上explain可以查看MySql的执行计划

例如：查询最后支付时间

 explain select max(payment_date) from payment \G;

 mysql> explain select max(uname) from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

查询的类型为simple，没有用到任何索引，扫描行数为2行，用时0.00sec

优化方法：

在id列建立索引

create index id on user1(id);

Database changed
mysql> explain select max(id) from user1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec) 
  

此执行结果的原因为：因为索引是顺序排列的，通过索引，就可以马上知道最后一个是什么

MySql子查询优化

1、where型子查询

（把内层查询结果当作外层查询的比较条件）

#不用order by 来查询最新的商品

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods); 
  

#取出每个栏目下最新的产品(goods_id唯一)

select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by 
  cat_id);

2、from型子查询

(把内层的查询结果供外层再次查询)

#用子查询查出挂科两门及以上的同学的平均成绩
思路：

#先查出哪些同学挂科两门以上

select name,count(*) as gk from stu where score < 60 having gk >= 
  2;

#以上查询结果，我们只要名字就可以了，所以再取一次名字

select name from (select name,count(*) as gk from stu having gk >=2) as 
  t;

#找出这些同学了，那么再计算他们的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by 
  name;

3、exists型子查询

（把外层查询结果拿到内层，看内层的查询是否成立）

#查询哪些栏目下有商品，栏目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

Limit优化

1、Mysql limit 简单介绍

LIMIT子句一般是用来限制SELECT语句返回的实际行数。LIMIT取1个或是2个数字参数，如果给定的是2个参数，第一个指定要返回的第一行的偏移量，第二个指定返回行的最大数目。 例如：

select * from table LIMIT 5,10; #返回第6-15行数据

select * from table LIMIT 5; #返回前5行

select * from table LIMIT 0,5; #返回前5行

2、limit性能优化

当一个查询语句偏移量offset很大的时候，如select * from table limit 10000,10 , 最好不要直接使用limit，而是先获取到offset的id后，再直接使用limit size来获取数据。效果会好很多。

如：

select * From customers Where customer_id >=(
select customer_id From customers Order By customer_id limit 10000,1
) limit 10;

基于MySQL5.0中Mysql limit的高性能,我对数据分页也重新有了新的认识.

 
Select * From cyclopedia Where ID>=(  
Select Max(ID) From (  
Select ID From cyclopedia Order By ID limit 90001  
) As tmp  
) limit 100;  
Select * From cyclopedia Where ID>=(  
Select Max(ID) From (  
Select ID From cyclopedia Order By ID limit 90000,1  
) As tmp  
) limit 100;  

同样是取90000条后100条记录,第1句快还是第2句快?

第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录

第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录

第1句执行结果.100 rows in set (0.23) sec

第2句执行结果.100 rows in set (0.19) sec

很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.

其实第2句完全可以简化成

 
Select * From cyclopedia Where ID>=(  
Select ID From cyclopedia limit 90000,1  
)limit 100; 

直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足.

可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select * From cyclopedia limit 90000,1呢?岂不更简洁?

这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的"高分".Select * 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的.

 
Select Top 100 * From cyclopedia Where ID>=(  
Select Top 90001 Max(ID) From (  
Select ID From cyclopedia Order By ID  
) As tmp  
 )

但不管是实现方式是存贮过程还是直接代码中,瓶颈始终在于MS-SQL的TOP总是要返回前N个记录,这种情况在数据量不大时感受不深,但如果成百上千万,效率肯定会低下的.相比之下MySQL的Mysql limit就有优势的多,执行:

 
Select ID From cyclopedia limit 90000  
Select ID From cyclopedia limit 90000,1 

 

Mysql 慢查询

1、什么是慢查询，它有什么用处？

在运营网站的过程中，可能会遇到网站突然变慢的问题，一般情况下和 MySQL 慢有关系，可以通过开启慢查询，找到影响效率的 SQL ，然后采取相应的措施。MySQL有一个功能就是可以log下来运行的比较慢的sql语句，默认是没有这个log的，为了开启这个功能，要修改 my.cnf或者在MySQL启动的时候加入一些参数。

2、如何开启Mysql慢查询日志功能？

默认情况下，MySQL是不会记录超过一定执行时间的SQL语句的。要开启这个功能，我们需要修改MySQL的配置文件，windows下修改my.ini,Linux下修改my.cnf文件,在[mysqld]最后增加如下命令：

slow_query_log
long_query_time = 1

3、测试慢查询日志功能

(1)进入MySql控制台，执行如下语句:

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
 1 row in set 
  (2.12sec)

(2)查看慢查询日志文件think-slow.log，在文件最后发现：

# Time: 121120 20:06:23
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.104120  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1353413183;
select 
  sleep(2);

4、解释：

(1)slow_query_log这句是开启记录慢查询功能，slow_query_log= 0关闭；slow_query_log= 1开启（这个1可以不写）

(2)long_query_time = 1 这句是记录超过1秒的SQL执行语句

(3)那么这个日志文件存放在什么地方呢？
默认是放在mysql的data目录，并且文件名为host_name-slow.log即 主机名-slow.log,比如在笔者的开发机上就是THINK-slow.log(因为偶用的Thinkpad，呵呵)

(4)如果日志文件不想放在data目录，我们可以通过如下配置指定存放的目录及日志文件名：

slow_query_log_file=file_name
其中file_name就是你的存放日志的目录和文件名，在这里注意有的资料上可能是log-slow-queries=file_name,这个在mysql5.5版已经过时！

5、如何记录低于1s的查询记录呢？

MySQL5.21版以前long_query_time 参数的单位是秒，默认值是10。这相当于说最低只能记录执行时间超过 1 秒的查询，怎么记录查询时间超过100毫秒的SQL语句记录呢？在mysql5.21+后版本支持毫秒记录

(1)进入MySql控制台，运行如下sql语句：

set global long_query_time=0.1

该句是设置记录慢查询超过时间100ms的SQL，记住要重启mysql才能生效！

(2)测试

进入mysql控制台，执行如下sql语句：

select 
  sleep(0.5);

查看慢查询日志文件，我们看到最后添加的新信息，代码如下：

# Time: 121120 20:42:06
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.500028  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1353415326;
select 
  sleep(0.5);

结束语》》心若没有栖息的地方，走到哪里都是流浪.