MYSQL

HootinVip

已于 2024-04-19 00:02:25 修改

阅读量1.5k

点赞数 51

CC 4.0 BY-SA版权

文章标签： mysql 数据库

于 2024-04-18 23:46:42 首次发布

本文链接：https://blog.csdn.net/HootinVip/article/details/137941795

MySql

SQL分类

DDL 数据定义语言，用来定义数据库对象（数据库，表，字段）
DML数据操作语言，用来对数据库中的数据进行增删改
DQL数据查询语言，用来查询数据库中表的记录
DCL数据控制语言，用来创建数据库用户、控制数据库的访问权限

事务

介绍：

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

四大特性：

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
一致性：事务完成时，必须使所有的数据都保持一致状态
持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题：

脏读：一个事务读取到另一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读（第二次查的时候，有别的事务提交修改了数据）
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影

事务隔离级别：

在这里插入图片描述

Read uncommitted（读未提交）可能会出现脏读、不可重复读、幻读
Read commited（读已提交）可能会出现不可重复读和幻读
Repeatable Read（读可重复读）可能会出现幻读
Serializable（可序列化）不会出现问题

查看事务隔离级别,mysql默认级别是Repeatable Read（读可重复读）

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

设置事务隔离级别，分为会话级seesion和全局级别global

set session taransaction isolation level read commited

set session transaction isolation level repeatable read;

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

事务隔离级别越高，数据越安全，但是性能越低。

Mysql体系结构

在这里插入图片描述

连接层

最上层是一些客户端和连接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层

第二层架构主要完成大多数的核心服务功能，如sql接口，并完成缓存的查询，sql的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
引擎层

存储引擎真正的负责了mysql中数据的存储和提取，服务器通过api和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选择合适的存储引擎。
存储层

主要是将数据存储在文件系统之上，并完成与存储引擎的交互

存储引擎

简介

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

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

在这里插入图片描述

SHOW ENGINES

查看某个表的存储引擎,默认的存储引擎是InnoDB

show create table table_name

创建myisam引擎的表

create table table_name(

id int null,

name varchar(10) null

)engine = MyISAM

InnoDB存储引擎

介绍：

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在mysql5.5之后，InnoDB是默认的mysql存储引擎。

特点：DML操作遵循ACID模型，支持事务；

行级锁，提高并发访问性能；

支持外键foreign key约束，保证数据的完整性和正确性；

文件：xxx.ibd，innoDB引擎的每张表都会对应一个表空间文件，存储该表的表结构（frm,sdi）、数据和索引。

在这里插入图片描述

Tablespace表空间：就是ibd文件，一个mysql实例可以对应多个表空间，用于存储记录、索引等数据
Segment段：innoDB是索引组织表，

数据段（Leaf node segment），即B+Tree的叶子结点，存放数据

索引段（non-leaf node segment）,即B+Tree的非叶子结点，存放索引

回滚段（Rollback segment）
Extent区：表空间的单元结构，每个区的大小为1M。默认情况下，innoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
Page页：是innoDB存储磁盘管理的最小单元，每个页的大小默认是16KB，为了保证页的连续性，innoDB存储引擎每次从磁盘申请4-5个区
Row行：innoDB存储引擎数据时按行进行存放的，trx_id是最后一次操作事务的id，roll_pointer相当于一个指针，通过这个指针可以找到增删改之前的

数据。

MyISAM存储引擎

早期mysql默认引擎

特点：不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

文件：

xxx.sdi:存储表结构信息

xxx.MYD:存储数据

xxx.MYI:存储索引

索引

介绍

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

select * from user where age = 45

这条语句执行时，需要全表扫描，效率极低。

如果有索引的情况，45先去和36对比，再和48对比，找到45,扫描次数减少

在这里插入图片描述

索引的优点：提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引的缺点：索引列也要占用空间，索引提高了查询效率，同时也降低了更新表的速度，对表insert,update.delete时效率降低。

索引结构

B+Tree索引：最长久的索引类型

Hash索引：底层数据结构是用哈希表实现的，只有精度匹配索引列的查询才有效，不支持范围查询

R-tree（空间索引) 空间索引是myisam引擎的一个特殊引擎类型，主要用于地理空间数据类型，通常很少使用

Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式。

在这里插入图片描述

B-Tree

多路平衡查找树

不断向上分裂

B+Tree

在这里插入图片描述

Hash索引结构

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

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

在这里插入图片描述

hash索引特点

只能用于对等比较（=，in)，不支持范围查询
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常高于B+Tree索引

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

为什么InnoDB存储引擎选择使用B+Tree索引结构？

相当于二叉树，层级更少，搜索效率高
相对于B-Tree，无论是叶子结点还是非叶子结点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
相对于hash索引，B+Tree支持范围匹配及排序操作

索引分类

在这里插入图片描述

聚集索引选取规则

聚集索引叶子结点，下面挂的是对应行的数据，二级索引叶子结点下面挂的是对应的id

在这里插入图片描述

聚集索引只能有一个，其他的都是二级索引；如果存在主键，逐渐索引就是聚集索引
如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引

sql语句执行过程解析

select * from user where name = ‘Arm’

这条查询语句的where条件是name，所以不是走的（主键）聚集索引，而是走的二级索引，先找到Lee，然后找到Geek，最后找到Arm并拿到二级索引叶子结点挂的id=10。

查找的内容为*（所有字段），再通过拿到的id，再去聚集索引中找到这一行的数据，先找到15，10比15小走左侧指针，最终定位找到10，拿到聚集索引叶子结点下挂的行数据。

索引语法

创建索引

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

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

查看sys_user表的索引

show index from sys_user\G;
#当前表只有主键索引

在这里插入图片描述

名称字段创建索引

create index idx_user_name on sys_user(user_name);
show index from sys_user\G;

在这里插入图片描述

创建完成，查询索引，成功创建了索引，索引结构为BTree

创建唯一索引

create unique index idx_phone_number on sys_user(phonenumber);

在这里插入图片描述

创建联合索引

create index idx_user_email_sex_sta on sys_user(email,sex,status);

在这里插入图片描述

Sql性能分析

执行频次

分析数据库业务量

show global status like 'Com_______';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 12    |
| Com_delete    | 12    |
| Com_insert    | 18    |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 299   |
| Com_signal    | 0     |
| Com_update    | 7     |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.01 sec)

分析结果为，该数据库以查询为主

#查询慢查询日志
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.01 sec)

慢查询日志的开关默认关闭

在root根目录下进入mysql的设置文件

vi /etc/my.cnf;

在末尾加入以下内容

#慢查询日志
#打开慢查询日志开关
slow_query_log=1
#慢查询时长为2秒，超过2s的查询定义为慢查询
long_query_time=2

重启mysql服务

 systemctl restart mysqld;

mysql>  show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

慢查询日志存放在/var/lib/mysql下,后缀为slow.log

实时输出慢查询日志的内容

tail -f iZ2zednz491t3ftltipuy7Z-slow.log

当select count(*) from tb_sku执行后，慢查询日志有新的输出内容

SET timestamp=1712912202;
select count(*) from tb_sku;

# Time: 2024-04-12T09:01:19.177150Z

# User@Host: root[root] @ localhost []  Id:     9

# Query_time: 5.315858  Lock_time: 0.000096 Rows_sent: 1  Rows_examined: 0

SET timestamp=1712912473;
select count(*) from tb_sku;

profiles指令

show profiles;

执行结果

+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 4.33905200 | select count(*) from tb_sku                 |
|        2 | 0.22023150 | select * from tb_user                       |
|        3 | 0.00047625 | select * from tb_user where id = 1          |
|        4 | 0.01865575 | select * from tb_user where name = '白起'   |
+----------+------------+---------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

sql性能分析

explain执行计划

mysql> explain select * from student s where s.id in (select studentid from student_course sc where sc.courseid  = (select id from course c where c.name = 'MySQL'));
+----+-------------+-------+------------+------+--------------------------+-------------+---------+-------+------+----------+----------------------------+
| id | select_type | table | partitions | type | possible_keys            | key         | key_len | ref   | rows | filtered | Extra                      |
+----+-------------+-------+------------+------+--------------------------+-------------+---------+-------+------+----------+----------------------------+
|  1 | PRIMARY     | s     | NULL       | ALL  | PRIMARY                  | NULL        | NULL    | NULL  |    3 |   100.00 | NULL                       |
|  1 | PRIMARY     | sc    | NULL       | ref  | fk_courseid,fk_studentid | fk_courseid | 4       | const |    2 |   100.00 | Using where; FirstMatch(s) |
|  3 | SUBQUERY    | c     | NULL       | ALL  | NULL                     | NULL        | NULL    | NULL  |    4 |    25.00 | Using where                |
+----+-------------+-------+------------+------+--------------------------+-------------+---------+-------+------+----------+----------------------------+
3 rows in set, 1 warning (0.00 sec)

注意：

id: id值越大越先执行，id值相同，从上到下执行，所以先执行的表course的

select_type：表示select的类型，常见的取值有simple（简单表，即不使用表连接或子查询）、PRIMARY（主查询，即最外层的查询）、UNION（UNION中的第二个或后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等

type: 表示连接类型，性能NUUL>system>const>eq_ref>ref>range>index>all

possible_keys: 显示可能应用在这张表上的索引，一个或多个

key:实际使用的索引，如果为NULL，则没有使用索引。

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows: MYSQL认为必须要执行查询的行数，在innoDB引擎的表中，是一个预估值，可能并不总是准确的。

filtered：表示返回结果的行数占需读取行数的百分比，值越大越好

未创建索引前的执行效率

> mysql> select * from tb_sku where sn = '100000003145001';
> +----+-----------------+-------------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+---------+----------+-------------+--------+
> | id | sn              | name        | price | num  | alert_num | image                                                                                                                 | images                                                                                                                | weight | create_time         | update_time         | category_name | brand_name | spec    | sale_num | comment_num | status |
> +----+-----------------+-------------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+---------+----------+-------------+--------+
> |  1 | 100000003145001 | 华为Meta1   | 87901 | 9961 |       100 | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp |     10 | 2019-05-01 00:00:00 | 2019-05-01 00:00:00 | 真皮包        | viney      | 白色1   |       39 |           0 | 1      |
> +----+-----------------+-------------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+---------+----------+-------------+--------+
> 1 row in set (11.54 sec)

耗费时间11.54s

创建索引

create index idx_sku_sn on tb_sku(sn);

再次执行查询语句，耗时降至0.02s

mysql> select * from tb_sku where sn = '100000003145001';
+----+-----------------+-------------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+---------+----------+-------------+--------+
| id | sn              | name        | price | num  | alert_num | image                                                                                                                 | images                                                                                                                | weight | create_time         | update_time         | category_name | brand_name | spec    | sale_num | comment_num | status |
+----+-----------------+-------------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+---------+----------+-------------+--------+
|  1 | 100000003145001 | 华为Meta1   | 87901 | 9961 |       100 | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp | https://m.360buyimg.com/mobilecms/s720x720_jfs/t5590/64/5811657380/234462/5398e856/5965e173N34179777.jpg!q70.jpg.webp |     10 | 2019-05-01 00:00:00 | 2019-05-01 00:00:00 | 真皮包        | viney      | 白色1   |       39 |           0 | 1      |
+----+-----------------+-------------+-------+------+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+---------------+------------+---------+----------+-------------+--------+
1 row in set (0.02 sec)

联合索引的最左前缀法则

创建一个包含多个字段的联合索引，如果查询时使用该联合索引，where条件必须包含联合索引最左边的列。

例如创建一个索引,最左边的列是profession

create index idx_user_pro_age_sta on tb_user(profession,age,status);

 #索引可以生效，因为包含profession
 select * from tb_user where age=31 and status='0' and profession = '软件工程' and gender = 2;

#索引失效，因为不包含profession
select * from tb_user where age=31 and status='0';

使用范围查询时，使>或<会使索引部分失效，尽量使用>=或<=进行范围查询

mysql> explain select * from tb_user where age>30 and status='0' and profession = '软件工程' and gender = 2;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 49      | NULL |    2 |     4.17 | Using index condition; Using where |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_user where age>=30 and status='0' and profession = '软件工程' and gender = 2;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | range | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | NULL |    2 |     4.17 | Using index condition; Using where |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

索引失效情况

不要在索引列上进行运算操作，否则索引会失效。select * from tb_user where substring(phone,10,2) = ‘15’;
字符串类型字段使用时，不加引号，索引会失效
or连接的条件，如果有其中一个没有索引，那么索引不会生效

模糊查询时，尾部模糊匹配，索引会生效，头部模糊匹配，索引失效

#可以生效的语句，尾部模糊匹配
select * from tb_user where profession like '软件%';
#索引失效的查询语句，头部模糊匹配
select * from tb_user where profession like '%工程';

数据分布影响。如果mysql评估使用索引比全表更慢，则不使用索引

#表中数据绝大部分都满足phone >= '17799990005'，则索引失效
select * from tb_user where phone >= '17799990005';
#表中数据只有少部分满足phone >= '17799990015'，则索引生效
select * from tb_user where phone >= '17799990015';

**为什么尽量不用select ***

当前tb_user表所有索引如下

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_pro              |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.00 sec)

比较下列查询语句的效率

#1
select id,profession,age,status from tb_user where age=31 and status = '0' and profession='软件工程';
#2
select id,profession,age,status,name from tb_user where age=31 and status = '0' and profession='软件工程';

> mysql> explain select id,profession,age,status from tb_user where age=31 and status = '0' and profession='软件工程';
  > +----+-------------+---------+------------+------+------------------------------+----------------------+---------+-------------------+------+----------+--------------------------+
  > | id | select_type | table   | partitions | type | possible_keys                | key                  | key_len | ref               | rows | filtered | Extra                    |
  > +----+-------------+---------+------------+------+------------------------------+----------------------+---------+-------------------+------+----------+--------------------------+
  > |  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta,idx_pro | idx_user_pro_age_sta | 54      | const,const,const |    1 |   100.00 | Using where; Using index |
  > +----+-------------+---------+------------+------+------------------------------+----------------------+---------+-------------------+------+----------+--------------------------+

  mysql> explain select id,profession,age,status,name from tb_user where age=31 and status = '0' and profession='软件工程';
  +----+-------------+---------+------------+------+------------------------------+----------------------+---------+-------------------+------+----------+-----------------------+
  | id | select_type | table   | partitions | type | possible_keys                | key                  | key_len | ref               | rows | filtered | Extra                 |
  +----+-------------+---------+------------+------+------------------------------+----------------------+---------+-------------------+------+----------+-----------------------+
  |  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta,idx_pro | idx_user_pro_age_sta | 54      | const,const,const |    1 |   100.00 | Using index condition |
  +----+-------------+---------+------------+------+------------------------------+----------------------+---------+-------------------+------+----------+-----------------------+

第一种查询，查询的所有字段，都在同一个联合索引里存在，不需要回表查询，效率高。

第二中查询，name字段不在联合索引中，需要回表查询，通过二级索引找到id值，再拿着id值去聚集索引里获取行数据，效率低。

前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

#创建前缀索引语法

create index idx_email_5 on tb_user(email(5));

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件（where）、排序（order by)、分组（group by)操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询，提高查询效率
要控制索引的数量，索引不是多多益善，因为索引越多，维护索引结构的代价也就越大，会影响增删改的效率
如果索引列不能存储NULL值，请在创建表是使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

插入数据优化

大批量插入数据时，可以使用load指令插入

#客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local-infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

主键优化

数据组织方式：在innoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

主键乱序插入的时候，可能会造成页分裂

页分裂如何理解，我的理解是当数据需要插入时，首先插入第一页，如主键为1,4,7,23，第一页已满，然后继续插入主键为88,99的数据到第二页，此时，如果再插入主键为50的数据，则需要再开辟一个第三页，将以第一页的数据，按比例划分为；两部分，后半部分转移到第三页，主键50的数据也放在第三页。最后要将链表指针重新设置，第一页后面是第三页，第三页之后是第二页。

页合并如何理解，当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，innoDB会开始寻找最靠近的页（前或后），看看是否可以将两个页合并以优化空间使用

主键的设计原则

满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择自增主键
尽量不要使用uuid做主键活着是其他自然主键，如身份证号，因为是无序的，而且长度较长
业务操作时，避免对主键的修改

order by 优化

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，索引不是通过索引直接返回排序结果的排序都叫fileSort排序

Using index:通过有序索引扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

注意：

order by依旧遵循最左匹配原则，如果排序条件为多个，第一个排序条件一定要为索引第一列
另外倒序排序和正序排序也会受索引影响，一般默认创建的索引是按正序创建的，如果倒序排序，mysql会根据索引倒序扫描，Backward index scan
如果排序方式，跟索引不完全一样，则索引会失效 Using index; Using filesort
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256K）

#创建age的单列索引

create index idx_age on tb_user(age);
#测试只使用age排序
mysql> explain select age from tb_user order by age;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_age | 2       | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#使用age作为orderby条件时，Using index ，效率高

#--------------------
#测试使用age和phone排序
mysql> explain select age from tb_user order by age,phone;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
#使用age和phone排序时，Using filesort  ，效率低

#创建一个age和phone的联合索引
 create index idx_age_phone on tb_user(age,phone);
#使用age和phone作为排序条件
mysql> explain select age from tb_user order by age,phone;
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_age_phone | 48      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#使用①age和②phone作为排序时，Using index，效率高

#------------------------------------------
#order by依旧遵循最左匹配原则，使用phone作为第一个排序条件时，结果为Using index; Using filesort，效率低

explain select age from tb_user order by phone,age;

#另外倒序排序和正序排序也会受索引影响，一般默认创建的索引是按正序创建的，如果倒序排序，mysql会根据索引倒序扫描，Backward index scan

mysql> explain select age from tb_user order by age desc,phone desc;
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_age_phone | 48      | NULL |   24 |   100.00 | Backward index scan; Using index |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

#如果排序方式，跟索引不完全一样，则索引会失效 Using index; Using filesort

mysql>  explain select age from tb_user order by age asc,phone desc;
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_age_phone | 48      | NULL |   24 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

group by 优化

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

第一次分析执行group by语句，发现Using temporary使用的是临时表，效率很低

#第一次分析执行group by语句，Using temporary
mysql> explain select profession,count(*) from tb_user group by profession;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

创建一个profession的索引

create index idx_pro on tb_user(profession);

重新分析执行group by语句，发现Using index，使用的是索引，效率高

#重新分析执行group by语句，发现Using index
mysql> explain select profession,count(*) from tb_user group by profession;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro       | idx_pro | 47      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

最左前缀法则

创建一个联合索引

create index idx_pro_age_sta on tb_user(profession,age,status);

#以status为分组条件，因为status不是联合索引的第一列，使用的是临时表
mysql> explain select status,count(*) from tb_user group by status;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index; Using temporary |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
#以profession和age先后作为分组条件，porfession在前，Using index
mysql> explain select profession,status,count(*) from tb_user group by profession,age;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#以profession和age先后作为分组条件，age在前，Using temporary
mysql> explain select profession,status,count(*) from tb_user group by age,profession;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index; Using temporary |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

limit优化

没听懂

count优化

MyISAM引擎把一个表的总行数存在了磁盘上，因此count(*)的时候会直接返回这个数，效率很高

InnoDB引擎在执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

count几种用法

count（主键）:

innoDB会遍历整张表，把每一行的主键id都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加

count(字段) :

没有not null约束:InnoD8引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为nul，计数累加

有not null 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count(1):

InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

count(*):

InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段)<count(主键id)<count(4)≈count(*****)，所以尽量使用 count(*)。

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

存储过程

介绍

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想就是数据库sql语言层面的代码封装与重用。

基本语法

#shell创建存储过程
#不能再单纯以分号结束，定义为以$$号结束
delimiter $$
#修改创建语句
CREATE PROCEDURE p1() BEGIN SELECT count(*) FROM student;END$$
#创建完以后，改回来结束符号
delimiter ;
#调用存储过程
mysql> call p1();
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

存储过程变量

系统变量

介绍：是mysql服务器提供，不是用户定义的，属于服务器层面。分为全局变量和会话变量。

查看系统变量

#查看系统所有变量,默认session级别,也可以手动选择global
show  variables;
#查看以auto开头的系统变量
show session variables like 'auto%';
#设置系统变量
shwo global variables like 'auto%';
#查询名称为@@global.autocommit的系统变量
select @@global.autocommit;

设置系统变量

#关闭autocommit，关闭了以后，插入删除修改语句需要手动commit一下才能生效
set session autocommit = 0;
#查询状态为0
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)
#关闭了以后，插入删除修改语句需要手动commit一下才能生效
insert into course(id,name) values (5,'oracle');
commit;

用户定义变量

介绍：是用户根据自己定义的变量，用户变量不需要提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

赋值

set @myname = 'itcast';
set @myage:=0,@mygender='man';
select @mycolor:='red';
select count(*) into @mycount from tb_user;

查看

mysql> select @myname,@myage,@mygender;
+---------+--------+-----------+
| @myname | @myage | @mygender |
+---------+--------+-----------+
| itcast  |      0 | man       |
+---------+--------+-----------+
1 row in set (0.00 sec)

局部变量

介绍：局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN…END块。

声明

#创建存储过程并赋值局部变量
create procedure p2()
begin
	declare stu_count int default 0;
	-- set stu_count := 100;
	select count(*) into stu_count from student;
end;
call p2();

IF条件判断

CREATE PROCEDURE p3()
BEGIN
	DECLARE score int DEFAULT 58;
	DECLARE result varchar(10);
	IF score >=85 THEN
		SET result := '优秀';
	ELSEIF score >=60 THEN
		SET result := '及格';
	ELSE
		SET result := '不及格';
	END IF;
	SELECT result;
END;

调用

mysql> call p3();
+-----------+
| result    |
+-----------+
| 不及格    |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

参数

IN 输入参数
OUT 输出参数，返回值
INOUT 既可作为输入参数，又可作为输出参数

案例1：根据传入参数score,判定当前分数对应的分数等级，并返回

①score>85，为优秀

②score>=60且<85为及格

③score<60，为不及格

#创建存储过程
CREATE PROCEDURE p4(IN score int,out result varchar(10))
BEGIN
	IF score >=85 THEN
		SET result := '优秀';
	ELSEIF score >=60 THEN
		SET result := '及格';
	ELSE
		SET result := '不及格';
	END IF;	
END;
#调用
call p4(50,@result);
#查询结果
mysql> SELECT @result;
+------------------+
| @result          |
+------------------+
| NULL             |
+------------------+
1 row in set (0.00 sec)

案例2:将传入的200分制的分数，进行换算成百分制返回

#创建存储过程
CREATE PROCEDURE p5(INOUT score double)
BEGIN
	SET score:= score*0.5;
END;

#调用并查询
mysql> SET @score = 156;
Query OK, 0 rows affected (0.00 sec)

mysql> call p5(@score);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @score;
+--------+
| @score |
+--------+
|     78 |
+--------+
1 row in set (0.00 sec)

CASE

案例：根据传入的月份，判断季度

#创建存储过程
CREATE PROCEDURE p6(IN month int)
BEGIN
	DECLARE result VARCHAR(10);
	CASE  
	WHEN month>=1 AND month <=3 THEN
		set result := '第一季度';
	WHEN month>=4 AND month <=6 THEN
		set result := '第二季度';
	WHEN month>=7 AND month <=9 THEN
		set result := '第三季度';
	WHEN month>=10 AND month <=12 THEN
		set result := '第四季度';
	ELSE
		set result := '非法参数';
END CASE;
SELECT CONCAT('您输入的月份为：'+month,'所属季度为：',result);
END;
#调用
mysql> CALL p6(5);
+----------------------------------------------------------------------+
| CONCAT('您输入的月份为：'+month,'所属季度为：',result)               |
+----------------------------------------------------------------------+
| 5所属季度为：第二季度                                                |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

WHILE循环

案例：从1累加到n

#创建存储过程
CREATE PROCEDURE p7(IN n INT)
BEGIN
	DECLARE total int DEFAULT 0;
	WHILE n>0 DO
	SET total := total +n;
	SET n := n-1;
END WHILE;
SELECT total;
END;
#调用
mysql> call p7(11);
+-------+
| total |
+-------+
|    66 |
+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

repeat循环

先执行满足条件则退出循环

#创建存储过程
CREATE PROCEDURE p8(IN n INT)
BEGIN
	DECLARE total int DEFAULT 0;
	REPEAT
	SET total:= total + n;
	SET n := n-1;
UNTIL n<=0 
END REPEAT;
SELECT total;
END;
#调用
mysql> call p8(20);
+-------+
| total |
+-------+
|   210 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

LOOP循环

loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环，loop可以配合一下两个语句使用

leave：配合循环使用，退出循环。
iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

案例1：计算从1累加到n的值

#创建存储过程
CREATE PROCEDURE p9(IN n INT)
BEGIN
	DECLARE total int DEFAULT 0;
	sum: LOOP
		IF n<=0 THEN
			LEAVE sum;
			END IF;
			
			SET total := total + n;
			SET n := n-1;
	END LOOP sum;
SELECT total;
END;

#调用
mysql> call p9(10);
+-------+
| total |
+-------+
|    55 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

案例2：聚酸从1到n的偶数的累加值

#创建存储过程
CREATE PROCEDURE p10(IN n INT)
BEGIN
	DECLARE total int DEFAULT 0;
	sum: LOOP
		IF n<=0 THEN
			LEAVE sum;
		END IF;
		IF n%2 =1 THEN
			SET n := n-1;
			ITERATE sum;
		END IF;
		
		SET total := total + n;
		SET n := n-1;	
	END LOOP sum;
SELECT total;
END;
#调用
mysql> call p10(10);
+-------+
| total |
+-------+
|    30 |
+-------+
1 row in set (0.00 sec)

游标

介绍：用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH、CLOSE

语法

#声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
#打开游标
OPEN 游标名称;
#获取游标记录
FETCH 游标名称 INTO 变量[,变量];
#关闭游标
CLOSE 游标名称;

案例：根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name)和专业（profession)，并将用户的姓名和专业插入到所创建的一张新表中（id,name,profession)

CREATE PROCEDURE p11(IN uage INT)
BEGIN
	DECLARE uname VARCHAR(100);
	DECLARE upro VARCHAR(100);
	#声明游标，存储符合条件的结果集
	DECLARE u_cursor CURSOR FOR SELECT name,profession FROM tb_user WHERE age <= uage;
	#声明条件处理程序，用于while退出循环,本质是利用异常关闭游标
	#DECLARE exit HANDLER FOR SQLSTATE '02000' CLOSE u_cursor;
	DECLARE exit HANDLER FOR not found CLOSE u_cursor;
	
	#准备：创建表结构
	CREATE TABLE IF NOT EXISTS tb_user_pro(
	id int PRIMARY key auto_increment,
	name VARCHAR(100),
	profession VARCHAR(100)
	);
	#开启游标
	OPEN u_cursor;
	
	WHILE TRUE DO
		##获取游标的数据
		FETCH u_cursor into uname,upro;
		#获取到的数据插入新表
		INSERT INTO tb_user_pro values(null,uname,upro);
	
	END WHILE;
	CLOSE u_cursor;

END;
call p11(40);
#生成的tb_user_pro表
1	吕布	软件工程
2	曹操	通讯工程
3	赵云	英语
4	花木兰	软件工程
5	大乔	舞蹈
6	露娜	应用数学
7	程咬金	化工
8	白起	机械工程及其自动化
9	韩信	无机非金属材料工程
10	荆轲	会计
11	貂蝉	软件工程
12	妲己	软件工程
13	芈月	工业经济
14	嬴政	化工
15	狄仁杰	国际贸易
16	廉颇	土木工程
17	后羿	城市园林
18	姜子牙	工程造价

存储函数

介绍：存储函数是有返回值的存储过程

案例：计算1到n的累加值

#创建存储函数
CREATE FUNCTION fun1(n int)
RETURNS int DETERMINISTIC
BEGIN 
	DECLARE total int DEFAULT 0;
	WHILE n>0 DO
		SET total:= total+n;
		SET n:=n-1;
	END WHILE;
	return total;
END;
#调用并查询结果
mysql> select fun1(20);
+----------+
| fun1(20) |
+----------+
|      210 |
+----------+
1 row in set (0.00 sec)

触发器

触发器是与表有关的数据库对象，指在insert、update、detele之前或之后，触发并执行触发器中定义的sql语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

只支持行级触发，不支持语句级触发，如一个sql的update语句，影响了五行，则触发5次，而不是1次

触发器类型

insert型触发器：new表示将要或者已经新增的数据

update型触发器：old表示修改之前的数据，new表示将要或已经修改后的数据

delete型触发器：old表示将要或者已经删除的数据

insert触发器

#创建触发器，使在tb_user表中插入数据之后，触发，在user_logs表中插入触发器中定义的内容
CREATE TRIGGER tb_user_insert_trigger
		AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params)VALUES
(NULL,'insert',NOW(),new.id,CONCAT('插入的数据内容为：id=',new.id,',name=',new.name,', '));
END;

#查看触发器
mysql> SHOW TRIGGERS;
+------------------------+--------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+
| Trigger                | Event  | Table   | Statement                                                                                                                                                                                          | Timing | Created                | sql_mode                                                                                                              | Definer | character_set_client | collation_connection | Database Collation |
+------------------------+--------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+
| tb_user_insert_trigger | INSERT | tb_user | BEGIN
INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params)VALUES
(NULL,'insert',NOW(),new.id,CONCAT('插入的数据内容为：id=',new.id,',name=',new.name,', '));
END          | AFTER  | 2024-04-16 01:13:51.40 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@%  | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------------------------+--------+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
#测试触发器
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (25,'二皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

#测试结果，成功触发插入数据
mysql> SELECT * FROM user_logs;
+----+-----------+---------------------+------------+---------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                    |
+----+-----------+---------------------+------------+---------------------------------------------------+
|  1 | insert    | 2024-04-16 01:16:14 |         25 | 插入的数据内容为：id=25,name=二皇子,              |
+----+-----------+---------------------+------------+---------------------------------------------------+
1 row in set (0.00 sec)

update触发器

#创建触发器，数据修改之后触发
CREATE TRIGGER tb_user_update_trigger
		AFTER UPDATE ON tb_user FOR EACH ROW
BEGIN
INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params)VALUES
(NULL,'insert',NOW(),new.id,CONCAT('更新之前数据内容为：id=',old.id,',name=',old.name,',|更新之后的数据内容：id=',old.id,',name=',old.name, ','));
END;
#测试
UPDATE tb_user SET name = '二皇子' WHERE name = '四皇子';
#结果
mysql> SELECT * FROM user_logs where operation='update';
+----+-----------+---------------------+------------+---------------------------------------------------------------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                                                                          |
+----+-----------+---------------------+------------+---------------------------------------------------------------------------------------------------------+
|  6 | update    | 2024-04-16 01:50:48 |         25 | 更新之前数据内容为：id=25,name=四皇子,|更新之后的数据内容：id=25,name=二皇子,                           |
+----+-----------+---------------------+------------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

delete触发器

#创建触发器
CREATE TRIGGER tb_user_delete_trigger
		AFTER DELETE ON tb_user FOR EACH ROW
BEGIN
INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params)VALUES
(NULL,'delete',NOW(),old.id,CONCAT('插入的数据内容为：id=',old.id,',name=',old.name,', '));
END;
#
delete from tb_user where name = '二皇子';
#结果
mysql> SELECT * FROM user_logs where operation='delete';
+----+-----------+---------------------+------------+---------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                    |
+----+-----------+---------------------+------------+---------------------------------------------------+
|  8 | delete    | 2024-04-16 01:57:52 |         25 | 插入的数据内容为：id=25,name=二皇子,              |
+----+-----------+---------------------+------------+---------------------------------------------------+
1 row in set (0.00 sec)

锁

介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

分类

全局锁：锁定数据库中的所有表
表级锁：每次操作锁住整张表
行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例枷锁，加锁后整个实例就处于只读状态，后序的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

#加全局锁，加了以后，别的人只能读不能写

#第一个窗口加锁
flush tables with read lock;
#第二个窗口备份
mysqldump -uroot -p123456 itcast> D:/itcast.sql;

#第一个窗口解锁
unlock tables;

特点：数据库中加全局锁，是一个比较重的操作，存在以下问题

如果再主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

如果在从库上备份，那么备份期间从库不能执行主库同步过来的二进制日志（binlog)，会导致主从延迟

在innoDB引擎中，我们可以在备份时加上参数 --single-transaction参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -root -p 123456 itcast > itcast.sql

表级锁

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MYISAM、innoDB、BDB等存储引擎中。

语法

加锁 lock tables table_name read/write;

释放锁：unlock tables/

表级锁分类：

表锁

表共享读锁,加读锁，别的客户端也可以读，所有客户端都不能写
表独占写锁，当前客户端能读也能写，别的客户端不能读也不能写

验证1

#客户端1

#查询tb_user表
mysql> select * from tb_user;
+----+-----------+-------------+-----------------------+-----------------------------+------+--------+--------+---------------------+
| id | name      | phone       | email                 | profession                  | age  | gender | status | createtime          |
+----+-----------+-------------+-----------------------+-----------------------------+------+--------+--------+---------------------+
|  1 | 吕布      | 17799990000 | lvbu666@163.com       | 软件工程                    |   23 | 1      | 6      | 2001-02-02 00:00:00 |
|  2 | 曹操      | 17799990001 | caocao666@qq.com      | 通讯工程                    |   33 | 1      | 0      | 2001-03-05 00:00:00 |
|  3 | 赵云      | 17799990002 | 17799990@139.com      | 英语                        |   34 | 1      | 2      | 2002-03-02 00:00:00 |

+----+-----------+-------------+-----------------------+-----------------------------+------+--------+--------+---------------------+
24 rows in set (0.00 sec)
#给tb_user表加上读锁
mysql> lock table tb_user read;
Query OK, 0 rows affected (0.00 sec)
#验证客户端1是否还能修改数据
mysql> update tb_user set name = '三姓家奴' where id =1;
#客户端1不能修改数据
ERROR 1099 (HY000): Table 'tb_user' was locked with a READ lock and can't be updated

#验证客户端2能否修改数据
mysql> update tb_user set name = '三姓家奴' where id =1;
#没有报错，但处于阻塞状态
#如果客户端1，此时解锁，客户端2一直被阻塞的语句就可以成功执行

验证2

#客户端1
#加写锁
mysql> lock tables tb_user write;
Query OK, 0 rows affected (0.00 sec)
#验证客户端1可以查询
mysql> select id,name,phone from tb_user limit 3;
+----+--------------+-------------+
| id | name         | phone       |
+----+--------------+-------------+
|  1 | 三姓家奴     | 17799990000 |
|  2 | 曹操         | 17799990001 |
|  3 | 赵云         | 17799990002 |
+----+--------------+-------------+
3 rows in set (0.00 sec)
#验证客户端1可以写数据
mysql> update tb_user set name = '吕奉先' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#客户端2
#验证写数据，结果不能写入，语句一直阻塞，必须要等客户端1解锁才行
mysql> 
mysql> update tb_user set name = '吕小布' where id =1;
#验证查询，结果不能查询，语句一直阻塞，必须要等客户端1解锁才行
mysql> select * from tb_user;

元数据锁

元数据锁加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。元数据锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。元数据指的就是表结构，如果某张表存在未提交的事务，我们不可以修改表的表结构。目的是避免DML语句与DDL语句冲突，保证读写的正确性。

在MySQL5.5中引入了元数据锁，当对一张表进行增删改查的时候，加元数据锁读锁（共享）；当对表结构进行变更操作的时候，加元数据锁写锁（排他）。

验证

#客户端1
#开启事务
mysql> begin;
#查询表
mysql> select id,name,phone from tb_user;
+----+-----------+-------------+
| id | name      | phone       |
+----+-----------+-------------+
|  1 | 吕奉先    | 17799990000 |
|  2 | 曹操      | 17799990001 |
|  3 | 赵云      | 17799990002 |
+----+-----------+-------------+
3 rows in set (0.00 sec)

#客户端2
#在客户端2，修改表结构，一直阻塞，说明客户端1在查询时，加了元数据共享读锁，而客户端修改表结构时，加了排他锁，这个排他锁与所有元数据锁都互斥，所以不能执行修改表结构语句
mysql> alter table tb_user add column haha int;

查看元数据锁

#开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#查看此时的元数据锁，只有一个SHARED_READ共享读锁
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+---------------+
| object_type | object_schema      | object_name    | lock_type   | lock_duration |
+-------------+--------------------+----------------+-------------+---------------+
| TABLE       | performance_schema | metadata_locks | SHARED_READ | TRANSACTION   |
+-------------+--------------------+----------------+-------------+---------------+
1 row in set (0.00 sec)

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在innoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

意向共享锁（IS）：由语句select … lock in share mode添加，与表锁共享锁（read)兼容，与表锁排他锁（write）互斥

意向排他锁（IX）：由insert、update、delete、select … for update添加，与表锁共享锁（read）及排他锁（write）都互斥。意向锁之间不会互斥

行级锁

介绍：每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在innoDB存储引擎中。

innoDB的数据时基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类

行锁：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
- 行级共享锁S：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
- 行级排他锁X：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
  - insert、update、delete mysql自动加行级排他锁
  - select 不会加任何行级锁
  - select … lock in share mode 手动加共享锁
  - select … for update 手动加排他锁
    
    默认情况下，innoDB在REPEATABLE READ事务隔离级别运行，innoDB使用next-key锁（临键锁）进行搜索和索引扫描，以防止幻读。
    1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
    2. innoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么innoDB将对表中的所有记录加锁，此时就会升级为表锁。
间隙锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
临键锁：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap，在RR隔离级别下支持。

验证select不加任何锁

#客户端1

#开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#执行查询语句
mysql> select id,name,phone from tb_user where id = 1;
+----+-----------+-------------+
| id | name      | phone       |
+----+-----------+-------------+
|  1 | 吕奉先    | 17799990000 |
+----+-----------+-------------+
1 row in set (0.00 sec)

#客户端2

#查询锁状态
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec) #没加任何锁

验证select … lock in share mode 手动加共享锁

#客户端1
mysql> begin;
#查询手动加锁
mysql> select id,name,phone from tb_user where id = 1 lock in share mode;
+----+-----------+-------------+
| id | name      | phone       |
+----+-----------+-------------+
|  1 | 吕奉先    | 17799990000 |
+----+-----------+-------------+
1 row in set (0.00 sec)

#客户端2
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast        | tb_user     | NULL       | TABLE     | IS            | NULL      |
| itcast        | tb_user     | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
#验证结果：加了共享锁IS
#客户端2再加一个共享锁
mysql> select id,name,phone from tb_user where id = 1 lock in share mode;
+----+-----------+-------------+
| id | name      | phone       |
+----+-----------+-------------+
|  1 | 吕奉先    | 17799990000 |
+----+-----------+-------------+
1 row in set (0.00 sec)
#验证结果：加了两个共享锁IS
#当执行修改数据的sql时，阻塞，因为id为1的这一行数据被共享锁锁住了，不能再添加排他锁，而update语句加的就是排他锁
mysql> update tb_user set name = '吕小布' where id = 1;

验证insert、update、delete mysql自动加行级排他锁

#客户端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#执行修改语句
mysql> update tb_user set name='吕狗蛋' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#客户端2
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast        | tb_user     | NULL       | TABLE     | IX            | NULL      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
#客户端2进行修改id为1的语句无法执行
mysql> update tb_user set name = '吕布' where id =1；
    -> 
#但是客户端2，可以执行修改其他行的语句
mysql> update tb_user set name ='曹阿瞒' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
#验证结果是客户端1执行修改语句，自动加了行级排他锁

验证innoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么innoDB将对表中的所有记录加锁，此时就会升级为表锁。

#客户端1
#查询tb_user表的索引，只有主键有一个索引
mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY  |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
#开启事务
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql>  update tb_user set name='吕狗蛋' where name ='吕奉先';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

#客户端2
#查看锁状态
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data              |
+---------------+-------------+------------+-----------+-----------+------------------------+
| itcast        | tb_user     | NULL       | TABLE     | IX        | NULL                   |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | supremum pseudo-record |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 1                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 3                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 4                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 5                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 6                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 7                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 8                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 9                      |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 10                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 11                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 12                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 13                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 14                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 15                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 16                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 17                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 18                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 19                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 20                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 21                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 22                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 23                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 24                     |
| itcast        | tb_user     | PRIMARY    | RECORD    | X         | 2                      |
+---------------+-------------+------------+-----------+-----------+------------------------+
26 rows in set (0.00 sec)
#可以看出，修改语句，通过没有索引的字段筛选，会把表的每一行都锁住，行锁升级为表锁

验证间隙锁：锁定索引记录间隙

#客户端1
#查询user2表数据
mysql> select * from user2;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  5 | Kit  | 男     |
|  8 | Ruby | 男     |
| 10 | Arm  | 女     |
| 11 | Rose | 女     |
| 12 | Zoom | 男     |
| 14 | Tim  | 男     |
| 15 | Dawn | 男     |
| 21 | Pine | 男     |
| 25 | Jack | 男     |
| 26 | Geek | 女     |
| 27 | Hero | 男     |
| 28 | Lem  | 男     |
| 30 | Roxy | 男     |
| 45 | Oil  | 女     |
| 60 | Xint | 男     |
| 72 | Mina | 女     |
| 80 | Lee  | 男     |
| 90 | Lily | 女     |
+----+------+--------+
18 rows in set (0.00 sec)

#开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#修改id为7的数据，但是id为7的数据不存在
mysql> update user2 set name ='haha' where id = 7;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

#客户端2
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| itheima       | user2       | NULL       | TABLE     | IX        | NULL      |
| itheima       | user2       | PRIMARY    | RECORD    | X,GAP     | 8         |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
#插入id为7的数据，一直阻塞
mysql> insert into user2 values(7,'jjj','女');

#验证结果：主键索引加了行级排他锁和间隙锁，锁的是8之前的间隙，即5和8之间的间隙

验证临键锁：同时锁住数据，并锁住数据前面的间隙Gap

等值验证

#客户端1
#创建一个普通索引
mysql> create index idx_name on user2(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
#查看索引
mysql> show index from user2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user2 |          0 | PRIMARY  |            1 | id          | A         |          18 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user2 |          1 | idx_name |            1 | name        | A         |          18 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
#开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#查询并手动加一个共享锁
mysql> select * from user2 where name = 'Oil' lock in share mode;
+----+------+--------+
| id | name | gender |
+----+------+--------+
| 45 | Oil  | 女     |
+----+------+--------+
1 row in set (0.00 sec)

#客户端2
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+------------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data  |
+---------------+-------------+------------+-----------+---------------+------------+
| itheima       | user2       | NULL       | TABLE     | IS            | NULL       |
| itheima       | user2       | idx_name   | RECORD    | S             | 'Oil', 45  |
| itheima       | user2       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 45         |
| itheima       | user2       | idx_name   | RECORD    | S,GAP         | 'Pine', 21 |
+---------------+-------------+------------+-----------+---------------+------------+
4 rows in set (0.00 sec)
#先加了一个行锁 itheima       | user2       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 45         |
#然后加了一个间隙锁，把21和45之间的间隙也锁上，同时锁住数据 ，并锁住数据前面的间隙Gap