索引的作用：空间换时间，加快查询的速度。
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 官网
网友评论