sql语句优化

Question

select count(*) from tab_log where tld = (select id from tab_tld_id where tld = 'cn')
大家帮我看一下这个SQL语句如何进行优化。之前的查询速度很慢。
多谢大家！！

Answer 1

select count(*) from tab_log A
INNER JOIN tab_tld_id B
ON A.tld =B.ID
where  B.tld = 'cn'

Answer 2

explain select count(*) 
from tab_log 
where tld = (select id from tab_tld_id where tld = 'cn');这种语法上就有问题啊，除非你的  (select id from tab_tld_id where tld = 'cn');只返回一行记录。如果只返回一行，则主要是优化你的 tab_log 表上 tld 这一列上加上索引。

Answer 3

确实是只返回一行，而且tab_log的tld字段已经建立了索引，表的数据量是7GB不知道还能用什么方法提高速度。

Answer 4

select count(*) from tab_log where tld = (select id from tab_tld_id where tld = 'cn') 
耗时：2min53.67sec
select count(*) from tab_log A 
INNER JOIN tab_tld_id B 
ON A.tld =B.ID 
where  B.tld = 'cn'
耗时：2min51.50sec我用explain看了一下，两种方法都是全表扫描，没有用到索引，难道count（*）就注定不能用索引吗？

Answer 5

CREATE TABLE tab_log(id         INT UNSIGNED NOT NULL AUTO_INCREMENT,time       TIME NOT NULL,ip         BIGINT UNSIGNED NOT NULL,domain     VARCHAR(1000) NOT NULL,htype      TINYINT UNSIGNED NOT NULL,tld        TINYINT UNSIGNED NOT NULL,qtype      TINYINT UNSIGNED NOT NULL,flag       TINYINT UNSIGNED NOT NULL,PRIMARY KEY(id)
)CREATE TABLE tab_tld_id(tld        CHAR(10) NOT NULL,id         TINYINT UNSIGNED NOT NULL,PRIMARY KEY(id));
tab_log的tld字段建立了索引，tab_tld_id的 tld字段建立了唯一索引。

Answer 6

select count(a.tld) from tab_log A 
INNER JOIN tab_tld_id B 
ON A.tld =B.ID 
where  B.tld = 'cn'在tab_tld_id上建立（ID，tld）复合索引

Answer 7

不好意思，刚才没有说清楚。tab_tld_id表的数据量很小，只有几行，tab_log的数据量为7GB。我用了上面的方法，用exlpain看了一下，仍然是对tab_log全表扫描。。

Answer 8

是的，这个从你的语法格式中就已经确定了。
现在估计你的问题主要是在 
select count(*) from tab_log where tld = 123;
直接把你(select id from tab_tld_id where tld = 'cn')的值代进去，看看时间是多少？另外从你的create table tab_log 语句上看不出来tld有索引存在。如果没有索引的话自然是个全表扫描。用 show index from tab_log; 看一下都有哪些索引。
然后再贴一下你的explain select count(*) from tab_log where tld = 123;

Answer 9

create index IX_tab_tld_id_main on tab_tld_id(tld,ID)create index IX_tab_log_main on tab_log(tld)执行上面语句后，再测试下效率看看
如果可以的话，把执行计划贴出来看看吧

Answer 10

+----+-------------+----------+------+---------------+------+---------+------+----------+-----------
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows     | Extra
+----+-------------+----------+------+---------------+------+---------+------+----------+-----------
|  1 | SIMPLE      | tab_log  | ALL  | NULL          | NULL | NULL    | NULL | 167460001|Using where
+----+-------------+----------+------+---------------+------+---------+------+----------+------------------

Answer 11

楼上再 show index 一下呢，很明显，似乎根本没有基于explain select count(*) from tab_log where tld = 123; 上的tld 的索引。

Answer 12

我检查过了，tld列上有索引，可是没有用，仍然是全表扫描，问题应该在于怎麽在语句中利用索引

Answer 13

有没有贴一下你的结果看一下。mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.06 sec)

Answer 14

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tab_log |          0 | PRIMARY    |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| tab_log |          0 | PRIMARY    |            2 | time        | A         |   167460001 |     NULL | NULL   |      | BTREE      |         | 
| tab_log |          1 | idx_ip     |            1 | ip          | A         |      900322 |     NULL | NULL   |      | BTREE      |         | 
| tab_log |          1 | idx_mhtype |            1 | htype       | A         |           5 |     NULL | NULL   |      | BTREE      |         | 
| tab_log |          1 | idx_mtld   |            1 | tld         | A         |          13 |     NULL | NULL   |      | BTREE      |         | 
| tab_log |          1 | idx_mqtype |            1 | qtype       | A         |          25 |     NULL | NULL   |      | BTREE      |         | 
| tab_log |          1 | idx_mflag  |            1 | flag        | A         |           4 |     NULL | NULL   |      | BTREE      |         | 
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
这个是结果。多谢！

Answer 15

是的，的确有 idx_mtld 这个基于 tld 的索引。但你可以看到 它的势Cardinality只有 13 ,也就是说你的表中至少167460001　条记录，但 tld 仅有 13 个左右，这个索引基本上没什么作用了。打个比方，你手上有 1000 个人员的名单，　让你把男同学全挑出来，　你是会先去看一张　性别，学号对照表（索引），从对照表中一个一个找到学号，然后到到队伍中的这个位置中找出这个人（利用索引），还是索性直接在队列中一个一个走下去，看到男的就挑出来（全表扫描）你可以通过　use index 来强制使用索引，但显然效率还不如全表扫描。

Answer 16

非常感谢ACMAIN_CHM 的生动解释！
照这样说的话，如果用位图索引会好一些？但是好像Mysql里没有位图索引，Oracle里有位图索引。