索引是指向表中数据的指针。数据库的索引和书籍后面的索引很相似。
作用:提高select和where的速度,却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度
索引类似于书的目录,子表除了包含指定字段中的数据,还包含一个 rowid 列,用于存储当前记录在原始表中的位置,加快了查询到速度。
创建:CREATE INDEX index_name ON table_name;
单列索引:CREATE INDEX index_name ON table_name (column_name);
唯一索引:CREATE UNIQUE INDEX index_name
on table_name (column_name);
聚簇索引: 聚簇索引在表中两个或更多的列的基础上建立。
CREATE INDEX index_name
on table_name (column1, column2);
创建单列索引还是聚簇索引,要看每次查询中,哪些列在作为过滤条件的 WHERE 子句中最常出现。
如果只需要一列,那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列,那么聚簇索引就是最好的选择。
删除索引:DROP INDEX table_name.index_name;
什么时候应当避免使用索引?
小的数据表不应当使用索引;
需要频繁进行大批量的更新或者插入操作的表;
如果列中包含大数或者 NULL 值,不宜创建索引;
频繁操作的列不宜创建索引。
索引使用成本:
表中插入、修改或者删除数据时,数据库引擎也必须维护索引,以保持索引和原始表的同步;
索引的数据结构:
B+ 树、B- 树、位图
为什么MySQL不采用哈希索引?
哈希值是无序的,如果采用哈希索引就不能实现数据库中范围的查找,大于小于的操作无法实现,如果要排序的话也不能用哈希值实现。还存在值不同但是哈希值相同的情况。
平衡二叉树:
左子树和右子树高度差不大于1
树越高,查找速度越慢,且需要回旋查找
B 树:一个节点存两个值(与平衡二叉树相比树的高度变矮了),查找速度变快
B+树(解决了回旋查找问题)
叶子节点组成链表,范围查找速度非常高
叶子结点存储了key和value:key为数字值,value为叶子结点地址
为什么索引会失效?
一般存在与联合索引中(a,b)
先按照a排序,a相等的情况下再排b
如果不遵循最佳左前缀法则的话(要从最左边开始查询 因此like中%在最左边的条件是无效的),b是无序的