引言
常用索引,莫过于BTREE和HASH了。
实例解析
下面以InnoDB引擎为例,进行讲解:
准备工作
创建表
创建两张表,一张设置BTREE索引,一张设置HASH索引;两类索引都基于多列的情况下:
BTREE索引
CREATE TABLE `test2` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`sex` tinyint(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uidx` (`name`(191),`sex`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
HASH索引
CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`sex` tinyint(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uidx` (`name`(191),`sex`,`age`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
借助explain分析工具,进行实操验证
=号操作符
注:分析结果主要看下图中key列中的值,如果用到了所以则会列出索引名字,否则为null。
成功
验证结果,都使用到了索引
失败
验证结果,都未使用到了索引;
分析原因:列未使用常量所致,亦即数据类型不对所致;
接下来又对>,>=,<,<=和BETWEEN等操作符做了验证,结果也都同=操作符,因此就不一一罗列了。
like操作符
成功(普通值)
验证结果,都使用到了索引
成功(%后置)
验证结果,都使用到了索引
失败(%前置)
验证结果,都未使用到了索引;
分析原因:索引不支持%前置,或者解析器不认为%前置为常量;
or操作符
失败
验证结果,都未使用到了索引;
分析原因:多列索引应用前提是AND操作符;
and操作符
成功(两列的情况)
验证结果,都使用到了索引,并且使用了最左索引支持,即两列均用到了!
成功(三列的情况)
验证结果,都使用到了索引,并且使用了全部索引列!
注:分析结果由上图中ref列所得出的结论!
结论
上述只是引出如何验证你创建的索引是否被使用到,更多地情况可以借助上述中explain工具进行验证!