MySQL SQL优化小结

优化是一个综合复杂的问题，涉及到硬软件平台。这里仅就MySQL5.0 SQL 查询语句方面作个小结。先举个例子：

现在表register(PRIMARY KEY regId),service(PRIMARY KEY servId, index serviceId )和subscribe(PRIMARY KEY subId,FOREIGN KEY regId/servId)，且前两表跟后张表均为一对多关联，假设有如下表格结构：

CREATE TABLE `subscribe` (
  `subId` int(10) unsigned NOT NULL auto_increment,
  `subcribeTime` datetime NOT NULL,
  `expireTime` datetime NOT NULL,
  `cancelTime` datetime default NULL,
  `paymoney` double NOT NULL,
  `paymentTime` datetime NOT NULL,
  `payWay` tinyint(3) unsigned NOT NULL,
  `subcribeStatus` tinyint(3) unsigned NOT NULL,
  `paymentStatus` tinyint(3) unsigned NOT NULL,
  `createTime` datetime NOT NULL,
  `regId` int(10) default NULL,
  `servId` int(10) default NULL,
  PRIMARY KEY  (`subId`),
  KEY `FK_REGID` (`regId`),
  KEY `FK_SERVID` (`servId`),
  CONSTRAINT `FK_REGID` FOREIGN KEY (`regId`) REFERENCES `register` (`regId`) 
ON DELETE CASCADE,
  CONSTRAINT `FK_SERVID` FOREIGN KEY (`servId`) REFERENCES `service` (`servId`) 
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在一业务根据条件subscribe.subcribeStatus=1 或 >=3 AND subscribe.expireTime <= now() 要取得subscribe.subId,register.phoneNum,service.serviceId 三个字段信息列表。测试中三表中各有三条数据具结果亦为3，初始sql 语句及执行结果如下：

这是一条最容易想到的SQL 语句，虽然优化器默认采用type 快速的index 和eq_ref 类型，但s 和u 的ref 均为NULL 意味着将全表扫描，并 产生一个笛卡尔乘积。对于这个实例由于各表都有3行数据，即扫描3*3*1=9 行。如果表中数据是99999,那么将是99999*99999*1=? ，你可以想象它将花多长时间……，如果是关键业务这将是一场恶梦。 那有没什么办法尽最大可能地减少这个笛卡尔乘积呢？先看看优化器对上面最终执行的语句：

可以看到优化器将使用内连接来执行这个语句，这将是一个考虑的优化点，再看看这有许多WHERE 条件，这可不可以做反应优化呢？

分析一下，可以看到这些WHERE 条件中起实质限定作用的 均与subscribe 这张表有关 ，那就从这开始吧， u.subcribeStatus>=3 OR u.subcribeStatus=1 这个语句看起来有点不顺眼 (可能是因为有个OR，呵呵 ~)，既然1到3中只2除外，那有没可能去掉这个OR 呢？ 看一下业务需求，原来subscribeStatus 只有1至4的值，很明显这个写法不妥。 可以改为u.subcribeStatus <>2 其它条件看起来没什么问题。再来看看联接查询方面的。 既然所有限定条件都是u 表的，那么自然想到查询应该从u 表开始搜索，这可以使用左/右联接，看个人爱好。 看一下最终语句的执行结果：

可看到笛卡尔积变成了：3*1*1=3 ，相对前者多了个ref 引用，虽然已经很“完美”了，但不可避免有个表type=all 意味着将根据条件进行全表扫描。再看看优化器的执行方式：

很显然，它按我们优化方向执行，先从u 开始再联接s /r (这两次序无所谓)查询。就这个示例，我们也只能优化到这里，看看笛卡积，效果还是相当不错的，少了一个数量级的扫描。根据SHOW WARNINGS 最终SQL 为(ON 条件中也可以不用括号)：

SELECT u.subId, r.phoneNum, s.serviceId FROM subscribe as u LEFT JOIN service as s
 ON (s.servId=u.servId) LEFT JOIN register as r ON (r.regId=u.regId)
 WHERE u.subcribeStatus<>=? AND u.expireTime <= now() LIMIT ?,?

 

通过上面示例，可以知道MySQL 提供的一些工具非常实用，下面介绍一下刚才用过的：

1. GRANT 语句尽量简单，以降低不必要的许可检查开销;
2. 如果问题与具体MYSQL表达式或函数有关，可使用BENCHMARK() 函数执行定时测试，语法：BENCHMARK(loop_count,expression) ;
3. EXPLAIN可作为DESCRIBE的同义词，它将解释MySQL如何处理SELECT语句，提供有关表如何联接和联接的次序信息，这对优化SQL 语句特别是级联查询时特别有用。

4. SHOW WARNINGS 可以浏览EXTENDED 产生的附加信息，输出优化器重写并优化后的SELECT语句，可能还包括优化过程的其它注解。
EXPLAIN 语法及等价SQL 语句如下：
EXPLAIN tbl_name / EXPLAIN [EXTENDED] SELECT select_options
EXPLAIN tbl_name = DESCRIBE tbl_name = SHOW COLUMNS FROM tbl_name

          何时须为表加入索引以得到更快的SELECT
         /
主要用途:
         \
          知道优化器是否以一个最佳次序联接表

 

 

WHERE子句优化

1. MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引,所以在类型相同时尽量保持相同尺寸(如对于固定大小的使用char类型),如果数据经常修改的话CHAR要优于VARCHAR。因为定长的行并不会有存储残片。对于非常短的列，CHAR要比VARCHAR高效。

 

2.去除不必要的括号

e.g. ((a AND b) AND c OR (((a AND b) AND (c AND d))))
   -> (a AND b AND c) OR (a AND b AND c AND d)

 

3. 常量重叠

e.g. (a < b AND b = c) AND a = 5
   -> b > 5 AND b = c AND a = 5

 

4. 去除常量条件(由于常量重叠需要)：

 e.g. (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
   -> B=5 OR B=6

 

5. 如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。

 

6. 对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。

 

7. 所有常数的表在查询中比其它表先读出。常数表为：
   空表或只有1行的表。
   与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。

e.g.下列的所有表用作常数表：
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id

 

 

待续。。。。