索引分类
- hash索引
- b+ tree索引
使用场景
hash索引
主要用于字符串,缺点是无法有序。
如果存在hash冲突,可以设置额外的列解决这个问题。比如下面二列数据:
name|hash_name
---------------
xun | 23432432
ggg | 32432432
hash_name就是一个额外的hash索引列,这个列根据name列,然后使用函数(md5等)计算出来。
查询的时候:
... where name='xun' and hash_name=hash_fun('xun')
b+ tree
结构如下
使用这种数据结果而不使用其他的比如(红黑树)是为了降低磁盘io提供性能
主要用在数字性的列中,因为可以范围查询,排序等等特性。
索引失效
- 情况一
比如列a列b列c共同创建列一个联合索引,如果查询的使用单独使用a列,那么(b、c)索引无效 - 情况二
如果列a是一个索引列,可是查询的时候写出如下的表达式,索引无效
... where a+1=3
- 情况三
查询优器的优化
比如
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
索引的代价
创建索引的好处
- 帮助用户提高查询速度
- 利用索引的唯一性来控制记录的唯一性
- 可以加速表与表之间的连接
- 降低查询中分组和排序的时间
创建索引的坏处
- 存储索引占用磁盘空间(可以忽略)
- 执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护
索引是万能药吗
对于非常小的表,大部分情况下全表扫描更高效,中到大型的表,索引可以有效提高效率。
对于大型的表,索引的代价就提高高了。对于大型的,就需要采取分区、或者记录源信息等技术了。
高新性能的索引策略
前缀索引
比如下面的列
city
-----
SanXiSgass
Tousfdsaes
Ftx23432ws
...
明显看到,city这一列是很长的,那么我们把它设置成索引之外,还可以更进一步,设置成前缀索引。
比如
alter table xxx and key left(city,4)
有点是优化列索引长度(这个长度需要合理设置,减少重复),缺点是mysql无法对这个进行group or order
多列索引
比如下面情况
show tables;
CREAE TABLE t(
c1 INT,
c2 INT,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
)
上面把t表里面的列都搞成索引了。
这种情况下,如果查询不是
where c1=1 and c2=2 and=c3=3
这种联合AND都查询的话,而是OR,那么这种效率比较低,反之可以提高效率。
聚镞索引
表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致
对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。
非镞索引
非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。