6、MySQL 中索引的介绍-USB迷|专注于互联网分享

6、MySQL 中索引的介绍

6.1、索引是什么？

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”。
下图就是一种可能的索引方式示例：
- 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
- 为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

在这里插入图片描述

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件形式存储磁盘上

6.2、磁盘块和磁盘页

数据库索引是存储在磁盘上的，如果数据很大，必然导致索引的大小也会很大，超过几个 G（好比新华字典字数多必然导致目录厚）
当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，为了降低内存开销，InnoDB 在把磁盘数据读入到内存时会以页（Page）为基本单位。（一次 I/O 操作）
每一个磁盘块在 MySQL 中是一个页，页大小是固定的，MySQL InnoDB 的默认的页大小是 16k，每个索引会分配在页上的数量是由索引字段的大小决定。当索引字段值的长度越长，每一页上的数量就会越少，因此在一定数据量的情况下，索引的深度会越深，影响索引的查找效率。

# 查看 innodb 的页大小；
show global status like '%innodb_page_size%';

6.3、索引的优缺点

优点：

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本；
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

缺点：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

6.4、聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据跟索引放在一起就是聚簇索引。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的好处：按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 io 操作。

聚簇索引的限制：

对于 MySQL 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id，比如 uuid 这种。

6.5、MySQL 索引分类

6.5.1、单值索引

即一个索引只包含单个列，一个表可以有多个单列索引；

# 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name)
);# 单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); # 删除索引：
DROP INDEX idx_customer_name  on customer;

6.5.2、唯一索引

索引列的值必须唯一，但允许有空值

# 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_no)
);# 单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); # 删除索引：
DROP INDEX idx_customer_no on customer ;

6.5.3、主键索引

设定为主键后数据库会自动建立索引，innodb 为聚簇索引；

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id) 
);CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200) 
);#单独建主键索引：
ALTER TABLE customer2 add PRIMARY KEY customer2(id);  #删除主键索引：
ALTER TABLE customer2 drop PRIMARY KEY ;  #修改主键索引：
#必须先删除掉(drop)原索引，再新建(add)索引

6.5.4、复合索引

即一个索引包含多个列

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_name),KEY (customer_no,customer_name)
);#单独建索引：
CREATE INDEX idx_customer_no_name ON customer(customer_no,customer_name); #删除索引：
DROP INDEX idx_customer_no_name  on customer ;

6.5.6、基本语法

# 1.创建
CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length)); 
# 如果是CHAR，VARCHAR类型，length可以小于字段实际长度； 如果是BLOB和TEXT类型，必须指定length。
ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) # 2.删除
DROP INDEX [indexName] ON mytable; # 3.查看
SHOW INDEX FROM table_name# 4.使用 ALTER 命令
# 有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);  # 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);  # 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);  # 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);  #该语句指定了索引为 FULLTEXT ，用于全文索引。

6.6、哪些情况下需要建立索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

思考：排序和分组哪个更伤性能？

6.7、哪些情况不要创建索引

表记录太少

300 万数据时 MySQL 性能就开始下降了，这时就可以开始开始优化了
经常增删改的表

提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。

因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件
where 条件里用不到的字段不创建索引

6、MySQL 中索引的介绍

6.1、索引是什么？

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”。
下图就是一种可能的索引方式示例：
- 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
- 为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

在这里插入图片描述

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件形式存储磁盘上

6.2、磁盘块和磁盘页

数据库索引是存储在磁盘上的，如果数据很大，必然导致索引的大小也会很大，超过几个 G（好比新华字典字数多必然导致目录厚）
当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，为了降低内存开销，InnoDB 在把磁盘数据读入到内存时会以页（Page）为基本单位。（一次 I/O 操作）
每一个磁盘块在 MySQL 中是一个页，页大小是固定的，MySQL InnoDB 的默认的页大小是 16k，每个索引会分配在页上的数量是由索引字段的大小决定。当索引字段值的长度越长，每一页上的数量就会越少，因此在一定数据量的情况下，索引的深度会越深，影响索引的查找效率。

# 查看 innodb 的页大小；
show global status like '%innodb_page_size%';

6.3、索引的优缺点

优点：

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本；
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；

缺点：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

6.4、聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据跟索引放在一起就是聚簇索引。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的限制：

对于 MySQL 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id，比如 uuid 这种。

6.5、MySQL 索引分类

6.5.1、单值索引

即一个索引只包含单个列，一个表可以有多个单列索引；

# 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name)
);# 单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); # 删除索引：
DROP INDEX idx_customer_name  on customer;

6.5.2、唯一索引

索引列的值必须唯一，但允许有空值

# 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_no)
);# 单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); # 删除索引：
DROP INDEX idx_customer_no on customer ;

6.5.3、主键索引

设定为主键后数据库会自动建立索引，innodb 为聚簇索引；

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id) 
);CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200) 
);#单独建主键索引：
ALTER TABLE customer2 add PRIMARY KEY customer2(id);  #删除主键索引：
ALTER TABLE customer2 drop PRIMARY KEY ;  #修改主键索引：
#必须先删除掉(drop)原索引，再新建(add)索引

6.5.4、复合索引

即一个索引包含多个列

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_name),KEY (customer_no,customer_name)
);#单独建索引：
CREATE INDEX idx_customer_no_name ON customer(customer_no,customer_name); #删除索引：
DROP INDEX idx_customer_no_name  on customer ;

6.5.6、基本语法

# 1.创建
CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length)); 
# 如果是CHAR，VARCHAR类型，length可以小于字段实际长度； 如果是BLOB和TEXT类型，必须指定length。
ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) # 2.删除
DROP INDEX [indexName] ON mytable; # 3.查看
SHOW INDEX FROM table_name# 4.使用 ALTER 命令
# 有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);  # 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);  # 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);  # 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);  #该语句指定了索引为 FULLTEXT ，用于全文索引。

6.6、哪些情况下需要建立索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

思考：排序和分组哪个更伤性能？

6.7、哪些情况不要创建索引

表记录太少

300 万数据时 MySQL 性能就开始下降了，这时就可以开始开始优化了
经常增删改的表

提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。

因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件
where 条件里用不到的字段不创建索引

USB迷 | 专注于互联网分享

6、MySQL 中索引的介绍

6、MySQL 中索引的介绍

6、MySQL 中索引的介绍

6.1、索引是什么？

6.2、磁盘块和磁盘页

6.3、索引的优缺点

6.4、聚簇索引与非聚簇索引

6.5、MySQL 索引分类

6.5.1、单值索引

6.5.2、唯一索引

6.5.3、主键索引

6.5.4、复合索引

6.5.6、基本语法

6.6、哪些情况下需要建立索引

6.7、哪些情况不要创建索引

6、MySQL 中索引的介绍

6、MySQL 中索引的介绍

6.1、索引是什么？

6.2、磁盘块和磁盘页

6.3、索引的优缺点

6.4、聚簇索引与非聚簇索引

6.5、MySQL 索引分类

6.5.1、单值索引

6.5.2、唯一索引

6.5.3、主键索引

6.5.4、复合索引

6.5.6、基本语法

6.6、哪些情况下需要建立索引

6.7、哪些情况不要创建索引

与本文相关的文章

评论列表 (0)