由于我现在没有大量数据来实测其性能,所以请大家帮我分析一下如果数据量比较多时的情况。
第一个例子用来查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 | | | |
+----------+---------------+------+-----+-------------------+----------------+
解决方案 »
- MySQL:如何快速更新某一列?
- MySQL is running but PID file could not be found failed
- 请各位推荐SQL的书,或者网站
- mysql能不能做延时同步,或者请教一下问题的解决方案。
- MYSQL中如何执行带有变量的查询语句
- 为什么?难道Mysql没有视图吗?
- 字段id为auto_increment,删除id为1的行后,怎么才能使auto_increment不跳过?
- 一个极菜的问题[100分]
- mysql5.5查询表结构时报错~急求解决办法
- mysql主从 主库Position不增加
- 求一MYSQL语句
- 求助使用MySQL-Front出现在这个错误是什么意思
1,如在PAPERID上建立索引,速度相差不是很大,没有索引,估计没有子查询的快一些
我说的索引,是在数据库中表的索引,建立合适索引在查询时速度应该快一些,
特别是在连接字段上,OR WHERE 字段上更为明现。