SQL优化 ,麻烦大大帮忙

Question

CREATE TABLE `items_relate` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sku_id1` varchar(255) NOT NULL,
  `ADID1` varchar(255) NOT NULL,
  `sku_id2` varchar(255) NOT NULL,
  `ADID2` varchar(255) NOT NULL,
  `status` enum('-1','1','0') CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `dis` float unsigned DEFAULT NULL,
  `dis2` float unsigned DEFAULT NULL,
  `time_update` varchar(20) CHARACTER SET utf8 DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku_id1_2` (`sku_id1`,`sku_id2`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=2440652 DEFAULT CHARSET=latin1REATE TABLE `items` (
  `id` bigint(100) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  `CATID` int(100) unsigned DEFAULT NULL,
  `STYLEID` bigint(20) unsigned DEFAULT '0',
  `enable_STYLEID` enum('1','0') NOT NULL DEFAULT '0',
  `pic` varchar(100) DEFAULT '',
  `picture` varchar(255) DEFAULT '',
  `source` varchar(512) DEFAULT NULL,
  `repins` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'repins的计数器',
  `favorites` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '(post_fav)的计数器',
  `comments` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '(comments)的计数器',
  `orders` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '已经超过多少销售额',
  `price` decimal(9,2) DEFAULT NULL,
  `time_update` varchar(20) DEFAULT NULL COMMENT '更新时间',
  `rating` float NOT NULL DEFAULT '0',
  `viewcount` bigint(20) unsigned NOT NULL DEFAULT '0',
  `expired` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '是否过期, 1为是, 0为不是',
  `real_url` varchar(512) DEFAULT NULL COMMENT '对应的真正链接',
  `ADID` int(11) DEFAULT NULL,
  `shop_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `sku_id` bigint(20) unsigned DEFAULT NULL COMMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku_id` (`sku_id`,`ADID`),
  KEY `CATID` (`CATID`) USING BTREE,
  KEY `STYLEID` (`STYLEID`)
) ENGINE=MyISAM AUTO_INCREMENT=1991855 DEFAULT CHARSET=utf8SELECT count(0)
FROM items_relate ir
INNER JOIN (SELECT * FROM items  WHERE CATID='176' ) as i1  ON (i1.sku_id = ir.sku_id1 )注 items表有150w记录
items_relate表有170w记录
请大大帮忙, 看看有哪里可以优化的地方,在我本机查一次, 好几分钟才出来 查这个总数是为了分页, 避免不了, 
另外该mysql用到是亚马逊的rds, 最小的那个, 不知道各位用过没, 升级机器貌似帮不上什么忙, 欢迎各位提建议. 小弟在此先谢谢了.

Answer 1

explain一下 
SELECT * FROM items  WHERE CATID='176'这个数据有多少

Answer 2

7K多 
我执行了, 结果半个小时都没出来. 暂时还没有explain

Answer 3

尝试下
select count(*)
from items_relate ir INNER JOIN items i1 on i1.sku_id = ir.sku_id1
where i1.CATID='176'另外贴出你的explain select ...
show index from .. 以供分析。

Answer 4

select * FROM items_relate ir
left join items i1 on (ir.sku_id1 = i1.sku_id)
WHERE 1=1 AND ir.status ='0'AND i1.CATID='175'  order by ir.id asc limit 0, 20 这个也是慢的可怜

Answer 5

SELECT count(0)
FROM items_relate ir,item i1
where i1.sku_id = ir.sku_id1 and i1.CATID='176'
等价于这个
另外你执行十几分钟出来的结果是多少个

Answer 6

楼主的SQL查询语句到底是什么？怎么一会有ＯＲＤＥＲ　ＢＹ，一会没有？　不同的语句优化策略是不同的。建议详细说明自己的问题。

Answer 7

不好意思, 没有说清楚, 是这样的, 两条SQL的都是要用到的select count(*)
from items_relate ir INNER JOIN items i1 on i1.sku_id = ir.sku_id1
where i1.CATID='176';select * FROM items_relate ir
    left join items i1 on (ir.sku_id1 = i1.sku_id)
    WHERE 1=1 AND ir.status ='0'AND i1.CATID='175'  order by ir.id asc limit 0, 20 ;

Answer 8

我看了下，i1使用了索引：CATID是正确的；ir也使用了索引，且使用了覆盖索引，应该是非常快的了。建议1：where i1.CATID='176';中的CATID是int，就应该去掉引号！
建议2：加大mysql使用的内存！！！

Answer 9

谢谢分析...在SQL上真的没有其他的优化了么, 就算我这本地的(还特地加大了ini的配置), 也真的太慢了,  难道这1百多w的数据非得硬件上升级?
各位大大, 求建议.

Answer 10

谢谢分析...在SQL上真的没有其他的优化了么, 就算我这本地的(还特地加大了ini的配置), 也真的太慢了,  难道这1百多w的数据非得硬件上升级?
各位大大, 求建议.select * FROM items_relate ir 这个的优化是：仅查询出需要的列，不要使用select *查询的索引都使用上了，木有问题呢。估计是你的硬件太低了，要不你不ini贴出来看看？还有推荐看下mysql官方的文档，这里讲的非常非常详细，我给你找到了链接：
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#maintenance-schedule

Answer 11

http://wenku.baidu.com/view/b06e331552d380eb62946d0e.html
这个也可能会帮到你！

Answer 12

这个是我本机的ini, 麻烦大大帮忙看下.# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:\mysql\data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
#
# binary logging format - mixed recommended 
#binlog_format=mixed# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update  = /path-to-dedicated-directory/hostname# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = D:\Program Files (x86)\Zend\MySQL51\data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = D:\Program Files (x86)\Zend\MySQL51\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50[mysqldump]
quick
max_allowed_packet = 16M[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M[mysqlhotcopy]
interactive-timeout

Answer 13

100 万的数据应该在1秒左右出来的....你的索引没有用好..如果确定索引没有问题,那就是MY.ini需要调整..参考.. paip.提升性能--- mysql 建立索引 删除索引 很慢的解决.