mysql索引和事务原理

发表于 更新于 Changyan： 
1
https://dev.mysql.com/doc/dev/mysql-server/latest/

初识mysql


按照数据结构来组织、存储和管理数据的仓库。

  • OLTP（on-line transaction processing）联机事务处理，主要数据库增删改查。
  • OLAP（on-line analytical processing）联机分析处理，主要对数据库进行统计分析，为决策提供支持。
    SQL命令：
  • DQL，数据查询语言，select
  • DML（data manipulate language），数据操作语言，插入删除更新记录
  • DDL（data define language）,数据定义语言，创建修改删除表
  • DCL（data control language），数据控制语言，授予用户权限、收回用户权限
  • TCL（transaction control language），事务控制语言，事务提交，事务回滚
    连接池模型：
1
2
3
4
5
6
7
8
select(listenfd+1, readfds, NULL, NULL, 0); // 一直阻塞。使用select，fd少，可以跨平台
int clientfd = accept(listenfd, &addr, &len);
mysql_thread_create(key_thread_one_connection, &id, &connection_attrib, handle_connection, (void*)channel_info);

while (1) { // 连接作为单独的session
    int n = read(clientfd); // 阻塞的
    do_command();
}

存在一个buffer pool，128M的大内存池，可以存放128M/16K的页，有一个free链表专门记录这个池子里free的节点，flush链表专门用来记录脏数据的节点，当池子满了以后，需要淘汰数据，使用lru链表，lru分为冷数据区和热数据区，如果某个页连续访问的时间大于1s，则将其移到热数据区，预防全表扫描的情况。flush链表里面的数据会有一个io线程定期写入磁盘。为了防止进程挂掉导致数据丢失，需要将sql命令写入redo log，并持久化，由于是顺序（提前生成ib_logfile文件）写所以性能高。这里一般有两个logfile文件，当两个文件都写满了以后，就触发一次checkpoint，并将脏数据持久化到磁盘，才可以继续清空原logfile文件重新写。可以调大logfile文件大小，这样可以减少刷盘的频率，但是启动进程后加载会更慢。
设计三范式

  • 确保每列保持原子性；数据库表中的所有字段都是不可分割的原子值。某表中有一个地址字段，如果经常需要访问地址字段中的城市属性，则需要将改字段拆分为多个字段，省份、城市、详细地址等。

  • 确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）。

    订单编号 商品编号 商品名称 数量 单位 价格 客户 所属单位
    1 1 电脑 1 台 10000 mark apple
    1 2 手机 2 部 6000 mark apple
    1 3 ipad 3 部 3000 milo apple

    需要修改为：

    订单编号 客户 所属单位
    1 mark apple
    2 milo apple
    商品编号 商品名称 单位 商品价格
    1 电脑 台 10000
    2 手机 部 6000
    3 ipad 部 3000
    订单编号 商品编号 数量
    1 1 1
    1 2 2
    2 3 3

  • 确保每列都和主键直接相关，而不是间接相关，减少数据冗余。

    订单编号 订单项目 业务员 订单数量 客户端编号
    1 电脑 秋香 10 1
    2 手机 贝贝 20 2
    客户端编号 客户名称 联系方式
    1 vico apple
    2 milo apple

crud：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
--  创建表
CREATE TABLE IF NOT EXISTS `0voice_tble` (
    `id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号',
    `course` VARCHAR(100) NOT NULL COMMENT '课程',
    `teacher` VARCHAR(40) NOT NULL COMMENT '讲师',
    `price` DECIMAL(8, 2) NOT NULL COMMENT '价格',
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

-- 删除表
DROP TABLE `table_name`;

-- 清空数据表
TRUNCATE TABLE `table_name`; -- 截断表，有自增索引的话，从初始值开始累加
DELETE TABLE `table_name`; -- 逐行删除，有自增索引的话，从之前值继续累加

-- 增
INSERT INTO `ovoice_table`(`course`, `teacher`, `price`) VALUES('服务器架构', 'mark', 5690.0);

-- 删除
DELETE FROM `ovoice_table` WHERE id = 3;

-- 改
UPDATE `0voice_tbl` SET `teacher` = 'mark' WHERE id = 2;
UPDATE `0voice_tbl` SET `age` = `age` + 1 WHERE id = 2;

-- 查
SELECT `price` FROM `0voice_tbl` WHERE id = 2;

-- 高级查询
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号',
    `name` VARCHAR(100) NOT NULL COMMENT '姓名',
    `age` TINYINT NOT NULL COMMENT '年龄',
    `sex` TINYINT NOT NULL COMMENT '性别（1:男；2:女）',
    `score` SMALLINT NOT NULL COMMENT '分数',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = '学生表';

INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('darren', 21, 1,100);
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('vico', 22, 1,102);
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('king', 23, 1,107);
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('mark', 24, 1,120);

SELECT * FROM student;
SELECT `name`, `age` FROM student;
SELECT `name` AS '姓名', `age` AS '年龄' FROM student;
SELECT distinct `score` FROM student; -- 去重

SELECT * FROM `student` WHERE `name` = 'mark';
SELECT * FROM `student` WHERE `sex` = 1 AND `age` = 22;

SELECT * FROM `student` WHERE age BETWEEN 22 AND 23;

SELECT * FROM `student` WHERE `score` IS NOT NULL;
SELECT * FROM `student` WHERE `score` IS NULL;
SELECT * FROM `student` WHERE sex <> '';
SELECT * FROM `student` WHERE sex = '';

SELECT * FROM `student` WHERE `name` LIKE 'm%'; -- m开头
SELECT * FROM `student` WHERE `name` LIKE '_a%'; -- 第二个字母为a

SELECT * FROM `student` LIMIT 1,2; -- 第一个参数为位置，从0开始，第二个参数是数量，查询第二条到第三条的内容

SELECT * FROM `student` ORDER BY `age` ASC; -- asc升序，desc降序
SELECT * FROM `student` ORDER BY `age` DESC `score` DESC;

SELECT sum(`age`) FROM `student`;
SELECT avg(`age`) FROM `student`;
SELECT max(`age`) FROM `student`;
SELECT min(`age`) FROM `student`;
SELECT count(`age`) FROM `student`;

SELECT `sex`, group_concat(DISTINCT `age` ORDER BY age DESC) as ages FROM `student` GROUP BY `sex`; -- sex对应的age有多个，需要使用group_concat把多个结果连接起来，或者使用聚合函数比如sum也是可以的
SELECT `sex` FROM `student` GROUP BY `sex`; -- 可以把查询出来的结果根据某个条件来分组显示
SELECT `sex`, count(*) as num FROM `student` GROUP BY `sex`;
SELECT `sex`, count(*) as num FROM `student` GROUP BY `sex` HAVING num > 3;

-- 连表查询
CREATE TABLE IF NOT EXISTS `dept` (
    `id` INT UNSIGNED AUTO_INCREMENT COMMENT '部门编号',
    `name` VARCHAR(32) NULL COMMENT '部门名称',
    PRIMARY KEY (`id`)
) COMMENT = '部门表';

INSERT INTO `dept` (`name`) VALUES (`讲师`);
INSERT INTO `dept` (`name`) VALUES (`助教`);
INSERT INTO `dept` (`name`) VALUES (`推广`);

CREATE TABLE IF NOT EXISTS `employee` (
    `id` INT UNSIGNED AUTO_INCREMENT COMMENT '员工编号',
    `name` VARCHAR(32) NULL COMMENT '姓名',
    `sex` TINYINT NOT NULL DEFAULT 1 COMMENT '性别（1:男；2:女）',
    `age` TINYINT NULL COMMENT '年龄',
    `salary` DECIMAL(8, 2) NULL DEFAULT 1000 COMMENT '薪水',
    `dept_id` INT NOT NULL COMMENT '部门编号',
    PRIMARY KEY(`id`)
)COMMENT = '雇员表';

INSERT INTO `employee` (`name`, `sex`, `age`, `salary`, `dept_id`) VALUES('darren', 1, 20, 10009, 1);
INSERT INTO `employee` (`name`, `sex`, `age`, `salary`, `dept_id`) VALUES('beibei', 2, 20, 9009, 2);
INSERT INTO `employee` (`name`, `sex`, `age`, `salary`, `dept_id`) VALUES('yiyi', 2, 20, 1009, 2);
INSERT INTO `employee` (`name`, `sex`, `age`, `salary`, `dept_id`) VALUES('milo', 1, 20, 20909, 1);
INSERT INTO `employee` (`name`, `sex`, `age`, `salary`, `dept_id`) VALUES('mark', 1, 20, 20009, 1);
INSERT INTO `employee` (`name`, `sex`, `age`, `salary`, `dept_id`) VALUES('king', 1, 20, 40009, 1);

SELECT * FROM `employee` INNER JOIN `dept` ON `employee`.dept_id = `dept`.id; -- 只取两张表有对应关系的记录
SELECT * FROM `employee` LEFT JOIN `dept` ON `employee`.dept_id = `dept`.id; -- 在内连接的基础上保留左表没有对应关系的记录
SELECT * FROM `employee` RIGHT JOIN `dept` ON `employee`.dept_id = `dept`.id; -- 在内连接的基础上保留右表没有对应关系的记录

-- 子查询/合并查询
SELECT * FROM `employees` WHERE `dept_id` = (SELECT `id` FROM `dept` WHERE `name` = '讲师'); -- 知道部门名字，找该部门所有员工
SELECT * FROM `employees` WHERE `dept_id` = (SELECT `dept_id` FROM `employees` WHERE `name` = 'mark'); -- 知道名字，找与它同部门的所有员工

-- 正则表达式
SELECT * FROM `employee` WHERE `name` REGEXP '^m';
SELECT * FROM `employee` WHERE `name` REGEXP 'k$';
SELECT * FROM `employee` WHERE `name` REGEXP '^M..k$';
SELECT * FROM `employee` WHERE `name` REGEXP '[ceo]';

视图是一种虚拟存在的表，是一个逻辑表，本身并不包含数据，其内容由查询定义。用来创建视图的表叫基表。通过视图可以展现基表的部分数据。
可复用，类似于函数。重复利器，假如user被拆分为usera和userb，使用select * from user会提示不存在；此时可以创建视图create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name，只需要修改数据库结构，不需要更改应用程序。
屏蔽查询细节，只关注数据返回。
权限控制，某些表对用户屏蔽，但是可以给该用户通过视图对表操作，不能插入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CREATE TABLE `user` (
    `id` INT AUTO_INCREMENT COMMENT '编号',
    `name` VARCHAR(32) COMMENT '学生姓名',
    `sex` TINYINT DEFAULT 1 COMMENT '性别（1:男；0:女）';
    `age` TINYINT DEFAULT 18 COMMENT '年龄',
    PRIMARY KEY (`id`)
) COMMENT = '学生表' ENGINE=InnoDB;

CREATE TABLE `goods` (
    `id` INT AUTO_INCREMENT COMMENT '编号',
    `name` VARCHAR(32) COMMENT '商品名称',
    `price` DECIMAL(10, 6) DEFAULT 0 COMMENT '价格',
    PRIMARY KEY(`id`)
) COMMENT = '商品表' ENGINE=InnoDB;

CREATE TABLE `user_goods` (
    `id` INT AUTO_INCREMENT COMMENT '编号',
    `user_id` INT COMMENT '用户ID',
    `goods_id` INT COMMENT '商品ID',
    PRIMARY KEY(`id`)
) COMMENT = '用户商品表' ENGINE=InnoDB;

-- 创建视图
CREATE VIEW view_test AS SELECT
    `user`.id AS user_id,
    `user`.`name` AS user_name,
    `user`.`sex` AS user_sex,
    `user`.`age` AS user_age,
    `goods`.id AS goods_id,
    `goods`.`name` AS goods_name
FROM `user`
JOIN `user_goods` ON `user`.id = `user_goods`.user_id
JOIN `goods` ON `goods`.id = `user_goods`.goods_id;

-- 调用
SELECT * FROM view_test;

-- 删除视图
DROP VIEW view_test;

-- 创建用户
CREATE USER username@host IDENTIFIED BY password; -- host指定用户在哪个主机可以登陆，本地localhost，任意则用通配符%
-- 授权
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; -- privileges为用户的操作权限，如select，insert，update等，所有权限为all；WITH GRANT OPTION代表该用户可以将自己的权限授予别人。
-- 对视图授权
GRANT select, SHOW VIEW ON `database`.`tablename` to 'username'@'host';
-- 刷新权限
FLUSH PRIVILEGES; -- 修改权限后需要刷新权限

-- 远层连接设置
-- mysqld.cnf
#bind-address=127.0.0.1
-- 修改user表
select `user`, `host` from `mysql`.`user`;
update user set host = '%' where user = 'root';

触发器：是否具有事务性？MYSQL提供给程序员和数据分析员来保证数据完整性对一种方法。对表进行DML操作（insert/delete/update）时会激活它执行。触发时间befer/after.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body
-- trigger_body: 可以一个语句，也可以是多个语句，多个语句写在BEGN...END间
-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | DELETE | UPDATE }
-- trigger_order: { FOLLOWS | PRECEDES }

CREATE TABLE `work` (
    `id` INT PRIMARY KEY AUTOINCREMENT,
    `address` VARCHAR(32)
) DEFAULT charset = utf8 ENGINE = INNODB;
CREATE TABLE `time` (
    `id` INT PRIMARY KEY AUTOINCREMENT,
    `time` DATETIME
) DEFAULT charset = utf8 ENGINE = INNODB;

CREATE TRIGGER trig_test AFTER INSERT ON `work` FOR EACH ROW INSERT INTO `time` VALUES(NULL, NOW());

-- 例子
CREATE TABLE `goods` (
    `id` INT PRIMARY KEY auto_increment,
    `name` VARCHAR(32),
    `num` SMALLINT DEFAULT 0
);

CREATE TABLE `order` (
    `id` INT PRIMARY KEY auto_increment,
    `goods_id` INT,
    `quantify` SMALLINT COMMENT '下单数量'
);

INSERT INTO `goods` VALUES(NULL, 'c++', 50);
INSERT INTO `goods` VALUES(NULL, 'c', 50);
INSERT INTO `goods` VALUES(NULL, 'mysql' 50);
INSERT INTO `order` VALUES(NULL, 1, 3);
INSERT INTO `order` VALUES(NULL, 2, 4);

-- delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号；，如果一次输入的语句较多，并且语句中间有分号，这时需要重新指定一个特殊的分隔符，通常指定$$或||
delimiter //
CREATE TRIGGER trig_order AFTER INSERT
ON `order` FOR EACH ROW
BEGIN
    UPDATE goods SET num = num -2 WHERE id = 1;
END//
delimiter;

-- 库存数量自动改变
delimiter //
CREATE TRIGGER trig_order BEFORE UPDATE
ON `order` FOR EACH ROW
BEGIN
    UPDATE goods SET num=num+old.quantity-new.quantity WHERE id = new.goods_id;
END//
delimiter;
UPDATE `order` SET quantity=quantity+2 WHERE id = 1; -- 测试

存储过程：多条执行语句一次网络传输，执行速度相对快。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
DELIMITER //
CREATE PROCEDURE proc_test()
BEGIN
    SELECT current_time();
    SELECT current_date();
END
//
DELIMITER ;
call proc_test();

DELIMITER //
CREATE PROCEDURE proc_in_param(IN p_in INT)
BEGIN
    SELECT p_in;
    SET p_in = 2;
    SELECT p_in;
END //
DELIMITER ;

SET @p_in = 1; -- 调用
CALL proc_in_param(@p_in);
SELECT @p_in; -- p_in虽然在存储过程中被修改，但是不影响@p_in的值

DELIMITER //
CREATE PROCEDURE proc_out_param(OUT p_out INT)
BEGIN
    SELECT p_out;
    SET p_out = 2; -- 只能接收一个字段
    SELECT p_out;
END//
DELIMITER ;

SET @p_out=1; -- 调用
CALL proc_out_param(@p_out);
SELECT @p_out;

-- INOUT既可以传参，又可以被修改

游标：相当迭代器，select查询到的结果的每一行可以进行独立的操作。

1
2
3
4
5
6
7
8
9
DECLARE cursor_name CURSOR FOR select_statement; -- 定义游标
OPEN cursor_name; -- 打开游标
FETCH cursor_name INTO var_name[, var_name,...] -- 取游标数据
CLOSE cursor_name; -- 关闭游标
DEALLOCATE cursor_name; -- 释放游标

-- 设置游标结束标志
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- done为标志

事务

数据库执行的最小单元，比如A付款B的100元，beign；A-100，B+100；end；这是需要原子执行的，要么全部成功，要么全部失败。在innodb中，每条语句都是事务，可以通过set autocommit = 0;设置当前会话提交。

1
2
3
4
5
6
7
START TRANSACTION | BEGIN
select * from project;
COMMIT
ROLLBACK -- 也会结束事务
SAVEPOINT identifier
RELEASE SAVEPOINT identifier
ROLLBACK TO [SAVEPOINT] identifer

特性：

  • 原子性：要么提交要么回滚，undo log是一张临时表，回滚就是做逆运算。
  • 隔离性：通过MVCC和锁来实现，MVCC是多版本并发控制，主要解决一致性非锁定读，而不是使用锁来限制读。锁用来处理并发DML操作。
  • 持久化：事务DML操作将会持久化，实时顺序写入redolog磁盘文件（哪个页、页偏移、具体数据），即使发生宕机，数据库也能恢复。redolog相当于一个消息队列，undolog的数据先写入redolog，再慢慢落盘，异步写入b+树中。
  • 一致性：由其它三者保证。

并发异常：

  • 脏读：读到未提交的数据，如果另外一个会话ROLLBACK了，可能读到不存在读数据，原因为read uncommitted读未加锁也没有mvcc支持，可以通过mvcc支持 
    1
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  • 不可重复读：读到其他事务提交的数据，造成两次读结果不一致。一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题。原因为read committed隔离级别下mvcc，读取最新的历史版本数据。可以修改mvcc读取定义，读取事务开始前的历史版本。 
    1
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 幻读：一个事务中某次读操作不能作为接下来业务逻辑的依据。原因为repeatable read隔离级别下读操作使用mvcc，读操作未加锁，而其它回话可以进行写操作，类似于if (a == 5) b = 4; 但判断完a后，a就被修改了，此时去修改b是不合理的 。可以手动给读操作加S锁或者X锁，通过next-key lock锁住读取范围，避免范围内插入数据 
    1
    2
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT * FROM concurrent_exception_t WHERE id = 5 lock in share mode; -- 加共享读锁，就不会被其他会话修改了，会被阻塞，SERIALIZABLE隔离级别下会自动加上这把间隙锁

隔离级别：

  • read uncommitted，读不做任何处理，写自动加x锁
  • read committed，读采用mvcc，读取最新版本的行数据，写自动加x锁
  • repeatable read，读采用mvcc，读取事务开始前版本的行数据，写自动加x锁
  • serializable，读自动加s锁（share）（next-key lock），写自动加x锁

MVCC：用来实现一致性的非锁定读；不需要等待访问的行上x锁的释放；在read committed和repeatable read下，innodb使用MVCC，但对于快照的数据定义不同；在read committed隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据；而在repeatable read隔离级别下，总是读取事务开始时的行数据版本；
因为没有事务对历史的数据进行修改操作，所以读取快照不需要上锁。有多少个事务在修改就有多少个快照版本。存在undolog里面，存储在共享表空间中 。

锁类型：按粒度划分为行锁、表锁、间隙锁；还可以分为排他锁和共享锁；还可以分为乐观锁和悲观锁。在事务的隔离级别中，需要用锁来解决幻读。
共享锁和排他锁都是行级锁；事务采用的是粒度锁；针对表（B+树）、页（B+树叶子节点）、行（B+树叶子节点中某一段记录行）三种粒度加锁。意向共享锁和意向排他锁都是表级别的锁。

  • 共享锁：事务读操作加的锁，对某一行加锁；在serializable隔离级别下，默认帮读操作加共享锁；在repeatable read隔离级别下，需手动加共享锁，可解决幻读问题；在read committed隔离级别下，没必要加共享锁，采用的锁MVCC；在read uncommitted隔离级别下，既没有加锁也没有使用MVCC；
  • 排他锁：事务删除或更新加的锁，插入除了加x锁还会加特殊的gap锁（insert intention lock），对某一行加锁；在四种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放。
  • 意向共享锁：对一张表中某几行加的共享锁，不由程序员控制
  • 意向排他锁：对一张表中某几行加的排他锁，不由程序员控制

锁具体实现算法：

  • record lock: 行锁，对表的索引加锁
  • gap lock：全开空间
  • next-key lock：record lock + gap lock，前开后闭空间
  • insert intention lock：1，5中间插入4时使用
  • auto-inc lock：5之后插入新值使用，特殊的表锁（粒度比较大）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
select * from t where id = 5 for update;
-- id为主键 RC隔离级别
-- 在主键id=5行上加x锁

-- id锁唯一索引 RC隔离级别
-- 在唯一索引id=5行上加x锁，在主键索引上对应行加x锁

-- id为非唯一索引 RC隔离级别
-- 在非唯一索引上所有id=5行上加x锁，对应主键索引列加x锁

-- id不是索引 RC隔离级别
-- 在聚集索引上扫描，所有行加上x锁，此处有个优化，不满足的行在加锁后，判断不满足即可释放锁

-- id为主键 RR隔离级别
-- 在主键id=5行加x锁

-- id是唯一索引 RR隔离级别
-- 在唯一索引id=5行上加x锁，在主键索引上对应行加x锁

-- id为非唯一索引 RR隔离级别
-- 在非唯一索引上查找id=5的行，找到则加上x锁和gap锁，然后对应的聚集索引加上x锁；没有找到则加上gap锁

-- id不是索引 RR隔离级别
-- 在聚集索引上扫描，所有行加上x锁和gap锁

-- 在RR下
-- 不加任何锁
select * from t;
-- 扫描到任何索引行上加s锁（next-key lock），在聚集索引上加x锁
select * from t lock in share mode;
-- 扫描到任何索引上加x锁（next-key lock），在聚集索引上加x锁
select * from t for update;
-- 扫描到任何索引上加x锁（next-key lock），在聚集索引上加x锁
update ... where condition
delete from t where condition
-- 如果是间隙插入，先添加insert intention lock，后在该行上加x锁；
-- 如果是递增插入，添加auto-inc lock或者轻量级的互斥锁
insert into t;

索引

一个表里面有10000条数据是怎么存储的？B+树，多路平衡搜索树，（平衡二叉树，由于只有两路，它的高度很高，决定访问磁盘的次数），所有的数据是存储在叶子节点上的，叶子节点之间采用链表的方式，而B树每个节点都存储了数据，为什么选择了B+树？B+树可以进行范围查找，因为采用的是链表。


select * from user where id >=18 and id < 40;过程如图。

特点：

  1. 比较key，有序结构，所有的数据都在叶子节点，高度平衡。
  2. b+树的高度代表磁盘io的次数，一般2～4层。
  3. 节点的大小默认为16k。
  4. 叶子节点当中存储所有的数据，非叶子节点只存储索引信息。
    分类：
  • 主键索引：非空唯一索引，一个表只有一个主键索引，PRIMARY KEY(key)
  • 唯一索引：不可以出现相同的值，可以为NULL，UNIQUE(key)
  • 普通索引：允许有相同的索引内容，INDEX(key)，或KEY(key[,…])
  • 组合索引：INDEX idx(key1, key2[,…]);、UNIQUE(key1, key2[,…]);、PRIMARY KEY(key1, key2[,…]);，组合索引里面并不会存全量的数据，只会存组合索引的数据加主键，通过组合索引找到主键，再到主键索引里面回表找完整的数据。
  • 全文索引：短字符串中用LIKE %;全文索引中用match和against;
    索引选择：
  1. 如果显示设置PRIMARY KEY，则该设置的key为该表的主键
  2. 如果没有显示设置，从非空唯一索引中选择；
    • 只有一个非空唯一索引，则选择该索引为主键
    • 有多个非空唯一索引，则选择声明的第一个为主键
  3. 没有非空唯一索引，则自动生成一个6字节（容量越大）的_rowid作为主键。
    约束：primary key(unique key, not null), unique key, foreign key, default, not null，约束是逻辑上的概念，索引是一个数据结构，既包含逻辑的概念也包含物理的存储方式。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table parent (
    id int not null,
    primary key(id)
) engine=innodb;
create table child (
    id int,
    parent_id int,
    foreign_key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb;
-- 被引用的表为父表，引用的表为子表；
-- 外键定义时，可以设置行为ON DELETE和ON UPDATE，行为发生时的操作可选择：
-- CASCADE 子表做同样的行为，具有事务性
-- SET NULL 更新子表相应的字段为NULL
-- NOT ACTION 父类做相应行为报错
-- RESTRICT 同NO ACTION
INSERT INTO parent VALUES(1);
INSERT INTO parent VALUES(2);
INSERT INTO child VALUES(10, 1);
INSERT INTO child VALUES(20, 2);

索引存储：innodb由段、区、页组成，段分为数据段、索引段、回滚段等。区大小为1MB（一个区由64个连续页组成）
；页默认为16K；页为逻辑页，磁盘物理页大小一般为4K或8K

聚集索引是由主键构成的索引，辅助索引不包含主键，可以通过辅助索引找到主键，再通过主键索引找到数据。如果没有辅助索引，那么需要遍历全部的聚集索引，效率低下，可以把索引看做一棵b+树。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- 某个表包含id name lockyNum，id是主键，lockyNum存储辅助索引
select * from user where lockyNum = 33;

-- 最左匹配原则：对于组合索引，从左到右依次匹配，遇到> < between like就停止匹配
KEY(`name`, `lockyNum`)
select * from user where name = 'mark' -- ok
select * from user where lockyNum = 33 -- 失效，改为聚集索引

CREATE TABLE `left_match_t` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) DEFAULT NULL,
    `cid` INT(11) DEFAULT NULL,
    `age` SMALLINT DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `name_cid_INX` (`name`, `cid`),
    KEY `name_INX` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

SHOW INDEX FROM `left_match_t`;

SET optimizer_trace='enabled=on';
EXPLAIN SELECT * FROM `left_match_match` where name = 'mark'; -- 选择name_cid_INX
SELECT * FROM information_schema.optimizer_trace\G

EXPLAIN SELECT * FROM `left_match_match` where cid = 4; -- 全表扫描

-- 覆盖索引：从辅助索引中就能找到数据，而不需要通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树，较少的磁盘io次数。
EXPLAIN SELECT `name`, `cid`, `id` from left_match where cid = 4; -- 覆盖索引name_cid_INX，辅助索引中包含有主键的信息

create table t(
    A int,
    B int,
    C int,
    D int,
    E int,
    primary key(A),
    INDEX(B, C, D)
);

-- 查询B=xxx对应A的值：
    select * from t where B = xxx;
    select A from t where B = xxx;

-- 性能差别：
    select * from t where B = xxx and C = yyy; // 能命中索引吗?
    select * from t where B = xxx and D = yyy; // 能命中索引吗?
    select * from t where C = xxx and D = yyy; // 能命中索引吗?

索引失效

  1. select … where A and B, 若A和B中有一个不包含索引，则索引失效
  2. 索引字段参与运算，则索引失效；例如where from_unixtime(idx) = ‘2021-04-30’; where a+1=1;
  3. 索引字段发生隐式转换，则索引失效，例如’1’隐式转换为1；
    字符’a’,’b’等都会被转为整数0，’123’会被转为实际的整数。select * from xx where e = 0; e字段为char，则字段值的字符会被全部转为整数才能对比，如果字符索引转为数字索引，此时索引可能会被破坏，所以这种情况不会走索引，导致索引失效。
  4. LIKE模糊查询，通配符%开头，则索引失效，例如select * from user where name = ‘%ark’;
  5. 索引字段上使用NOT <> !=索引失效，可以修改为idx>0 or idx <0
  6. 组合索引中，没有使用第一列索引，索引失效，覆盖索引除外。

索引原则：

  1. 查询频次较高且数据量大的表建立索引；
  2. 使用短索引，节点包含的信息多，较少磁盘io操作，如辅助索引。
  3. 对于很长的动态字符串，考虑使用前缀索引。 
    1
    2
    3
    4
    5
    6
    select count(distinct left(name, 3))/count(*) as sel3,
    count(distinct left(name, 4))/count(*) as sel4,
    count(distinct left(name, 5))/count(*) as sel5,
    count(distinct left(name, 6))/count(*) as sel6,
    from user;
    alter table user add key(name(4)); -- 前缀索引不能做order by和group by
  4. 对于组合索引，考虑最左侧匹配原则和覆盖索引，频次越高的key放越左边。
    最左匹配原则：如果索引为bcd索引，那么至少要有b的查找，并不是b、c、d的and连接顺序，如果没有b查找，就没办法走索引，如果查询bd，是可以走索引的。对于组合索引b>3这样的全量数据查询，如果3在索引的最左边了，此时走组合索引，会回表多次，还没走全扫描的快，所以这种情况会走全标扫描。
    覆盖索引：对于bcd索引，如果查询select b from xx where b > 1, 查到的数据里面就有b字段，此时不需要回表，这种就叫覆盖索引。
    select b from xx这种不带where的查询，也会考虑走bcd索引，因为bcd索引存的是不完整的数据，一页存的数据量更多，索引结构更小。
    select * from xx order by b,c,d; 因为也是取全量数据，所以直接全表扫描再内存排序比较快。
  5. 尽量选择区分度高的列作为索引，该列的值相同的越少越好。 
    1
    select count(distinct idx)/count(*) from table_name;
  6. 尽量扩展索引，在现有索引的基础上，添加复合索引。
  7. 不要select *，尽量只列出需要的列字段；
  8. 索引列，列尽量设置为非空；

慢日志查询：

1
2
3
4
5
SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 4; -- 单位s，默认10s

或者修改配置

1
2
3
slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log

查找最近10条慢日志：mysqldumpslow -s t -t 10 -g ‘select’ D:/mysql/mysql57-slow.log

分区表：分区并不一定带来性能提升

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
show variables like '%partition%'
show plugins;

create table range_t{ -- range分区，用户很久不用的数据可以分区
    id INT
 } engine = innodb
partition by range(id)(
    partition p0 values less than(10),
    partition p1 values less than(20)
);

create table hash_t { -- hash分区
    id INT,
    tm DATETIME
}engine = innodb
partition by hash(YEAR(tm))
partitions 4;

存储引擎

来自客户端的数据，到解析器-优化器-执行器，数据丢到存储引擎中。
myisam与innodb的区别是什么？myisam节点是存储的引用，存储的数据量少，数据是存在引用的地址里，查找到节点后，还需要去磁盘寻址，如博客可以用myisam，因为文章数据量比较大，全文索引也可以用这个。

为什么myisam不需要支持事务？
myisam表有三个文件，分别为frm格式（表结构）、myd数据、myi索引文件，读取性能比较高。innodb表有两个文件，分别为frm格式（表结构），ibd（innodb data）数据和索引。

如何分析SQL执行过程？

  1. EXPLAIN语句：在执行SQL语句前，使用EXPLAIN语句可以查看MySQL优化器的执行计划，即查询优化器是如何选择执行计划的。EXPLAIN命令会返回一个表格，其中列出了查询优化器使用的索引、选择的行数、扫描的行数等信息，可以帮助我们了解查询的执行情况和瓶颈。
  2. 慢查询日志：如果发现某个SQL执行速度较慢，可以打开MySQL慢查询日志来查看哪个查询较慢。可以根据慢查询日志中的时间戳和SQL语句来定位问题，找到慢查询的原因并进行优化。
  3. PROFILING：可以使用MySQL的性能分析器（PROFILING）来分析SQL查询的性能。启用性能分析后，我们可以获取一些比如执行时间、查询次数、扫描行数、磁盘操作次数等各种性能指标，从而找到潜在的性能问题。
  4. SHOW ENGINE INNODB STATUS：针对InnoDB存储引擎，在查询执行过程中使用SHOW ENGINE INNODB STATUS语句，可以查看InnoDB存储引擎的状态和运行情况，包括InnoDB缓冲池、事务状态、锁等信息，可以帮助我们找到和优化SQL语句相关的问题。

WAL技术是什么？
WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。事务在提交写入磁盘前，会先写到redo log里面去。如果直接写入磁盘涉及磁盘的随机I/O访问，涉及磁盘随机I/O访问是非常消耗时间的一个过程，相比之下先写入redo log，后面再找合适的时机批量刷盘能提升性能。

MySQL是如何保证主备一致的？
MySQL通过binlog（二进制日志）实现主备一致。binlog记录了所有修改了数据库或可能修改数据库的语句，而不会记录select、show这种不会修改数据库的语句。在备份的过程中，主库A会有一个专门的线程将主库A的binlog发送给 备库B进行备份。其中binlog有三种记录格式：

  1. statement:记录对数据库进行修改的语句本身，有可能会记录一些额外的相关信息。优点是binlog日志量少，IO压力小，性能较高。缺点是由于记录的信息相对较少，在不同库执行时由于上下文的环境不同可能导致主备不一致。
  2. row:记录对数据库做出修改的语句所影响到的数据行以及对这些行的修改。比如当修改涉及多行数据，会把涉及的每行数据都记录到binlog。优点是能够完全的还原或者复制日志被记录时的操作。缺点是日志量占用空间较大，IO压力大，性能消耗较大。
  3. mixed:混合使用上述两种模式，一般的语句使用statment方式进行保存，如果遇到一些特殊的函数，则使用row模式进行记录。MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式， 否则就用statement格式。但是在生产环境中，一般会使用row模式。

简述MySQL的共享锁排它锁
共享锁也称为读锁，相互不阻塞，多个客户在同一时刻可以同时读取同一个资源而不相互干扰。排他锁也称为写锁，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一资源。

如何解决数据库死锁
预先检测到死锁的循环依赖，并立即返回一个错误。
当查询的时间达到锁等待超时的设定后放弃锁请求。

索引是什么？
索引是存储引擎中用于快速找到记录的一种数据结构。在关系型数据库中，索引具体是一种对数据库中一列或多列的值进行排序的存储结构。

两阶段提交是什么？
为了保证binlog和redo log两份日志的逻辑一致，最终保证恢复到主备数据库的数据是一致的，采用两阶段提交的机制。

  1. 执行器调用存储引擎接口，存储引擎将修改更新到内存中后，将修改操作记录redo log中，此时redo log处于prepare状态。
  2. 存储引擎告知执行器执行完毕，执行器生成这个操作对应的binlog，并把binlog写入磁盘。
  3. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成。

简述MySQL中的日志log

  1. redo log: 存储引擎级别的log（InnoDB有，MyISAM没有），该log关注于事务的恢复.在重启mysql服务的时候，根据redo log进行重做，从而使事务有持久性。
  2. undo log：是存储引擎级别的log（InnoDB有，MyISAM没有）保证数据的原子性，该log保存了事务发生之前的数据的一个版本，可以用于回滚，是MVCC的重要实现方法之一。
  3. bin log：数据库级别的log，关注恢复数据库的数据。

简述聚集索引和稀疏索引
聚集索引按每张表的主键构建一棵B+树，数据库中的每个搜索键值都有一个索引记录，每个数据页通过双向链表连接。表数据访问更快，但表更新代价高。
稀疏索引不会为每个搜索关键字创建索引记录。搜索过程需要，我们首先按索引记录进行操作，并按顺序搜索，直到找到所需的数据为止.

为什么引入索引？
为了提高数据查询的效率。索引对数据库查询良好的性能非常关键，当表中数据量越来越大，索引对性能的影响越重要。

简述Hash索引
哈希索引对于每一行数据计算一个哈希码，并将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。只有 Memory 引擎显式支持哈希索引。
Hash索引不支持范围查询，无法用于排序，也不支持部分索引列匹配查找。

简述联合索引和最左匹配原则
联合索引是指对表上的多个列的关键词进行索引。
对于联合索引的查询，如果精确匹配联合索引的左边连续一列或者多列，则mysql会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配。Mysql会对第一个索引字段数据进行排序，在第一个字段基础上，再对第二个字段排序。

简述InnoDB存储引擎
InnoDB 是 MySQL 的默认事务型引擎，支持事务，表是基于聚簇索引建立的。支持表级锁和行级锁，支持外键，适合数据增删改查都频繁的情况。
InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ，并通过间隙锁策略防止幻读，间隙锁使 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定防止幻行的插入。

非主键索引的查询一定会回表吗？
不一定，当查询语句的要求字段全部命中索引，不用回表查询。如select 主键 from 非主键=XX，此时非主键索引叶子节点即可拿到主键信息，不用回表。

数据库中多个事务同时进行可能会出现什么问题？
丢失修改
脏读：当前事务可以查看到别的事务未提交的数据。
不可重读：在同一事务中，使用相同的查询语句，同一数据资源莫名改变了。
幻读：在同一事务中，使用相同的查询语句，莫名多出了一些之前不存在的数据，或莫名少了一些原先存在的数据。

简述覆盖索引
覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值，不需要回表查询，即索引本身存了对应的值。

简述MySQL优化流程
通过慢日志定位执行较慢的SQL语句
利用explain对这些关键字段进行分析
根据分析结果进行优化

简述MyISAM存储引擎
MySQL5.1及之前，MyISAM 是默认存储引擎。MyISAM不支持事务，Myisam支持表级锁，不支持行级锁，表不支持外键，该存储引擎存有表的行数，count运算会更快。适合查询频繁，不适合对于增删改要求高的情况

基于主键索引的查询和非主键索引的查询有什么区别？
对于select * from 主键=XX，基于主键的普通查询仅查找主键这棵树，对于select * from 非主键=XX，基于非主键的查询有可能存在回表过程（回到主键索引树搜索的过程称为回表），因为非主键索引叶子节点仅存主键值，无整行全部信息。

crash-safe能力是什么？
InnoDB通过redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

只靠binlog可以支持数据库崩溃恢复吗？
不可以。 历史原因：

  1. InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力，那引入InnoDB原有的redo log来保证崩溃恢复能力。 实现原因：
    binlog没有记录数据页修改的详细信息，不具备恢复数据页的能力。binlog记录着数据行的增删改，但是不记录事务对数据页的改动，这样细致的改动只记录在redo log中。当一个事务做增删改时，其实涉及到的数据页改动非常细致和复杂，包括行的字段改动以及行头部以及数据页头部的改动，甚至b+tree会因为插入一行而发生若干次页面分裂，那么事务也会把所有这些改动记录下来到redo log中。因为数据库系统进程crash时刻，磁盘上面页面镜像可以非常混乱，其中有些页面含有一些正在运行着的事务的改动，而一些已提交的事务的改动并没有刷上磁盘。事务恢复过程可以理解为是要把没有提交的事务的页面改动都去掉，并把已经提交的事务的页面改动都加上去这样一个过程。这些信息，都是binlog中没有记录的，只记录在了存储引擎的redo log中。
  2. 操作写入binlog可细分为write和fsync两个过程，write指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘,fsync才是将数据持久化到磁盘的操作。通过参数设置sync_binlog为0的时候，表示每次提交事务都只write，不fsync。此时数据库崩溃可能导致部分提交的事务以及binlog日志由于没有持久化而丢失。

简述MySQL中的按粒度的锁分类
表级锁: 对当前操作的整张表加锁,实现简单，加锁快，但并发能力低。
行锁: 锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
Gap 锁：也称为间隙锁: 锁定一个范围但不包括记录本身。其目的是为了防止同一事物的两次当前读出现幻读的情况。
Next-key Lock： 行锁+gap锁。

简述B-Tree与B+树
演示图参考：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B-Tree 是一种自平衡的多叉树。每个节点都存储关键字值。其左子节点的关键字值小于该节点关键字值，且右子节点的关键字值大于或等于该节点关键字值。
加入一个元素，把中间的元素顶上去了。

B+树也是是一种自平衡的多叉树。其基本定义与B树相同，不同点在于数据只出现在叶子节点，所有叶子节点增加了一个链指针，方便进行范围查询。
B+树中间节点不存放数据，所以同样大小的磁盘页上可以容纳更多节点元素，访问叶子节点上关联的数据也具有更好的缓存命中率。并且数据顺序排列并且相连，所以便于区间查找和搜索。非叶子结点的数据类似于索引数据，可以用来辅助查找到真正的数据，非叶子结点类似于页目录记录起始指针和起始值，见图。




假设每条记录是1K，三层b+树可存163841638416=42928704条记录。
但B树每一个节点都包含key和value，查询效率比B+树高。

B+树的分支因子（branching factor）是指每个非叶子节点最多可以拥有的子节点数量，是一个B+树的重要参数，通常用B表示。B+树的性能和磁盘I/O访问次数直接关联，因此B+树的分支因子的选择要根据实际应用场景和硬件环境来决定。

通常情况下，B+树的分支因子取决于磁盘块的大小和存储的键值大小。假设磁盘块大小为P，每个键值大小为K，那么B+树的分支因子B就可以通过以下公式计算得出：

B = floor((P - H) / (K + Pn))

其中，H为磁盘块头部的开销，Pn为指针的大小。这个公式的含义是，每个节点需要存储的键值对的数量是固定的，因此可以根据磁盘块的大小和指针的大小来计算出分支因子。具体来说，分支因子B越大，每个节点可以容纳的键值对就越多，树的高度就越低，磁盘I/O访问次数就越少，B+树的性能就越好。但是，分支因子也不能太大，否则每个节点就会变得太大，不利于节点的存储和查询。

redo log与binlog的区别？
redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。binlog是MySQL的Server层实现的，会记录所有引擎对数据库的修改。
redo log是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑。
redo log是循环写的，空间固定会用完；binlog是可以追加写入的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

undo log如何保证原子性
在执行数据操作之前，首先将原始数据备份，这就是undo log。之后执行数据修正。 如果执行出现了错误，系统可利用undo log中的备份将数据恢复到事务开始之前的状态，保证事务原子性。

脏读是什么，如何解决
一个事务读取了另一个事务修改但未提交的数据
将事务隔离级别设置为：读已提交，串行化，可重复读进行解决。

SQL的事务隔离级别有哪些？
读未提交：
一个事务还没提交，它做的变更就能被别的事务看到。
读提交： 一个事务提交后，它做的变更才能被别的事务看到。
可重复读： 一个事务执行过程中看到的数据总是和事务启动时看到的数据是一致的。在这个级别下事务未提交，做出的变更其它事务也看不到。
串行化： 对于同一行记录进行读写会分别加读写锁，当发生读写锁冲突，后面执行的事务需等前面执行的事务完成才能继续执行。

不可重复读是什么，如何解决
一个事务连续读两次数据，但结果不一样。(两次读之间，数据被其他事务修改)。
将事务隔离级别设置为：串行化，可重复读进行解决。

幻读是什么，如何解决
一个事务连续读两次数据，读取数据量不一样。(两次读之前，数据被其他事务删除或新增)。
将事务隔离级别设置为：串行化，或在innodb引擎中有gap锁的情况下设置可重复读进行解决。

InnoDB可重复读是否存在幻读问题
不存在，InnoDB通过引入间隙锁+行锁（next key lock）的方式，解决了幻读问题。

MySQL的MyISAM和Innodb的区别（从事务，锁，B+树索引，读写性能方面分析）
两个存储引擎的主要区别如下：

  1. 事务支持
    MyISAM不支持事务，而InnoDB支持事务。如果你需要支持事务和并发控制，则应该选择InnoDB。
  2. 锁
    MyISAM的表锁定粒度更高，而InnoDB的行锁定粒度更细。MyISAM在低并发的情况下性能更高，而InnoDB在高并发的情况下表现更好，并具有更好的并发控制能力。
    例如，当许多用户同时试图修改相同的数据行时，使用MyISAM会出现性能瓶颈。
  3. B+树索引
    MyISAM使用B+树索引，而InnoDB使用B+树索引和聚簇索引。MyISAM对读操作优化，而InnoDB对于写操作具有更好的优化。
  4. 读写性能
    MyISAM对于读取操作具有更好的性能，而InnoDB对于写操作具有更好的性能。如果你的应用程序主要是读取数据，那么MyISAM是一个不错的选择。而如果有大量并发的写入或更新操作，则应该使用InnoDB。

Innodb的B+树索引和MyISAM的B+树索引的结构区别

  1. InnoDB的B+树索引是基于聚簇索引实现的。聚簇索引意味着表中的数据按照主键的值在B+树中排列。因此，InnoDB的B+树索引将主键作为排序依据，并根据主键值查找数据行。
  2. 相比之下，MyISAM的B+树索引则是基于非聚簇索引实现的。在MyISAM中，每一个索引都会维护一个B+树结构，该结构的叶子节点存储指向数据行的指针，并按照索引值排列。
    MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引同样是B+树，实现方式却完全不同。InnoDB表数据文件本身就是一个索引结构，树的叶节点data域保存了完整的数据记录，这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列，则mysql自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。InnoDB的所有辅助索引都引用主键作为data域。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以如果主键定义的比较大，其他索引也将很大。InnoDB 不会压缩索引。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

为什么索引能够提升性能（可以从B+树结构方面分析）

  1. 减少IO操作：索引使得查询操作可以更快地定位到所需数据的存储位置，减少了数据库执行的IO操作次数。因此，当数据库表中的数据量很大时，使用索引可以显著提高查询效率。
  2. 减少排序操作：如果没有索引，则某些查询操作可能需要进行排序来按特定顺序检索数据。但使用索引可以避免排序操作，减少查询时间。
  3. 减少锁的竞争： 如果没有使用索引，在查询时数据库执行全表扫描操作，会使得许多数据行被锁住，而使用索引可以减少锁的竞争，从而加快查询速度。

B树和B+树的区别，为什么MySQL要使用B+树（磁盘和树高方面答）
鉴于B+树非叶子节点比B树更小，可以容纳更多的索引节点，因此MySQL在设计中选择了B+树作为存储引擎的索引结构。 在磁盘和树高方面，B+树将所有的数据存放在叶子节点上，每次数据查找都会搜寻叶子节点，因此B+树的高度更低，平均查询效率更高。B+树还利用指针将数据连接到叶子节点上，从而形成一个有序的叶子节点链表，便于数据的区间查询。

对于COUNT(1)和COUNT()执行优化器的优化是完全一样的，并没有COUNT(1)会比COUNT()快这个说法。
从执行计划来看，count(1)和count()的效果是一样的。
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count()用时多！
当数据量在1W以内时，count(1)会比count()的用时少些，不过也差不了多少。
如果count(1)是聚集索引时，那肯定是count(1)快，但是差的很小。
因为count()，会自动优化指定到那一个字段。所以没必要去count(1)，使用count()，sql会帮你完成优化的
因此：在有聚集索引时count(1)和count()基本没有差别！