微信网站建设收费标准,wordpress网站管理员插件,十大免费自学app,八冶建设集团有限公司网站MySQL 六、MySQL索引视图6.1 索引底层原理6.1.1 索引hash算法6.1.2 索引二叉树算法6.1.3 索引平衡二叉树算法6.1.4 索引BTREE树算法6.1.5 普通SQL全表扫描过程 6.2 索引分类6.2.1 按数据结构层次分类6.2.2 按字段数量层次分类6.2.3 按功能逻辑层次分类#xff08;面试题#… MySQL 六、MySQL索引视图6.1 索引底层原理6.1.1 索引hash算法6.1.2 索引二叉树算法6.1.3 索引平衡二叉树算法6.1.4 索引BTREE树算法6.1.5 普通SQL全表扫描过程 6.2 索引分类6.2.1 按数据结构层次分类6.2.2 按字段数量层次分类6.2.3 按功能逻辑层次分类面试题6.2.4 按存储方式层次分类 6.3 索引的设计原则6.4 创建索引6.5 查看索引6.6 删除索引6.7 索引的优劣分析6.7.1 引入索引带来的优势6.7.2 引入索引带来的弊端 6.8 视图概念6.9 创建视图6.10 更新视图6.11 修改视图6.12 删除视图6.13 视图使用规则 七、SQL编程7.1 存储过程7.1.1 存储过程概念7.1.2 存储过程创建与调用7.1.3 变量局部变量用户变量系统变量 7.1.4 参数传递7.1.5 存储过程的管理7.1.6 存储过程的应用场景 7.2 流程控制7.2.1 if判断7.2.2 case判断7.2.3 循环while循环格式repeat循环格式loop循环格式 7.3 触发器7.3.1 创建触发器7.3.2 NEW与OLD 7.4 存储函数7.4.1 存储函数创建和调用create function7.4.2 删除存储函数drop function 八 MySQL事务8.1 数据库事务处理原则8.2 事务的特性面试-ACID8.2.1 原子性(Atomicity)8.2.2 一致性(Consistency)8.2.3 隔离性(Isolation)8.2.4 持久性(Durability) 8.3 MySQL使用事务8.3.1 MySQL的事务操作主要有以下三种8.3.2 使用事务的方法8.3.3 自动提交策略8.3.4 设置回滚点8.3.5 事务的隐式提交 8.4 InnoDB 事务的ACID如何保证8.4.1 WAL技术8.4.2 redo log相关参数8.4.3 MySQL CSR—前滚8.4.4 undo 回滚日志 8.5 事务的隔离级别8.5.1 脏读8.5.2 不可重复读8.5.3 幻读8.5.4 事务的隔离级别8.5.5 查看隔离级别8.5.6 设置隔离级别8.5.7 示例8.5.8 总结 九、SQL优化9.1 SQL优化技巧9.1.1 查询时尽量不要使用 *9.1.2 连表查询时尽量不要关联太多表9.1.3 多表查询时一定要以小驱大小表放前9.1.4 不要使用like左模糊和全模糊查询9.1.5 查询时尽量不要对字段做空值判断9.1.6 不要在条件查询 前对字段做任何运算9.1.7 !、!、not in、not like、or...要慎用9.1.8 避免频繁创建、销毁临时表9.1.9 从业务设计层面减少大量数据返回的情况9.1.10 尽量避免深分页的情况出现9.1.11 SQL务必要写完整不要使用缩写法9.1.12 明确仅返回一条数据的语句可以使用limit 19.1.13 客户端的一些操作可以批量化完成 9.2 查看SQL执行频率9.3 定位低效率执行SQL9.4 explain分析执行计划9.5 show profile 分析SQL9.6 trace分析优化器执行计划9.7 使用索引优化9.8 架构优化 六、MySQL索引视图 mysql表建立索引的本质是为了避免查询时走全表扫描减少时间开销 新建表插入大量数据通过无索引查询及有索引查询来对比性能
mysql create database mydb13_indexdb;mysql use mydb13_indexdb;mysql create table student(id int, name varchar(64), age int(2));mysql desc student;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int | YES | | NULL | |
| name | varchar(64) | YES | | NULL | |
| age | int | YES | | NULL | |
-----------------------------------------------mysql insert into student values(1,das,20),(2,dasdas,19),(3,dsfsfsd,18),(4,bbggbbg,22),(5,eeeee,19);mysql insert into student select * from student;
mysql insert into student select * from student;
……
# 多次执行上述的自我复制将数据量增加到百万级……
……
mysql insert into student select * from student;
Query OK, 1310720 rows affected (13.59 sec)
Records: 1310720 Duplicates: 0 Warnings: 0# 插入一条新数据
mysql insert into student values(666,andy,40);# 查询刚插入的新数据
mysql select * from student where id666;
------------------
| id | name | age |
------------------
| 666 | andy | 40 |
------------------
1 row in set (2.75 sec) # 耗费2.75秒# 查看文件容量student表占用120M
C:\ProgramData\MySQL\MySQL Server 8.0\Data\mydb13_indexdbmysql create index id_index on student(id); # 给student表新建索引
Query OK, 0 rows affected (12.13 sec) # 耗时较长
Records: 0 Duplicates: 0 Warnings: 0mysql select * from student where id666; # 在有索引的情况下再次查询
------------------
| id | name | age |
------------------
| 666 | andy | 40 |
------------------
1 row in set (0.00 sec) # 有索引查询花费的时间mysql select 2.75/0.000001; # 性能提升了
----------------
| 2.75/0.000001 |
----------------
| 2750000.000000 |
----------------# 再次查看文件容量新建索引前为120m新建索引后为164m表容量增大
C:\ProgramData\MySQL\MySQL Server 8.0\Data\mydb13_indexdb# 总结索引的本质就是以空间换时间同时把随机的事件变成顺序的事件日常项目开发中读写比例在10:1左右查询使用频率比较大虽然损失点存储空间但两害取其轻还是需要对关键字段新建索引提高查询性能最后需要删除上述student表mysql drop table student;mysql show tables;6.1 索引底层原理
一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上。
6.1.1 索引hash算法
优点通过字段的值计算的hash值定位数据非常快 。缺点不能进行范围查找因为散列表中的值是无序的无法进行大小的比较。 6.1.2 索引二叉树算法
特性分为左子树、右子树和根节点左子树比根节点值要小右子树比根节点值要大缺点有可能产生不平衡 类似于链表的结构 。 6.1.3 索引平衡二叉树算法
优点 它的左子树和右子树都是平衡二叉树左子树比中间小右子树比中间值左子树和右子树的深度之差的绝对值不超过1 缺点 插入操作需要旋转支持范围查询但回旋查询效率较低比如要查找大于8的会回旋到父节点7、10。如果存放几百条数据的情况下树高度越高查询效率会越慢
6.1.4 索引BTREE树算法
目前大部分数据库系统及文件系统都采用B-Tree或其变种BTree作为索引结构Btree结构可以有效的解决之前的相关算法遇到的问题。 注意InnoDB引擎使用BTreeInnoDB的叶节点的data域存放的是数据相比MyISAM效率要高一些但是比较占硬盘内存大小。 6.1.5 普通SQL全表扫描过程 以下列表为例 首先假设表中不存在任何索引此时来执行下述这条SQL
mysql select * from student where sname陈晓晓;因为表中不具备索引所以这里会走全表扫描的形式检索数据但不管是走全表亦或索引本质上由于数据都存储在磁盘中因此首先都会触发磁盘IO磁盘结构如下 当走全表扫描时会发生磁盘IO但是磁盘寻道是需要有一个地址的这个地址最开始就是本地表数据文件中的起始地址也就是从表中的第一行数据开始读读到数据后会载入内存然后MySQL-Server会根据SQL条件对读到的数据做判断如果不符合条件则继续发生磁盘IO读取其他数据如果表比较大这里不会以顺序IO的形式走全表检索而是会触发随机的磁盘IO。上面student表中【陈晓晓】 这条数据位于表的第五行那这里会发生五次磁盘IO吗不会因为OS即MYSQL中都有一个优化措施叫做局部性读取原理 局部性原理的思想:如目前有三块内存页x、y、z是相连的CPU此刻在操作x页中的数据那按照计算机的特性一般同一个数据都会放入到物理相连的内存地址上存储也就是当前在操作x页的数据那么对于yz这两页内存的数据也很有可能在接下来的时间内被操作因此对于yz这两页数据则会提前将其载入到高速缓冲区L1/L2/L3这个过程叫做利用局部性原理“预读”数据。 作用一般用于减少存储数据的磁盘和内存之间的性能差异 MySQL的InnoDB引擎默认一次磁盘IO会读取16KB数据到内存。 继续全表扫描由于MySQL中会使用局部性原理的思想可能只需发生一次磁盘IO就能将前五条数据全部读到内存然后会在内存中对本次读取的数据逐条判断看一下每条数据的姓名字段是否为【陈晓晓】 如果发现不符合SQL条件的行数据则会将当前这条数据放弃同时在本次SQL执行过程中会排除掉这条数据不会对其进行二次读取。如果发现当前的数据符合SQL条件要求则会将当前数据写入到结果集中然后继续判断其他数据。当本次磁盘IO读取到的所有数据全部筛选完成后紧接着会看一下表中是否还有其他数据如果还有则继续触发磁盘IO检索数据如果没有则将内存中的结果集返回。 全表扫描结束会产生问题当表的数据量变为百万级别、千万级别假设表中一条数据大小为512Byte一次磁盘IO也只能读32条假设表中有320w条数据一次全表就有可能会触发10W次磁盘IO每次都需要在硬件上让那个盘面转啊转其过程的开销可想而知…
因此建立索引的原因就在于此处为了避免查询时走全表扫描。
6.2 索引分类
6.2.1 按数据结构层次分类
索引建立后也会在磁盘生成索引文件每个索引节点在本地文件中的存储形式是由索引的数据结构来决定的。MySQL索引支持的数据结构如下 BTree类型MySQL中最常用的索引结构也是 默认索引物理存储结构有序。Hash类型大部分存储引擎都支持字段值不重复的情况下查询最快无序。R-Tree类型MyISAM引擎支持也就是空间索引的默认结构类型。T-Tree类型NDB-Cluster引擎支持主要用于MySQL-Cluster服务中。 6.2.2 按字段数量层次分类
从表字段的层次来看索引又可以分为单列索引和多列索引单列索引 唯一索引指索引中的索引节点值不允许重复一般配合唯一约束使用。主键索引主键索引是一种特殊的唯一索引和普通唯一索引的区别在于不允许有空值。普通索引通过KEY、INDEX关键字创建的索引就是这个类型没啥限制单纯的可以让查询快一点。…还有很多很多只要是基于单个字段建立的索引都可以被称为单列索引。 多列索引组合索引、联合索引、复合索引、多值索引…即由多个字段组合建立的索引
6.2.3 按功能逻辑层次分类面试题
面试题请回答一下你知道的MySQL索引类型。其上主要就是指MySQL索引从逻辑上可以分为那些类型以功能逻辑划分索引类型这也是最常见的划分方式从这个维度来看主要可划分为五种 普通索引加速查找最常用的索引允许重复唯一索引加速查找约束不能重复主键索引加速查找约束不为空、不能重复全文索引仅可用于 MyISAM 表建立于char字段空间索引使用不多基于GIS地理信息系统的空间数据相关字段创建
6.2.4 按存储方式层次分类
分为两大类 聚簇索引也被称为聚集索引、簇类索引非聚簇索引也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引 区别 聚簇索引逻辑上连续且物理空间连续索引数据和表数据在磁盘中的位置是一起的一张表中只能存在一个聚簇索引聚簇索引要求索引必须是非空唯一索引一般适合采用带有自增性的顺序值非聚簇索引逻辑上的连续物理空间上不连续索引数据和表数据在磁盘中分开存储用物理地址的方式维护两者的联系
6.3 索引的设计原则
选择惟一性索引为经常需要排序、分组和联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目尽量使用数据量少的索引尽量使用字段前缀来索引即限制索引长度 索引长度对表中特定字段的前N个字符创建索引通常用于减少索引的大小并且可以提高查询性能 删除不再使用或者很少使用的索引
6.4 创建索引
方法1使用create语句在已经存在的表上创建索引
# 创建普通索引create index indexname on tablename (columnName(length) [asc|desc]);# indexName当前创建的索引创建成功后叫啥名字。
# tableName要在哪张表上创建一个索引这里指定表名。
# columnName要为表中的哪个字段创建索引这里指定字段名。
# length如果字段存储的值过长选用值的前多少个字符创建索引。
# asc|desc指定索引的排序方式asc是升序desc是降序默认asc。# 创建唯一索引create unique index indexname on tablename (columnName(length) [asc|desc]);方法2使用alter table语句来添加索引
# 添加普通索引alter table tableName add index indexname(columnname(length) [asc|desc]);# 添加唯一索引alter table tableName add unique index indexname(columnname(length) [asc|desc]); 方式3创建表的时候创建索引
# 创建普通索引create table 表名 (字段名1 数据类型 [完整性约束条件…],字段名2 数据类型 [完整性约束条件…],[unique | fulltext | spatial] index | key[索引名] (字段名[(长度)] [asc | desc]) );# 创建唯一索引create table 表名 (字段名1 数据类型 [完整性约束条件…],字段名2 数据类型 [完整性约束条件…],unique index [索引名] (字段名[(长度)] [asc | desc]) );# 创建主索引
create table 表名 (字段名1 数据类型 [完整性约束条件…],字段名2 数据类型 [完整性约束条件…],primary key [索引名] (字段名[(长度)] [asc | desc]) );
# 注意# 创建主键索引时必须要将索引字段先设为主键否则会抛1068错误码。# 主索引不同使用create语句创建否则会提示1064语法错误。# 创建索引时关键字要换成key并非index否则会提示语法错误。示例
# 创建表的同时创建普通索引
mysql create table index1_tb( id int, name varchar(20), sex boolean, index(id));# 创建表的同时创建唯一索引
mysql create table index2_tb( id int unique, name varchar(20), unique index index2(id asc) );# 创建单列索引 (即普通的单列索引)
mysql create table index3_tb( id int, subject varchar(30), index index3(subject(10)) );# 创建多列索引 (即普通的多列索引)
# 注意使用多列索引时一定要特别注意只有使用了索引中的第一个字段时才会触发索引。
mysql create table index4_tb( id int, name varchar(20), sex char(4), index index4(name,sex) );# 在创建完表后为其添加索引
mysql create unique index un_index on index1_tb(name);mysql alter table index3_tb add primary key(id);6.5 查看索引
格式
# 查询索引
show create table 表名 \G# 查询某张表中索引情况
show index from table_name;# 使用计划查询SQL使用索引情况
explain select * from 表名 where id1 \G# 使用系统表查看所有索引
select * from mysql.innodb_index_stats a where a.database_name 数据库名’; # 使用系统表查看单张表的所有索引
select * from mysql.innodb_index_stats a where a.database_name 数据库名 and a.table_name like %表名%’; 示例
mysql show create table index1_tb \Gmysql show index from index3_tb\G
*************************** 1. row ***************************Table: index3_tbNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
*************************** 2. row ***************************Table: index3_tbNon_unique: 1Key_name: index3Seq_in_index: 1Column_name: subjectCollation: ACardinality: 0Sub_part: 10Packed: NULLNull: YESIndex_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL# 分析
• Table当前索引属于那张表。• Non_unique目前索引是否属于唯一索引0代表是的1代表不是。• Key_name当前索引的名字。• Seq_in_index如果当前是联合索引目前字段在联合索引中排第几个。• Column_name当前索引是位于哪个字段上建立的。• Collation字段值以什么方式存储在索引中A表示有序存储NULL表无序。• Cardinality当前索引的散列程度也就是索引中存储了多少个不同的值。• Sub_part当前索引使用了字段值的多少个字符建立NULL表示全部。• Packed表示索引在存储字段值时以什么方式压缩NULL表示未压缩• Null当前作为索引字段的值中是否存在NULL值YES表示存在。• Index_type当前索引的结构BTREE, FULLTEXT, HASH, RTREE。• Comment创建索引时是否对索引有备注信息。
……………………mysql explain select * from index1_tb where id1 \G
# 注意possible_keys和key 这两个属性possible_keysMySQL在搜索数据记录时可以选用的各个索引key实际选用的索引6.6 删除索引
语法
drop index 索引名 on 表名注意一些不再使用的索引会降低表的更新速度影响数据库的性能对于这样的索引应该将其删除
6.7 索引的优劣分析
6.7.1 引入索引带来的优势
整个数据库中数据表的查询速度直线提升数据量越大时效果越明显。通过创建唯一索引可以确保数据表中的数据唯一性无需额外建立唯一约束。在使用分组和排序时同样可以显著减少SQL查询的分组和排序的时间。连表查询时基于主外键字段上建立索引可以带来十分明显的性能提升。索引默认是BTree有序结构基于索引字段做范围查询时效率会明显提高。从MySQL整体架构而言减少了查询SQL的执行时间提高了数据库整体吞吐量。
6.7.2 引入索引带来的弊端 建立索引会生成本地磁盘文件需要额外的空间存储索引数据磁盘占用率会变高。 写入数据时需要额外维护索引结构增、删、改数据时都需要额外操作索引。 写入数据时维护索引需要额外的时间开销执行写SQL时效率会降低性能会下降。
6.8 视图概念
MySQL中的视图view是一种虚拟表其内容由查询定义视图本身并不包含数据是一种数据库对象其内没有存储任何数据它只是对表的一个查询 作用 控制安全保存查询数据 优点 简化操作通过视图可以使用户将注意力集中在他所关心的数据上。使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件。提高数据的安全性在设计数据库时可以针对不同的用户定义不同的视图使用视图的用户只能访问他们被允许查询的结果集。数据独立视图的结构定义好之后如果增加新的关系或对原有的关系增加新的字段对用户访问的数据都不会造成影响。
6.9 创建视图
语法
create [or replace] [algorithm {undefined | merge | temptable}]view view_name [(column_list)]as select_statement[with [cascaded | local] check option]# 说明1、or replace如果要创建的视图名称已存在则替换已有视图。2、algorithm可选参数表示视图选择的算法默认算法是 undefined(1)undefined未定义指定算法(2)merge:更新视图表数据的同时会更新真实表的数据(3)temptable:只能查询不能更新3、view_name新建的视图名称。4、column_list可选表示视图的字段列表。若省略则使用 select 语句中的字段列表。5、as select_statement创建视图的 select 语句。6、with check option表示更新视图时要保证该视图的 where 子句为真。比如定义视图create view v1 as select * from salary 5000;如果要更新视图则必须保证 salary 字段的值在 5000 以上否则报错。(1)cascaded:必须满足所有针对该视图的条件才可以更新(2)local:只需满足本视图的条件就可以更新示例1:创建来源1张表的视图
mysql show databases;mysql use mydb9_stusys;mysql create view v_student as select sno,sname,ssex,year(now())-year(birth) as age from student;mysql select * from v_student;mysql show tables;示例2创建多表连接的视图
mysql create view v_score as select student.*, score from student join sc on student.sno sc.sno;
Query OK, 0 rows affected (0.02 sec)mysql select * from v_score;mysql show tables;mysql show create view v_score;示例3创建视图字段起别名
mysql create or replace view v_avg(sex,avg_score) as select ssex , round(avg(score),2) from student inner join sc on student.snosc.sno group by ssex;
Query OK, 0 rows affected (0.01 sec)mysql select * from v_avg;
-----------------
| sex | avg_score |
-----------------
| 女 | 73.52 |
| 男 | 78.51 |
-----------------6.10 更新视图 更新视图中的数据实际上是更新创建视图时用到的基本表中的数据 以下视图不可更新 包含以下关键字的 SQL 语句聚合函数、distinct、group by 、having、union 或 uinon allselect 中包含子查询from 一个不可更新的试图where 子句的子查询引用了 from 子句中的表。 示例4创建视图限制更新
mysql create or replace view v_age as select sno,sname,ssex,sage from student where sage20 with check option; # 增加限制更新参数
Query OK, 0 rows affected (0.00 sec)mysql select * from v_age;mysql update v_age set sage 24 where sno s011; # 更新视图24符合条件
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from student; # 查看视图的基本表数据已经变更# 若更新时条件不符合where字句则限制更新
mysql update v_age set sage 18 where sno s011; # 18岁不符合where子句
ERROR 1369 (HY000): CHECK OPTION failed mydb9_stusys.v_age示例5视图中聚合函数不可更新
mysql select * from v_student;mysql update v_student set age30 where snos001;
ERROR 1348 (HY000): Column age is not updatable示例6对分组和having字段不可更新
mysql select * from v_avg;
-----------------
| sex | avg_score |
-----------------
| 女 | 73.52 |
| 男 | 78.51 |
-----------------
2 rows in set (0.00 sec)mysql update v_avg set avg_score80 where sex女;
ERROR 1288 (HY000): The target table v_avg of the UPDATE is not updatable6.11 修改视图
通过 create or replace view 命令修改视图
mysql desc v_student; # 查看结构mysql create or replace view v_student as select sno, sname, ssex, sage from student; # 将age直接读取通过 alter view 命令修改视图
mysql alter view v_student as select sno, sname, ssex, sage from student where ssex女;
Query OK, 0 rows affected (0.02 sec)mysql select * from v_student;6.12 删除视图
格式
drop view [if exists] view_name;示例7
mysql show tables;mysql drop view v_age;mysql drop view v_avg;mysql drop view v_score;mysql drop view v_student;6.13 视图使用规则 视图必须有唯一命名 在mysql中视图的数量没有限制 创建视图必须从管理员那里获得必要的权限 视图支持嵌套也就是说可以利用其他视图检索出来的数据创建新的视图 在视图中可以使用order by但是如果视图内已经使用该排序子句则视图的order by将覆盖前面的order by。 视图不能索引也不能关联触发器或默认值 视图可以和表同时使用
七、SQL编程
7.1 存储过程
7.1.1 存储过程概念
存储过程(Stored Procedure): 是一组为了完成特定功能的SQL语句集合将常用且复杂的SQL语句预先写好然后用一个指定名称存储起来该存储过程会经过MySQL编译解析、执行优化后存储在数据库中因此称为存储过程。当以后需要使用这个过程时只需调用根据名称调用即可
存储过程和函数的区别 函数必须有返回值而存储过程没有。存储过程的参数可以是IN、OUT、INOUT类型函数的参数只能是IN
优点
复用性存储过程被创建后可以在程序中被反复调用不必重新编写该存储过程的SQL语句同时库表结构发生更改时只需要修改数据库中的存储过程无需修改业务代码也就意味着不会影响到调用它的应用程序源代码。灵活性普通的SQL语句很难像写代码那么自由而存储过程可以用流程控制语句编写也支持在其中定义变量有很强的灵活性可以完成复杂的条件查询和较繁琐的运算。省资源普通的SQL一般都会存储在客户端如Java中的dao/mapper层每次执行SQL需要通过网络将SQL语句发送给数据库执行而存储过程是保存在MySQL中的因此当客户端调用存储过程时只需要通过网络传送存储过程的调用语句和参数无需将一条大SQL通过网络传输从而可降低网络负载高性能存储过程执行多次后会将SQL语句编译成机器码驻留在线程缓冲区在以后的调用中只需要从缓冲区中执行机器码即可无需再次编译执行从而提高了系统的效率和性能。安全性对于不同的存储过程可根据权限设置执行的用户因此对于一些特殊的SQL例如清空表这类操作可以设定root、admin用户才可执行。同时由于存储过程编写好之后对于客户端而言是黑盒的因此减小了SQL被暴露的风险。
缺点
CPU开销大如果一个存储过程中涉及大量逻辑运算工作会导致MySQL所在的服务器CPU飙升因而会影响正常业务的执行有可能导致MySQL在线上出现抖动毕竟MySQL在设计时更注重的是数据存储和检索对于计算性的任务并不擅长。内存占用高为了尽可能的提升执行效率因此当一个数据库连接反复调用某个存储过程后MySQL会直接将该存储过程的机器码放入到连接的线程私有区中当MySQL中的大量连接都在频繁调用存储过程时这必然会导致内存占用率同样飙升。维护性差一方面是过于复杂的存储过程普通的后端开发人员很难看懂毕竟存储过程类似于一门新的语言不同语言之间跨度较大。另一方面是很少有数据库的存储过程支持Debug调试MySQL的存储过程就不支持这也就意味着Bug出现时无法像应用程序那样正常调试排查必须得采取“人肉排查”模式即一步步拆解存储过程并排查。
7.1.2 存储过程创建与调用
语法
# 1.定义
delimiter 自定义结束符号
create procedure 储存名([IN | OUT | INOUT]参数名 类型...)beginSQL语句end 自定义结束符号
delimiter ;# 2.调用
call 存储过程名(实参列表)# 注1自定义符号可以用除了分号的符号一般用$$ 或 //# 注2存储过程的参数形式IN 输入参数OUT 输出参数INOUT 输入输出参数在MySQL的存储过程中delimiter的作用是改变执行语句的分隔符。在MySQL中delimiter是用于指定命令是否结束的符号默认情况下是分号;。使用delimiter命令来更改默认的分隔符例如将分隔符更改为//或$$这样MySQL就会将//或$$之后的分号视为代码块的结束而不是单个语句的结束。这种做法在定义存储过程、函数或其他数据库对象时特别有用因为它允许开发者在一个代码块中编写多个语句而不需要为每个语句后都加上分号。当代码块编写完成后可以通过将delimiter命令设置回默认的分号来恢复正常的语句执行方式。 示例
mysql use mydb2_stuinfo;mysql delimiter $$mysql mysql create procedure proc01()- begin- select name,sex,age from student1;- end $$
mysql delimiter ; # 注意空格mysql call proc01();# 使用存储过程插如多条数据mysql create table passwd(id int,pwds varchar(50)); mysql delimiter $$mysql create procedure proc02() begin declare i int default 1; while(i10000) do insert into passwd values(i, md5(i)); set ii1; end while; end $$mysql delimiter ;mysql call proc02();mysql select * from passwd;删除格式
drop procedure 过程名;# 示例
mysql drop procedure proc01; # 不用加园括号7.1.3 变量
局部变量
局部变量: 用户自定义在begin/end块中有效格式
# 声明变量
declare var_name type [default var_value];
# 举例
declare nickname varchar(32);示例
mysql delimiter $$# 定义局部变量
mysql create procedure proc03() begin declare var_name01 varchar(20) default aaa; set var_name01 zhangsan; select var_name01; end $$mysql delimiter ;mysql call proc03();
------------
| var_name01 |
------------
| zhangsan |
------------MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下
# 格式
select col_name [...] into var_name[,...]
from table_name where condition # 其中col_name 参数表示查询的字段名称var_name 参数是变量的名称table_name 参数指表的名称condition 参数指查询条件。# 注意当将查询结果赋值给变量时该查询语句的返回结果只能是单行单列。示例
mysql delimiter $$mysql create procedure proc04() begin declare var_name02 varchar(20) ; select name into var_name02 from student1 where id1003; select var_name02; end $$mysql delimiter ;mysql call proc04();
------------
| var_name02 |
------------
| 李四 |
------------用户变量
用户变量用户自定义当前会话连接有效。类比java的成员变量格式
var_name# 不需要提前声明使用即声明即无declare子句示例
mysql delimiter $$mysql create procedure proc05() begin set var_name03 openlab; end $$mysql delimiter ;mysql call proc05() ;mysql select var_name03 ;
-------------
| var_name03
-------------
| openlab |
-------------系统变量
系统变量 系统变量又分为全局变量与会话变量 在MYSQL启动的时候由服务器自动将它们初始化为默认值这些默认值可以通过更改my.ini这个文件来更改。 会话变量在每次建立一个新的连接的时候由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。 也就是说如果在建立会话以后没有手动更改过会话变量与全局变量的值那所有这些变量的值都是一样的。 全局变量与会话变量的区别就在于对全局变量的修改会影响到整个服务器但是对会话变量的修改只会影响到当前的会话也就是当前的数据库连接。 有些系统变量的值是可以利用语句来动态进行更改的但是有些系统变量的值却是只读的对于那些可以更改的系统变量我们可以利用set语句进行更改。 系统变量-全局变量由系统提供在整个数据库有效
语法
global.var_name示例
# 查看全局变量
show global variables; # 查看某全局变量
select global.auto_increment_increment; # 修改全局变量的值
set global sort_buffer_size 40000;
set global.sort_buffer_size 40000;注意 global 与 global 等价都是系统全局变量 SET global 用于更改全局系统变量的值影响所有客户端连接 SET GLOBAL一般用于查看全局变量的当前值 系统变量-会话变量由系统提供当前会话连接有效格式
# 语法
session.var_name示例
# 查看会话变量
show session variables;# 查看某会话变量
select session.auto_increment_increment;# 修改会话变量的值
set session sort_buffer_size 50000;
set session.sort_buffer_size 50000 ;mysql变量种类总结 用户变量以””开始形式为”变量名”。用户变量跟mysql客户端是绑定的设置的变量只对当前用户使用的客户端生效全局变量定义时以如下两种形式出现set GLOBAL 变量名 或者 set global.变量名对所有客户端生效。只有具有super权限才可以设置全局变量会话变量只对连接的客户端有效。局部变量作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。可以使用set语句是设置不同类型的变量包括会话变量和全局变量
7.1.4 参数传递
IN表示传入的参数 可以传入数值或者变量即使传入变量并不会更改变量的值可以内部更改仅仅作用在函数范围内。
# 接着passwd表的例子
mysql delimiter $$mysql create procedure proc06(in a int) begin declare i int default 1; while(ia) do insert into passwd values(i,md5(i)); set ii1; end while; end $$mysql delimiter ;mysql set num100; # 定义用户变量mysql select num; # 查看
------
| num |
------
| 100 |
------mysql call proc06(num); # 执行过程传递变量值到过程中mysql select * from passwd;out表示从存储过程内部传值给调用者,in的反向传递
mysql delimiter $$mysql create procedure proc07(out cnt1 int) begin select count(*) into cnt1 from passwd; end $$mysql delimiter ;mysql call proc07(cnt2);mysql select cnt2;
-------
| cnt2 |
-------
| 10099 |
-------# in和out的综合使用# 例1统计指定部门的员工数
mysql use mydb7_openlab;mysql select * from dept;
------------------
| dept1 | dept_name |
------------------
| 101 | 财务 |
| 102 | 销售 |
| 103 | 运维 |
| 104 | 行政 |
------------------
4 rows in set (0.00 sec)mysql select * from emp_new;
---------------------------------------------------
| sid | name | age | worktime_start | incoming | dept2 |
---------------------------------------------------
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
---------------------------------------------------mysql delimiter $$mysql create procedure proc08(in d_num int, out cnt1 int) begin select count(*) into cnt1 from emp_new where dept2d_num; end$$mysql delimiter ;mysql call proc08(101,cnt2);mysql select cnt2;# 例2统计指定部门工资超过例如5000的总人数
mysql delimiter $$mysql create procedure proc09(in d_num int, in wages int, out cnt1 int) begin select count(*) into cnt1 from emp_new where dept2d_num and incomingwages; end$$mysql delimiter ;mysql call proc09(102,5000,wa);mysql select wa;
------
| wa |
------
| 2 |
------inout
mysql delimiter $$mysql create procedure proc10(inout p1 int) begin if (p1 is not null) then set p1p11; else select 100 into p1; end if; end$$mysql delimiter ;mysql select h;
------
| h |
------
| NULL |
------mysql call proc10(h);mysql select h;
------
| h |
------
| 100 |
------总结 in 输入参数意思说你的参数要传到存过过程的过程里面去在存储过程中修改该参数的值不能被返回out 输出参数:该值可在存储过程内部被改变并向外输出inout 输入输出参数既能输入一个值又能传出来一个值
7.1.5 存储过程的管理
通过以下命令管理存储过程
SHOW PROCEDURE STATUS; # 查看当前数据库中的所有存储过程。SHOW PROCEDURE STATUS WHERE db 库名 AND NAME 过程名; # 查看指定库中的某个存储过程。SHOW CREATE PROCEDURE 存储过程名; # 查看某个存储过程的源码。ALTER PROCEDURE 存储过程名称 .... # 修改某个存储过程的特性。DROP PROCEDURE 存储过程名; # 删除某个存储过程。7.1.6 存储过程的应用场景
存储过程难以维护同时拓展性和移植性都很差因此大多数的开发规范中都会明令禁止使用如存储过程在《阿里开发手册》中是强制禁止使用的它是一把双刃剑用的好其实能够带来不小的收益推荐在以下场合使用 插入测试数据时一般为了测试项目都会填充测试数据往常是写Java-for跑数据但现在可以用存储过程来批量插入它的效率会比用for循环快上无数倍毕竟从Java传递SQL需要时间拿到SQL后还要经过解析、优化…一系列工作而用存储过程则不会有这些问题。对数据做批处理时也可以用存储过程来跑比如将一个表中的数据洗到另外一张表时就可以利用存储过程来处理。一条SQL无法完成的、需要应用程序介入处理的业务尤其是组成起来SQL比较长时也可以编写一个存储过程然后客户端调用即可。
7.2 流程控制
7.2.1 if判断
IF语句包含多个条件判断根据结果为TRUE、FALSE执行语句与编程语言中的if、else if、else语法类似其语法格式如下
if 条件判断
then-- 分支操作.....
elseif 条件判断
then-- 分支操作.....
else-- 分支操作.....
end if例1定义了一个判断年龄的存储过程
mysql delimiter $$mysql create procedure if_user_age(in age int,out msg varchar(255))
beginif age 18 thenset msg :未成年;elseif age 18thenset msg :刚成年;elseset msg :已成年;end if;
end $$mysql delimiter ;mysql set msg:Not Data;mysql call if_user_age(16,msg);mysql select msg;
--------
| msg |
--------
| 未成年 |
--------例2输入学生的成绩来判断成绩的级别
/*score 60 :不及格score 60 , score 80 :及格score 80 , score 90 :良好score 90 , score 100 :优秀score 100 : 成绩错误
*/mysql delimiter $$mysql create procedure proc11_if(in score int)
beginif score 60 thenselect 不及格;elseif score 80thenselect 及格 ;elseif score 80 and score 90then select 良好;elseif score 90 and score 100then select 优秀;elseselect 成绩错误;end if;
end $$mysql delimiter ;mysql call proc11_if(120);mysql call proc11_if(86);例3输入员工的名字判断工资的情况。
mysql use mydb7_openlab;mysql select * from emp_new;
---------------------------------------------------
| sid | name | age | worktime_start | incoming | dept2 |
---------------------------------------------------
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
---------------------------------------------------mysql delimiter $$mysql create procedure proc12_if(in in_ename varchar(50))
begindeclare result varchar(20);declare var_sal int;select incoming into var_sal from emp_new where name in_ename;if var_sal 3000 then set result 试用薪资;elseif var_sal8000then set result 转正薪资;else set result 元老薪资;end if;select result;
end$$mysql delimiter ;mysql call proc12_if(赵六);mysql call proc12_if(荣八);7.2.2 case判断
case是另一个条件判断的语句类似于C编程语言中的switch语法格式
# 语法1
case变量
when值1 then
-- 分支操作1....
when值2 then
-- 分支操作2....
.....
else
-- 分支操作n....
endcase;# 语法2
case
when 条件判断1 then
-- 分支操作1....
when 条件判断2 then
-- 分支操作2....
.....
else
-- 分支操作n....
endcase;例1
mysql delimiter $$
mysql create procedure proc13_case(in pay_type int)
begincase pay_typewhen 1 then select 微信支付 ;when 2 then select 支付宝支付 ;when 3 then select 银行卡支付;else select 其他方式支付;end case ;
end $$mysql delimiter ;mysql call proc13_case(2);mysql call proc13_case(4);例2
mysql delimiter $$mysql create procedure proc14_case(in score int)
begincasewhen score 60 then select 不及格;when score 80 then select 及格 ;when score 80 and score 90 then select 良好;when score 90 and score 100 then select 优秀;else select 成绩错误;end case;
end $$mysql delimiter ;mysql call proc14_case(88);mysql call proc14_case(120);7.2.3 循环
循环分类 whilerepeatloop 循环控制 leave 类似于 break跳出结束当前所在的循环iterate类似于 continue继续结束本次循环继续下一次 while循环格式
[标签:]while 循环条件 do循环体;
end while[标签];mysql select database();
---------------
| database() |
---------------
| mydb7_openlab |
---------------# 创建测试表
mysql create table user (uid int primary key,username varchar(50),password varchar(50));# 存储过程-while
mysql delimiter $$mysql create procedure proc15_while1(in insertcount int)
begindeclare i int default 1;label:while iinsertcount doinsert into user(uid,username,password) values(i,concat(user-,i),123456);set ii1;end while label;
end $$mysql delimiter ;mysql call proc15_while1(10);mysql select * from user;
-------------------------
| uid | username | password |
-------------------------
| 1 | user-1 | 123456 |
| 2 | user-2 | 123456 |
| 3 | user-3 | 123456 |
| 4 | user-4 | 123456 |
| 5 | user-5 | 123456 |
| 6 | user-6 | 123456 |
| 7 | user-7 | 123456 |
| 8 | user-8 | 123456 |
| 9 | user-9 | 123456 |
| 10 | user-10 | 123456 |
-------------------------# 存储过程-while leave
mysql truncate table user; # 清空表内容mysql delimiter $$mysql create procedure proc16_while2(in insertcount int)
begindeclare i int default 1;label:while iinsertcount doinsert into user(uid,username,password) values(i,concat(user-,i),123456);if i5 then leave label;end if;set ii1;end while label;
end $$mysql delimiter ;mysql call proc16_while2(10);mysql select * from user;
-------------------------
| uid | username | password |
-------------------------
| 1 | user-1 | 123456 |
| 2 | user-2 | 123456 |
| 3 | user-3 | 123456 |
| 4 | user-4 | 123456 |
| 5 | user-5 | 123456 |
-------------------------# 存储过程-whileiterate
mysql truncate table user;mysql delimiter $$mysql create procedure proc17_while3(in insertcount int)
begindeclare i int default 1;label:while iinsertcount doset ii1;if i5 then iterate label;end if;insert into user(uid,username,password) values(i,concat(user-,i),123456);end while label;
end $$mysql delimiter ;mysql call proc17_while3(10);mysql select * from user; # 没有第五条记录
-------------------------
| uid | username | password |
-------------------------
| 2 | user-2 | 123456 |
| 3 | user-3 | 123456 |
| 4 | user-4 | 123456 |
| 6 | user-6 | 123456 |
| 7 | user-7 | 123456 |
| 8 | user-8 | 123456 |
| 9 | user-9 | 123456 |
| 10 | user-10 | 123456 |
| 11 | user-11 | 123456 |
-------------------------repeat循环格式
[标签:]repeat 循环体;until 条件表达式
end repeat [标签];# 存储过程-循环控制-repeat
mysql truncate table user;mysql delimiter $$mysql create procedure proc18_repeat(in insertCount int)
begindeclare i int default 1;label:repeatinsert into user(uid, username, password) values(i,concat(user-,i),123456);set i i 1;until i insertCountend repeat label;select 循环结束;
end $$mysql delimiter ;mysql call proc18_repeat(100);loop循环格式
[标签:] loop循环体;if 条件表达式 then leave [标签]; end if;
end loop;# 存储过程-循环控制-loop
mysql truncate table user;mysql delimiter $$mysql create procedure proc19_loop(in insertCount int)
begindeclare i int default 1;label:loopinsert into user(uid, username, password) values(i,concat(user-,i),123456);set i i 1;if i 5 then leave label;end if;end loop label;select 循环结束;
end $$mysql delimiter ;mysql call proc19_loop(10);7.3 触发器
触发器trigger是一个特殊的存储过程存储过程需要人为手动调用而触发器是由事件来触发 触发器经常用于加强数据的完整性约束和业务规则等 7.3.1 创建触发器
语法结构
1、创建只有一个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;2、创建有多个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin执行语句列表
end;# 说明触发器名称 最多64个字符它和MySQL中其他对象的命名方式一样{ before | after } 触发器时机{ insert | update | delete } 触发的事件on 表名称 标识建立触发器的表名即在哪张表上建立触发器for each row 触发器的执行间隔通知触发器每隔一行执行一次动作而不是对整个表执行一次触发器程序体 要触发的SQL语句可用顺序判断循环等语句实现一般程序需要的逻辑功能注意每个触发器创建后必然是附着在一张表上的因为在创建触发器的时候必须要指定表名它会监控这张表上发生的事件。 7.3.2 NEW与OLD
MySQL 中定义了 NEW 和 OLD用来表示触发器的所在表中触发了触发器的那一行数据来引用触发器中发生变化的记录内容具体地
触发器类型触发器类型NEW 和OLD 的使用INSERT 型触发器NEW 表示将要或者已经新增的数据UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据DELETE 型触发器OLD 表示将要或者已经删除的数据
使用方法 NEW.columnName columnName为相应数据表某一列名
示例
1.创建表tab1
mysql create table tab1(id int primary key auto_increment,name varchar(50),sex enum(m,f),age int);2.创建表tab2
mysql create table tab2(id int primary key auto_increment,name varchar(50),salary double(10,2));3.创建触发器1.创建触发器tab1_after_insert_trigger 作用当tab1增加记录后自动增加到tab2
mysql create trigger tab1_after_insert_trigger after insert on tab1 for each row insert into tab2 values(id, new.name, 5000); 3.创建触发器tab1_after_update_trigger ,作用当tab1更新后自动更新tab2
mysql create trigger tab1_after_update_trigger after update on tab1 for each row update tab2 set namenew.name where nameold.name; 2.创建tab1_after_delete_trigger触发器作用tab1表删除记录后自动将tab2表中对应记录删除
mysql create trigger tab1_after_delete_trigger after delete on tab1 for each row delete from tab2 where nameold.name;4.查看触发器
mysql show triggers\G5测试
mysql insert into tab1 values(1,张三,m,22);
Query OK, 1 row affected (0.01 sec)mysql select * from tab1;
----------------------
| id | name | sex | age |
----------------------
| 1 | 张三 | m | 22 |
----------------------
1 row in set (0.00 sec)mysql select * from tab2;
-------------------
| id | name | salary |
-------------------
| 1 | 张三 | 5000.00 |
-------------------
1 row in set (0.00 sec)mysql update tab1 set name李四 where name张三;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from tab1;
----------------------
| id | name | sex | age |
----------------------
| 1 | 李四 | m | 22 |
----------------------
1 row in set (0.00 sec)mysql select * from tab2;
-------------------
| id | name | salary |
-------------------
| 1 | 李四 | 5000.00 |
-------------------
1 row in set (0.00 sec)mysql delete from tab1 where name李四;
Query OK, 1 row affected (0.01 sec)mysql select * from tab1;
Empty set (0.00 sec)mysql select * from tab2;
Empty set (0.00 sec)注意
MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作以免递归循环触发尽量少使用触发器假设触发器触发每次执行1sinsert table 500条数据那么就需要触发500次触发器光是触发器执行的时间就花费了500s而insert 500条数据一共是1s那么这个insert的效率就非常低了。触发器是针对每一行的对增删改非常频繁的表上切记不要使用触发器因为它会非常消耗资源。
7.4 存储函数
MySQL存储函数自定义函数一般用于计算和返回一个值可以将经常需要使用的计算或功能写成一个函数。函数和存储过程类似 存储函数与存储过程的区别 存储函数有且只有一个返回值而存储过程可以有多个返回值也可以没有返回值。存储函数只能有输入参数而且不能带in, 而存储过程可以有多in,out,inout参数。存储过程中的语句功能更强大存储过程可以实现很复杂的业务逻辑而函数有很多限制如不能在函数中使用insert,update,delete,create等语句存储函数只完成查询的工作可接受输入参数并返回一个结果也就是函数实现的功能针对性比较强。存储过程可以调用存储函数。但函数不能调用存储过程。存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
7.4.1 存储函数创建和调用create function
创建存储函数在MySQL中创建存储函数使用 create function 关键字其基本形式如下
create function func_name ([param_name type[,...]])
returns type
[characteristic ...]
beginroutine_body
END;# 参数说明
1func_name 存储函数的名称。
2param_name type可选项指定存储函数的参数。type参数用于指定存储函数的参数类型该类型可以是MySQL数据库中所有支持的类型。
3returns type指定返回值的类型。
4characteristic可选项指定存储函数的特性。
5routine_bodySQL代码内容。调用存储函数 在MySQL中存储函数的使用方法与MySQL内部函数的使用方法基本相同用户自定义的存储函数与MySQL内部函数性质相同。区别在于存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下
select func_name([parameter[,…]]);例1 无参数存储函数
mysql set global log_bin_trust_function_creatorsTRUE; # 信任子程序的创建者否则可能报错,执行一次即可# 创建存储函数-没有输输入参数mysql delimiter $$mysql create function myfunc1_emp()
returns int
begindeclare cnt int default 0;select count(*) into cnt from emp_new;return cnt;
end $$mysql delimiter ;# 调用存储函数
mysql select myfunc1_emp();
---------------
| myfunc1_emp() |
---------------
| 6 |
---------------例2有参数存储函数
mysql select * from emp_new;
---------------------------------------------------
| sid | name | age | worktime_start | incoming | dept2 |
---------------------------------------------------
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
---------------------------------------------------mysql delimiter $$mysql create function myfunc2_emp(in_sid int)
returns varchar(50)
begindeclare out_name varchar(50);select name into out_name from emp_new where sid in_sid;return out_name;
end $$mysql delimiter ;mysql select myfunc2_emp(1776);
-------------------
| myfunc2_emp(1776) |
-------------------
| 王五 |
-------------------7.4.2 删除存储函数drop function MySQL中使用 drop function 语句来删除存储函数。 示例删除存储函数。
mysql drop function if exists myfunc1_emp;mysql drop function if exists myfunc2_emp;八 MySQL事务
事务Transaction一个最小的不可再分的工作单元一个事务对应一个完整的业务一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成事务只针对DML语句Data Manipulation Language(数据操作语言)eg.insert、update、delete
数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列这些操作要么全部执行,要么全部不执行是一个不可分割的工作单位事务由事务开始与事务结束之间执行的全部数据库操作组成 mysql中只有Innodb是支持事务
mysql show engines; # 查看Mysql的所有执行引擎可以到默认的执行引擎是innoDB 支持事务行级锁定和外键。示例 以银行转账为例账户转账是一个完整的业务最小的单元不可再分——也就是说银行账户转账是一个事务 update account set money money - 200 where id 1;
update account set money money 200 where id 2;分析 以上两个DML语句必须同时成功或者同时失败最小单元不可再分当第一条DML语句执行成功后并不能将底层数据库中的第一个账户的数据修改只是将操作记录了一下这个记录是在内存中完成的当第二条DML语句执行成功后和底层数据库文件中的数据完成同步若第二条DML语句执行失败则清空所有的历史操作记录要完成以上的功能必须借助事务
8.1 数据库事务处理原则
保证所有事务都作为一个工作单元来执行即使出现了故障都不能改变这种执行方式当在一个事务中执行多个操作时要么所有的事务都被提交(commit)那么这些修改就永久地保存下来要么数据库管理系统将放弃所作的所有修改整个事务回滚(rollback)到最初状态。为确保数据库中数据的一致性数据的操纵应当是离散的成组的逻辑单元当它全部完成时数据的一致性可以保持而当这个单元中的一部分操作失败整个事务应全部视为错误所有从起始点以后的操作应全部回退到开始状态
8.2 事务的特性面试-ACID
8.2.1 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位事务中的操作要么都发生要么都不发生保证事务的整体性 例zhangsan给lisi转帐200元钱的时候只执行了扣款语句就提交了此时如果突然断电zhangsan账号已经发生了扣款lisi账号却没收到加款在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么都执行要么就都不执行 8.2.2 一致性(Consistency)
一致性是指事务必须使数据库从一个一致性状态变换到另外一个一致性状态分析 当事务完成时数据必须处于一致状态在事务开始前数据库中存储的数据处于一致状态在正在进行的事务中数据可能处于不一致的状态当事务成功完成时数据必须再次回到一致状态 例对银行转帐事务不管事务成功还是失败应该保证事务结束后表中zhangsan和lisi的存款总额跟事务执行前一致如zhangsan的账户有1000元lisi的账户有1000元现在zhangsan转账200元给lisi不管事务成功还是失败转账前后zhangsan和lisi的存款总额都应该是2000元 8.2.3 隔离性(Isolation)
隔离性是多个用户并发访问数据库时数据库为每一个用户开启的事务不能被其他事务的操作数据所干扰多个并发事务之间要相互隔离每个事务都有各自的完整数据空间
8.2.4 持久性(Durability)
持久性是指一个事务一旦被提交它对数据库中数据的改变就是永久性的接下来即使数据库发生故障也不应该对其有任何影响 注意ACID面试常考
8.3 MySQL使用事务
8.3.1 MySQL的事务操作主要有以下三种
1.开启事务start transaction 任何一条DML语句(insert、update、delete)执行标志事务的开启命令begin 或 start transaction 2.提交事务commit transaction 成功的结束将所有的DML语句操作历史记录和底层硬盘数据来一次同步命令commit 3.回滚事务rollback transaction 失败的结束将所有的DML语句操作历史记录全部清空命令rollback
8.3.2 使用事务的方法
方式1方法2方法3方法4beginstart transactionset autocommit1set autocommit0sql语句sql语句sql语句sql语句commit | rollbackcommit | rollbackcommit | rollback
分析
开始事务begin 或 start transaction 注意mysql5.5 以上的版本不需要手工书写begin只要你执行的是一个DML会自动在前面加一个begin命令此方法又称为显示开启事务MySQL中用 autocommit参数设置事务是否自动提交commit 手动提交事务一旦事务提交成功 就说明具备ACID特性了rollback 回滚事务将内存中已执行过的操作回滚回去 注意 事务是基于当前数据库连接而言的而不是基于表一个事务可以由操作不同表的多条SQL组成 下面画出了两个数据库连接假设连接A中开启了一个事务那后续过来的所有SQL都会被加入到一个事务中也就是图中连接A后面的SQL②、SQL③、SQL④、SQL⑤这四条都会被加入到一个事务中只要在未曾收到commit/rollback命令之前这个连接来的所有SQL都会加入到同一个事务中因此对于这点要牢记开启事务后一定要做提交或回滚处理。 8.3.3 自动提交策略
MySQL默认已经开启自动提交可以通过对应的设置来开启或者关闭自动提交
查看自动提交设置
mysql select autocommit; # 用于查看全局变量mysql show variables like autocommit; # 或者设置自动提交
mysql set autocommit0; # 针对当前连接会话mysql set global autocommit0; # 针对全局事务# 可以在C:\ProgramData\MySQL\MySQL Server 8.0\my.ini配置文件中增加一行
autocommit0 注意一般在有事务需求的MySQL中将其关闭使用begin手动开启所以不管有没有事务需求都建议设置为0可以很大程度上提高数据库性能 示例1 测试begin事务的一致性
mysql create database mydb17_transcation;mysql use mydb17_transcation;mysql create table account (id int(10) primary key not null, name varchar (40), money double);mysql insert into account values(1, 张三, 1000);mysql insert into account values(2, 李四, 2000);mysql begin;mysql update account set money3000 where id2;mysql select * from account;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
-------------------mysql exit; # 断开连接
Bye
PS C:\Users\Administrator mysql -uroot -p123456mysql use mydb17_transcation;mysql show tables;mysql select * from account;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 1000 |
| 2 | 李四 | 2000 | # 发现未提交事务数据没变
-------------------# 手动提交事务
mysql begin;mysql update account set money3000 where id2;mysql commit; # 提交事务mysql exit;
Bye
PS C:\Users\Administrator mysql -uroot -p123456mysql use mydb17_transcation;mysql select * from account;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 | # 内容改变
-------------------# 回滚事务
mysql select * from account;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
-------------------
2 rows in set (0.00 sec)mysql begin;mysql delete from account where id1; # 删除记录mysql select * from account;
-------------------
| id | name | money |
-------------------
| 2 | 李四 | 3000 |
-------------------
1 row in set (0.01 sec)mysql rollback; # 回滚事务mysql select * from account;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
-------------------示例2 关闭自动提交
mysql select autocommit;
--------------
| autocommit |
--------------
| 1 |
--------------mysql set autocommit0;mysql select autocommit;
--------------
| autocommit |
--------------
| 0 |
--------------mysql insert into account values(3, 王五, 3000); # 省略beginmysql commit; # 提交mysql select * from account;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 1000 |
| 2 | 李四 | 3000 |
| 3 | 王五 | 3000 |
-------------------8.3.4 设置回滚点
作用可以创建一个或多个回滚点将事务回滚到标记点格式
savepoint 回滚点名rollback to 回滚点# 注意手动设置回滚点需要关闭事务自动提交若自动提交已开启则语句执行后代表事务已提交所有回滚点都会删除此时执行手动回滚会报错即
ERROR 1305 (42000): SAVEPOINT ** does not exist示例
mysql exitPS C:\Users\Administrator mysql -uroot -p123456mysql use mydb17_transcation;mysql select autocommit;
--------------
| autocommit |
--------------
| 1 |
--------------
1 row in set (0.00 sec)mysql set autocommit0; # 关闭自动提交mysql select * from account;mysql savepoint s1; # 创建1个回滚点mysql update account set moneymoney1000 where id1; # 金额1000mysql select * from account;mysql rollback to s1; # 回滚到s1mysql select * from account;8.3.5 事务的隐式提交
概念因为某些特殊的语句而导致事务自动提交的情况称为隐式提交
那些事务不会自动提交 使用start transaction开启事务使用begin开启事务把系统变量autocommit的值设置为OFF | 0 常见隐式提交 示例
mysql exitPS C:\Users\Administrator mysql -uroot -p123456mysql use mydb17_transcation;mysql select autocommit;
--------------
| autocommit |
--------------
| 1 |
--------------mysql insert into account values(4, 刘七, 4000);mysql insert into account values(5, 孙鹏, 5000);select * from account;mysql rollback; # 隐式提交后回滚mysql select * from account; # 未变化注意 在MySQL中为了避免隐式提交造成的隐患建议使用显式提交操作即通过手动执行COMMIT或ROLLBACK来明确事务的完成或回滚sql查询操作会在事务的上下文中执行但是它并没有对数据进行写的操作所以事务不会持有任何锁在查询结束后会立即提交因此InnoDB所有的操作都是在事务中进行的
8.4 InnoDB 事务的ACID如何保证
8.4.1 WAL技术 redo log又叫“重做日志”,是存储引擎层 (innoDB) 生成的日志记录的是物理级别上的页修改操作比如页号x偏移量y写入了’z’数据主要目的为了保证数据不丢失当MySQL发生宕机的时候可以利用redo log日志进行数据恢复用于确保事务的持久性 一条SQL更新语句怎么运行 当有一条记录要更新时InnoDB 引擎就会先把记录写到 redo log磁盘文件中此时更新就算结束了在系统空闲的时候将上述操作记录更新到磁盘文件中
eg.比如小店做生意有个粉板有个账本来客点餐了先写粉板等不忙的时候再写账本
性能不够缓存来凑由于CPU的性能远远大于磁盘为了消除这个鸿沟引入了两个缓存 Buffer Pool 缓冲池包含了磁盘中部分数据页的映射作为访问数据库的缓冲redo log bufferredo log本质是磁盘上的日志文件为提交读写增加了缓冲区来存放重做日志 WALWrite-Ahead Logging预写日志系统先写日志再写磁盘只有日志写入成功才算事务提交成功的技术思想在MySQL叫做WAL技术其基本过程如下 先将原始数据从磁盘中读入到Buffer Pool中修改Buffer Pool中的数据生成一条重做日志并写入redo log buffer记录数据修改后的值当事务提交时将redo log buffer中的内容追加磁盘中的redo log文件中将磁盘日志文件redo log file 内容刷到数据库表中(也称为“脏刷”) 为什么为什么要多此一举先写入到redo log磁盘文件中然后再脏刷到数据库表中而不直接落到数据库表中 数据在MySQL中存储是以页为单位事务中的数据可能遍布在不同的页中如果直接写入到对应的页中是随机IO写入磁盘顺序IO性能远高于随机IOredo log是通过顺序IO追加的方式写入到文件末尾只包含真正需要写入的无效 IO 减少但刷脏页以Page为单位一个Page上的修改整页都要写且为随机IO
8.4.2 redo log相关参数
redo log容量查看
mysql show variables like innodb_redo_log_capacity;
-------------------------------------
| Variable_name | Value |
-------------------------------------
| innodb_redo_log_capacity | 104857600 | # 默认100MB
-------------------------------------存储位置共生成32个 redo log 文件,每个文件的大小等于 1/32 * innodb_redo_log_capacityredo log 有两种:正在使用的和未被使用的分别使用 #ib_redoNN 和 #ib_redoNN_tmp其中NN是重做日志文件编号
C:\ProgramData\MySQL\MySQL Server 8.0\Data\#innodb_redo查看状态
mysql show global status like %innodb%redo%;
-------------------------------------------------
| Variable_name | Value |
-------------------------------------------------
| Innodb_redo_log_read_only | OFF |# 当前redo不是处于RO状态
| Innodb_redo_log_uuid | 3173314963 |# 归档用
| Innodb_redo_log_checkpoint_lsn | 635392598 |# 最新的checkpoint
| Innodb_redo_log_current_lsn | 635392598 |# 当前LSN
| Innodb_redo_log_flushed_to_disk_lsn | 635392598 |# 已刷盘的LSN
| Innodb_redo_log_logical_size | 512 |# 当前活跃事务使用log大小
| Innodb_redo_log_physical_size | 6553600 |# 当前正在使用中的log大小
| Innodb_redo_log_capacity_resized | 104857600 |# 总大小
| Innodb_redo_log_resize_status | OK |# 状态
| Innodb_redo_log_enabled | ON |
-------------------------------------------------8.4.3 MySQL CSR—前滚
MySQL在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致LSN 称为日志的逻辑序列号log sequence number在InnoDB存储引擎中LSN占8个字节LSN的值会随着日志的写入而逐渐变大内容包括 数据页的版本信息。写入的日志总量。通过LSN开始号码和结束号码可以计算出写入的日志量。可知道检查点的位置。…… 前滚过程
1.我们做了一个事务begin;update ....;commit;
2. begin时会立即分配一个TXIDtx_01.
3. update时,会将需要修改的数据页(dp_01,LSN101),加载到data buffer中
4. DBWR线程,会进行dp_01数据页修改更新,并更新LSN102
5. LOGBWR日志写线程,会将dp_01数据页的变化LSNTXID存储到redobuffer
6. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
7. 假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
8. MySQL再次重启时,要求redolog和磁盘数据页的LSN是一致的但是磁盘的值为LSN101,而redolog中LSN102
9.MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一致此时MySQL正常启动
10.以上的工作过程,我们把它称之为基于REDO的前滚操作8.4.4 undo 回滚日志
undo回滚的作用是保证事务的原子性和隔离性同时支持快照技术和数据恢复作用 原子性Atomicity通过undo可以在事务回滚时将数据恢复到修改之前的状态确保事务要么完全执行要么完全回滚避免了部分操作的影响。隔离性Isolation并发事务可能同时修改同一个数据使用undo可以提供事务的私有数据版本保证每个事务独立地看到修改之前的数据从而实现隔离性。快照技术undo提供了事务修改之前的数据状态的快照这意味着在事务执行过程中可以回退到之前的状态。这对于实现多版本并发控制MVCC非常重要每个事务可以读取数据的一致版本而不会受到其他事务的修改影响。数据恢复undo日志记录了事务的修改操作和之前的数据状态当系统发生故障或崩溃时可以使用undo日志来恢复未提交的事务或未完成的操作。这种数据恢复机制对于数据库的持久性和可靠性非常重要。
8.5 事务的隔离级别 隔离级别是指多个并发事务之间的隔离程度 多个线程开启各自事务操作数据库中数据时数据库系统要负责隔离操作以 保证各个线程在获取数据时的准确性 8.5.1 脏读
脏读(Dirty Read)指一个事务读取了另外一个事务未提交的数据在这种情况下如果另一个事务回滚则第一个事务读取到的数据实际上是无效的或错误的
示例如下图 分析上图中DB连接①/事务A正在执行下单业务目前扣减库存、增加订单两条SQL已经完成了恰巧此时DB连接②/事务B跑过来读取了一下库存剩余数量就将事务A已经扣减之后的库存数量读回去了。但好巧不巧事务A在添加物流信息时执行异常导致事务A全部回滚也就是原本扣的库存又会增加回去这种问题就是脏读其会导致数据不一致进而影响整个业务系统出现问题。 8.5.2 不可重复读
不可重复读(Non-repeatable Read)指在一个事务中多次读取同一数据先后读取到的数据不一致
示例如下 分析事务A执行下单业务时因为添加物流信息的时候出错了导致整个事务回滚事务回滚完成后事务A就结束了。但事务B却并未结束在事务B中在事务A执行时读取了一次剩余库存然后在事务回滚后又读取了一次剩余库存其中B事务第一次读到的剩余库存是扣减之后的第二次读到的剩余库存则是扣减之前的因为A事务回滚又加回去了。 上述案例中同一个事务中读取同一数据结果却并不一致也就说明了该数据存在不可重复读问题。要理解不可重复读和可重复读的意思是可重复读在同一事务中不管读取多少次读到的数据都是相同的。
8.5.3 幻读
示例 假设此时平台要升级用户表中的性别字段原本是以「男、女」的形式保存数据现在平台升级后要求改为「0、1」代替。因此事务A开始更改表中所有数据的性别字段当负责执行事务A的线程正在更改最后一条表数据时此时事务B来了正好向用户表中插入了一条「性别男」的数据并提交了然后事务A改完原本的最后一条数据后当再次去查询用户表时结果会发现表中依旧还存在一条「性别男」的数据似乎跟产生了幻觉一样。 幻读问题的原因是在于另外一个事务在第一个事务要处理的目标数据范围之内新增了数据然后先于第一个事务提交造成的问题。幻读和不可重复读的区别 不可重复读是同一条SQL查询的内容不同被修改了幻读是查询的结果条数不同增加了、或者删除了记录
8.5.4 事务的隔离级别
为了处理这些问题SQL标准定义了以下几种事务隔离级别 READ-UNCOMMITTED(读取未提交) 最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读、幻读或不可重复读。READ-COMMITTED(读取已提交) 允许读取并发事务已经提交的数据可以阻止脏读但是幻读或不可重复读仍有可能发生。REPEATABLE-READ(可重复读) 对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生。SERIALIZABLE(可串行化) 最高的隔离级别完全服从 ACID 的隔离级别。所有的事务依次逐个执行这样事务之间就完全不可能产生干扰也就是说该级别可以防止脏读、不可重复读以及幻读。 总结如下表
脏读不可重复读幻读Read uncommitted(读取未提交)√√√Read committed(读取已提交)×√√Repeatable read(可重复读)××√Serializable(可串行化)×xx
8.5.5 查看隔离级别
sql查询查看
mysql select GLOBAL.transaction_isolation, transaction_isolation;
---------------------------------------------------------
| GLOBAL.transaction_isolation | transaction_isolation |
---------------------------------------------------------
| REPEATABLE-READ | REPEATABLE-READ |
---------------------------------------------------------查看变量
mysql show variables like transaction_isolation;
----------------------------------------
| Variable_name | Value |
----------------------------------------
| transaction_isolation | REPEATABLE-READ |
----------------------------------------8.5.6 设置隔离级别
设置当前连接隔离
# 设置read uncommitted(读未提交)级别
mysql set session transaction isolation level read uncommitted;#设置read committed(读已提交)级别
mysql set session transaction isolation level read committed;#设置repeatable read(可重复度)级别
mysql set session transaction isolation level repeatable read;# 设置serializable(串行化)级别
mysql set session transaction isolation level serializable;# 查看当前级别
mysql show variables like transaction_isolation;设置全局隔离
mysql set global transaction isolation level 隔离级别;8.5.7 示例
准备工作建立并发环境并建立2个mysql链接且登录数据库
# 连接1
mysql use mydb17_transcationmysql create table ac (id int(2) primary key not null, name varchar (10), money double);mysql insert into ac values(1, 张三, 1000);mysql insert into ac values(2, 李四, 2000);mysql select * from ac;mysql show variables like transaction_isolation; # 查看当前隔离级别mysql set autocommit 0; # 设置取消自动提交# 连接2
mysql use mydb17_transcationmysql select * from ac;mysql set autocommit 0;read uncommitted(读未提交)级别
# 连接1
mysql set session transaction isolation level read uncommitted;mysql select * from ac;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 1000 |
| 2 | 李四 | 2000 |
-------------------# 连接2
mysql set session transaction isolation level read uncommitted;mysql begin; # 开始一个事务mysql update ac set money5000 where name张三; # 更新数据# 注意此时连接2的事务未提交# 连接1
mysql select * from ac;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 5000 |
| 2 | 李四 | 2000 |
-------------------# 连接1中前后2次查询结果不同# read uncommitted(读未提交)级别不管对方事务是否提交只要数据发生改变本连接就可以察觉到# 连接2
mysql commit; 提交事务read committed(读已提交)级别
# 连接1
mysql exit # 退出重新登录
Bye
PS C:\Users\Administrator mysql -uroot -p123456mysql use mydb17_transcationmysql set autocommit 0;mysql set session transaction isolation level read committed; # 重新设置隔离mysql show variables like transaction_isolation;mysql select * from ac;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 5000 |
| 2 | 李四 | 2000 |
-------------------# 连接2
mysql exit # 退出重新登录
Bye
C:\Users\Administratormysql -uroot -p123456mysql use mydb17_transcation;mysql set autocommit 0;mysql set session transaction isolation level read committed;mysql show variables like transaction_isolation;mysql begin;mysql update ac set money5000-1000 where name张三; # 转账mysql update ac set money20001000 where name李四; # 转账# 连接1
mysql select * from ac;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 5000 |
| 2 | 李四 | 2000 |
-------------------
# 此时查询由于连接2未提交隔离策略为read committed(读已提交)所以在连接2未提交以前连接1查询的结果不变# 连接2
mysql commit; # 提交事务# 连接1
mysql select * from ac;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 4000 |
| 2 | 李四 | 3000 |
-------------------
# 连接2事务提交后连接1查询的数据发生变化repeatable read(可重复读)级别该隔离级别为mysql的默认隔离级别对某字段进行操作时其他事务禁止操作该字段所以总能保持读取数据的一致性
# 连接1
mysql exit # 退出重新登录
Bye
PS C:\Users\Administrator mysql -uroot -p123456mysql use mydb17_transcationmysql set autocommit 0;mysql show variables like transaction_isolation; # 查看发现为默认级别
----------------------------------------
| Variable_name | Value |
----------------------------------------
| transaction_isolation | REPEATABLE-READ |
----------------------------------------# 连接2
mysql exit # 退出重新登录
Bye
C:\Users\Administrator mysql -uroot -p123456mysql use mydb17_transcationmysql set autocommit 0;mysql show variables like transaction_isolation; # 查看发现为默认级别# 连接1
mysql select * from ac;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 4000 |
| 2 | 李四 | 3000 |
-------------------
2 rows in set (0.00 sec)mysql begin; # 连接1开启事务mysql update ac set money4000-1000 where name张三; # 模拟转账mysql update ac set money30001000 where name李四; # 模拟转账# 注意此时连接1的事务还未提交# 连接2
mysql begin;mysql select * from ac;
-------------------
| id | name | money |
-------------------
| 1 | 张三 | 4000 |
| 2 | 李四 | 3000 |
-------------------
2 rows in set (0.00 sec)mysql update ac set money8000 where name张三;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction# 等待.....之后上例报错超过锁定等待超时尝试重新启动事务
# 由于连接1的update事务未提交此时会给表增加update语句独占锁此时其他事务对持有独占锁的记录进行修改时就会被阻塞# 连接1
mysql commit; # 提交事务# 连接2
mysql update ac set money8000 where name张三; # 执行成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0serializable串行化级别该隔离模式下执行的事务在对某表进行操作期间禁止其他所有事务对该表进行任何操作如果强行操作也会报错和上述错误一致,因为serializable隔离级别并发性能太低用的相对比较少
8.5.8 总结
MySQL是一个客户端/服务器对于同一个服务器来说允许若干个客户端进行连接每个客户端与服务器连接上后就可以称为一个会话Session。每个客户端都可以在自己的会话中向服务器发送请求语句服务器可能同时处理多个事务事务有隔离性的特性理论上在某个事务对某个数据进行访问时其他事务应该进行排队当该事务提交之后其他事务才可以继续访问这个数据。但这样对性能影响太大我们即想保持事务隔离性又想让服务器在处理访问同一数据的多个事务性能尽量高些那就看二者如何权衡取舍了。四种隔离的并发性能如图 九、SQL优化
SQL优化原则减小查询的数据量、提升SQL的索引命中率
MySQL的优化方式 从设计上优化从查询上优化从索引上优化从存储上优化
9.1 SQL优化技巧
9.1.1 查询时尽量不要使用 *
一般在写SQL为了方便以通常会采用*来代替所有字段毕竟用*号只要按键盘一下写字段则需要一个个字段名去写。写*的确能让程序员更省力但对机器就不太友好了因此在写查询语句时一律不要使用*代替所有字段原因如下 分析成本变高增加了SQL解析器的额外解析成本网络开销变大返回数据太多无意义网络数据包体积太大内存占用变高SQL查到的结果集更大、占用的内存也越大维护性变差增加维护业务量
9.1.2 连表查询时尽量不要关联太多表
关联太多的表就会导致执行效率变慢执行时间变长交互型的业务中关联的表数量应当控制在5张表之内后台型的业务由于不考虑用户体验感且业务复杂可以增加多张表的关联但按照《高性能MySQL》上的推荐最好也要控制在16~18张表之内阿里开发规范中要求控制在3张表以内。
9.1.3 多表查询时一定要以小驱大小表放前
意义先查小表再用小表的结果去大表中检索数据即写SQL时最好小表放前大表放后
9.1.4 不要使用like左模糊和全模糊查询
原因like关键字以 % 号开头会导致索引失效从而导致SQL触发全表查询因此需要使用模糊查询时千万要避免 %xxx、%xxx% 这两种情况出现实在需要使用这两类模糊查询时可以适当建立全文索引来代替数据量较大时可以使用ES、Solr…这类搜索引擎来代替
9.1.5 查询时尽量不要对字段做空值判断
select * from xxx where xxx is null;
select * from xxx where xxx not is null;由于判断 null 的时不会走索引因此字段做空值判断会导致索引失效一般在设计字段结构的时候请使用 not null 来定义字段同时如果想为空的字段可以设计一个0、这类空字符代替一方面要查询空值时可通过查询空字符的方式走索引检索
9.1.6 不要在条件查询 前对字段做任何运算
select * from student where id * 2 8;
select * from student where trim(name) 张三; # trim()函数用于清理串前后的空格因为MySQL优化器在生成执行计划时发现这些前面涉及到了运算因此就不会继续执行会将具体的运算工作留到执行时完成由于优化器没有继续执行因此不会为运算完成后的字段选择索引最终导致索引失效走全表查询。
9.1.7 !、!、not in、not like、or…要慎用
原因可能导致索引失效
select name from student where id1 or id2;# 可以替换成select name from student where id1
union all
select name from student where id2;上述SQL虽然变长了但查询效率会而更高因为后面的SQL可以走索引
9.1.8 避免频繁创建、销毁临时表
临时表是一种 数据缓存 对于一些常用的查询结果可以为其建立临时表这样后续要查询时可以直接基于临时表来获取数据MySQL默认会在内存中开辟一块临时表数据的存放空间所以走临时表查询数据是直接基于内存的速度会比走磁盘检索快上很多倍。但注意只有对于经常查询的数据才对其建立临时表不要盲目的去无限制创建否则频繁的创建、销毁会对MySQL造成不小的负担。
9.1.9 从业务设计层面减少大量数据返回的情况
若查询时要求一次性将所有数据全部返回而后在前端去做筛选展现但如果一次性返回的数据量过于巨大时就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现因此如果项目中存在这类业务一定要记住拆分掉它比如分批返回给客户端。
9.1.10 尽量避免深分页的情况出现
有SQL如下
select xx,xx,xx from yyy limit 100000,10; 上述这条SQL相当于查询第1W页数据在MySQL的实际执行过程中首先会查询出100010条数据然后丢弃掉前面的10W条数据将最后的10条数据返回这个过程无异极其浪费资源。 改进方法分为两种情况 情况1如果查询出的结果集存在递增且连续的字段可以基于有序字段来进一步做筛选后再获取分页数据如下 select xx,xx,xx from yyy where 有序字段 nnn limit 10; 情况2搜索分页该分页是无序的因为搜索到的数据可以位于表中的任意行所以搜索出的数据中就算存在有序字段也不会是连续的这种情况下就只能在业务上限制深分页的情况以百度为例其搜索关键字MYSQL的结果有很多甚至上亿条为了杜绝分页问题到最后连分页按钮都不给用户提供接了这时自然也就无法根据用户操作生成深分页的SQL在搜索需求中一般用户最多看前面30页如果还未找到他需要的内容基本上就会换个更精准的关键词重新搜索。
9.1.11 SQL务必要写完整不要使用缩写法
# 为字段取别名的简单写法
select name 姓名from student;
# 为字段取别名的完整写法
select name as姓名from student; # as需要写上# 内连接查询的简单写法
select * from 表1,表2... where 表1.字段表2.字段...;
# 内连接查询的完整写法
select * from 表1 别名1 inner join 表2 别名2 on 别名1.字段别名2.字段;注意所有隐式的这种写法在底层都需要做一次转换将其转换为完整的写法因此简写的SQL会比完整的SQL多一步转化过程如果需要极致程度的优化切记将SQL写成完整语法。
9.1.12 明确仅返回一条数据的语句可以使用limit 1
select * from student where name 李四;
select * from student where name 李四 limit 1;上述这两条SQL语句都是根据姓名查询一条数据但后者大多数情况下会比前者好因为加上 limit 1关键字后当程序匹配到一条数据时就会停止扫描可提升性能如果不加的情况下会将所有数据都扫描一次。 9.1.13 客户端的一些操作可以批量化完成
for (xxObject obj : xxObjs) {xxbiao.insert(obj);
}# xxbiao.insert(obj)对应的SQL如下insert into tb_xxx values(......);
# 批量新增某些数据、批量修改某些数据的状态…若使用上述接口的结构即insert语句嵌入在for循环中由于每次都需要往MySQL发送SQL语句则会带来额外的网络开销以及耗时应更改为如下
xxbiao.insertBatch(xxObjs);# xxbiao.insertBatch(xxObjs)对应的SQL如下insert into tb_xxx values(......),(......),(......),(......),.....;
# 上述修改会 组合成一条SQL发送给MySQL执行能够在很大程度上 节省网络资源的开销提升批量操作的执行效率 。 9.2 查看SQL执行频率
作用 MySQL 客户端连接成功后通过 show [session|global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。 格式
mysql show session status like Com_______; # 7个下划线查看当前会话统计结果
----------------------
| Variable_name | Value |
----------------------
| Com_binlog | 0 |
| Com_commit | 1 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 0 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 3 |
| Com_signal | 0 |
| Com_update | 2 |
| Com_xa_end | 0 |
----------------------
11 rows in set (0.00 sec)mysql show global status like Com_______; # 查看自数据库上次启动至今统计结果
----------------------
| Variable_name | Value |
----------------------
| Com_binlog | 0 |
| Com_commit | 5 |
| Com_delete | 1 |
| Com_import | 0 |
| Com_insert | 7 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 91 |
| Com_signal | 0 |
| Com_update | 16 |
| Com_xa_end | 0 |
----------------------
11 rows in set (0.00 sec) mysql show status like Innodb_rows_%; # 查看针对Innodb引擎的统计结果
-----------------------------
| Variable_name | Value |
-----------------------------
| Innodb_rows_deleted | 1 |
| Innodb_rows_inserted | 7 |
| Innodb_rows_read | 120 |
| Innodb_rows_updated | 15 |
----------------------------- 重要参数分析
9.3 定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的 SQL 语句
慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句。show processlist该命令查看当前MySQL在进行的线程包括线程的状态、是否锁表等可以实时地查看 SQL 的执行情况同时对一些锁表操作进行优化。
慢查询日志
# 查看慢日志配置信息
show variables like %slow_query_log%; # 开启慢日志查询
set global slow_query_log1; # 查看慢日志记录SQL的最低阈值时间,单位秒
show variables like long_query_time%; # 修改慢日志记录SQL的最低阈值时间需要重启mysql服务
set global long_query_time4; SQL- show processlist
mysql show processlist;
-------------------------------------------------------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info |
-------------------------------------------------------------------------------------------------------------------
| 5 | event_scheduler | localhost | NULL | Daemon | 6637 | Waiting on empty queue | NULL |
| 26 | root | localhost:52507 | mydb17_transcation | Query | 0 | init | show processlist |
-------------------------------------------------------------------------------------------------------------------# 分析
1 id列用户登录mysql时系统分配的会话id可以使用函数connection_id()查看
mysql select connection_id();
-----------------
| connection_id() |
-----------------
| 26 |
-----------------2 user列显示当前用户。如果不是root这个命令就只显示用户权限范围的sql语句3 host列显示这个语句是从哪个ip的哪个端口上发的可以用来跟踪出现问题语句的用户4 db列显示这个进程目前连接的是哪个数据库5 command列显示当前连接的执行的命令一般取值为休眠sleep查询query连接connect等6 time列显示这个状态持续的时间单位是秒7 state列显示使用当前连接的sql语句的状态很重要的列。state描述的是语句执行中的某一个状态。一个sql语句以查询为例可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成8 info列显示这个sql语句是判断问题语句的一个重要依据9.4 explain分析执行计划 作用 通过以上步骤查询到效率低的 SQL 语句后可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 格式
explain sql示例
mysql use mydb9_stusys;mysql select * from student;
-----------------------------------------------
| sno | sname | ssex | sage | monitor | birth |
-----------------------------------------------
| s001 | 张玲丽 | 女 | 20 | s010 | 2009-12-03 |
| s002 | 吴鹏 | 男 | 19 | s010 | 2009-10-11 |
| s003 | 李锐 | 男 | 19 | s003 | 2008-02-13 |
| s004 | 赵丁雯 | 女 | 21 | s003 | 2008-06-24 |
|……………………mysql explain select * from student where sno 003;分析 参数使用 key如果该值为空则表示未使用索引查询此时需要调整SQL或建立索引。type这个字段决定了查询的类型如果为index、all就需要进行优化。rows这个字段代表着查询时可能会扫描的数据行数较大时也需要进行优化。filtered这个字段代表着查询时表中不会扫描的数据行占比较小时需要进行优化。Extra这个字段代表着查询时的具体情况在某些情况下需要根据对应信息进行优化。
9.5 show profile 分析SQL 作用 show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。 查看是否开启
mysql select have_profiling; # 查看
------------------
| have_profiling |
------------------
| YES |
------------------
1 row in set, 1 warning (0.00 sec)mysql set profiling1; # 设置开启示例
mysql use mydb9_stusys;mysql set profiling1;mysql select * from student where year(now())-year(birth)20;mysql select * from student where year(now())-year(birth)16;mysql select count(*) from student;mysql show profiles;通过 show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间
mysql show profile for query 3; # 第三个sql
------------------------------------------
| Status | Duration |
------------------------------------------
| starting | 0.000059 |
| Executing hook on transaction | 0.000002 |
| starting | 0.000018 |
| checking permissions | 0.000003 |
| Opening tables | 0.000025 |
| init | 0.000002 |
| System lock | 0.000005 |
| optimizing | 0.000002 |
| statistics | 0.000009 |
| preparing | 0.000009 |
| executing | 0.000977 |
| end | 0.000006 |
| query end | 0.000002 |
| waiting for handler commit | 0.000007 |
| closing tables | 0.000005 |
| freeing items | 0.000037 |
| cleaning up | 0.000006 |
------------------------------------------
17 rows in set, 1 warning (0.00 sec)在获取到最消耗时间的线程状态后MySQL支持进一步选择 all、cpu、block io 、context switch、page faults 等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如选择查看CPU的耗费时间
mysql show profile cpu for query 3;
----------------------------------------------------------------
| Status | Duration | CPU_user | CPU_system |
----------------------------------------------------------------
| starting | 0.000059 | 0.000000 | 0.000000 |
| Executing hook on transaction | 0.000002 | 0.000000 | 0.000000 |
| starting | 0.000018 | 0.000000 | 0.000000 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 |
| Opening tables | 0.000025 | 0.000000 | 0.000000 |
| init | 0.000002 | 0.000000 | 0.000000 |
| System lock | 0.000005 | 0.000000 | 0.000000 |
| optimizing | 0.000002 | 0.000000 | 0.000000 |
| statistics | 0.000009 | 0.000000 | 0.000000 |
| preparing | 0.000009 | 0.000000 | 0.000000 |
| executing | 0.000977 | 0.000000 | 0.000000 |
| end | 0.000006 | 0.000000 | 0.000000 |
| query end | 0.000002 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.000007 | 0.000000 | 0.000000 |
| closing tables | 0.000005 | 0.000000 | 0.000000 |
| freeing items | 0.000037 | 0.000000 | 0.000000 |
| cleaning up | 0.000006 | 0.000000 | 0.000000 |
----------------------------------------------------------------9.6 trace分析优化器执行计划 作用 MySQL提供了对SQL的跟踪 trace通过trace文件能够进一步了解为什么优化器选择A计划而不是选择B计划 方法 打开trace 设置格式为 JSON并设置trace最大能够使用的内存大小避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_traceenabledon,end_markers_in_jsonon; set optimizer_trace_max_mem_size1000000;示例
mysql select * from student where year(now())-year(birth)16;# 通过检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的
mysql select * from information_schema.optimizer_trace\G;9.7 使用索引优化 作用 索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。 索引建立原则 一般针对数据分散的关键字进行建立索引比如ID、QQ像性别、状态值等建立索引没有意义对大数据量表建立聚集索引避免更新操作带来的碎片尽量使用短索引一般对int、char/varchar、date/time 等类型的字段建立索引需要的时候建立联合索引但是要注意查询SQL语句的编写谨慎建立 unique 类型的索引唯一索引大文本字段不建立为索引如果要对大文本字段进行检索可以考虑全文索引频繁更新的列不适合建立索引order by 字句中的字段where 子句中字段最常用的sql语句中字段应建立索引。对于只是做查询用的数据库索引 越多越好 但对于在线实时系统建议 控制在5个以内 。
9.8 架构优化
业务拆分搜索功能like 前后都有%一般不用MySQL数据库业务拆分某些应用使用nosql持久化存储例如memcahcedb、redis、ttserver 比如粉丝关注、好友关系等数据库前端必须要加cache例如memcached用户登录商品查询动态数据静态化。整个文件静态化页面片段静态化数据库集群与读写分离单表超过2000万拆库拆表人工或自动拆分登录、商品、订单等
