B-Tree索引的特征
B-Tree索引可以使用比较表达式,包括 =, >, >=, <, <=, BETWEEN等表达式,还可以使用 like表达式(1、不能以 % 等通配符开始,2、而且必须是常量字符串)。 下面的两条sql会使用索引
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
下列的sql不会使用索引
-- 以通配符开始的 SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; -- 不是常量 SELECT * FROM tbl_name WHERE key_col LIKE other_col;
如果一个列被索引,但是搜索条件是null,也会使用索引
如果想要使用一个索引,在where语句中查询条件必须是and连接的,而且是符合索引前缀的。下列sql使用了索引
WHERE index_part1=1 AND index_part2=2 AND other_column=3 -- 查询条件会被优化为index = 1 OR index = 2 WHERE index=1 OR A=10 AND index=2 -- 优化为 index_part1='hello' WHERE index_part1='hello' AND index_part3=5 -- 会使用index1 不会使用index2和index3 WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
下列的sql不会使用索引
-- index_part1没有使用 WHERE index_part2=1 AND index_part3=2 -- 索引的两个部分都没有使用 WHERE index=1 OR A=10 -- 没有索引匹配到所有的记录 WHERE index_part1=1 OR index_part2=10
有时候MySQL并不会使用索引,即使该索引有效的。例如:当优化引擎认为使用索引会需要MySQL扫描大半个表的时候,表扫描只需很少的查找,反而更快。如果是sql中有limit语句仅需检索出部分记录,这种情况下MySQL会使用索引,因为这样能够更快地查找到需要的记录并返回。
Hash索引特征
hash索引支持 等于 比较操作符(= 、 <=>),但是很快。不支持这种 < <= > >= 等这种比较操作符的范围查询。如果你的系统使用这种 key-value 形式的查询,可以使用hash索引。
优化器不支持使用hash索引排序
使用hash索引,MySQL不能估出两个值之间有多少记录(这一点很重要,范围优化器会参考这个结果决定使用哪个索引)。在使用 MyISAM 和I nnoDB 存储引擎转换为 MEMORY引擎的时候要特别注意
仅支持使用索引的全值来查询一条记录。(在B-tree索引中,任何符合最左索引前缀的规则都可以用来查找记录)