索引是在搜索引擎层实现的,不是在数据库服务层实现的
1.索引主要分为 2种 B-tree索引和哈希索引
B-tree索引
(1)B-tree索引的特点
B-tree索引 能够加快数据的查询
B-tree索引更适合进行范围查询(原因B-tree索引是顺序存储的)
(2)什么情况下可以使用B-tree索引
全值匹配
例如name=“zhansan”
匹配最左前缀的查询
name age的联合索引 name=“zhansan”还是会用到联合索引的,即符合条件的联合索引的 一列被用到 第2列是无法用到联合索引的
匹配列前缀查询
例如 name like “zhang%”
匹配范围值的查询
age>18
精确匹配左前列并范围匹配另外一列
只访问索引的查询
B-tree不仅可以使用在查询中,还可以使用在order by中
(3)B-tree 索引的使用限制
如果不是按照所用的最左列开始查找的,则无法使用索引
使用索引时不能跳过索引中的左端的列(不是最左端的列)索引a b c ,跳过了b的索引 则 只能使用到a的索引,c的索引无法使用
Not in 和<> 操作无法使用索引
如果查询中有某个列的范围查询,则其右边所有的列都无法使用索引
Hash索引
(1)Hash索引的特点
Hash索引是基于Hash表实现的,只有查询条件的精准匹配
Hash索引中的所有列时,才能使用hash索引
Hash索引的所有列,存储引擎都会为每一行计算一个Hash码,Hash中存储的就是Hash码
(2)hash索引的限制
Hash索引必须进行2次查询 找到对应的行,再对行经进行一次读取
hash 索引无法用于排序
hash索引不支持部分索引查询也不支持范围查询 如果产生了大量的冲突影响性能,比如重复很高的性别不适合使用哈希索引,例如身份证号码就比较适合
hash索引中hash码的计算可能存在hash冲突
索引的好处
索引大大减少存储引擎所需要扫描的数据量 存储引擎是以页为单位的,一页存储的数据越多查询越快,通常1页是16k,索引大小远比1行的大小小的多
索引可以帮助我们进行排序避免使用临时表
索引可以把随机的IO改成顺序的IO B-tree索引的键值是按顺序存放的 数据行是随机分布的
索引是不是越多就越好???
案例 开发想通过增加索引来增加数据的导入速度 最好的方法是把所有的索引 全部删除掉
索引会增加写操作的成本 由于对数据库进行 插入 更新删除操作时,同时要对索引进行维护,有一些DB使用了插入缓存,将多次插入操作狐缓存成1次
太多的索引会增加查询优化器的选择时间
索引优化的策略
索引列上,不能使用表达式或者函数
使用多个列作为条件查询时使用多列索引和多个单列索引更好
让选择性更强的索引放在前面
索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,查询效率也越高。
对于 BLOB、TEXT 和 VARCHAR 类型的列,必须使用前缀索引,只索引开始的部分字符
索引包含所有需要查询的字段的值。
具有以下优点:
索引通常远小于数据行的大小,只读取索引能大大减少数据访问量
一些存储引擎(例如 MyISAM)在内存中只缓存索引,而数据依赖于操作系统来缓存。因此,只访问索引可以不使用系统调用(通常比较费时)。
对于 InnoDB 引擎,若辅助索引能够覆盖查询,则无需访问主索引