首页 » Mysql » 正文

索引珠讥

索引是个永恒的话题,有存储有查询就得有索引,无论是关系数据库中的一条SQL查询,还是搜索引擎中的一个关键字查询,还是文件系统中的文件查询,都离不开索引的实现,可见索引在存储以及整个计算机软件行业的重要性。

本文简单介绍那些常见索引及其使用场景等。更多内容参考《高性能MySQL》第五章 创建高性能索引。

1. 按实现分类

Hash索引:高效,只能应用在等于查询条件上,mysql有些引擎内部支持内存hash索引,oracle支持hash join
B*树索引:RDBMS中的主要索引类型,B+树是二插树的变种,主要解决随机IO的问题,可以控制一次索引查找的随机IO在3或者4次
位图索引: 增加空间利用率,insert/update容易死锁,适合有大量重复值的列(选择性低),适合OLAP系统

2. 按列分类

单列索引:在一个列上建立索引,单列独享
组合索引:在多个列上建立索引,需要符合最左前缀原理,范围索引必须是最后一个列

3. 分区索引

分区表的全局索引:在分区表上建立全局的索引,一般不用,索引空间太大
分区表的局部索引:在每个区上建立单独的本地索引

4. 按存储分类

聚簇索引:数据和索引存放在一起,数据存放在主键索引里,辅助索引记录的是主键的ID
非聚簇索引:数据和索引分开存放,索引记录的是记录的逻辑或者物理地址

5. 其他索引

压缩索引:对key值中相同的前缀进行压缩
覆盖索引:只适用索引查询,需要的数据都在索引里,不需要访问数据文件
逆向索引:减少索引页面的争用
函数索引:通过一个函数映射建立索引
前缀索引:符合最左前缀原理
重复索引:有的数据库允许建立重复的索引,浪费资源,不报错,需要自己多加注意
无用索引:所有索引永远未被使用
倒排索引:搜索专用的索引,按照词来索引文章,和用文章索引词相反,所以叫做倒排索引

6. tips

1. create index是加表级别的读锁的
2. 范围索引between and, > < >= <=, in等会使用索引
3. 有些引擎in语句会转化成等号的语句 4. 如果你有一个页面要查询,标准是性别,喜好,年龄,可是用户数据每个选项都不是强制的,你怎么建立索引呢?

方法1:可以针对不同情况建立多个索引,但是索引开销很大
方法2:也可以建立一个索引create index on (性别,喜好,年龄), 如果没有输入性别,则用sql 性别 in (‘男’,’女’) and 喜好 = ‘篮球’ and 年龄 > 30

4. Oracle的R,W,Row-R, Row-W, 哪些之间互斥,哪些之间共享呢?

Row-R 只和 W 互斥
Row-X 只和 R,W互斥
Row-R和Row-W, Row-W和Row-W, Row-R和Row-R之间都是共享的
Row-R和R不互斥