10. MySQL索引介绍

索引介绍

​ 索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

​ 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在 单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都 是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

索引的优势和劣势

1. 优势：

• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。 – 检索

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。 --排序

  • 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
  • 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
  • where 索引列在存储引擎层处理 索引下推 ICP

2. 劣势：

• 索引会占据磁盘空间
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还要保存或者更新对应的索引文件。

索引的分类

1. 单列索引

• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询 数据更快一点。 add index
• 唯一索引：索引列中的值必须是唯一的，但是允许为空值. add unique index
• 主键索引：是一种特殊的唯一索引，不允许有空值。 pk

2. 组合索引

• 在表中的多个字段组合上创建的索引 add index(col1,col2…)
• 组合索引的使用，需要遵循最左前缀原则（最左匹配原则）。
• 一般情况下，建议使用组合索引代替单列索引（主键索引除外）。

3. 全文索引

• 只有在MyISAM引擎、InnoDB（5.6以后）上才能使用，而且只能在CHAR,VARCHAR,TEXT类型字段上使用全文索 引。 fulltext

• 优先级最高 先执行 不会执行其他索引

• 存储引擎 决定执行一个索引

4. 空间索引

不做介绍，一般使用不到。

创建索引的场景

1. 哪些情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
• 查询中排序的字段，应该创建索引
• 频繁查找字段 覆盖索引
• 查询中统计或者分组字段，应该创建索引 group by

2. 哪些情况不需要创建索引

• 表记录太少
• 经常进行增删改操作的表
• 频繁更新的字段
• where条件里使用频率不高的字段

索引的使用

1. 创建索引

• 单列索引之普通索引

  CREATE INDEX index_name ON table(column(length)) ;
  ALTER TABLE table_name ADD INDEX index_name (column(length)) ;
  

• 单列索引之唯一索引

  CREATE UNIQUE INDEX index_name ON table(column(length)) ; 
  alter table table_name add unique index index_name(column);
  

• 单列索引之全文索引

  CREATE FULLTEXT INDEX index_name ON table(column(length)) ; 
  alter table table_name add fulltext index_name(column)
  

• 组合索引

  ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;
  

2. 删除索引

DROP INDEX index_name ON table

3. 查看索引

SHOW INDEX FROM table_name