学习目标

• 能够使用mysql的常见函数
• 能够理解事务的相关概念
• 能够在Mysql中使用事务
• 能够理解脏读,不可重复读,幻读的概念及解决办法
• 能够理解索引的作用
• 能够理解索引的结构
• 能够在表中创建索引、查看索引，删除索引

  第一章 MySQL常用函数

  4.1 字符串函数

  | 函数 | 描述 | 实例 | | —- | —- | —- | | CHAR_LENGTH(s) | 返回字符串 s 的字符数 | SELECT CHAR_LENGTH(‘itheima’) AS ‘长度’; | | CONCAT(s1,s2…sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | SELECT CONCAT(‘I’,’love’,’you’); | | LOWER(s) | 将字符串 s 的所有字母变成小写字母 | SELECT LOWER(‘ITHEIMA’); | | UPPER(s) | 将字符串转换为大写 | SELECT UPPER(“itheima”); | | SUBSTR(s, start,length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | SELECT SUBSTR(“itheima”,1,2); | | TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | SELECT TRIM(‘ itheima ‘) |

示例:

SELECT CHAR_LENGTH('itheima') AS '长度';
--执行结果为: 7
SELECT CONCAT('I','love','you');
--执行结果为: Iloveyou
SELECT LOWER('ITHEIMA');
--执行结果为: itheima
SELECT UPPER("itheima");
--执行结果为: ITHEIMA
SELECT SUBSTR("itheima",1,2);
--执行结果为: it
SELECT TRIM(' itheima ');
--执行结果为: itheima

4.2 数字函数

示例:

SELECT RAND();    -- 返回0-1之间的随机数 0.21809973867433122
SELECT ROUND(3.1415926,2) ; -- 执行结果: 3.14
SELECT LEAST(13, 14, 521, 74, 1);    -- 执行结果: 1
SELECT GREATEST(13, 14, 521, 74, 1); -- 执行结果: 521

4.3 日期函数

示例:

SELECT NOW(); 或 SELECT SYSDATE(); -- 返回系统的当前时间: 年-月-日 时:分:秒 
SELECT CURDATE(); -- 返回系统当前日期: 年-月-日
SELECT CURTIME(); -- 返回系统当前时间: 时:分:秒
SELECT YEAR(NOW()); -- 返回当前日期中的年份
SELECT MONTH(NOW()); -- 返回当前日期中的月份
SELECT DAY(NOW()); -- 返回当前日期中的日

4.4 高级函数

第二章 事务

1.1 事务的概述

业务场景

什么是事务

如果在执行一个业务操作的时候，需要执行多条SQL语句，必须保证所有的SQL语句都执行成功。只要其中有一条执行失败，则所有的SQL语句都要进行回滚
事务必须是所有的SQL语句都执行成功，或者都执行失败
事务的四大特性ACID

需求
不使用事务，用SQL模拟Jack给Rose转账500？
应用

-- 创建数据表account（id 整型 自增主键，name 变长字符串30，balance 整型）
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    balance decimal
);

-- 添加数据（Jack，1000）、（Rose，1000）
INSERT INTO account (name, balance) VALUES ('Jack', 1000), ('Rose', 1000);
-- 如果要完成Jack转账给Rose : 500
-- 至少2条，Jack扣钱，Rose加钱，要有2条update语句
update account set balance = balance - 500 where name='Jack';
update account set balance = balance + 500 where name='Rose';
-- 账户全部还原成1000
update account set balance = 1000;

假设当Jack账号上-500元,服务器崩溃了。Rose的账号并没有+500元，数据就出现问题了
我们需要保证其中一条SQL语句出现问题，整个转账就算失败
只有两条SQL都成功了转账才算成功。这个时候就需要用到事务

1.2 事务的开启与提交

有两种方式：

1. 手动开启，需手动提交
2. 自动开启，提交由两种模式
   1. 手动提交
   2. 自动提交

      1.2.1 手动开启与提交

      SQL语句

提交事务的过程

1. 开启事务
2. 执行SQL语句
3. 提交或回滚事务

案例：事务提交

模拟Jack给Rose转500元钱成功的情况

1. 使用DOS控制台进入MySQL
2. 执行以下SQL语句：
   • 开启事务
   • Jack账号-500
   • Rose账号+500
3. 使用Sqlyog查看数据库：发现数据并没有改变
4. 在控制台执行commit提交任务：
5. 使用Sqlyog查看数据库：发现数据改变

案例：事务回滚

模拟Jack给Rose转500元钱失败的情况，在控制台执行以下SQL语句：

1. 开启事务
2. Jack账号-500，Rose账户+500
3. 使用Sqlyog查看数据库：发现数据并没有改变
4. 在控制台执行rollback回滚事务
5. 使用Sqlyog查看数据库：发现数据没有改变

1.2.2 自动开启

事务自动开启时，提交有两种模式，利用autocommit关键字来实现

关键字autocommit：1表示自动提交，0表示手动提交

-- 查询事务提交模式
select @@autocommit;
-- 修改事务提交模式
set @@autocommit=0;

在默认的情况下，MySQL中每条SQL语句执行前都会创建一个事务，执行结束后就关闭事务


案例：自动提交事务

1. 将账户金额重置为1000
2. 更新Jack的账户
3. 使用Sqlyog查看数据库：发现数据已经改变

案例：取消自动提交事务

1. 查看MySQL是否开启自动提交事务
2. 如果是自动提交，就取消自动提交事务
3. Jack账户扣款500
4. 使用Sqlyog查看数据库，发现数据并没有改变
5. 执行commit提交任务
6. 使用Sqlyog查看数据库，发现数据已经改变

但是Sqlyog里面看到，Jack账户金额依然是1000

继续，在命令行里提交事务

最后，在Sqlyog里可以看到，Jack的账户金额被修改了。说明，事务被修改成了手动模式

1.3 事务的回滚点和执行原理

什么是回滚点

上面的操作，如果回滚，直接回滚到事务开始前。
有时我们并不需要回滚到最开始的状态，可能只需要回滚到中间的某个位置，就可以设置回滚点
语法

应用

1. 将账户数据还原到1000
2. 开启事务
3. 让Jack账号减2次钱，每次10块
4. 设置回滚点：savepoint two_times;
5. 让Jack账号减2次钱，每次10块
6. 回到回滚点：rollback to two_times;
7. 最后commit提交事务

可以看到，最终Jack账户金额是980，表示虽然转账4此，但是又回滚了2次，因此一共减去了20
执行原理

1. 一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态
2. 如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件
3. 如果开启事务，将所有的写操作写到日志文件中
4. 如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中
5. 如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作

   1.4 并发访问的问题（了解）

   并发访问的问题

因为一张表可以同时有多个用户在访问，相互之间会有影响，会引发以下三种并发访问的问题

并发访问的问题可以通过数据库隔离级别来解决

1.4.1 事务的隔离级别

隔离级别的特点：

1. 级别1最低，级别4最高
2. 隔离级别越高，性能越低，安全性越高

MySQL与隔离级别相关的命令

-- 查询事务隔离级别
select @@tx_isolation;
-- 设置全局事务隔离级别
set global transaction isolation level 四种级别名字;

四种隔离级别起的作用

1. Read uncommitted (读未提交)： 级别最低，会同时出现三种问题
2. Read committed (读已提交)：可以解决脏读的问题
3. Repeatable read (可重复读)：可以解决脏读和不可重复读的问题
4. Serializable (串行化)： 所有的事务都是以串行的方式执行，没有并发执行的事务

   1.4.2 脏读

   准备数据

-- 创建数据表，账户表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE  -- 金额
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('Jack', 1000), ('Rose', 1000);
select * from account;
-- 还原
update account set balance = 1000;

脏读演示

-- 1. 打开一个命令行Jack，设置全局的隔离级别为最低：
set global transaction isolation level read uncommitted;
-- 2. 选择数据库，开启事务
use db;
start transaction;
-- 3. 更新2个人的账户，未提交
update account set balance = balance - 500 where name = 'Jack';
update account set balance = balance + 500 where name = 'Rose';
-- 4. 打开另一个命令行Rose，选择数据库，开启事务
use db;
start transaction;
-- 5. 查询账户，发现钱已经到账，发货
select * from account;
-- 6. 命令行Jack，回滚
rollback;
-- 7. 命令行Rose，查询账户，钱没了
select * from account;

解决办法

-- 1. 打开命令行Jack，设置全局的隔离级别为 read committed
set global transaction isolation level read committed; 
-- 2. 重复上面的操作：需要重新登录。命令行Jack在没有提交和回滚之前，Rose账户看到的金额不变
会发现命令行Jack在没有提交和回滚之前，命令行Rose看不到账户发生任何变化
-- 3. 命令行Jack。使用commit提交以后，Rose账户看到的金额变化
命令行Rose，可以看到账户发生了变化

1.4.3 不可重复读

不可重复读演示

-- 1.将数据进行恢复，并关闭窗口重新登录。
update account set balance=1000;
-- 2.开启一个命令行Jack，确保当前的事务隔离是read committed
select @@tx_isolation;
-- 3.选择数据库，开启一个事务
use db;
start transaction;
-- 4.查询用户Jack的账户，查到是1000块
select * from account where name='Jack';
-- 5.开启另一个命令行Rose，选择数据库，开启一个事务
use db;
start transaction;
-- 6.更新Jack账户，减500元
update account set balance=balance-500 where name='Jack';
-- 7.提交事务
commit;
-- 8.命令行Jack再次查询Jack的账户，查到是500块
select * from account where name='Jack';
-- 9.提交事务
commit;

两次查询输出的结果不同，到底哪次是对的？
解决办法

-- 1. 打开命令行Jack，设置全局的隔离级别为 repeatable read
set global transaction isolation level repeatable read;
-- 2. 重复上面的操作：需要重新登录
会发现命令行Rose事务提交后，命令行Jack依然读到1000，表示可以重复读

1.4.4 幻读

幻读演示

-- 1.将数据恢复成1000，并关闭窗口重新登录。
update account set balance=1000;
-- 2.开启一个命令行Jack，确保当前的事务隔离是repeatable read
select @@tx_isolation; 
-- 3.选择数据库，开启一个事务
use db;
start transaction;
-- 4.查询id大于1的账户信息，查到1条记录
select * from account where id > 1;
-- 5.开启另一个命令行Rose，选择数据库，开启事务
use db;
start transaction
-- 6.新增一条记录
insert into account values (null, 'Tom', 300);
-- 7.提交事务
commit;
-- 8.命令行Jack再次查询id大于1的账户信息，查到1条记录
select * from account where id > 1;
-- 9.修改id大于1的账户金额为200，发现修改了2条记录
update account set balance = 200 where id > 1; 
-- 10.再次查询id大于1的账户信息，查到2条记录，出现幻觉
select * from account where id > 1;

查到2条记录，出现幻觉？
解决办法

-- 1. 打开命令行Jack，设置全局的隔离级别为 serializable
set global transaction isolation level serializable; 
-- 2. 重复上面的操作：需要重新登录
会发现命令行Rose新增SQL提交后，没有立即执行，而是在等待Jack的事务结束
我们提交了Jack事务后，Rose新增SQL才执行完成，表示目前事务是串行化的

第三章 索引

2.1 索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
如下面的示意图 所示 :

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

2.2 索引优势劣势

优势

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2.3 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

• BTREE 索引 ：最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从
  Mysql5.6版本开始支持全文索引。

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

BTREE 结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

• 树中每个节点最多包含 m个孩子。
• 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)]个孩子。
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由 n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。
插入 3 14 7 1 8 5 11 17 13 6 23 12 20 26 4 16 18 24 25 19 数据为例
演变过程如下：
1). 插入前4个数字 3 14 7 1

2). 插入8，n>4，中间元素7向上分裂到新的节点

3). 插入5，11，17不需要分裂

4). 插入13，中间元素13向上分裂到父节点7

5). 插入6，23，12，20不需要分裂

6). 插入26，中间元素20向上分裂到父节点中

7). 插入4，中间元素4向上分裂到父节点中。然后插入16，18，24，25不需要分裂

8). 最后插入19，14 16 17 18节点n>5，中间节点17向上分裂，但分裂后父节点4 7 13 20的n>5，中间节点13向上分裂

到此，该 BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

B+TREE 结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
• 所有的非叶子节点都可以看作是key的索引部分。

由于 B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

MySQL中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
MySQL中的 B+Tree 索引结构示意图:

2.4 索引分类

1. 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
2. 唯一索引 ：索引列的值必须唯一，但允许有空值
3. 复合索引 ：即一个索引包含多个列

   2.5 索引语法

   索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。

   准备环境:

   ``sql create database demo01 default charset=utf8mb4; use demo01; CREATE TABLEcity(city_idint(11) NOT NULL AUTO_INCREMENT,city_namevarchar(50) NOT NULL,country_idint(11) NOT NULL, PRIMARY KEY (city_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE country ( country_id int(11) NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, PRIMARY KEY (country_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into city (city_id, city_name, country_id) values(1,’西安’,1); insert into city (city_id, city_name, country_id) values(2,’NewYork’,2); insert into city (city_id, city_name, country_id) values(3,’北京’,1); insert into city (city_id, city_name, country_id) values(4,’上海’,1); insert into country (country_id, country_name) values(1,’China’); insert into country (country_id, country_name) values(2,’America’); insert into country (country_id, country_name) values(3,’Japan’); insert into country (country_id, country_name) values(4,’UK’);

<a name="de4e5908"></a>
### 创建索引
语法: 
```sql
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
index_col_name : column_name[(length)][ASC | DESC]

示例:
为city表中的city_name字段创建索引 ；

查看索引

语法:

show index from table_name;

示例:
查看city表中的索引信息

删除索引

语法:

DROP INDEX index_name ON tbl_name;

示例:
想要删除city表上的索引idx_city_name，可以操作如下：

ALTER命令

1). alter table tb_name add primary key(column_list);
    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

2). alter table tb_name add unique index_name(column_list);
    这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

3). alter table tb_name add index index_name(column_list);
    添加普通索引， 索引值可以出现多次。

4). alter table tb_name add fulltext index_name(column_list);
    该语句指定了索引为FULLTEXT， 用于全文索引

2.6 索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。
对查询频次较高，且数据量比较大的表建立索引。
索引字段的选择，最佳候选列应当从 where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用唯一索引，区分度越高，使用索引的效率越高。
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨
船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低
DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
利用最左前缀， N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建复合索引 :
    CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
    对name 创建索引 ;
    对name , email 创建了索引 ;
    对name , email, status 创建了索引 ;