mysql索引的问题

Question

select * from table1 t1 left jion table2 t2 on t1.two=t2.two
where t2.one='7'当t1的数据上万后，速度就比较慢了，table中的two,table2中的two,one分别建立的索引，
通过left 的关连后，table2中的one索引可能起不到作用了．
怎么办？
talbe1中没有存one信息．

Answer 1

t1 left join t2 ..
where t2.one=7;这种left join 和 inner join 完全等同。MySQL会自动把它优化成 inner join 的。所以最佳方案是 t2 有(one)的索引先用于 where, 然后再用 t1 的 (tow)索引进行 join.

Answer 2

select * from table1 t1 inner jion table2 t2 on t1.two=t2.two 
where t2.one='7' 
是这样么？速度也是很慢．

Answer 3

show index from table1;
show index from table2;
explain select * from table1 t1 inner jion table2 t2 on t1.two=t2.two  where t2.one='7' ;贴一下结果。这两天刚好有个贴子在讨论索引。
http://topic.csdn.net/u/20090526/17/639d78ec-e299-40d0-9c8e-8d5b21229405.html

Answer 4

select sd.id,left(sd.subject,14) as subject　from sortdata sd
inner join sorttwo s2 on sd.sorttwo=s2.id
where s2.oneid = 7 
order by sd.id desc 
limit 0,8结果还是很慢，去掉这句就快了．

Answer 5

where s2.oneid = 7 去掉这句．

Answer 6

先按3楼的的三个语句，贴一下你的索引情况和MySQL的执行计划，否则很难进行下一步分析。

Answer 7

mysql> show index from sortdata;
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Ca
rdinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
| sortdata |          0 | PRIMARY  |            1 | id          | A         |
    58798 |     NULL | NULL   |      | BTREE      |         |
| sortdata |          1 | sorttwo  |            1 | sorttwo     | A         |
        2 |     NULL | NULL   | YES  | BTREE      |         |
| sortdata |          1 | dottimes |            1 | posttime    | A         |
    58798 |     NULL | NULL   | YES  | BTREE      |         |
| sortdata |          1 | dottimes |            2 | dottimes    | A         |
    58798 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+
4 rows in set (0.16 sec)mysql> show index from sorttwo;
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
dinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| sorttwo |          0 | PRIMARY  |            1 | id          | A         |
      50 |     NULL | NULL   |      | BTREE      |         |
| sorttwo |          1 | orderno  |            1 | orderno     | A         |
      50 |     NULL | NULL   | YES  | BTREE      |         |
| sorttwo |          1 | oneid    |            1 | oneid       | A         |
      16 |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
mysql> select sd.id,left(sd.subject,14) as subject from sortdata sd
    -> inner join sorttwo s2 on sd.sorttwo=s2.id where s2.oneid=7
    -> order by sd.id desc limit 0,8;
+-------+------------------------------+
| id    | subject                      |
+-------+------------------------------+
| 44873 | 余姚红合塑化有限公司         |
| 44872 | 余姚市金马电器有限公司       |
| 44871 | 余姚万顺制笔厂               |
| 44870 | 余姚市信源印刷文化有限公司   |
| 44869 | 余姚富金文具礼品有限公司     |
| 44868 | 宁波中诺仪器设备有限公司     |
| 44867 | 王大军(个体经营)             |
| 44866 | 宁波雯宏喷雾器有限公司（外销 |
+-------+------------------------------+
8 rows in set (0.00 sec)

Answer 8

explain select * from table1 t1 inner jion table2 t2 on t1.two=t2.two  where t2.one='7' ;再用这个命令看一下MYSQL的执行计划。

Answer 9

EXPLAIN select sd.id,left(sd.subject,14) as subject from sortdata sd inner join sorttwo s2 on sd.sorttwo=s2.id where s2.oneid=7 order by sd.id desc limit 0,8;

Answer 10

mysql> EXPLAIN select sd.id,left(sd.subject,14) as subject from sortdata sd inne
r join sorttwo s2 on sd.sorttwo=s2.id where s2.oneid=7 order by sd.id desc limit
 0,8;
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref
          | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
|  1 | SIMPLE      | sd    | index  | sorttwo       | PRIMARY | 4       | NULL
          | 58798 |             |
|  1 | SIMPLE      | s2    | eq_ref | PRIMARY,oneid | PRIMARY | 4       | cixi.s
d.sorttwo |     1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
2 rows in set (0.01 sec)

Answer 11

看上去你sortdata表中sorttwo 的重复项很多。所以导致索引失效。
试一下这样。1. 创建索引如下
create index idx_sortdata_sorttwo_id on sortdata(sorttwo,id);2. 强制你的查询先做 select id from sorttwo where oneid=7
select sd.id,left(sd.subject,14) as subject 
from sortdata sd inner join (select id from sorttwo where oneid=7) s2 on sd.sorttwo=s2.id 
order by sd.id desc 
limit 0,8;

Answer 12

where s2.oneid=7　速度快的，换成where s2.oneid=２其他就慢了．oneid=7是有数据的．

Answer 13

从你10楼的结果可以看到 MySQL先做的 JOIN 再做的 where ,

Answer 14

where s2.oneid=2  花了16422ms 结果数据是空的where s2.oneid=7　花了４７ms 有数据

Answer 15

贴一下两句的explain 的信息。对比分析一下。

Answer 16

mysql> create index idx_sortdata_sorttwo_id on sortdata(sorttwo,id);
ERROR 1114 (HY000): The table '#sql-56c_36c' is full
mysql>
mysql>

Answer 17

mysql> EXPLAIN select sd.id,left(sd.subject,14) as subject from sortdata sd
    -> inner join sorttwo s2 on sd.sorttwo=s2.id where s2.oneid=2
    -> order by sd.id desc limit 0,8;
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref
          | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
|  1 | SIMPLE      | sd    | index  | sorttwo       | PRIMARY | 4       | NULL
          | 55439 |             |
|  1 | SIMPLE      | s2    | eq_ref | PRIMARY,oneid | PRIMARY | 4       | cixi.s
d.sorttwo |     1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN select sd.id,left(sd.subject,14) as subject from sortdata sd
    -> inner join sorttwo s2 on sd.sorttwo=s2.id where s2.oneid=7
    -> order by sd.id desc limit 0,8;
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref
          | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
|  1 | SIMPLE      | sd    | index  | sorttwo       | PRIMARY | 4       | NULL
          | 55439 |             |
|  1 | SIMPLE      | s2    | eq_ref | PRIMARY,oneid | PRIMARY | 4       | cixi.s
d.sorttwo |     1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------
----------+-------+-------------+
2 rows in set (0.00 sec)

Answer 18

从楼上记录看出，
|  1 | SIMPLE    | sd  | index  | sorttwo     | PRIMARY | 4     | NULL    | 55439 |       |
做了个全表扫描。 找到8条记录就停了，而找不到要一直找下去所以就慢了。你先试一下语句
select sd.id,left(sd.subject,14) as subject 
from sortdata sd inner join (select id from sorttwo where oneid=7) s2 on sd.sorttwo=s2.id 
order by sd.id desc 
limit 0,8;select sd.id,left(sd.subject,14) as subject 
from sortdata sd inner join (select id from sorttwo where oneid=2) s2 on sd.sorttwo=s2.id 
order by sd.id desc 
limit 0,8;的速度.

Answer 19

select sd.id,left(sd.subject,14) as subject 
from sortdata sd inner join (select id from sorttwo where oneid=7) s2 on sd.sorttwo=s2.id 
order by sd.id desc 
limit 0,8; 
花了：8047msselect sd.id,left(sd.subject,14) as subject 
from sortdata sd inner join (select id from sorttwo where oneid=2) s2 on sd.sorttwo=s2.id 
order by sd.id desc 
limit 0,8; 
花了：10781ms

Answer 20

反而更慢。select id from sorttwo where oneid=2　重复的记录是不是很多？
select count(*) from sorttwo where oneid=2　的结果是多少？

Answer 21

select count(*) from sorttwo where oneid=2
结果有７条oneid=2的．
结果有８条oneid=7的．

Answer 22

把 134 换成一个 select id from sorttwo where oneid=2 中的值试一下。如果慢，就要想办法在这个sortdata表起办法了。select sd.id,left(sd.subject,14) as subject 
from sortdata sd 
where sd.sorttwo=134
order by sd.id desc 
limit 0,8; 

Answer 23

select sd.id,left(sd.subject,14) as subject 
from sortdata sd 
where sd.sorttwo=134 
order by sd.id desc 
limit 0,8; 花了：14594msselect id from sortdata order by dottimes desc,id desc limit 0,10
花了：7984ms
我对dottimes也建了索引的．

Answer 24

我去删除dottimes这个索引时，
出现了以下错误：
error no 1114
the table '#sql-56c_35b' is full

Answer 25

error no 1114
the table '#sql-56c_35b' is full
你用的存储引擎是什么？
一般是由于 1. 磁盘满了，  2。 你的文件到限制了 2G或4G

Answer 26

时间主要是花在这张表上了。
试一下这个的速度
select count(*)
from sortdata sd 
where sd.sorttwo=134 如果速度快，则说明MySQL没有问题，需要我们进行索引的优化了。

Answer 27

select count(*)
from sortdata sd 
where sd.sorttwo=134 这个查询主要是想检测一下你的这个索引，理论上应该是0.01ms左右。
sortdata  1  sorttwo   1  sorttwo     A      2    NULL   NULL     YES    BTREE     sd

Answer 28

select count(*) 
from sortdata sd 
where sd.sorttwo=134 
我这里用了15ms

Answer 29

由于你的磁盘曾经满过，建议先做一表的修复check table sortdata ;
或者
analyze  table sortdata ;

Answer 30

check table sortdata ;
后还是一样的，
我另外一台电脑上也是挺慢的．那台空间有的．
ACMAIN_CHM　我是不是得把索引重新建一下呢？

Answer 31

你用的什么存储引擎？这个我也说不上来．我是mysql5

Answer 32

show create table sortdata ;看一下。

Answer 33

-----------------------------------+
| sortdata | CREATE TABLE `sortdata` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `subject` varchar(100) default NULL,
  `content` text,
  `sorttwo` varchar(45) default NULL,
  `infosten` varchar(45) default NULL,
  `inforank` varchar(45) default NULL,
  `istop` varchar(20) default NULL,
  `postusername` varchar(45) default NULL,
  `input` varchar(45) default NULL,
  `posttime` datetime default NULL,
  `endtime` datetime default NULL,
  `areatwo` varchar(45) default NULL,
  `pic1` varchar(45) default NULL,
  `pic2` varchar(45) default NULL,
  `pic3` varchar(45) default NULL,
  `dottimes` int(10) unsigned default NULL,
  `realname` varchar(45) default NULL,
  `tel` varchar(45) default NULL,
  `email` varchar(45) default NULL,
  `qq` varchar(45) default NULL,
  `signa` varchar(45) default NULL,
  `www` varchar(45) default NULL,
  `view` varchar(45) default NULL,
  `address` varchar(100) default NULL,
  PRIMARY KEY  (`id`),
  KEY `sorttwo` (`sorttwo`),
  KEY `dottimes` (`dottimes`),
  KEY `signa` (`dottimes`,`signa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
------------select left(subject,12) as subject from  sortdata where signa='是' order by dottimes desc limit 0,10这样的语句很慢，我建了signa，dottimes 联合索引，结果还是慢的．

Answer 34

ENGINE=InnoDB 存储引擎是 InnoDB 的, Analyze table 一下就应该可以了。对慢的语句，explain 一下看看是什么问题，否则只是说慢，很难分析。