(1)负向条件查询不能使用索引select * from user where status!=0 ,not in/not exists都不是好习惯可以优化为in查询:select * from user where status in(1,2)
数据准备:
CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `status` int(1) DEFAULT NULL, KEY `index_status` (`status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `user` VALUES ('1', '张三', '12', '0');INSERT INTO `user` VALUES ('2', '李四', '23', '1');INSERT INTO `user` VALUES ('3', '王五', '34', '2');//在status字段添加索引
测试结果:
(2)前导模糊查询不能使用索引select * from user where name like '%张三' 而非前导模糊查询则可以:select * from user where name like '张三%'
数据准备:
CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `status` int(1) DEFAULT NULL, KEY `index_name` (`name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `user` VALUES ('1', '张三', '12', '0');INSERT INTO `user` VALUES ('2', '李四', '23', '1');INSERT INTO `user` VALUES ('3', '王五', '34', '2');//在name字段添加索引
测试结果:
(3)数据区分度不大的字段不宜使用索引select * from user where sex=1原因:性别只有男,女,每次过滤掉的数据很少,不宜使用索引。经验上能过滤80%数据时可以使用索引
(4)在属性上进行计算不能命中索引select * from user where YEAR(birth_date) < = '2017' 即使date上建立了索引,也会全表扫描,可优化为值计算:select * from user where birth_date< = '2000-01-01'
数据准备
CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `name` varchar(32) DEFAULT NULL, `sex` int(1) DEFAULT NULL, `birth_date` date DEFAULT NULL, KEY `index_birth_date` (`birth_date`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `user` VALUES ('1', '张三', '0', '2010-02-03');INSERT INTO `user` VALUES ('2', '李四', '1', '2000-02-02');INSERT INTO `user` VALUES ('3', '王五', '1', '1998-09-09');//在birth_date上添加索引
测试结果:
(5)如果业务大部分是单条查询,使用Hash索引性能更好,例如用户表select * from user where id=1或select * from user where name='张三',原因:B-Tree索引的时间复杂度是O(log(n))Hash索引的时间复杂度是O(1)
(6)允许为null的列,查询有潜在大坑,单列索引不存null值,复合索引不存全为null的值,如果列允许为null,可能会得到"不符合预期”结果,所以请使用not null约束以及默认值
(7)复合索引最左前缀,并不是值SQL语句的where顺序要和复合索引一致,用户表建立了(login_name, passwd)的复合索引,select * from user where login_name=? and passwd=? ; select * from user where passwd=? and login_name=?; select * from user where login_name=? 都能命中索引,他们都满足复合索引最左前缀。 然而select * from user where passwd=?不能命中索引,不满足复合索引最左前缀
数据准备
CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `login_name` varchar(32) DEFAULT NULL, `password` varchar(32) DEFAULT NULL, KEY `index_loginname_password` (`login_name`,`password`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;//在login_name,password列建立复合索引,KEY `index_loginname_password` (`login_name`,`password`)
测试结果:
(8)如果明确知道只有一条结果返回,limit 1能够提高效率select * from user where login_name=? 可以优化为:select * from user where login_name=? limit 1原因:你知道只有一条结果,但数据库并不知道,明确告诉它,让它主动停止游标移动
(9)强制类型转换会全表扫描select * from user where phone_no=13800001234你以为会命中phone_no索引么?那就错了,因为phone_no是varchar(11) ,所以13800001234会强制转换类型,如果phone_no为bigint(20),那么phone_no=13800001234和phone_no='13800001234'都会走索引
数据准备
CREATE TABLE `user` ( `id` bigint(20) NOT NULL, `phone_no` varchar(11) DEFAULT NULL, KEY `index_phone_no` (`phone_no`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;//在phone_no列加索引,KEY `index_phone_no` (`phone_no`)
测试结果: