AI 助理
备案控制台
开发者社区 数据库 文章 正文

132.【MySQL_进阶】（二）

2023-09-25 76
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介： 132.【MySQL_进阶】

(二)、索引 (Index)

1.MySQL安装(Linux安装)

(1).登入并连接阿里云的MySQL

安装之后，我们要进行密码验证。执行Linux命令 cd /www/server/mysql 进入mysql的文件夹。并执行 mysql -u root -p 回车并输入密码。

mysql -u root -p

2.索引概述

(1).索引介绍

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

(2).索引演示
  1. 无索引_演示方式

比如说我们要: select *from user where age=45;。

基本算法就是: 从表头到表尾按照顺序进行遍历查找。时间复杂度为O(n)。

  1. 有索引_演示方式

比如说我们要: select *from user where age=45;。

基本算法： 从头节点进行二叉树遍历。二叉搜索树特点: 数字比中间的大走右边，数字比中节点小走左边。

(3).索引优缺点

优点：

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

缺点：

3. 索引列也是要占用空间的。

4. 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update、delete时，效率降低。（二叉树插入顺序插入没有数组快）

3.索引结构_介绍 ⭐

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

(1).四种索引结构
  1. B+Tree索引: 最常见的索引类型，大部分引擎都支持B+树索引。
  2. Hash索引 : 底层数据结构用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
  3. R-Tree(空间索引): 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
  4. Full-text(全文索引):是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucence,Solr,ES。
索引 Innodb MyISAM Memory
B+Tree 支持 支持 支持
Hash ❌ ❌ 支持
R-Tree ❌ 支持 ❌
Full-text 5.6版本之后支持 支持 ❌

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

4.索引结构_BTree (多路平衡查找树)

(1).什么是二叉树

理想化二叉树: 我们插入数据和增加删除数据很遍历

非理想化二叉树:成链表状态插入和删除效率很低

二叉树缺点: 顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级越深，检索速度慢。

红黑树: 大数据量情况下，层级较深，检索速度慢。

(2).BTree

以一颗最大度数(max-degree)为5(5阶)的b-tree为列(每个节点最多存储4个key,5个指针)； N阶的tree为N个指针，N-1个key

(3).BTree 动态演示

动态查看一下我们的B-Tree数据:

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

依次插入数据: 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250

结论: 我们发现我们的数据首先当达到第五个值的时候向上分裂一次，分裂的数据是五个数据的中间值，然后剩下的4个分成2个模块，依次类推。

5.索引结构_B+Tree (BTree的变种)

(1).B+Tree介绍

B+Tree是B树的变种，有着比B树更高的查询性能。

以一颗最大度数(max-degree) 为 4 (4阶) 的B+Tree为列:

  • 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  • 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
(2).B+Tree 动态演示

我们使用数据为: 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250。

(3).B+Tree 与 BTree的区别

B+Tree:

  1. 所有的数据都会出现在叶子节点上。
  2. 叶子节点形成一个单向链表。
  3. 向下分裂。
  4. 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
(4).MySQL下的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问的性能。

6.索引结构_Hash ⭐(Hash冲突)

(1). Hash索引介绍和Hash冲突

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

Hash冲突: 哈希冲突是区块链网络中，两个节点间存在的一种特殊的数据交换方式。在区块链系统中，一个节点的状态会同步地传递给其他节点（或区块），每个区块都包含上一个区块的哈希值和本区块的哈希值。因此，当两个或多个节点之间存在数据交换时就会发生碰撞，这种碰撞称为"冲突"。当发生Hash冲突的时候可以使用链表来解决。

(2).Hash索引特点
  1. Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<)。
  2. 无法利用索引完成排序操作。
  3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引。
(3).存储引擎支持

在MySQL,支持Hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

7.索引结构_思考题

(1).为什么InnoDB存储引擎选择使用B+Tree索引结构?
  1. 相对于二叉树或红黑树来说，层级更少，搜索效率高。
  2. 相对于BTree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
  3. 相对于Hash索引,B+Tree支持范围匹配和排序操作。

8.索引_分类

(1).四种索引分类
分类 含义 特点 关键字
主键索引 针对表中主键创建的索引 默认自动创建，只能有一个 primary
唯一索引 避免同一个表中某数据列中的值重复 可以多有个 unique
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是文本中的关键词，而不是比较索引中的值 可以有多个 fulltext
单列索引 一个字段一个索引的 可以有多个
组合索引 多个字段共同一个索引的 可以有多个

(2).InnoDB下的两种索引存储形式
分类 含义 特点
聚集索引 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个
二级索引 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

聚集索引选取规则:

  1. 如果存在主键，主键索引就是聚集索引。
  2. 如果不存在主键，将使用第一个唯一索引(unique)作为聚集索引。
  3. 如果表中没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

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

比如 select *from user where name='Arm' 这个SQL语句。

  1. 假如name字段添加了二级索引。那么二级索引下面直接挂的就是二级索引的这个字段的数据+对应的主键，所以我们只需要通过一次二级索引全部查询到，不需要回表。
  2. 假如name字段没有添加二级索引。他会先选择走二级索引，因为二级索引存放的是某个字段的主键ID；然后再根据这个ID去聚集索引查找这一行的数据。由二级索引跳到聚集索引的过程我们称为 回表查询。
  3. 有索引的直接拿取这个字段+主键，没有索引的需要回表。
  4. 一个索引对应着一个B+Tree。

9.索引_思考题 ⭐(回表)

(1).以下SQL语句，那个执行效率高?为什么?
select *from user where id=10;
select *from user where name='Arm';
备注: id为主键,name字段创建的有索引。

第一个SQL语句执行效率高，因为这个不会触发回表查询，只需要执行一次聚集查询即可。

(2).InnoDB主键索引的B+tree高度为多高?

假设: 一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的高度，主键即使为bigint,占用字节数为8。

n代表key的数量，n+1代表指针的数量。注意1KB=1024B
n×8+6×(n+1)=16×1024,算出一个n约为1170.
1. 求高度为2:
所以得出一个根节点最多有1171个指针，指向下一层的1117个节点，又因为一个节点等于一行数据，
一行数据大小为16，所以如下:
16*1170=18376
2.求高度为3:
1171*1171*16

高度为三的图层:

(3).得出结论:

一个根节点下最多拥有1171个子结点，每一个子结点最多存储16行数据。

高度为m的树，占多少B?

1171^(高度m-1)*16 = 树高度m下的内存

10.索引_语法

(1).创建索引
1.一个索引可以关联多个字段。
一个索引只关联一个字段叫做单列索引；如果一个索引关联多个字段我们叫做联合索引或者组合索引。
2.假如声明unique则称为创建的是唯一索引；fulltext称为全文索引；假如都没有添加我们称为常规索引
create [unique|fulltext] index 索引名字 on 表名(表中哪个字段名称成为索引...);
(2).查看索引
show index from 表名;
(3).删除索引
drop index 索引名字 on 表名
(4).索引语法_演示
  1. 准备数据
create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');
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');


-- 1.name字段为姓名字段，该字段的只可能会重复，为该字段创建索引。
create index idx_user_name on tb_user(name);
-- 2.phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引
create unique index idx_user_phone on tb_user(phone);
-- 3.为profession、age、status创建联合索引
create index idx_user_pro_age_status on tb_user(profession,age,status);
-- 4. 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
show index from tb_user;

11.SQL性能分析_执行频率 (工具一)

(1).SQL执行频率

MySQL 客户端连接成功后，通过show [session | global] status命令提供服务器状态信息。提高如下质量，可以查看当前数据库的insert、update、delete、select的访问频次:

show global status like 'com_______';  #这里要是七个下划线
(2).SQL执行频率示列
show global status like 'com_______';
select *from emp;
show global status like 'com_______';

12.SQL性能分析_慢查询分析 (工具二)

(1).慢查询日志

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

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf或my.ini)中配置如下信息:

(2).window下配置慢日志查询

1.查看慢日志是否开启

show variables like 'slow_query_log';

2.开启MySQL慢日志查询开关

slow_query_log=1

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

long_query_time=2

window下进行配置:

查看我们的慢日志是否配置成功!

配置成功后，会自动生成一个日志文件

(3)Linux下配置慢查询日志

查询我们的Mysql配置文件 : cat /etc/my.cnf

阿里云自动配置的慢查询信息如下:

slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3

查看我们的慢查询日志信息:

cat /www/server/data/mysql-slow.log

实时查询我们尾部慢查询日志信息

tail -f /www/server/data/mysql-slow.log

由于我们的服务器比较小，所以不查询了。如果超过三秒数据就会在这里展示。

文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
索引
存储
MySQL
SQL
关键词：
云数据库 RDS MySQL 版进阶
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
吉士先生
目录
相关文章
数据库指南针
|
5月前
|
存储 SQL 关系型数据库
MySQL语句详解：从基础到进阶的全面指南
MySQL语句详解：从基础到进阶的全面指南
数据库指南针
95 1
半夜敲代码的夜猫子
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇（MySQL性能优化）
MySQL数据库进阶第三篇（MySQL性能优化）
半夜敲代码的夜猫子
203 2
半夜敲代码的夜猫子
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇（InnoDB引擎架构，事务原理，MVCC）
MySQL数据库进阶第六篇（InnoDB引擎架构，事务原理，MVCC）
半夜敲代码的夜猫子
226 1
半夜敲代码的夜猫子
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶第五篇（锁）
MySQL数据库进阶第五篇（锁）
半夜敲代码的夜猫子
76 1
让线程再跑一会
|
6月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（2）
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
让线程再跑一会
118 0
让线程再跑一会
|
6月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（1）
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
让线程再跑一会
118 0
jianz123
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库开发进阶：精通数据库表的创建与管理22
【7月更文挑战第22天】数据库的创建与删除，数据表的创建与管理
jianz123
52 1
公众号:码到三十五
|
6月前
|
JSON 关系型数据库 MySQL
MySQL常用函数解读：从基础到进阶的全方位指南
MySQL常用函数解读：从基础到进阶的全方位指南
公众号:码到三十五
191 0
半夜敲代码的夜猫子
|
6月前
|
SQL 关系型数据库 MySQL
Python进阶第二篇（Python与MySQL数据库）
Python进阶第二篇（Python与MySQL数据库）
半夜敲代码的夜猫子
56 2
半夜敲代码的夜猫子
|
6月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇（视图/存储过程/触发器）
MySQL数据库进阶第四篇（视图/存储过程/触发器）
半夜敲代码的夜猫子
322 1

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    MySql进阶使用之视图、索引（详解）
  • 4
    Mysql5.7使用DTS增量同步数据到MaxCompute
  • 5
    mysql中的时间函数---运维常用
  • 6
    mysql--索引 （查询）
  • 7
    mysql索引优化
  • 8
    MYSQL 逗号分割字段行转列
  • 9
    MySQL学习之流程函数
  • 10
    MySQL无法重启问题解决Warning: World-writable config file ‘/etc/my.cnf’ is ignored
  • 1
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    81
  • 2
    Linux对MySQL语法校验
    90
  • 3
    【MySQL系列笔记】分库分表
    216
  • 4
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    163
  • 5
    服务器Linux系统配置mysql数据库主从自动备份
    215
  • 6
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    420
  • 7
    golang使用gorm操作mysql3，数据查询
    97
  • 8
    golang使用gorm操作mysql2
    67
  • 9
    golang使用gorm操作mysql1
    116
  • 10
    Hbase与MySQL对比，区别是什么？
    309

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    DataWorks售前咨询