第一条SQL
explain SELECT tid FROM threads WHERE tid not in(SELECT tid FROM threadtags) \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: threads
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 1556827
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: threadtags
type: index_subquery
possible_keys: tid
key: tid
key_len: 4
ref: func
rows: 23791
Extra: Using index; Using where
2 rows in set (0.00 sec)第二条SQL
explain SELECT t.tid FROM threads t LEFT JOIN threadtags tt ON t.tid=tt.tid WHERE tt.tid is null \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 1556827
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tt
type: ref
possible_keys: tid
key: tid
key_len: 4
ref: t.tid
rows: 19
Extra: Using where; Using index; Not exists
2 rows in set (0.00 sec)
请问怎么看?
因为我在页面测试的时候,感觉上很难判断,相差不是很大。
explain SELECT tid FROM threads WHERE tid not in(SELECT tid FROM threadtags) \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: threads
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 1556827
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: threadtags
type: index_subquery
possible_keys: tid
key: tid
key_len: 4
ref: func
rows: 23791
Extra: Using index; Using where
2 rows in set (0.00 sec)第二条SQL
explain SELECT t.tid FROM threads t LEFT JOIN threadtags tt ON t.tid=tt.tid WHERE tt.tid is null \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 1556827
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tt
type: ref
possible_keys: tid
key: tid
key_len: 4
ref: t.tid
rows: 19
Extra: Using where; Using index; Not exists
2 rows in set (0.00 sec)
请问怎么看?
因为我在页面测试的时候,感觉上很难判断,相差不是很大。
第二个好一点,有not exists。另外你的数据量有多大,数据量非常大的时候 explain才更有说服力
有A表,数据很多,id,name,fid
id是主键,name是varchar(20)的一个字段,fid的数据重复的非常多,这样的情况,fid需要建立索引吗?其中有用到 where fid=11这样的查询。另外在建立字段的时候 int(6) 和 int(6) unsigned 那种性能要更好点呢?
你的字段 内容是否有符号(+、-),如果没有, int(6)就可以了
Sending data0.0001250.00000
starting0.0000416.66667
freeing items0.000028.33333
init0.000014.16667
System lock0.000014.16667
preparing0.000014.16667
Table lock0.000014.16667
Opening tables0.000014.16667
statistics0.000014.16667
Total0.00024100.00000
Change Of STATUS VARIABLES Due To Execution Of Queryvariablevaluedescription
Bytes_received96Bytes sent from the client to the server
Bytes_sent2854Bytes sent from the server to the client
Com_select1Number of SELECT statements that have been executed
Handler_commit1Number of internal commit statements
Handler_read_first1Number of times the first entry was read from an index. A high value indicates that full index scans were done
Handler_read_key2Number of requests to read a row based on a key
Handler_read_rnd_next50Number of requests to read the next row in the data file. A high value indicates that a full table scan was required
Innodb_buffer_pool_read_requests#12The number of logical read requests Innodb has done
有100多万的数据,显示出来不是很耗时?还是我弄错了
Query_ID: 2
Duration: 10.36107775
Query: SELECT t.tid FROM threads t LEFT JOIN threadtags t
t ON t.tid=tt.tid WHERE fid=572 AND tt.tid is null
*************************** 3. row ***************************
Query_ID: 3
Duration: 4.82690300
Query: SELECT tid FROM threads WHERE fid=572 AND tid not in(SELECT
tid FROM threadtags)
t ON t.tid=tt.tid WHERE fid=572 AND tt.tid is null你原来的语句中并没有这个。 你需要重新EXPLAIN看一下分析结果。
ags tt ON t.tid=tt.tid WHERE fid=572 AND tt.tid is null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: displayorder,typeid
key: typeid
key_len: 2
ref: const
rows: 121899
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tt
type: ref
possible_keys: tid
key: tid
key_len: 4
ref: t.tid
rows: 19
Extra: Using where; Using index; Not exists
2 rows in set (0.02 sec)
mysql> EXPLAIN SELECT tid FROM threads WHERE fid=572 AND tid not in(S
LECT tid FROM threadtags) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: threads
type: ref
possible_keys: displayorder,typeid
key: typeid
key_len: 2
ref: const
rows: 121899
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: threadtags
type: index_subquery
possible_keys: tid
key: tid
key_len: 4
ref: func
rows: 49765
Extra: Using index; Using where
2 rows in set (0.03 sec)
Query_ID: 1
Duration: 1.43465575
Query: SELECT t.tid FROM threads t LEFT JOIN threadtags t
t ON t.tid=tt.tid WHERE fid=572 AND tt.tid is null
*************************** 2. row ***************************
Query_ID: 2
Duration: 1.47833125
Query: SELECT tid FROM threads WHERE fid=572 AND tid not in(SELECT
tid FROM threadtags)
2 rows in set (0.00 sec)
*************************** 2. row ***************************
Query_ID: 2
Duration: 1.44006850
Query: SELECT t.tid FROM threads t LEFT JOIN threadtags t
t ON t.tid=tt.tid WHERE fid=572 AND tt.tid is null
*************************** 3. row ***************************
Query_ID: 3
Duration: 1.40945000
Query: SELECT tid FROM threads WHERE fid=572 AND tid not in(SELECT
tid FROM threadtags)
恩,测试两次,第二个SQL稍微好点
感谢大家