索引
- 1.索引概述
- 2.索引优劣势
- 3.索引结构
- 拓展:
- 4.索引分类
- 5.索引语法
- 5.1创建索引
- 5.2查看索引
- 5.3删除索引
- 5.4修改索引
- 6.索引设计原则
1.索引概述
索引是是帮助MySQL高效获取数据的一种数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以通过这些数据结构实现对数据的高效查找。这种数据结构就是索引。
2.索引优劣势
- 优势
- 类似与书籍的目录索引,提高数据检索效率,降低数据库的IO成本。
- 通过索引列对数据排序,降低数据的排序成本和cpu的消耗。
- 劣势
- 索引也是一张表,表中保存了主键和索引字段,以及指向数据的记录,要占用磁盘空间。
- 对表进行insert,update,delete时,mysql不仅要更新表的数据,还要调整索引的信息,因此会降低表更新的速度。
3.索引结构
索引是在MySQL存储引擎层面实现的,而不是在服务器层面实现的。每种存储引擎支持的索引不一定相同。
MySQL提供的 四种:
- BTREE索引:最常见的索引类型
- HASH索引:使用场景简单
- R-TREE索引(空间索引):主要用于地理空间的数据类型
- FULL-TEXT(全文索引):主要用于全文索引
MyISAM、InnoDB、Memory存储引擎对索引的支持
MySQL中的B+TREE
- MySQL索引数据结构对B+TREE索引数据结构进行了优化,增加了指向相邻叶子节点的链表指针,提高了区间访问的性能。
拓展:
BTREE结构(m叉):
- 树中每个节点最多包含m个子节点
- 除根节点外每个节点至少ceil(m/2)个孩子
- 若根节点不是叶子节点至少有俩个孩子
- 所有的叶子节点都在同一层
- 每个非叶子节点由n个key与n+1个指针组成,其中[ceil(m/2)-1] <=n<=m-1
B+TREE与BTREE的区别:
- n叉B+TREE最多含有 n个key,BTREE最多含有n-1个key。
- B+TREE的叶子节点保存所有key的信息,依照key的大小顺序排列。
- 所有非叶子节点可以看做是key的索引部分。
4.索引分类
- 单列索引:即一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,允许有多个空值
- 复合索引:一个索引包含多个列
5.索引语法
5.1创建索引
创建普通索引
CREATE INDEX index_name ON table_name(username(length));
创建唯一索引
CREATE UNIQUE INDEX indexName ON table_name(username(length))
5.2查看索引
SHOW INDEX FROM table_name;
5.3删除索引
DROP INDEX [indexName] ON table_name;
5.4修改索引
修改唯一索引
ALTER table table_name ADD UNIQUE [indexName] (username(length))
修改普通索引
ALTER table tableName ADD INDEX indexName(columnName)
6.索引设计原则
- 对查询频率高且数据量大的表建立索引。
- 从where子句中(查询条件)中提取最佳候选列做索引,或者组合最常用的、过滤效果最好的列做组合索引。
- 多使用唯一索引,唯一索引区分度高,效率更高。
- 不要过多的建立索引,建立过多的索引,会对表的增删改操作引入相当高的维护操作。另外索引过多会提高MySQL的选择代价。
- 使用短索引,索引创建后也是通过硬盘来存储的,短索引可以提升索引的IO访问效率,也可以提升总体的访问效率。因为短索引相比较长索引在给定的存储空间中存储的数目更多,进而降低IO次数,提高IO访问效率。
- 利用最左前缀,若给3个字段(1,2,3)建立索引,相当于给(1)、(1,2)、(1,2,3)建立索引,对于(2),(2,3)等查询条件不会生效。