MySQL - 索引总结

 目录

一、索引 - 优化查询

1-0 查询的三种情况

1-1 索引种类

二、普通索引 - 单例索引、组合索引

2-1 普通索引的创建方式（三种）

2-1-1 创建表是建立索引

2-1-2 在已存在的标上创建索引 - CREATE

2-1-3 在已存在表上创建索引 -  ALTER TABLE

2-2 普通索引的删除方式

2-3 查看索引信息

三、基于Btree的索引

3-1 聚集索引

3-2 辅助索引

3-2-1 覆盖索引 and 非覆盖索引

3-2-2 普通辅助索引创建 （MUL）

3-2-3 查看索引结构

3-2-4 删除索引

3-3 前缀索引

3-4 唯一键索引

3-5 覆盖索引 with 联合索引

四、explain（desc）命令应用 - 获取优化器选择后的执行计划

五、索引结构的重要字段

5-0 查看表结构的重要字段

5-1 explain 下的 type字段 - 查询SQL语句的查询类型

5-1-1 ALL - 全表扫描

5-1-2 Index - 全索引扫描 - 即在覆盖索引实现查询

5-1-3 range - 索引范围扫描

5-1-4 ref - 辅助索引的等值查询

5-1-5 eq_ref - 多表链接查询（join、on）

5-1-6 const、system - 主键或唯一键等值查询

5-2 Extra - 使用的索引决定

5-2-1 Extra下出现using  filesort - 文件排序的解决方式

---

 

一、索引 - 优化查询

1-0 查询的三种情况

• 缓存查询(不在mysql中进行数据查询)
• 全表扫描
• 索引扫描

1-1 索引种类

• Btree(btree  b+tree b*tree) - 二叉树
  • BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
• Rtree
  • RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
    相对于BTREE，RTREE的优势在于范围查找。
• HASH
  • 由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
    HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
• FullText - 全文索引
  • 即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
    全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

二、普通索引 - 单例索引、组合索引

MySQL索引的建立可以大大提高MySQL的检索速度。

索引分单列索引和组合索引：

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的缺点：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 建立索引会占用磁盘空间的索引文件。
• 建立索引的时候，速度很慢

注：一旦表创建索引，所有查询首先查询索引，再根据索引定位的结果去找数据。

2-1 普通索引的创建方式（三种）

2-1-1 创建表是建立索引

CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


CREATE TABLE mytable(  
 
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
 
);  

2-1-2 在已存在的标上创建索引 - CREATE

CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

CREATE INDEX indexName ON mytable(username(length)); 

2-1-3 在已存在表上创建索引 -  ALTER TABLE

ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;

ALTER table tableName ADD INDEX indexName(columnName)

2-2 普通索引的删除方式

DROP INDEX [indexName] ON mytable; 

2-3 查看索引信息

SHOW INDEX FROM table_name; \G

三、基于Btree的索引

 

• 聚集索引:基于主键,自动生成的,一般是建表时创建主键.如果没有主键,自动选择唯一键做为聚集索引.
• 辅助索引:人为创建的(普通,覆盖)；MUL
• 唯一索引:人为创建(普通索引,聚集索引)；UNI,如果有重复值是创建不了的

聚集索引和辅助索引的对比总结

• 聚集索引:叶子结点,按照主键列的顺序,存储的整行数据,就是真正的数据页
• 辅助索引: 叶子结点,列值排序之后,存储到叶子结点+对应的主键的值,便于回表查询