1.案例
有张表名字为table,由3列组成,分别是姓名、性别和婚姻状况,其中性别只有男和女两项,婚姻状况有已婚、未婚、离婚这3项,该表共100w条记录,现在有这样一个查询:
SELECT * FROM table WHERE gender='男' AND marital='未婚'
1)不使用索引
在不使用索引时,数据库只能一行行扫描所有记录,然后判断该记录是否满足查询条件。
2)使用B树索引
对于性别,可取值的范围只有‘男’、‘女’,并且男和女可能各占该表50%的数据,这是添加B树索引还是要取出一半的数据,因此完全没有必要。
相反,如果某个字段的取值范围很广,几乎没有重复,比如身份证号(此处感觉不合理,身份证号相当于随机的,插入时候必须调整B+树节点结构,所以最好使用自增字段作为B+树索引)。事实上,当记录重复率很高(即选择性很低时),即使添加了B树索引,数据库也不会使用B树索引,而很有可能还是一行行全部扫描
2.位图索引
1)原理
1>根据某列中值的数量来确定使用的位图的数量(这也就是为什么列的可选值太多不适合建立位图索引)
如性别列只有‘男’、‘女’2个值,那么就使用2个位图,第一个代表男、第二个代表女
2>根据所有记录的总数来确定位图的位数
如上表中只显示了5条记录,那么位图就有5位
3>如果只有一个列有位图索引,那么查询的时候就查询查询值对应位图即可;如果有多个列有位图索引,那么可以先将所有符合标准的位图相与,这样就可以加快查询速度
2)实例
对于上表和查询语句,用户查询语句的列的基数非常小,只有几个固定值,如性别、婚姻状况等。要为这些基数值比较小的列建立索引,就需要建立位图索引。
对于性别这个列,位图索引生成两个位图,第一个表示男,如果某条记录的性别列为男,则设置为1,同理女对应的位图也是这样
对于婚姻状况这一列,有3个位图:
当我们使用查询语句
SELECT * FROM table WHERE gender='男' AND marital='未婚'
首先取出男对应的位图10100,然后取出未婚对应的位图00101,这两个做and操作,会生成新位图00100,可以发现第三位为1,表示该表的第三行数据就是我们查询的结果。
3.位图索引适用条件
1)因为列的值影响到位图的数量,所以位图索引只适合只有几个固定值的列,如性别、婚姻状况、行政区等,而如身份证这种不是和用位图索引
2)适合静态数据,不是和频繁更新的数据举例:有一个字段login,记录各个用户是否登陆,如果使用位图索引,假设用户A正在update login的值, 比如update table set login=1 where rowid=100,但是还没有commit,而此时用户B也使用update更新自己的登陆状态,update table set login=1 where rowid = 12,这时用户B会发现怎么也更新不了,需要等待用户A commit
原因:用户A更新了login的值为1,会导致所有的login为1的位图发生改变,因此数据库会将login=1的所有行锁定,只有当commit之后才会解锁。