mysql的ddl什么意思 ddl mysql

转载

小蝌蚪 2023-12-13 01:34:29

文章标签 mysql的ddl什么意思 mysql 数据库 sql 存储过程 文章分类 MySQL 数据库

一、基本操作

1、DDL&&DML

2、增删改查

3、函数

4、存储过程、触发器、视图

二、执行计划

1、输出格式

2、调优分析

三、索引

1、分类

2、索引结构

四、事务

1、事务特征

2、隔离级别和并发问题

3、事务失效

摘要：随着mysql的普及程度越来越高，越来越多的企业级应用逐渐采用了mysql作为关系型存储数据库。本文以mysql8.0为学习版本，介绍mysql的相关操作和基本理论。

一、基本操作

1、DDL&&DML

DDL是数据库定义语言，即Data Definition Language，主要包含创建表、修改表、删除表、索引处理等改变表结构的操作。

DML是数据操作语言，即Data Manipulate Language，主要包含增删改查等数据层面的操作。

举个例子，以DDL建表语句为例：

CREATE TABLE `score` (
  `id` int NOT NULL,
  `score` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2、增删改查

DML，数据操作语言，包括select/update/delete/insert，可对上表score进行操作。

select id , score from score ;  /**查**/ 
insert into score(id,score) value('1','75'); /**增**/ 
update score s set s.score='100' where id=1; /**改**/ 
delete from score where id = 10; /**删**/

3、函数

mysql官网里面有一章节，专门讲函数与运算符的，MySQL參考手冊有最全的函数说明文档。

地址在这：MySQL :: MySQL 8.0 Reference Manual :: 12.1 Built-In Function and Operator Reference

文档里面分为多类函数，如常用的日期和时间函数、数值函数、字符串函数、聚合函数等。

日期和时间函数如：DATE()、DATE_FORMAT()、NOW()、DAY()、WEEK()、YEAR()等。
数值函数如：RAND()、ROUND()、SQRT()、MOD()、POWER()等。
字符串函数如：LOWER()、UPPER()、REGEXP()、REPLACE()、LEFT()、RIGHT()等。
聚合函数如：AVG()、COUNT()、MAX()、MIN()、SUM()等。

举个例子，以DATE函数为例：

mysql> SELECT DATE('2003-12-31 01:02:03');
     >'2003-12-31'

4、存储过程、触发器、视图

存储过程：是一组sql语句的集合，可以实现复杂的逻辑处理，是对sql语言的封装。调用时需要指定存储过程名称进行触发。

触发器：是一种特殊的存储过程，能触发完成特定的复杂逻辑功能的sql语句集。执行DML语句时会自动触发，无需手工调用。

视图：是一种虚拟存在的表，不存在于实际的数据库中，使用视图查询数据时，会从数据库真实表中取出对应的数据。

/** 
视图 
**/
-- 创建视图
create view view_score as select id,score from score where score>80;
-- 查询视图
select * from view_score ;
-- 删除视图
drop view view_score ;

/** 
存储过程 
**/
-- 创建存储过程
delimiter $
create procedure procedure_score01()
begin
    select id,score from score ;
end $
delimiter ;
-- 调用存储过程
call procedure_score01;
-- 删除存储过程
drop procedure procedure_score01 ;

/** 
触发器
功能描述：当score表添加一行数据，则自动向student_log日志表插一条记录
**/
-- 定义触发器
create trigger trigger_score01 after insert on score for each row 
insert student_log values(null , '新增了记录');
-- 添加数据，自动执行触发器
insert into score values(12,'65');
-- 删除触发器
drop trigger trigger_score01 ;

/** 为了演示触发器功能，建个辅助表student_log **/
CREATE TABLE `student_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `remark` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

二、执行计划

查看数据库sql的执行计划，主要是用到关键字explain或desc。这里用explain来做详细解析：

1、输出格式

先看一下mysql官网上的explain命令输出的字段含义。

mysql的ddl什么意思 ddl mysql_mysql的ddl什么意思

这里我简单翻译几个重点观测的字段：

select_id:查询的标识符，用于记录查询计算的次数，id大的先执行。
select_type:查询的类型，包括SIMPLE简单查询、UNION联合查询、SUBQUERY子查询等。
table:用到的表名。
type:关联查询的访问方式，ALL表示全表扫描，INDEX表示扫描索引，RANGE表示取出范围的记录并利用索引来获取，还有其他一些取值，如REF、CONST、SYSTEM。
key:查询实际用到的索引。
possible_keys:查询可以选用的索引。
rows:估算出的大概的扫描行数，值越小越优。
filtered:返回结果的行数占读取行数的百分比，值越大越优。
extra:额外的执行信息，如use filesort、use temporary、use index、use where等。

2、调优分析

以上可能比较抽象，先看个效果来对比一下效果，用前面建好的score表来做个试验。

语句1，这是走主键索引的执行计划：

mysql的ddl什么意思 ddl mysql_存储过程_02

语句2，这是不走索引的执行计划：

mysql的ddl什么意思 ddl mysql_mysql的ddl什么意思_03

可以看出，两个sql语句的执行计划是不同的。

分析：语句1的type是const，扫描的是常量级别的记录。语句2的type是ALL，进行了全表扫描。从possible_keys和key两个字段结果可以看出，语句1使用了主键索引，语句2不走索引。从rows可以看出，语句1扫描的行数是1行，语句2扫描的行数是9行。两者返回结果的行数占读取行数的百分比filtered也是不同的，额外信息说明也是不同的。由上可以分析得出，语句1的执行效率是远优于语句2的。

由上面实验和常见的慢sql现象，可以得出以下几个优化思路：

避免全表扫描，type为ALL的，rows特别大的需要优化。
不走索引的需要优化，key和possible_keys为空的，extra为use filesort的需要优化。
返回结果的行数占读取行数的百分比filtered小的需要优化，可以尝试采用联合索引、索引覆盖、索引下推、最左匹配等方式优化。
额外信息extra的优化，use filesort使用非索引列进行排序，use temporary使用临时表保存中间结果，都需要优化。

刚开始用博客记录笔记那会儿，水笔一篇数据库查询优化初级方案，那时候还不知道各种花式调优，只知道加索引。看来人的认知是一个不断完善的过程呀~

三、索引

索引用于快速查找具有特定列值的行，如果沒有索引，MySQL 必须从第一行开始扫描。索引就像书本的目录，直接存放着记录内容的地址，通过索引查找，我们很快可以锁定记录的位置。

1、分类

分类这个仁者见仁智者见智。这里根据索引的不同特征，简单分出以下这些分类：

①根据物理储存划分，索引叶子节点是否直接连接记录内容可分为：

聚簇索引：物理索引，按顺序储存，物理上是连续的，一个表只能有一个聚簇索引，直接和记录内容存放一起。

非聚簇索引：也叫辅助索引，叶子节点存索引列的值，指向聚簇索引对应的地址。

②根据功能特点划分，是否以主键作为索引可分为：

主键索引：以主键作为索引，是一种约束，值唯一且不能为空，可以作为外键。

普通索引：非主键索引，值没有唯一性限制。

唯一索引：值有唯一性限制，可以为空，一个表可以有多个。

③根据索引特点，也衍生出了以下几类索引：

联合索引：指对表上的多个列进行索引，键值数量>=2

全文索引：采用反向索引设计，达到全文快捷搜索目的，搜索引擎的关键技术

引用以下官网原文来辅助说明，InnoDB uses inverted lists for FULLTEXT indexes.

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.

2、索引结构

B+树：索引的数据结构。作为B树的一种特殊拓展的数据结构，其本身树高较矮，读写效率高的特点，让它成为了mysql索引数据结构的不二选择。myisam、innodb引擎都采用了B+树作为其储存的数据结构。

树高矮：B+树是M阶的多叉树，每个节点上可以储存多个元素，自身通过裂变来维持树的高度和平衡。
读写快：树高比较低，记录存放在叶子节点上，查询时IO消耗比较小。且叶子节点兄弟节点互成链表。

Hash索引：查找速度非常快，非常适用于键值储存，空间占用不大，支持memory引擎。但也伴随着以下缺点：

hash索引只能用整个键来搜索。
优化器不能使用hash索引来加速order by操作。
只能做等值查询，无法做范围查找。

索引的数据库储存引擎中，经典的有myisam、innodb、memory等，这里就不作展开讲述了，储存引擎可以和MVCC、数据库锁机制一起学习。

四、事务

1、事务特征

事务的四大特征：ACID，即一致性(C)、原子性(A)、持久性(D)、隔离性(I)。

四大特性如何保证：

原子性：依赖undolog记录需要回滚的日志信息，事务回滚时可以撤销已经执行的sql。

隔离性：由mysql的MVCC机制来实现，多版本控制。

持久性：依赖redolog记录数据的修改，数据没保存成功但有日志信息可保证数据不丢失。

一致性：由以上三个特性和业务程序一起保证。

2、隔离级别和并发问题

数据库隔离级别：读未提交、读已提交、可重复读、串行化。

并发数据库事务问题：脏写（数据丢失）、脏读、不可重复读、幻读。

	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

3、事务失效

在应用程序中，采用编程式事务或者声明式事务的时候，偶尔会发生事务失效的情况，没有按照我们预想的那样提交或者回滚事务。

这里也总结了几点声明式事务失效的场景：

a.未抛出异常：应在事务方法里面抛出异常让事务捕获。
b.未正确抛出异常：事务方法抛出的异常应小于或等于事务声明或默认的异常。
c.同一个类内方法调用：事务是基于动态代理实现的，同个类方法是this调用，非代理调用。
d.同一个类非事务方法调用事务方法：无事务传播，应采用代理类调用方式。
e.事务作用于非共有方法：事务作用的方法应是public且非final非static的方法。
f.事务的传播属性配置不正确：没有正地传播事务属性也会导致事务失效。

本文通过阅读mysql官方文档，结合实际开发过程中遇到的问题，总结的一套知识文档，如有错误，欢迎指正。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。