MySQL基础
DDL操作delete from table where 条件;truncate table table;区别：1.delete可以加条件2.truncate删除，整个表一起删了，然后建一张空表，效率高一些，delete是一条数据一条数据删除的3.假如要删除的表中有自增的长列，如果用delete删除，再插入数据，自增长列的值从**断点（比如上次从id为5的删除，则下次新增就从id就从6开始）**开
DDL操作
delete from table where 条件;
truncate table table;
区别：
1.delete可以加条件
2.truncate删除，整个表一起删了，然后建一张空表，效率高一些，delete是一条数据一条数据删除的
3.假如要删除的表中有自增的长列，如果用delete删除，再插入数据，自增长列的值从**断点（比如上次从id为5的删除，则下次新增就从id就从6开始）**开始，truncate的话从1开始
4.delete删除有返回值，truncate没有
5.truncate不可以回滚，delete可以回滚
备注：自增长列就是会某一个字段会自动增长，比如id，1，2，4，5，则新插入一条数据，id会自动的默认值为6。
约束
比如create table a(b int default 3, c char not null, d char unique, key char primary key, e char foreign key，f char(1) check(f=‘男’ pr f=女’'));
foreign key: 该字段来源于另一个表的字段，当前表想要进行引用时，可以设置为外键。
// 展示一些表的属性
desc table;
mysql> desc t_fruit ;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| fid | int | NO | PRI | NULL | auto_increment |
| fname | varchar(20) | NO | | NULL | |
| price | int | YES | | NULL | |
| fcount | int | YES | | NULL | |
| remark | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)
// 查看表中所有的索引
mysql> show index from t_fruit;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_fruit | 0 | PRIMARY | 1 | fid | A | 16 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.03 sec)
除了列级约束，还有表级约束
create 下面加constraint 约束名 约束类型 (字段名)
一般只有外键才加表级约束。
主键（primary key）和唯一（unique）对比
- 主键不为空，唯一的话允许为空，但是该字段只能允许一条数据为null。
- 主键只有一个
- 主键是否可以多个字段组合比如说创建表级约束时，create table a1 (a int, b char(1), Primary key(a, b) #意思是两个字段组合成一个主键)，唯一不行
相同点：都是唯一的，无重复的值
// 修改约束
// 列级
alter table table_1 modify column col_1 varchar(20) not null;
// 表级
alter table table_1 add primary key(col_1) ;
// 删除主键
alter table table_1 drop primary key;
// 删除唯一
alter table table_1 drop index col_1;
// 标示列：自增长列，一张表只能一个，而且必须是唯一（或者是主键、外建，但必须是唯一的）的列才能是标识列，只能是数值型
create table a(b int auto_increment);
// 查看自增长的步长和初始值，支持自定义
show variables like '%auto_increment%'
set auto_increment_increment=3;
alter table test change column a a int auto_increment;
mysql> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.02 sec)
事务
transaction control Language 事务控制语言
// 就是一个执行单元：一个或者一组sql执行，要们一起成功，要们一起失败
show engines；查看支持的引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
innodb支持事务，myisam和memory不支持
事务的acid
原子性：就是一个操作完整的执行，中间有任何一个步骤失败都算失败
一致性：从一个一致性状态切换到另一个一致性状态（能量守恒）
隔离性：并发执行的各个事务互不干扰，但应该是有先后顺序的
持久性：一个事务被提交，则对数据库的改变是永久的
隐士事务：insert，update，delete这些命令相当于是一个事务
显示事务：比如两条隐士事务语句就必须啊显示指定，必须提前设置autocommit
mysql> show variables like '%autocommit%'
-> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
show variables like ‘%autocommit%’
// 开启事务
set autocommit=0;
start transaction;（可选）
delete from a where col_1=2;
delete from a where col_2=3;
commit;提交事务
rollback;回滚
savepoint a; 设置保存点，配合
rollback to a;回到保存点
// 提交事务或者回滚都代表着一个事务的结束
mysql> set autocommit=0;
mysql> select * from test;
+------+------+
| a | b |
+------+------+
| 2 | 4 |
| 2 | 6 |
| 2 | 7 |
+------+------+
3 rows in set (0.00 sec)
mysql> delete from test;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+------+
| a | b |
+------+------+
| 2 | 4 |
| 2 | 6 |
| 2 | 7 |
+------+------+
3 rows in set (0.00 sec)
多个事务访问相同的数据，没有隔离机制就会有触发并发问题，就可以理解为多条线程去访问同一张表的同一条数据的问题，都是在各自的事务里面
脏读：事务1和事务2，事务1读取了事务2还没有提交的字段，接着事务2选择回滚，那么相当于事务1读了无效的字段，一般是update操作
不可重复读：事务1读取了一个字段，事务2更新了这个字段，事务1再次读取，字段的值发生了改变，一个字段被重复读了几次结果不一样，正确的应该是在同一个事务里（当前是事务1），在没有提交之前，读取同一张表，应该是一样的，也就是可以重复读取的才是正常的
幻读：事务1从表中读取到一个字段，然后事务2插入了一些新的行，之后，事务1update该表时，竟然发现多了几条数据，一般是一个事务insert操作之后，另外一个事务update，总结一下就是在一个事物中，一开始读取，接着update或者delete的数据不一样
不同数据库有不同的隔离管理级别
#select @@transaction_isolation;
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)
隔离级别：=> (导致的意思)
read uncommited： 读未提交 => 脏读，没提交（比如执行了一个update操作）的数据在另一个mysql客户端也能读取到。
read commited： 读已提交 => 不可重复读，这种就是事务1提交了更新操作，在事务2中由于事务1没提交，所以读取不到，但是事务1提交了，事务2就读取到了，但是事务2还没提交，按理说不应该读到
REPEATABLE-READ（default）：重复读 => 幻读，事务1进行插入，未提交；然后事务2执行读取，由于脏读，是可以读取插入的数据，接着进行更新或者删除，更新或者删除是无法对事务1的数据进行处理的，那么就会发现更新时与之前读到的数据条数不一致
serializable：序列化，这种方式就是会加一个行级别的锁，当进行一个事务的插入操作时而，如果另一个事务也正在进行ddl操作时，那么当前事务会被阻塞住，必须等另外一个事物commit了，才能执行当前事务，性能会相对降低了
set session/global（全局） transaction isolation level read committed;
视图
只保存了sql逻辑，不保存查询结果
create view c as select * from a where id < 10;
配置文件
bin/log => 主从复制的文件
mysqld --help --verbose | less
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mac /usr/local/etc/my.cnf
linux /etc/my.cnf
log-bin: 二进制文件，进行主从复制时使用
log-err: err级别的日志文件
查询日志log
数据文件：windows：$安装路径/mysqlServer-version/data 目录
linux：默认路径 /var/lib/mysql
mac: /usr/local/var/mysql
frm文件：存放表的结构
myd文件：表数据
myi文件：表的索引（通过索引，找到表中具体数据）
sql服务架构
上层就是各种语言写好mysql客户端，然后连接服务端，一条sql进来会解析，然后优化，还有一些缓存的优化，但是这些优化的逻辑会由于不同的sql引擎而不同，市面上比较多的就是myisam和innodb，最后是存储在磁盘上，磁盘往上其实存储成文件。
优点：插件式的存储引擎的架构将查询处理和其他系统任务以及数据的存储提取相分离
连接层、接口层、引擎层、存储层
myisam和innodb的区别
这篇总结的不错，可以看看～
除此之外：innodb缓存了索引和真实数据，对内存要求较高，myisam缓存了索引，所以如果关注性能，则选择myisam，innodb关注的是事务。高并发高的操作，最好是能支持行锁的innodb，但是必须是命中了索引才能触发，因为锁是锁在index上的。
性能
sql慢的原因：
- 索引失效：
索引的创建：
create table user(id int, name char, email int, num int);
// 单索引
create index idx_user_name on user(name);
// 复合索引
select * from user where user="’ and email= “”;
create index idx_user_nameEmail on user(name, email); - join过多
- 服务器端调优
join查询
sql的执行顺序
select distinct(*) from a join b on a.id = b.id where a.count < 100 group by a.name having a.color='red' order by a.name limit 10;
sql解析
from ：做一个笛卡尔积，t1和t2生成一个虚拟表v1
on ：主表进行保留，v1根据条件筛选得到v2
join ： 不符合on也添加，如果是（left or right）outer join，则v1生成v2时过滤掉的数据还得加回来，生成v3
where：过滤一些数据，条数变少，非聚合，非select别名
group by:
having:
select
distinct ：去重
order by:
limit
join的方式
左连接：左表是主表
右连接
内连接
外连接：
索引
高效获取数据的方式，是一种数据结构，b🌲
建一个索引的原因：提高查找效率，先找索引，然后直接就找到数据，相当于排好序的快速查找数据结构，也就是会影响where和order by
myi ：索引文件，也就是表中的索引，最终是指向数据
myd：实际的数据文件
b🌲：底层就是一个n叉查找树，这棵树存储着索引号，找数据先找myi，然后找到实际数据的物理地址（在磁盘中存储着的）
create index idx_user_name on user(name);
如果有索引的表，有条数据删除了，那么是需要重建的，否则索引就失效了，索引不可能全部存在内存，往往是以文件存储着，如myi文件
先来看看为什么会出现B-树这类数据结构？
传统用来搜索的平衡二叉树有很多，如 AVL 树，红黑树等。这些树在一般情况下查询性能非常好，但当数据非常大的时候它们就无能为力了。原因当数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。一般而言内存访问的时间约为 50 ns，而磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中比较的时间。这说明程序大部分时间会阻塞在磁盘 IO 上。那么我们如何提高程序性能？减少磁盘 IO 次数，像 AVL 树，红黑树这类平衡二叉树从设计上无法“迎合”磁盘。
B+ 树的优点在于：
1.由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key，如下图所示，只有最后一层存储key-value，上面的层级都是存着指针，不存真实数据。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
2.B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
但是B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。
3.B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
实际索引也是一张表，表保存了主键和索引字段，并指向实体表的记录，会占用存储空间，而且对表进行ddl操作时，索引也得更新，平时生产上复合索引建立的比较多
添加索引的方式：主键索引（唯一，并且不能有null，也就是说主键本身也是一种索引），唯一索引（唯一，但允许有null，unique的列也是一种索引），还有全文索引（fullText）
什么时候需要建立索引：
- 主键本身自带
- 常查询的字段（但不频繁更新）
- where条件里面用不到的不建
- 高并发情况建复合索引
什么时候不建： - 记录过少
- 经常增删改茶的表
- 记录中过多重复的，比如性别
Explain
能干嘛：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些所以被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
mysql> explain select * from t_fruit;
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Connection id: 303
Current database: fruitdb
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_fruit | NULL | ALL | NULL | NULL | NULL | NULL | 16 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
- id :select 的查询顺序， 也就是可以知道读取表的顺序，id越大，越先被读取
sql优化
观察，先跑一天，查看生产慢的sql
开启慢查询日志，设置阈值，比如超过多少秒就是慢sql
explain+慢sql
show profile
参数调优
查询优化：永远小表驱动大表
select * from A where ID in (select id from B)
等价于
for select id from B
for select * from A where A.id =B.id
当表A的数据大于表B时，in的效率高于exists
select * from A where exists (select id from B where A.id=B.id)
for select id from B
for select * from A where A.id =B.id
当表A的数据小于表B时，exists的效率高于in
A，B表的id需要建立索引
order by优化：
mysql支持两种方式的排序：fileSort和index，index效率高（b+树logn的排序效率）
age是索引，birth不是
select * from B where age > 20 order by birth
这会导致fileSort（有双路查询，扫两次磁盘，读取行指针和排序字段，后改进为单路查询，按照order by的列在buffer进行排序，随机io变顺序io，而且只读一次数据，但是占用的内存更多
对比：
其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
但是单路缺点也很明显，容易造成buffer放不下所有字段，所以需要调整一下参数sort_buffer_size, max_length_for_sort_data, 并且少写select * ，否则查询的字段大于sort_buffer_size时，就不是单路查询而是双路查询。如果能够排序和查询的字段都是相同的索引列，那么效率会更高。
）
而为了实现index的排序，需要保证最左前列或者使用where子句与order by子句组合满足索引最左前列，尽可能让索引列取排序。
索引是（a,b,c）这种复合索引
order by a
order by a,b
order by a,b,c
order by a desc, b desc, c desc (同升同降)
如果where a=常量 order by b,c
where a=const and b=const order by c
where a=const and b > const order by b,c
以上的情况都是满足最左前缀，可以使用索引的
下面的是不行的
order by a asc, b desc, c desc
where g=const and b=const order by c
where g=const order by c
慢查询日志
开启会影响性能
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/var/mysql/DIDI-C02G137EQ05P-slow.log |
+---------------------+-------------------------------------------------+
2 rows in set (0.04 sec)
// 设置慢的一个时间阈值
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
set global slow_query_log=1 当钱数据库生效
想要永久生效就改配置文件my.cnf
[mysqld]下增加或修改参数
show variables like ‘%long_query_time%’;
批量数据的插入
show profile
mysql提供的用来分析当前会话中语句执行的资源消耗情况。默认处于关闭的状态，保存最近15次的结果
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.01 sec)
开启：set profiling = on;
show profiles 可以查看查询的时间消耗
mysql> show profiles;
+----------+------------+------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00560000 | show variables like '%profiling%' |
| 2 | 0.00281100 | select * from t_fruit |
| 3 | 0.00080600 | select * from t_fruit group by fid%20 order by 6 |
| 4 | 0.00064400 | select * from t_fruit group by fid%20 order by 3 |
| 5 | 0.00057500 | select * from t_fruit group by price%20 order by 3 |
| 6 | 0.00142700 | select * from t_fruit group by price%20 |
| 7 | 0.00098400 | select price from t_fruit group by price%20 |
| 8 | 0.00141600 | select price from t_fruit group by price |
| 9 | 0.00077600 | set sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’ |
| 10 | 0.00155100 | set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' |
| 11 | 0.00288000 | select price from t_fruit group by price%20 |
| 12 | 0.00073500 | select price from t_fruit group by price%20 order by 6 |
| 13 | 0.00077400 | select price from t_fruit group by price%20 order by 2 |
| 14 | 0.00136100 | select price from t_fruit group by price%20 limit 1 |
+----------+------------+------------------------------------------------------------------------------------------------------------------------+
14 rows in set, 1 warning (0.01 sec)
查看某一个query通过id，可以看出sql的执行时间，各个流程消耗的时间
show profiles cpu, block io for query 3;
全局查询日志：（生产别用，非常影响效率）
开启 general log 将所有到达MySQL Server的SQL语句记录下来。
一般不会开启开功能，因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general log以供排障使用。
相关参数一共有3：general_log、log_output、general_log_file
show variables like 'general_log'; -- 查看日志是否开启
set global general_log=on; -- 开启日志功能
show variables like 'general_log_file'; -- 看看日志文件保存位置
set global general_log_file='tmp/general.lg'; -- 设置日志文件保存位置
show variables like 'log_output'; -- 看看日志输出类型 table或file
//log_output=’FILE’ 表示将日志存入文件,默认值是FILE
//log_output=’TABLE’表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中.
set global log_output='table'; -- 设置输出类型为 table
set global log_output='file'; -- 设置输出类型为file
// 编写的sql都会记录在general_log表里
mysql> select * from mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+--------------------------------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+--------------------------------------------------------------------+
| 2022-05-15 18:25:30.403078 | root[root] @ localhost [] | 304 | 1 | Query | 0x73686F77207661726961626C6573206C696B6520276C6F675F6F757470757427 |
| 2022-05-15 18:26:06.108146 | root[root] @ localhost [] | 304 | 1 | Query | 0x73656C656374202A2066726F6D206C6F675F6F7574707574 |
| 2022-05-15 18:26:18.392300 | root[root] @ localhost [] | 304 | 1 | Query | 0x73656C656374202A2066726F6D206D7973716C2E6C6F675F6F7574707574 |
| 2022-05-15 18:26:33.342315 | root[root] @ localhost [] | 304 | 1 | Query | 0x73656C656374202A2066726F6D206D7973716C2E67656E6572616C5F6C6F67 |
+----------------------------+---------------------------+-----------+-----------+--------------+--------------------------------------------------------------------+
4 rows in set (0.01 sec)
// 可以看到所有的sql的日志
mysql> select convert(argument using utf8) from mysql.general_log;
+--------------------------------------------------------------+
| convert(argument using utf8) |
+--------------------------------------------------------------+
| show variables like 'log_output' |
| select * from log_output |
| select * from mysql.log_output |
| select * from mysql.general_log |
| show variables like 'log_output' |
| show tables |
| select * from user |
| select * from a |
| select * from test |
| select * from mysql.general_log |
| select convert(argument using utf8) from mysql.general_log |
| select *,convert(argument using utf8) from mysql.general_log |
| select convert(argument using utf8) from mysql.general_log |
+--------------------------------------------------------------+
13 rows in set, 1 warning (0.00 sec)
LOCK
“库存仅剩1”的场景，采用mysql的事务以及写锁来处理
表锁：偏向读
行锁：偏向写
引擎myisam就是加锁快，没有死锁的场景，但发生锁冲突概率很高，并发度很低
手动增加表锁
lock table 表名字 read/write …
查看加锁的表
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| fruitdb | test | 0 | 0 |
| fruitdb | a | 0 | 0 |
| information_schema | SHOW_STATISTICS | 0 | 0 |
| mysql | tablespace_files | 0 | 0 |
| information_schema | COLUMNS | 0 | 0 |
| mysql | column_statistics | 0 | 0 |
| mysql | replication_asynchronous_connection_failover_managed | 0 | 0 |
| mysql | index_stats | 0 | 0 |
| mysql | innodb_table_stats | 0 | 0 |
| mysql | help_topic | 0 | 0 |
| mysql | plugin | 0 | 0 |
| mysql | help_relation | 0 | 0 |
| mysql | help_keyword | 0 | 0 |
| mysql | help_category | 0 | 0 |
| mysql | general_log | 0 | 0 |
| fruitdb | t_fruit | 0 | 0 |
| mysql | tablespaces | 0 | 0 |
| information_schema | TABLES | 0 | 0 |
| mysql | catalogs | 0 | 0 |
| information_schema | SCHEMATA | 0 | 0 |
| mysql | events | 0 | 0 |
| mysql | func | 0 | 0 |
| mysql | time_zone_transition | 0 | 0 |
| mysql | time_zone | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| mysql | slave_worker_info | 0 | 0 |
// 解锁
mysql > unlock tables;
ex:
同一张表mylock
开启两个mysql session，session1将 mylock设置读锁，两个session都可以进行读取，没问题，但是session1进行update操作的时候就失败了。为什么呢？
读阻塞
session1加的mylock表是读锁，加了锁之后，不能读其他的表，不能修改当前mylock表，很类似java程序中的锁，但是session2并不受影响，可以读别的表，但是也不能修改修改mylock表（会block住，因为session1还未解锁，所以session1上读锁了只能对mylock这张表进行读操作，而session2能读但是不能执行写操作，并且可以读其他的表）
写操作
session1加的mylock表是写锁，那么是可以对当前表进行读写的，但是也不能读取别的表，session2对mylcok读写都不行，但对其他的表正常访问
一句话：读锁阻塞写，写锁阻塞读写
经验：由于myisam是表锁，所以执行写操作过多的表不适合，会发生阻塞
引擎myisam是加锁快，并发低，阻塞时间比较长，但不会死锁，得手动加表级锁
引擎innodb则是枷锁慢，并发高，会发生死锁，加的是行及锁，性能损耗会比表及所高一些，当系统并发量比较高的时候，innodb会有较大的优势
行锁比较好理解，session1开启autocommit=0(手动提交)，session2也开启，session1对某一行进行update操作（insert不怕），session2也对该行进行update操作，session2会阻塞，只有当session1 commit update操作之后，才能解除阻塞，如果是update两个不同的行，则不会阻塞。
额外的：索引失效，行锁变表锁，varchar类型需要加单引号，否则被当作int类型处理，索引会失效
间隙锁：session1 update的字段b在where a>1 and a < 6，但是a不存在2这一个值，session2想要插入a=2的一条数据，这时session1执行完，没有commit，session2执行会阻塞。相当于a从1到6都锁住了，就算2没有，但都锁住了，所以session2就只能等着了。
手动上行锁：session1: select * from mylock where a=8 for update，session更新不了a=8的数据，只有session1 执行他想做的操作之后了，commit了，session2才能结束阻塞。
update操作是会申请行锁的吗
是的，UPDATE操作通常会申请行锁来保证数据的一致性和完整性。
行锁是数据库为了实现并发控制而提供的一种锁机制。当一个事务在对数据库中的某一数据行进行UPDATE操作时，为了防止其他事务也对这一行数据进行修改，造成数据的不一致，数据库系统会对这一数据行加锁，这样其他事务就无法对这一行数据进行修改了，只能等待当前事务完成并释放锁之后，才能进行操作。
这样，如果你的UPDATE操作在一个事务中，并且这个事务没有完成（即，没有进行COMMIT或ROLLBACK操作），那么其他所有尝试更新这个行的操作都将等待，直到这个事务结束，释放其锁，其他的UPDATE操作才能进行。这可以保证数据库的一致性，并防止同一时间有两个UPDATE操作争用同一个数据行。
不过，值得注意的是，行锁的具体实现和数据库类型相关，不同类型的数据库系统行锁的实现和行为可能会有所不同。
下面这个可以看出行锁的性能消耗情况
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 123794 |
| Innodb_row_lock_time_avg | 15474 |
| Innodb_row_lock_time_max | 50206 |
| Innodb_row_lock_waits | 8 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
主从复制
原理：slaver节点从master读取binlog，然后执行相同的sql操作实现同步，slaver启动一个i/o线程进行读取，然后写入自己的relayLog中继日志中，然后SQL 线程读取relayLog，两条线程虽然是异步的，但是是串行化的
i/o线程和sql线程必须都是yes，才能完成主从复制，并且进行主从之前master_Log_file和master_log_pos必须得让master和slaver一致.
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)