目录
问题4：InnoDB与MyISAM的区别是？InnoDB是怎样支持事务的？
事务：在一次与数据库的会话中几条SQL要么一起成功，要么一起回滚；
1、事务的四特性：
原子性(Atomicity)：几条SQL放在一起执行，被看作是原子的、不可分割的；也就是放在一个事务中的几条SQL要么一起成功，要么一起失败(银行转账，A用户增499，B用户减499)；
一致性(Consistency)：事务在提交之前的数据与回滚之后的数据是一致的（下面的MVCC多版本并发控制就能保证一致性）；
隔离性(Isolation)：指一个事务的执行不能被其他事物干扰，即一个事务内部操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事物之间不能互相干扰。A事务执行对B事务执行的影响情况，主要看数据库的隔离级别(根据业务来设置);
持久性(Durability)：事务一旦提交对数据的影响是持久的。
2、数据库的隔离级别
事务在并发过程中不可避免的会发生脏读、不可重复的、幻读的问题，那么就需要设置数据库的隔离级别以及给数据加锁来保证数据的安全。
RU：read uncommitted(读未提交)，A事务执行了一条insert into语句，但是还没有commit，B事务就能够select到刚刚新增的这条记录，此时如果A事务回滚，B事务相当于读到了脏的数据（还未持久化，数据变脏的可能性很大）；
RC：read committed(读已提交)，A事务update一条SQL，已经commit进数据库，此时B事务就能读(select)到；解决脏读问题，A事务需要提交了，其他的事务(BCDE)才能读到；但是在同一个事务中，第一次执行select查到的数据是88分，第二次查到的数据变为90分了，造成本次事务执行过程中，前后读到的数据不一致，主要是看业务如何走向，不影响就使用RC级别也够；Oracle数据库的默认隔离级别就是RC(MySQL的默认隔离级别是RR,隔离级别看业务需求设置)。
RR：repeatable read(可重复读)，如果业务中设计到某一时刻用电量的统计与计算，这个时候可使用RR隔离级别，因为电量时时在变，无法确定计算，A事务在第一次读的时候是3946度，第二次可能就3949............，所以设置RR隔离级别，事务只会以在第一次读取的结果为准，但是会出现幻读问题；A事务第一次读到了数据不会改变，那么B事务如果新增了一条数据，A事务也无法即刻知晓，明明在A事务中我没有看到这条数据，但是为什么我就是添加不了这条数据呢？像是出现了幻觉一样；可以加锁来解决数据不一致的问题。
从RU->RC->RR->Serializable，事务并发性越来越严。
Serializable：串行化，完全遵循ACID，但是事务没有并发性，只能一个一个执行，效率低下，不太考虑。
SELECT @@tx_isolation; -- 查看数据库当前的隔离级别
-- 设置数据库的隔离级别 以MySQL为例，Oracle也差不多。
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; -- 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL read committed; -- 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read; -- 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL serializable; -- 串行化3、MVCC设计思想
一图胜千言
事务ABCDE.......属于并发访问的事务；事务id就是记录当前事务的编号，几个事务在读的时候某一时刻是一致的，但是涉及到增删改就会更新事务id。
4、查看MySQL的当前锁
SHOW OPEN TABLES WHERE In_use > 0; -- 查询是否锁表
SHOW PROCESSLIST; -- 查询进程
SELECT * FROM information_schema.INNODB_LOCKS; -- 查看正在锁的事务
SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 查看正在等锁的事务-- 修改表字段名称
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
alter table data_temp_record change extr_auth_id business_id varchar(100)COMMENT '外来授权id';
-- 改表名称
ALTER TABLE 旧表名 RENAME TO 新表名;
alter table xxl_job_user rename to family_user;
-- 新增字段
ALTER TABLE 表名 ADD 字段名 数据类型 COMMENT 字段说明;
alter table product_info add max_amount varchar(100) COMMENT '最高额度(单位：万元)';
-- 新增索引
ALTER TABLE 表名称 ADD INDEX 索引字段(索引名称) USING BTREE;
alter table smart_product add INDEX `result_id`(`result_id`) USING BTREE;
CREATE INDEX mul_orno_bno_bname ON order_books(order_no,book_no,book_name);
-- 修改索引名称
ALTER TABLE 表名称 RENAME INDEX 旧索引名称 TO 新索引名称;
ALTER TABLE ss_data_temp_record RENAME INDEX `auth_id` TO `in_business_id`;
-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名(futxt_idx_content);
ALTER TABLE book DROP INDEX futxt_idx_content;
DROP INDEX futxt_idx_content ON book;
-- 修改索引的可见性
ALTER TABLE 表名 ALTER INDEX 索引名 invisible(不可见，visible可见的);
-- copy表结构 LIKE，AS不会将索引等copy
CREATE TABLE order_info_202402 LIKE order_info_202401;
CREATE TABLE order_item_202402 LIKE order_item_202401;问题1：MySQL有哪几种索引类型？
NORMAL：普通索引，最为常见用的最多。直接create index 索引名 no 表名，就可以创建了；
UNIQUE：唯一索引，对标数据库表中具有唯一业务的字段（建表所用字段，修饰关键字也是UNIQUE），例子：如果表中有身份证ID字段，天然就是一个不重复的。
FULLTEXT：全文索引，用于字段数据量较大的建索引，提高查询效率，但是数据量太大一般都用ElasticSearch中间件做一个倒排索引，这样能更好地提高检索效率，且不容易失效。
SPATIAL：空间索引，InnoDB不支持，
问题2：MySQL有哪几种索引方法？
BETREE：B+树，每层容纳节点数量大，且数据从左向右，从小到大排列，很好支持范围查找。
HASH：Hash表，单个数据查询时间复杂度O(1)，一次操作就行；但是不支持范围查找，因为Hash表的结构就是数据散列的。
问题3：EXPLAIN执行计划
1、Id列：id数值大的先执行，数值相等，位于上面的先执行，id为空，表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中(下面有例子)。
2、select_type列：DERIVED【派生表 #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`，将表包了一层】 from关键字后面的子查询，会产生衍生表；PRIMARY 【 #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
#查询的`select_type`值就是`PRIMARY`】最外层主查询；SIMPLE简单查询(不包含子查询的)； SUBQUERY 【
#子查询：
#如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。
#该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`】；UNION 连接2个及2个以上的select查询(联合查询)【 #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
#以外，其余的小查询的`select_type`值就是`UNION`】。
3、table列：查询的表名称，也可能有派生表，临时表union
4、type列：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge_unique_subquery>index_subquery>range>index>all(性能由高到低排列)
null：聚合函数查询，直接索引树上拿数据；
system：很少见，比如用MyIsam做存储引擎时，查询表中的一条记录，或者count(*)时，因为MyIsam存储引擎会将表中的数据用一个常量统计，用时直接取；
const：使用主键索引或唯一索引和常量进行比较；
eq_ref：多表连接查询，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
，（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
，对该被驱动表的访问方法就是`eq_ref`。
ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法；就可能是`ref_or_null`。
ref：查询条件是普通的索引列和常量进行比较；
range：主键索引做范围查找；索引优化，最少也要达到range级别
index：查询没有进行条件判断，所有数据可以从索引树上拿；
all：全表扫描。
5、possible_keys列：可能用到的索引
6、key列：实际用到的索引，与possible_keys一起看，该SQL有无走索引
如果possible_keys有索引，而实际是没有走索引，且type是all，说明MySQL内部优化器进行判断，走索引还不如全表扫描来得快些；一般的，二级索引的叶子节点存放主键值，查询到数据后再根据主键值，走主键索引（聚簇索引）查到数据，需要扫描2棵索引树，还不如一次性扫描表中全部的数据。
聚簇索引在InnoDB中，只有一颗，即索引即数据，数据即索引；
非聚簇索引就是那些二级索引
7、key_len：索引字符的长度，多用于联合索引，看其命中索引情况。主要看你使用的索引字段，设计数据类型与长度，看你使用的字符集charset=utf8，一个中文是3个字节，例如：varchar(100)，那么名字该字段就是 3* 100，且该字段是可变长字符串，还需要2位存储实际数据存放长度，需要+2，如果该字段允许为NULL，还需要一位来记录，需要+1，最后结果命中该字段即是303；utf8mb4：一个中文4个字节；latin1(拉丁)，不支持中文占1个字节的英文；gbk：占2个字节；数值类型：tinyint 1个字节，smallint 2个字节，int 4个字节，bigint 8个字节；时间类型：date 3个字节，timestamp 4个字节，datetime 8个字节。
8、rows列：该次查询可能查询的条数
9、Extra列：额外的信息，有
using index：使用索引;
using where：没有使用到索引;
using index condition：索引下推
using temporary：会创建临时表来执行，比如在没有索引的列上进行去重操作，就需要临时表来实现;
using filesort：使用了文件排序，会使用磁盘+内存的方式进行文件排序。
EXPLAIN不考虑各种Cache，即不考虑是在磁盘还是在内存中加载的数据；
EXPLAIN不能显示MySQL在执行查询时所作的优化工作；
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
部分统计信息是估算的，并非精确值。问题4：InnoDB与MyISAM的区别是？InnoDB是怎样支持事务的？
InnoDB支持事务，MyISAM 不支持事务；
InnoDB会根据Redo Log进行事务提交后的持久化；根据Undo Log进行事务失败后的回滚操作；
以一个updatue语句为例：
1、InnoDB在收到一个update语句之后，会根据条件找到数据所在的页，并将该页缓存在buffer pool 中；
2、执行update语句，修改buffer pool中的数据，也就是加载到内存中的数据；
3、针对update语句生成一个RedoLog对象，并存入到LogBuffer中，用于提交成功之后做持久化；
4、针对update语句生成（一条反向数据的update语句）Undo Log日志，用于事务回滚；
5、如果事务提交，那么则把RedoLog对象进行持久化，后续还有机制将buffer pool中所修改的数据页持久化到磁盘中
6、如果事务回滚，则利用Undo Log日志进行回滚。
问题5：事务的传播特性
一共有5个，一般用的比较多的是REQUIRED(默认的)和SUPPORTS；都是放在@Transactional注解里面的
1、方法A调方法B，注解都是放在方法B上的；
2、REQUIRED：如果方法A当前有事务，那么方法B的事务就加入进去，AB里面的SQL要么全部成功，要么全部回滚；如果方法A不存在事务，那么方法B就新建一个事务，自己执行，成功就提交，失败就回滚；
3、SUPPORTS：如果方法A存在事务就加入进去一起变成一个事务，如果不存在事务，方法B就以非事务方式运行。
复制表，AS能复制，但是没有LIKE强大，LIKE可以复制索引等，all in所有的数据结构
CREATE TABLE delivery_info_202401 LIKE delivery_info_202312;2年前的真实案例：
为什么我的微信号就是绑定不了，而且也不能实现自动登录！
alter table user_info convert to character set utf8mb4 collate utf8mb4_general_ci;执行上面那句SQL就可以了，将表改成支持emoji表情就行了，因为微信昵称是支持emoji表情的，所以有些人昵称中有相关表情，结果造成微信接口返回的数据无法落库，最后造成不能自动登录等等相关操作。
以上就是我最近温故而知新的初见，欢迎大家品评、探讨；如果今后发现查漏补缺，会再修正！
扫一扫
1596
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
