SQL性能优化
Customer表定义如下
CREATE TABLE `test01` (
custid` int(10) NOT NULL,
custname` varchar(100) NOT NULL,
date` datetime default NULL,
money` int(10) default NULL,
PRIMARY KEY (`custid`),
KEY `index_test01_custname` (`custname`),
KEY `index_test01_custname_union` (`money`,` date`,`custname`,),
)
secondinfo 表定义如下
CREATE TABLE `test02` (
secid` int(10) NOT NULL,
firstid` int(10) NOT NULL,
custid` int(10) default NULL,
PRIMARY KEY (`secid`),
KEY `Index_test02_custid` (`custid `)
)
下列SQL执行速度比较慢，请分析原因并做优化：
1) SELECT * FROM test01 WHERE substring(custname,1,4)='beizhi';
2) SELECT * FROM test01 WHERE money/30<1000;
3) SELECT * FROM test01 WHERE custname=3721;
4) SELECT * FROM test02 s WHERE s.custid NOT IN (SELECT c.custid FROM test01 c);
5) select * from test01 where money<1000
union
select * from test01 where date >’20150101’;
