索引的好处:
1 可以帮助服务器避免排序。
B-Tree索引在如下的情况中会生效
测试表结构:
CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(255) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`nick_name` varchar(255) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `last_name_first_name_createtime_index` (`last_name`,`first_name`,`createtime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
1 全值匹配–联合索引在查询中均用到了,
SELECT *
FROM people
WHERE
last_name="tang"
AND first_name="xi"
AND createtime='2019-01-09 00:00:00'
2 匹配最左前缀–使用组合索引的第一列
EXPLAIN
SELECT *
FROM people
WHERE
last_name="tang"
3 匹配列前缀–只匹配某一列的开头部分,
EXPLAIN
SELECT *
FROM people
WHERE
last_name="t%"
4 匹配范围值
EXPLAIN
SELECT *
FROM people
WHERE
last_name>"a"
AND last_name<"c"
5 精确匹配某一列并范围匹配另外一列
EXPLAIN
SELECT *
FROM people
WHERE
last_name>"b"
AND createtime>'2019-01-09 00:00:00'
AND createtime<'2020-01-09 00:00:00'
多列索引:
当服务器出现and查询时,有必要创建组合索引。
一般将选择性高的放在最左边,同时考虑部分列是否能筛除掉大部分数据,则也可以考虑将该列放在最左边。
T-Tree的一些规则
1 如果不是按照索引定义的顺序来查询,则无法使用到索引。
2 不能跳过索引中间的某些列。
3 如果某个列用到范围查询,则其右边都无法用到索引进行优化查找。
Hash索引:
哈希索引,将会把指定列的数据通过hash算法计算出一个哈希码,将hash码和数据地址存在hash索引中。
hash索引的优缺点
1 hash索引容易出现hash碰撞,如果出现hash碰撞, 则会以列表的形式存储多行记录的地址。
2 hash索引的值不是按照索引值排序的,无法用于排序(疑问点)。
3 hash索引不支持匹配查询,因为hash索引总是使用列的值来进行计算hash值,所以hash只支持比较查询
摘自《高性能Mysql》