之前学习的数据库知识，主要是对表的增删改查（CRUD），并没有考虑对数据库做出优化，这章内容就是学习怎么优化数据库。

为什么要优化数据库？
当数据库中的数据大于 50W 的时候，在操作数据时，有没有做过优化就会产生比较大的影响，特别是千万级，亿级数据库，优化就显得尤为重要。

索引优化

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

一般来说，索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上，我们平常所说的索引，如果没有特别指明，都是指 B 树 结构组织的索引。在磁盘上表现为后缀.MYI（ MySQL index 的缩写）的文件。

一般建立索引的列不超过 6-7 个，太多会影响整个 MySQL 引擎性能。

一. MySQL 索引结构

BTREE: B树（Balance Tree 多路平衡查找树）
其他不常用的：
- Hash 索引
- full-text 全文索引
- R-Tree 索引

二. 索引的分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引：一个索引包含多个列。
使用工具可以很方便的建立索引，如Navicat。

三. 关键字 EXPLAIN

使用 EXPLAIN 关键字可以模拟执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句，分析你的查询语句或是表结构的性能瓶颈。

1. EXPLAIN 作用

查看表的读取顺序查看
查看数据读取操作的操作类型
查看哪些索引可以使用
查看哪些索引被实际使用
表之间的引用
每张表有多行被优化器查询

2. EXPLAIN 使用方法

EXPLAIN + SQL 语句
执行计划包含的信息如下图：

3. EXPLAIN 性能指标参数详解

id：说明每张表的执行顺序，id 越大执行越早，id 越小执行越晚，id 一样按照顺序从上往下执行
select_type：查询类型，主要用于区别普通查询 SIMPLE、联合查询 UNION、子查询 SUBQUERY 等复杂查询
- SIMPLE：简单查询，查询中不包含子查询 SUBQUERY 或者联合查询 UNION
- PRIMARY：主键查询
- SUBQUERY：子查询
- DERIVED：衍生表，在 FROM 列表中包含的子查询被标记为 DERIVED，MySQL 会递归执行这些子查询，把结果放到临时表里
- UNION：联合查询
- UNION RESULT
table：查询的是哪张表
type（重点）：查询时使用的类型(即索引类型)，性能从快到慢依次为 system > const > eq_ref > ref > range > index > ALL。一般来说，查询至少保证范围索引 range 级别，最好能到关联索引ref
- system：系统常量，只有一条记录
- const：常量，例如：查询条件是一个具体的值。WHERE age = 20（该字段必须已经建立索引）
- eq_ref（特殊关联索引）：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。例如：SELECT * FROM m ，e WHERE m.num = e.pid，m 表中只有一条数据，e表中的 pid 是索引键
- ref（关联索引）：非唯一性索引扫描，多表查询时尽量关联到索引，基本都能达到这个级别
- range（范围索引）:例如：select * from m where m.id < 10
- index（Full Index Scan）：用到了索引，只遍历索引树
- ALL（Full Table Scan）：全表查询，没有用到索引
- 复合索引中字段的位置会影响索引的类型，在建立时和使用时，应尽量考虑在用户应用查询时常用的排序方向和字段组合顺序
possible_keys：可能用到的索引
key：实际使用的索引
key_len：索引字段的最大可能长度。建库时 varchar 长度不要太长，影响效率。常见字段类型的 key_len：
- int：4
- varchar：长度 * 3+2（var自身可变长度）+1（运行null值）
- char：长度 * 3+1（运行null值）
ref：关联的索引字段是哪个
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数
Extra：额外信息
- Using filesort：避免出现的信息，说明排序没有充分利用索引
- Using temporary：避免出现的信息，说明分组没有充分利用索引
- Using index：使用索引
- Using where
- using join buffer
- impossible where

4. 索引失效

所有索引字段全部匹配速度最快
如果索引有多列，查询从索引最左前列开始并且不跳过索引中间列
不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效转而全表扫描
索引中范围（>, <等）条件右边的列全失效
尽量使用覆盖索引（只访问索引列的查询），减少SELECT*
使用不等于（!=, <, >）时会导致索引失效
NULL, NOT NULL 对索引有影响
LIKE 以通配符%开头会导致索引失效
字符串不加单引号'会导致索引失效
尽量不要用OR，否则会导致索引失效

使用数据库函数随机插入数据

【转】Mysql进阶学习Ⅱ，使用数据库函数随机插入数据

我们使用 MySQL 中的函数来进行随机数据的插入。函数分为两种：一种是有返回值的，用它来生成一些随机的单个数据；另一种是无返回值的，用它执行一些复杂数据库操作，比如自动生成表数据，对多张表的关联增删改查等。

以一个实例进行学习，使用的工具是 Navicat for Mysql。

1. 建表

建立两张表：

emp（员工表）
dept（部门表）

2. 开启数据库函数

在查询编辑器中执行下面的语句开始 MySQL 支持数据库函数：

显示状态

show variables like 'log_bin_trust_function_creators'

设置状态：值为 1 开启（ON），值为 0 关闭（OFF）

set global log_bin_trust_function_creators = 1

3. 编写随机生成函数

3.1 随机字符串函数

在查询编辑器中执行下面的语句：

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$

点击左侧函数刷新后得到下图的结果：
右键 rand_string 运行结果：输入参数 6，会随机生成一个6个字母的字符串，在本表中可用于各种名字

3.2 随机生成数字函数

在查询编辑器中执行下面的语句：

DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN 
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*20);
RETURN i;
END $$

点击左侧函数刷新后得到下图的结果：
右键 rand_num 运行结果：会随机生成 100~119 中的一个数,在本表中用于部门编号

4. 编写存储函数

4.1 随机存储部门表数据函数

在查询编辑器中执行下面的语句:

DELIMITER $$
CREATE PROCEDURE insert_dept(IN start_num INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0 ;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES ((start_num+i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

点击左侧函数刷新后得到下图的结果：
右键 inset_dept 运行结果：会向部门表里插入 20 条数据，deptno 从 101~120

4.2 随机存储员工表数据函数

在查询编辑器中执行下面的语句:

DELIMITER $$
CREATE PROCEDURE insert_emp(IN start_num INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT 
SET i = i + 1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (start_num+i,rand_string(6),'SALESMAN',0001,NOW(),rand_sal(),400,rand_num());
UNTIL i = max_num;
END REPEAT;
COMMIT;
END;

点击左侧函数刷新后得到下图的结果：

其中 rand_sal 函数（参考 rand_num）生成随机工资数。
右键 inset_emp 运行结果：输入参数 0, 500000，会向部门表里插入 50W 条数据。这里使用的是腾讯云数据库，耗时 01:01:57。参数 0 代表从 0 开始，500000 代表插入 50W 条数据

数据库锁机制初级

【转】Mysql进阶学习Ⅲ，数据库锁机制初级

在数据库中，数据是一种共享资源，为了保证数据并发访问的一致性、有效性，使用锁机制就很有必要。

数据库的事务

概念：逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部不成功。
四大特性
- 原子性：事务的操作要么全部成功，要么全部不成功
- 一致性：前后状态一致，例如：转账前后总金额不变
- 隔离性：多用户并发访问数据库时，每个用户的事务不被其他事务影响
- 持久性：事务一旦提交，数据库数据的改变是永久性的
事务不考虑隔离性引发的问题

脏读：一个事务读取了另一个事务未提交的数据。（针对 update）

例如：用户 A 将库存从 1 修改为 0，但是未提交，此时用户 B 查询了库存为 0，然后用户 A 执行失败回滚了数据，库存变为 1，用户 B 读到的数据（库存 0 ）就叫脏数据。

不可重复读：事务读取某一行数据时，多次读取结果不同，及读取了另一个事务已经提交的数据。（针对update）

例如：用户 A 先查询库存为 1，此时用户 B 修改库存为 0，这时用户 A 再次查询库存为 0，两次查询结果不一致。

虚读：事务读取到了别的事务插入的数据，导致前后读取不一致。事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据。（针对insert）

事务的隔离级别
- Serializable（串行化）：全部避免
- Repeatable read（可重复读）：避免脏读，不可重复读（Mysql默认）
- Read committed（读已提交）：可避免脏读（Oracle默认）
- Read Uncommitted（读未提交）：最低级别，都无法保证

悲观锁和乐观锁

悲观锁：悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
乐观锁：乐观锁不是数据库自带的，需要我们自己去实现，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

实现：

给数据表加一个版本(version)字段，每次操作成功都将 version + 1，如果要对那条记录进行操作（更新），则先判断此刻 version 的值是否与刚刚查询出来时的 version 的值相等——如果相等，则说明这段期间，没有其他程序对其进行操作则可以执行更新，反之则不执行。

select kc,version from sku where sku_id = ?
update sku set kc = kc -1,version = version + 1 where sku_id = ? and version = version

表锁（MyISAM 锁）

锁定一张表，一个用户操作完成之前其他用户都不能对该表操作。

读锁（Table Read Lock）：不会阻塞其他用户对同一表的读操作，但会阻塞对同一表的更新（Update）和插入（Insert）操作
- 加锁语法：LOCK TABLE 表名 READ
- 解锁语法：UNLOCk TABLES
写锁（Table Write Lock）：同时阻塞其他用户的所有操作
- 加锁语法：LOCK TABLE 表名 WRITE
- 解锁语法：UNLOCk TABLES
查询表锁争用情况：可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺情况

如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况。

行锁（InnoDB 锁）

锁定一行数据，一个用户操作完成之前其他用户都不能对该数据操作。

事务使用的就是行锁。

共享锁（读锁）：多个事务只能读数据不能改数据
- 语法：SQL 语句 + LOCK IN SHARE MODE
排他锁（写锁）：一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过 for update 和 lock in share mode 锁的方式查询数据，但可以直接通过 select …from … 查询数据，因为普通查询没有任何锁机制。
- 语法：SQL 语句 + FOR UPDATE
查询行锁争用情况：可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况
- 语法：show status like ‘innodb_row_lock%’
  
  如果 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高，则说明存在着较严重的表级锁争用情况。

默认的修改数据语句（update, delete, insert）都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果加排他锁可以使用select … for update 语句。

行锁实现方式（重点）

行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，才使用行级锁，否则使用表锁。

1、在不通过索引条件查询的时候，InnoDB 使用的是表锁，而不是行锁

2、访问不同行的记录，如果使用相同的索引键，会出现锁冲突。应用设计的时候要注意这一点

3、当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

举例来说，假如 emp 表中只有 101 条记录，其 empid 的值分别是 1,2,…,100,101，SQL语句 Select * from emp where empid > 100 for update 是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对 empid 大于 101（这些记录并不存在）的“间隙”加锁。

特别说明：InnoDB 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

下面这个例子假设 emp 表中只有 101 条记录，其 empid 的值分别是1,2,……,100,101。

标签：加锁,进阶,数据库,查询,索引,num,MySQL,数据
From： https://www.cnblogs.com/Jojo-L/p/16708589.html

MySQL进阶