发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

Mysql进阶篇（二）之索引

吴剑明

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
显示全部楼层
一. 索引概述

1. 介绍

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

2. 演示

表结构及其数据如下：

假如我们要执行的SQL语句为：select * from user where age = 45;
(1). 无索引情况


在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。
(2). 有索引情况

如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构。

此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高了查询的效率。
注：这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，下面会有介绍。
3. 特点

优势劣势提高数据检索的效率，降低数据库的IO成本索引列也是要占用空间的通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低二. 索引结构

1. 简介

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：
索引结构描述B+Tree索引最常见的索引类型，大部分引擎都支持B+树索引Hash索引底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询R-tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少Full-text(全文索引)全文索引是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES上述是MySQL中所支持的所有索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。
索引InnoDBMyISAMMemoryB+tree索引支持支持支持Hash索引不支持不支持支持R-tree索引不支持支持不支持Full-text索引5.6版本之后支持支持不支持2. 二叉树

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

如果主键是顺序插入的，则会形成一个单向链表，结构如下：

所以，如果选择二叉树作为索引结构，会存在以下缺点：

  • 顺序插入时，会形成一个链表，查询性能大大降低。
  • 大数据量情况下，层级较深，检索速度慢
此时可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树，结构如下：

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：

  • 大数据量情况下，层级较深，检索速度慢。
所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而是选择B+Tree，在详解B+Tree之前，先来介绍一个B-Tree。
3. B-Tree

B-Tree，B树是一种多叉路平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5（5阶）的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：


注：树的度数指的是一个节点的子节点格式
特点：

  • 5阶的B树，每一个节点最多存储4个key，对应5个指针。
  • 一旦节点存储的key数量达到5，就会裂变，中间元素向上分裂。
  • 在B树中，非叶子节点和叶子节点都会存放数据。
4. B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：

我们可以看到，两部分：

  • 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  • 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
B+Tree和B-Tree相比，主要有以下三点区别：

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表。
  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL优化之后的B+Tree。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

5. Hash

MySQL中除了支持B+Tree索引，还支持一种索引---Hash索引。
(1). 结构

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

(2). 特点

<ul>
<strong>Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，， 30 and status = '0';[/code]

当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字段是没有走索引的。
  1. CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
复制代码


<strong>当范围查询使用>= 或 = 或  或 = '17799990005'; select * from tb_user where phone >= '17799990015';[/code]


经过测试我们发现，相同的SQL语句，只是传入的字段值不同，最终的执行计划也完全不一样，就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如全表扫描来的快，此时索引就会失效。
接下来，我们再来看看is null 与 is not null 操作是否走索引。
执行如下两条语句：
  1. SHOW INDEX FROM table_name;
复制代码

接下来，我们做一个操作将profession字段值全部更新为null。

然后，再次执行上述的两条SQL，查看SQL语句的执行计划。

最终我们看到，一摸一样的SQL语句，先后执行了两次，结果查询计划是不一样的，为什么会出现这种现象，这是和数据库的数据分布有关系。查询时MySQL会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。因此，is null、is not null是否走索引，得具体情况具体分析，并不是固定的。
5. SQL提示

目前tb_user表的数据情况如下：

索引情况如下：

把上述的 idx_user_age，idx_email 这两个之前测试使用过的索引直接删除。
  1. DROP INDEX index_name ON table_name;
复制代码
A. 执行SQL：
  1. create table tb_user(
  2.         id int primary key auto_increment comment '主键',
  3.         name varchar(50) not null comment '用户名',
  4.         phone varchar(11) not null comment '手机号',
  5.         email varchar(100) comment '邮箱',
  6.         profession varchar(11) comment '专业',
  7.         age tinyint unsigned comment '年龄',
  8.         gender char(1) comment '性别 , 1: 男, 2: 女',
  9.         status char(1) comment '状态',
  10.         createtime datetime comment '创建时间'
  11. ) comment '系统用户表';
  13. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
  14. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
  15. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
  16. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
  17. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
  18. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
  19. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动 化', 27, '1', '2', '2001-08-16 00:00:00');
  20. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工 程', 27, '1', '0', '2001-06-12 00:00:00');
  21. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
  22. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
  23. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
  24. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
  25. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
  26. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
  27. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
  28. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
  29. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
  30. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
  31. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
  32. INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');
复制代码

查询走了联合索引。
B. 执行SQL，创建profession的单列索引：
  1. CREATE INDEX idx_user_name ON tb_user(name);
复制代码

C.创建单列索引后，再次执行A中的SQL语句，查看执行计划，看看到底走哪个索引。

测试结果，我们可以看到，possible_keys中idx_user_pro_age_sta, idx_user_pro这两个索引都可能用到，最终MySQL选择了idx_user_pro_age_sta索引。这是MySQL自动选择的结果。
但是，我们可以借助于MySQL的SQL提示来自己指定使用哪个索引。
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
1). use index：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
  1. CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
复制代码

2). ignore index：忽略指定的索引。
  1. CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
复制代码

3). force index：强制使用索引。
  1. CREATE INDEX idx_email ON tb_user(email);
复制代码

6. 覆盖索引

*尽量使用覆盖索引，减少select 。覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
接下来，我们来看一组SQL的执行计划，看看执行计划的差别，然后再来具体做一个分析。
  1. -- session 是查看当前会话;
  2. -- global 是查询全局数据;
  4. SHOW GLOBAL STATUS LIKE 'Com_______';
复制代码

从上述的执行计划我们可以看到，这四条SQL语句的执行计划前面所有的指标都是一样的，看不出来差异。但是此时，我们主要关注的是后面的Extra，前面两条SQL的结果为 Using  Where; Using Index；而后面两条SQL的结果为：Using index condition。
Extra含义Using where; Using Index查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。Using index condition查找使用了索引，但是需要回表查询数据。因为在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。所以当我们查询返回的数据在 id、profession、age、status之中，则直接走二级索引直接返回数据了。如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。
SQL的执行过程：
A. 表结构及索引示意图：

id是主键，是一个聚集索引。name字段建立了普通索引，是一个二级索引（辅助索引）。
B. 执行SQL：select * from tb_user where id = 2;

根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
C. 执行SQL：select id, name from tb_user where name = 'Arm';

虽然是根据name字段查询，查询二级索引，但是由于查询返回的字段为id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。
D. 执行SQL：select id, name, gender from tb_user where name = 'Arm';

由于在name的二级索引中，不包含gender，所以需要两次索引扫描，也就是需要回表查询，性能相对比较差一点。
问题1：一张表，有四个字段(id, username, password, status)，由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：
select id, username, password from tb_user where username = 'itcast';
答案：针对于username，password建立联合索引，sql为 create index idx_user_name_pass on tb_user(username, password);
这样可以避免上述的SQL语句，在查询过程中，出现回表查询。
7. 前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。
(1). 语法
  1. show variables like 'slow_query_log';
复制代码
案例1：为tb_user表的email字段建立长度为5的前缀索引。
  1. # 开启MySQL慢日志查询开关
  2. slow_query_log=1
  4. # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
  5. long_query_time=2
复制代码

(2). 前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
  1. systemctl restart mysqld
复制代码
(3). 前缀索引的查询流程


8. 单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
我们先来看看tb_user表中目前的索引情况：

在查询出来的索引中，既有单列索引，又有联合索引。
接下来，我们来执行一条SQL语句，看看其执行计划：

通过上述执行计划我们可以看出来，在and连接的两个字段phone、name上都是有单列索引的，但是最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的。
紧接着，我们再来创建一个phone和name字段的联合索引来查询一下执行计划。
  1. select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
  3. select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时 13.35sec
复制代码

此时查询时，就走了联合索引，而在联合索引中包含了phone、name的信息，在叶子节点下挂的是对应的主键id，所以查询是无需回表查询的。
如果查询使用的是联合索引，具体的结构示意图如下：

七. 索引设计原则

1. 针对于数据量较大且查询比较频繁的表建立索引。

2. 针对于常作为查询条件（where）、排序(order by)、分组(group by)操作的字段建立索引。

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

4. 如果是字符串类型的字符，字段的长度较大，可以针对于字段的特点，建立前缀索引。

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

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

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

更多mysql学习请关注微信公众号”云哥技术yun3k”，回复”mysql学习”，免费领取mysql全套学习资料。


来源:https://www.cnblogs.com/yun3k/p/17558461.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: mac使用podman替代docker使用详解

下一篇: shell脚本-MySQL数据库备份

发表于 2023-7-16 23:34:29

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4