-
(1) 数据越多时,索引越重要
(2) 如果索引了多列数据,那么列的顺序非常重要 --> 索引的最左前缀原则
(3) 索引在__存储引擎层__实现, 而不是在服务器层
-
MySQL支持的索引类型
(1) BTree索引
(2) Hash索引
(3) RTree索引
-
BTree索引
(1) BTree索引只是一种称呼,实际实现有可能是B+树
(2) 大概的结构
指针1 key1 指针2 key2 ...指针n+1
直到叶子页面才是包含数据的
(3) 索引中保存数据的顺序是按照创建索引的顺序
CREATE TABLE People { first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, dob DATE NOT NULL, gender ENUM('M','F') NOT NULL, INDEX( last_name, first_name, dob) );
此时,在一个key中的结构大概类似于
('Bo', 'Chen', '1994-10-18')
对于每个叶子页面,保存数据记录的顺序也是按照创建索引的顺序排序
('Basinger', 'Viven', '1976-12-08'), ('Basinger', 'Viven', '1979-11-24')
(4) 最左前缀原则:只支持以下几种查询方式
1° 匹配全名
查询中和索引中的所有列匹配,并且顺序也相同
2° 匹配最左前缀
查询中匹配索引中的前N列,并且顺序相同
3° 匹配列前缀
查询中匹配索引第一列的前缀,例如查询last_name中以’B’开头的人
4° 匹配范围值
查询中匹配索引第一列的范围,例如查询last_name中大于Allen小于Barry的人
5° 精确匹配前面的部分并且匹配后面某个范围中的另一部分
例如查询first_name为Allen,last_name为Smith,出生日期大于1970-10-01小于1970-12-31的人
6° 只访问索引的查询
???
(2) 局限性的几种情况
1° 查找必须从索引的最左面一列开始,否则就用不上索引
2° 不能跳过索引中的列查询,此时用上的还是前面连续的列
例如
SELECT * FROM PEOPLE WHERE last_name = 'MARK' AND dob = '1960-01-01';
只用到了索引的第一列
3° 不能访问出现了范围查询后面的列
例如
SELECT * FROM PEOPLE WHERE last_name = 'MARK' AND first_name LIKE 'J%' AND dob = '1960-01-01';
只用到了前两列
-
哈希索引
(1) 原理
对一些列计算一下hash值,每当需要查询的时候,根据待查询的值计算hash值,在hash表中寻找这个值,hash表中存储着hash值和记录的位置指针,然后根据指针找到记录,判断一下是否是符合要求的记录
hash表中是按照hash值排好序的,所以在hash表中找hash值的过程可以使用二分查找很快的搜索到
(2) 局限性
1° 如果建立hash索引时使用了A,B,C三列,那么查询时为了使用hash索引,必须要在WHERE中把这三列都用上(因为要根据散列函数计算hash值必须全用上)
2° hash表中保存的是hash值和记录的位置。如果记录在内存中可以很快定位记录,如果记录在磁盘上定位也需要时间
3° hash索引只支持=、IN这种确定范围查询,部分前缀LIKE、范围查询都是不支持的
4° 如果hash函数选的不好,会导致一个hash值对应好几条记录指针,每个都要判断
(3) 只有Memory引擎__显式__支持hash索引
但是,InnoDB会建立__自适应hash索引__:当一些索引值被频繁访问时,InnoDB会自动在内存中建立hash表
-
MYSQL对使用索引的顺序会优化,例如索引中列的顺序是 a = A and b = B and c = C,但是实际如果使用了 b = B and a = A and c = C 也会优化成正确顺序,所以只要使用了就行,顺序无关紧要
chapter03_架构优化和索引_2_索引基础知识
最新推荐文章于 2024-08-14 04:22:49 发布