常用命令
-- ------------------------------------------------------------ DDL 与"表"相关的操作 ------------------------------------
-- 更改表结构
alter table card add (`province` varchar(20) comment '省(编码)', `city` varchar(20) comment '市(编码)', `county` varchar(20) comment '区/县(编码)')
-- 添加表列
ALTER TABLE web_approve_record ADD COLUMN `source` TINYINT(4) DEFAULT 0 COMMENT '案件来源';
--删除表列
ALTER TABLE web_execute_punish DROP COLUMN `legal_description`;
-- 修改列的默认值
alter table web_cover_illegal alter column station_name set default '';
-- 修改表列类型
alter table zczhgl_web_dev.web_position_record modify `id` bigint(20);
ALTER TABLE web_approve_record MODIFY `evidence_number` VARCHAR(50) NOT NULL COMMENT '证据编号';
-- 修改表列名
alter table test change column address address1 varchar(30)
ALTER TABLE web_approve_registration CHANGE COLUMN `handle_person_time` `handle_person_time` BIGINT(20) DEFAULT 0 COMMENT '经办负责人处置时间';
-- 修改字段长度
ALTER TABLE promotion_data MODIFY `prize_name` VARCHAR(111) COMMENT '奖品名称' DEFAULT '';
-- 多字段一起修改
ALTER TABLE promotion_data MODIFY `prize_name` VARCHAR(244) COMMENT '奖品名称' DEFAULT '', MODIFY `city_code` INT(244) COMMENT '城市编码' DEFAULT 0;
-- 修改表名
alter table test rename test1;
-- 多条语句合并到一起：新增表列和新增索引两条语句写成一条语句
ALTER TABLE appeal_record ADD COLUMN `agencyNumber` varchar(36) DEFAULT NULL COMMENT '机构编号', ADD INDEX idx_agencyNumber ( `agencyNumber` )
-- ------------------------------------------------------------ DDL 与“索引”相关的操作 ------------------------------------
-- 创建普通索引
CREATE INDEX idx_plate ON web_overload_record (plate);
CREATE INDEX idx_city_sign ON card (city, `SIGN`);
-- 创建普通索引(推荐语法)
ALTER TABLE `virtualnumberbindlog` ADD INDEX idx_upBindTime ( `upbindTime` );
-- 创建组合索引(推荐语法)
ALTER TABLE `virtualnumberbindlog` ADD INDEX idx_status_upBindTime (`status`, `upbindTime`);
-- 多列建唯一索引
ALTER TABLE card ADD UNIQUE INDEX(province, city);
--删除索引
ALTER TABLE web_execute_punish DROP INDEX idx_caseNo;
-- ------------------------------------------------------------ DML 与“记录”相关的操作 ------------------------------------
-- insert
insert into driver_behalf_rule(`id`, `cityCode`, `areaCode`, `scopeSwitch`, `status`, `driverType`, `updateUserId`, `updateTime`,
`createuserId`,`createTime`, `deleteStatus`, `settingStatus`, `limitStatus`, `effectiveTime`, `effectiveEndTime`, `maxAge` ) value
(0, 2, null,1,1,3, 2, '2022-12-26 09:48:54', 2,'2022-12-26 09:48:01', 1,1,1, null, null, null);
-- 批量 insert
insert into driver_behalf_rule_param(`driverVipRuleId`, `paramKey`, `paramValue`, `createTime`, `updateTime`) values
(0, 'maxFreeOrderNumPerDay', 1, '2022-12-06 09:21:53','2022-12-06 09:21:53'),
(0, 'maxLocalDriverDay',1000, '2022-12-06 09:21:52','2022-12-06 09:21:52'),
(0, 'buyVipCity',1, '2022-12-06 09:21:54', '2022-12-06 09:21:55');
-- update set
update risk_target_conf set andOr = 1, updateId = 5333 where openStatus in (1,2);
-- 删除 delete
delete from chargingcityconfig where id in (1171,1174);
-- select操作
--分组查询
select max(id), cityCode from driver_behalf_rule group by cityCode order by cityCode asc;
-- 查询分组查最大id的数据(下面两句话都能实现同样的效果)
select * from configservice.driver_behalf_rule where id in (select max(id) from driver_behalf_rule group by cityCode) order by cityCode asc;
select * from configservice.driver_behalf_rule tb2 where id = (select max(id) from driver_behalf_rule tb1 where tb1.cityCode = tb2.cityCode) order by tb2.cityCode asc;
-- having的使用，where是在聚合前筛选的，having是对统计后的数据进行筛选的，having和group by 配合使用
select id, count(0) n, createUserId FROM configservice.label group by createUserId having n < 5 order by n desc;
-- 以id分组，把name字段的值打印在一行，逗号分隔(默认)，实现代码的join效果
select id,group_concat(name) from aa group by id;
-- ------------------------------------------------------------ 其他 ------------------------------------
-- 查看事物是否开启
SHOW VARIABLES LIKE 'event_scheduler';
-- 打印建表命令
SHOW CREATE TABLE learning_dev.im_message;
自增
使用 BIGINT 的自增类型作为主键的设计仅仅适合非核心业务表，比如告警表、日志表等。真正的核心业务表，一定不要用自增键做主键，主要有 6 个原因：
- 自增存在回溯问题；
- 自增值在服务器端产生，存在并发性能问题；
- 自增值做主键，只能在当前实例中保证唯一，不能保证全局唯一；
- 公开数据值，容易引发安全问题，例如知道地址http://www.example.com/User/10/，很容猜出 User 有 11、12 依次类推的值，容易引发数据泄露；
- MGR（MySQL Group Replication） 可能引起的性能问题；
- 分布式架构设计问题。
自增主键达到最大值：当达到 INT 上限后，再次进行自增插入时，会报重复错误，MySQL 数据库并不会自动将其重置为 1。
树形结构科普
- 传说中的叶子节点，指的是最外层的节点，就像一棵树，只有最外层的节点才长叶子
- 二叉搜索树的特点：
- 所有结点至多拥有两个儿子（Left和Right）；
- 所有结点只存储一个关键字（可以理解为索引，比如ID值）；
- 非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；
- 二叉搜索树如果是满二叉树时，查找的性能逼近有序数组的二分查找，同时插入的性能远远高于有序数组，因为只需要再对应的节点添加引用，而不需要移动任何老的节点
- B-Tree的特点
- 所有键值分布在整个树中（区别与B+树，B+树的值只分部在叶子节点上）
- 任何关键字出现且只出现在一个节点中（区别与B+树）
- 搜索有可能在非叶子节点结束（区别与B+树，因为值都在叶子节点上，只有搜到叶子节点才能拿到值）
- 在关键字全集内做一次查找，性能逼近二分查找算法
B+树的结构特点
- B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。数据的读取是精确到页的，因为页是计算机管理存储器的逻辑块，IO的磁盘读取，每次都读取数据的大小是一个页大小的整数倍。
- 假设B+Tree的高度为h，一次检索最多需要h-1次I/O（根节点常驻内存），复杂度O(h) = O(logmN)，m指的是一个节点存储的数据的个数。实际应用场景中，M通常较大，常常超过100，因此树的高度一般都比较小，通常不超过3。
- B+树与B树的不同在于：
- 所有关键字存储在叶子节点，非叶子节点不存储真正的data
- 为所有叶子节点（左右相邻的节点之间）增加了一个链指针
- 为什么数据库使用B+而不使用红黑树呢？
- 计算器在IO磁盘读取的时候，为了降低读取的次数，默认一次会读取一个页的数据量，MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(这个值可以修改)。linux 默认页大小为4K。所以每次IO读取，都是读取一个页的数据量，所以B树的节点都是存储一个页的节点，这样的查询效率才是最高的
- 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个结点只需一次I/O。这样大大降低了树的高度
- 为什么mysql的索引使用B+树而不是B树呢？
- 范围查找更快，mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树的数据有一部分存在在非叶子节点上面，而且默认的B树的相邻的叶子节点之间是没有指针的，所以范围查找相对更慢。
- 降低树的高度，但是最底下一层的节点会更多，因为所有的数据都堆积在最底下一层了，用空间换速度。B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高
二叉搜索树
在二叉搜索树中，我们要执行搜索，最好情况是搜索 0009，也就是 BST 的根结点，只需要一次磁盘 IO。最坏情况就是树最深的底层叶子节点（深度为 N 就需要 N 次磁盘 IO）。
二叉搜索树已经很优秀了，还有没有优化的空间？我们从以下几个点来考虑。
BST 的最坏情况怎么优化。
二叉搜索树是由树的深度决定的，我们能不能压缩它。
B树
非叶子节点也存了数据。叶子结点没有指针。
B 树，平衡多路查找树。B 树是为磁盘等存储设备设计的一种平衡查找树。B 树结构的数据可以让系统高效的找到数据所在的磁盘块。
我们以主键索引为例子。
上图就是一个 B 树，紫色为 Key，黄色为 data，蓝色为指针。
相比于之前的 BST 多了在每一个磁盘页的索引比较，但是因为磁盘页已经被磁盘 IO 操作读取到了内存中。因为内存 IO 操作比磁盘 IO 操作省时很多根本不在一个数量级所以可以忽略不计，所以磁盘 IO 操作仍然是最重要的性能指标。
B 树相比于二叉搜索树压缩了深度，所以磁盘 IO 会比二叉搜索树少，能有效地提高新能，所以 B 树更适合索引。
B+树
概念
B+树是在B树的基础上进行改造，它的数据都在叶子结点，同时叶子结点之间还加了指针形成链表。B+树的最大元素始终位于根节点当中。所有叶子节点包含了全量元素信息，并且每一个叶子节点都带有指向 下一个节点指针，形成了一个有序链表。
插入和删除的逻辑
https://blog.csdn.net/sunshine_lyn/article/details/82747596
- 插入：和红黑树特别像，新数据插入到一个满了的节点中时，会优先进行左旋右旋，如果邻近的节点都满了的话,会取中间的一个key往上一个层级插入，直至到Root节点,树的高度的增加，都是通过根节点的拆分来完成的，这保证了所有左右节点的高度差不超过1
- 删除：会进行调整优化树形结构，使树的数据更分散，以及降低树的高度。比如如果该节点的数据过少，可以从邻近的节点左旋 右旋数据来填充。可能的话，降低一个树的高度。
B 树与 B+ 树的区别
B+ 树的磁盘读写更低，因为非叶子节点可以存储更多的索引 key，而 key 索引在同一层更集中，那么会降低磁盘 IO 读写次数。
B+ 树的查询效率更稳定，任何查询都必须从根节点到叶子节点，路径是相似的，所以更稳定（最好最坏都在底层）。
区间访问友好性，MySQL 是关系型数据库，所以经常会按照区间来访问某个索引，B+ 树的叶子节点会按照顺序建立起链状指针，增强了区间访问性。
MySQL 为什么使用 B/B+ 树来实现索引那？
MySQL 是基于磁盘的数据库，索引是以索引文件的形式存于磁盘中的。索引的过程就是磁盘 IO 的过程，磁盘 IO 消耗比内存 IO 消耗好几个数量级，所以能有效减少磁盘 IO 的数据结构适合用来实现数据库索引。
B+树相对于B树的优势
1.单一节点存储更多的元素，使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。
3.所有叶子节点形成有序链表，便于范围查询。
磁盘 IO：系统读取磁盘是将磁盘的基本单位—磁盘块读取出来。磁盘读取 IO 是机械动作，时间大概为内存读取的十多万倍。所以磁盘 IO 读写速度称为索引性能的主要指标。
B+ 树是在 B 树的基础上的一种优化，使其更适合实现外存储索引结构。InnoDB 和 MyISAM 存储引擎都是使用 B+ 树实现其索引结构。
我们说过，B 树的索引和关键自 key-data 存储在磁盘里面，然后被磁盘 IO 操作读入内存。如果这个 data 很大的话，每次家在到内存中的 key 就会减少，这会使得 B 树的深度增加，这样还是会增加磁盘 IO 查询。
为了解决这个问题，B+ 树将所有数据记录节点按照键值的大小顺序存放在同一层叶子节点上，而非叶子节点只存储 key 值信息，这样可以大大增加每个节点存储的 key 值的数量，降低 B+ 树的高度。
非叶子节点只存储键值信息，所有叶子节点之间都有一个链指针，数据记录都存储于叶子节点中。
常见面试题
- 为什么 MySQL 的索引要使用 B+ 树而不是其他树形结构?比如 B 树?
他的简单版本回答是：因为 B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出)。指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。
B树的特点是非叶子结点有key和data数据，叶子节点没有链指针相连，因为树的每一层能够存储的大小是有限制的，非叶子结点存储了key和data，当data数据过大时意味着这一层存储的point就会减少，数据量增大时，意味着需要纵向增加树的高度，同理就会多增加一次磁盘的I/O操作，优化SQL核心还是减少磁盘I/O的次数。 而B+树的结构特点是，叶子结点只存储point和数，所有的key和data都在非叶子节点上，并且他们之间有point相连接，意味着树的同一层可以存储更多的数据，非叶子节点的数据也是有序排列的，查询速度会更快，同时又减少了磁盘I/O的次数
- MySQL中存储索引用到的数据结构是B+树，B+树的查询时间跟树的高度有关，是log(n)，如果用hash存储，那么查询时间是O(1)。既然hash比B+树更快，为什么mysql用B+树来存储索引呢？
**答：**一、从内存角度上说，数据库中的索引一般时在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。
二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。
- 为什么不用红黑树或者二叉排序树？
**答：**树的查询时间跟树的高度有关，B+树是一棵多路搜索树可以降低树的高度，提高查找效率。由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊！
- 既然增加树的路数可以降低树的高度，那么无限增加树的路数是不是可以有最优的查找效率？
**答：**这样会形成一个有序数组，文件系统和数据库的索引都是存在硬盘上的，并且如果数据量大的话，不一定能一次性加载到内存中。有序数组没法一次性加载进内存，这时候B+树的多路存储威力就出来了，可以每次加载B+树的一个结点，然后一步步往下找，
- 在内存中，红黑树比B树更优，但是涉及到磁盘操作B树就更优了，那么你能讲讲B+树吗？
B+树概念
- 为什么B+树要这样设计？
**答：**这个跟它的使用场景有关，B+树在数据库的索引中用得比较多，数据库中select数据，不一定只选一条，很多时候会选中多条，比如按照id进行排序后选100条。如果是多条的话，B树需要做局部的中序遍历，可能要跨层访问。而B+树由于所有数据都在叶子结点不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了。
事务
概述
set autocommit=0; 将自动提交事务禁用，需要手动commit。
事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。
MySQL服务器逻辑架构从上往下可以分为三层：
（1）第一层：处理客户端连接、授权认证等。
（2）第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。
（3）第三层：存储引擎，负责MySQL中数据的存储和提取。**MySQL中服务器层不管理事务，事务是由存储引擎实现的。**MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛；其他存储引擎不支持事务，如MyIsam、Memory等。
典型的MySQL事务是如下操作的：
start transaction;
…… #一条或多条sql语句
commit;
在MySQL中，存在一些特殊的命令，如果在事务中执行了这些命令，会马上强制执行commit提交事务；如DDL语句(create table/drop table/alter/table)、lock tables语句等等。
不过，常用的select、insert、update和delete命令，都不会强制提交事务。
快照读、当前读
因为MySQL默认的隔离级别是可重复读
这种隔离级别下，我们普通的SELECT语句都是快照读，也就是在一个事务内，多次执行SELECT语句，查询到的数据都是事务开始时那个状态的数据（这样就不会受其他事务修改数据的影响），这样就解决了幻读的问题。
- mysql如何实现避免幻读
- 在快照读读情况下，mysql通过mvcc来避免幻读。
- 在当前读读情况下，mysql通过next-key来避免幻读
简单理解：快照数据就已经被版本锁定了。
快照读就是每一行数据中额外保存两个隐藏的列，插入这个数据行时的版本号，删除这个数据行时的版本号（可能为空），滚动指针(指向undo log中用于事务回滚的日志记录)。
事务在对数据修改后，进行保存时，如果数据行的当前版本号与事务开始取得数据的版本号一致就保存成功，否则保存失败。
当我们不显式使用BEGIN来开启事务时，我们执行的每一条语句就是一个事务，每次开始事务时，会对系统版本号+1作为当前事务的ID。
- 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)
- select * from table where ?;
- 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
当前读
像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
快照读
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
结论
所以说InnoDB的RR隔离级别没有或者解决了幻读问题都不太准确。应该说它并没有完全解决幻读的问题。
如果在同一个事务里面，只是总是执行普通的select快照读，是不会产生幻读的。
但是如果在这个事务里面通过当前读或者先更新然后快照读的形式来读取数据，就会产生幻读。
事务特性
ACID是衡量事务的四个特性：
- 原子性（Atomicity，或称不可分割性）
- 一致性（Consistency）
- 隔离性（Isolation）
- 持久性（Durability）
原子性
原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
在说明原子性原理之前，首先介绍一下MySQL的事务日志。MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。
下面说回undo log。实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：
持久性
持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
实现原理：redo log
redo log和undo log都属于InnoDB的事务日志。下面先聊一下redo log存在的背景。
InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。
Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：
（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。
** redo log与binlog**
我们知道，在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：
（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。
（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。
（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：
- 前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。
- 除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。
隔离性
隔离性研究的是不同事务之间的相互影响。隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
实现：InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）
锁机制
两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。
行锁与表锁
按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。
脏读、不可重复读和幻读
**脏读：**当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
**不可重复读：**在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
**幻读：**在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。
避免幻读，则需要通过next-key lock。next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)。
RR解决了 脏读，不可重复读，幻读等问题使用到的就是MVCC（Multi-Version Concurrency Control）
mysql数据库定义了四种隔离级别
- serializable：可避免脏读、不可重复读、虚读情况的发生。
- repeatable read：可以避免脏读、不可重复读情况的发生。
- read committed：可以避免脏读情况发生。
- read uncommitted：最低级别，都会发生。
什么是当前读和快照读？
在学习 MVCC 多版本并发控制之前，我们必须先了解一下，什么是 MySQL InnoDB 下的当前读和快照读?
MVCC
https://zhuanlan.zhihu.com/p/150089208
InnoDB存储用mvcc实现了可重复读，间隙锁+行锁 解决了可重复读级别下的幻读问题（标准事务隔离级别，应该是串行化级别下才能解决幻读问题，可重复读级别不能解决幻读）。
mvcc解决了快照读下的幻读问题(select)，但是他无法解决当前读下的幻读问题(select for update),当前读下的幻读要通过行锁+间隙锁来解决
https://blog.csdn.net/SnailMann/article/details/94724197
说白了 MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
在 RR 级别下的某个事务的对某条记录的第一次快照读会创建一个快照及 Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个 Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个 Read View，所以对之后的修改不可见；
即 RR 级别下，快照读生成 Read View 时，Read View 会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见
而在 RC 级别下的，事务中，每次快照读都会新生成一个快照和 Read View , 这就是我们在 RC 级别下的事务中可以看到别的事务提交的更新的原因
保证了acid中的i:隔离性
MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议
下面的例子很好的体现了MVCC的特点：在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)——在T5时刻，事务A和事务C可以读取到不同版本的数据。
MVCC最大的优点是读不加锁，因此读写不冲突，并发性能好。
实现原理
1）隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。
2）基于undo log的版本链：前面说到每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。
3）ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本；但是具体要恢复到哪个版本，则需要根据ReadView来确定。
一致性
一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
实现：
可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。
实现一致性的措施包括：
- 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
- 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
- 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致
mysql事务执行过程中宕机了
事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是人们口中常说的“日志先行”(Write-Ahead Logging)。
日志
WAL（Write-Ahead Logging）机制
MySQL 里经常说到的 WAL技术，也就是先写日志，再写磁盘
所以，redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性。
redo log(重做日志文件)
用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来
在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录redo log，通过顺序io来改善性能。所有的事务共享redo log的存储空间，它们的redo log按语句的执行顺序，依次交替的记录在一起。
此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。
redo log日志的大小是固定的，即记录满了以后就从头循环写。
undo log
undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。
binlog(binary-log二进制日志)
可以配合canal使用，进行数据同步，不需要写代码，在配置文件配置就好了，canal也是springboot项目。
binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。
- binlog主要有两个应用场景：
- 一是用于复制，master把它的二进制日志传递给slave来达到master-slave数据一致的目的。
- 二是用于数据恢复，例如还原备份后，可以重新执行备份后新产生的binlog，使得数据库保持最新状态
- 除去这两个主要用途外，binlog可以用于异构系统之间数据的交互，binlog完整保存了一条记录的前项和后项记录，可以用DTS服务，将MySQL数据以准实时的方式抽取到底层数据平台，比如Base、Hive、Spark等，打通OLTP和OLAP
canal主要用于对mysql的增量日志进行解析（请注意，只支持增量解析，不支持全量解析），提供增量数据的订阅和消费，对mysql增量数据进行实时同步，支持同步到mysql、elasticsearch、hbase等数据源
Canal就是一个很好的数据库同步工具。canal是阿里巴巴旗下的一款开源项目，纯Java开发。基于数据库增量日志解析，提供增量数据订阅&消费，目前主要支持了MySQL。
如何恢复数据？
当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
1、首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
2、然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。
这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。
慢查询日志
查看慢查询日志是否开启：SHOW VARIABLES LIKE 'slow_query%';
mysql默认不开启慢查询日志，命令行的开启方式，重启之后会失效，开启慢查询之后多多少少会影响数据库的性能，默认10秒
永久设置方式就是去改配置文件
# 开启慢查询日志步骤
set global slow_query_log='ON';
# 设置慢查询日志存放位置
SET GLOBAL slow_query_log_file='/var/lib/mysql/slow_query.log'; //这里一定要自己去建文件。。。
# 设置慢查询时间标准(一般设置为1秒)
set global long_query_time=1;
#测试
select sleep(2);
最后查询出的结果，有前后对比图，仔细看两次cat输出
锁
乐观锁、悲观锁
update order set price = 1, version = version + 1 where id = 1 and version = 0
执行完成后，version字段值将变成1, 第二人执行update:
update order set price = 1, version = version + 1 where id = 1 and version = 0
此时的version的值已经被修改为1，所以第二人修改失败，实现乐观锁控制。
InnoDB行锁实现方式
InnoDB行锁是通过给索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录枷锁。
for update 使用场景
如果项目对某个数据准确性有要求，并且项目存在并发（不一定高并发），则需要使用 for update
比如：用户A使用余额购买商品，此时用户B向用户A发起转账，如果恰好处在同一时间，则可能造成用户A最终余额错误。此时需要使用 for update 进行数据加锁防止出错
这种情况下，即使并发很小，但是也会有一定的概率会碰到，而余额的错误即使差一分钱也是不能容忍的，所以这种特定的场景，即使不是高并发，也应该使用 for update 规避问题
关于 for update 的锁范围
for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
begin;
select * from XXX where XXX for update;
...
commit;
InnoDB默认是行级别的锁，当有明确指定的主键或者索引的时候，是行级锁。否则是表级别。
查询不到数据的情况下：
明确指定主键或者索引则不锁任何数据，没有主键或者索引的情况下锁表
如果没查到记录会加锁吗？
答：会的。有主键/索引产生间隙锁，无主键/索引产生表锁表级锁。
for update的锁表
InnoDB默认是行级别的锁，当有明确指定的主键/索引时候，是行级锁。否则是表级别。
意向锁(Intention Locks)
意向锁是一种不与行级锁冲突表级锁
意向锁的作用：如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。
mysql 死锁
当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。
其他
json存储
用户 1 登录有三种方式：手机验证码登录、微信登录、QQ 登录，而用户 2 只有手机验证码登录。
可以看到，虽然用传统关系型的方式也可以完成相关数据的存储，但是存在两个问题。
- 有些列可能是比较稀疏的，一些列可能大部分都是 NULL 值；
- 如果要新增一种登录类型，如微博登录，则需要添加新列，而 JSON 类型无此烦恼。
同理，树高度为 3 的 B+ 树索引，最多能存放的记录数为：
总记录数 = 1100（根节点） * 1100（中间节点） * 32 = 38,720,000
讲到这儿，你会发现，高度为 3 的 B+ 树索引竟然能存放 3800W 条记录。在 3800W 条记录中定位一条记录，只需要查询 3 个页。
可以看到，表 User 有三个二级索引 idx_name、idx_register_date、idx_last_modify_date。
通常业务是无法要求用户注册的昵称是顺序的，所以索引 idx_name 的插入是随机的， 性能开销相对较大；另外用户昵称通常可更新，但业务为了性能考虑，可以限制单个用户每天、甚至是每年昵称更新的次数，比如每天更新一次，每年更新三次。
而用户注册时间是比较顺序的，所以索引 idx_register_date 的性能开销相对较小， 另外用户注册时间一旦插入后也不会更新，只是用于标识一个注册时间。
而关于 idx_last_modify_date ， 我在 03 讲就强调过，在真实业务的表结构设计中，你必须对每个核心业务表创建一个列 last_modify_date，标识每条记录的修改时间。
这时索引 idx_last_modify_date 的插入和 idx_register_date 类似，是比较顺序的，但不同的是，索引 idx_last_modify_date 会存在比较频繁的更新操作，比如用户消费导致余额修改、money 字段更新，这会导致二级索引的更新。
由于每个二级索引都包含了主键值，查询通过主键值进行回表，所以在设计表结构时让主键值尽可能的紧凑，为的就是能提升二级索引的性能，我在 05 讲推荐过 16 字节顺序 UUID 的列设计，这是性能和存储的最佳实践。
除此之外，在实际核心业务中，开发同学还有很大可能会设计带有业务属性的主键，但请牢记以下两点设计原则：
- 要比较顺序，对聚集索引性能友好；
- 尽可能紧凑，对二级索引的性能和存储友好；
除主键索引外的索引都是二级索引，索引的叶子节点存放的是（索引键值，主键值）；
由于二级索引不存放完整记录，因此需要通过主键值再进行一次回表才能定位到完整数据；
由于二级组合索引的叶子节点，包含索引键值和主键值，若查询的字段在二级索引的叶子节点中，则可直接返回结果，无需回表。这种通过组合索引避免回表的优化技术也称为索引覆盖（Covering Index）。
可以通过 FORMAT=tree 观察得到：
EXPLAIN FORMAT=tree
SELECT * FROM orders
WHERE o_orderdate > '1994-01-01'
AND o_orderdate < '1994-12-31'\G
可以看到，MySQL 认为全表扫描，然后再通过 WHERE 条件过滤的成本为 592267.11，对比强制使用二级索引 idx_orderdate 的成本为 844351.87。
成本上看，全表扫描低于使用二级索引。故，MySQL 优化器没有使用二级索引 idx_orderdate。
为什么全表扫描比二级索引查询快呢？ 因为二级索引需要回表，当回表的记录数非常大时，成本就会比直接扫描要慢，因此这取决于回表的记录数。
MySQL直方图
用到这个的情况：
在订单状态这种区分度不是很高的字段加了索引，如性别一般不用加，订单状态，但是如果有数据倾斜的情况，比如查找未支付的订单，统一修改状态，占比很小，这种就需要加
但是这种加了之后，MySQL优化器也会默认区分度不高，走全变扫描，这个时候就可以用直方图
因此，优化器会认为订单状态为 P 的订单占用 1/3 的数据，使用全表扫描，避免二级索引回表的效率会更高。
然而，由于数据倾斜，订单状态为 P 的数据非常少，根据索引 idx_orderstatus 查询的效率会更高。这种情况下，我们可以利用 MySQL 8.0 的直方图功能，创建一个直方图，让优化器知道数据的分布，从而更好地选择执行计划。
join到底能不能用
要看驱动表的数量大不大，不大则可以考虑使用
left 左驱动 right 右 inner join 优化器会自己选择
小表驱动大表
MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。
子查询
子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
exist
三大范式
列不可再分
属性完全依赖于主键
属性不依赖于其它非主属性 属性直接依赖于主键
限定数据的范围
务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
update stock set remainder=remainder-amount,version=version+1 where stock_id=‘ S T O C K I D STOCK_ID STOCKID’ and version=$version
SQL语句的执行过程
•SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
•对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit
引擎对比
inndb
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
MyISAM
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
int类型
int(M) 这里的M代表的是显示宽度，不影响存储值得大小，所以int(1)和int(10)存的值一样大，只是显示宽度不一样，想要看到明显效果可以使用zerofill这个值，表示用 0 填充
如果不是无符号类型，BIGINT(20)的取值范围为-9223372036854775808~9223372036854775807。与Java.lang.Long的取值范围完全一致，mybatis会将其映射为Long而BIGINT(20) UNSIGNED的取值范围是0 ~ 18446744073709551615，其中一半的数据超出了Long的取值范围，Mybatis将其映射为BigInteger
有符号int最大可以支持到约22亿，远远大于我们的需求和MySQL单表所能支持的性能上限。对于OLTP应用来说，单表的规模一般要保持在千万级别，不会达到22亿上限。
无符号int，上限为42亿，这个预留量已经是非常的充足了。使用bigint，会占用更大的磁盘和内存空间，内存空间毕竟有限，无效的占用会导致更多的数据换入换出，额外增加了IO的压力，对性能是不利的。
key_len计算方式
key_len的长度计算公式：
varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL = 10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
mysql常用字段和所占字节数
TINYINT 1 字节
SMALLINT 2 个字节
MEDIUMINT 3 个字节
INT 4 个字节
INTEGER 4 个字节
BIGINT 8 个字节
FLOAT 4 个字节
DOUBLE 8 个字节
REAL 8 个字节
DECIMAL(M,D) M字节(D+2 , 如果M <D)
NUMERIC(M,D) M字节(D+2 , 如果M <D)
DATE 3 个字节
DATETIME 8 个字节
TIMESTAMP 4 个字节
TIME 3 个字节
YEAR 1 字节
CHAR(M) M字节，1 <= M <=255
VARCHAR(M) L+1 字节, 在此L <= M和1 <= M<= 255
TINYBLOB, TINYTEXT L+1 字节, 在此L< 2 ^ 8
BLOB, TEXT L+2 字节, 在此L< 2 ^ 16
ENUM('value1','value2',...) 1 或 2 个字节, 取决于枚举值的数目(最大值65535）
MySQL中中的整数类型int主要有如下几种：
tinyint 的范围是-128~127；存储大小为1个字节;
smallint unsigned的范围是 –2^15（2^15表示2的15次幂） 到2^15 – 1，即 –32768 到 32767；
smallint 的范围是 0 到 2^16 – 1，即 0 到 65535，存储的字节是2个字节。
int的范围是-2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据（所有数字），存储大小为4个字节；
bigint的范围是 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节；
我们在设计的时候要注意尽可能使用较小数据类型。
-- 8+1 = 9 这个只走了createTime的索引
explain select
*
from
learning_dev.tb_people_info
WHERE
CREATE_time > 1626921092573
and create_time <1626921092873
and city in ('北京') ;
-- 8+1 = 9 一样 也是只走了createTime的索引
explain select
*
from
learning_dev.tb_people_info
WHERE
CREATE_time > 1626921092573
and create_time <1626921092873
and city = '北京' ;
-- (32*3 + 2 +1 ） * 2 = 198 不允许null为196 两个索引都走了
explain select
*
from
learning_dev.tb_people_info
WHERE
username = '湛秋'
and city in ('黑龙江') ;
-- 98+9 = 107 , (city, create_time) 这样子建立索引两个都用上了
-- 这个也会用上面的索引，虽然条件多了一个，但是MySQL自己会推断
explain
select
*
from
learning_dev.tb_people_info
WHERE
city = '北京'
and create_time > 1626921092573
and create_time <1626921092873
and username = '干杰' ;
where 1=1
项目中看到1=1
防止语法报错
如果不用1=1的话，每加一个条件，都要判断前面有没有where 条件，如果没有就写where …，有就写and语句，因此此时用1=1可以简化了应用程序的复杂度。
ORM
对象-关系映射
面向对象编程把所有实体看成对象（object），关系型数据库则是采用实体之间的关系（relation）连接数据
ORM 把数据库映射成对象
总结起来，ORM 有下面这些优点
- 数据模型都在一个地方定义，更容易更新和维护，也利于重用代码。
- ORM 有现成的工具，很多功能都可以自动完成，比如数据消毒、预处理、事务等等。
- 它迫使你使用 MVC 架构，ORM 就是天然的 Model，最终使代码更清晰。
- 基于 ORM 的业务代码比较简单，代码量少，语义性好，容易理解。
- 你不必编写性能不佳的 SQL。
但是，ORM 也有很突出的缺
- ORM 库不是轻量级工具，需要花很多精力学习和设置。
- 对于复杂的查询，ORM 要么是无法表达，要么是性能不如原生的 SQL。
- ORM 抽象掉了数据库层，开发者无法了解底层的数据库操作，也无法定制一些特殊的 SQL。
DDL DML DCL
DDL
DDL(Data Definition Language 数据定义语言)用于操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，像：表、视图等等，DDL对这些对象和属性的管理和定义具体表现在Create、Drop和Alter上。特别注意：DDL操作的“对象”的概念，”对象“包括对象及对象的属性，而且对象最小也比记录大个层次。以表举例：Create创建数据表，Alter可以更改该表的字段，Drop可以删除这个表，从这里我们可以看到，DDL所站的高度，他不会对具体的数据进行操作。
DDL的主要语句(操作)
Create语句：可以创建数据库和数据库的一些对象。
Drop语句：可以删除数据表、索引、触发程序、条件约束以及数据表的权限等。
Alter语句：修改数据表定义及属性。
DDL的操作对象(表)
表的概念
表的创建就是用来存放数据用的，由于我们存放的数据的不通，所以我们需要定义些数据类型，以方便管理。
表的属性
主键属性：主键就是主键约束，只不过起的名字不同了，主键的起名偏向于虚的(就是描述描述这件事)，主键约束起名偏向于实得(就是描述操作的实施)，描述的都是同一件事，主键约束就是表中的一个属性;在一个表中最多可以有一个主键;一个主键可以定义在一个或多个字段;主键使一个或多个字段的值必须唯一且不为空，这样做可以通过该字段或该组字段中的值唯一的代表一条记录。
唯一属性：一个表中只能有一个主键属性，为了方表用户，提出唯一约束;唯一约束可以定义在一个或多个字段上;唯一约束使该字段或该组字段中的值唯一，可以为空，但是，不能重复。
外键属性：又叫外键，又叫外键约束，跟主键和主键约束的关系是一样的;外键约束针对的两个表，如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表，但要注意，必须要计算机要知道你是这种关系。
核查、Null和缺省属性：核查属性又叫核查约束，Null属性又叫Null约束，缺省属性又叫缺省约束;这些名称是描述一件事，描述一种情况，这件事或这张情况我们当然可以人为的那样特意做(输入数据是注意就行)，但是，他们的本意是实现自动化，也就是让计算机做这件事。
(你知道为什么建立主键和唯一约束的时候，会自动的创建索引吗?而且是唯一索引，想一想索引大多在那些字段上用，以及索引的作用就会知道了。像主键约束、唯一约束、非空约束、外键约束、核查约束和缺省约束这些操作都是使表具有某些特性，所以在这里我认为他们都是表的属性。)
DML
DML(Data Manipulation Language 数据操控语言)用于操作数据库对象中包含的数据，也就是说操作的单位是记录。
DML的主要语句(操作)
Insert语句：向数据表张插入一条记录。
Delete语句：删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作对象仍是记录。
Update语句：用于修改已存在表中的记录的内容。
DML的操作对象——记录
注意
当我们对记录进行Insert、Delete和Update操作的时候，一定要注意，一定要清楚DDL对其的一些操作。
DCL
DCL(Data Control Language 数据控制语句)的操作是数据库对象的权限，这些操作的确定使数据更加的安全。
DCL的主要语句(操作)
Grant语句：允许对象的创建者给某用户或某组或所有用户(PUBLIC)某些特定的权限。
Revoke语句：可以废除某用户或某组或所有用户访问权限
DCL的操作对象(用户)
此时的用户指的是数据库用户。
Mysql集群
MySQL主从复制
MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。
主从复制三步骤
- 主服务器：
- 开启二进制日志
- 配置唯一的server-id
- 获得master二进制日志文件名及位置
- 创建一个用于slave和master通信的用户账号
- 从服务器：
- 配置唯一的server-id
- 使用master分配的用户账号读取master二进制日志
- 启用slave服务
- 建立一个主节点，开启binlog，设置服务器id
- 建立一个从节点，设置服务器id
- 将从节点连接到主节点上
- 创建(主机)Master实例并启动，指定密码为root
docker run -p 3307:3306 --name mysql-master \
-v /mydata/mysql/master/log:/var/log/mysql \
-v /mydata/mysql/master/data:/var/lib/mysql \
-v /mydata/mysql/master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
- 创建从机
docker run -p 3317:3306 --name mysql-slaver-01 \
-v /mydata/mysql/slaver/log:/var/log/mysql \
-v /mydata/mysql/slaver/data:/var/lib/mysql \
-v /mydata/mysql/slaver/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
- 进入主mysql的conf目录
vi /mydata/mysql/master/conf/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
server-id=11201
log-bin=mysql-bin
read-only=0
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_sms
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_wms
binlog-do-db=gulimall_admin
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
重启mysql服务使配置生效
service mysql restart
重启容器
mysql -u root -p 123456
CREATE USER ‘slave’@‘%’ IDENTIFIED BY ‘root’;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@‘%’;
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
drop user 'slave'@'%';
# 从库配置这个，去同步主库的数据
CHANGE MASTER TO MASTER_HOST='192.168.108.134', MASTER_USER='root', MASTER_PASSWORD='root', MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=952;
- 进入从mysql的conf目录
vi /mydata/mysql/slaver/conf/my.cnf
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
server-id=11202
log-bin=mysql-bin
read-only=1
binlog-do-db=gulimall_oms
binlog-do-db=gulimall_pms
binlog-do-db=gulimall_sms
binlog-do-db=gulimall_ums
binlog-do-db=gulimall_wms
binlog-do-db=gulimall_admin
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
在/var/log/mysql目录下看到一个mysql-bin.000001文件
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;
- docker restart mysql-master mysql-slaver-01
- 设置权限
为master授权用户来他的同步数据
1、进入master容器
docker exec -it mysql-master /bin/bash
2、mysql -uroot -p root
1）授权root可以远程访问（主从无关，方便我们可以远程链接mysql）
grant all privileges on *.* to 'root'@'%' IDENTIFIED BY 'root' with grant option;
flush privileges;
2）添加同步用户，链接master数据库，在master授权一个 复制权限的 用户
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
3、查看master状态
show master status;
配置slaver同步master数据
1、进入slaver容器
docker exec -it mysql-slaver-01 /bin/bash
2、mysql -uroot -p root
1）授权root可以远程访问（主从无关，方便我们可以远程链接mysql）
grant all privileges on *.* to 'root'@'%' IDENTIFIED BY 'root' with grant option;
flush privileges;
2）设置主库连接
change master to master_host='192.168.108.134',master_user='backup',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=0,master_port=3307;
3、启动从库同步
start slave;
4、查看从库状态
show slave status;
mysql 分库分表
- 安装https://shardingsphere.apache.org/document/current/cn/overview/
- 在lib目录放入mysql驱动
13万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
