1.之前看视频呢的时候,里面提到一道索引题目:假设某个表有一个联合索引(c1,c2,c3,c4)-只能使用该联合索引的c1,c2,c3部分
a.where c1=x and c2=x and c4>x and c3=x(c1,c2,c3,c4都用到了索引,c4是范围索引查找)
b.where c1=x and c2=x and c4=x order by c3(c1,c2用于查找,c3用于排序)
c.where c1=x and c4=x group by c3,c2(只用到c1)
d.where c1=? and c5=? order by c2,c3(c1用于查找,c2, c3用于排序)
e.where c1=? and c2=? and c5=? order by c3,c2(c1,c2用于查找,c3用于排序)
2.建表语句如下,插入了50万条数据,如果表数据太少,mysql会优化成直接做全表扫描,而不需要使用索引:
CREATE TABLE `MNG_ROLE` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) NOT NULL,
`CREATE_DATE` char(8) NOT NULL,
`CREATE_TIME` char(6) DEFAULT NULL,
`UUID` char(32) NOT NULL,
`REMARK` varchar(3000) NOT NULL DEFAULT '',
`RESERVER` varchar(3000) NOT NULL DEFAULT 'RESERVER',
PRIMARY KEY (`ID`),
UNIQUE KEY `UK_UUID` (`UUID`) USING BTREE,
KEY `INDEX_NAME_CREATE_DATE_TIME_REMARK` (`NAME`,`CREATE_DATE`,`CREATE_TIME`,`REMARK`(255)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=510335 DEFAULT CHARSET=utf8
3.语句执行情况:
a.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME='role100' AND CREATE_DATE='20151012