MySQL-索引

一：概述

索引(index)是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向数据），这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。 

优缺点：

二：索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有着不同的结构主要包含以下几种。

各个存储引擎对索引的支持情况

三：索引分类

从组成索引的字段个数角度分类

1.单列索引

2.联合索引（复合索引）

根据字段特性索引分类：

在InnoDB存储引擎中的索引。

根据存InnoDB表的索引按照叶子节点存储的是否为完整表数据又可以分为以下两种：

全表数据就是存储在聚簇索引中的。

聚集索引的选取规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一索引作为聚集索引。

如果没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

举个例子：我们有一张user表，id为主键，为name创建了索引。当我们执行

select* from user where name='Arm'; 

我们会通过name的索引定位到元素，name是二级索引，定位拿到的是聚集索引的值，接着通过聚集索引的表去查询拿到行数据。这个也称为回表查询。

四：索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名,...);

省略号代表一个索引可以关联多个字段。

查看索引

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名 ON 表名;

五：SQL性能分析

staus查询：我们要进行SQL数据库的优化。我们得先了解SQL语句执行频率，例如增删改查哪个业务进行的多。

MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息，可以查看当前数据库的INSERT,UPDATE,DELETE,SELECT的访问频次。(global是查看全局的信息，session是查看当前会话的信息)

假如我们已经确认了当前数据库slesct占用了大部分。那么到底需要优化哪些select语句呢？那么就要借助慢查询日志来定位。

慢查询日志：慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志。

比如一条sql执行超过5秒钟，我们就算慢SQL，希望能 收集超过5秒的sql，接着来进行优化。MySQL的慢查询日志默认没有开启（可以通过show variables like 'slow_query_log';查询）。

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件。

我们可以通过使用set global slow_query_log=1;开启慢查询日志指对当前数据库生效，set long_query_time=2;设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志。但是MySQL重启后则会失效。

如果需要永久生效，就必须修改配置文件

查找配置文件的方法在下方：

https://blog.csdn.net/weixin_42555514/article/details/103158468

配置文件中配置如下信息：

#开启慢查询日志开关

slow_query_log=1;

#设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志。

long_query_time=2;

profile详情:能够查询每条SQL语句的耗时时间。

通过select@@have_profiling; 语句能够看到当前MYSQL是否开启profile操作。默认profiling是关闭的，可以通过set语句在session/global级别开启profiling; SET [session/global] PROFILING=1;

SHOW PROFLES ; #查看每一条SQL语句的耗时。

show profile for query query_id;#用来查询指定 query_id(上面的查询表结果中有query_id)的SQL语句各个阶段的耗时情况。

show profile cpu for query query_id;#查看指定 query_id 的SQL语句CPU执行情况。

explain执行计划：EXPLAIN或者DESC命令获取如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

语法：# 直接在select语句之前加上关键字explain/desc.

EXPLAIN SELECT 字段列表FROM 表名 WHERE 条件;

EXPLAIN执行计划各个字段的含义：

1.id:select查询到序列号，代表查询中执行select字句或者是操作表的顺序(id相同，执行顺序从上到下，id不同，值越大，越先执行)。

举个例子：我们有三张表，一张学生表s，一张课程表c，学生和课程是多对多的关系，第三张表sc用来维护他们之间的关系。我们来查看各个学生选的课程的执行计划：explain select s,c from s,c,sc where s.id=sc.student_id and c.id=sc.course_id;

可以看到id值相同都是1，所以从上往下依次执行查询语句。

2.select_type:表示select的类型，常见的取值有SIMPLE(简单查询),PRIMARY(主查询,即外层查询),UNION(联合查询),SUBQUERY(子查询)等。

3.type:连接类型，性能由好到差的连接类型为NULL,system,const,eq_ref,ref,range,index,all。NULL是基本不可能的，比如我们什么都不做一般是NULL,例如SELECT 1;system 一般是查询系统表会出现。根据主键或者唯一索引一般会是const。使用非唯一索引性的索引进行查询会出现ref，

4.possible_key：显示可能应用在这张表上的索引，一个或多个。

5.key:实际使用的索引，如果为NULL,则没有使用索引。

6.key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精度的前提下，长度越段越好

7.rows：MySQL认为必须要执行查询的次数，在innodb引擎表中，是一个估计值，可能并不总是准确。

8.filtered：返回结果的行数占需要读取行数的百分比，filtered的值越大越好。

9.Extra：额外的信息。

六：索引使用

1.最左前缀法则：

如果索引了多列(联合索引)联合索引，要遵循最左前缀法则。最左前缀法则是指在查询时从索引的最左列开始，并且不跳过索引的列，如果跳过了某一列，索引将部分失效（跳过字段和后面的字段索引失效）；

我们先来认识一下联合索引：

举例：创建一个（a, b）的连接索引，那么它的索引树就是下面这个样子：

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可以发现a在等值的情况下，b值又是按照顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。所以说如果跳过了某一列，索引将部分失效（后面的字段索引失效）；

例子：我们有一张表，把里面三个字段(profession,age,status)创建联合索引。

三个字段按顺序进行查询，并且查看explain具体执行计划

可以看到用上了我们创建的索引，索引长度为54，我们的索引三个字段都用上了。

当我们跳过中间索引进行查询。可以看到同样用上了索引，但此时索引的长度为49-只使用了第一个索引

当我们跳过第一个索引进行查询。可以看到索引失效了，没有使用索引

2.范围查询：联合索引中，出现范围查询（>,<,between,like）,范围查询右侧的列索引将会失效。

在业务查询时候尽量写>=或<=。

3.索引列操作：不要在索引列上进行运算操作，索引将失效。

例子：我们有一张表，里面有一个字段是电话号码，我们要查询电话号码最后两个数字为15的电话号，我们使用字符串分割函数 substring 进行操作,使用 explain查看执行计划,我们看到索引失效了

4.字符串不加引号：字符串类型字段使用时，不加引号，索引将失效。联合索引也是如此。

5.模糊查询：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊查询匹配，索引失效。

6.or连接的条件：用or分隔开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及到的索引都不会用到。

7.如果MySQL评估使用索引比全表扫描更慢，则不使用索引。

下面是一张表数据：

我们看到，当扫描的数量占了大部分，扫描启动了索引

我们看到，当扫描的数量占了小部分，扫描没启动索引，直接使用了全表扫描。IS NOLL和 IS NOT NULL同样如此。

8.SQL提示：SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

举个例子：一张表table中有三个字段(A,B,C)我们根据这三个字段创立联合索引'abc'，并且根据A单独创建一个索引a.我们使用查询语句查询a字段并使用执行计划explain:explain select * from table where a='xxx'; 结果使用的是联合索引'abc'。那么如果我们想使用索引a有没有办法呢？

在执行语句加入下面三个SQL提示即可：第一个是建议使用这个索引，第二个是忽略这个索引，第三个是强制使用这个索引

9.覆盖索引：尽量使用覆盖索引（查询使用了索引，并且返回的列，在该索引中已经全部能找到）

前缀索引： 当字段类型为字符串（varchar,text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约空间。

语法： CREATE INDEX 索引名 ON 表名(字段名(n));

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性时1，这是最好的索引选择性。

10.单列索引和联合索引的选择

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了很多列。

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议联合索引而非单列索引。

七：索引设计原则