数据库索引

1，创建索引

CREATE TABLE 表名称（
	......
	 INDEX [索引名称] （字段）
	 ......
）

• 添加索引
  CREATE INDEX 索引名称 ON 表名(字段); /*添加索引方式1*/
ALTER TABLE 表名 ADD INDEX 索引名称(字段); /*添加索引方式2*/

CREATE INDEX idx_type ON t_message(type); /*添加索引方式1*/
 
ALTER TABLE t_message ADD INDEX idx_type(type);/*添加索引方式2*/

• 查找索引
  SHOW INDEX FROM 表名

• 删除索引
  DROP INDEX 索引名称 ON 表名

• Mysql提供的索引

1. 主键索引：primary key
2. 唯一索引：unique key
3. 全文索引：fulltext index
4. 普通索引：index

二、B+ Tree原理

• 数据结构
  B Tree 指平衡树，平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree是基于B Tree和叶子节点顺序访问指针进行实现，具有B Tree的平衡性，并且通过顺序访问指针提高查询性能

• 操作
  首先在根节点进行二分查找，找到一个key所在的指针，然后递归在所在指针指向的节点进行查找，直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的data

（注意:插入删除操作会破坏树的平衡性，需要对树进行一个分裂、合并、旋转等操作维护树的平衡性

• 文件系统及数据库系统采用B+ Tree作为索引结构的原因：

1. 更少的查找次数 ：平衡树的查找时间复杂度和树高h有关
2. 利用磁盘预读特性 ： 为了减少磁盘I/O 操作

三、B+ Tree索引

索引是在存储引擎层实现

优点：

1. 不需要进行全表查找，只需要进行树搜索
2. B+ Tree有序性：可以指定多个列作为索引列，多个索引列共同组成键
3. B+ Tree 单个节点可以存放多个子节点，相同的IO操作可以检索更多的信息
4. B+ Tree只在叶子节点存数据，非叶子节点（内部节点）存索引，先定义索引再通过索引查找数据

四、哈希索引

哈希索引以O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序和分组
• 只支持精确查找，无法用于部分查找和范围查找

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找

五、全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，查找条件使用MATCH AGAINST，而不是普通的WHERE

六、索引优化

• 独立的列
  索引列不能作为查询表达式的一部分，也不能是函数的参数

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

• 多列索引

使用多个列作为查询条件，使用多列索引

• 索引列的顺序
  选择性最强的索引放在前面

• 覆盖索引
  索引包含所有需要查找的字段的值

深入浅出数据库索引原理

1. 聚集索引

数据表创建一个主键，表在磁盘上的存储结构转换为树结构，即平衡树。（整个表就变成了一个索引）

2.非聚集索引

采用平衡树的数据结构，索引树结构中的各节点的值来自于表中的索引字段，例如user表中的username字段加上一个索引，那么索引树中节点值就是由username字段中的值构成
``每个索引之间互相不关联

3. 聚集索引和非聚集索引的区别

通过聚集索引可以查找需要的数据、通过非聚集索引可以查找记录对应的主键值，再使用主键值通过聚集索引查找需要的数据

聚集索引（主键）是通往真实数据所在的唯一路径。

举例：

create index index_birthday on user_info(birthday) select user_name from user_info where birthday = '1990-02-01'

执行过程：
首先：通过非聚集索引index_birthday 查找birthday = '1990-02-01’所记录的主键ID值
然后：通过主键ID值执行聚集查找，找到其对应的真实数据行存储的位置
最后：获取user_name字段的值并返回