【话题讨论】研究 MySQL 5.6 查询问题（获奖名单已公布-2013-6-19）

arron刘

获奖名单已公布，详情请看：http://bbs.chinaunix.net/thread-4086812-1-1.html

查询是数据库系统中最基本也是最常用的一种操作,因此查询是否具有较快的执行速度,已成为数据库用户和设计者极其关心的问题。
MySQL 5.6中，Oracle对子查询进行了优化处理。其中涉及到将一个子查询转变为一个半连接操作，然后将它作为另外一个连接操作来加以评估。优化器能够进行识别，即一条IN语句要求子查询从子查询表只返回一个值。在这些场景下，查询是以半连接来执行的，该操作仅返回子查询中每条记录的一个实例，而子查询则是通过记录与外部查询进行匹配的。

话题讨论：
1、你在使用新版本查询中曾遇见哪些问题？是怎么解决的？
2、你一般如何对你的数据库查询进行优化？


讨论时间：2013.5.20--2013.6.3

讨论奖励:活动结束后将会抽取5名会员赠送《锋利的SQL》一书。



　

heaven_sky

还没用过呢

laputa73

这个太底层了.
以我们用oracle的经验.表链接多数情况都比子查询快
因此要求优先使用表链接.

chenyx

还没用过5.6版本呢.
从网上的评测来看,5.6和5.5相比,确实进步很大,有很多项目性能相差1倍,Oracle确实功力不凡啊.

jieforest

在回答问题前我先对MySQL 5.6做一番说明。
MySQL 5.6与之前的5.5版或5.1版是兼容的，也就是说，在5.5版或5.1版建立的数据库，可以轻松迁移到5.6版。今年4月份的时候，我在Windows Server 2008系统下部署了MySQL 5.6，把原先的数据库平滑地迁移到5.6版之上，没有任何问题。我安装的是mysql-5.6.10-winx64.zip，大约216MB，值得一提的是，压缩包里包含了MySQL Server、MySQL Workbench、MySQL Notifier、MySQL for Excel。

1、你在使用新版本查询中曾遇见哪些问题？是怎么解决的？
MySQL 5.6所做的改进有很多，我在写查询脚本是遇到过不少新特性。
1）MySQL 5.6对于只读性能做了很大的优化。比如：
SET autocommit = 1;
SELECT testtable1.`name` FROM testtable1 WHERE id=123;
可以改成这样：
SET autocommit = 0;
START TRANSACTION READ ONLY;
SELECT testtable1.`name` FROM testtable1 WHERE id=123;
COMMIT;

2）MySQL 5.6支持全文本检索。我们可以这样创建表：
create table movies(
  `id` int unsigned auto_increment primary key,
  `moviename` varchar(64),
  `intro` varchar(4000),
  `play_url` varchar(64),
  fulltext(`intro`)
) engine=innodb;
以后，在搜索intro中的字符串时，效率会高得多。

2、你一般如何对你的数据库查询进行优化？
基本上，有以下几点：
1）使用临时表来加速查询；
2）合理地使用索引；
3）对于数据记录很大的表，对查询进行优化时应尽量避免全表扫描；
4）尽量避免大事务操作，提高系统并发能力；
5）尽量避免向客户端返回大数据量，若数据量过大，应该考虑客户端实现分页查询。
……

ylky_2000

首先可以明确：5.5和5.4版本是可以平滑升级的。
简单的进行测试查询如下：
1、分别在5.5和5.6版本中做如下操作：
CREATE TABLE t1(id int, PRIMARY KEY (id)) engine=innodb;
INSERT INTO t1(id) VALUES(1),(2),(3),(4),(5),(6),(7),(,(9),(10);

CREATE TABLE t2(rid int, id int, PRIMARY KEY (rid, id)) engine=innodb;
INSERT INTO t2(rid, id) VALUES(547, 1),(547, 2),(547, 5),(547, ,(203, 2),(203, 4),(203, ;
2、用explain查看执行结果：分开两个版本查询

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.30-log |
+------------+
1 row in set (0.01 sec)

mysql> explain SELECT * FROM t1 INNER JOIN t2 WHERE rid = 203 AND t2.id > 2 AND t2.id IN (SELECT t1.id FROM t1 INNER JOIN t2 USING(id) WHERE rid = 547);
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                          |
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
|  1 | PRIMARY            | t2    | range  | PRIMARY       | PRIMARY | 8       | NULL       |    2 | Using where; Using index       |
|  1 | PRIMARY            | t1    | index  | NULL          | PRIMARY | 4       | NULL       |   10 | Using index; Using join buffer |
|  2 | DEPENDENT SUBQUERY | t2    | eq_ref | PRIMARY       | PRIMARY | 8       | const,func |    1 | Using index                    |
|  2 | DEPENDENT SUBQUERY | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | func       |    1 | Using where; Using index       |
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
4 rows in set (0.01 sec)


mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.10    |
+-----------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM t1 INNER JOIN t2 WHERE rid = 203 AND t2.id > 2 AND t2.id IN (SELECT t1.id FROM t1 INNER JOIN t2 USING(id) WHERE rid = 547);
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                                              |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | ref    | PRIMARY       | PRIMARY | 4       | const            |    1 | Using where; Using index                           |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 8       | const,test.t2.id |    1 | Using index                                        |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.id       |    1 | Using index                                        |
|  1 | SIMPLE      | t1    | index  | NULL          | PRIMARY | 4       | NULL             |   10 | Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------------+
4 rows in set (0.00 sec)
3、比较执行的策略
从执行策略上讲，5.6强于5.5。在实际的环境中，5.6版本的执行速度要比5.5的慢。

mysql最忌讳用in一个子查询  更改成表连接，一般in语句都可以更改为join。



二、查询优化办法还是老办法
1、优化数据类型 避免使用null、尽可能使用更小的字段、
2、对字符转换的过程小心，utf-8的时候是否需要多字节，这个因为多字节需要更多的空间；
3、优化count(my_col)和count(*) ，使用MyISAM表，没有where子句的情况下使用count(*)速度快，主要是因为行数量统计非常精确，所以MySQL不会一行一行地去找，如my_col列没有空值，那么和前面说的情况会一样，即count(my_col)速度也会很快。如果有where子句时使用count( )，基本上就无法进行更多优化了，在where子句中超出了明显的索引列，对于复杂的where子句，只有使用覆盖索引才有用。
4、优化子查询 经常将子查询转换为连接查询。以上例子有这个方面的说明。

wonghoifung

之前在china-pub买书时送了这本。。。

枫影谁用了

期待还是子查询的智能优化啦，
不然用临时表代替挺浪费时间的。

方兆国

chenyx 发表于 2013-05-20 21:58
还没用过5.6版本呢.
从网上的评测来看,5.6和5.5相比,确实进步很大,有很多项目性能相差1倍,Oracle确实功力不 ...

有些地方却说,MySQL进入Oracle后会受到Oracle的打压

方兆国

ylky_2000 发表于 2013-05-21 09:13
首先可以明确：5.5和5.4版本是可以平滑升级的。
简单的进行测试查询如下：
1、分别在5.5和5.6版本中做如下 ...

3、优化count(my_col)和count(*) ，使用MyISAM表，没有where子句的情况下使用count(*)速度快，主要是因为行数量统计非常精确，所以MySQL不会一行一行地去找，如my_col列没有空值，那么和前面说的情况会一样，即count(my_col)速度也会很快。如果有where子句时使用count( )，基本上就无法进行更多优化了，在where子句中超出了明显的索引列，对于复杂的where子句，只有使用覆盖索引才有用。

聚集函数不能用在where之后,应该是Having 吧