MySQL-索引

最新推荐文章于 2024-06-14 09:42:13 发布

转载最新推荐文章于 2024-06-14 09:42:13 发布 · 207 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/wangfeijiu/article/details/113409719

文章标签：

#mysql

数据库专栏收录该内容

4 篇文章

订阅专栏

索引介绍

索引是对数据库表中一列或多列的值进行排序的一种结构。

生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

索引的优缺点

优点：

1、索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。

2、索引可以帮助服务器避免排序和临时表

3、索引可以将随机IO变成顺序IO

4、索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性

5、关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

6、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

7、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

8、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

9、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

2、索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大

3、对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度

4、如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

5、对于非常小的表，大部分情况下简单的全表扫描更高效；

索引分类

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

一、逻辑分类
有多种逻辑划分的方式，比如按功能划分，按组成索引的列数划分等

1、按功能划分
主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL

ALTER TABLE TableName ADD PRIMARY KEY(column_list)

唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；

CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list);

普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

CREATE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));

全文索引：它查找的是文本中的关键词，主要用于全文检索。

2、按列数划分
单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。
二、物理分类

分为聚簇索引和非聚簇索引（有时也称辅助索引或二级索引）
聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

1、聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

2、非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表只能拥有一个聚簇索引。

聚簇索引优缺点（InnoDB）
此处针对InnoDB的聚簇索引和二级索引而言的。

优点：

1、数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

2、聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：

1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）

2、更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

3、二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。