MySQL高级

Mysql高级

1. 字符集的修改和表在文件系统中的表示

1.1字符集的修改

分别查看Mysql8和Mysql5.7的默认字符集区别show variables like 'character%'

• mysql8.0

mysql> show variables like 'character%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | D:\mysql8\mysql-8.0.19-winx64\share\charsets\ |
+--------------------------+-----------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

• mysql5.7

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

看看他们分别创建数据库的区别

在8.0中

在5.7中,默认字符集不支持中文，因此我们需要修改我们的字符集

修改字符集

• 找到配置文件vim /etc/my.cnf

• 添加中文字符集配置：

character_set_server=utf8

• 此时修改的操作并不会生效，我们还需要重启mysql服务

#重启mysql服务
systemctl restart mysqld.service;

• 再次查看可见字符修改成功

注意：修改后影响的是以后创建的数据库，而先前的数据库不受影响

已有库&表的字符集变更

1. 修改已创建数据库的字符集

alter database dbtest1 character set 'utf8'; 

2. 修改已创建数据表的字符集

alter table t_emp convert to character set 'utf8';

注意：但是原有的数据如果是用非utf8编码的话，数据本身编码不会发生改变。已有数据需要导出或删除，然后重新插入。

字符集说明

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷的定义了两个概念：

• utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。（默认utf就是按utfmb3）

• utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。（mysql8.0默认）

大小写说明

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名、表名、表的别名、变量名是严格区分大小写的；

2、关键字、函数名称在 SQL 中不区分大小写；

3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

注意：MySQL在Windows的环境下全部不区分大小写

SQL编写建议

1. 关键字和函数名称全部大写；

2. 数据库名、表名、表别名、字段名、字段别名等全部小写；

3. SQL 语句必须以分号结尾。

1.2 表在文件系统中的表示

InnoDB存储引擎

1. 表结构

表名.frm

2. 表中的数据和索引

• 系统表空间

默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件（自扩展文件），这个文件就是对应的 系统表空间 在文件系统上的表示。

• 独立表空间

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为 每 一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个 .ibd 的扩展名而已。

表名.ibd

• 系统表空间与独立表空间的设置

我们可以配置mysql服务器来决定使用哪种方式

[server] innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间

如果采用 系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中

如果采用 独立表空间 存储模式，data\a中还会产生 b.ibd 文件（存储数据信息和索引信息）

MyISAM存储引擎模式

1. 表结构

与InnoDB一致，为表名.frm

2. 表中的数据和索引

表名.frm #存储表结构
表名.MYD #存储数据 (MYData)
表名.MYI #存储索引 (MYIndex)

2.用户的权限和管理

2.1 用户管理

登录mysql服务器

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

• -h:主机号
• -P：端口号
• -p：密码
• DatabaseName：表示连接到哪个数据库
• -e：执行的SQL语句

查看用户

这四个用户都是系统默认帮我们创建好的用户

创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

• 用户名参数表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；如:‘amo’@‘%’

• 接下来我们可以验证一下用amo这个用户登录mysql

• 查看权限，发现只有一点点权限

修改用户

UPDATE mysql.user SET USER='amoyyy' WHERE USER='amo';
FLUSH PRIVILEGES;--记得刷新权限

举例

mysql> use mysql;
Database changed
mysql> update user set user='amoyyy' where user='amo';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

删除用户

• drop删除（推荐）

DROP USER user[,user]…; -- 可以删除多个用户，默认host为%

举例

• delete删除

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;--执行这个命令需要刷新权限

注意：不推荐通过DELETE FROM USER u WHERE USER='li4'进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

修改用户密码

修改密码分为两种：一种是登录用户的密码修改，另一种是管理员修改普通用户的密码

1. 登录用户的密码修改（适用于普通用户和根用户）

• 使用ALTER USER命令来修改当前用户密码

ALTER USER USER() IDENTIFIED BY 'new_password';

再次登录尝试，可见修改成功

• 使用SET语句来修改当前用户密码

SET PASSWORD='new_password';

再次登录

2. 管理员修改普通用户的密码

• 使用ALTER句来修改普通用户的密码

ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…; --user表示用户名

举例:将amoyyy用户密码修改为hello

mysql> alter user amoyyy identified by 'hello';
Query OK, 0 rows affected (0.01 sec)

• 使用SET句来修改普通用户的密码

SET PASSWORD FOR 'username'@'hostname'='new_password';

举例: 将amoyyy用户密码修改为amoyyy

mysql> set password for 'amoyyy'@'%'='amoyyy';
Query OK, 0 rows affected (0.01 sec)

2.2 权限管理

权限列表

输入命令show privileges查看所有权限

mysql> show privileges;
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege                  | Context                               | Comment                                               |
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                      | Tables                                | To alter the table                                    |
| Alter routine              | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                     | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine             | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create role                | Server Admin                          | To create new roles                                   |
| Create temporary tables    | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view                | Tables                                | To create new views                                   |
| Create user                | Server Admin                          | To create new users                                   |
| Delete                     | Tables                                | To delete existing rows                               |
| Drop                       | Databases,Tables                      | To drop databases, tables, and views                  |
| Drop role                  | Server Admin                          | To drop roles                                         |
| Event                      | Server Admin                          | To create, alter, drop and execute events             |
| Execute                    | Functions,Procedures                  | To execute stored routines                            |
| File                       | File access on server                 | To read and write files on the server                 |
| Grant option               | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                      | Tables                                | To create or drop indexes                             |
| Insert                     | Tables                                | To insert data into tables                            |
| Lock tables                | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                    | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                      | Server Admin                          | To make proxy user possible                           |
| References                 | Databases,Tables                      | To have references on tables                          |
| Reload                     | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client         | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave          | Server Admin                          | To read binary log events from the master             |
| Select                     | Tables                                | To retrieve rows from table                           |
| Show databases             | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view                  | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                   | Server Admin                          | To shut down the server                               |
| Super                      | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                    | Tables                                | To use triggers                                       |
| Create tablespace          | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                     | Tables                                | To update existing rows                               |
| Usage                      | Server Admin                          | No privileges - allow connect only                    |
| ENCRYPTION_KEY_ADMIN       | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ARCHIVE    | Server Admin                          |                                                       |
| BACKUP_ADMIN               | Server Admin                          |                                                       |
| SYSTEM_USER                | Server Admin                          |                                                       |
| CLONE_ADMIN                | Server Admin                          |                                                       |
| RESOURCE_GROUP_ADMIN       | Server Admin                          |                                                       |
| SESSION_VARIABLES_ADMIN    | Server Admin                          |                                                       |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin                          |                                                       |
| CONNECTION_ADMIN           | Server Admin                          |                                                       |
| SYSTEM_VARIABLES_ADMIN     | Server Admin                          |                                                       |
| APPLICATION_PASSWORD_ADMIN | Server Admin                          |                                                       |
| AUDIT_ADMIN                | Server Admin                          |                                                       |
| BINLOG_ADMIN               | Server Admin                          |                                                       |
| BINLOG_ENCRYPTION_ADMIN    | Server Admin                          |                                                       |
| SET_USER_ID                | Server Admin                          |                                                       |
| SERVICE_CONNECTION_ADMIN   | Server Admin                          |                                                       |
| GROUP_REPLICATION_ADMIN    | Server Admin                          |                                                       |
| REPLICATION_APPLIER        | Server Admin                          |                                                       |
| TABLE_ENCRYPTION_ADMIN     | Server Admin                          |                                                       |
| ROLE_ADMIN                 | Server Admin                          |                                                       |
| REPLICATION_SLAVE_ADMIN    | Server Admin                          |                                                       |
| RESOURCE_GROUP_USER        | Server Admin                          |                                                       |
| XA_RECOVER_ADMIN           | Server Admin                          |                                                       |
+----------------------------+---------------------------------------+-------------------------------------------------------+
56 rows in set (0.00 sec)

授予权限原则

权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：

1、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。

2、创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。

3、为每个用户 设置满足密码复杂度的密码 。

4、 定期清理不需要的用户 ，回收权限或者删除用户。

直接授予权限

授权命令:

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

• 该权限如果发现没有该用户，则会直接新建一个用户。

• 举例

给amoyyy用户用网络命令行方式，授予dbtest1这个库下的所有表的查找、更新和添加的权限

mysql> grant select,insert,update on dbtest1.* to 'amoyyy'@'%';
Query OK, 0 rows affected (0.01 sec)

登录amoyyy用户，执行操作

授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限（即他不能授予别人权利）

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

查看权限

查看当前用户权限:

• SHOW GRANTS;

mysql> show grants;
+-------------------------------------------------------------+
| Grants for amoyyy@%                                         |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `amoyyy`@`%`                          |
| GRANT SELECT, INSERT, UPDATE ON `dbtest1`.* TO `amoyyy`@`%` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec) 

• SHOW GRANTS FOR CURRENT_USER;

mysql> show grants for current_user;
+-------------------------------------------------------------+
| Grants for amoyyy@%                                         |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `amoyyy`@`%`                          |
| GRANT SELECT, INSERT, UPDATE ON `dbtest1`.* TO `amoyyy`@`%` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

• SHOW GRANTS FOR CURRENT_USER();

mysql> show grants for current_user();
+-------------------------------------------------------------+
| Grants for amoyyy@%                                         |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `amoyyy`@`%`                          |
| GRANT SELECT, INSERT, UPDATE ON `dbtest1`.* TO `amoyyy`@`%` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

查看某用户的全局权限:SHOW GRANTS FOR 'user'@'主机地址' ;

mysql> SHOW GRANTS FOR 'amoyyy'@'%' ;
+-------------------------------------------------------------+
| Grants for amoyyy@%                                         |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `amoyyy`@`%`                          |
| GRANT SELECT, INSERT, UPDATE ON `dbtest1`.* TO `amoyyy`@`%` |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

收回权限

收回权限就是取消已经赋予用户的某些权限。**收回用户不必要的权限可以在一定程度上保证系统的安全性。**MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。

注意:在将用户账户从user表删除之前，应该收回相应用户的所有权限

• 收回权限命令:

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

• 举例

-- 收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM 'amoyyy'@'%'; 
-- 收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'amoyyy'@'%';

注意:收回权限需要用户重新登录后才生效

2.3 角色管理

引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关重要的。角色不能用于登录数据库！！！！

创建角色

角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为空。

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

• 举例

分别创建一个管理者角色和一个经理的角色

mysql> create role 'boss'@'localhost','manager';
Query OK, 0 rows affected (0.01 sec)

给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

• 举例

给管理员授予dbtest1数据库下所有表的查询与更新功能

mysql> grant select,update on dbtest1.* to 'manager';
Query OK, 0 rows affected (0.00 sec)

给经理赋予所有权限

mysql> grant all privileges on *.* to 'boss'@'localhost';
Query OK, 0 rows affected (0.01 sec)

查看角色的权限

SHOW GRANTS FOR 角色名

我们分别查看刚刚授权是否成功

mysql> show grants for 'manager';
+------------------------------------------------------+
| Grants for manager@%                                 |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%`                  |
| GRANT SELECT, UPDATE ON `dbtest1`.* TO `manager`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'boss'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for boss@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `boss`@`localhost`                                                                                                           |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `boss`@`localhost` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

回收角色权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。修改了角色的权限，会影响拥有该角色的账户的权限。

REVOKE privileges ON tablename FROM 'rolename';

举例:回收manger角色的更新权限

mysql> revoke update on dbtest1.* from 'manager';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'manager';
+----------------------------------------------+
| Grants for manager@%                         |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%`          |
| GRANT SELECT ON `dbtest1`.* TO `manager`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)

删除角色

如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限

DROP ROLE role [,role2]...

• 举例

删除boss这个角色

mysql> drop role 'boss'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'boss'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'boss' on host 'localhost' --角色不存在

2.4 角色赋予

给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。

GRANT role [,role2,...] TO user [,user2,...];

• 举例

我们先创建一个对象zhang3

mysql> create user 'zhang3' identified by 'zhang123';
Query OK, 0 rows affected (0.01 sec)

登上其账户并查看当前权限

mysql> show grants;
+------------------------------------+
| Grants for zhang3@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `zhang3`@`%` |
+------------------------------------+
1 row in set (0.00 sec)

现在将manager角色的权限赋予zhang3

mysql> grant 'manager' to 'zhang3';
Query OK, 0 rows affected (0.01 sec)

再次查询zhang3权限

mysql> show grants;
+-------------------------------------+
| Grants for zhang3@%                 |
+-------------------------------------+
| GRANT USAGE ON *.* TO `zhang3`@`%`  |
| GRANT `manager`@`%` TO `zhang3`@`%` |
+-------------------------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

我们发现角色虽然被赋予了，但仍然权限不生效，这是因为我们未激活角色！！

使用SELECT CURRENT_ROLE(); 语句可以查看角色是否被激活

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

可以看见未被激活

角色激活

1. 使用set default role命令激活角色

SET DEFAULT ROLE ALL TO 'zhang3'@'%';

举例:激活manager角色

mysql> SET DEFAULT ROLE 'manager'@'%' TO 'zhang3'@'%';
Query OK, 0 rows affected (0.00 sec)
-- 激活后需要重新登录才起效
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `manager`@`%`  |
+----------------+
1 row in set (0.00 sec)
-- 此时再次查询数据库便可以看见
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dbtest1            |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

2. 将activate_all_roles_on_login设置为ON

默认情况下

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set, 1 warning (0.01 sec)

设置

SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

撤销用户的角色

REVOKE role FROM user;

• 举例

撤销zhang3的用户角色

mysql> revoke 'manager' from 'zhang3';
Query OK, 0 rows affected (0.01 sec)
-- 查看可以发现张3的role被回收
mysql> show grants for 'zhang3';
+------------------------------------+
| Grants for zhang3@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `zhang3`@`%` |
+------------------------------------+
1 row in set (0.00 sec)

设置强制角色

1. 服务启动前设置

[mysqld] 
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

2. 运行时设置

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; -- 系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; -- 系统重启后失效

3.逻辑架构

3.1 逻辑架构剖析

服务器处理客户端请求

连接层

• 系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。

• 经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

• TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

服务层

• SQL Interface: SQL接口

接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface

MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

• Parser: 解析器

在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。

在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建 语法树 ,并根据数据字典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后MySQL还会对SQl查询进行语法上的优化，进行查询重写。

• Optimizer: 查询优化器

SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划 。这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。

• Caches & Buffers： 查询缓存组件

  • MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。

  • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。

  • 这个查询缓存可以在 不同客户端之间共享 。

  • 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。

引擎层

插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

MySQL8.0默认支持的引擎有:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

3.2 SQL执行流程

MySQL中sql的执行流程

SQL语法顺序

查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人，显示人数最多的第一名部门信息

下面是经常出现的查询顺序：

4. 存储引擎

4.1 操作

配置存储引擎

查看默认的存储引擎：show variables like '%storage_engine%';或SELECT @@default_storage_engine;

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set, 1 warning (0.01 sec)

修改默认的存储引擎:SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改my.cnf文件

default-storage-engine=MyISAM 

然后再重启MySQL服务器

# 重启服务 
systemctl restart mysqld.service

设置表的存储引擎

1. 创建表时指定存储引擎

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎 InnoDB 。如果我们想显式的指定一下表的存储引擎，那可以这么写：

CREATE TABLE 表名( 
    建表语句; 
) ENGINE = 存储引擎名称;

2. 修改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名称;

4.2 InnoDB存储引擎

InnoDB 引擎：具备外键支持功能的事务存储引擎

• MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。

• InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

• 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。

• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

• 数据文件结构：

  表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

  表名.ibd 存储数据和索引

• InnoDB是 为处理巨大数据量的最大性能设计 。

• 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了。

• 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和索引。

• MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。

4.3 MYISAM引擎

MyISAM 引擎：主要的非事务处理存储引擎

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级 锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。

• 5.5之前默认的存储引擎

• 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用

• 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高

• 数据文件结构：

  表名.frm 存储表结构

  表名.MYD 存储数据 (MYData)

  表名.MYI 存储索引 (MYIndex)

• 应用场景：只读应用或者以读为主的业务

4.4 InnoDB和MYISAM引擎对比

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y

5.索引

5.1 索引及其优缺点

索引概述

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

**索引的本质：**索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。

这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现 高级查找算法 。

索引优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主要的原因。

（2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。

（3）在实现数据的参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

（4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时 间 ，降低了CPU的消耗。

索引缺点

增加索引也有许多不利的方面，主要表现在如下几个方面：

（1）创建索引和维护索引要 耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。

（2）索引需要占 磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

（3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。因此，选择使用索引时，需要综合考虑索引的优点和缺点。

提示：索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

5.2 InnoDB索引

设计索引

我们建一个表

mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;

这个新建的 index_demo 表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，

这个表使用 Compact 行格式来实际存储记录的。这里我们简化了index_demo表的行格式示意图：

在上面这个图中：

record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记录、 3 表示最大记录、 1 表示目录项记录

next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。

各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。

其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

我们存储所用到的结构为B+树，用到的B+树的层次一般都不会超过4层，树的高度代表IO的操作次数，因此我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页）。

---

---

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

聚簇索引

特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内 的记录是按照主键的大小顺序排成一个 单向链表 。

• 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。

• 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。

2. B+树的 叶子节点 存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

• 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

• 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快

• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。

缺点：

• 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

• 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新

• 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

二级索引（辅助索引、非聚簇索引）

概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！（如果二级索引也保存完整信息，那么这样数据就太冗余了，因为二级索引可以创建多个！）

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序。

• 在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

• 建立 联合索引 只会建立如上图一样的1棵B+树。

• 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

注意事项

**1. ** 根页面位置万年不动

B+树的形成过程是这样的:

• 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
• 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
• 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是:一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

2. 内节点中目录项记录的唯一性

我们知道B+树索引的内节点中目录项记录的内容是索引列＋页号的搭配，但是这个搭配对于二级索引来说有点儿不严谨。还拿index_demo表为例，假设这个表中的数据是这样的:

c1	c2	c3
1	1	‘u’
3	1	‘d’
5	1	‘y’
7	1	‘a’

如果我们只单纯的按索引列+页号作为目录项记录，那么构建的B+树为：

如何确保目录项记录唯一呢？因此我们把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以我们为c2列建立二级索引后的示意图实际上应该是这样子的

3. 一个页面最少存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错!这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢?那就是目录层级非常非常非常多，而且最后的那个存放真实数据的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录?所以InnoDB的一个数据页至少可以存放两条记录。

5.3 MyISAM中的索引方案

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址 。

我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储∶

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
• 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyITSAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。

5.4 MyISAM 与 InnoDB对比

MyISAM的索引方式都是非聚簇的，与InnoDB包含1个聚簇索引是不同的。两种引擎中索引的区别：

① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

5.5索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价

每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

5.6关于Hash索引

Hash结构效率高，那为什么索引结构要设计成树型呢?

原因1: Hash索引仅能满足（=）、（<>）和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为O（n）;而树型的“有序"特性，依然能够保持O(log2N)的高效率。

原因2:∶Hash 索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。

原因3∶对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。

原因4:对于等值查询来说，通常Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列上，比如列为性别年龄的情况等。

Hash索引的适应性

Hash 索引存在着很多限制，相比之下在数据库中B+树索引的使用面会更广，不过也有一些场景采用Hash索引效率更高，比如在键值型(Key-Value)数据库中, Redis存储的核心就是Hash表。

MySQL中的Memory存储引擎支持Hash存储，如果我们需要用到查询的临时表时，就可以选择Memory存储引擎，把某个字段设置为Hash 索引，比如字符串类型的字段，进行Hash计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值查询的时候，架用Hash索引是个不错的选择。

另外，InnoDB本身不支持 Hash索引，但是提供**自适应 Hash索引(Adaptive Hash Index)。**什么情况下才会使用自适应Hash 索引呢?如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。

5.7思考题

B+树和B树的区别

1. 有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数+1。

2. 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。

3. 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中， 非 叶子节点既保存索引，也保存数据记录 。

4. 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

思考题：为了减少IO，索引树会一次性加载吗？

1、数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G。
2、当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是:逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节）或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^{3。也就是说一个深度为3的B+Tree索引可以维护`10^3*10^3*10^3=10`亿条记录。(这里假定一个数据页也存储10}3条行记录数据了)实际情况中每个节点可能不能填充满，因此在数据库中，**B+Tree 的高度一般都在2_{4层**。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

思考题：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

1、B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2、B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

思考题：Hash 索引与 B+ 树索引的区别

我们之前讲到过B+树索引的结构，Hash索引结构和B树的不同，因此在索引使用上也会有差别。

1、Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。

2、Hash索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash 索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。

3、Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询（比如%结尾）的话就可以起到优化作用。

4、InnoDB 不支持哈希索引

思考题：Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？

可以看到，针对InnoDB和MyISAM存储引擎，都会默认采用B+树索引，无法使用Hash索引。InnoDB提供的自适应Hash 是不需要手动指定的。如果是Memory/Heap和NDB存储引擎，是可以进行选择Hash索引的。

算法的时间复杂度

6.索引的创建与设计原则

6.1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。

按照 作用字段个数 进行划分，分成单列索引和联合索引。

普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的字段name上建立一个普通索引，查询记录时就可以根据该索引进行查询。

唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯的，但允许有空值。在一张数据表里可以有多个唯一索引。例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引，可以更快速地确定某条记录。

主键索引

主键索引就是一种特殊的唯一性索引， 在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。**Why?**这是由主键索引的物理实现方式决定的， 因为数据存储在文件中只能按照一种顺序进行存储。

单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯-一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

多列(组合、联合)索引

多列索引是在表的多个字段组合.上创建一个索引。 该索引指向创建时对应的多个字段,可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender.上建立一个多列索引idx_ id. name_ gender、 ，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用[分词技术]等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。使用参数FULLTEXT可以设置索引为全文索引。在定义索引|的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引|只能创建在CHAR、VARCHAR 或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表student的字段information是TEXT类型,该字段包含了很多文字信息。在字段information. 上建立全文索引后，可以提高查询字段information的速度。

6.2 创建索引

创建表的时候创建索引

CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；

• INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；

• index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；

• col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；

• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

• ASC 或 DESC 指定升序或者降序的索引值存储。

举例：

1. 创建普通索引

-- 创建普通索引
CREATE TABLE book( 
book_id INT , 
book_name VARCHAR(100),
authors VARCHAR(100), 
info VARCHAR(100) , 
comment VARCHAR(100), 
year_publication YEAR, 
INDEX(year_publication) 
);

查看索引

SHOW INDEX FROM book;

2. 创建唯一索引

-- 创建唯一索引
CREATE TABLE test1( 
id INT NOT NULL, 
name varchar(30) NOT NULL, 
UNIQUE INDEX uk_idx_id(id) 
);

SHOW INDEX FROM test1;

3. 创建主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：

-- 创建主键索引
CREATE TABLE student (
id INT(10) UNSIGNED PRIMARY KEY, 
student_no VARCHAR(200), 
student_name VARCHAR(200)
);
-- 查看索引
SHOW INDEX FROM student;

删除主键索引（通过删除的主键的方式删除主键索引）

-- 删除主键索引
ALTER TABLE student drop PRIMARY KEY;

4. 创建单列索引

-- 创建单列索引
CREATE TABLE test2(
id INT NOT NULL, 
name CHAR(50) NULL, 
INDEX single_idx_name(name(20)) 
);
SHOW INDEX FROM test2;

5. 创建组合索引

-- 创建组合索引
CREATE TABLE test3( 
id INT(11) NOT NULL, 
name CHAR(30) NOT NULL, 
age INT(11) NOT NULL, 
info VARCHAR(255), 
INDEX multi_idx(id,name,age) 
);
SHOW INDEX FROM test3;

6. 创建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索弓|总是对整个列进行，不支持局部(前缀)索引。

-- 创建全文索引
CREATE TABLE test4( 
id INT NOT NULL, 
name CHAR(30) NOT NULL, 
age INT NOT NULL, 
info VARCHAR(255), 
FULLTEXT INDEX futxt_idx_info(info) 
);
SHOW INDEX FROM test4;

在已经存在的表上面创建索引

1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：

ALTER TABLE table_name 
ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

举例:

先创建个普通的表

-- 在建表的基础上创建索引
CREATE TABLE test5( 
book_id INT , 
book_name VARCHAR(100),
authors VARCHAR(100), 
info VARCHAR(100) , 
comment VARCHAR(100), 
year_publication YEAR
);

• 创建普通索引

-- 创建普通索引
ALTER TABLE test5 ADD INDEX idx_cmt(comment);
SHOW INDEX FROM test5;

• 创建唯一索引

-- 创建唯一索引
ALTER TABLE test5 ADD UNIQUE INDEX uk_idx_bname(book_name);

• 创建组合索引

-- 创建组合索引
ALTER TABLE test5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

2. 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

举例：

-- 第二种方式
CREATE TABLE test6( 
book_id INT , 
book_name VARCHAR(100),
authors VARCHAR(100), 
info VARCHAR(100) , 
comment VARCHAR(100), 
year_publication YEAR
);
-- 创建索引
CREATE INDEX idx_cmt ON test6(comment);
CREATE UNIQUE INDEX uk_idx_bname ON test6(book_name);
CREATE INDEX mul_bid_bname_info ON test6(book_id,book_name,info);
-- 查看索引
SHOW INDEX FROM test6;

6.3 删除索引

AlTER TABLE方式

ALTER TABLE table_name DROP INDEX index_name;

DROP INDEX方式

DROP INDEX index_name ON table_name;

提示: 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除

举例:

采用方式一

-- 索引的删除
-- 方式一 ALTER TABLE
ALTER TABLE test5 DROP INDEX idx_cmt;

采用方式二

-- 方式二 DROP INDEX
DROP INDEX uk_idx_bname ON test5;

现在我们剩下一个联合索引，如果我们删除其中的一列会怎么样呢

-- 删除组合索引中的一列
ALTER TABLE test5 DROP COLUMN book_name;

我们删除到剩下的两个字段，发现索引也跟着被删除

-- 删除剩余的两列
ALTER TABLE test5 DROP COLUMN book_id;
ALTER TABLE test5 DROP COLUMN info;
-- 查看索引
SHOW INDEX FROM test5;

6.4 隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索 引，再删除索引的方式就是软删除 。

创建隐藏索引

方式一：建表的时候创建

CREATE TABLE tablename(
    propname1 type1[CONSTRAINT1], 
    propname2 type2[CONSTRAINT2], 
    ……
    propnamen typen,
    INDEX [indexname](propname1 [(length)]) INVISIBLE 
);

上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。

-- 创建隐藏索引
CREATE TABLE book1( 
book_id INT , 
book_name VARCHAR(100),
authors VARCHAR(100), 
info VARCHAR(100) , 
comment VARCHAR(100), 
year_publication YEAR,
INDEX idx_cmt(comment) INVISIBLE
);

我们执行一下查询语句，看看它是否会去走索引EXPLAIN SELECT * FROM book1 WHERE comment='amo';

方式二 ：建表基础上新增隐藏索引

-- 创建表后
ALTER TABLE book1 ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;

CREATE INDEX idx_year_pub ON book1(year_publication);
-- 查看
SHOW INDEX FROM book1;

我们执行一下不隐藏的索引，看看其是否采用索引EXPLAIN SELECT * FROM book1 WHERE year_publication='2022';

修改索引的可见性

我们将idx_cmt索引改为可见

ALTER TABLE book1 ALTER INDEX idx_cmt visible;

注意:当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

使隐藏索引被优化器可见

在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关**（use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes设置为off(默认)**，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。

1. 在MySQL命令行执行如下命令查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

​ 在输出的结果信息中找到如下属性配置。

-- 此属性配置值为off，说明隐藏索引默认对查询优化器不可见。
use_invisible_indexes=off

2. 使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令：

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

在输出的结果信息中找到如下属性配置。

-- use_invisible_indexes属性的值为on，说明此时隐藏索引对查询优化器可见。
use_invisible_indexes=on

6.5 数据准备

创建数据库和数据表

-- 创建数据库
CREATE DATABASE atguigudb1;
-- 使用数据库
USE atguigudb1;
-- 1.创建学生表和课程表 
CREATE TABLE `student_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    `student_id` INT NOT NULL , 
    `name` VARCHAR(20) DEFAULT NULL, 
    `course_id` INT NOT NULL , 
    `class_id` INT(11) DEFAULT NULL, 
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

CREATE TABLE `course` ( 
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    `course_id` INT NOT NULL , 
    `course_name` VARCHAR(40) DEFAULT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建模拟数据必备的存储函数

-- 函数1：创建随机产生字符串函数
DELIMITER // 
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) 
-- 该函数会返回一个字符串 
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0; 
	WHILE i < n DO 
		SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
		SET i = i + 1; 
	END WHILE; 
	RETURN return_str; 
END // 
DELIMITER ;


-- 函数2：创建随机数函数
DELIMITER // 
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) 
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ; 
	RETURN i; 
END // 
DELIMITER ;

注意:如果创建上述的函数报错，解决方法如下

-- 1.查看mysql是否允许创建函数：
show variables like 'log_bin_trust_function_creators';

-- 2.命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; -- 不加global只是当前窗口有效。

上述方法中：mysql服务重启，上述参数又会消失，永久方法

在Window中，修改my.ini[mysqld]加上

log_bin_trust_function_creators=1

在linux中,/etc/my.cnf下my.cnf[mysqld]加上：

log_bin_trust_function_creators=1

创建插入模拟数据的存储过程

-- 存储过程1：创建插入课程表存储过程 
DELIMITER // 
CREATE PROCEDURE insert_course( max_num INT ) 
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; -- 设置手动提交事务 
	REPEAT -- 循环 
	SET i = i + 1; -- 赋值 
	INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6)); 
	UNTIL i = max_num
	END REPEAT; 
	COMMIT; -- 提交事务 
END // 
DELIMITER ;


-- 存储过程2：创建插入学生信息表存储过程 
DELIMITER // 
CREATE PROCEDURE insert_stu( max_num INT ) 
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; -- 设置手动提交事务 
	REPEAT -- 循环 
	SET i = i + 1; -- 赋值 
	INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT; -- 提交事务 
END // 
DELIMITER ;

调用存储过程

-- 向course表中插入100条数据
CALL insert_course(100);
-- 向student中插入100w条数据
CALL insert_stu(1000000);

检查结果

6.6 适合创建索引的几种情况

字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。

• 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

• 说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

频繁作为WHERE查询条件的字段

某个字段在SELECT语句的WHERE条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

经常GROP BY 和ORDER BY的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索

引 。如果待排序的列有多个，那么可以在这些列上建立 组合索引 。

UPDATE、DELETE的WHERE条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记

录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

DISTINCT字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

多表J0IN连接操作时,创建索引注意事项

首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。其次，对WHERE条件创建索引，因为

WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比

如course_id在student_info表和course表中都为int(11)类型，而不能一个为int另一个为varchar类型。

使用列的类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT 就不要使用INT。这是因为:

• 数据类型越小，在查询时进行的比较操作越快
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:

• B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大

• 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

区分度高(散列性高)的列适合作为索引

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，**在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。**这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

可以使用公式 select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

拓展:联合索引把区分度高(散列性高)的列放在前面。

使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

在多个字段都要创建索引的情况下，联合索引优于单值索引

6.7 不适合创建索引

限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因:

• 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

• 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。

• 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

在where中使用不到的字段，不要设置索引

WHERE条件(包括GROUP BY、ORDER BY)里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

数据量小的表最好不要使用索引

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的“性别"字段上只有“男"与“女"两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

避免对经常更新的表创建过多的索引

第一层含义:频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义:避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为Asdl，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

不要定义冗余或重复的索引

1. 冗余索引

有时候有意或者无意的就对同一个列创建了多个索引，比如: index(a,b,c)相当于index(a)、index(a,b)、index(a,b,c)。

2. 重复索引

另一种情况，我们可能会对某个列重复建立索引，比方说这样:

CREATE TABLE repeat_index_demo (
	col1 INT PRIMARY KEY ,
	col2 INT,
	UNIQUE uk_idx_c1 (col1 ) ,
    INDEX idx_c1 (col1)
);

我们看到，col1既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

7. 性能分析工具的使用

7.1 查看系统性能的参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。

SHOW STATUS语句语法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下:

• Connections:连接MySQL服务器的次数 。

• Uptime: MySQL服务器的上线时间。

• Slow_queries:慢查询的次数。

• lnnodb_rows_read: select查询返回的行数

• Innodb_rows_inserted:执行INSERT操作插入的行数

• Innodb_rows_updated:执行UPDATE操作更新的行数

• lnnodb_rows_deleted:执行DELETE操作删除的行数.

• Com_select:查询操作的次数。

• Com_insert:插入操作的次数。对于批量插入的INSERT 操作，只累加一次。

• Com_update:更新操作的次数。

• Com_delete:删除操作的次数。

7.2 统计SQL的查询成本

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001;

运行结果

mysql> SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001;
+------------+----------+--------+---------------------+
| student_id | class_id | NAME   | create_time         |
+------------+----------+--------+---------------------+
|     117334 |    10017 | IDSFws | 2022-07-30 15:17:27 |
+------------+----------+--------+---------------------+
1 row in set (0.00 sec)

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id BETWEEN 900001 AND 900100;

运行结果为

.......................................................
|     179182 |    10000 | yKbfwQ | 2022-07-30 15:17:27 |
|     198690 |    10140 | TskVWW | 2022-07-30 15:17:27 |
|     102156 |    10127 | HKFVKP | 2022-07-30 15:17:27 |
|      20908 |    10034 | aLEPjC | 2022-07-30 15:17:27 |
|      41133 |    10073 | WdByOw | 2022-07-30 15:17:27 |
|      79085 |    10200 | ZNSYpF | 2022-07-30 15:17:27 |
|     136235 |    10163 | YPcTlx | 2022-07-30 15:17:27 |
|      38374 |    10165 | zVbxjA | 2022-07-30 15:17:27 |
+------------+----------+--------+---------------------+
100 rows in set (0.00 sec)

然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 20.290463 |
+-----------------+-----------+
1 row in set (0.00 sec)

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页 数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 没有增加多少查询时间 。

**使用场景：**它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

7.3 定位执行慢的SQL：慢查询日志

MysQL的慢查询日志，用来记录在MysQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，我们就算慢sQL，希望能收集超过5秒的sql，结合explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件。

开启slow_query_log

在使用前，我们需要先看一下慢查询是否已经开启，使用下面这条命令即可：

show variables like '%slow_query_log';

我们发现默认是关着的

mysql> show variables like '%slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set, 1 warning (0.01 sec)

现在我们打开它

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.02 sec)

然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------------------+
| Variable_name       | Value                                                       |
+---------------------+-------------------------------------------------------------+
| slow_query_log      | ON                                                          |
| slow_query_log_file | D:\mysql8\mysql-8.0.19-winx64\data\USER-20190629LA-slow.log |
+---------------------+-------------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

修改long_query_time阈值

接下来我们来看下慢查询的时间阈值设置，使用如下命令：

 show variables like '%long_query_time%';

可以发现默认是10s

mysql>  show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

set global long_query_time = 1; 

但我们发现这样设置global的方式对当前session的long_query_time失效，对新连接的客户端有效。

mysql> show global variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.01 sec)
-- 对当前session失效
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

因此，我们需要再执行一下对当前session会话的修改

set long_query_time = 1; 

查看慢查询的数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

可以发现，没有

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.01 sec)

慢查询日志分析工具:mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。

查看mysqldumpslow的帮助信息mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

• a: 不将数字抽象成N，字符串抽象成S

• -s: 是表示按照何种方式排序：

  • c: 访问次数

  • l: 锁定时间

  • r: 返回记录

  • t: 查询时间

  • al:平均锁定时间

  • ar:平均返回记录数

  • at:平均查询时间 （默认方式）

  • 8ac:平均查询次数

• -t: 即为返回前面多少条的数据；

• -g: 后边搭配一个正则匹配模式，大小写不敏感的；

关闭慢查询日志

第一种方式:停止MySQL慢查询日志功能

SET GLOBAL slow_query_log=off;

第二种方式:重启MySQL服务

systemctl restart mysqld

删除慢查询日志文件

使用SHOW语句显示慢查询日志的信息，具体SQL语句如下

SHOW VARIABLES LIKE 'slow_query_log%';

执行结果

mysql> SHOW VARIABLES LIKE 'slow_query_log%';
+---------------------+-------------------------------------------------------------+
| Variable_name       | Value                                                       |
+---------------------+-------------------------------------------------------------+
| slow_query_log      | OFF                                                         |
| slow_query_log_file | D:\mysql8\mysql-8.0.19-winx64\data\USER-20190629LA-slow.log |
+---------------------+-------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可。使用命令mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件。

mysqladmin -uroot -p flush-logs slow

注意:慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

查看SQL的执行成本

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

可以发现默认是关闭的，通过设置profiling='ON’来开启 show profile：

mysql> set profiling = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：

mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00279075 | show variables like 'profiling' |
+----------+------------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

我们看到当前会话一共有 21个查询。如果我们想要查看最近一次查询的开销，可以使用：

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000011 |
| Opening tables       | 0.000017 |
| init                 | 0.000051 |
| System lock          | 0.000009 |
| optimizing           | 0.000003 |
| optimizing           | 0.000002 |
| statistics           | 0.000010 |
| preparing            | 0.000022 |
| statistics           | 0.000012 |
| preparing            | 0.000010 |
| executing            | 0.002390 |
| end                  | 0.000014 |
| query end            | 0.000010 |
| removing tmp table   | 0.000018 |
| query end            | 0.000004 |
| closing tables       | 0.000010 |
| freeing items        | 0.000115 |
| cleaning up          | 0.000017 |
+----------------------+----------+
19 rows in set, 1 warning (0.00 sec)

我们也可以查看指定的Query lD的开销，比如show profile for query 1查询结果是一样的。在SHOWPROFILE中我们可以查看不同部分的开销，比如cpu、block.io等:

mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000069 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000011 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000051 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000010 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000022 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000010 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.002390 | 0.015625 |   0.000000 |         NULL |          NULL |
| end                  | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000010 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table   | 0.000018 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000010 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000115 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
19 rows in set, 1 warning (0.00 sec)

show profile的常用查询参数：

1. ALL:显示所有的开销信息。
2. BLOCK IO:显示块IO开销。
3. CONTEXT SWITCHES: 上下文切换开销。
4. CPU:显示CPU开销信息。
5. IPC:显示发送和接收开销信息。
6. MEMORY:显示内存开销信息。
7. PAGE FAULTS:显示页面错误开销信息。
8. SOURCE:显示和Source_function，Source_file,Source_line相关的开销信息。
9. SWAPS:显示交换次数开销信息。

日常开发需注意的结论:

• converting HEAP to MyISAM:查询结果太大，内存不够，数据往磁盘上搬了。
• creating tmp table:创建临时表。先拷贝数据到临时表，用完后再删除临时表。
• Copying to tmp table on disk:把内存中临时表复制到磁盘上，警惕!
• locked。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

注意：

不过SHOW PROFILE命令将被弃用，我们可以从information_schema 中的profiling数据表进行查看。

7.4 分析查询语句 EXPLAIN

**定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。**DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。

MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。

这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

基本语法

EXPLAIN SELECT select_options 
-- 或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

mysql> EXPLAIN SELECT 1;

各列参数描述如下:

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

EXPLAIN各列作用

1. table

不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）

2.id

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• 关注点: id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

3.select_type

一条大的查询语句里边可以包含若 干个SELECT关键字，每 个SELECT关键字代表着一个小的查 询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一 张 表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

4.partitions

代表分区表中的命中情况，非分区表，该项为NULL。一般情况下我们的查询语句的执行计划的partitions列的值都是NULL。

5.type

执行计划的一条记录就代表着MySQL对某 个表的执行查询时的访问方法，又称访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。

比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

6.possible_key和key

在EXPLAIN语句输出的执行计划中，possible_ keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key 列表示实际用到的索引有哪些，如果为NULL,则没有使用索引。

7.key_ len:

实际使用到的索引长度(即:字节数)帮你检查、是否充分的利用上了索引，值越大越好、主要针对于联合索引，有一定的参考意义。

8.ref:

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。比如只是一个常数或者是某个列。

9.rows:

预估的需要读取的记录条数,值越小越好

10.filtered:

某个表经过搜索条件过滤后剩余记录条数的百分比

如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

小结

1. EXPLAIN不考虑各种Cache
2. EXPLAIN不能显示MySQL在执行查询时所作的优化工作
3. EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
4. 部分统计信息是估算的，并非精确值

7.5 索引优化

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索弓|对查询的速度有着至关重要的影响。

• 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度,提高数据库的性能。

• 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都(默认)采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer)，它不是基于规则( Rule- Basedoptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

COUNT(*)与COUNT(具体字段)的效率

问:在MysQL中统计数据表的行数，可以使用三种方式: SELECT COUNT（*）、SELECT COUNT(1)和SELECTCOUNT(具体字段)，使用这三者之间的查询效率是怎样的?

答:

前提:如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。

环节1：COUNT（*）和COUNT(1)都是对所有结果进行COUNT，COUNT(*)和COUNT(1)本质上并没有区别(二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的)。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计;如果没有WHERE子句，则是对数据表的数据行数进行统计。

环节2：如果是MyISAM存储引擎，统计数据表的行数只需要**O（1）**的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则由表级锁来保证。

如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MylSAM一样，维护一个row_count变量，因此需要采用扫描全表，是O（n）的复杂度，进行循环＋计数的方式来完成统计。

环节3：在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

关于SELECT（*）

在表查询中，建议明确字段，不要使用*作为查询的字段列表，推荐使用SELECT <字段列表>查询
原因:

MysQL在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。无法使用覆盖索引

LIMIT 1对优化的影响

针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1了

多使用COMMIT

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。

COMMIT所释放的资源:

• 回滚段上用于恢复数据的信息。
• 被程序语句获得的锁
• redo / undo log buffer 中的空间。
• 管理上述3种资源中的内部花费

8. 子查询优化和排序优化

8.1 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是MySQL的一项重要的功能，可以帮助我们通过一个sQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因:

①执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和lo资源，产生大量的慢查询。

②子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

③对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

8.2 排序优化

**问题:**在WHERE 条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢?

回答:
在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

• Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。

• FileSort排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/o到磁盘进行排序的情况，效率较低。

优化建议:

1. SQL中，可以在WHERE子句和ORDER BY子句中使用索引,目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用Index完成 ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列;如果不同就使用联合索引。
3. 无法使用Index时，需要对FileSort方式进行调优。

8.3 分组优化

• group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。
• group by先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
• where效率高于having，能写在where限定的条件就不要写在having中了
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在100o行以内，否则sQL会很慢。

9.主键的设计

自增ID的问题

自增ID做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。自增ID除了简单，其他都是缺点，总体来看存在以下几方面的问题:

1. 可靠性不高

存在自增ID回溯的问题，这个问题直到最新版本的MysQL 8.0才修复。

2. 安全性不高

对外暴露的接口可以非常容易猜测对应的信息。比如:/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。

3. 性能差

自增ID的性能较差，需要在数据库服务器端生成。

4. 交互多

业务还需要额外执行一次类似last_insert_id()的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

5. 局部唯一性

最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

推荐的主键设计

非核心业务︰对应表的主键自增ID，如告警、日志、监控等信息。

核心业务︰主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。

这里推荐最简单的一种主键设计:UUID。

UUID的特点:

全局唯一，占用36字节，数据无序，插入性能差。

MySQL数据库的UUID组成如下所示:

UUID =时间+UUID版本（16字节)–时钟序列（4字节)-MAC地址（12字节)

这里我们举个列子:SELECT UUID() FROM DUAL;

为什么UUID是全局唯一的?

在UUID中时间部分占用60位，存储的类似TIMESTAMP的时间戳，但表示的是从1582-10-15 00: 00:00:00到现在的100ns的计数。可以看到UUID存储的时间精度比TIMESTAMPE更高，时间维度发生重复的概率降低到1/100ns。时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。

为什么UUID占用36个字节?

UUID根据字符串进行存储，设计时还带有无用" -"字符串，因此总共需要36个字节。

为什么UUID是随机无序的呢?

因为UUID的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序。

改造UUID

若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

通过函数uuid_to_bin(@uuid,true)将UUID转化为有序UUID了。全局唯一+单调递增，这不就是我们想要的主键!

mysql> set @uuid=UUID();
Query OK, 0 rows affected (0.00 sec)

mysql> select @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,true);
+--------------------------------------+----------------------------------------+--------------------------------------------------+
| @uuid                                | uuid_to_bin(@uuid)                     | uuid_to_bin(@uuid,true)                          |
+--------------------------------------+----------------------------------------+--------------------------------------------------+
| 34a148d5-119c-11ed-88f3-c85b7638a3df | 0x34A148D5119C11ED88F3C85B7638A3DF     | 0x11ED119C34A148D588F3C85B7638A3DF               |
+--------------------------------------+----------------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

10.数据库的设计规范

10.1 键和相关属性概念

范式的定义会使用到主键和候选键，数据库中的键(Key)由一个或者多个属性组成。数据表中常用的几种键和属性的定义:

• 超键:能唯一标识元组的属性集叫做超键。
• 候选键:如果超键不包括多余的属性，那么这个超键就是候选键。
• 主键:用户可以从候选键中选择一个作为主键。
• 外键∶如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键。
• 主属性:包含在任一候选键中的属性称为主属性。
• 非主属性:与主属性相对，指的是不包含在任何一个候选键中的属性。

通常，我们也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分。

10.2 范式

**在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。**可以理解为，一张数据表的设计结构需要满足的某种设计标准的 级别 。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF）和第五范式(5NF，又称完美范式)。

第一范式(1st NF)

第一范式主要是确保数据表中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。

我们在设计某个字段的时候，对于字段X来说，不能把字段X拆分成字段X-1和字段X-2。事实上任何的DBMS都会满足第一范式的要求，不会将字段进行拆分。

第二范式(2nd NF)

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。如果知道主键的所有属性的值，就可以检索到任何元组(行)的任何属性的任何值。(要求中的主键，其实可以拓展替换为候选键)。

举例一:

成绩表(学号，课程号，成绩）关系中，(学号，课程号）可以决定成绩，但是学号不能决定成绩，课程号也不能决定成绩，所以“(学号，课程号)→成绩”就是完全依赖关系。

举例二：

比赛表 player_game，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键(或主键）来决定如下的关系:

(球员编号，比赛编号)→(姓名，年龄，比赛时间，比赛场地，得分)

但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系:

(球员编号)→(姓名，年龄)
--
(比赛编号)→(比赛时间，比赛场地)

对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢?

1. 数据冗余∶如果一个球员可以参加m场比赛，那么球员的姓名和年龄就重复了m-1次。一个比赛也可能会有n个球员参加，比赛的时间和地点就重复了n-1次。
2. 插入异常:如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
3. 删除异常∶如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
4. 更新异常∶如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。

表名	属性（字段）
球员 player 表	球员编号、姓名和年龄等属性
比赛 game 表	比赛编号、比赛时间和比赛场地等属性
球员比赛关系 player_game 表	球员编号、比赛编号和得分等属性

这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。

1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只表达一个意思。

第三范式(3rd NF)

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。（即，不能存在非主属性A依赖于非主属性B，非主属性B依赖于主键c的情况，即存在“A→B→"的决定关系）通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

关于数据表的设计，有三个范式要遵循。

（1）第一范式(1NF)，确保每列保持原子性

数据库的每一列都是不可分割的原子数据项，不可再分的最小数据单元，而不能是集合、数组、记录等非原子数据项。

（2）第二范式(2NF)，确保每列都和主键完全依赖

尤其在复合主键的情况下，非主键部分不应该依赖于部分主键。

（3）第三范式(3NF）确保每列都和主键列直接相关，而不是间接相关

**范式的优点:**数据的标准化有助于消除数据库中的数据冗余，第三范式(3NF）通常被认为在性能扩展性和数据完整性方面达到了最好的平衡。

范式的缺点∶范式的使用，可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。

范式只是提出了设计的标准，实际上设计数据表时，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则，通过增加少量的冗余或重复的数据来提高数据库的读性能，减少关联查询，join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。

BCNF(巴斯范式)

人们在3NF的基础上进行了改进，提出了巴斯范式(BCNF)，也叫做巴斯-科德范式(Boyce-Codd NormalForm)。BCNF被认为没有新的设计规范加入，只是对第三范式中设计规范要求更强，使得数据库冗余度更小。所以，称为是修正的第三范式，或扩充的第三范式，BCNF不被称为第四范式。

若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BC范式。

一般来说，一个数据库设计符合3NF或BCNF就可以了。

第四范式

多值依赖的概念:

• 多值依赖：即属性之间的—对多关系，记为K→→A。
• 函数依赖：事实上是单值依赖，所以不能表达属性值之间的一对多关系。
• 平凡的多值依赖：全集U=K+A，一个K可以对应于多个A，即K→→A。此时整个表就是一组一对多关系。
• 非平凡的多值依赖：全集U=K+A+B，一个K可以对应于多个A，也可以对应于多个B，A与B互相独立，即K→→A，K→→B。整个表有多组一对多关系，且有:“一"部分是相同的属性集合，“多"部分是互相独立的属性集合。

第四范式即在满足巴斯-科德范式(BCNF)的基础上，消除非平凡且非函数依赖的多值依赖(即把同一表内的多对多关系删除)。

第五范式、域键范式

除了第四范式外，我们还有更高级的第五范式(又称完美范式)和域键范式(DKNF)。

在满足第四范式(4NF)的基础上，消除不是由候选键所蕴含的连接依赖。如果关系模式R中的每一个连接依赖均由R的候选键所隐含，则称此关系模式符合第五范式。

数依赖是多值依赖的一种特殊的情况，而多值依赖实际上是连接依赖的一种特殊情况。但连接依赖不像函数依赖和多值依赖可以由语义直接导出，而是在关系连接运算时才反映出来。存在连接依赖的关系模式仍可能遇到数据冗余及插入、修改、删除异常等问题。

第五范式处理的是无损连接问题，这个范式基本没有实际意义，因为无损连接很少出现，而且难以察觉。而域键范式试图定义一个终极范式，该范式考虑所有的依赖和约束类型，但是实用价值也是最小的，只存在理论研究中。

10.3 反范式化

有的时候不能简单按照规范要求设计数据表，因为有的数据看似冗余，其实对业务来说十分重要。这个时候，我们就要遵循业务优先的原则，首先满足业务需求，再尽量减少冗余。

如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MysQL的三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式优化也是一种优化思路。此时，可以通过在数据表中增加冗余字段来提高数据库的读性能。

规范化vs 性能

1. 为满足某种商业目标,数据库性能比规范化数据库更重要
2. 在数据规范化的同时,要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4. 通过在给定的表中插入计算列，以方便查询

反范式的新问题

反范式可以通过空间换时间，提升查询的效率，但是反范式也会带来一些新问题:

• 存储空间变大了
• 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则数据不一致
• 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源。
• 在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

反范式的适用场景

当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采取反范式的优化。

1. 增加冗余字段的建议

增加冗余字段一定要符合如下两个条件。只有满足这两个条件，才可以考虑增加冗余字段。

1）这个冗余字段不需要经常进行修改;
2）这个冗余字段查询的时候不可或缺。

2. 历史快照、历史数据的需要

在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每次发生的订单收货信息都属于历史快照，需要进行保存，但用户可以随时修改自己的信息，这时保存这些冗余信息是非常有必要的。

反范式优化也常用在数据仓库的设计中，因为数据仓库通常存储历史数据，对增删改的实时性要求不强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。

我简单总结下数据仓库和数据库在使用上的区别:

1. 数据库设计的目的在于捕获数据，而数据仓库设计的目的在于分析数据;

2. 数据库对数据的增删改实时性要求强，需要存储在线的用户数据，而数据仓库存储的一般是历史数据;

3. 数据库设计需要尽量避免冗余，但为了提高查询效率也允许一定的冗余度，而数据仓库在设计上更偏向采用反范式设计。

10.4 ER模型

数据库设计是牵一发而动全身的。那有没有什么办法提前看到数据库的全貌呢?比如需要哪些数据表、数据表中应该有哪些字段，数据表与数据表之间有什么关系、通过什么字段进行连接，等等。这样我们才能进行整体的梳理和设计。

其实，ER模型就是一个这样的工具。ER模型也叫作实体关系模型，是用来描述现实生活中客观存在的事物、事物的属性，以及事物之间关系的一种数据模型。在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮助我们理清业务逻辑，从而设计出优秀的数据库。

ER模型三要素

ER模型中有3三个要素,分别是实体、属性和关系。

实体，可以看做是数据对象,往往对应于现实生活中的真实存在的个体。在ER模型中，用矩形来表示。实体分为两类，分别是强实体和弱实体。强实体是指不依赖于其他实体的实体;弱实体是指对另-一个实体有很强的依赖关系的实体。

属性，则是指实体的特性。比如超市的地址、联系电话、员工数等。在ER模型中用椭圆形来表示。

关系，则是指实体之间的联系。比如超市把商品卖给顾客,就是一种超市与顾客之间的联系。 在ER模型中用菱形来表示。

注意:实体和属性不容易区分。这里提供一个原则: 我们要从系统整体的角度出发去看，可以独立存在的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。

关系的类型

在ER模型的3个要素中，关系又可以分为3种类型，分别是一对一、一对多、多对多。

一对一:指实体之间的关系是一对应的， 比如个人与身份证信息之间的关系就是一对一 的关系。一个人只能有一个身份证信息，一个身份证信息也只属于一个人。

一对多: 指一边的实体通过关系,可以对应多个另外一边的实体。相反,另外-边的实体通过这个关系，则只能对应唯一的一 边的实体。比如说,我们新建一个班级表， 而每个班级都有多个学生,每个学生则对应一个班级,班级对学生就是一对多的关系。

多对多:指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中,供货商与超市之间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购商品。再比如一个选课表，有许多科目，每个科目有很多学生选,而每个学生又可以选择多个科目，这就是多对多的关系。

10.5 数据表的设计原则

综合以上内容，总结出数据表设计的一般原则:“三少一多”

1.数据表的个数越少越好

RDBMS的核心在于对实体和联系的定义，也就是E-R图(Entity Relationship Diagram)，数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。

⒉数据表中的字段个数越少越好

字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。

3.数据表中联合主键的字段个数越少越好

设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式(也就是用多个字段来定义一个主键)。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

4.使用主键和外键越多越好
数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

“三少一多"原则的核心就是简单可复用。简单指的是用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。

11.事务

**事务:**一组逻辑操作单元，使数据从一种状态变换到另一种状态。

事务处理的原则:保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交（commit），那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改，整个事务回滚（rollback）到最初状态。

11.1 事务的ACID特性

原子性(atomicity) :

原子性是指事务是一个不可分割的工作单位,要么全部提交,要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间的状态。如果无法保证原子性会怎么样?就会出现数据不一致的情形，A账户减去100元，而B账户增加100元操作失败，系统将无故丢失100元。

一致性(consistency) :

根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。那什么是合法的数据状态呢?满足预定的约束的状态就叫做合法的状态。通俗一点，这状态是由你自己来定义的(比如满足现实世界中的约束)。满足这个状态，数据就是一致的,不满足这个状态,数据就是不一致的!如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务,返回到事务操作之前的状态。

• 举例1: A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的,为什么呢?因为你定义了一一个状态，余额这列必须>= 0。
• 举例2: A账户200元，转账50元给B账户, A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢?因为你定义了一一个状态，要求A+B的总余额必须不变。
• 举例3:在数据表中我们将姓名字段设置为唯一性约束 ,这时当事务进行提交或者事务发生回滚的时候，如果数据表中的姓名不唯一,就破坏了事务的一致性要求。

隔离型(isolation) :

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(durability) :

持久性是指一个事务一旦被提交, 它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候,首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

总结

ACID是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。

数据库事务，其实就是数据库设计者为了方便起见，把需要保证原子性、隔离性、一致性 和持久性的一个或多个数据库操作称为一个事务。

11.2 事务的状态

我们现在知道事务是一个抽象的概念，它其实对应着一个或多 个数据库操作，MySQL根据这些操作所执行的不同阶段把事务大致划分成几个状态:

• 活动的(active)

事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。

• 部分提交的(partially committed)

当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时,我们就说该事务处在部分提交的状态。

• 失败的(failed)

当事务处在活动的或者部分提交的状态时，可能遇到了某些错误(数据库自身的错误、操作系统错误或者直接断电等)而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。

• 中止的(aborted)

如果事务执行了部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

• 提交的(committed)

当一个处在部分提交的状态的事务将修改过的数据都同步到店盘上之后，我们就可以说该事务处在了提交的状态。

基本的状态转换图

图中可见，只有当事务处于提交的或者中止的状态时，-个事务的生命周期才算是结束了。对于已经提交的事务来说，该事务对数据库所做的修改将永久生效，对于处于中止状态的事务,该事务对数据库所做的所有修改都会被回滚到没执行该事务之前的状态。

11.3 使用事务的分类

使用事务有两种分类，分别为显式事务和隐式事务

显式事务

步骤1： START TRANSACTION或者BEGIN, 作用是显式开启一个事务。

START TRANSACTION 语句相较于BEGIN特别之处在于，后边能跟随几个修饰符:

• READ ONLY :标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
• READ WRITE :标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
• WITH CONSISTENT SNAPSHOT :启动一致性读。

步骤2：一列事务中的操作(主要是DML,不含DDL)

步骤3：提交事务或中止事务(即回滚事务)

-- 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;
-- 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK;
-- 将事务回滚到某个保存点。
mysql> ROLLBACK TO [SAVEPOINT]

其中关于参数SAVEPOINT相关操作有:

-- 在事务中创建保存点，方便后续针对保存点进行回滚。一- 个事务中可以存在多个保存点。
SAVEPOINT 保存点名称;
-- 删除某个保存点。
RELEASE SAVEPOINT 保存点名称;

隐式事务

默认情况下，如果我们不显式的使用START TRANSACTION 或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。也就是说，不以START TRANSACTION 或者BEGIN语句显式的开启一个事务，DML语句就相当于放到两个独立的事务中去执行。

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)

当然，如果我们想关闭这种自动提交的功能，可以使用下边两种方法之一:

1. 显式的的使用START TRANSACTION 或者BEGIN语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。

2. 把系统变量autocommit的值设置为0FF，就像这样:

SET autocommit = OFF ;
-- 或者
SET autocommit = 0;

这样的话，我们写入的多条语句就算是属于同一个事务了，直到我们显式的写出COMMIT语句来把这个事务提交掉，或者显式的写出ROLL BACK语句来把这个事务回滚掉。

隐式提交数据的情况

1. 数据定义语言(Data definition language, 缩写为: DDL)

数据库对象，指的就是数据库、表、视图、存储过程等结构。当我们使用CREATE、ALTER、 DROP 等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事务。

2. 隐式使用或修改mysq|数据库中的表

当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、 RENAME USER、REVOKE 、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。

3. 事务控制或关于锁定的语句

①当我们在一个事务还没提交或者回滚时就又使用START TRANSACTION 或者`BEGIN语句开启了另一个事务时，会隐式的提交上一个事务。即：

BEGIN;
SELECT ..... -- 事务中的一条语句
UPDATE ..... --事务中的一条语句
......       --事务中的其它语句
-- 这里未COMMIT就开启下一个BEGIN
BEGIN; -- 此语句会隐式的提交前边语句所属于的事务

②当前的autocommit系统变量的值为0FF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。

③使用LOCK TABLES、 UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。

4. 加载数据的语句

使用LOAD DATA 语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。

5. 关于MySQL复制的一些语句

使用START SLAVE、 STOP SLAVE、 RESET SLAVE 、CHANGE MASTER TO等语句时会隐式的提交前边语句所属的事务。

6. 其它的一些语句

使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE 、0PTIMIZE TABLE、 REPAIR TABLE、 RESET 等语句也会隐式的提交前边语句所属的事务。

链式事务

MySQL中completion_ type 参数的作用，实际上这个参数有3种可能:

1. completion=0 ，这是默认情况。当我们执行COMMIT的时候会提交事务，在执行下一个事务时，还需要使用START TRANSACTION 或者BEGIN来开启。
2. completion=1 ，这种情况下，当我们提交事务后，相当于执行了COMMIT AND CHAIN, 也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务。
3. completion=2 ，这种情况下COMTIT=COMMIT AND RELEASE ，也就是当我们提交后，会自动与服务器断开连接。

可以通过SET @@completion_type=1来设置参数。

当我们设置autocommit=0时，不论是否采用START TRANSACTION或者BEGIN的方式来开启事务，都需要用COMMIT进行提交，让事务生效，使用ROLLBACK对事务进行回滚。

当我们设置autocommit=1时，每条SQL语句都会自动进行提交。不过这时，如果你采用START TRANSACTION或者BEGIN的方式来显式地开启事务，那么这个事务只有在COMMIT时才会生效，在ROLLBACK时才会回滚。

11.4 事务隔离级别

MySQL是一个客户端/服务器架构的软件,对于同一个服务器来说，可以有若千个客户端与之连接,每个客户端与服务器连接上之后，就可以称为一个会话**( Session)。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。事务有隔离性的特性,理论上在某个事务对某个数据进行访问时**,其他事务应该进行排队，当该事务提交之后,其他事务才可以继续访问这个数据。但是这样对性能影响太大，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，那就看二者如何权衡取舍了。

事务并发问题

1.脏写(Dirty Write )

对于两个事务Session A、Session B,如果事务SessionA修改了另一个未提交事务Session B修改过的数据，那就意味着发生了脏写，示意图如下:

Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为’李四’，然后Session A中的事务接着又把这条studentno列为1的记录的name列更新为张三。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。这时Session A中的事务就没有效果了，明明把数据更新了，最后也提交事务了，最后看到的数据什么变化也没有。这里大家对事务的隔离级比较了解的话，会发现默认隔离级别下，上面SessionA中的更新语句会处于 等待状态,这里只是跟大家说明一下会出现这样现象。

2.脏读(Dirty Read )

对于两个事务SessionA、Session B, SessionA 读取了已经被SessionB更新但还没有被提交的字段。之后若SessionB回滚，Session A读取的内容就是临时且无效的。

Session A和Session B各开启了一个事务， Session B中的事务先将studentno列为1的记录的name列更新为’张三’，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为张三’，而Session B中的事务稍后进行了回滚,那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

3.不可重复读( Non-Repeatable Read )

对于两个事务SessionA、Session B，SessionA 读取了一个字段，然后SessionB更新了该字段。之后Session A再次读取同一个字段，值就不同 了。那就意味着发生了不可重复读。

我们在Session B中提交了几个隐式事务(注意是隐式事务, 意味着语句结束事务就提交了)， 这些事务都修改了studentno列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。

4.幻读( Phantom )

对于两个事务Session A、Session B, Session A从一个表中读取了一个字段，然后Session B在该表中插入了一些新的行。之后， 如果SessionA再次读取同一个表,就会多出几行。那就意味着发生了幻读。

Session A中的事务先根据条件studentno > 0这个条件查询表student,得到了name列值为张三’的记录;之后Session B中提交了一个隐式事务，该事务向表student中插入了一条新记录;之后Session A中的事务再根据相同的条件studentno > 0查询表student,得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。我们把新插入的那些记录称之为幻影记录。

注意1:

有的同学会有疑问，那如果SessionB中删除了一些符合 studentno > 0 的记录而不是插入新记录,那SessionA之后再根据studentno > 0的条件读取的记录变少了，这种现象算不算幻读呢?这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时,后读取时读到了之前没有读到的记录。

注意2:

那对于先前已经读到的记录，之后又读取不到这种情况，算啥呢?这相当于对每一条记录都发生了 不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录。| I

11.5 SQL中四种隔离级别

上面介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题有轻重缓急之分，我们给这些问题按照严重性来排一下序:

脏写>脏读>不可重复读>幻读

我们愿意舍弃一部分隔离性来换取一 部分性能在这里就体现在:设立一些隔离级别，隔离级别越低,并发问题发生的就越多。SQL标准 中设立了4个隔离级别:
●READ UNCOMMITTED: 读未提交,在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。

●READ COMMITTED: 读已提交,它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别(但不是MySQL 默认的)。可以避免脏读,但不可重复读、幻读问题仍然存在。

●REPEATABLE READ: 可重复读,事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读,但幻读问题仍然存在。这是MySQL的默认隔离级别。

●SERIALIZABLE :可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务

SQL标准中规定,针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下:

注意：不论是哪种隔离级别，都不允许脏写的情况发生。

查看隔离级别

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)
-- 或者
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

修改事务的隔离级别

SET [GLOBAL |SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
#其中，隔离级别格式:
> READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE

或者：（推荐使用下面这种）

SET [GLOBAL |SESSION] TRANSACTION ISOLATION = '隔离级别'
#其中，隔离级别格式:
> READ-UNCOMMITTED
> READ-COMMITTED
> REPEATABLE-READ
> SERIALIZABLE

12. MySQL事务日志

事务有4种特性:原子性、一致性、 隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢?

●事务的隔离性由锁机制实现。

●而事务的原子性、一 致性和持久性由事务的redo日志和undo日志来保证。

• REDO LOG称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
• UNDO LOG称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

有的DBA或许会认为UNDO是REDO的逆过程，其实不然。REDO 和UNDO都可以视为是一种恢复操作，但是:

●redo log: 是存储引擎层(innodb)生成的日志，记录的是"物理级别"上的页修改操作，比如页号xxx、 偏移量yyy写入了’zz’数据。主要为了保证数据的可靠性;

●undo log:是存储引擎层(innodb)生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了INSERT语句操作，那么undo log就记录一条与之相反的DELETE操作。主要用于事务的回滚(undo log记录的是每个修改操作的逆操作)和一致性非锁定读(undo log回滚行记录到某种特定的版本–MVCC,即多版本并发控制。

12.1 redo日志

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool 之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘( checkPoint机制)，通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

为什么需要REDO日志

一方面， 缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟, checkpoint机制可以保证数据的最终落盘，然而由于checkpoint并不是每次变更的时候就触发的，而是master线程隔一 段时间去处理的。所以最坏的情况就是事务提交后，刚写完缓冲池，数据库宕机了,那么这段数据就是丢失的,无法恢复。

另一方面，事务包含持久性的特性,就是说对于-一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。

那么如何保证这个持久性呢?一个 简单的做法:在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘,但是这个简单粗暴的做法有些问题:

• 修改量与刷新磁盘工作量严重不成比例

有时候我们仅仅修改了某个页面中的一个字节，但是我们知道在InnoDB中是以页为单位来进行磁盘l0的，也就是说我们在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘 ，我们又知道一个页面默认是16KB大小，只修改一一个字节就要刷新16KB的数据到磁盘上显然是太小题大做了。

• 随机IO刷新较慢

一个事务可能包含很多语句，即使是一条语句也可能修改许多页面， 假如该事务修改的这些页面可能并不相邻，这就意味着在将某个事务修改的Buffer Pool中的页面刷新到磁盘时，需要进行很多的随机IO，随机IO比顺序IO要慢，尤其对于传统的机械硬盘来说。

另一个解决的思路:我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效,即使后来系统崩溃,在重启后也能把这种修改恢复出来。所以我们其实没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西记录一下就好。比如，某个事务将系统表空间中第10号页面中偏移量为100处的那个字节的值1改成2。我们只需要记录一下:将第0号表空间的10号页面的偏移量为100处的值更新为2。

InnoDB引擎的事务采用了WAL技术( Write- Ahead Logging) ，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷到磁盘的时候，可以通过redo log来恢复,保证ACID中的D, 这就是redo log的作用。

REDO日志的好处、特点

1.好处

●redo日志降低了刷盘频率

●redo 日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

2.特点

●redo日志是顺序写入磁盘的

在执行事务的过程中，每执行一条语句， 就可能产生若干条redo日志这些日志是按照产生的顺序写入磁盘的,也就是使用顺序I0,效率比随机10快。

●事务执行过程中，redo log不断记录

redo log跟bin lop的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录,而bin log不会记录，直到这个事务提交，才会一次写入到bin log文件中。

InnoDB刷盘策略

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存(pagecache)中去(这是现代操作系统为了提高文件写入效率做的一个优化)，真正的写入会交给系统自己来决定(比如page cache足够大了)。那么对于InnoDB来说就存在一个问题， 如果交给系统来同步,同样如果系统宕机，那么数据也丢失了(虽然整个系统宕机的概率还是比较小的)。

针对这种情况，InnoDB给出 innodb_ flush. log_ at_ trx_ commit 参数，该参数控制commit提交事务时，如何将redo log buffer中的日志刷新到redo log file中。它支持三种策略:

●设置为0 :表示每次事务提交时不进行刷盘操作。 (系统默认master thread每隔1s进行一 次重做日志的同步)

●设置为1 :表示每次事务提交时都将进行同步，刷盘操作**(默认值)**

●设置为2 :表示每次事务提交时都只把redo log buffer内容写入page cache,不进行同步。由os自己决定什么时候同步到磁盘文件。

总的形式

12.2 undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个undo 1og。

如何理解Undo日志

事务需要保证原子性，也就是事务中的操作要么全部完成,要么什么也不做。但有时候事务执行到一半会出现一些情况，比如:

●情况一:事务执行过程中可能遇到各种错误， 比如服务器本身的错误，操作系统错误，甚至是突然 断电导致的错误。

●情况二:程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前事务的执行。

以上情况出现，我们需要把数据改回原先的样子，这个过程称之为回滚，这样就可以造成一个假象:这个事务看起来什么都没做,所以符合原子性要求。每当我们要对一条记录做改动时(这里的改动可以指INSERT、DELETE、 UPDATE) ，都需要"留一手”——把回滚时所需的东西记下来。

Undo日志的作用

●作用1:回滚数据

用户对undo日志可能有误解: undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。

这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此,不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

●作用2: MVCC
undo的另一个作用是MVCC,即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo log是如何回滚的

以上面的例子来说，假设执行rollback，那么对应的流程应该是这样:

1.通过undo no=3的日志把id=2的数据删除

2.通过undo no=2的日志把id=1的数据的deletemark还原成0

3.通过undo no=1的日志把id=1的数据的name还原成Tom

4.通过undo no=0的日志把id=1的数据删除

undo log的删除

• 针对于insert undo log

因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。

• 针对于update undo log

该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

补充：purge线程两个主要作用是:清理undo页和清除page里面带有Delete_Bit标识的数据行。在InnoDB中，事务中的Delete操作实际上并不是真正的删除掉数据行，而是一种Delete Mark操作，在记录上标识Delete_Bit，而不删除记录。是一种"假删除";只是做了个标记，真正的删除工作需要后台purge线程去完成。

总的形式

undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。

redo log是物理日志，记录的是数据页的物理变化,undo log不是redo log的逆过程。

13. 锁

事务的隔离性由锁决定

锁是计算机协调多个进程或线程并发访问某一资源的机制。在程序开发中会存在多线程同步的问题，当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据（比如订单、金额等)，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样很重要。

在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MysQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

13.1 并发问题的解决方案

怎么解决脏读、不可重复读、幻读这些问题呢?其实有两种可选的解决方案:

• 方案一:读操作利用多版本并发控制（MVCC)，写操作进行加锁。

所谓的MVCC，就是生成一个 ReadView，通过ReadView找到符合条件的记录版本（历史版本由undo日志构建)。查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。

普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。

• 在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象;
• 在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。

• 方案二:读、写操作都采用加锁的方式。

如果我们的一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本。比如，在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。

脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录，如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录。采用加锁的方式解决幻读问题就有一些麻烦，因为当前事务在第一次读取记录时幻影记录并不存在，所以读取的时候加锁就有点尴尬（因为你并不知道给谁加锁）

13.2 锁的分类

锁的分类如下:

13.3 读锁、写锁

从数据操作的类型划分:读锁、写锁

对于数据库中并发事务的读-读情况并不会引起什么问题。对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以MySQL实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁(Shared Lock，SLock)和排他锁(Exclusive Lock，XLock),也叫读锁(readlock)和写锁(write lock)。

• 读锁︰也称为共享锁、英文用S表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
• 写锁:也称为排他锁、英文用X表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

需要注意的是对于InnoDB引擎来说，读锁和写锁可以加在表上，也可以加在行上。

锁定读

在采用加锁方式解决脏读、不可重复读、幻读这些问题时，读取一条记录时需要获取该记录的S锁，其实是不严谨的，有时候需要在读取记录时就获取记录的X锁，来禁止别的事务读写该记录，为此MysQL提出了两种比较特殊的SELECT语句格式:

• 对读取的记录加S锁:

# 对读的记录加共享锁
SELECT ... LOCK IN SHARE MODE;
#或
SELECT ... FOR SHARE;#(8.日新增语法)

在普通的SELECT语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S锁，但是不能获取这些记录的X锁。如果别的事务想要获取这些记录的X锁，那么它们会阻塞直到当前事务提交之后将这些记录上的S锁释放掉。

• 对读取的记录加X锁:

SELECT ... FOR UPDATE;

在普通的SELECT语句后边加FOR UPDATE，如果当前事务执行了该语句，那么它会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁，也不允许获取这些记录的X锁。如果别的事务想要获取这些记录的S锁或者X锁，那么它们会阻塞直到当前事务提交之后将这些记录上的X锁释放掉。

MySQL8.0新特性:

在5.7及之前的版本，SELECT …FOR UPDATE，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。在8.0版本中，SELECT....OR UPDATE，SELECT...FOR SHARE 添加NOWAIT 、SKIP LOCKED语法，跳过锁等待，或者跳过锁定。

SELECT ... FOR SHARE NOWAIT;
#或者
SELECT ... FOR SHARE SKIP LOCKED;

通过添加NOWAIT、SKIP LOCKED语法，能够立即返回。如果查询的行已经加锁:

• 那么NOWAIT会立即报错返回
• 而SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行。

写操作

平常所用到的写操作无非是DELETE、UPDATE、INSERT 这三种:

1. DELETE:

对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，再执行delete mark 操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。

2. UPDATE:在对一条记录做UPDATE操作时分为三种情况:

• 情况1∶未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化。

则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。

• 情况2:未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生变化。

则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由工NSERT 操作提供的隐式锁进行保护。

• 情况3∶修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照 DELETE和INSERT的规则进行了。

3. INSERT:

一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。

13.4 表级锁

从数据操作的粒度划分:表级锁、页级锁、行锁

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取、检查、释放锁等动作)。因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度〈Lock granularity)”的概念。

对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细;其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁。

表锁（Table Lock)

该锁会锁定整张表，它是MysQL中最基本的锁策略，并不依赖于存储引擎（不管你是MySQL的什么存储引擎，对于表锁的策略都是一样的)，并且表锁是开销最小的策略（因为粒度比较大)。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

1. 表级别的S锁、X锁

一般情况下，不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下，比方说崩溃恢复过程中用到。比如，在系统变量autocommit=0，innodb_table_locks = 1时，手动获取InnoDB存储引擎提供的表t的S锁或者x锁可以这么写:

• LOCK TABLES 表名 READ : InnoDB存储引擎会对表t加表级别的S锁。

• LOCK TABLES 表名 WRITE: InnoDB存储引擎会对表t加表级别的X锁。

2. 意向锁(intention lock)

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

1、意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁)的锁并存。

2、意向锁是一种不与行级锁冲突表级锁，这一点非常重要。

3、表明“某个事务正在某些行持有了锁或该事务准备去持有锁”意向锁分为两种:

• 意向共享锁(intention shared lock, lS)︰事务有意向对表中的某些行加共享锁（S锁)

-- 事务要获取某些行的S 锁，必须先获得表的IS 锁。
SELECT column FROM table ... Lock IN SHARE MODE;

• 意向排他锁(（intention exclusive lock, lX)︰事务有意向对表中的某些行加排他锁（X锁)

-- 事务要获取某些行的X锁，必须先获得表的 IX锁。
SELECT column FROM table ... FOR UPDATE;

即:意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前lnooDB会先获取该数据行所在数据表的对应意向锁。

在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

• 如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁。
• 如果事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁。

13.5 InnoDB行级锁

行锁(Row Lock)也称为记录锁，顾名思义，就是锁住某一行(某条记录row)。需要的注意的是，MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。

优点:锁定力度小，发生锁冲突概率低，可以实现的并发度高。

缺点:对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

InnoDB与MylISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。

记录锁

记录锁是有S锁和X锁之分的，称之为S型记录锁和X型记录锁。

• 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁;
• 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁,也不可以继续获取X型记录锁。

间隙锁（Gap Locks）

临键锁(Next-Key Locks)

插入意向锁(Insert lntention Locks)

页锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

13.6 从对待锁的态度划分:乐观锁、悲观锁

从对待锁的态度来看锁的话，可以将锁分成乐观锁和悲观锁，从名字中也可以看出这两种锁是两种看待数据并发的思维方式。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的设计思想。

悲观锁(Pessimistic Locking)

悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程)。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

乐观锁(Optimistic Locking)

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者CAS机制实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

在Java中java.util.concurrent.atomic包下的原子变量类就是使用了乐观锁的一种实现方式:CAS实现的。

1.乐观锁的版本号机制

在表中设计一个版本字段version，第一次读的时候，会获取version字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。

这种方式类似我们熟悉的SVN、CVS版本管理系统，当我们修改了代码进行提交时，首先会检查当前版本号与服务器上的版本号是否一致，如果一致就可以直接提交，如果不一致就需要更新服务器上的最新代码然后再进行提交。

2.乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳(版本号或者时间戳)，从而证明当前拿到的数据是否最新。

两种锁适用场景

13.7 其他锁

全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞:数据更新语句（数据的增删改)、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是:做全库逻辑备份。

全局锁的命令:

Flush tables with read lock

14. 多版本并发控制

14.1 MVCC

MVCC (Multiversion Concurrency Control)，多版本并发控制。顾名思义，MVCC是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

MVCC没有正式的标准，在不同的DBMS中MVCC的实现方式可能是不同的，也不是普遍使用的(大家可以参考相关的DBMS文档)。这里讲解InnoDB中 MVCC 的实现机制(MySQL其它的存储引擎并不支持它)。

14.2快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，而这个读指的就是快照读,而非当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的SELECT都属于快照读，即不加锁的非阻塞读;比如这样:

SELECT * FROM player WHERE ...

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下避免了加锁操作，降低了开销。

既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

当前读

当前读读取的是记录的最新版本〈最新数据，而不是历史版本的数据)，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的SELECT，或者对数据进行增删改都会进行当前读。比如:

SELECT * FROM student LOCK IN SHARE MODE;# 共享锁

SELECT *FROM student FOR UPDATE;# 排他锁

INSERT INTO student values ... # 排他锁

DELETE FROM student WHERE ...#排他锁

UPDATE student SET ...# 排他锁

14.3 MVCC实现原理之ReadView

MVCC的实现依赖于:隐藏字段、Undo Log、Read View。

什么是ReadView

在MCc机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了,它帮我们解决了行的可见性问题。

ReadView就是事务在使用MVCc机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID(“活跃"”指的就是，启动了但还没提交）。

设计思路

ReadView规则

MVCC整体操作流程

14.4 总结

15. 其他数据库日志

15.1 日志类型

日志的弊端

15.2 通用查询日志

通用查询日志用来记录用户的所有操作，包括启动和关闭MysQL服务、所有用户的连接开始时间和截止时间、发给MysQL数据库服务器的所有SQL指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

查看当前状态

mysql> SHOW VARIABLES LIKE '%general%';
+------------------+--------------------------------------------------------+
| Variable_name    | Value                                                  |
+------------------+--------------------------------------------------------+
| general_log      | OFF                                                    |
| general_log_file | D:\mysql8\mysql-8.0.19-winx64\data\USER-20190629LA.log |
+------------------+--------------------------------------------------------+
2 rows in set, 1 warning (0.07 sec)

启动日志

查看日志

停止日志

删除/刷新日志

15.3 错误日志

错误日志记录了MysQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。

通过错误日志可以查系统的运行状态，便于即时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。

启动日志

查看日志

mysql> SHOW VARIABLES LIKE 'log_err%';
+----------------------------+--------------------------------------------------------+
| Variable_name              | Value                                                  |
+----------------------------+--------------------------------------------------------+
| log_error                  | D:\mysql8\mysql-8.0.19-winx64\data\USER-20190629LA.err |
| log_error_services         | log_filter_internal; log_sink_internal                 |
| log_error_suppression_list |                                                        |
| log_error_verbosity        | 2                                                      |
+----------------------------+--------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

删除/刷新日志

15.4 二进制日志

查看二进制文件

mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------------+
| Variable_name                   | Value                                           |
+---------------------------------+-------------------------------------------------+
| log_bin                         | ON                                              |
| log_bin_basename                | D:\mysql8\mysql-8.0.19-winx64\data\binlog       |
| log_bin_index                   | D:\mysql8\mysql-8.0.19-winx64\data\binlog.index |
| log_bin_trust_function_creators | OFF                                             |
| log_bin_use_v1_row_events       | OFF                                             |
| sql_log_bin                     | ON                                              |
+---------------------------------+-------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

临时设置文件

查看日志

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000016 |      5638 | No        |
| binlog.000017 |       178 | No        |
| binlog.000018 |    376261 | No        |
| binlog.000019 |    209410 | No        |
| binlog.000020 |    103024 | No        |
| binlog.000021 |      1146 | No        |
| binlog.000022 | 139037539 | No        |
| binlog.000023 |       155 | No        |
+---------------+-----------+-----------+
8 rows in set (0.02 sec)

因为是二进制日志文件，因此我们不能直接查看，可通过命令

mysqlbinlog -v "二进制文件具体路径"

使用日志恢复数据

删除二进制日志

述](https://img-blog.csdnimg.cn/41b0e48db2d5433f9ec8576e19ab4e84.png)

删除/刷新日志

15.3 错误日志

错误日志记录了MysQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。

通过错误日志可以查系统的运行状态，便于即时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。

启动日志

查看日志

mysql> SHOW VARIABLES LIKE 'log_err%';
+----------------------------+--------------------------------------------------------+
| Variable_name              | Value                                                  |
+----------------------------+--------------------------------------------------------+
| log_error                  | D:\mysql8\mysql-8.0.19-winx64\data\USER-20190629LA.err |
| log_error_services         | log_filter_internal; log_sink_internal                 |
| log_error_suppression_list |                                                        |
| log_error_verbosity        | 2                                                      |
+----------------------------+--------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

删除/刷新日志

15.4 二进制日志

查看二进制文件

mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------------+
| Variable_name                   | Value                                           |
+---------------------------------+-------------------------------------------------+
| log_bin                         | ON                                              |
| log_bin_basename                | D:\mysql8\mysql-8.0.19-winx64\data\binlog       |
| log_bin_index                   | D:\mysql8\mysql-8.0.19-winx64\data\binlog.index |
| log_bin_trust_function_creators | OFF                                             |
| log_bin_use_v1_row_events       | OFF                                             |
| sql_log_bin                     | ON                                              |
+---------------------------------+-------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

临时设置文件

查看日志

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000016 |      5638 | No        |
| binlog.000017 |       178 | No        |
| binlog.000018 |    376261 | No        |
| binlog.000019 |    209410 | No        |
| binlog.000020 |    103024 | No        |
| binlog.000021 |      1146 | No        |
| binlog.000022 | 139037539 | No        |
| binlog.000023 |       155 | No        |
+---------------+-----------+-----------+
8 rows in set (0.02 sec)

因为是二进制日志文件，因此我们不能直接查看，可通过命令

mysqlbinlog -v "二进制文件具体路径"

使用日志恢复数据

删除二进制日志