mysql栏目表设计,MySQL 表的设计

6

2012-03-05 10:34:29 +08:00

说明：

1.in(list)运算，如果list是一个明确的项目列表，in(list)运算性能尚可。

2.in(list)运算，如果list是一个子查询，那么只有在满足 1)父查询是覆盖索引查询 2)过滤性够好(我的不知道错误还是正确的85%无效结果的排除率) 的时候父查询才会用的上索引，反之则用不上索引。

就如上面那句

select feed_name, feed_url from feed where feed_id = ( select feed_id from user,subscription where user.user_id = subscription.user_id and user_name = 'Tom');

大部分人的想法可能是 首先 子查询查询一堆feed_id 之后 返回feed_id的列表给父查询

而实际上MySQL优化器并不会这么做，而是先对feed表进行全表扫描，或者如果有个 idx1(feed_id,feed_name, feed_url) 索引，则进行全索引扫描，之后把feed表的feed_id代入到子查询，效率有多低相信你应该知道。至少目前已经GA的版本都是这样，not in一样的方式。

mysql> show create table a\G

*************************** 1. row ***************************

Table: a

Create Table: CREATE TABLE `a` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`uid` int(10) NOT NULL,

`time` datetime NOT NULL,

`type` tinyint(4) DEFAULT NULL,

`status` varchar(320) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_uid` (`uid`)

) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

+----+-----+---------------------+------+-----------+

| id | uid | time | type | status |

+----+-----+---------------------+------+-----------+

| 1 | 1 | 2012-01-16 17:34:24 | 1 | vvvvvvvvv |

| 2 | 1 | 2012-01-16 17:34:10 | 2 | vvvvvvvvv |

| 3 | 1 | 2012-01-11 12:16:56 | 0 | vvvvvvvvv |

| 4 | 1 | 2012-01-16 17:34:24 | 1 | vvvvvvvvv |

| 5 | 1 | 2012-01-16 17:34:10 | 2 | vvvvvvvvv |

+----+-----+---------------------+------+-----------+

mysql> select * from c;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+----+

10 rows in set (0.00 sec)

1.如果list是一个明确的项目列表

mysql> explain select count(*) from a where uid in (1,2,3,4,5,6,7,8,9,10)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: a

type: range

possible_keys: idx_uid

key: idx_uid

key_len: 4

ref: NULL

rows: 4996

Extra: Using where; Using index

1 row in set (0.00 sec)

mysql> select sql_no_cache count(id) from b where uid in (1,2,3,4,5,6,7,8,9,10);

+-----------+

| count(id) |

+-----------+

| 5000 |

+-----------+

1 row in set (0.00 sec)

2.如果list是一个子查询，满足 1)父查询是覆盖索引查询 2)过滤性够好 父查询用的上索引

mysql> explain select count(uid) from a where uid in (select id from c)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: a

type: index

possible_keys: NULL

key: idx_uid

key_len: 4

ref: NULL

rows: 500101

Extra: Using where; Using index

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: c

type: unique_subquery

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: func

rows: 1

Extra: Using index

2 rows in set (0.00 sec)

mysql> select sql_no_cache count(uid) from a where uid in (select id from c);

+------------+

| count(uid) |

+------------+

| 5000 |

+------------+

1 row in set (1.61 sec)

2.如果list是一个子查询，不满足 1)父查询是覆盖索引查询 2)过滤性够好 父查询用不上索引

mysql> explain select count(time) from a where uid in (select id from c)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: a

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 500101

Extra: Using where

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: c

type: unique_subquery

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: func

rows: 1

Extra: Using index

2 rows in set (0.00 sec)

mysql> select sql_no_cache count(time) from a where uid in (select id from c);

+-------------+

| count(time) |

+-------------+

| 5000 |

+-------------+

1 row in set (1.68 sec)