1. 索引分类
1.1 普通索引
- 加快对数据的访问速度,一般在查询条件
WHERE column=
或排序条件ORDER BY column
中的数据列创建索引。
命令:
ALTER TABLE `table_name` ADD INDEX index_name ( `column` );
1.2 唯一索引(UNIQUE)
- 创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复;
- 唯一索引和普通索引使用的结构都是
B-tree
,执行时间复杂度都是O(log n)
; - 在
sql server
中,唯一索引字段不能出现多个null
值,在mysql
的Innodb
引擎中,是允许在唯一索引的字段中出现多个null
值的。
命令:
ALTER TABLE `table_name` ADD UNIQUE ( `column` );
或
ALTER TABLE 'table_name' ADD UNIQUE INDEX index_name ( `column` );
1.3 主键索引(PRIMARY)
- 不允许重复,不允许空值。
命令:
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );
或
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index_name ( `column` );
1.4 全文索引(FULLTEXT)
命令:
ALTER TABLE `table_name` ADD FULLTEXT ( `column`);
或
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index_name ( `column`);
1.5 组合索引 /多列索引 /复合索引 /联合索引
- 遵循“最左前缀”原则,把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了
column1
、column1column2
、column1column2column3
三个索引,而column2
或者column3
是不能使用索引的。
命令:
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );
<!-- 组合索引可能因为列名长度过长而导致索引的`key`太大,导致效率降低,在允许的情况下,可以只取`column1`和`column2`的前几个字符作为索引。-->
<!-- 例:表示使用`column1`的前4个字符和`column2`的前3个字符作为索引 -->
ALTER TABLE 'table_name' ADD INDEX index_name ( column1(4), column2(3) );
2 查询-解释(EXPLAIN)
EXPLAIN SELECT
命令,给SELECT
命令加一个EXPLAIN
关键字作为前缀。
列名称 | 含义 | 解释 |
---|---|---|
table | 数据表名称 | 按被读取的先后顺序排列 |
type | 本数据表与其它数据表之间的关联关系(JOIN) | 效率从高到低排序,system > const > eq_ref > ref > range > index > All |
possible_keys | 可选用的各个索引 | 本次查询中可选用的各个索引 |
key | 实际选用的索引 | 本次查询中实际选用的索引 |
key_len | 索引长度 | 按字节计算的索引长度(INTEGER 对应字节长度 4,复合索引可以看到查询的具体使用部分),一般key_len数据列里的值越小越好(意思是更快) |
ref | 关联数据表里的数据列 | 关联关系中另一个数据表里的数据列的名字 |
row | 预计读取数据行个数 | 本次查询预计读取此数据表中的数据行的个数(row数据列里的所有数字的乘积可以让我们大致了解这个查询需要处理多少组合) |