mysql 升级笔记

MySQL

执行步骤

SQL 语句在 Oracle 中经历了以下的几个步骤。

1. 语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
2. 语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
3. 权限检查：看用户是否具备访问该数据的权限。
4. 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析和硬解析又该怎么理解呢？

在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。

如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

5. 优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。

6. 执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。

存储引擎

1. InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
2. MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
3. Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
4. NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
5. Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

DDL:数据定义语言，增删改：

create database wytest;

drop database wytest;

创建表结构：

create table test(

player_id int(11) NOT NULL AUTO_INCREMENT,

player_name varchar(255) NOT NULL

);

CREATE TABLE `player`  (  
  `player_id` int(11) NOT NULL AUTO_INCREMENT,  
  `team_id` int(11) NOT NULL, 
  `player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
  `height` float(3, 2) NULL DEFAULT 0.00,  
  PRIMARY KEY (`player_id`) USING BTREE,  
  UNIQUE INDEX `player_name`(`player_name`) USING BTREE  
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;  

里面的数据表和字段都使用了反引号，这是为了避免它们的名称与 MySQL 保留字段相同，对数据表和字段名称都加上了反引号。

ALTER TABLE player ADD (age int(11)); //添加字段

ALTER TABLE player RENAME COLUMN age to player_age //修改字段名

ALTER TABLE player MODIFY (player_age float(3,1)); //修改字段数据类型

ALTER TABLE player DROP COLUMN player_age; //删除字段

约束

首先是主键约束。

主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。在上面的例子中，我们就把 player_id 设置为了主键。

其次还有外键约束。

外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。比如 player_id 在 player 表中是主键，如果你想设置一个球员比分表即 player_score，就可以在 player_score 中设置 player_id 为外键，关联到 player 表中。

除了对键进行约束外，还有字段约束。

唯一性约束。

唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。比如我们在 player 表中给 player_name 设置唯一性约束，就表明任何两个球员的姓名不能相同。需要注意的是，唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。

NOT NULL 约束。对字段定义了 NOT NULL，即表明该字段不应为空，必须有取值。

DEFAULT，表明了字段的默认值。如果在插入数据的时候，这个字段没有取值，就设置为默认值。比如我们将身高 height 字段的取值默认设置为 0.00，即DEFAULT 0.00。

CHECK 约束，用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE，比如我们可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3，即CHECK(height>=0 AND height<3)。

检索

SQL：SELECT DISTINCT attack_range FROM heros //去除重复行

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …//输入顺序

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT //执行顺序

asc表示增排序；desc表示减排序

sql函数

聚集函数：

SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000 	
//role_assist 为 NULL，这时`COUNT(role_assist)`会忽略值为 NULL 的数据行，而 COUNT(*) 只是统计数据行数，不管某个字段是否为 NULL。

SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros ；	
//取不同生命最大值，即`DISTINCT hp_max`，然后针对它们取平均值，即`AVG(DISTINCT hp_max)`，最后再针对这个值保留小数点两位，也就是`ROUND(AVG(DISTINCT hp_max), 2)`。

SELECT COUNT(*), role_main FROM heros GROUP BY role_main  
//先对数据按照不同的数值进行分组，然后对这些分好的组进行聚集统计。对数据进行分组，需要使用 GROUP BY 子句。比如我们想按照英雄的主要定位进行分组，并统计每组的英雄数量。

HAVING 的作用和 WHERE 一样，都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。

//首先我们需要获取的是英雄的数量、主要定位和次要定位，即SELECT COUNT(*) as num, role_main, role_assist。然后按照英雄的主要定位和次要定位进行分组，即GROUP BY role_main, role_assist，同时我们要对分组中的英雄数量进行筛选，选择大于 5 的分组，即HAVING num > 5，然后按照英雄数量从高到低进行排序

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC  
  

非关联子查询

关联子查询：子查询的执行与主查询相关，需要执行多次

非关联子查询：子查询执行与主查询执行无关，只需要执行一次即可

通过SELECT max(height) FROM player可以得到最高身高这个数值，结果为 2.16，然后我们再通过 player 这个表，看谁具有这个身高，再进行输出，这样的子查询就是非关联子查询。

SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player) ;

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。比如我们想要查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 ID。

SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)  

关联子查询通常也会和 EXISTS 一起来使用，EXISTS 子查询用来判断条件是否满足，满足的话为 True，不满足为 False。

SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)  
  
  
集合比较子查询的作用是与另一个查询结果集进行比较，我们可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符，它们的含义和英文意义一样：

SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)  

数据库如何连接？

笛卡尔积

等值连接where…=…；非等值连接where…>… ；

外连接；左外连接；(+)表示从表

SQL：SELECT * FROM player, team where player.team_id = team.team_id(+)

SQL：SELECT * FROM player, team where player.team_id(+) = team.team_id //右外连接

SQL：SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = ‘布雷克 - 格里芬’ and a.height < b.height //自连接

视图

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。

视图作为一张虚拟表，帮我们封装了底层与数据表的接口。它相当于是一张表或多张表的数据结果集。视图的这一特点，可以帮我们简化复杂的 SQL 查询，比如在编写视图后，我们就可以直接重用它，而不需要考虑视图中包含的基础查询的细节

CREATE VIEW player_above_avg_height AS  
SELECT player_id, height  
FROM player  
WHERE height > (SELECT AVG(height) from player)  

存储过程

事务

InnoDB 支持事务是 InnoDB 取代 MyISAM 的重要原因。那么什么是事务呢？事务的英文是 transaction，从英文中你也能看出来它是进行一次处理的基本单元，要么完全执行，要么都不执行。

事物四个特性：ACID，原子性，一致性，隔离性，持久性；

多个应用程序访问数据库的时候，事务可以提供隔离，保证事务之间不被干扰。

一致性：比如说，在数据表中我们将姓名字段设置为唯一性约束，这时当事务进行提交或者事务发生回滚的时候，如果数据表中的姓名非唯一，就破坏了事务的一致性要求。所以说，事务操作会让数据表的状态变成另一种一致的状态，如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作之前的状态。

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

事务隔离

脏读：读到了其他事务还未提交的数据

SQL> BEGIN;  
SQL> INSERT INTO heros_temp values(4, '吕布');  
当小张还没有提交该事务的时候，小李又对数据表进行了访问，他想看下这张英雄表里都有哪些英雄：
SQL> SELECT * FROM heros_temp;  
这个时候小张还没有提交事务，但是小李却读到了小张还没有提交的数据，这种现象我们称之为“脏读”。

不可重复读：同一条记录，两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。

幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产生了幻读。

mysql> SHOW VARIABLES LIKE 'transaction_isolation';  显示mysql隔离等级

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  改变隔离等级

游标

类似于指针

DECLARE cursor_name CURSOR FOR select_statement  //创建游标
OPEN cursor_name  								//打开游标
FETCH cursor_name INTO var_name ...  				//从游标中取数据
CLOSE cursor_name  
DEALLOCATE PREPARE  							//释放游标

利用Python操作MySQL

我们在使用 Python 对 DBMS 进行操作的时候，需要经过下面的几个步骤：

1. 引入 API 模块；
2. 与数据库建立连接；
3. 执行 SQL 语句；
4. 关闭数据库连接。

这里有不少库供我们选择，比如 MySQLdb、mysqlclient、PyMySQL、peewee 和 SQLAIchemy 等。今天我讲解的是 mysql-connector，它是 MySQL 官方提供的驱动器，用来给后端语言，比如 Python 提供连接。

pip install mysql-connector  
在安装之后，你可以创建数据库连接，然后查看下数据库的版本号，来验证下数据库是否连接成功。代码如下：
# -*- coding: UTF-8 -*-  
import mysql.connector  
# 打开数据库连接  
db = mysql.connector.connect(  
       host="localhost",  
       user="root",  
       passwd="XXX", # 写上你的数据库密码  
       database='wucai',   
       auth_plugin='mysql_native_password'  
)  
# 获取操作游标   
cursor = db.cursor()  
# 执行 SQL 语句  
cursor.execute("SELECT VERSION()")  
# 获取一条数据  
data = cursor.fetchone()  
print("MySQL 版本: %s " % data)  
# 关闭游标 & 数据库连接  
cursor.close()  
db.close()  
运行结果：
MySQL 版本: 8.0.13   

当我们通过cursor = db.cursor()创建游标后，就可以通过面向过程的编程方式对数据库中的数据进行操作：

1. 使用cursor.execute(query_sql)，执行数据库查询；
2. 使用cursor.fetchone()，读取数据集中的一条数据；
3. 使用cursor.fetchall()，取出数据集中的所有行，返回一个元组 tuples 类型；
4. 使用cursor.fetchmany(n)，取出数据集中的多条数据，同样返回一个元组 tuples；
5. 使用cursor.rowcount，返回查询结果集中的行数。如果没有查询到数据或者还没有查询，则结果为 -1，否则会返回查询得到的数据行数；
6. 使用cursor.close()，关闭游标。

增
sql = "INSERT INTO player (team_id, player_name, height) VALUES (%s, %s, %s)"  
val = (1003, " 约翰 - 科林斯 ", 2.08)  
cursor.execute(sql, val)  
db.commit()  
print(cursor.rowcount, " 记录插入成功。")  

查
sql = 'SELECT player_id, player_name, height FROM player WHERE height>=2.08'  
cursor.execute(sql)  
data = cursor.fetchall()  
for each_player in data:  
  print(each_player)  
 
改
sql = 'UPDATE player SET height = %s WHERE player_name = %s'  
val = (2.09, " 约翰 - 科林斯 ")  
cursor.execute(sql, val)  
db.commit()  

数据库调优

范式

在设计关系型数据库模型的时候，需要对关系内部各个属性之间联系的合理化程度进行定义，这就有了不同等级的规范要求，这些规范要求被称为范式（NF）

反范式设计

BCNF（巴斯范式）

主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系，这样就有可能导致上面的异常情况；

巴斯 - 科德范式，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系 。

索引

从功能逻辑上说，索引主要有 4 种，分别是

• 普通索引：没有约束
• 唯一索引：在普通索引上增加数据唯一性约束
• 主键索引：增加不为空的约束；NOT NULL+UNIQUE
• 全文索引

使用B+树来进行索引

二分查找存在一条路走到黑，退化成链表的情况的极端情况

一般采用B+树作为数据库索引

 B+ 树和 B 树的查询过程差不多，但是 B+ 树和 B 树有个根本的差异在于，B+ 树的中间节点并不直接存储数据。这样的好处都有什么呢？
首先，B+ 树查询效率更稳定。因为 B+ 树每次只有访问到叶子节点才能找到对应的数据，而在 B 树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。
其次，B+ 树的查询效率更高，这是因为通常 B+ 树比 B 树更矮胖（阶数更大，深度更低），查询所需要的磁盘 I/O 也会更少。同样的磁盘页大小，B+ 树可以存储更多的节点关键字。
不仅是对单个关键字的查询上，在查询范围上，B+ 树的效率也比 B 树高。这是因为所有关键字都出现在 B+ 树的叶子节点中，并通过有序链表进行了链接。而在 B 树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

hash索引

键值 key 通过 Hash 映射找到桶 bucket。在这里桶（bucket）指的是一个能存储一条或多条记录的存储单位。一个桶的结构包含了一个内存指针数组，桶中的每行数据都会指向下一行，形成链表结构，当遇到 Hash 冲突时，会在桶中进行键值的查找。

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

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

数据库缓冲池

锁：悲观锁和乐观锁

共享锁和排它锁，是我们经常会接触到的两把锁。
共享锁也叫读锁或 S 锁，共享锁锁定的资源可以被其他用户读取，但不能修改。在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

LOCK TABLE product_comment READ;  

UPDATE product_comment SET product_id = 10002 WHERE user_id = 912178;  
会报错ERROR 1099 (HY000): Table 'product_comment' was locked with a READ lock and can't be updated 

UNLOCK TABLE;  解锁

SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178 LOCK IN SHARE MODE  //对某一行加锁

排它锁也叫独占锁、写锁或 X 锁。排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。

另外当我们对数据进行更新的时候，也就是INSERT、DELETE或者UPDATE的时候，数据库也会自动使用排它锁，防止其他事务对该数据行进行操作。

LOCK TABLE product_comment WRITE;  
UNLOCK TABLE;  

乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。

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

乐观锁和悲观锁的适用场景：

1. 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

我们都不希望出现死锁的情况，可以采取一些方法避免死锁的发生：

1. 如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步来获取，这样可以减少死锁发生的概率；
2. 如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；
3. 不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率。

MVCC

有没有一种方式，可以不采用锁机制，而是通过乐观锁的方式来解决不可重复读和幻读问题呢？实际上 MVCC 机制的设计(MVCC+Next-Key LOCK)，就是用来解决这个问题的，它可以在大多数情况下替代行级锁，降低系统的开销。

MVCC 的英文全称是 Multiversion Concurrency Control，中文翻译过来就是多版本并发控制技术。从名字中也能看出来，MVCC 是通过数据行的多个版本管理来实现数据库的并发控制，简单来说它的思想就是保存数据的历史版本。这样我们就可以通过比较版本号决定数据是否显示出来（具体的规则后面会介绍到），读取数据的时候不需要加锁也可以保证事务的隔离效果。

我们需要记住，MVCC 的核心就是 Undo Log+ Read View，“MV”就是通过 Undo Log 来保存数据的历史版本，实现多版本的管理，“CC”是通过 Read View 来实现管理，通过 Read View 原则来决定数据是否显示。同时针对不同的隔离级别，Read View 的生成策略不同，也就实现了不同的隔离级别。

Redis

为什么采用连接池机制

基于直接连接的弊端，Redis 提供了连接池的机制，这个机制可以让我们事先创建好多个连接，将其放到连接池中，当我们需要进行 Redis 操作的时候就直接从连接池中获取，完成之后也不会直接释放掉连接，而是将它返回到连接池中。

连接池机制可以避免频繁创建和释放连接，提升整体的性能。

连接池机制的原理

在连接池的实例中会有两个 list，保存的是_available_connections和_in_use_connections，它们分别代表连接池中可以使用的连接集合和正在使用的连接集合。当我们想要创建连接的时候，可以从_available_connections中获取一个连接进行使用，并将其放到_in_use_connections中。如果没有可用的连接，才会创建一个新连接，再将其放到_in_use_connections中。如果连接使用完毕，会从_in_use_connections中删除，添加到_available_connections中，供后续使用。

事务处理命令

1. MULTI：开启一个事务；
2. EXEC：事务执行，将一次性执行事务内的所有命令；
3. DISCARD：取消事务；
4. WATCH：监视一个或多个键，如果事务执行前某个键发生了改动，那么事务也会被打断；
5. UNWATCH：取消 WATCH 命令对所有键的监视。