由于我现在没有大量数据来实测其性能,所以请大家帮我分析一下如果数据量比较多时的情况。
第一个例子用来查pdate列有多少个不同值
1 单查询:
desc select count(distinct(pdate)) as n from paper where paperid=1;
+----+-------------+-------+------+-----------------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | paper | ref | paperid,paperid_pdate | paperid_pdate | 4 | const | 5107 | Using index |
+----+-------------+-------+------+-----------------------+---------------+---------+-------+------+-------------+
2 复合查询:
desc select count(1) from (select distinct(pdate) as n from paper where paperid=1) t;
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+---------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | paper | range | paperid,paperid_pdate | pdate_paperid | 7 | NULL | 6 | Using where; Using index for group-by |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+---------------------------------------+请问1与2效率那个高?第2个例子,用来列出各个paperid对应的最大的pdate,但是需要join另一个表
1 简单连接
desc select a.name,t.paperid,max(t.pdate) as date from paper t left join paper_admin a on a.id=t.paperid where a.area='广东';
+----+-------------+-------+------+-----------------------+---------------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+---------------+---------+----------+------+-------------+
| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where |
| 1 | SIMPLE | t | ref | paperid,paperid_pdate | paperid_pdate | 4 | wap.a.id | 871 | Using index |
+----+-------------+-------+------+-----------------------+---------------+---------+----------+------+-------------+
2 复合连接
desc select a.name,t.* from (select a.paperid,max(a.pdate) as date from paper a group by a.paperid)t left join paper_admin a on a.id=t.paperid where a.area='广东'
+----+-------------+------------+--------+---------------+---------------+---------+-----------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------------+---------+-----------+------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 6 | |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | t.paperid | 1 | Using where |
| 2 | DERIVED | a | range | NULL | paperid_pdate | 4 | NULL | 8 | Using index for group-by |
+----+-------------+------------+--------+---------------+---------------+---------+-----------+------+--------------------------+另附表结构如下(其中表paper_admin大致不会超过100行,而paper表的数据则会随时间不断增多):
mysql> desc paper;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| paperid | int(11) | NO | MUL | 0 | |
| pdate | date | NO | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+
mysql> desc paper_admin;
+----------+---------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| area | varchar(30) | NO | | | |
+----------+---------------+------+-----+-------------------+----------------+
第一个例子用来查pdate列有多少个不同值
1 单查询:
desc select count(distinct(pdate)) as n from paper where paperid=1;
+----+-------------+-------+------+-----------------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | paper | ref | paperid,paperid_pdate | paperid_pdate | 4 | const | 5107 | Using index |
+----+-------------+-------+------+-----------------------+---------------+---------+-------+------+-------------+
2 复合查询:
desc select count(1) from (select distinct(pdate) as n from paper where paperid=1) t;
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+---------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | paper | range | paperid,paperid_pdate | pdate_paperid | 7 | NULL | 6 | Using where; Using index for group-by |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+---------------------------------------+请问1与2效率那个高?第2个例子,用来列出各个paperid对应的最大的pdate,但是需要join另一个表
1 简单连接
desc select a.name,t.paperid,max(t.pdate) as date from paper t left join paper_admin a on a.id=t.paperid where a.area='广东';
+----+-------------+-------+------+-----------------------+---------------+---------+----------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+---------------+---------+----------+------+-------------+
| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where |
| 1 | SIMPLE | t | ref | paperid,paperid_pdate | paperid_pdate | 4 | wap.a.id | 871 | Using index |
+----+-------------+-------+------+-----------------------+---------------+---------+----------+------+-------------+
2 复合连接
desc select a.name,t.* from (select a.paperid,max(a.pdate) as date from paper a group by a.paperid)t left join paper_admin a on a.id=t.paperid where a.area='广东'
+----+-------------+------------+--------+---------------+---------------+---------+-----------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------------+---------+-----------+------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 6 | |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | t.paperid | 1 | Using where |
| 2 | DERIVED | a | range | NULL | paperid_pdate | 4 | NULL | 8 | Using index for group-by |
+----+-------------+------------+--------+---------------+---------------+---------+-----------+------+--------------------------+另附表结构如下(其中表paper_admin大致不会超过100行,而paper表的数据则会随时间不断增多):
mysql> desc paper;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| paperid | int(11) | NO | MUL | 0 | |
| pdate | date | NO | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+
mysql> desc paper_admin;
+----------+---------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| area | varchar(30) | NO | | | |
+----------+---------------+------+-----+-------------------+----------------+
1,如在PAPERID上建立索引,速度相差不是很大,没有索引,估计没有子查询的快一些
我说的索引,是在数据库中表的索引,建立合适索引在查询时速度应该快一些,
特别是在连接字段上,OR WHERE 字段上更为明现。