一天一个面试题之——索引

• 索引（Index）是帮助MySQL高效获取数据的数据结构，可以得到索引的本质：索引是数据结构。

• 索引的主要目的就是为了提高查询速度

• 除数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。

当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

索引的数据结构：B-、B+、R-、散列

散列实现对直接查找方式能提供最优的性能，但对一定范围的查找却效率底下。

B-树索引实现是一个专门为范围查询设计的

MySQL索引结构有

• B-Tree索引
• B+Tree索引
• 聚簇索引与非聚簇索引
• full-text全文索引
• Hash索引
• R-Tree索引

1 作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接，实现表与表之间的参照完整性（外键）
• 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化

2 分类

• 主键索引（PRIMARY KEY）
• 唯一索引（UNIQUE）
• 常规索引（INDEX）
• 全文索引（FULLTEXT）不使用。假设大量的文本检索，可以考虑使用其他数据库产品。

3 常规索引

索引类似数的目录：索引本身也会一张表，索引也会占用空间。

index和key关键字都可设置常规索引
应加在查找条件的字段

• 不宜添加太多常规索引，影响数据的插入、删除和修改操作

CREATE TABLE  `result` (
	 //省略一些代码
	INDEX/KEY   `ind`  (`studentNo`, `subjectNo`)
)

ALTER TABLE  `result`  ADD  INDEX  `ind`  (`studentNo`, `subjectNo`);

CREATE index ind_name ON 表名(字段名); 

4 唯一索引(UNIQUE INDEX)

唯一索引（UNIQUE）
作用：

• 避免同一个表中某数据列中的值重复
• 与主键索引的区别
  • 主键索引只能有一个
  • 唯一索引可有多个
  • 唯一索引可以有一个 null值

  CREATE TABLE  `Grade` (
    `GradeID`  INT(11)  AUTO_INCREMENT  PRIMARY KEY,
    `GradeName`  VARCHAR(32)  NOT NULL  UNIQUE
    #或  UNIQUE  KEY  `GradeID`  (`GradeID`)
    )
  
  

CREATE UNIQUE index ind_name ON 表名(字段名);

创建的是唯一索引，假设里面有数据，那么不能有重复的数据，创建失败。

5 主键索引

某一个属性组能唯一标识一条记录

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置
• 特殊的唯一索引，不能存在null

 CREATE TABLE  `表名` (
	`GradeID`  INT(11)   AUTO_INCREMENT    PRIMARY KEY,
	#或  PRIMARY KEY(`GradeID`)
)

6 索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

7 索引的失效场景(面试会经常问）

• 1.列类型是字符串，查询条件未加引号。
• 2.未使用该列作为查询条件
• 3.使用like时通配符在前
• 4.在查询条件中使用OR
• 5.对索引列进行函数运算
• 6.联合索引ABC问题

8 查看索引是否失效

-- 执行计划：检测当前sql语句各种指标，从而来判断当前这个sql是不是一个合格的sql语句
EXPLAIN SELECT * FROM `hpa_normal_tissue` WHERE id LIKE 'ENSG00000000%'

EXPLAIN:常看的那些字段