MySQL索引总结

最新推荐文章于 2023-12-01 17:22:18 发布

xylitolz

最新推荐文章于 2023-12-01 17:22:18 发布

阅读量162

点赞数

分类专栏： MySQL 文章标签： mysql 索引

本文链接：https://blog.csdn.net/xylitolz/article/details/118122306

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本篇文章已同步更新至github仓库JavaSummary，欢迎star!

简介

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的排好序的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

索引的优势与劣势

优势

查找：提高数据检索的效率，降低数据库的IO成本
排序：通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

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

索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列

主键索引与唯一索引的区别

一个表的主键只能有一个，而唯一索引可以建多个。创建了一个主键的同时，也就为这个字段创建了一个唯一索引
主键可以作为其它表的外键
主键不可为null，唯一索引可以为null

索引设计原则

【1】最左前缀匹配原则：非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

索引匹配的最左原则具体是说，假如索引列分别为A，B，C，顺序也是A，B，C：

那么查询的时候，如果查询【A】【A，B】【A，B，C】，那么可以通过索引查询
如果查询的时候，采用【A，C】，那么C这个虽然是索引，但是由于中间缺失了B，因此C这个索引是用不到的，只能用到A索引
如果查询的时候，采用【B】【B，C】【C】，由于没有用到第一列索引，不是最左前缀，那么后面的索引也是用不到了
如果查询的时候，采用范围查询，并且是最左前缀，也就是第一列索引，那么可以用到索引，但是范围后面的列无法用到索引

【2】= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。
【3】尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)（列中不同值的数目与表中记录数的比），表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是0.1以上，即平均1条扫描10条记录。
【4】**索引列不能参与计算，保持列“干净”，**比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
【5】尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

因为索引虽然加快了查询速度，但**索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引**，因此索引并不是越多越好

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键

最左索引：最左：索引当中的第一个字段，只要查询条件中包含了第一个字段就会创建索引

什么时候适合创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
where条件里用不到的字段不创建索引
在高并发下倾向于创建组合索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不需要创建索引

表记录太少
经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
- 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
数据重复且平均分布的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
- 注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
- 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0. 99。一个索引的选择性越接近于1，这个索引的效率就越高。

建了索引，什么时候会不命中索引

如果条件中有or，即使其中有条件带索引也不会使用（这也是为什么尽量少用or的原因）。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
不遵守最左前缀原则，则不会使用索引
like查询是以%开头
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
如果mysql估计使用全表扫描要比使用索引快，则不使用索引

MySQL只能使用一个索引

如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、 age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

MySQL多条件查询索引为什么只用到一个？

如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。

与其说是“数据库查询只能用到一个索引”，倒不是说是和只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。
如这条语句：

select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'

我们来想象一下当数据库有N个索引并且查询中分别都要用上他们的情况：
查询优化器（用大白话说就是生成执行计划的那个东西）需要进行N次主二叉树查找[这里主二叉树的意思是最外层的索引节点]，此处的查找流程大概如下：

查出第一条column1主二叉树等于1的值，
然后去第二条column2主二叉树查出foo的值并且当前行的coumn1必须等于1，
最后去column主二叉树查找bar的值并且column1必须等于1和column2必须等于foo。

如果这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生，所以当遇到以下语句的时候，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。

select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar'

所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。
所以如上条的情况，最佳推荐是使用index(column1,column2,column3）这种联合索引，此联合索引可以把b+tree结构的优势发挥得淋漓尽致：
一条主二叉树（column=1），查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询，在二级二叉树查询到column2=foo后，去三级二叉树column3=bar查找

最左前缀

多列索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引：

* firstname，lastname，age
* firstname，lastname
* firstname

添加索引

ALTER TABLE

PRIMARY KEY 主键索引（索引的值必须唯一且不能为NULL）

alter table `table_name` add primary key (`column`) ;

UNIQUE唯一索引（索引的值必须唯一（除了NULL外，NULL可能会出现多次））

alter table `table_name` add unique index_name(column_list) ;

INDEX普通索引/复合索引

alter table `table_name` add index index_name (column_list) ;

FULLTEXT全文索引

ALTER TABLE `table_name` ADD FULLTEXT index_name( `column`)

索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引

CREATE INDEX

普通索引

create index index_name on table_name (column_list) ;

UNIQUE索引

create unique index index_name on table_name (column_list) ;

说明：不能用CREATE INDEX语句创建PRIMARY KEY索引

ALTER TABLE与CREATE INDEX的区别

With CREATE INDEX, we must provide a name for the index. With ALTER TABLE, MySQL creates an index name automatically if you don’t provide one.
Unlike ALTER TABLE, the CREATE INDEX statement can create only a single index per statement.
```
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
```
In addition, only ALTER TABLE supports the use of PRIMARY KEY.

查看索引

show index from `table_name`;
show index from `table_name`\G;

删除索引

drop index `index_name` on `table_name` ;
alter table `table_name` drop index `index_name` ;
alter table `table_name` drop primary key ;

说明：

在前两条语句中，都删除了table_name中的索引index_name。
最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

key和index区别

mysql的key和index多少有点令人迷惑，这实际上考察对数据库体系结构的了解

key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。
- primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index
- unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index
- foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index
可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（至少在Oracle上建立外键，不会自动建立index），因此创建key也有如下几种方式：
- 在字段级以key方式建立，如 create table t (id int not null primary key);
- 在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
- 在表级以key方式建立，如create table t(id int, primary key (id));
其它key创建类似，但不管哪种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key
index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。如: create table t(id int, index inx_tx_id (id));
我们说索引分类，分为主键索引、唯一索引、普通索引(这才是纯粹的index)等，也是基于是不是把index看作了key。
比如 create table t(id int, unique index inx_tx_id (id)); index当作了key使用
最重要的也就是，不管如何描述，理解index是纯粹的index，还是被当作key，当作key时则会有两种意义或起两种作用

索引规则

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；

以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。