Mysql索引

标签：index name ## 查询 索引 Mysql 主键

1、什么是索引

        索引是数据库中一种特殊存储结构，旨在提高数据检索性能，提高访问速度，且是有序的。InnoDB存储引擎中有 主键索引，唯一索引，普通索引，全文索引 四类索引。

        注意：索引在InnoDB存储引擎中 与数据存储在一起的。

1.1、主键索引

        概念：数据库只有一个唯一索引，唯一且不重复。

        主键索引生成策略：

        1、数据表有主键（primary key），则 以primary key为主键索引。

        2、数据表未设置主键（primary key），但创建了唯一索引，则 以唯一索引值 为主键索引。

        3、数据表既没有创建主键也没有创建唯一索引，则 Mysql默认采用 DB_ROW_ID为主键索引。（这也是系统默认创建主键索引）

        语法：

## table_name 表名
## column_name 字段名
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

## 移除主键
ALTER TABLE table_name DROP PRIMARY KEY

1.2、唯一索引

        数据库可以存在多个唯一索引，但是设置唯一索引列值唯一不重复。它可以作用单列，也可以作用于多列（多列值组合起来唯一）。

        语法：

## index_name 索引名称
## table_name 数据表名
## column 列
## 作用于单列
create unique index index_name on table_name(column);
## 作用域多列
create unique index index_name on table_name(column1, column2,...);

## 移除唯一索引
alter table table_name drop key index_name;

        例如：用户id单列唯一，姓名+手机号 联合唯一

1.3、普通索引

        普通索引是数据库表中一列或多列的值的集合，这些值按照一定的顺序存储，以便于快速查找和访问。

        语法：

## index_name 索引名称
## table_name 数据表名
## column 列
## 作用于单列
create index index_name on table_name(column);
## 作用于多列
create index index_name on table_name(column1,column2);

1.4、全文索引

        全文索引（Full-Text Index）是一种特殊的数据库索引，它允许用户对文本中的单词或短语进行搜索。这种索引专门用于存储、检索和管理大型文本数据，使得在包含大量文本的列中进行搜索变得更加高效。

        语法：

## table_name 表名
## index_name 索引名称
## column_name 字段名
ALTER TABLE table_name ADD FULLTEXT(index_name) (column_name);

2、索引数据结构

2.1、前序

        在探讨索引数据结构之前，大家也听说过B+Tree是索引数据结构，但是为什么要选择B+Tree结构呢，而不选择 常规的红黑树，Hash表呢。

        我们先从二叉树来看，请看下图：

        这个二叉树是我们理想中的情况，但是如果是按照顺序插入数据，二叉树有变成什么样了。

        图中可看到，竟然变成了链表结构，我们都知道链表结构的查询性能是很低的，最坏的是o(n)，这个肯定无法满足索引结构，有一定风险。

        为了解决二叉树的顺序插入的缺点，二叉树又提出来红黑树，我们来看看它是什么样的。

        数据插入顺序（1，2，3，4，5，6，7，8，9，10，11，12，13，14，15），可以看出红黑树就解决了二叉树顺序查询变成链表的问题，这也是Java中Hash表底层结构会变成数组+链表+红黑树的原因。

         既然红黑树能满足了，但是InnoDB引擎为啥没有选择它也，其他红黑树本身还存在一个问题，当数据存量达到100w时，红黑树的高度与层级就变得很大，不利于查询，对于数据库这种高效查询场景是不适合的。

        我们看到红黑树能满足顺序存储问题，但是又把深度加大了，那我们何不在保持树特性时，减少深度也，不就解决这个问题了，是的，这就是后续的B-Tree结构了。

        B-Tree结构又是什么样的呢。我们先了解哈B-Tree的高度是什么，高度并不是我们理解的层高，而是一个节点可以存放几个子节点，一个节点可以存放 n-1个数据。例如：高度为5，一个节点的子节点就有5个（相当于一个节点的指针有5个），一个节点可以存放4个值，如果有疑问，可以看看下面的图。

        图中可知：一个节点存放多个值，一个节点多个子节点，这样就解决了红黑树深度问题。既然解决了为啥没有选择呢，我们从排序方面来思考，上图的叶子节点中可以看出是一个顺序的，但是又缺少了值，如 1，2，4，5，7，8中缺少了3，6两个值，如果采用它那是不是在排序的时候还需再去拿去一次，这样对性能不就降低了。、

        所以为了解决这个问题 就将B-Tree优化成立B+Tree树，同时将叶子节点之间以指针关联，做到可以访问后续数据的目的，下面我们来看看图：

        图中可知：叶子节点包含了上级节点所有的数据，并将相邻节点连接起来，达到快速访问目的。

         可以看出B+Tree结构满足需求，这也是InnoDB选择B+Tree结构的原因。InnoDB并不是这届这么使用的，它优化了叶子节点的关系，优化成立双向循环链表，同时将数据存放在里面，这也是我们常说的Mysql数据页。

        看到这大家对整个思路有了一定的认识，既然树可以实现，那为啥Hash表不可以呢。Hash的查询性能优于Tree查询性能的，其实这个本质上还是Hash表的缺陷造成的，如果Hash值没有冲突，那绝对是YYDS，但是一个冲突了就麻烦了（虽然业内提出来链地址法，开放寻址法等来解决）最终还是有些影响。其次冲突是一个原因，最核心的原因是Hash只能等值查询，范围查询不支持了，这才是没有选择的最终原因，排序这些本身还可以处理，范围不支持就不行了。

        Hash表缺点如下：1）Hash冲突。2）不支持范围查询。 

2.2、数据结构

        InnoDB索引分为 聚集索引 与 非聚集索引（二级索引）两种，他们之间的结构基本一致，只是叶子节点有点不一样。

        聚集索引：聚集索引可以看作是一个主键索引，一张表只有一个，生成策略可以查看主键索引。

        非聚集索引（二级索引）：可以看作是一个索引之外的都是非聚集索引。

2.2.1、聚集索引

2.2.1、非聚集索引（二级索引）

2.2.2、总结

        上面两图可知：聚集索引与非聚集索引基本一致，除也叶子节点外的结构不一致外，其他的也差不多。 

        非聚集索引叶子节点之所以不存储Row数据，第一个原因：冗余存储，需要维护更多的数据，不利于数据库性能。第二个原因：节约内存。

3、索引的优缺点

        优点：提高检索速度，加速排序（索引本身有序），提高分组效率，减少磁盘IO（全命中索引）。

        缺点：占用磁盘空间，维护成本高，增加DML和DDl语句开销，创建和摧毁耗时等。

        总结：一般对常用查询字段添加索引，对经常更新字段不做索引，避免增加索引开销。

4、案例分析

        注意：索引不会讲解SQL如何优化，只会对比性能，相关优化点会在SQL优化中统一讲解。

4.1、主键索引认证

4.1.1、不使用主键索引查询

（1）查询id=100000的用户信息，图中可看到根据id等值查询竟然需要将近2秒时间。

4.1.2、使用主键索引查询

（1）创建主键索引列

## 创建主键索引
alter table d_user add primary key idx_primary_id(id);

 （2）查询id=100000的用户信息

        图中可知：耗时基本可以忽略不记，所以使用索引查询时会极大提高查询性能。

4.1.3、总结

        对比上诉两图中可知：b场景执行效率 > a场景执行效率

4.2、唯一索引认证

4.2.1、不使用唯一索引

（1）新增/更新数据场景：允许列有相同数据内容。

（2）查询phone='13500003162'用户信息，图中可知耗时2s

4.2.2、使用唯一索引

（0）phone列创建唯一索引

        注意随着数据越多，创建/更新/移除索引都需要耗更多时间。

create unique index idx_unique_phone on d_user(phone);

 

（1）新增/更新数据场景：不允许列有相同数据内容。

（2）查询phone=’13500003162‘用户信息

        注意：explain 命令在SQL优化中描述。

4.2.3、总结

       添加唯一索引列不允许有重复值，且作为条件查询时，提升性能。如果是单列则整列唯一，如果是多列唯一，则多列值整合在一起唯一。

4.3、普通索引认证

        图中可值：目前姓名与年龄都没有创建索引。

4.3.1、不使用普通索引查询

（1）查询name=测试名称480919 用户信息，耗时2秒多

4.3.2、使用普通索引查询

（0）姓名创建普通联合索引

create index idx_user_name on d_user(user_name);

 

（1）查询name=测试名称480919 用户信息

4.3.3、总结

        使用普通索引查询时，能极大提升查询性能。

4.4、全文索引认证

        全文索引在Mysql中很少使用，一般使用ES替代，所以不在展示。

5、SQL优化

        待定！！！

标签：index,name,##,查询,索引,Mysql,主键
From： https://blog.csdn.net/2401_85207246/article/details/144500792