浅谈Mysql索引

最新推荐文章于 2024-07-09 11:09:43 发布

沙滩上的

最新推荐文章于 2024-07-09 11:09:43 发布

阅读量348

点赞数 3

文章标签： mysql 学习数据库

本文链接：https://blog.csdn.net/qq_41311691/article/details/125249381

版权

浅谈Mysql索引

1.Mysql引擎：

1.1 Mysql体系结构：

Mysql是一个典型的CS架构体系，即客户端与服务端；其中服务端大体上又分为连接层、服务层、引擎层与存储层四个部分；

连接层：这一层主要处理来自客户端的连接请求，包括权限的认证、连接池的管理等；
服务层：这一层主要负责sql语句的编写及优化，sql的解析，对缓存进行管理等；
引擎层：这一层一方面负责与底层的文件系统进行交互，对数据进行读取和写入；另一方面，Mysql的存储引擎是可插拔式的，通过各种API与上层的服务层进行交互，屏蔽了不同引擎之间的差距；Mysql中最常用的两种引擎是InnoDB和MyISAM,在Mysql5.5之后InnoDB成为了Mysql默认的存储引擎；
存储层：主要负责将数据存储在文件系统之中，并完成与存储引擎之间的交互；

1.2 存储引擎简介：

存储引擎就是一个存储数据、建立索引、更新或查询数据等技术的实现方式；其中存储引擎是基于表实现的 ，不是基于数据库实现的，所以存储引擎也被称之为表类型；

我们可以在创建表的时候使用关键字 “ENGINE” 来指定存储引擎，用法为：

CREATE TABLE 表名（
	...
) ENGINE = INNODB [COMMENT 表注释];         #其中INNODB为存储引擎的一种

当我们想查看已存在数据库支持的存储引擎种类时，可以使用命令：

SHOW ENGINES;

1.3 存储引擎特点：

不同的存储引擎拥有不同的特点，在这里只介绍InnoDB与MyISAM两种存储引擎的特点；

1.3.1 InnoDB的特点：

InnoDB是一种高可靠、高性能的通用引擎，并且在Mysql5.5之后InnoDB成为了Mysql默认的存储引擎；

InnoDB主要有以下三个特点,分别是：

支持事务，DML操作遵循ACID模型；
行级锁,提高了并发访问的性能；
支持外键约束，保证了数据的完整性与正确性；

那么在InnoDB中是如何对数据进行存储的呢？实际上，InnoDB的逻辑结构中主要包括五个部分：

Tablespace : 表空间，表现为.ibd文件，主要用来存储表的结构、数据和索引；前提是用户启用了参数innodb_file_per_table,查看方式为 show variables like 'innodb_file_per_table';
segment : 段，段主要用来管理多个区（簇），分为数据段（Leaf node segment）、索引段(Non-leaf node segment)、回滚段(Rollback segment)，其中数据段指的就是B+树的叶子节点，索引段指的就是B+树的非叶子节点；
Extent : 区（簇），表空间的单元结构，每个区的大小都是1M。默认情况下一个区一共有64个连续的页；
Page :页，InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次向磁盘申请4-5个区；
Row :行，InnoDB存储引擎数据是按照行进行存放的；

1.3.2 MyISAM的特点：

MyISAM是MySQL早期的默认存储引擎；

MyISAM主要有以下三个特点，分别是：

不支持事务，不支持外键。
支持表锁，不支持行锁。
访问速度快。

MyISAM引擎使用三个文件分别存储一个表的结构、数据、索引：

xxx.sdi : 存储表结构信息
xxx.MYD : 存储数据
xxx.MYI : 存储索引

1.3.3 InnoDB与MyISAM的异同：

事务：InnoDB支持事务，而MyISAM不支持事务；
锁粒度：InnoDB是行级锁，而MyISAM是表级锁；
外键：InnoDB支持外键，而MyISAM不支持外键；
InnoDB和MyISAM索引都是使用的B+树，但他们具体实现不一样，innodb的b+树的叶子节点是存放数据的，myisam的b+树的叶子节点是存放指针的；
存储结构方面：InnoDB使用一个尾缀为.ibd的表空间文件存储表的结构、数据与索引，并且每张表都肯定有这样的一个文件，而MyISAM是表级锁；
innodb不存储表的行数，所以select count( * )的时候会全表查询，而myisam会存放表的行数，select count(*）的时候会查的很快。

2.Mysql索引结构：

Mysql索引（Index）是帮助MySQL高效获取数据的数据结构，也就是说nysql索引就是一种数据结构；

2.1 索引的优缺点：

优点	缺点
能够提高数据检索的效率，降低数据库IO的成本	建立索引也是需要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引可以提高查询的效率，但是同时降低了DML操作的效率

2.2 Mysql常见的索引：

Mysql的索引是在存储引擎实现的，不同的存储引擎具有不同的结构，常见的有以下几种：

B+Tree索引：最常见的索引类型，大部分引擎都支持该索引。
Hash索引：底层数据结构是利用哈希表实现的，只支持精确匹配索引列的查询才有效，精确匹配下效率要比B+Tree效率高，不支持范围查询。
R-Tree（空间索引）：空间索引是MyISAM引擎的一个特殊的索引类型，主要用于存储地理空间数据类型，通常使用较少。
Full-text（全文索引）：全文索引是一种通过建立倒排索引，快速匹配文档的方式。注意Innodb引擎是在Mysql5.6版本之后支持该索引的，之前是不支持的，Myisam一直是支持该索引的。

2.3 Mysql选择B+Tree作为索引的原因：

背景:

二叉树：二叉树在顺序插入的时候，会形成一个单向链表，查询效率大大降低，而且在大树据量的情况下，二叉树的层级较深，检索速度慢。
红黑树：红黑树是一种变异的二叉树，虽然红黑树可以通过自选降低树的层级，但是在数据量比较大的情况下，红黑树还是会出现上述二叉树的情况，即层级较深，检索速度慢。
B-Tree：B-Tree也叫多路平衡查找树，B-Tree的最大特点就是：
- 在B-Tree的节点中可以存储多个数据节点（索引+数据）与指针（指向下一个子节点），且指针的数量总是要比key的数量多一个，每一个非叶子节点可以拥有多个子节点。
- B-Tree在节点中同时存储数据节点的索引与数据。
- 在每个节点存储的数据节点达到阈值的时候，B-Tree会进行分裂对树重新进行平衡。
- 相比较于二叉树，B-Tree单个节点存储多个数据节点的方式加大了数据的存储量。
B+Tree：B+Tree是一种变种的B-Tree，相比较于B-Tree，B+Tree对一些方面进行了修改：
- 所有的数据都存储在叶子结点，非叶子节点只存储数据的索引。
- 在叶子节点之间添加一个指向相邻叶子结点的链表指针，在叶子节点之间形成双向链表，提高了区间访问性能；
Hash：哈希索引就是采用一定的hash算法，将键值换算成hash值，然后映射到对应的槽位上边，再存储在hash表中。这种存储方式可能会造成哈希冲突或者叫哈希碰撞，即如果两个（或多个）键值在经过hash运算之后产生了相同哈希值，映射到了一样的槽位上。解决这种问题最常用的一种方法就是：在哈希槽之后添加一个链表存储发生哈希碰撞的数据。

结论：

总的来说， Mysql选择B+Tree作为索引的原因有以下几点：

相比较于二叉树，层级更少，搜索效率更高，存储的数据量更大；
相对于B-Tree，数据只存储在叶子节点中，非叶子节点只存储节点的索引；由于mysql中每一页的大小都是固定的，这样一来使用B+Tree非叶子节点可以存储更多的索引，从而使数据的存储能力更强。一般3层的B+Tree存储的数据量就可以到达2000W左右；
相对于Hash索引，B+Tree支持范围匹配与排序操作；

在这里给大家推荐一个学习数据结构的网站：Data Structure Visualization (rmboot.com)

3.Mysql索引分类：

在Mysql中，索引主要有以下几种：

主键索引：该索引是针对表中主键进行创建的索引，特点就是在创建主键的时候会默认创建索引，而且一张表中只有一个主键索引，关键字是 PRIMARY；
唯一索引：唯一索引是为了避免表中某一列数据值重复，一张表中可以创建多个，关键字是 UNIQUE;
普通索引：最基本的索引，没有任何限制；
全文索引：全文索引查找的是文本中的关键词，不是比较索引中的值，类似于一个搜索引擎的作用，一张表中可以创建多个，关键字是FULLTEXT;
联合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则；
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL使用SPATIAL关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引。
前缀索引：当字段的长度超出索引限制的时候，可以选取字段的部分前缀创建索引，注意在选择的时候要保证全列索引选择性越高越好，所谓的选择性是指不重复的索引值（基数）和数据表的记录总数的比值；

在InnoDB中，按照索引的存储形式，又可以将索引分为：

聚集索引（Clustered Index）：将数据存储与索引放到了一起，索引结构的叶子节点保存了行数据。在一张表中该索引必须有，且只能有一个；
二级索引（Secondary Index）：将索引与数据分开存储，索引结构的叶子节点关联的是对应的主键，在一张表中二级索引可以存在多个；

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。
如果主键不存在，将使用第一个唯一索引作为聚集索引。
如果表既没有主键也没有合适的唯一索引，InnoDB会自己生成一个rowid作为隐藏的聚集索引。

索引使用建议：

在使用索引进行数据的查询的时候建议尽量只返回索引包含的字段，不返回索引之外的字段。如果只返回索引包含的字段，在进行一次二级索引之后就可以返回全部的数据，效率较高。如果要返回索引包含字段之外的字段，会在进行一次二级索引之后根据得到的id进行一次聚集索引，最终拿到数据，这个过程叫做进行了一次回表查询，是比较耗费资源的；

回表查询的定义是：InnoDB引擎中，非主键索引查找数据时需要先找到主键，再根据主键查找具体行数据，这种现象叫回表查询。

4.Mysql索引语法:

索引可以在创建表的时候进行指定，也可以在建表之后对表进行修改添加索引，下边就是上述几种索引的创建方式:

主键索引 :

CREATE TABLE 表名(
    `id` int not null AUTO_INCREMENT comment '主键',
	...，
    PRIMARY KEY (`id`)
);

唯一索引 :

#语法：
1. CREATE UNIQUE INDEX 索引名 ON 表名(列名);      #建表后添加索引
2. ALTER TABLE 表名 ADD UNIQUE 索引名 ON (列名);   #修改表结构添加索引
3. CREATE TABLE 表名(
	...
    UNIQUE 索引名 (列名)
);                                               #建表时添加索引

#示例：
alter table user add unique idx_phone(`phone`);      #为user表中的phone字段添加名为idx_phone的唯一索引

普通索引 :

#语法:
1.CREATE INDEX 索引名 ON 表名(列名);
2.ALTER TABLE 表名 ADD INDEX 索引名 ON (列名);
3.CREATE TABLE 表名(
	...
    INDEX 索引名 (列名)
);

全文索引 :

#语法:
1.CREATE FULLTEXT INDEX 索引名 ON 表名(列名);
2.ALTER TABLE 表名 ADD FULLTEXT 索引名(列名);
3.CREATE TABLE 表名(
	...
    FULLTEXT (列名)
);

联合索引 :

#语法:
1. CREATE INDEX 索引名 ON 表名（列1,列2,列,...）;
2. ALTER TABLE 表名 ADD INDEX 索引名 (列1,列2,列3...);

空间索引 :

#语法:
alter table user add SPATIAL KEY `idx_gis` (`gis`) ;     #gis为user表中的'空间位置信息'字段；

前缀索引：

# 全列索引计算查询方式：
SELECT COUNT(DISTINCT left(字段名,长度)) / COUNT(DISTINCT 字段名) FROM system_user;              #在限定范围内取出来的值越大越好，该值对应的'长度'就可以用来创建前缀索引；

# 语法：
alter table 表名 add index 索引名(字段名(长度));

查看索引：

SHOW INDEX FROM 表名;

删除索引：

DROP INDEX 索引名 ON 表名;

5.SQL性能分析：

当我们要进行sql语句的优化时，首先要知道sql语句具体的执行过程以及过程中执行的效率，sql在执行中是否用到了索引等问题，以下就是在分析SQL性能的时候常用到的几种方法；

在Mysql客户端连接成功之后，我们可以使用以下命令对当前数据库最近的使用状况进行查询：

SHOW GLOBAL|SESSION STATUS LIKE 'Com_______';    #'_'一共有7个，表示对当前的7个状态进行查看

5.1 慢查询日志：

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志。慢查询日志的功能Mysql默认是没有开启的，需要我们自己在Mysql的配置文件(/etc/my.cnf)中添加以下信息：

# 开启Mysql慢查询日志开关
slow_query_log = 1
# 设置慢查询日志的时间为几秒，当sql执行时间超过该时间的时候就会被视为是一条慢查询，添加慢查询日志
slow_query_time = 10

配置完后执行以下命令重启mysql服务器使修改生效:

systemctl restart mysqld;

查看配置是否生效：

show variables like 'slow_query_log';

#当结果显示'slow_query_log'的值'ON'时代表慢查询的配置已经生效了；

接下来我们可以在 /var/lib/mysql/localhost-slow.log 中查看执行的sql中的慢查询sql了；

5.2 profile查看：

show profile操作可以帮助我们了解sql在执行得到时候将时间都花费在了什么地方，从而对sql进行修改优化；

查看Mysql是否支持profile操作：

select @@have_profiling;              #当查询结果为'YES'的时候代表支持

在mysql中profile功能默认是关闭的，我们需要手动将他打开，命令如下：

set profiling = 1;

在我们执行一系列sql操作之后可以通过一下命令对指令的耗时进行查看：

# 查询每一条sql耗时基本情况：
show profiles;
# 查看指定query_id的sql语句每个执行阶段的耗时情况：
show profile for query query_id;
# 查看指定query_id的sql语句CPU使用情况
show profile cpu for query query_id;

5.3 explain执行计划：

explain 或者 desc命令能够获取mysql执行DQL语句得到信息，包括在执行过程中表是如何连接和连接的顺序；

# 语法
EXPLAIN|DESC SELECT 字段列表 from 表名 where 条件；

在这里插入图片描述

字段含义分析：

字段	含义
id	select查询的序列号，表示查询中执行select子句或者操作表的顺序；当id相同的时候，执行顺序从上到下；当id不同的时候，id值越大越先执行。
select_type	表示 select 的类型，常见的取值有 simple（简单表，即不使用表连接或者子查询）、primary（主查询，即外层的查询）、union（union联合查询中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等
table	查询涉及到的表
partitions	查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表中的分区情况。
type	表示连接类型，性能由好到差的连接类型为： NULL 一般查询时不访问任何表才会出现 system 访问一张系统表 const 主键或者唯一索引 eq_ref 知道查询结果只有一个 ref 使用了索引但是索引列值不唯一 range 有范围的索引扫描 index 按照索引顺序进行全表扫描，没比全表扫描快多少 all 全表扫描
possible_keys	显示可能应用在这张表上的索引，一个或多个
key	实际使用的索引，如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
ref	当使用常量等值查询，显示`const`，当关联查询时，会显示相应关联表的关联字段如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为`func` 其他情况`null`
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered	表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好
Extra	不适合在其他列中显示的信息，explain 中的很多额外的信息会在 Extra 字段显示

在这么多字段中，我们一般关注的是type、possible_keys、key、key_len、Extra五个字段，其他的字段作为了解即可；

6.索引的使用：

索引在使用的时候需要满足一些条件才能很好地发挥它的作用，而我们在sql优化的时候也大部都是根据这些原则对sql进行优化的；

6.1 最左前缀法则：

如果索引指定了多列（联合索引），就要遵守最左前缀法则。最左前缀法则指的就是查询从索引的最左列开始，并且不跳过索引中的列。但是如果在使用的过程中遇到了以下几种情况，索引就会出现失效：

如果跳过了某一列，索引将部分失效（后面的字段索引失效）。
联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。解决方法就是在业务允许的情况下将（>,<）改为（>=,<=）,这样就可以继续走索引了。
不要在索引列上进行运算操作，否则索引将失效。
在使用字符串类型的字段作为索引时，不加引号，索引将失效。
在使用模糊匹配的时候，如果仅仅是尾部模糊匹配，索引不会失效，但是如果是头部模糊匹配，索引将失效。也就是说 ‘’%‘’ 在前，索引失效，‘’%‘’ 在后，索引不失效。
用or分隔开的条件，如果or前的条件中得到列有索引，但是后边的列中没有索引，那么涉及到的索引将不会被用到。也就是只有or两侧字段都是索引列时才会走索引。
如果mysql评估使用索引比全表慢的情况下，则不会用到索引。

6.2 索引使用建议：

使用sql提示，sql提示是优化数据库最重要的一个手段，简单来说就是在SQL语句中加入一些认为的提示来达到优化操作的目的。一般这一操作有三种：
- use index : 使用指定的索引，建议性质，具体用不用取决于mysql
```
explain select 字段列表 from 表名 use index(索引名) where 条件;
```
- ignore index : 忽视指定的索引，使用其他的索引
```
explain select 字段列表 from 表名 ignore index(索引名) where 条件;
```
- force index : 强制使用指定的索引
```
explain select 字段列表 from 表名 force index(索引名) where 条件;
```
使用覆盖索引，尽量使用覆盖索引（如果查询使用了索引，并且需要返回的列在该索引中可以全部找到），减少select * 的操作，减少回表查询的概率；

当使用关键字explain对sql语句进行分析时，在Extra字段中出现以下结果，代表了不同的执行结果：
- using index condition : 查找使用了索引，但是需要回表查询数据；
- using where; using index : 查找使用了索引，但是需要的数据在索引中都可以找到，不需要回表查询；
使用前缀索引，当字段的类型为字符串且长度超出索引限制的时候，可以选取字段的部分前缀创建索引，节约索引空间，提高索引效率。具体的前缀索引创建语法参考前边的Mysql索引语法一节；