使用一个多表查询的例子:
首先创建表:
CREATE DATABASE IF NOT EXISTS mysql_test_bruce DEFAULT CHARACTER SET UTF8;CREATE TABLE `a`( `id` INT(11) NOT NULL AUTO_INCREMENT, `seller_id` BIGINT(20) DEFAULT NULL, `seller_name` VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8_BIN DEFAULT NULL, `gmt_create` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8;CREATE TABLE `b`( `id` INT(11) NOT NULL AUTO_INCREMENT, `seller_name` VARCHAR(100) DEFAULT NULL, `user_id` VARCHAR(50) DEFAULT NULL, `user_name` VARCHAR(50) DEFAULT NULL, `sales` BIGINT(20) DEFAULT NULL, `gmt_create` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8;CREATE TABLE `c`( `id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` VARCHAR(50) DEFAULT NULL, `order_id` VARCHAR(100) DEFAULT NULL, `state` BIGINT(20) DEFAULT NULL, `gmt_create` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8;
查询语句如下:
explain SELECT a.seller_id, a.seller_name, b.user_name, c.state FROM a,b,c WHERE
a.seller_name=b.seller_name
AND b.user_id = c.user_id AND c.user_id=17
AND a.gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(),INTERVAL 600 MINUTE )
ORDER BY a.gmt_create;
查询花费了190ms
查看查询计划,发现都是用了全表扫描,显然是需要优化的。
怎样优化呢?
1.既然是关联查询,那么需要先找到驱动表,小表驱动大表
对3张表分别进行查询,查看那张表得到的数据最少,将其做为驱动表
从结果来看,a和c表都可以作为驱动表,但是因为要使用a表的gmt_create作为查询条件,如果从索引的利用率来说,使用a表作为驱动表更合适
2.建立索引
查询中没有用到索引,所以这里需要先建立索引
查询语句:
SELECT a.seller_id, a.seller_name, b.user_name, c.state FROM a,b,c WHERE
a.seller_name=b.seller_name
AND b.user_id = c.user_id AND c.user_id=17
AND a.gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(),INTERVAL 600 MINUTE )
ORDER BY a.gmt_create;
为了优化排序,给a表的gmt_create字段建立索引
b表的seller_name字段因为要与a表进行关联,所以是必不可少需要建立索引的
c表的user_id字段因为要与b表进行关联,所以也是要建立索引的
再次查询:
可以看到C表使用了user_id索引,但是a、b两表却没有用到索引,还是全表扫描,这是怎么回事呢?
再加一个参数extended去查看更加详细的执行计划:
通过上面执行计划的分析,发现问题如下:
主要是索引字段在使用的时候发生了隐式转换
采用如下解决方案:
再次执行sql语句:索引失效的问题被解决。
以上就是一个完整的sql优化的思路,这种过程很少有人总结,希望能够对大家有帮助。