MySQL 索引学习笔记

索引的作用：空间换时间，加快查询的速度。

select * from student where name = 'Tom'

• 若name字段没有索引：full table scan
• 若name字段有索引：减少scan的数目

索引的创建：基于某一列创建，由某一列上的数据组成。

create index <index_name> on students(name)

详细的语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...

• index_type: USING {BTREE | HASH}
• index_col_name: col_name [(length)] [ASC | DESC]
• 如果是CHAR，VARCHAR类型，length可以小于字段实际长度
• 如果是BLOB和TEXT类型，必须指定 length
• index_option: KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
• algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY}
• lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

索引的数据结构：

• B-树：有序树，因此查找、插入、删除的时间复杂度为O(logN)
• 过程：select * from students where name='Tom'，在 B- 树中找到值为 Tom 的结点，时间复杂度为 O(logN)，得到 Tom 对应的行在数据库表中的位置。
• Hash表：
• 数组结构：key 为列的值，value 为该行在数据库表的位置，在数组中的位置由 key 的 hash code 确定。
• 优点：查找快，时间复杂度 O(1)
• 缺点：无序，对于范围的查找不行，例如 select * from students where age < 30
• R-树：解决空间问题

有的情况下即使定义了索引也不会使用：

where name != 'Tom'
where name <> 'Tom'
where name not in ('Tom', 'Lily')
where name like '%To%' // %出现在最左边，无法进行比较

联合索引 Composite Index：

create index <index_name> on students(name, age, address)

对于联合索引，只有符合最左前缀的查询才会被优化：

where name = 'Tom'  // 优化
where name = 'Tom' and age = '12'  // 优化
where age = '12'  // 不优化
where age = '12' and address='Shanghai'  // 不优化

前缀索引：
假设students表包括 first_name 和 last_name：

• 若只对 last_name 建立索引，则 last_name （姓）的选择性太低。
• 若对 first_name 和 last_name 建立联合索引，则索引太长，导致插入/更新/删除 数据行时维护索引的时间较长。
• 因此可以对 first_name 取前缀：create <index_name> on students(last_name, left(first_name, 3))

不需要建立索引的情况：

• 表中行数少
• 某一列的选择性 selectivity 较低。例如 gender 列只有 M 和 F 两种值，则无需对该列建立索引。

一些注意事项：

• 索引不能为空。因此任何包含 null 值的列都不会包含在索引中，即使显示地对该列建立索引也不会提高性能。
• 连接列不会使用索引：

select * from students where first_name = 'San' and last_name = 'Zhang'; // 推荐使用
select * from students where first_name || last_name = 'San Zhang'; // 不推荐使用

• 带%的like语句会降低索引的效率
• 注意 NOT 的使用

where age != '20' // 不推荐使用
where age < '20' or age > '20' // 推荐使用

---

引用：
MySQL 官网