MySQL -senior
Senior MySQL
Published: 2020-05-25

The article contains the senior knowledge of MySQL…

绪论以及概念

1、Mysql单表瓶颈500万

2、MySQL单库瓶颈5000万

3、int本身长度为4，如果为null的话长度为5（这是int最长的长度了）

4、Varchar如果是gbk那么一个字符占两位，如果是utf8那么一个字符占三位，又因为是varchar（动态），所以在乘相应的倍数之后还要再加2.

5、CAP理论（C：强一致性、A：可用性、P：分区容错性）（最多只能保证其中的两个），一般电商网站可用性要高于强一致性，举个例子：我关心商品能不能下单要高于商品现在点赞的人数有多少，点赞人数的一致性之后可以修复，因此会选择AP而不是CP。

GPL是很严格的开源协议，linux用的也是这个。

注意在设计sql表的时候要遵循命名规范

文件位置

配置文件位置

中文乱码问题

步骤：

  1. 修改mysql配置文件；

  2. 修改已经存在的库或表的字符集

  3. 如果表中已经出现乱码数据，应该把表删了或者更新一下

用户

查看MySQL中的user

Select * from mysql.user\G;

创建用户

默认创建的是远程用户，因此该账户类型不是localhost而是‘%’，表示远程用户，所有远程主机都可以访问

Create user user_name identified by user_password;

或者使用mysql_native_password认证：

Create user user_name identified with mysql_native_password by user_password;

删除用户

Drop user ‘username’@’host’;

授权

案例：

**命令:**GRANT privileges ON databasename.tablename TO ‘username’@‘host’

说明:
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
**例子:**
GRANT SELECT, INSERT ON test.user TO ‘pig’@'%';
GRANT ALL ON *.* TO ‘pig’@'%';
GRANT ALL ON maindataplus.* TO ‘pig’@'%';
**注意:**
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO ‘username’@‘host’ WITH GRANT OPTION;

查看授权

Show grants for ‘username’@’host’;

撤销权限

Revoke privilege_names on db_name.table_name to from ‘username’@’host’;

密码

修改当前用户密码

Set password = password(“123456”);

Set password for ‘username’@‘host’=password(‘new_password’);

修改某个用户的密码

Update mysql.user set password = password(“123456”) where user = “root”;

Flush privileges;

Group by的坑

这个错误很明显，但是还是要注意

正确做法：关联一下

Sql_mode

我们来查看一下造成上面group by出错的原因：ONLY_FULL_GROUP_BY

开发的时候把测试数据库的sql_mode拷贝到本地开发库，这样测试跟开发最后出来的结果就一样了，不会出现开发可以，测试不行的情况。

类似的还有ORACLE这个字段，表示oracle遵循的sql_mode我MySQL也遵循

Mysql逻辑架构

配置缓存

这里缓冲跟缓存的区别：缓存是读数据的，缓冲是写数据的

配置文件中开启缓存后，还要再开启profiling才能使用缓存。如果要查看执行计划，执行命令show profiles;

查看sql执行命令时候的执行计划、cpu等详细信息

注意：show profile cpu,block io for query之后还需要加上之前show profiles命令显示的query_id才可以，如下：

之后就可以出现所有的执行计划了：

之后我们再次执行这条sql语句，我们发现他直接用缓存返回数据：

注意，这回我们使用效果相同但是表达不同的sql语句再次查询，结果发现缓存没有命中，因为缓存中key就是sql语句，value就是查询结果，key不一样意味着缓存没有命中。

机器自动优化之后的sql

MySQL存储引擎

对于Memory引擎，现在都用redis了。

查看存储引擎列表

MyISAM和InnoDB的区别★

一般来讲企业里不会设置外键，因为表之间的关系太复杂的话之后改动数据会非常麻烦。实际工作的时候用的是Innodb

行锁表锁

行锁：各行之间的修改不受限制；表锁：同一时间只能改表中的一行。MySQL中会出现死锁，一般只有行锁会出现死锁，而表锁不会，因为表锁范围大。

我们建表默认InnoDB引擎，因为难免会有高并发

CSV

逗号分隔存储格式

支付宝转账之后一般钱不会立刻转过去，只是前台显示已经转过去了，实际上真正转账是需要时延的，而在一段时间后会有一个对账的操作，这个时候对账的数据一般就以csv格式存储，之后再把它读到数据库进行对比看看没有问题。

CSV引擎

Csv格式的文件可以直接从该引擎的数据库下载下来而不需要编写读文件、写文件的代码，而且外部的csv文件也可以直接存储到数据库，因此也省去了sql更新、插入、修改等语句，非常方便。

一般报表系统、文本编辑这些都可以用这个引擎。

Federated引擎（联合引擎）

如图有两台机器上面分别有表A和表B，现在想要这两张表联合查询，这个时候用memory引擎就可以在其中一台机器上面创建另一张表的快捷方式（类似于linux的ln -s），这个时候即可完成关联查询。

注意：只能完成简单的联合查询，像什么分组、筛选等复杂联合查询就不行了，在其中一台机器上固化web页面也是不行的。而且不能下载上传数据毕竟它是一个快捷方式，而且这种方法效率太低。

说明这种引擎就是应急用的。

如果真的要完成一些复杂功能一般情况会再来一台机器做数据中心，比方说每天把多台机器上的数据做一个汇总到我这一台数据中心机器上，如下：

一般nosql数据库，大数据分析都是这么干的。都是先汇总，后清洗，后分析生成报表。

索引优化

主键会自动建索引，关联字段也会自动建索引，等到sql变慢的时候再有针对性的建索引。

注意，以上四块优化中索引建立效果最好!

简介

索引都是存在硬盘里的，MySQL一个文件存数据，一个文件存索引

索引结构

B树

1、数据

2、向下的指针

3、指向数据的指针

B+树

第一层找到28之后没有指针，到下一层还是没有指针，一直到最后一层才有指针，之后才返回指针指向数据。

1、数据

2、向下的指针

B树 VS B+树

B树一次可以加载两块磁盘块

由于索引需要读到内存里面才能起效，所以B树的时候我们读98就会不断发生内存中的缺页情况，因此需要频繁IO从磁盘中读取缺少的页，比方说在上面的情况下就发生了两次IO。

B+树一个节点就比B树多1/3，所以它一次能加载三块磁盘块

这里B+树在找99的时候就发生了一次IO。

时间复杂度

跳表（每隔一段数据用一个指针指一下，并把这些指针存储在一张表里），如下：

由于二叉树最坏情况就跟链表一样了，因此MySQL选择了平衡树。

聚簇索引与非聚簇索引

聚簇从上到下排好序了，非聚簇是分散的，比方说名字这个字段它就是无序排列分散到各处的。

索引分类

按照索引种类分还可以分成B-TREE索引和HASH索引

B-TREE索引的特点：

  • B-TREE以B+树结构存储数据，大大加快了数据的查询速度
  • B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）

B-TREE索引使用场景：

  • 全值匹配的查询SQL，如 where act_id= '1111_act'
  • 联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
  • 匹配模糊查询的前匹配，如where act_name like '11_act%'
  • 匹配范围值的SQL查询，如where act_date > '9865123547215'（not in和<>无法使用索引）
  • 覆盖索引的SQL查询，就是说select出来的字段都建立了索引

如何使用B-TREE索引：

CREATE TABLE act_info(
    id BIGINT NOT NULL AUTO_INCREMENT,
    act_id VARCHAR(50) NOT NULL COMMENT "活动id",
    act_name VARCHAR(50) NOT NULL COMMENT "活动名称",
    act_date datetime NOT NULL,
    PRIMARY KEY(id),
    KEY idx_actid_name(act_id,act_name) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ROW_FORMAT=COMPACT COMMENT "活动记录表";

HASH的特点：

  • Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引
  • 存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询
  • Hash索引无法用于排序
  • Hash不适用于区分度小的列上，如性别字段

如何使用HASH索引：

CREATE TABLE act_info(
    id BIGINT NOT NULL AUTO_INCREMENT,
    act_id VARCHAR(50) NOT NULL COMMENT "活动id",
    act_name VARCHAR(50) NOT NULL COMMENT "活动名称",
    act_date datetime NOT NULL,
    PRIMARY KEY(id),
    KEY idx_actid_name(act_id,act_name) USING HASH
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ROW_FORMAT=COMPACT COMMENT "活动记录表";

基本语法

创建（使用create字段）

key表示最普通的索引，主要是用来加快查询速度的

创建单值索引

Create index index_name on table_name(column)；

创建唯一索引

Create unique index index_name on table_name(column);

创建复合索引

Create index index_name1_name2… on table_name(column1,column2,…);

注意复合索引是有顺序的，因此在Seq_in_index这一字段下是1、2、3，表示写该索引的时候要按照这样的顺序去写字段。

创建（使用alter字段）

删除

Drop index index_name1、index_name2、… on table_name;

查看

Show index from table_name\G;

在information_schema(元数据库)中查看更详细的索引信息

对应的statistic统计表就是

哪些情况需要创建索引

如果有5个字段加索引，由于MySQL只会选择一个索引，因此单键索引的话，就算5个字段都加了索引，它最后也还是只会选择其中一个，但是如果是组合索引的话就5个都可以用到了。所以组合索引性价比更高。

Group by比order by更加消耗性能，因为它先排序后分组。

哪些情况不需要创建索引

表记录太少没必要建立索引。

由于建索引之后写操作会变慢，所以经常增删改的就不要建索引了。

Where里用不到的字段建了索引也浪费。

过滤性不好的例如性别这些的也别建立索引。

MYSQL索引中的USING BTREE 和 USING HASH

  • B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）
  • Hash索引无法用于排序，不适用于区分度小的列上，如性别字段

参考：https://blog.csdn.net/m0_46864744/article/details/113635073

性能分析

Mysql原则

除了or这种查询语句之外mysql只会选择一个它认为最优的索引使用

各字段解释

重点：

Id（用于分析读取顺序）

关于查询趟数

表1要关联表2的数据再关联表3的数据最终才能查到数据，那么找第一行数据的时候是需要表1、2、3都走一遍的，之后三表关联的结果就会出来，所以找第二行数据的时候，就不需要再走表2表3了，只需要走表1。这样来一遍就叫做一趟查询。

所以id显示为不同数字的就表示不是同一趟查询，同理显示同一数字的表示同一趟查询。趟数越少越好，也就是id全都是1那就是最好的。

Select_type

Simple

Primary和derived

Subquery和dependent subquery

Subquery:

Dependent subquery:

主要区别：where id = ()和where id in ()

Uncacheable subquery

系统变量可能随时会变，所以在这种情况下会被MySQL认为是不可缓存的subquery

Union和union result

Type

All

这里我们看到all是红色的，表示如果出现这个字段的话说明查询需要查全表，效率极低，需要建立索引去处理。如下：

Index

Index是橙色的，表示where后面没有用到设置为索引的字段，只是select或者from这些字段中用到索引了，如果是像这种覆盖索引或者利用索引进行了排序分组，那么它的效率还是非常低的，需要想办法优化，在后面的筛选条件中用到索引。

Range

Range是黄色的，表示索引字段用在了范围查询，这种情况效率也是很低的，但是如果说我们就是想用这个字段做范围查询，那也没办法，只能这么用了，一切以需求为先。如下：

Const

等号后面的值是常量

Eq_ref

表示按照索引查出唯一一条数据

Ref

表示按照索引查出了多条数据

Index_merge(这个or很关键)

如果or两边的字段都用上了索引，那么MySQL会将这两个索引合并，显示index_merge，两个索引都能用。

注意：where语句里面如果带有or条件，myisam表能用到索引，innodb不行。参考：https://www.cnblogs.com/microtiger/p/9075242.html

因此应该尽可能使用union来代替or。

Ref_or_null(这里也是or)

这种情况下Or两边的字段是一样的，只不过只需要任意一个为空即可，这时候会显示ref_or_null。

Index_subquery

Unique_subquery

Possible_keys和key

Possible_keys列出可能用到的索引

Key列出被实际使用的索引

Key_len

这一块主要用在复合索引上，查看复合索引中索引的命中情况，数值越大越好。

解释：

注意，int本身长度为4，如果为null的话长度为5（这是int最长的长度了）

Varchar如果是gbk那么一个字符占两位，如果是utf8那么一个字符占三位，又因为是varchar（动态），所以在乘相应的倍数之后还要再加2.

Rows

显示搜索时被扫描的物理行数，越少越好

Extra

Using filesort

该字段意为文件排序（手工排序，很慢），为红色，表示order by没用上索引。我们知道用上索引之后排序会很快

这种情况叫“要你命3000”，超级慢!!

优化后：

Using temporary

这个字段也是红的，表示group by没用上索引，我们知道group by包含order by，所以这个比order by更慢！

我们看到这种情况在using temporary之后还带了一个using filesort。

要你命30000！

优化之后两个字段同时消失，效果：

Using join buffer

该字段也是红色的，意为join字段用上buffer缓冲了，表示关联字段没用上索引

解决方法：以上面为例，我们应该给emp.deptno加索引。

Impossible where

这个字段虽然是黑色的，但也是不好的，意为sql写错了。

Using index、using where

这些字段是好的

Select tables optimized away

这个字段意为用上了优化

Table、partitions、ref、filtered

有可能是真实的表，也有可能是我们起的别名，还有可能是衍生表（查询出来的结果作为一个虚拟表存在）

实际开发中处理索引

1、查询索引名

2、取出索引名

3、怎么把字符串转换成sql

查询索引

还是从information_schema元数据库中查看statistics表

注意，我们删索引可以，但是主键索引不能删，所以我们的索引名应该将主键索引剔除出去（where index_name != ‘PRIMARY’）.

此外，我们注意看它的index_name是有重复的，因此我们取后面那一列seq_in_index为1的数据即可查询出对应表包含哪些索引。

实战分析索引

批量导入数据

这里涉及到MySQL主从：主机将所有写操作写到一个二进制日志叫做bin.log而从机去读这个日志，主机做了什么事情，从机也做什么事情

但是如果主机写了一个函数获取当前时间，那么从机再去执行这个函数获取当前时间，这两个时间必定不一致，这会造成主从不一致问题，为了避免这个问题MySQL禁止用户创建函数，所以就有了上图中log_bin_trust_function_creators=OFF

但是在这里我们要批量插入数据就必须写函数，因此要将这个参数设置为on

生成随机字符串：

生成随机数：

注意：但凡是批量输入数据、导数据、操作数据的时候，要写存储过程

往员工表插入数据

往部门表插入数据

批量删除索引

Mysql游标

用于循环集合

CURSOR 游标

FETCH xxx into xxx

游标每fetch一次指针向下走一格，直到游标指向数据为空为止：

论mysql执行一个字符串

PREPARE预编译之后execute执行

分析索引

全值匹配我最爱，建立复合索引

注意：这里的SQL_NO_CACHE表示查询的时候排除缓存的影响

建立索引之前

建立索引之后

建立组合索引

效果显著啊！

注意：如果两个字段中只有一个建立了索引，那么就命中那一个，但是如果两个都没有单独建立索引（注意这里就算某个字段之前建立过组合索引，但是没有单独建立索引的话也不会命中索引），这个时候就一个也不会命中了。

复合索引最佳左前缀法则

复合索引是分层的，比方说上面age为第一层，那么age有多少子节点对应的deptid就有多少颗树，在age命中之后跳到第二层deptid，命中之后再跳到name层。

所以如果这个层级断裂了，比方说deptid层没了，那么name这一层就没法用了。同理如果name层没了，那整个索引就都没法用了。

给索引加函数，加计算，加类型转换会造成索引失效

用于范围查询的索引右边添加的索引失效

这里有个一本身也为索引的字段deptid，但是它被用在了范围查询，所以它右边的这个name索引字段就会失效，解决方法：建立索引的时候把deptid字段放在最后面

这里我们发现可选索引有两个，MySQL选择了deptid在最后的那个

索引遇到“<>”（不等于号）时失效

注意：工作中如果没有特殊原因不要写不等于号

索引遇到IS NOT NULL失效

IS NULL是可以用索引的，但是IS NOT NULL失效

Like查询条件最前面有%时索引失效

例如：like ‘%abc%’

由于复合索引的原理，一般英文的字段按照a-z的顺序排列，但是如果连首字母都无法确定那么就显然会失效。

等号右边的值做类型转换索引失效

刚才上面讲到等号左边的变量做计算、类型转换、加函数等操作都会使得索引失效。

这里的name是varchar类型，123是int类型，MySQL自动给123做了类型转换，使得索引失效。

场景：Java代码中使用对象直接作为结果去MySQL中查找，可能对象和MySQL中那个字段类型不匹配，这个时候MySQL就会触发类型转换使索引失效

调整sql顺序（涉及sql优化器）

以上两条sql效果一样，只是语句顺序发生了修改，但是这两条语句照样用上了索引，原因是MySQL有优化器，优化器的效果就是在不改变sql语句的前提下调整sql顺序，所以它还是可以用到索引的。

总结

关联、子查询索引优化

非inner join

没做关联索引优化的时候出现Using join buffer。这时他做全盘扫描，一共需要扫描20*20=400行数据！

两表关联查询是有顺序的，分驱动表和被驱动表，其中驱动表无法避免全盘扫描，因此建索引也没用，只有被驱动表可以建索引优化。

做了索引之后需要扫描20*1=20行数据

Inner join

但凡是inner join，MySQL自己选择驱动表和被驱动表

Straight_join（手动设置驱动表与被驱动表）

效果跟inner join一样，但是它可以指定谁为驱动表，谁为被驱动表。如下：

这个时候straight_join前面的就为驱动表，后面的为被驱动表。

大表关联小表

这个时候肯定是选择小表为驱动表，因为扫描的物理行数少

比较四种join写法的优劣

第一名：

第二名：

第三名：

第四名：

解释：

第三第四名的表A和表B关联之后生成了一张虚拟表，而这张虚拟表被作为了被驱动表，**而被驱动表是建不了索引的！**所以这里相当于浪费了被驱动表优势。

这里为什么第三名只有两个all而第四名有三个all，是因为第四名把子查询放在left join后面的最后了，而第三名left join后面先放了一个子查询，后面又放了一个正常的能建索引的被驱动表。

避免不了子查询时的优化案例

上面那种查询既用到了is not null还用到not in

还用到了子查询。因此效率极其低下。

这个时候我们想到了这个算法根本目的在于找一个表里有但另一个表里没有的数据，想到了使用left join来优化。如下：

这个算法一趟查询，而且用到了索引，效率非常高

总结

排序分组优化

必须加过滤条件

注意：但凡用到了order by，一定要搭配过滤条件，实在不行用limit也可以，不然using filesort去不掉

过滤条件和排序先后顺序可互换

过滤条件在前面，排序照样用到了索引

Order by是否用上索引就看最后一列指标extra

这里我们给age、deptid、name三个字段按照顺序加了复合索引，我们发现key_len是5，是因为key_len只记录where后面用上索引的字段，不记录order by后面的，但事实上这里的order by中的两个字段都用上了索引，所以看order by是否用上索引应该看最后一列指标是否消除了using filesort

Order by后面有一个字段跟所建索引对不上

这个时候想要这两个order by后面的字段用上索引是不可能的，所以需要再创建一个索引，如下：

Order by后面字段都在复合索引里但是顺序调换

这种情况也是不行的，要注意MySQL是在不改变结果的情况下进行优化，这里order by字段都换了，结果必定不一样，所以MySQL不会进行优化。这种情况也是只能再建立一个新的复合索引

Where和order by后字段顺序与索引对应字段顺序不一致

这也是不行的

有过滤，字段顺序正确但有asc、desc情况下的索引

要么都降序要么都升序情况下索引正常

一个降序一个升序，出现using filesort

总结（上）

1、无过滤，不索引；

2、顺序错，必using filesort

3、方向反，必排序

索引的选择

首先这个案例empno用到了范围查询，所以后面那个order by里面的索引肯定会失效，所以这个一块只能选择age_name（舍弃empno）或者age_empno，测试结果发现使用age_name时using filesort被优化了，但是rows有四万多行，使用age_empno时，虽然有using filesort，但是rows只有40多行，最终MySQL选择了age_empno

Filesort的两种算法：双路排序和单路排序

在避免不了Using filesort的时候MySQL会选用这两种算法中的一个。那么这个时候我们就可以调整下面两个字段的参数使得filesort更快。

单路排序比双路排序要快得多，因为单路是在内存中排序

**双路：**先将所有数据取出，再进行排序；

**单路：**取一个数据排一下，取一个数据排一下，直到所有数据都取出来后，自然也就排好序了，而且是在内存中排的序，因此内存要足够大。

设置sort_buffer_size与max_length_for_sort_data

Sort_buffer_size是指单个sql 1M-8M，已经非常大了；

Max_length_for_sort_data：单路排序的时候我们需要从磁盘取一次数据并做一次排序，那这个字段是指每一次取数据的大小是多少，一般设置1024-8192。

Group by关键字优化

Group by就算没有过滤条件用到索引，也可以直接使用索引

总结（下）

1、用到Order by和group by索引且是复合索引的时候各字段与我们创建的索引的各字段必须一一对应，不然很可能索引就失效了。

2、order by和group by 后面如果有多个字段而且某些字段已经有索引了，那么就不需要再全部合起来建一个复合索引，只需要把没有建索引的几个字段建立索引即可，**因为order by、group by后面但凡有索引就能用，是没有限制条件的。**例如：

这两个字段中id已经有主键索引了，就不需要再建了，只需建立deptName这个字段的索引即可

覆盖索引（最后的索引手段）

这一块有经验技巧可言，如下：

案例1

这个例子有两个sql语句，第一句sql在where后用了age索引但是用了”<>”不等号，导致了age索引失效，所以做了第二句sql优化，在select后面加了个id字段，这个字段也是索引，这个操作之后变成了using index，也就是覆盖索引，type由all变成了range，至少优化了一点点。

*注意：这里select * 和select id,name…是不同的，select 没有用到覆盖索引！

案例2

这里用了语句like ‘%abc’，索引又失效了。

优化如下：

将select 中的展开之后但凡有索引字段就会用到覆盖索引。虽然效率只提升了一点点。

总结

从今以后写sql不再写select *，而是具体写出相应字段。

查询截取分析

慢查询日志

日志分析工具mysqldumpslow

At表示平均查询时间，sql语句相同但是里面参数不同则被归为一类。

主要就用以上这几个参数，其中-s c：查询次数，-s t：查询时间，-t NUM：选择前NUM个数据，-a：默认将数字替换成N，将字符串替换成S，写上-a之后可以取消这些替换，将所有数据显示出来

语法

Mysqldumpslow [参数] [日志文件]

全局日志

一般这种级别的不会去开，除非出现诡异情况才会开。该功能可以记录对sql的所有操作。

配置启用

编码启用

选用file记录日志

选用table记录日志

MySQL进程以及杀死进程

语法：show processlist

类似于linux的ps -ef

在程序死锁的时候可以杀掉部分MySQL进程

杀死语法：kill id号

Show Profile(上面查看sql执行计划时用过)

Show profile cpu,block io for query除了cpu、block io还可以查以下参数：

之后会出现类似这样的结果：

如果在这些结果里面出现：

这四条，说明sql语句有严重问题，需要优化！

就拿creating tmp table来讲，首先group by分组之前先排序，之后还会创建临时表，最后还要把临时表删了。如果查询的数据有上百万条，那我们把这上百万条数据先拷贝到临时表再展现给客户最后再删临时表必定发生大量性能损耗，如下：

可见copying to tmp table非常损耗性能

锁机制

锁分类

锁粒度

  • 表锁

    表级读锁：

    LOCK TABLE user_info READ;

    表级写锁：

    LOCK TABLE user_info WRITE;

  • 行锁

  • 页锁

加锁机制

  • 乐观锁
  • 悲观锁

兼容性

  • 共享锁

    共享锁（Shared Lock）针对行锁，主要为了支持并发读场景，语法：

    SELECT * FROM T WHERE id = 1 LOCK IN SHARE MODE;

  • 排他锁（互斥锁）

    排他锁（Exclusive Lock）针对行锁，主要为了支持并发写场景，语法：

    SELECT * FROM T WHERE id = 1 FOR UPDATE;

加锁模式

  • 记录锁

  • gap锁（间隙锁）

  • next-key锁（临建锁）

    原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间

    原则2：查找过程中访问到的对象才会加锁

    优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁

    优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock退化为间隙锁

    注意：唯一索引上的范围查询会访问到不满足条件的第一个值为止

    参考：https://www.likecs.com/show-204851943.html（MySQL锁 - 间隙锁实战）

  • 意向锁

    意向锁（Intention Lock）针对表锁，无法手动创建

  • 插入意向锁

参考：https://www.jianshu.com/p/478bc84a7721（MySQL记录锁、间隙锁、临键锁（Next-Key Locks）详解）、https://blog.csdn.net/songzehao/article/details/124074013（MySQL的共享锁、排他锁、意向锁）

概述

表锁（主要是myisam，偏读）

查看锁

Show open tables;

看 in_use这一列，为0表示没有锁

上锁

解锁

Unlock tables；

读锁（共享锁）

共享锁可以和其他锁共存

特点

假设有两个客户端c1和c2以及表stu

如果c1把stu锁了，那么：

c1可以读stu，但是不能改stu，也不能读其他的表。

c2可以读stu，也可以读其他表，但是当修改stu表的时候被阻塞，只有当c1释放了锁之后，c2的修改操作才被执行。

写锁（排他锁）

排他锁和其他锁不能共存

特点

假设有两个客户端c1和c2以及表stu

如果c1把stu锁了，那么：

c1可以查stu，可以改stu，但是不可以查别的表。

c2可以正常对其它表操作，但是查stu表的时候被阻塞，连查都被阻塞，改就更不用说了。

分析表锁命令（show status like ‘%table%’）

这里的两个参数主要看table_locks_waited，这个参数表示被阻塞的次数，table_locks_im/mysql_seniorte表示顺利获得锁的次数。因此主要看被阻塞的这个参数。

此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎，因为写锁之后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

所以myisam要偏读而不是偏写，如果来写的话直接写锁，那别的客户端连读都会被阻塞，更别说更新操作了。

小结

简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞。

注意点：

Myisam要偏读

行锁（主要是innodb，偏写）

在关闭auto_commit之后，一个客户端修改某一行数据是可以的，但是另外一个客户端修改同一行数据就会被阻塞，直到上一个客户端手动commit之后该客户端才会被执行，之后该行的数据将会被后面那个客户端修改的数据覆盖。

但是如果两个客户端修改不同行的数据是互不影响的。

如何锁定一行

首先关闭auto_commit

语法：

首先Begin;

之后在sql语句最后加for update; 例如：select * from dept where address=’myadd’ for update;

这个时候另外一个客户端想要修改address=’myadd’这一行就会被阻塞，直到上一个客户端手动commit才能恢复执行

注意：mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁则可以像上面那样使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

分析innodb表锁、innodb行锁命令

分析innodb表锁命令：show variables like 'innodb_table_locks';、show global variables like 'innodb_table_locks';

分析innodb行锁命令：show status like ‘innodb_row_lock%’;、show global status like ‘innodb_row_lock%’;

如果出现问题的话，一般都用show profile分析

页锁

索引失效行锁变表锁

首先关闭auto_commit，设置某一列为索引(这里简称b列)，然后c1客户端进行了update操作，其中where条件用到了索引，并且等号右边进行了类型转换，即发生了索引失效，这个时候c2客户端无论改哪一行的数据都会发生阻塞，因为c1在事务期间发生了索引失效，导致行锁变成了表锁。直到c1手动commit之后才可恢复

间隙锁的危害

举个例子，某个表id值有1、3、4、5、6，我需要修改id>1 and id<6的所有值，这个时候虽然id=2的数据不存在，但innodb仍旧给我锁了2，导致另外一个客户端想要对id=2的行做操作，比方说插入操作的时候被阻塞（事实上id>1 and id<6的所有行都会被阻塞），直到前一个客户端手动commit之后，id=2的行才会被执行，并且这个时候id=2的行是不会被之前的语句（也就是那个id>1 and id<6的范围的行进行操作的语句）所影响的。

总结

视图

除了基本的优点外，视图还可以用于Java程序与MySQL的解耦。例如：在Java程序中我们直接select

  • from view_name，而当需求改变的时候不需要改Java代码，只需要更改视图即可

主从复制

主机只能有一台，而从机可以多台。

Redis中从机拜主机，拿到rdb直接从头开始复制。但是MySQL不一样，MySQL从接入点开始复制，主机之前的事情与从机无关。

**过程：主机将写操作信息全写在binary log文件中，从机读取该文件，再写入自己的relay log，之后再从relay log读到从机里面执行。**这里发生了很多次io，会导致主从的数据有时延，因此在主机写入数据后，从机不能马上就去读数据，可以先查一些别的东西，或者做点日志，再去查数据。

注意：redis从机不能写，但是MySQL从机只要有有写权限的用户就可以写操作。那么这一块的解决办法是限制从机权限。

主从搭建（配置主机从机的config）

具体的主机从机配置文件写在linux中。

注意：

1、改配置之前最好备份一下。

2、跟redis不一样，MySQL的配置文件直接配置在原配置文件位置。

Binlog_format=STATEMENT(默认)

Statement

该模式下binlog日志中记录所有的写操作，但是有一点，这里有个字段为now().

这里的time=now()从主机复制到从机的时候时间必然就不相同，会造成主从不一致，为了解决这个问题，有了row模式。

Row

不再记录写操作sql，而是记录执行完sql之后每一行的改变（比方说ceo字段变了，那就要记录每一行ceo的改变），这就避免了函数执行造成主从不一致的情况。

但是row模式也有缺点，就是当我们写update语句不写where导致全表更新的时候，如果表太大有几百万行，那么这种模式下binlog就要记录几百万行的改变，然后到从机下还得执行几百万行。这个问题显而易见了，就是效率有问题，但凡有大批量更新的时候，这个问题就会凸显出来。

Mixed

Mix模式原理：当写操作有函数，比方说now()这种的，那么就切到row模式，如果没有那就用statement这种模式，来回切。

但是它也有缺点，当遇到sql中有系统变量时，它也没办法避免主从不一致。

主机上创建用户（权限不需要很高，只需要能复制主从即可）

不可能来一个机器我就给你复制，需要授权

语法：

Grant replication slave on . to ‘slave190401’@’从机数据库ip’ identified by ‘123456’;

或者

Grant replication slave on . to ‘slave190401’@’%’ identified by ‘123456’;

这里%表示所有远程用户

查看master状态

语法：

Show master status;

1、File表示新增的logbin日志；

2、position表示就从107这个位置开始复制

3、binlog_do_db表示指定的复制的数据库

4、binlog_ignore_db表示指定的不要复制的数据库

注意：这个时候主机器一定不要执行sql语句，比方说创建数据库之类的操作，一旦执行了，上面这些参数中的position是会变化的

从机上配置需要复制的主机

上面的第一条截取了一半的很长的命令：

Change master to master_host=’主机ip地址’，

Master_user=’上面主机上创建的那个用户slave190401’，

Master_password=’上面主机上创建的那个用户的密码123456’，

Master_log_file=’mysqlbin.具体数字(具体数字通过show master status就可以知道)’，

Master_log_pos=接入点position，也是通过show master status知道的;

注意：

在写master_host的时候，主机的ip地址一定要写对，要和从机在一个网段，如下：

开启从机

这一步之后应该是开起来了，为了确认还需要查看从机状态

查看从机状态

这两块是yes，则搭起来了

如果出问题，首先看看后面的error，一般情况就是防火墙的问题，或者是position接入点出错了（看到的是缓存中的position，但是实际上它已经变了）

创建数据库

至此，我们可以创建数据库了，按照之前配置的需要复制的数据库为mydb_190401，我们创建名为这个的数据库

这个时候主从机就都有这个库了。

最后，如果出现问题先stop slave，后reset master

这两个命令可以重新配置主从。

ShardingSphere（Sharding-JDBC、Sharding-Proxy、Sharding-Sidecar）

参考：

https://blog.csdn.net/Curtisjia/article/details/126980797（Sharding-JDBC简单使用）、https://it-blog-cn.com/blogs/db/sharding_jdbc.html（深入理解Sharding-JDBC）、https://blog.csdn.net/crazymakercircle/article/details/123420859（Sharding-JDBC 实战（史上最全））

https://blog.csdn.net/akenseren/article/details/127332868（Sharding-Jdbc实战之三：读写分离）、https://blog.csdn.net/akenseren/article/details/127350807（Sharding-Jdbc实战之四：分库分表）

https://blog.csdn.net/Kiven_ch/article/details/119087048（ShardingSphere基本介绍及核心概念）、https://blog.csdn.net/weixin_45976114/article/details/115249792（ShardingSphere学习）、https://www.cnblogs.com/mic112/p/15450285.html（分库分表利器之Sharding Sphere）、https://blog.csdn.net/weixin_43549578/article/details/106709343（Sharding Sphere学习）

https://blog.csdn.net/ShardingSphere/article/details/124030367（ShardingSphere-Proxy：从实际场景出发，快速上手）、https://blog.csdn.net/qq_44306975/article/details/121931274（分库分表利器sharding-proxy）

公司实战项目：data-hub、app-manager、warning-manager

Mycat

如果没有mycat，那么增删改查都是交给Java来判断到底是给哪个数据库。

而且这种情况下slave仍然可以插入数据，除非限制slave只有读的权限。即读写未分离。

有了mycat之后，数据库跟Java就解耦了，而且可以做到读写分离

原理：拦截

这里的schema.xml配置的逻辑库表示在逻辑上存在于mycat，但在物理上就是一台台机器上的真实的数据库

修改server.xml

为了与mysql进行区分，我们将mycat的user属性进行一些修改

修改schema.xml

这个地方写了进行主从复制的主机数据库mydb_190401

打开schema.xml之后我们发现从第6行到第32行都是不需要的，因此把他们删掉

需要给schema逻辑数据库配一个默认的节点，如下图标黑部分

之后就是配置mycat节点和主机以及从机，并且删除多余部分

验证数据库访问情况

主要访问一下主从机看看能不能连上。如果出错，就建立对应用户，一般情况下上图的“缺少的host”字段为“%”，即所有远程主机

开启mycat

用控制台启动的好处是它报错的时候我们能看到，而且他报的是Java错误

修改控制台名称

后面会大量进行表和库的配置修改，为了分工明确，我们将两个控制台更名为conf和bin

登录

这里的-u后面的用户名是server.xml中之前更改的那个用户名，我们改的是mycat，所以他就叫mycat，-p后面的密码也同理。

运维一般用9066这个端口，开发用8066，mycat默认端口8066.

区分到底是哪个机器的数据库

在主机的mytbl表中插入@@hostname主机名后，我们发现在windows中的主机的数据库和在linux中的从机的数据库的数据不一样。当我们切回mycat中再次查找发现它显示的是主机在windows中的数据库的表。如下（重点看id为3的数据）：

出现这种原因是因为读写分离没有搭建起来，即schema.xml中的balance字段

Balance配置读写分离

Schema.xml中的dataHost中的balance为：

1、0表示不配置读写分离

2、1表示在多台主机多台从机情况下，用于备用的那台主机以及它的从机也参与读的负载均衡，举个例子：双主双从模式下(M1为主机，M2为备用主机，注意这里M2还不是真正的主机，就跟皇帝和太子的关系一样，太子是没人理他的，只有皇帝死了，太子登基之后他才有所有权利。那么M1->S1,M2->S2，并且M1与M2互为主备)，这种情况下M2、S1、S2都参与读的负载均衡

3、2表示所有的读操作随机地在写主机与读从机上进行分发

4、3表示所有读都发到读从机上，而所有写主机都不参与读。显然这才是我们需要的读写分离模式

分库

切记分库需要两台机器上的数据库是没有任何数据的干净的库

举个例子，一般情况下客户表和订单表就可以分离，因为通常情况下我们需要登录才可以查订单，这个时候只需要在订单表加一个字段类似于id来识别用户就可以了，而不需要跟用户表进行关联，所以分表的时候就可以将这两个表分离在不同的机器上。

这里schema中加了一个table，而且dataNode设置为dn2，但是schema中的dataNode还是dn1，表示除了这个表，其它表仍旧使用dn1那个节点，而且给该节点配置的数据库也不一样了，因为原先那个数据库中是有表的，我们应该新建一个新的数据库。

原先写死的进行主从复制的主机数据库mydb_190401:

现在由于分表新增的数据库order190401，由于之前写死了主从复制的主机数据库，因此order190401不会再进行主从复制，如下：

修改schema.xml

建库

如果这个时候直接启动mycat显然会出错，因为没有order190401这个库。

因此建立分表所要存入的数据库order190401，这里由于该库不会进行主从复制，因此需要手动在两个机器上建库。

开启mycat建表

这里需要在mycat中建表那么mycat怎么知道要将表建在哪呢？原理还是拦截。通过schema中的配置知道customer表需要建立在节点dn2的主机上，而其他表则建在节点dn1的主机上

注意这个时候虽然不同的表被分配在不同的机器上，但是访问mycat的时候是可以访问全表的。

分表

就拿用户订单表举例，我们取一个用户的数据最好能从同一个数据库数据表中读取，因此最好按照用户id进行分表。仿照redis中分表操作，它是拿到id转换成hash码再取模最后再将数据分配到slot槽中。对应的mycat也应该这样分，若有两个节点，则对应id取2的模之后再进行分配，这样可以保证根据用户id平均分配了数据

修改schema.xml

修改rule.xml

哪个机器缺表就把哪个机器的表建好

在所有节点的机器上都建好表，比方说customer表需要分表，那么节点1和节点2都需要创建好这张表

插入数据

但凡在mycat中插入数据都需要写清楚字段名，像上面那个案例，我们是按照customer_id进行分表，那么如果不写明字段，mycat是找不到插入的值中customer_id对应的那个数值的，就会报错。

查找数据

我们发现id是乱序的

原因还是拦截，mycat在dn1和dn2分别返回数据之后union了一下。

因此这个就是所谓的跨库查询

小结

实际情况下如果有旧数据库，而我们需要将旧数据库中的某个表进行拆分，那我们应该先买好新的服务器，再配置mycat，再创建好对应表，再执行插入语句即可

join关联表分表

如果没有给关联的表分表，而直接进行关联，由于主表在两个节点dn1、dn2都有，因此mycat会将这个查询语句发给dn1、dn2节点，假设关联表在dn1中，那么dn1这个节点是会返回查询结果的，但是问题是dn2没有这个关联表，那么就会报错，导致所有数据都不返回。

ER表

关联表跟随前面那张已经分表的主表根据关联键分表，举个例子，order表已经分表，order_detail的order_id字段对应order的id字段，那么order_detail表根据order_id进行分表。

这种分发叫做ER表

修改schema.xml

跟上面一样，哪个机器缺表就在哪个机器里面建表

插入关联表数据

查询关联数据

全局表

相当于给每个数据库复制了一份，通过冗余保证可以进行关联

所以全局表一般不能是大数据表

修改schema.xml

跟上面一样，哪个机器缺表就在哪个机器创建表

全局序列(id)

由于某个表会被分到多个节点，那么id如果是自增序列会导致id重复。我们需要搭建全局序列保证id号不可能重复

本地文件方式

将存放id序列的文件放在mycat，来一个插入我赋给它一个id，这样可以保证id不重复，但是有个问题，如果主机挂了，那么这个文件就丢失了。因此不推荐。

时间戳方式

但凡从mycat主机这边过，我们取mycat主机的时间，时间戳不会重复，因为它会不断变大，要是主机挂了，从机上线的时候保证主机从机时间一致即可。

而且有一个优点，他不再需要取配置复杂的文件和配置项，直接用系统时间就可以了。

但是依旧是不推荐使用，因为一个id如果配时间戳那将是18位，有点浪费空间

数据库方式（推荐）

将序列存在一台机器的数据库中，每次比方说从数据库批量读出100个id，来一个序列我给他一个id，如果id用完了，再从数据库读，比方说第一次读1-100，那么第二次读101-200，这样就算mycat主机挂了，再来一个新的mycat，我依旧是继续上次读101-200，这样就不会冲突了。

使用方法
建库序列脚本

先建序列表

修改mycat配置

1、修改sequence_db_conf.properties文件

如果要配置linux的，那么在linux的数据库中我们还得执行一下建库序列脚本那一步

2、修改server.xml文件

这里0表示本地文件方式，1表示数据库方式，2表示时间戳方式

重启mycat

插入语句

Next value for MYCATSEQ_ORDERS表示从MYCATSEQ_ORDERS取下一个值

效果：

如果mycat挂了，那么刚才已经给过mycat100-199这一段的id了，现在给mycat200-299的id

效果：

自主生成方式

通过编写Java逻辑拼接id，这种方法相当于写死了id，再将id传回来。

也可以通过redis 的原子性incr来生成序列。

当然，我们更加推荐数据库的方式

可能出现的错误（centos6.8就出现过）

解决方案：

之后重启即可

Seata

参考：

https://www.cnblogs.com/crazymakercircle/p/14375424.html（详解分布式事务；详解seata）

https://seata.apache.org/zh-cn/docs/user/quickstart/（官网）

TiDB

在 NewSQL 数据库出现之前，一般采用单机数据库（比如 MySQL）作为存储，随着数据量的增加，分库分表是早晚面临的问题，即使有诸如 MyCat、ShardingJDBC 等优秀的中间件，分库分表还是给 RD 和 DBA 带来较高的成本；NewSQL 数据库出现后，由于它不仅有 NoSQL 对海量数据的管理存储能力、还支持传统关系数据库的 ACID 和 SQL，所以对业务开发来说，存储问题已经变得更加简单友好，进而可以更专注于业务本身。而 TiDB，正是 NewSQL 的一个杰出代表！

关于TiDB的安装部署请参考：https://blog.csdn.net/weixin_45730091/article/details/112321400（TiDB安装部署）

联合查询复习

思考题

论如何往数据库中插入100万行数据：

1、利用高级语言将多条数据拼接到insert into table_name values后面，争取在一条sql语句中能插多少插多少，这样效率高；

2、由于索引会使得曾删改数据变慢，因此在插入之前应该删除除主键以外的所有索引；

3、关闭自动提交，在插入所有数据之后手动提交；

4、多线程异步插入数据；

5、使用JDBC的executeBatch进行插入。

优化习题

1、

2、

我们发现这种情况下大表被MySQL自动设置成了驱动表（这里是因为小表有primary索引，MySQL想使用被驱动表的优势，即被驱动表索引，因此将小表设置为被驱动表），这不是我们希望的，我们希望的是小表能变成驱动表，这个时候就需要straight_join这个字段，效果跟inner join一样，但是他可以指定驱动表，如下：

这个时候dpet_b就被设置为驱动表了，emp_a就被设置为被驱动表，这是我们希望的。

之后我们建立索引，因为group by后面的id已经建立了索引，因此只需要给deptName建立索引即可。

效果如下：

3、

我们发现大表为驱动表，使用straight_join

建立索引

效果

4、

5、

这里我们只写id应该也可以，但是先来一个deptName分组再来id分组也许会更快

6、

7、（略难）

但是如果两个人的deptid和age相等的话，这里就有bug了，本来应该并列排名的人变成了没有并列的，因此还可以改进：

这里涉及到的组内排序在oracle里面是rank（） over（）这两个函数，这两个函数还可以实现并列第一名之后第二名就没了直接是第三名的情况