这是我参与「第五届青训营」伴学笔记创作活动的第 9 天

Day9

索引

查询数据时，MySQL底层会触发磁盘IO，对表中的数据逐条读取并判断。数据越多，查询越慢。

《新华字典》中有目录索引，我们可以根据音节、偏旁等方式查找不认识的字。

类似地，MySQL也提供了索引机制。

基本操作

创建索引

CREATE

CREATE [UNIQUE|FULLTEXT] [INDEX|KEY] indexName ON tableName (columnName(length) [ASC|DESC]) [USING {BTREE|HASH]}];

length：如果字段存储的值过长，选用值的前多少个字符创建索引。使用一个字段值中的前N个字符创建出的索引，就可以被称为前缀索引。

索引默认的数据结构就为B+Tree，USING关键字显式指定索引的数据结构（必须是当前引擎支持的结构）。

ALTER

ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

DDL

CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,
  .....,
  PRIMARY KEY [indexName1] (columnName1(length)),# 主键索引
  INDEX [indexName] (columnName(length))  # 普通索引
);

查询索引

SHOW INDEX FROM tableName;

Key_name：当前索引的名字。
Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。如果该值少于数据的实际行数，那目前索引有可能失效。
Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。

删除索引

MySQL并未提供修改索引的命令。

DROP INDEX indexName ON tableName;

指定索引查询

一条查询语句在有多个索引可以检索数据时，显式指定一个索引，减少优化器选择索引的耗时。

SELECT * FROM table_name FORCE INDEX(index_name) WHERE ...;

如果不熟悉整个业务系统的话，建议交给优化器来自行选择，否则会适得其反！

MySQL索引基本操作 | 青训营笔记