SQL语句查询慢

txwdhs

[mysqld]
port            = 3311
socket          = /tmp/mysql.sock
datadir = /opt/mysql
max_connections = 512
max_connect_errors = 10000000
log = /data/mysql/mysqld_common.log
log-error = /data/mysql/mysqld_err.log
#skip-locking
#key_buffer_size = 16M
#max_allowed_packet = 1M
#table_open_cache = 64
#sort_buffer_size = 512K
#net_buffer_length = 8K
#read_buffer_size = 256K
#read_rnd_buffer_size = 512K
#event_scheduler = 1
key_buffer_size = 512M
max_allowed_packet = 16M
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 128M
#log-bin=mysql-bin
long_query_time = 0.5
expire-logs-days = 3
log-slow-queries=/data/mysql/slowquery.log
#binlog-do-db = jmh
#innodb_buffer_pool_size = 512M
innodb_buffer_pool_size = 3G
table_cache = 2048
#innodb_log_file_size = 128M
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
#DEFAULT-CHARACTER-SET = utf8
innodb_thread_concurrency = 4
character_set_server = utf8
log_bin_trust_function_creators = 1
replicate-ignore-db = mysql
wait-timeout = 1800
innodb_data_file_path = ibdata1:10M:autoextend



CREATE TABLE `TIMELINE_ITEM_HOME_74` (
  `TLID` bigint(16) NOT NULL,
  `OWNUNO` varchar(36) NOT NULL,
  `TLDOMAIN` varchar(32) NOT NULL,
  `TLCONTENTTYPE` varchar(32) NOT NULL,
  `DIRECTUNO` varchar(36) NOT NULL,
  `DIRECTID` varchar(36) NOT NULL,
  `PARENTUNO` varchar(36) DEFAULT NULL,
  `PARENTID` varchar(36) DEFAULT NULL,
  `RELATIONUNO` varchar(36) DEFAULT NULL,
  `RELATIONID` varchar(36) DEFAULT NULL,
  `FILTERTYPE` int(11) NOT NULL DEFAULT '1' COMMENT '过滤类型',
  `TLDESCRIPTION` varchar(12 DEFAULT NULL,
  `CREATEDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `REMOVESTATUS` varchar(32) NOT NULL,
  `REMOVEDATE` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`TLID`) USING BTREE,
  UNIQUE KEY `IDX_TLI_HOME_74_UNIQUE` (`OWNUNO`,`DIRECTID`) USING BTREE,
  KEY `IDX_TLI_HOME_74_CREATEDATE` (`CREATEDATE`) USING BTREE,
  KEY `IDX_TLI_HOME_74_REMOVE_UNO` (`REMOVESTATUS`,`OWNUNO`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
TIMELINE_ITEM_HOME_74 0 PRIMARY 1 TLID A 1485352 \N \N  BTREE
TIMELINE_ITEM_HOME_74 0 IDX_TLI_HOME_74_UNIQUE 1 OWNUNO A \N \N \N  BTREE
TIMELINE_ITEM_HOME_74 0 IDX_TLI_HOME_74_UNIQUE 2 DIRECTID A 1485352 \N \N  BTREE
TIMELINE_ITEM_HOME_74 1 IDX_TLI_HOME_74_CREATEDATE 1 CREATEDATE A 4612 \N \N  BTREE
TIMELINE_ITEM_HOME_74 1 IDX_TLI_HOME_74_REMOVE_UNO 1 REMOVESTATUS A 2 \N \N  BTREE
TIMELINE_ITEM_HOME_74 1 IDX_TLI_HOME_74_REMOVE_UNO 2 OWNUNO A 21 \N \N  BTREE


[root@web002 mysql]# mysqldumpslow -t 20 slowquery.log
Reading mysql slow query log from slowquery.log
Count: 3  Time=348.40s (1045s)  Lock=0.04s (0s)  Rows=1.0 (3), timelinero[timelinero]@[172.16.75.82]
  SELECT COUNT(N) FROM TIMELINE_ITEM_HOME_49 WHERE OWNUNO = 'S' AND REMOVESTATUS = 'S'
Count: 1  Time=280.57s (280s)  Lock=0.00s (0s)  Rows=50.0 (50), timelinero[timelinero]@[172.16.75.82]
  SELECT * FROM TIMELINE_ITEM_HOME_11 WHERE OWNUNO = 'S' AND REMOVESTATUS = 'S' ORDER BY CREATEDATE DESC LIMIT N, N
Count: 1  Time=273.89s (273s)  Lock=0.00s (0s)  Rows=50.0 (50), timelinero[timelinero]@[172.16.75.82]
  SELECT * FROM TIMELINE_ITEM_HOME_03 WHERE OWNUNO = 'S' AND REMOVESTATUS = 'S' ORDER BY CREATEDATE DESC LIMIT N, N
Count: 2  Time=220.69s (441s)  Lock=0.00s (0s)  Rows=50.0 (100), timelinero[timelinero]@[172.16.75.82]
  SELECT * FROM TIMELINE_ITEM_HOME_29 WHERE OWNUNO = 'S' AND REMOVESTATUS = 'S' ORDER BY CREATEDATE DESC LIMIT N, N


以上是基本信息，现在问题就在TIMELINE一个语句要100到300秒的时间，数据在100W到200W之间，不知道怎么才能再进行优化了，之前问过问题，没有解决，机器配置是dell的R310，4G内存，硬盘7200转没做RAID，测试环境这个是，下面是执行计划

EXPLAIN SELECT  * FROM TIMELINE_ITEM_HOME_74 WHERE OWNUNO = '49652ece-bc1e-4ef0-928b-8085a4f9a4dd' AND REMOVESTATUS = 'n' ORDER BY CREATEDATE DESC LIMIT 0,50;



id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE TIMELINE_ITEM_HOME_74 ref IDX_TLI_HOME_74_UNIQUE,IDX_TLI_HOME_74_REMOVE_UNO IDX_TLI_HOME_74_UNIQUE 110 const 2683 Using where; Using filesort


现在真的没有头绪要怎么办了，请教大家帮忙想想，在线等，谢谢！！

飞鸿无痕

KEY `IDX_TLI_HOME_74_REMOVE_UNO` (`REMOVESTATUS`,`OWNUNO`)
这个索引为什么不把OWNUNO放在前面呢？ 感觉OWNUNO的辨析度会比REMOVESTATUS的大很多，这样你的EXPLAIN SELECT  * FROM TIMELINE_ITEM_HOME_74 WHERE OWNUNO = '49652ece-bc1e-4ef0-928b-8085a4f9a4dd' AND REMOVESTATUS = 'n' ORDER BY CREATEDATE DESC LIMIT 0,50;就能用到联合索引了！

txwdhs

回复 2# 飞鸿无痕


    那我去试试，改一下，那这么说的话，我单独建一个UNO的索引，不建status的也差不多吧应该？

飞鸿无痕

回复 3# txwdhs


    不一样，你前面有UNIQUE KEY `IDX_TLI_HOME_74_UNIQUE` (`OWNUNO`,`DIRECTID`) USING BTREE,索引了，所以你单独建OWNUNO索引和没有建是一样的！还浪费空间，使表更新变慢！

txwdhs

回复 4# 飞鸿无痕


    明白，那order by的话怎么办呢？有他的话怎么能优化下呢？他走不到索引

飞鸿无痕

一个表只能用到一个索引，试一下建立OWNUNO，REMOVESTATUS ，CREATEDATE 三个的联合索引看看！

txwdhs

问题已经解决了， 创建了一个OWNUNO，REMOVESTATUS，createdate的索引，现在速度快起来了，可还是有一个问题，现在比较慢的语句还有一个SELECT COUNT(N) FROM TIMELINE_ITEM_HOME_70 WHERE OWNUNO = 'S' AND REMOVESTATUS = 'S'
这个比上面那句少了order by可是现在如果我不强制他走新建的索引，他就会走那个唯一索引，有什么办法让他快起来吗？求助各位高手

strong_sjhi

一个表只能用到一个索引，试一下建立OWNUNO，REMOVESTATUS ，CREATEDATE 三个的联合索引看看！
飞鸿无痕 发表于 2011-12-01 17:36

    一个表只能用到一个索引,我感觉这句话好象有点问题.

飞鸿无痕

回复 8# strong_sjhi


    我记得是这样，即使使用组合索引，也只能是一个的。求教，能否举个例子一个表能用到多个索引的？