运维开发网

你知道MySQL的索引吗

运维开发网 https://www.qedev.com 2022-07-21 22:07 出处:网络
这篇文章主要为大家详细介绍了MySQL的索引,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下,希望能够给你带来帮助

这篇文章主要为大家详细介绍了MySQL的索引,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下,希望能够给你带来帮助


一、索引介绍

索引是帮助MySQL高效获取数据的数据结构(顺序)。除了数据,数据库系统还维护符合特定搜索算法的数据结构。这些数据结构以某种方式引用(指向)数据,以便可以在这些数据结构上实现高级搜索算法。这个数据结构是一个索引。


二、索引优缺点

优势:

提高数据检索的效率,降低数据库的io成本,通过索引列对数据进行排序,降低数据排序的成本,减少CPU的消耗。

缺点:

列还会占用空之间的空间。索引大大提高了查询效率,但同时也降低了更新表的速度。比如插入、更新、删除表时,效率降低。


三、索引结构

我们通常所说的索引,除非特别说明,都是指按B+树结构组织的索引。

+b树索引:最常见的索引类型,大多数引擎都支持b+树索引。

Hash:底层数据结构由哈希表实现,只有与索引列完全匹配的查询才有效,不支持范围查询。

r-tree(空inter-index):空inter-index是MyISAM引擎的一种特殊索引类型,主要用于geographic 空 inter数据类型,通常使用较少。

全文(全文索引):它是一种通过建立倒排索引来快速匹配文档的方法。类似Lucene,Solr,ES



1. 经典B+树


看结构和B树。B+树和B-树的区别在于:

1.所有的元素都会出现在叶节点中,非叶节点主要起到索引的作用,而叶节点是用来存储数据的。

2.在B+树的数据结构中,叶子节点形成一个单向链表,每个节点会通过一个指针指向下一个元素。


2. MySQL中B+树索引


MySQL索引数据结构优化了经典的b+树。在原有B+树的基础上,加入指向相邻叶节点的链表指针,形成具有顺序指针的B+树,提高了区间访问的性能。叶子节点首尾相连,便于范围搜索和排序。


3. Hash索引

Hash就是用一定的哈希算法将键值转换成新的哈希值,映射到相应的槽位,然后存储在哈希表中。

如果两个(或两个以上)键值映射到同一个槽,就有哈希冲突(也叫哈希冲突),可以用链表解决。

特点:

1.哈希索引只能用于对等比较(=,in)和范围查询(between,gt;,lt;,...)

2.无法使用索引来完成排序操作。

3.查询效率高,通常只需要一次搜索,效率通常高于B+树索引。

存储引擎支持:

在MySQL中,内存引擎支持哈希索引,而InnoDB具有自适应哈希功能。哈希索引是存储引擎在指定条件下根据B+树索引自动构建的。


4. 为什么InnoDB选择B+树索引?   

与二叉树相比,它具有更少的层次和更高的搜索效率。

对于B树,无论是叶节点还是非叶节点,都会保存数据,导致一个页面中存储的键值减少,指针减少。同样要保存大量数据,只能增加树的高度,导致性能下降;

与哈希索引相比,哈希索引只支持等价匹配,而B+树支持范围匹配和排序操作。


四、索引分类


在InnoDB存储引擎中,根据索引的存储形式,可以分为以下两种类型:

聚簇索引:数据存储和索引放在一起,索引结构的叶节点存储行数据;必须有且只有一个。

二级索引:数据与索引分开存储,索引结构的叶节点与对应的主键相关联;可以有多个。

聚集索引选择规则:

如果有主键,则主键索引是聚集索引。

如果没有主键,第一个唯一索引将用作聚集索引。

如果表没有主键或者没有合适的惟一索引,InnoDB将自动生成一个rowid作为隐藏的聚集索引。


如果是(非主键)条件查询,则采用回表查询,即通过二级索引找到主键(聚集索引),再通过聚集索引找到该行的数据。

InnoDB主键索引的B+树的高度是多少?

假设:

一行数据的大小为1k,一页可以存储16行这样的数据。InnoDB的指针占用了空的6字节空间。即使主键是bigint,占用的字节数也是8。

高度2:

N*8+(n+ 1)*6= 16*1024,算出n约为1170。

1171*16= 18736

高度是3:

1171 * 1171 * 16 = 21939856


五、索引语法

创建索引

对表名创建[ UNIQUE | FULLTEXT ]索引索引名(index_ _col_ name,..) ;

查看索引

显示table_ name的索引;

删除索引

请删除table_ name上的索引index _ name


六、SQL性能分析


1. SQL执行频率

MySQL客户端连接成功后,可以通过show [session|global] status命令提供服务器状态信息。您可以通过以下指令查看当前数据库的插入、更新、删除和选择的访问频率:

show global status like 'Com_______';


2. 慢查询日志

慢速查询日志记录所有执行时间超过指定参数(long_ query_ _time,单位:秒,默认为10秒)的SQL语句的日志。

MySQL的慢速查询日志默认不开启。您需要在MySQL (/etc/my.cnf)的配置文件中配置以下信息:

#开启MySQL慢日志查询开关slow_query_log=1#设置慢日志的时间为2秒,SQL 语句执行时间超过2秒,就会视为慢查询,记录慢查询日志long query time=2

配置完成后,按照以下说明重新启动MySQL服务器进行测试,并检查慢速日志文件/var/lib/MySQL/localhost-slow . log中记录的信息。

当操作时间超过2s时,将记录在慢速查询日志中。


3. profile详情

显示概要文件可以帮助我们了解在进行SQL优化时时间花在了哪里。通过have_ profiling参数,可以看出当前的MySQL是否支持profile操作:

#查看当前数据库是否支持profile操作select @@have_profiling

默认情况下,分析是关闭的。可以通过set语句在会话/全局级别启动分析:

#开启profilingset profiling = 1;#查看每一条SQL 的耗时基本情况show profiles;#查看指定query_ id的SQL语句各个阶段的耗时情况show profile for query query_ id;#查看指定query_ id的SQL语句CPU的使用情况show profile cpu for query query_id;


4. explain执行计划

EXPLAIN或DESC命令获取有关MySQL如何执行SELECT语句的信息,包括在SELECT语句执行期间表是如何连接的以及连接的顺序。语法:

#直接在select语句之前加,上关键字explain / descEXPLAIN SELECT 字段列表FROM 表名WHERE 条件;


解释执行计划中每个字段的含义:

Id:

select查询的序号表示查询中select子句或操作表的执行顺序(id相同,执行顺序是从上到下;id不同,值越大,执行越早)。

选择类型:

选择的类型。常见的值有简单(简单表,即没有表连接或子查询)、主(主查询,即外部查询)、联合(第二个或后面的查询语句UNION (UNION)、子查询(子查询包含在SELECT/WHERE之后)等。

类型:

指示连接类型。性能从好到差的连接类型有NULL、system、const、eq_ref、ref、range、index和all。

可能的关键字:

显示可能应用于此表的一个或多个索引。

关键:

实际使用的索引,如果为空,则不使用该索引。

密钥长度:

指示索引中使用的字节数。该值是索引字段的最大可能长度,而不是实际长度。在不损失精度的前提下,长度越短越好。

行数:

MySQL认为必须查询的行数是innodb engine的表中的一个估计值,不一定总是准确的。

已过滤:

指示结果返回的行数占要读取的行数的百分比。过滤值越大越好。


七、索引使用


1. 索引效率

当数据量极大时,在建立索引之前,执行SQL和查询未索引字段SQL需要花费大量时间。

为字段创建索引后。

再次执行同一个SQL语句,会大大减少SQL的时间消耗。


2. 联合索引

最左边的前缀规则

如果对多个列进行索引(联合索引),应该遵循最左边的前缀规则。最左侧前缀规则意味着查询从索引中最左侧的列开始,并且查询必须包含最左侧的列(否则,它将全部失败)而不跳过索引中的列。

如果跳过一列,索引将部分无效(后面字段的索引将无效)。

范围查询

在联合索引中,范围查询(gt;,lt;),范围查询右侧的列索引无效,一般使用gt;=或lt;=这种情况可以有效避免。


3. 索引失效

列操作

不要对索引列进行操作,索引将无效。

不带引号的字符串

当使用type字段时,如果没有引号,索引将无效。

模糊查询

如果只是尾部模糊,索引就不会失效。如果标题模糊,则索引无效。

或连接条件

使用由or分隔的条件时,如果条件中or之前的列有索引,而之后的列没有索引,则不会使用所有涉及的索引。只有当双方都使用该指数时,该指数才会生效。

数据分布影响

如果MySQL评估使用索引的速度比全表扫描慢,它将不会使用索引,索引将无效。


4. SQL提示

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

# use index:explain select * from tb_name use index(索引名) where profession= 'xxxx';# ignore index:explain select * from tb_name ignore index(索引名) where profession='xxxx';# force index:explain select * from tb_name force index(索引名) where profession='xxxx';


5. 覆盖索引

尽量覆盖索引(查询使用索引,需要返回的列都已经可以在索引中找到),减少select *。

对额外字段中出现的数据进行分析:

使用索引条件:索引用于搜索,但数据需要查询回表中。

使用where使用索引:索引用于搜索,但是所有需要的数据都可以在索引列中找到,所以不需要回到表中查询数据。


6. 前缀索引

当字段类型为字符串(varchar、文本等)时。),有时候需要索引一个很长的字符串,会让索引变得很大。查询时会浪费大量磁盘IO,影响查询效率。此时可以只对字符串的一部分前缀进行索引,这样可以大大节省索引空之间的空间,提高索引效率。

#语法create index idx_xxx on table_ name(column(n)) ;#前缀长度可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。# 求取选择性 select count(distinct email)/ count(*) from tb_name ; select count(distinct substring(email,1 ,5)) / count(*) from tb_name ;


7. 单列索引与联合索引

单列索引:也就是说,一个索引只包含一列。

联合索引:也就是说,一个索引包含多个列。

在业务场景中,如果有多个查询条件,在考虑索引查询字段时,建议建立联合索引(效率更高,可以有效避免一些回表查询),而不是单列索引。

在多条件联合查询中,MySQL优化器会评估哪个字段的索引效率更高,会选择这个索引来完成这个查询。创建联合索引时,会有单列索引干扰,所以我们可以指定联合索引查询。

联合指数情况:



八、索引设计原则

1.对大量数据和频繁查询的表进行索引。

2.索引常用作查询条件(where)、排序(order by)和分组(group by)的字段。

3.尽量选择区分度高的列作为索引,尽量建立唯一的索引。区分度越高,使用索引的效率越高。

4.如果是长度较长的字符串型字段,可以根据字段的特点建立前缀索引。

5.尽量使用联合索引,减少单列索引。查询时,联合索引往往可以覆盖索引,节省存储空,避免回表,提高查询效率。

6.要控制索引的数量,索引越多越好。索引越多,维护索引结构的成本越大,会影响添加、删除、修改的效率。

7.如果索引列不能存储空值,则在创建表时使用NOT NULL来约束它。当优化器知道每个列是否包含空值时,它可以更好地确定哪个索引对查询最有效。


总结

本文到此为止。希望能帮到你,也希望你能多多关注源搜网的更多内容!


0

精彩评论

暂无评论...
验证码 换一张
取 消