Mysql索引相关知识

最新推荐文章于 2024-06-12 13:18:40 发布

你要不要认识我

最新推荐文章于 2024-06-12 13:18:40 发布

阅读量110

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/mengxinluguo111/article/details/130517124

版权

一、索引概述

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

这里举个例子，当没有索引的时候需要去执行这么一条语句：select * from user where age =18;数据库只能去进行全表扫描。而当有一个索引结构类似于二叉排序树的时候，他的查询速率就会很高。

二、索引优缺点

优点：

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

缺点：

索引是要占用磁盘空间的。
索引虽然提高了查询的效率，但同时也增加了增删改操作的时间

三、索引的结构

下图中展示了mysql中的索引

由于索引是在存储引擎层实现的，因此不同的存储引擎会有不同的索引结构。

3.1 B+Tree

下面可以看下Mysql中的B+Tree的结构，提示：Mysql是对B+Tree经过优化的

也可以分析一下他的特点

所有的数据都会出现在叶子节点
所有的叶子节点形成一个双向循环链表
所有非叶子节点仅仅起到索引作用

3.2 Hash

哈希结构索引，就是通过哈希算法，把键值换算到新的hash值，直接进行映射，然后存储再hash表中。这里就不多赘述了。

特点：

hash索引只可以用于对等比较，例如（=，in)，不支持范围查询（between,>,<,...)
无法完成排序
查询的效率很高，在没有hash冲突时，只需要一次检索就可以了。

在mysql中支持hash索引的是Memory存储引擎。InnoDB中也具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在特定的条件下自动构成的。

四、索引分类

在Mysql数据库中，按照索引的具体类型分类：

而根据索引的存储形式，又分类为：

聚集索引选取规则：

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

特点：

聚集索引的叶子节点下挂的是这一行的数据
二级索引的叶子节点下挂的是该字段值对应的主键值

4.1 回表查询

当查找数据时先去二级索引查找数据，找到对应的主键值，然后再去聚集索引，根据主键值获取数据。把这样的过程称为回表查询。

因此可以知道，直接使用聚集索引的效率更高一点。

五、索引语法

（1）创建索引

create [unique|fulltext] index index_name on table_name (index_col_name,...);

（2）查看索引

show index from table_name;

（3）删除索引

drop index index_name on table_name;

六、SQL性能分析

6.1 sql执行频率

-- session 是查看当前的会话;
-- global 是查询全局数据;

show global status like 'Com_______'; -- 7个下划线

通过这个表格，可以得到该数据库是以查询为主还是以增删改为主。

6.2 慢查询日志

Mysql中的慢查询日志默认是不开启的。可以先去查询一下：

show variables like 'slow_query_log';

没有开启的话要去，mysql的配置文件（/etc/my.cnf)中配置如下信息；

# 开启Mysql慢日志查询开关

slow_query_log=1;

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

long_query_time=2;

配置完毕后，重新启动Mysql服务器进行测试

systemctl restart mysqld

6.3 profile详情

show profiles 能够在SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前Mysql是否支持profile操作

select @@have_profiling;

如果没有开启

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;

6.4 explain

explain是在日常中经常会使用到的，它可以展示更多的信息，让你可以直观的看到具体的SQL执行情况。

-- 直接在select语句之前加上关键字 explain /desc
explain select 字段列表 from 表名 where 条件;

explain中各个字段的含义

七、索引使用

使用索引会大大的提高查询的效率。

7.2 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。

最左前缀法则指的是从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引会部分失效。（后面的字段索引失效）

例如在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession， age，status。对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

具体希望大家自己下去多尝试，多发现；

通过explain语句,调换顺序，观察索引字段的长度的变化。

explain select * from tb_user where profession ='高等数学' and age > 30 and status ='0';

7.3 范围查询

在联合索引中，出现范围查询（> , < ) 范围查询右侧的列索引失效。

因此在业务允许的情况下，尽可能的使用>= 或 <=这样的查询。

7.4 索引失效情况

在索引列上进行运算操作
字符串类型字段使用时，不加引号，索引将会失效。
出现头部模糊匹配查询，索引失效
or连接的条件，如果or的前面又索引,后面没有索引，那么涉及的索引都不会用到。
数据分布影响，当全表查询比走索引快时，不会使用索引。

7.5 SQL提示

(1) use index : 建议使用哪一个索引来完成此次查询（只是建议，不一定会使用，mysql内部会进行评估后再选择）

explain select * from tb_user use index(idx_user_pro) where profession='高等数学';

(2) ignore index :忽略指定的索引

(3)force index: 强制使用索引

7.6 覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，再该索引中全部可以找到。

这里可以通过explain的extra清晰的得到是否是覆盖索引。

7.7 前缀索引

一般是字段的类型为字符串，且字段很长，这会让索引变得很大，浪费大量的磁盘IO，这时，可以把字符串的一部分前缀建立索引，从而提高索引效率。

create index idx_xxxx on table_name(column(n));

那么如何确定适合的长度呢？

select count (distinct substring(字段 , 1,5)) / count(*) from tb_ user; 
-- 5是你要的前缀长度

7.8 单列索引与联合索引

这里直接建议，存在多个查询条件时，能建立联合索引就建立联合索引。

哪怕有两个单列索引，在一同查询时，只会使用一个，还是会走回表查询.

八、索引设计原则

针对数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件，排序，分组操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，则索引的效率越高
如果时字符串类型，字段的长度较长时，可以使用前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表
要控制索引的数量，并不是多多益善。
如果索引不能存储null值，请用非空约束，让mysql内部的优化器知道每列是否包含null值，可以更好的确定哪个索引更加有效。