MyBlog

# 其他操作

# 事务 transaction

# 什么是事务

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：

  • 给A的账户减去100元；

update 账户表 set money=money**-100** where name='A';

  • 给B的账户加上100元。

update 账户表 set money=money**+100** where name='B';

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么B的账户没有加上100元，而A却减去了100元，在现实生活中这肯定是不允许的。

如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。

也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。

# 事务4个特性ACID

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

# 隔离级别

事务隔离分为不同级别，包括

  • 读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
  • 读提交（read committed） Oracle默认的隔离级别
  • 可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
  • 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

  • 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
  • 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
  • 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
  • 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

# 事务隔离级别

SQL 标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
隔离级别 脏读 不可重复读 幻影读
READ-UNCOMMITTED √ √ √
READ-COMMITTED × √ √
REPEATABLE-READ × × √
SERIALIZABLE × × ×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1
2
3
4
5
6

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

# 实际情况演示

在下面我会使用 2 个命令行mysql ，模拟多线程（多事务）对同一份数据的脏读问题。

MySQL 命令行的默认配置中事务都是自动提交的，即执行SQL语句后就会马上执行 COMMIT 操作。如果要显式地开启一个事务需要使用命令：START TARNSACTION。

我们可以通过下面的命令来设置隔离级别。

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
1

我们再来看一下我们在下面实际操作中使用到的一些并发控制语句:

  • START TARNSACTION |BEGIN：显式地开启一个事务。
  • COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
  • ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

# 脏读(读未提交)

# 避免脏读(读已提交)

# 不可重复读

还是刚才上面的读已提交的图，虽然避免了读未提交，但是却出现了，一个事务还没有结束，就发生了 不可重复读问题。

# 可重复读

# 防止幻读(可重复读)

一个事务对数据库进行操作，这种操作的范围是数据库的全部行，然后第二个事务也在对这个数据库操作，这种操作可以是插入一行记录或删除一行记录，那么第一个是事务就会觉得自己出现了幻觉，怎么还有没有处理的记录呢? 或者 怎么多处理了一行记录呢?

幻读和不可重复读有些相似之处 ，但是不可重复读的重点是修改，幻读的重点在于新增或者删除。

# 查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

  • 开启事务：start transaction；
  • 结束事务：commit（提交事务）或rollback（回滚事务）。

在执行SQL语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

SELECT @@tx_isolation;

Repeatable Read（可重读）

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

# 事务处理

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
  • 事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
  • 事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
  • MySQL默认数据库的事务是开启的，执行SQL后自动提交。
  • MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

# 提交 commit

#多条语句时，批量执行，事务提交

#有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱

#mysql的事务默认就是开启的 -- 多条语句一起操作时,要么一起成功要么一起失败

BEGIN; #关闭事务的自动提交,相当于start transaction

INSERT INTO user (id) VALUES(25);#成功
INSERT INTO user (id) VALUES(5);#已经存在5了，会失败
COMMIT; #手动提交事务
1
2
3
4
5

# 回滚 rollback

#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

BEGIN;
INSERT INTO user (id) VALUES(15);
INSERT INTO user (id) VALUES(35);#存在了
ROLLBACK;#事务回滚，就不会再提交了
1
2
3
4

# 索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行对比，然后返回满足条件的记录。这样做会执行大量磁盘 I/O 操作，并花费大量数据库系统时间。

而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。

对一张表中的某个列建立索引，有以下两种语句格式：

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);
1
2
3

索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。

在使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。

比如有一个用户表，它拥有用户名(username)和个人签名(note)两个字段。其中用户名具有唯一性，并且格式具有较强的限制，我们给用户名加上一个唯一索引；个性签名格式多变，而且允许不同用户使用重复的签名，不加任何索引。

这时候，如果你要查找某一用户，使用语句 select * from user where username=? 和 select * from user where note=? 性能是有很大差距的，对建立了索引的用户名进行条件查询会比没有索引的个性签名条件查询快几倍，在数据量大的时候，这个差距只会更大。

一些字段不适合创建索引，比如性别，这个字段存在大量的重复记录无法享受索引带来的速度加成，甚至会拖累数据库，导致数据冗余和额外的 CPU 开销。

# 定义

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

# 分类

  • 单值索引：一个索引只包括一个列，一个表可以有多个列
  • 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
  • 复合索引：一个索引同时包括多列

# 创建索引

#查看索引，主键会自动创建索引

show index from dept;
1

#创建普通索引

#create index 索引名字 on 表名(字段名); #创建索引
create index loc_index on dept(loc); #创建索引
1
2

# 创建唯一索引

#创建唯一索引--索引列的值必须唯一
CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
CREATE UNIQUE INDEX bindex ON dept(loc)
1
2
3

# 创建复合索引

#如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX 索引名 ON 表名 (字段1, 字段2)
CREATE INDEX PIndex ON Persons (LastName, FirstName)
1
2
3

# 删除索引

alter table dept drop index fuhe_index
1

# 索引扫描类型

type：

  • ALL 全表扫描，没有优化，最慢的方式
  • index 索引全扫描，其次慢的方式
  • range 索引范围扫描，常用语<，<=，>=，between等操作
  • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
  • eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
  • const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
  • null MySQL不访问任何表或索引，直接返回结果

# 最左特性

explain
select * from dept where loc='二区' #使用了loc索引

explain
select * from dept where dname='研发部'#使用了dname索引

explain

select * from dept where dname='研发部' and loc='二区' #使用了dname索引
1
2
3
4
5
6
7
8
9

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)、(k1,k3)和(k1,k2,k3)索引，这就是最左匹配原则，也称为最左特性。

# 为何索引快？

明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

  • 排序，tree结构，类似二分查找
  • 索引表小

# 小结

优点:

  • 索引是数据库优化
  • 表的主键会默认自动创建索引
  • 每个字段都可以被索引
  • 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
  • 索引事先对数据进行了排序，大大提高了查询效率

缺点:

  • 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
  • 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
  • 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
  • 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

# 视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

注意理解视图是虚拟的表：

  • 数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
  • 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
  • 视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
  • 在使用视图的时候，可以把它当作一张表。

创建视图的语句格式为：

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
1

可见创建视图的语句，后半句是一个 SELECT 查询语句，所以视图也可以建立在多张表上，只需在 SELECT 语句中使用子查询或连接查询，这些在之前的实验已经进行过。

# 概念

可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。 下次还要发起相同的sql，直接查视图。现在用的少,了解即可. 使用： 1，创建视图 2，使用视图

# 测试

create view 视图名 as  SQL语句;
select * from 视图名;

#视图：就是一个特殊的表，缓存上次的查询结果

#好处是提高了SQL的复用率，坏处是占内存无法被优化
#1.创建视图

CREATE VIEW emp_view AS
SELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询，名字里包含a的

#2.使用视图

SELECT * FROM emp_view
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 导入

此处讲解的是导入一个纯数据文件，该文件中将包含与数据表字段相对应的多条数据，这样可以快速导入大量数据，除此之外，还有用 SQL 语句的导入方式，语法为：source *.sql 这是实验中经常用到的。两者之间的不同是：数据文件导入方式只包含数据，导入规则由数据库系统完成；SQL 文件导入相当于执行该文件中包含的 SQL 语句，可以实现多种操作，包括删除，更新，新增，甚至对数据库的重建。

数据文件导入，可以把一个文件里的数据保存进一张表。导入语句格式为：

LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;
1

# 导出

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
1

注意：语句中 “文件路径” 之下不能已经有同名文件。

# 备份

数据库中的数据十分重要，出于安全性考虑，在数据库的使用中，应该注意使用备份功能。

备份与导出的区别：导出的文件只是保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。

mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 备份的语句：

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表
1
2
3

mysqldump 是一个备份工具，因此该命令是在终端中执行的，而不是在 mysql 交互环境下

# 恢复

用备份文件恢复数据库，其实我们早就使用过了。在本次实验的开始，我们使用过这样一条命令：

source /home/shiyanlou/Desktop/MySQL-06.sql
1

这就是一条恢复语句，它把 MySQL-06.sql 文件中保存的 mysql_shiyan 数据库恢复。

还有另一种方式恢复数据库，但是在这之前我们先使用命令新建一个空的数据库 test：

CREATE DATABASE test;  #新建一个名为test的数据库
1

再次 Ctrl+D 退出 MySQL，然后输入语句进行恢复，把刚才备份的 bak.sql 恢复到 test 数据库：

mysql -u root test < bak.sql
1

# SQL优化

# 创建mysql-db库

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mysql-db` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mysql-db`;
1
2
3

# 准备student表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (

`id` varchar(4) NOT NULL,

`NAME` varchar(20) DEFAULT NULL,

`sex` char(2) DEFAULT NULL,

`birthday` date DEFAULT NULL,

`salary` decimal(7,2) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `student`(`id`,`NAME`,`sex`,`birthday`,`salary`) values ('1','张慎政','男','2020-01-01','10000.00'),('2','刘沛霞','女','2020-01-02','10000.00'),('3','刘昱江','男','2020-01-03','10000.00'),('4','齐雷','男','2020-01-04','20000.00'),('5','王海涛','男','2020-01-05','20000.00'),('6','董长春','男','2020-01-06','10000.00'),('7','张久军','男','2020-01-07','20000.00'),('8','陈子枢','男','2020-10-11','3000.00');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 准备tb_dept表

DROP TABLE IF EXISTS `tb_dept`;

CREATE TABLE `tb_dept` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,

`sort` int(11) DEFAULT NULL,

`note` varchar(100) DEFAULT NULL,

`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert into `tb_dept`(`id`,`name`,`parent_id`,`sort`,`note`,`created`,`updated`) values (1,'集团',0,1,'集团总部','2018-10-02 09:15:14','2018-09-27 16:35:54'),(2,'财务部',1,2,'财务管理','2018-09-27 16:35:52','2018-09-27 16:34:15'),(3,'软件部',1,3,'开发软件、运维','2018-09-27 16:35:54','2018-09-27 16:34:51');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 准备tb_user表

DROP TABLE IF EXISTS `tb_user`;

CREATE TABLE `tb_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`dept_id` int(11) DEFAULT NULL,

`username` varchar(50) DEFAULT NULL,

`password` varchar(100) DEFAULT NULL,

`salt` varchar(50) DEFAULT NULL,

`email` varchar(100) DEFAULT NULL,

`mobile` varchar(100) DEFAULT NULL,

`valid` tinyint(4) DEFAULT NULL,

`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert into `tb_user`(`id`,`dept_id`,`username`,`password`,`salt`,`email`,`mobile`,`valid`,`created`,`updated`) values (1,1,'陈集团','123456',NULL,'tony@sina.com','13572801415',1,'2018-09-30 09:32:18','2018-09-30 09:32:18'),(2,3,'牛软件','567890',NULL,'niu@sina.com','13208737172',0,'2018-10-02 09:23:19','2018-09-20 09:32:18');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 查询SQL尽量不要使用select *，而是具体字段

反例：SELECT * FROM student

正例：SELECT id,NAME FROM student

理由：

字段多时，大表能达到100多个字段甚至达200多个字段

只取需要的字段，节省资源、减少网络开销

select * 进行查询时，很可能不会用到索引，就会造成全表扫描
1
2
3
4
5
6
7
8
9
10
11

# 避免在where子句中使用or来连接条件

反例：SELECT * FROM student WHERE id=1 OR salary=30000

正例：

# 分开两条sql写

SELECT * FROM student WHERE id=1

SELECT * FROM student WHERE salary=30000

理由：

使用or可能会使索引失效，从而全表扫描

对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 使用varchar代替char

反例：`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

正例：`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

理由：

varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间

char按声明大小存储，不足补空格

其次对于查询来说，在一个相对较小的字段内搜索，效率更高
1
2
3
4
5
6
7
8
9
10
11

# 尽量使用数值替代字符串类型

主键（id）：primary key优先使用数值类型int，tinyint

性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint

支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡

服务状态（state）：1-开启、2-暂停、3-停止

商品状态（state）：1-上架、2-下架、3-删除
1
2
3
4
5
6
7
8
9

# 查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。

通常采用分页，一页习惯10/20/50/100条。

# 使用explain分析你SQL执行计划

SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN

SELECT * FROM student WHERE id=1
1
2
3

# 是否使用了索引及其扫描类型

type：

  • ALL 全表扫描，没有优化，最慢的方式
  • index 索引全扫描
  • range 索引范围扫描，常用语<，<=，>=，between等操作
  • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
  • eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
  • const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
  • null MySQL不访问任何表或索引，直接返回结果

key：

  • 真正使用的索引方式

# 创建name字段的索引

ALTER TABLE student ADD INDEX index_name (NAME)
1

# 优化like语句

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

反例：

EXPLAIN

SELECT id,NAME FROM student WHERE NAME LIKE '%1'


EXPLAIN

SELECT id,NAME FROM student WHERE NAME LIKE '%1%'
1
2
3
4
5
6
7
8

正例：

EXPLAIN

SELECT id,NAME FROM student WHERE NAME LIKE '1%'
1
2
3

# 字符串怪现象

反例：

#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=123
1
2
3

正例：

#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME='123'
1
2
3

理由：

  • 为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

# 索引不宜太多，一般5个以内

  • 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
  • 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
  • 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
  • insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
  • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要

# 索引不适合建在有大量重复数据的字段上

如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

# where限定查询的数据

数据中假定就一个男的记录

反例：

SELECT id,NAME FROM student WHERE sex='男'
1

正例：

SELECT id,NAME FROM student WHERE id=1 AND sex='男'
1

理由：

  • 需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销

# 避免在where中对字段进行表达式操作

反例：

EXPLAIN
SELECT * FROM student WHERE id+1-1=+1
1
2

正例：

EXPLAIN
SELECT * FROM student WHERE id=+1-1+1
EXPLAIN
SELECT * FROM student WHERE id=1
1
2
3
4

理由：

  • SQL解析时，如果字段相关的是表达式就进行全表扫描

  • # 避免在where子句中使用!=或<>操作符

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

反例：

EXPLAIN
SELECT * FROM student WHERE salary!=3000
EXPLAIN
SELECT * FROM student WHERE salary<>3000
1
2
3
4

理由：

  • 使用!=和<>很可能会让索引失效

  • # 去重distinct过滤字段要少

#索引失效

EXPLAIN
SELECT DISTINCT * FROM student
1
2

#索引生效

EXPLAIN
SELECT DISTINCT id,NAME FROM student
EXPLAIN
SELECT DISTINCT NAME FROM student
1
2
3
4

理由：

  • 带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间

# where中使用默认值代替null

#修改表，增加age字段，类型int，非空，默认值0

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
1

# 批量插入性能提升

大量数据提交，上千，上万，批量性能非常快，mysql独有

多条提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷');

INSERT INTO student (id,NAME) VALUES(5,'刘昱江');
1
2
3

批量提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷'),(5,'刘昱江');
1

理由：

  • 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
  • 数据量小体现不出来

# 批量删除优化

避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

反例：

#一次删除10万或者100万+？

delete from student where id <100000;
1

#采用单一循环操作，效率低，时间漫长

for（User user:list）{

delete from student;

}
1
2
3
4
5

正例：

//分批进行删除，如每次500

for(){


delete student where id<500;

}

delete student where id>=500 and id<1000;
1
2
3
4
5
6
7
8

理由：

  • 一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

# 伪删除设计

商品状态（state）：1-上架、2-下架、3-删除

理由：

  • 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
  • 同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
  • 通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
  • 操作速度快，特别数据量很大情况下

# 提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee
group by job
having job ='president' or job = 'managent';
1
2
3

正例：先过滤，后分组

select job，avg（salary） from employee
where job ='president' or job = 'managent'
group by job;
1
2
3

# 复合索引最左特性

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
1

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢'
1
2

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
SELECT * FROM student WHERE salary=3000
1
2

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000
1
2

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='陈子枢'
1
2

理由：

  • 复合索引也称为联合索引
  • 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
  • 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

# 排序字段创建索引

什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用 *，包含了未索引的字段，导致索引失效

EXPLAIN
SELECT * FROM student ORDER BY NAME;
1
2

#name字段有索引

EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME
1
2

#排序字段未创建索引，性能就慢

EXPLAIN
SELECT id,NAME FROM student ORDER BY sex
1
2

# 删除冗余和重复的索引

SHOW INDEX FROM student
1

#创建索引index_name

ALTER TABLE student ADD INDEX index_name (NAME)
1

#删除student表的index_name索引

DROP INDEX index_name ON student ;
1

#修改表结果，删除student表的index_name索引

ALTER TABLE student DROP INDEX index_name ;
1

#主键会自动创建索引，删除主键索引

ALTER TABLE student DROP PRIMARY KEY ;
1

# 不要有超过5个以上的表连接

  • 关联的表个数越多，编译的时间和开销也就越大
  • 每次关联内存中都生成一个临时表
  • 应该把连接表拆开成较小的几个执行，可读性更高
  • 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
  • 阿里规范中，建议多表联查三张表以下

# inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

  • inner join 内连接，只保留两张表中完全匹配的结果集
  • left join会返回左表所有的行，即使在右表中没有匹配的记录
  • right join会返回右表所有的行，即使在左表中没有匹配的记录

理由：

  • 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
  • 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

# in子查询的优化

日常开发实现业务需求可以有两种方式实现：

  • 一种使用数据库SQL脚本实现
  • 一种使用程序实现

如需求：查询所有部门的所有员工：

#in子查询

SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
1

#这样写等价于：

#先查询部门表

SELECT id FROM tb_dept
1

#再由部门dept_id，查询tb_user的员工

SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id
1

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：

List<> resultSet;
for(int i=0;i<B.length;i++) {
for(int j=0;j<A.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
		}
	}

}
1
2
3
4
5
6
7
8
9
10

上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。

理由：

  • 数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿

# 数据库设计的三范式

# 概述

简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式,也是作为数据库 设计的一些规则. 关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。所以有时为了提高运行效率，可以让数据冗余.

# 1NF的定义为：符合1NF的关系中的每个属性都不可再分

# 2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖,也就是说,表里的每个字段都要依赖于主键

第一步：找出数据表中所有的码。 第二步：根据第一步所得到的码，找出所有的主属性。 第三步：数据表中，除去所有的主属性，剩下的就都是非主属性了。 第四步：查看是否存在非主属性对码的部分函数依赖

# 3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖

就是指没个属性都跟主键有直接关系而不是间接关系。 像：a-->b-->c 属性之间含有这样的关系，是不符合第三范式的。 比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话） 这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话) 这样的表结构，我们应该拆开来，如下。 （学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

# 总结

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

← 基础函数 →