mysql sql 优化二

 
  
   完成相同功能，二种不同方式；
   下一种速度快一些；
  
  
   1. mysql> select * from wanyouxi where youxitag='bird' ORDER BY credits desc limit 1; 
   2. +-------+------+--------+------------+---------+------------+------+-----+------+ 
   3. | id    | uid  | gameid | youxitag| credits | dateline   | type | sid | wins | 
   4. +-------+------+--------+------------+---------+------------+------+-----+------+ 
   5. | 49578 | 1155 |      0 | bird|   43830 | 1221623720 |    0 |   6 |    0 |  
   6. +-------+------+--------+------------+---------+------------+------+-----+------+ 
   7. 1 row in set (0.37 sec) 

   1. mysql> SELECT * from wanyouxi where youxijifen= (SELECT MAX(youxijifen) FROM cdb_playsgame where youxitag='bird') and youxitag='bird'; 
   2. +-------+------+--------+------------+---------+------------+------+-----+------+ 
   3. | id    | uid  | gameid | youxitag| credits | dateline   | type | sid | wins | 
   4. +-------+------+--------+------------+---------+------------+------+-----+------+ 
   5. | 49578 | 1155 |      0 | bird|   43830 | 1221623720 |    0 |   6 |    0 |  
   6. +-------+------+--------+------------+---------+------------+------+-----+------+ 
   7. 1 row in set (0.30 sec) 



   、、＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

   //临时表；；
   SQL优化--MYSQL中的临时表
当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，
不是对整个表运行这些查询，
而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快
些，然后多这些表运行查询。

创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字：

Sql代码

   1. CREATE TEMPORARY TABLE tmp_table ( 
   2. name VARCHAR(10) NOT NULL, 
   3. value INTEGER NOT NULL 
   4. ) 

CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
)



临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

Sql代码

   1. DROP TABLE tmp_table 

DROP TABLE tmp_table



如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在，临时表将有必要屏蔽（隐藏）非临时表tmp_table。

如果你声明临时表是一个HEAP表，MySQL也允许你指定在内存中创建它：

Sql代码

   1. 　CREATE TEMPORARY TABLE tmp_table ( 
   2. name VARCHAR(10) NOT NULL, 
   3. value INTEGER NOT NULL 
   4. ) TYPE = HEAP 

　CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
value INTEGER NOT NULL
) TYPE = HEAP

　

因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。然而，HEAP表与一般的表有些不同，且有自身的限制。详见MySQL参考手册。

正如前面的建议，你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引，临时表可能一点不快。


//-----------------------------------------------

SQL优化--select into 和 insert into select 两种表复制语句
Sql代码

   1. select * into destTbl from srcTbl 
   2. insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl 

select * into destTbl from srcTbl
insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl
以上两句都是将 srcTbl 的数据插入到 destTbl，但两句又有区别的。
第一句（select into from）要求目标表（destTbl）不存在，因为在插入时会自动创建。
第二句（insert into select from）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5。



//=============================

SQL优化--使用索引提高查询速度
如果EXPLAIN结果possible_key列包含一些NULL值，可能需要对正在被讨论的表添加一个索引来提高查询性能。
ALTER TABLE table ADD INDEX(column);

ALTER TABLE `cdb` ADD INDEX `dolumn` ( `column` )

ALTER TABLE `cdb_playsgame` DROP INDEX `gametag` 

//---------------------

SQL优化--count
尽量不用count(*), 最好用count(id)

//====================


SQL优化 随机显示记录
SELECT * FROM Ads ORDER BY RAND() LIMIT 1
我们可以使用索引，并分解为下列语句
SELECT @row_id := COUNT(*) FROM Ads;
SELECT @row_id := FLOOR(RAND() * @row_id) + 1;
SELECT * FROM Ads WHERE ad_id = @row_id;


//
SQL 优化增加查询的范围限制
第三掌 增加查询的范围限制
增加查询的范围限制，避免全范围的搜索。
例3：以下查询表record 中时间ActionTime小于2001年3月1日的数据：
select * from record where ActionTime < to_date ('20010301' ,'yyyymm')
查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。修改如下： select * from record where
ActionTime < to_date ('20010301' ,'yyyymm')
and   ActionTime > to_date ('20010101' ,'yyyymm')
后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。把'20010301'换成一个变量，根据取值的机率，可以有一半以上的机会提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。


///==============
网站开发日记(14)-MYSQL子查询和嵌套查询优化
查询游戏历史成绩最高分前100

Sql代码

   1. SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits)  
   2. FROM cdb_playsgame ps1  
   3. where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3'  
   4. GROUP BY ps.uid order by ps.credits desc LIMIT 100; 

SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits)
FROM cdb_playsgame ps1
where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3'
GROUP BY ps.uid order by ps.credits desc LIMIT 100;





Sql代码

   1. SELECT ps.*  
   2. FROM cdb_playsgame ps,(select ps1.uid, ps1.gametag, MAX(credits) as credits 
   3. FROM cdb_playsgame ps1 group by uid,gametag) t 
   4. WHERE ps.credits=t.credits AND ps.uid=t.uid AND ps.gametag=t.gametag AND ps.gametag='yeti3'  
   5. GROUP BY ps.uid order by ps.credits desc LIMIT 100; 

SELECT ps.*
FROM cdb_playsgame ps,(select ps1.uid, ps1.gametag, MAX(credits) as credits
FROM cdb_playsgame ps1 group by uid,gametag) t
WHERE ps.credits=t.credits AND ps.uid=t.uid AND ps.gametag=t.gametag AND ps.gametag='yeti3'
GROUP BY ps.uid order by ps.credits desc LIMIT 100;

执行时间仅为0.22秒，比原来的25秒提高了10000倍


查询当天游戏最好成绩
Sql代码

   1.  SELECT ps. * , mf. * , m.username 
   2. FROM cdb_playsgame ps 
   3. LEFT JOIN cdb_memberfields mf ON mf.uid = ps.uid 
   4. LEFT JOIN cdb_members m ON m.uid = ps.uid 
   5. WHERE ps.gametag = 'chuansj' 
   6. AND FROM_UNIXTIME( ps.dateline, '%Y%m%d' ) = '20081008' 
   7. AND ps.credits = ( 
   8. SELECT MAX( ps1.credits ) 
   9. FROM cdb_playsgame ps1 
  10. WHERE ps.uid = ps1.uid 
  11. AND ps1.gametag = 'chuansj' 
  12. AND FROM_UNIXTIME( ps1.dateline, '%Y%m%d' ) = '20081008' ) 
  13. GROUP BY ps.uid 
  14. ORDER BY credits DESC 
  15. LIMIT 0 , 50  

SELECT ps. * , mf. * , m.username
FROM cdb_playsgame ps
LEFT JOIN cdb_memberfields mf ON mf.uid = ps.uid
LEFT JOIN cdb_members m ON m.uid = ps.uid
WHERE ps.gametag = 'chuansj'
AND FROM_UNIXTIME( ps.dateline, '%Y%m%d' ) = '20081008'
AND ps.credits = (
SELECT MAX( ps1.credits )
FROM cdb_playsgame ps1
WHERE ps.uid = ps1.uid
AND ps1.gametag = 'chuansj'
AND FROM_UNIXTIME( ps1.dateline, '%Y%m%d' ) = '20081008' )
GROUP BY ps.uid
ORDER BY credits DESC
LIMIT 0 , 50



像查询里
Sql代码

   1. AND ps.credits=(SELECT MAX(ps1.credits)  
   2.   FROM {$tablepre}playsgame ps1 where ps.uid=ps1.uid AND ps1.gametag = '$game'   
   3.   AND FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime'  ) 

AND ps.credits=(SELECT MAX(ps1.credits)
  FROM {$tablepre}playsgame ps1 where ps.uid=ps1.uid AND ps1.gametag = '$game' 
  AND FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime'  )


特别消耗时间

另外，像：
Sql代码

   1. FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime' 

FROM_UNIXTIME(ps1.dateline, '%Y%m%d') = '$todaytime'

这样的语句会导致索引无效，因为对每个dataline的值都需要用函数计算一遍，需要调整为：
Sql代码

   1. AND ps1.dateline >= UNIX_TIMESTAMP('$todaytime')   

AND ps1.dateline >= UNIX_TIMESTAMP('$todaytime') 



//更改后
Sql代码

   1.  SELECT ps. * , mf. * , m.username 
   2. FROM cdb_playsgame ps, cdb_memberfields mf, cdb_members m, ( 
   3.  
   4. SELECT ps1.uid, MAX( ps1.credits ) AS credits 
   5. FROM cdb_playsgame ps1 
   6. WHERE ps1.gametag = 'chuansj' 
   7. AND ps1.dateline >= UNIX_TIMESTAMP( '20081008' ) 
   8. GROUP BY ps1.uid 
   9. ) AS t 
  10. WHERE mf.uid = ps.uid 
  11. AND m.uid = ps.uid 
  12. AND ps.gametag = 'chuansj' 
  13. AND ps.credits = t.credits 
  14. AND ps.uid = t.uid 
  15. GROUP BY ps.uid 
  16. ORDER BY credits DESC 
  17. LIMIT 0 , 50  

SELECT ps. * , mf. * , m.username
FROM cdb_playsgame ps, cdb_memberfields mf, cdb_members m, (

SELECT ps1.uid, MAX( ps1.credits ) AS credits
FROM cdb_playsgame ps1
WHERE ps1.gametag = 'chuansj'
AND ps1.dateline >= UNIX_TIMESTAMP( '20081008' )
GROUP BY ps1.uid
) AS t
WHERE mf.uid = ps.uid
AND m.uid = ps.uid
AND ps.gametag = 'chuansj'
AND ps.credits = t.credits
AND ps.uid = t.uid
GROUP BY ps.uid
ORDER BY credits DESC
LIMIT 0 , 50



对于每个球员，找出球员号码，名字以及他所引起的罚款的号码，但只是针对那些至少有两次罚款的球员。

更紧凑的查询，在FROM子句中放置一个子查询。
Sql代码

   1. SELECT PLAYERNO,NAME,NUMBER 
   2. FROM (SELECT PLAYERNO,NAME, 
   3.              (SELECT COUNT(*) 
   4.               FROM PENALTIES 
   5.               WHERE PENALTIES.PLAYERNO = 
   6.                     PLAYERS.PLAYERNO) 
   7.               AS NUMBER 
   8.        FROM PLYERS) AS PN 
   9. WHERE NUMBER>=2 

SELECT PLAYERNO,NAME,NUMBER
FROM (SELECT PLAYERNO,NAME,
             (SELECT COUNT(*)
              FROM PENALTIES
              WHERE PENALTIES.PLAYERNO =
                    PLAYERS.PLAYERNO)
              AS NUMBER
       FROM PLYERS) AS PN
WHERE NUMBER>=2



FROM子句中的子查询决定了每个球员的号码，名字和罚款的编号。接下来，这个号码变成了中间结果中的一列。然后指定了一个条件（NUMBER>=2）;最后，获取SELECT子句中的列。



//================
group by 统计数量后再排序
首页需要做个调整就是，把玩的最多的游戏进行统计数量然后再按大小排序
SELECT gametag, count( * ) AS cntNum
FROM cdb_playsgame
GROUP BY gametag
ORDER BY cntNum

按照组统计的数量再排序



//====================

2008-10-21
MYSQL优化--show status解详
SHOW STATUS提供服务器的状态信息(象mysqladmin extended-status一样)。输出类似于下面的显示，尽管格式和数字可以有点不同：

+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Connections | 17 |
| Created_tmp_tables | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 2 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 0 |
| Handler_read_rnd | 35 |
| Handler_update | 0 |
| Handler_write | 2 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 11 |
| Questions | 14 |
| Slow_queries | 0 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 149111 |
+--------------------------+--------+

上面列出的状态变量有下列含义：

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。

关于上面的一些注释：

如果Opened_tables太大，那么你的table_cache变量可能太小。
如果key_reads太大，那么你的key_cache可能太小。缓存命中率可以用key_reads/key_read_requests计算。
如果Handler_read_rnd太大，那么你很可能有大量的查询需要MySQL扫描整个表或你有没正确使用键值的联结(join)。
SHOW VARIABLES显示出一些MySQL系统变量的值，你也能使用mysqladmin variables命令得到这个信息。如果缺省值不合适，你能在mysqld启动时使用命令行选项来设置这些变量的大多数。输出类似于下面的显示，尽管格式和数字可以有点不同：

+------------------------+--------------------------+
| Variable_name | Value |
+------------------------+--------------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | /my/monty/ |
| datadir | /my/monty/data/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer_size | 131072 |
| flush_time | 0 |
| interactive_timeout | 28800 |
| key_buffer_size | 1048540 |
| language | /my/monty/share/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| net_buffer_length | 16384 |
| port | 3306 |
| protocol-version | 10 |
| record_buffer | 131072 |
| skip_locking | ON |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097116 |
| table_cache | 64 |
| thread_stack | 131072 |
| tmp_table_size | 1048576 |
| tmpdir | /machine/tmp/ |
| version | 3.23.0-alpha-debug |
| wait_timeout | 28800 |
+------------------------+--------------------------+

见10.2.3 调节服务器参数。

SHOW PROCESSLIST显示哪个线程正在运行，你也能使用mysqladmin processlist命令得到这个信息。如果你有process权限， 你能看见所有的线程，否则，你仅能看见你自己的线程。见7.20 KILL句法。如果你不使用FULL选项，那么每个查询只有头100字符被显示出来。

SHOW GRANTS FOR user列出对一个用户必须发出以重复授权的授权命令。

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+


//===============================

MAX函数和GROUP BY 语句一起使用的一个误区
摘自yueliangdao0608.cublog.cn
http://blog.chinaunix.net/u/29134/
MAX函数和GROUP BY 语句一起使用的一个误区



使用MAX 函数和 GROUP 的时候会有不可预料的数据被SELECT 出来。
下面举个简单的例子：
想知道每个SCOREID 的 数学成绩最高的分数。


表信息：
/*DDL Information For - test.lkscore*/
--------------------------------------

Table    Create Table                                                               
-------  -----------------------------------------------------------------------------
lkscore  CREATE TABLE `lkscore` (                                                   
           `scoreid` int(11) DEFAULT NULL,                                          
           `chinese` int(11) DEFAULT '0',                                           
           `math` int(11) DEFAULT '0',                                              
           KEY `fk_class` (`scoreid`),                                              
           CONSTRAINT `fk_class` FOREIGN KEY (`scoreid`) REFERENCES `lkclass` (`id`)
         ) ENGINE=InnoDB DEFAULT CHARSET=gb2312                                     




select * from lkscore;



query result(12 records)
scoreid chinese math
1 90 80
2 100 99
3 29 98
4 87 79
5 89 99
1 49 98
3 98 56
2 76 88
2 80 90
3 90 70
1 90 90
1 67 90


错误的SELECT
select scoreid,chinese,max(math) max_math from lkscore group by scoreid;



query result(5 records)
scoreid chinese max_math
1 90 98
2 100 99
3 29 98
4 87 79
5 89 99
上面的90明显不对。

方法一：

select scoreid,chinese,math max_math from


(

select * from lkscore order by math desc


) T


group by scoreid;



query result(5 records)
scoreid chinese max_math
1 49 98
2 100 99
3 29 98
4 87 79
5 89 99

方法二：


select * from lkscore a where a.math = (select max(math) from lkscore where scoreid = a.scoreid) order by scoreid asc;



query result(5 records)
scoreid chinese max_math
1 49 98
2 100 99
3 29 98
4 87 79
5 89 99

这个也是用MAX函数，而且还用到了相关子查询。
我们来看一下这两个的效率如何：


explain
select scoreid,chinese,math max_math from (select * from lkscore order by math desc) T group by scoreid;



query result(2 records)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 12 Using temporary; Using filesort
2 DERIVED lkscore ALL (NULL) (NULL) (NULL) (NULL) 12 Using filesort

很明显，有两个FULL TABLE SCAN。



explain
select scoreid,chinese,math max_math from lkscore a where a.math =
(select max(math) from lkscore where scoreid = a.scoreid) order by scoreid asc;



query result(2 records)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a index (NULL) fk_class 5 (NULL) 12 Using where
2 DEPENDENT SUBQUERY lkscore ref fk_class fk_class 5 a.scoreid 1 Using where


第二个就用了KEY,子查询里只扫描了一跳记录。

很明显。在这种情况下第二个比第一个效率高点。