MySQL笔记(进阶篇)

包含内容:    

    存储引擎
    索引
    SQL优化
    视图/存储过程/触发器
    锁
    InnoDB引擎
    MySQL管理

存储引擎

MySQL体系结构

1. 连接层:最上层是一些客户端和连接服务,主要完成一些类似于连接处理,授权认证,及相关的安全方案.服务器也会为安全接入的每个客户验证它所具有的操作权限.
2. 服务层:第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化,部分内置函数的执行.所有跨存储引擎的功能也在这一层实现,如过程,函数等.
3. 引擎层:存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信.不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎.
4. 存储层:主要是将数据存储在文件系统之上,并完成与存储引擎的交互.

存储引擎简介

存储引擎就是存储数据,建立索引,更新/查询数据等技术的实现方式.存储引擎是基于表的,而不是基于库的,所以存储引擎也可以被称为表类型

默认存储引擎:

InnoDB引擎

在创建表时,指定存储引擎

CREATE TABLE 表名(
                    字段1 字段1类型[comment 字段1注释],
                    ...
                    字段n 字段n类型[comment 字段n注释]
                )ENGINE=INNODB[comment 表注释];

查看当前数据库支持的存储引擎

SHOW ENGINES;

demo:

## 创建表my_myisam ,并制定MyISAM存储引擎
                create table my_myisam(
                    id int,
                    name varchar(10)
                )engine =MyISAM;
## 创建表my_memory,置顶Memory存储引擎
                create table my_memory(
                    id int,
                    name varchar(10)
                )engine =Memory;

存储引擎特点

InnoDB引擎

介绍:

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySQL5.5之后,InnoDB是默认的MySQL存储引擎

特点:

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

文件:

xxx.ibd: xxx代表表名,InnoDB引擎的每张表都对应这样一个表空间文件,存储表的表结构(frm,sdi),数据和索引.

参数:

innodb_file_per_table

逻辑存储结构:

见图"逻辑存储结构"

MyISM引擎

介绍:

MyISM引擎是MySQL早期的默认存储引擎

特点:

• 不支持事务,不支持外键
• 支持表锁,不支持行锁
• 访问速度快

文件: 

• xxx.MYD:存放表数据信息
• xxx.MYI: 存储索引
• XXX.SDI:存放表结构信息

Memory引擎

介绍:

Memory引擎的表数据是存储在内存中的,由于受到硬件问题,或断电问题的影响,只能将这些表作为临时表或缓存使用

特点:

• 内存存放
• hash索引(默认)

文件:

xxx.sdi: 存储表结构信息

三种存储引擎区别

见图: 存储引擎区别

      

存储引擎选择

在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎,对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合

InnoDB: 是MySQL的默认存储引擎,支持事务,外键.如果应用对事务的完整性有比较高的要求, 在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新,删除操作,那InnoDB存储引擎是比较合适的选择

MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性,并发性要求不是很高,那么选择这个引擎是非常合适的

MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存,MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性

索引

    索引概述
    索引结构
    索引分类
    索引语法
    SQL性能分析
    索引使用
    索引设计原则

索引概述

介绍

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

演示

见图 索引概述1

优缺点

见图 索引优缺点

索引结构

索引结构: 见图 索引结构1

不同引擎索引支持情况:

见图 不同引擎索引支持情况1

二叉树索引:

见图 二叉树索引

B-Tree(多路平衡查找树):

见图 B-Tree(多路平衡查找树)

B-Tree(多路平衡查找树)演变过程:

见图 B-Tree(多路平衡查找树)演变过程

B+Tree 索引结构

见图 B+Tree 索引结构

B+Tree与B-Tree 区别  

见图B+Tree与B-Tree 区别

Hash索引结构:

见图Hash索引结构

Hash索引特点和引擎支持:

见图Hash索引特点+引擎支持

为什么InnoDB存储引擎选择B+tree索引结构?

见图: 为什么InnoDB存储引擎选择B+tree索引结构

索引分类

见图: 索引分类

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

见图: InnoDB存储方式

聚集索引和二级索引对比:

见图: 聚集索引和二级索引

回表查询:

见图: 回表查询

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

案例

建表
                create table tb_user(
                id int comment 'id',
                name varchar(10) comment 'name',
                phone varchar(11) comment 'phone',
                email varchar(20) comment 'email',
                profession varchar(10) comment 'profession',
                age int comment 'age',
                gender varchar(1) comment 'gender',
                status varchar(2) comment 'status'
            ) comment '数据表';

                alter table tb_user add createtime date comment 'createtime';

                insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES
                (1,'吕布','17799990000','lvbu@163.com','软件工程',23,'1','6','2001-02-02'),
                (2,'曹操','17799990001','caocao@qq.com','通讯工程',33,'1','0','2001-03-05'),
                (3,'赵云','17799990002','zhaoyun@qq.com','英语',34,'1','2','2002-03-02'),
                (4,'孙悟空','17799990003','sunwuk@qq.com','工程造价',54,1,'0','2001-07-02'),
                (5,'花木兰','17799990004','1@qq.com','软件工程',23,'2','1','2001-04-22'),
                (6,'大乔','17799990005','2@qq.com','舞蹈',22,'2','0','2001-02-07'),
                (7,'露娜','17799990006','3@qq.com','应用数学',24,'2','0','2001-02-08'),
                (8,'程咬金','17799990007','4@qq.com','化工',38,'1','5','2001-05-23'),
                (9,'项羽','17799990008','5@qq.com','金属材料',43,'1','0','2001-09-18'),
                (10,'白起','17799990009','6@qq.com','机械工程及其自动化',27,'1','2','2001-08-16'),
                (11,'韩信','17799990010','7@qq.com','无机非金属',27,'1','0','2001-06-12'),
                (12,'荆轲','17799990011','8@qq.com','会计',29,'1','0','2001-05-11'),
                (13,'兰陵王','17799990012','9@qq.com','工程造价',44,'1','1','2001-04-09'),
                (14,'狂铁','17799990013','0@qq.com','应用数学',43,'1','2','2001-04-10'),
                (15,'貂蝉','17799990014','11@qq.com','软件工程',40,'2','3','2001-02-12'),
                (16,'妲己','17799990015','12@qq.com','软件工程',31,'2','0','2001-01-30'),
                (17,'闵月','17799990016','13@qq.com','工业经济',35,'2','0','2000-05-03'),
                (18,'嬴政','17799990017','14@qq.com','化工',38,'1','1','2001-08-08'),
                (19,'狄仁杰','17799990018','15@qq.com','国际贸易',30,'1','0','2007-03-12'),
                (20,'安其拉','17799990019','16@qq.com','城市规划',51,'2','0','2001-08-15'),
                (21,'典韦','17799990020','123@qq.com','城市规划',52,'1','2','2000-04-12'),
                (22,'廉颇','17799990021','2222@qq.com','土木工程',19,'1','3','2002-07-18'),
                (23,'后羿','17799990022','1232@qq.com','城市园林',20,'1','0','2002-03-10'),
                (24,'姜子牙','17799990023','caocao@qq.com','工程造价',29,'1','4','2003-05-26');
                
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_sta on tb_user(profession, age, status);
4.为email建立合适的索引来提升查询效率
                create index inx_user_email on tb_user(email);

SQL性能分析

SQL执行频率

MySQL客户端连接成功后,通过show[session|global]status命令可以提供服务器状态信息.通过如下指令,可以查看当前数据库的INSERT,UPDATE,DELETE,SELECT的访问频次
            SHOW GLOBAL STATUS LIKE 'Com_______'; // 7个下划线

// 见图 MySQL查询操作执行频率

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(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

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了,通过have_profiling参数,能够看到当前MySQL是否支持profile操作
SELECT @@have_profiling;
            默认profiling是关闭的,可以通过set语句在session/global级别开启profiling;
            set 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;

未完...待更新 2023-10-07 18:16:56