Mysql的索引总结

数据库

发布日期: 2020-12-17

作者: 锦泉

文章字数: 2.5k

阅读时长: 8 分

阅读次数:

普通索引

转载参考：https://www.cnblogs.com/jiawen010/p/11805241.html

普通索引（单值索引）没有任何限制。

// 表建好后创建
// 格式：create  索引类型  索引名  on  表( 字段 ); 例如：
create  index  dept_index  on  table_name(dept);

//改变表结构
// 格式：alter table 表名 索引类型  索引名（字段）; 例如：
alter table table_name add index dept_index(dept) ;

//创建表的时候直接指定
CREATE TABLE table_name( 
    ID INT NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    INDEX [indexName] (username(index_length)) 
); 

//删除索引
DROP INDEX [indexName] ON table_name;

创建表的时候创建索引

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

其中：

UNIQUE是可选参数，表示索引为唯一性索引；FULLTEXT是可选参数，表示索引为全文索引；SPATIAL也是可选参数，表示索引为空间索引；
INDEX和KEY参数用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；
“索引名”是可选参数，用来给创建的索引取的新名称；
“属性名1”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段；
“长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用；
“ASC”和”DESC”都是可选参数，”ASC”参数表示升序排列，”DESC”参数表示降序排列。

改变表结构创建索引

ALTER  TABLE  表名  ADD   [ UNIQUE | FULLTEXT | SPATIAL ]   INDEX  
索引名（属性名  [ (长度) ]  [ ASC | DESC]）;

跟上边一样结构。

唯一索引

MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

唯一索引保证数据记录的唯一性。

语法的话把上边的 INDEX 改成 UNIQUE INDEX 就行。

主键索引

特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable( 
    ID INT NOT NULL, 
    username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) 
);

主键与唯一索引的区别

主键不允许空值，唯一索引允许空值
主键只允许一个，唯一索引允许多个
InnoDB中，主键产生唯一的聚簇索引，唯一索引产生唯一的非聚簇索引

组合索引（复合索引）

如果分别在 username，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

alter table table_name add index username_city_age_index(username,city,age)

建立这样的组合索引，其实是相当于分别建立了下面三组组合MySQL数据库索引：username,city,age | username,city | username 三种。为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合，并不是只要包含这三列的查询都会用到该组合索引。

二级索引（辅助索引、非聚簇索引）

没错，二级索引=辅助索引=非聚簇索引。

叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。InnoDB中主键所建立的是聚簇索引，而唯一索引、普通索引、前缀索引等都是二级索引。

我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引（非聚簇索引），先找到主键索引再通过主键索引找数据。

聚簇索引

MySQL数据库中innodb存储引擎，聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。将数据存储与索引放到了一块，找到索引也就找到了数据。

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

InnoDB

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。

MyISAM

InnoDB使用的是聚簇索引，MyISam使用的是非聚簇索引。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

MyISAM和InnoDB的区别

MyISAM：

不支持事务，但是每次查询都是原子的
支持表级锁
存储表的总行数
一个MyISAM表有三个文件：表结构文件，索引文件，数据文件
采用非聚簇索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不保证唯一性

InnoDB

支持ACID事务，支持四种隔离级别
支持行级锁及外键约束，因此可以支持写并发
不存储总行数，算行数要全表扫描
一个InnoDB引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也可能多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G）
主键索引采用聚簇索引，辅索引的数据域存储主键的值；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

全文索引

通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT（column）；创建全文索引

目前比较鸡肋，用es来实现会比较好

覆盖索引（using index）

有下面两种理解：

就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到“Using index”的信息：

从执行结果上看，这个SQL语句只通过索引，就取到了所需要的数据，这个过程就叫做索引覆盖。

只扫描索引而无需回表的优点：

索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量
因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多
一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
innodb的聚簇索引，覆盖索引对innodb表特别有用。innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询