一.创建表：
1.1 建表语句的语法格式：
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);
1.2 关于MySQL当中字段的数据类型？以下只说常见的：
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型 （对应Java中的java.sql.Date）
BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object）
......
1.3 char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
1.4 BLOB和CLOB类型的使用？
电影表: t_movie
id(int) name(varchar) playtime(date/char) haibao(BLOB) history(CLOB)
1 zs1 101 北京大兴区经济技术开发区亦庄二中高三1班
2 zs2 101 北京大兴区经济技术开发区亦庄二中高三1班
3 zs3 102 北京大兴区经济技术开发区亦庄二中高三2班
4 zs4 102 北京大兴区经济技术开发区亦庄二中高三2班
5 zs5 102 北京大兴区经济技术开发区亦庄二中高三2班
缺点：冗余。【不推荐】
第二种方案：两张表（班级表和学生表）【推荐】
t_class 班级表
cno(pk) cname
1 zs1 101
2 zs2 101
3 zs3 102
4 zs4 102
5 zs5 102
- 将以上表的建表语句写出来：
t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做 子表。t_class表叫做 父表。
顺序要求：
删除数据的时候，先删除子表，再删除父表
添加数据的时候，先添加父表，在添加子表
创建表的时候，先创建父表，再创建子表
删除表的时候，先删除子表，在删除父表
drop table if exists t_student;
drop table if exists t_class;
create table t_class( //先创建父表
cno int,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)//引用
);
insert into t_class values(101,'xx'); //先添加父表
insert into t_student values(1,'zs1',101);
insert into t_student values(7,'lisi',103); //报错：不能添加父表中的cno所没有的classno
6.3 注意：外键可以为NULL；
外键字段引用其他表的某个字段的时候，被引用的字段不一定是主键，但至少具有unique约束;
七.事务（Transaction）:
7.1、一个事务是一个完整的业务逻辑单元，不可再分。
银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败，而要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的"事务机制"；
7.2、和事务相关的语句只有：DML语句（insert delete update）
为什么？因为它们这三个语句都是和数据库表当中的"数据"相关的；事务的存在是为了保证数据的完整性，安全性。
7.3、假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？
那就不需要了；但实际情况不是这样的，通常一个"事儿（事务/业务）"需要多条DML语句共同联合完成。
7.4、事务的特性？
事务包括四大特性：ACID
A: 原子性：事务是最小的工作单元，不可再分；
C: 一致性：事务必须保证多条DML语句同时成功或者同时失败；
I：隔离性：事务A与事务B之间具有隔离；
D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束；
7.5、关于事务之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括4个：
第一级别： 读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据；
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
第二级别： 读已提交（read committed）
对方事务提交之后的数据我方可以读取到；这种隔离级别解决了"脏读现象"；
读已提交存在的问题是：不可重复读。
第三级别： 可重复读（repeatable read）
这种隔离级别解决了"不可重复读问题"；
这种隔离级别存在的问题是：读取到的数据是幻象。
第四级别： 序列化读/串行化读（serializable）
解决了所有问题；但效率低。需要事务排队。
oracle数据库默认的隔离级别 --- 读已提交（2）；
mysq l数据库默认的隔离级别 --- 可重复读（3）；
7.6、演示事务
* mysql事务默认情况下是自动提交的。
（什么是自动提交？只要执行任意一条DML语句则提交一次）怎么关闭自动提交 --- start transaction;
-
准备表：
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
); -
演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。
mysql> insert into t_user(username) values('zs');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
mysql> rollback; // 回滚（撤回未提交的操作）
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
* 演示：使用start transaction 关闭自动提交机制：
mysql> start transaction;
mysql> insert into t_user(username) values('lisi');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | lisi |
+----+----------+
mysql> insert into t_user(username) values('wangwu');
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
mysql> rollback;
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
1 张三
2 李四
3 王五
t_teacher 讲师表
tno(pk) tname
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
口诀：一对多？两张表，多的表加外键
班级t_class
cno(pk) cname
101 张1 1
102 张2 1
103 张3 2
104 张4 2
105 张5 2
注意：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。
7.3、一对一怎么设计？
第一种设计方案 --- 主键共享
t_user_login 用户登录表
id(pk) username password
1 张三 1111111111
2 李四 1111415621
第二种设计方案 --- 外键唯一
t_user_login 用户登录表
id(pk) username password
1 张三 1111111111 2
2 李四 1111415621 1
随笔：
1.增删改查有一个术语：CRUD操作
Create（增） Retrieve（检索） Update（修改） Delete（删除）
2.复合主键（不需要掌握）：
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
password varchar(255),
primary key(id,username)
);
insert .......
3、DBA命令
3.1、将数据库当中的数据导出
在windows的dos命令窗口中执行：（导出整个库）
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333
在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123
3.2、导入数据
create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.sql
4.存储引擎：（了解）
4.1、完整的建表语句
CREATE TABLE t_x (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。
建表的时候可以指定存储引擎，也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式。
默认采用的字符集是UTF8
4.2、什么是存储引擎呢？
存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，
就是"表的存储方式"）
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。
4.3、查看当前mysql支持的存储引擎？
show engines \G
mysql 5.5.36版本支持的存储引擎有9个：
***** 1. row ****
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*** 2. row ***
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*** 3. row ***
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*** 4. row ***
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*** 5. row ***
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*** 6. row ***
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*** 7. row ***
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*** 8. row ***
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*** 9. row ******
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
4.4、常见的存储引擎？
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA引擎。
5.索引实现原理：
Original: https://www.cnblogs.com/Burning-youth/p/15680722.html
Author: 猿头猿脑的王狗蛋
Title: 【MySQL】笔记（4）--- 创建表；插入，修改，删除数据；主键，外键约束；事务；索引；视图；三范式；
相关阅读1
Title: MVCC - Read View的可见性判断理解
读了 @SnailMann大佬【MySQL笔记】正确的理解MySQL的MVCC及实现原理 收益颇丰，非常感谢！
但对其中如何判断事务是否可见性还是不太理解，于是作了本文，在原博客基础上，举例画图论证、理解了 Read View的可见性判断。
引用 @SnailMann大佬【MySQL笔记】正确的理解MySQL的MVCC及实现原理 的字段说明。
隐式字段
每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID 等字段
DB_TRX_ID
6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 IDDB_ROLL_PTR
7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）DB_ROW_ID
6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
Read View 的三个全局属性
trx_list（名称我随意取的）：一个数值列表，用于维护Read View生成时刻系统 正活跃的事务 ID 列表
up_limit_id：是trx_list列表中事务 ID 最小的 ID
low_limit_id：Read View生成时刻系统尚未分配的下一个事务 ID ，也就是 目前已出现过的事务 ID 的最大值 + 1
为什么是low_limit? 因为它也是系统此刻可分配的事务 ID 的最小值
可见性判断逻辑
DB_TRX_ID < up_limit_id, 当前行事务id比活跃的最小事务id还小时，说明了两件事，当前行事务对该记录的修改已经提交，因为当前事务id比活跃的最小事务id还小，不在活跃的事务之中，也就意味着该事务已经提交或回滚，这时因为已经成功修改，那么应该就是提交成功了。
也就是在生成Read View之前，事务已经提交，- 接下来判断
DB_TRX_ID >= low_limit_id, 修改该行的事务id大于了Read View里系统待分配的下一个事务id，说明修改该行的事务是生成该Read View之后出现的事务，因为Read View系统待分配的下一个事务id被用了，才会出现比该事务id大的事务。这时，也应该是不可见的，一个事务怎么可以看到后面新来事务做的修改了。 - 判断
DB_TRX_ID是否在活跃事务之中，trx_list.contains (DB_TRX_ID)，如果在活跃事务之中，说明该修改是其他事务未提交的修改，应该是不可见的，如果可见就是脏读了，如果不在活跃事务之中，说明在生成Read View之前，该事务的修改就已提交，与第一个判断逻辑类似，事务2是可以查到这条记录的。
针对上面三种情况，下面举例说明：
原记录： amount = 100
事务1 事务2 事务3 事务4 开启事务 开启事务 开启事务
update amount = 200 update amount = 300
提交事务 ①
select amount;
开始快照读，生成
Read View
提交事务 开启事务 ②
select amount; update amount = 400;
提交事务 ③
select amount;
请问三次 select amount; 快照读到的值分别是多少，为什么？
画一张图，把undo表里存的记录版本链及当前记录画出来。
①
1>如图，当前行 DB_TRX_ID(1) == up_limit_id(1)，说明本次修改该记录的事务正在进行中，也就是 事务1还未结束， 事务2就应该对 事务1这次修改不可见，可见就是脏读了。
2>当前记录不可见，再根据回滚指针追踪到上个版本记录，如图undo日志内 金额为200的行，此时再通过Read View进行可见性判断。
第一种情况：当前行 DB_TRX_ID(3) > up_limit_id(1)，不确定；
第二种情况： DB_TRX_ID(3) < low_limit_id(4)，也不确定；
第三种情况： DB_TRX_ID(3)不在 trx_list中，不是活跃的事务，说明 事务3在事务2生成 Read View之前就已经提交，那么是可见的。
所以读取的金额为200。
②
事务1提交事务，不过undo表与当前行数据无变化，对事务1的 Read View的数据也不会变化，因为RR模式下， Read View 只会在第一次快照读时生成，后面几次快照读不会生成新的 Read View，也不会改动之前Read View的值。
当前行数据与 Read View 都无变化，那么可见性判断也同①一致，读取到的金额为200。
③
第一种情况：当前行 DB_TRX_ID(4) > up_limit_id(1)，不确定；
第二种情况： DB_TRX_ID(4) > low_limit_id(1)，说明当前行是被生成 Read View之后出现的事务修改的，这种未来的数据肯定是不可见的。
再接着追溯，就与①中追溯的过程相差不大了，最终读取的金额也是为200。
总结
这里举例论证了可见性判断的合理性，总结来说，可见性的三个判断约束了一件事， 只有在本事务生成 Read View 之前就已经提交的事务的修改才可以被看见，其他的无论是正在进行的事务的修改还是之后再提交的事务的修改都不可见。
Original: https://www.cnblogs.com/ZJJCodeLife-520/p/16472231.html
Author: 桃子来了
Title: MVCC - Read View的可见性判断理解
相关阅读2
Title: 容器化 | 在 NFS 备份恢复 RadonDB MySQL 集群数据
社区于上个月发布了 RadonDB MySQL Kubernetes v2.2.0，集群数据备份恢复的存储类型除了 S3，新增 NFS 存储。本文将为您演示如何进行 NFS 备份及恢复操作。
- Kubernetes 集群
- RadonDB MySQL 集群
方法一：使用 Helm 安装
helm install demo charts/mysql-operator --set nfsBackup.installServer=true --set nfsBackup.volume.createLocalPV=true
或者手动创建 PVC，再执行
helm install demo charts/mysql-operator --set nfsBackup.installServer=true --set nfsBackup.volume.specifiedPVC=XXXX
用该方法，可以在安装 Operator 时，也将 NFS 服务的 Pod 和 Service 安装到集群中。
方法二：使用 kubectl 安装
kubectl apply -f config/samples/nfs_pv.yaml
kubectl apply -f config/samples/nfs_server.yaml
例如：
kubectl get svc nfs-server --template={{.spec.clusterIP}}
10.98.253.82
获取到 ClusterIP，即可以使用该地址进行 NFS 备份。这里 IP 地址为 10.96.253.82。
配置 NFS 服务的地址
创建备份
kubectl apply -f config/samples/mysql_v1alpha1_backup.yaml
注意：备份自定义资源与 MySQL 集群自定义资源必须在同一个命名空间中。
验证备份
使用如下命令，可以发现名称格式为 <cluster name>_<timestamp></timestamp></cluster> 的备份文件夹。
kubectl exec -it <pod name of nfs server> -- ls /exports
# 显示结果
index.html initbackup sample_2022419101946
</pod>
备份恢复
从已有的 NFS 备份文件中恢复集群。配置 mysql_v1alpha1_cluster.yaml，将 nfsServerAddress 设置为 NFS 服务的地址。
...
restoreFrom: "sample_2022419101946"
nfsServerAddress: 10.96.253.82
注意：
restoreFrom是备份路径的名称，可以从 NFS 服务加载的路径中看到。然后从 NFS 备份副本恢复集群，如下：
kubectl apply -f config/samples/mysql_v1alpha1_cluster.yaml
恢复完成，已经从名为 sample_2022419101946 的 NFS 备份中恢复一个集群。
Original: https://www.cnblogs.com/radondb/p/16550913.html
Author: RadonDB
Title: 容器化 | 在 NFS 备份恢复 RadonDB MySQL 集群数据
相关阅读3
Title: 小心陷入MySQL索引的坑
索引可以说是数据库中的一个大心脏了，如果说一个数据库少了索引，那么数据库本身存在的意义就不大了，和普通的文件没什么两样。所以说一个好的索引对数据库系统尤其重要，今天来说说MySQL索引，从细节和实际业务的角度看看在MySQL中B+树索引好处，以及我们在使用索引时需要注意的知识点。
合理利用索引
在工作中，我们可能判断数据表中的一个字段是不是需要加索引的最直接办法就是：这个字段会不会经常出现在我们的 where条件中。从宏观的角度来说，这样思考没有问题，但是从长远的角度来看，有时可能需要更细致的思考，比如我们是不是不仅仅需要在这个字段上建立一个索引？多个字段的联合索引是不是更好？以一张用户表为例，用户表中的字段可能会有 用户的姓名、 用户的身份证号、 _用户的家庭地址_等等。
「1.普通索引的弊端」
现在有个需求需要根据用户的身份证号找到用户的姓名，这时候很显然想到的第一个办法就是在 id_card上建立一个索引，严格来说是唯一索引，因为身份证号肯定是唯一的，那么当我们执行以下查询的时候：
<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">WHERE</span> id_card=xxx
该过程应如下所示：
[En]
The process should be like this:
- 先在id_card索引树上搜索，找到id_card对应的主键id
- 通过id去主键索引上搜索，找到对应的name
从效果上来看，结果是没问题的，但是从效率上来看，似乎这个查询有点昂贵，因为它检索了两颗B+树，假设一颗树的高度是3，那么两颗树的高度就是6，因为根节点在内存里（此处两个根节点），所以最终要在磁盘上进行IO的次数是4次，以一次磁盘随机IO的时间平均耗时是10ms来说，那么最终就需要40ms。这个数字一般，不算快。 
「2.主键索引的陷阱」
既然问题是回表，造成了在两颗树都检索了，那么核心问题就是看看能不能只在一颗树上检索。这里从业务的角度你可能发现了一个切入点， 身份证号是唯一的，那么我们的主键是不是可以不用默认的自增id了，我们把主键设置成我们的身份证号，这样整个表的只需要一个索引，并且通过身份证号可以查到所有需要的数据包括我们的姓名，简单一想似乎有道理，只要每次插入数据的时候，指定id是身份证号就行了，但是仔细一想似乎有问题。
这里要从B+树的特点来说，B+树的数据都存在叶子节点上，并数据是页式管理的，一页是16K，这是什么意思呢？哪怕我们现在是一行数据，它也要占用16K的数据页，只有当我们的数据页写满了之后才会写到一个新的数据页上，新的数据页和老的数据页在物理上 不一定是连续的，而且有一点很关键，虽然数据页物理上是不连续的，但是数据在 逻辑上是连续的。
也许你会好奇，这和我们说的身份证号当主键ID有什么关系？这时你应该关注 「 连续」这个关键字，身份证号不是连续的，这意味着什么？当我们插入一条不连续的数据的时候，为了保持连续，需要移动数据，比如原来在一页上的数据有1->5，这时候插入了一条3，那么就需要把5移到3后面，也许你会说这也没多少开销，但是如果当新的数据3造成这个页A满了，那么就要看它后面的页B是否有空间，如果有空间，这时候页B的开始数据应该是这个从页A溢出来的那条，对应的也要移动数据。如果此时页B也没有足够的空间，那么就要申请新的页C，然后移一部分数据到这个新页C上，并且会切断页A与页B之间的关系，在两者之间插入一个页C，从代码的层面来说，就是切换链表的指针。
总结来说，不连续的身份证号当主键可能会造成页数据的移动、随机IO、频繁申请新页相关的开销。如果我们用的是自增的主键，那么对于id来说一定是顺序的，不会因为随机IO造成数据移动的问题，在插入方面开销一定是相对较小的。
其实不推荐用身份证号当主键的还有另外一个原因：身份证号作为数字来说太大了，得用bigint来存，正常来说一个学校的学生用int已经足够了，我们知道一页可以存放16K，当一个索引本身占用的空间越大时，会导致一页能存放的数据越少，所以在一定数据量的情况下，使用bigint要比int需要更多的页也就是更多的存储空间。
「3.联合索引的矛与盾」
从以上两个结论，我们可以得出一个结论：
[En]
From the above two conclusions, we can draw a conclusion:
- 尽量不要去回表
- 身份证号不适合当主键索引
所以自然而然地想到了联合索引，创建一个【身份证号+姓名】的联合索引，注意联合索引的顺序，要符合最左原则。这样当我们同样执行以下sql时：
select name from user <span class="hljs-built_in">where</span> id_card=xxx
不需要回表就可以得到我们需要的name字段，然而还是没有解决身份证号本身占用空间过大的问题，这是业务数据本身的问题，如果你要解决它的话，我们可以通过一些转换算法将原本大的数据转换成小的数据，比如crc32：
crc32.ChecksumIEEE([]byte(<span class="hljs-string">"341124199408203232"</span>))
可以将原本需要8个字节存储空间的身份证号用4个字节的crc码替代，因此我们的数据库需要再加个字段 crc_id_card，联合索引也从【身份证号+姓名】变成了【crc32(身份证号)+姓名】，联合索引占的空间变小了。但是这种转换也是有代价的：
- 每次额外的crc，导致需要更多cpu资源
- 额外的字段，虽然让索引的空间变小了，但是本身也要占用空间
- crc会存在冲突的概率，这需要我们查询出来数据后，再根据id_card过滤一下，过滤的成本根据重复数据的数量而定，重复越多，过滤越慢。
关于联合索引存储优化，这里有个小细节，假设现在有两个字段A和B，分别占用8个字节和20个字节，我们在联合索引已经是[A,B]的情况下，还要支持B的单独查询，因此自然而然我们在B上也建立个索引，那么两个索引占用的空间为 8+20+20=48，现在无论我们通过A还是通过B查询都可以用到索引，如果在业务允许的条件下，我们是否可以建立[B,A]和A索引，这样的话，不仅满足单独通过A或者B查询数据用到索引，还可以占用更小的空间： 20+8+8=36。
「4.前缀索引的短小精悍」
有时我们需要索引的字段是字符串类型，并且字符串非常长。我们希望对此字段进行索引，但不希望索引占用太多空间，因此可以考虑设置前缀索引。使用该字段的第一部分字符构建索引，不仅可以享受索引，还可以节省空间。这里需要注意的是，在前缀重复率较高的情况下，前缀索引的速度与一般索引之间应该存在差距。
[En]
Sometimes the field we need to index is of string type, and the string is very long. We want this field to be indexed, but we don't want the index to take up too much space, so we can consider setting up a prefix index. build an index with the first part of the characters of this field, which can not only enjoy the index, but also save space. It should be noted here that in the case of high prefix repetition, there should be a gap between the speed of the prefix index and the general index.
alter table xx add index(name(7));<br>select xx from xx <span class="hljs-built_in">where</span> name=<span class="hljs-string">"JamesBond"</span>
「5.唯一索引的快与慢」
在说唯一索引之前，我们先了解下普通索引的特点，我们知道对于B+树而言，叶子节点的数据是有序的。
假设现在我们要查询 _2_这条数据，那么在通过索引树找到2的时候，存储引擎并没有停止搜索，因为可能存在多个2，这表现为存储引擎会在叶子节点上接着向后查找，在找到第二个2之后，就停止了吗？答案是否，因为存储引擎并不知道后面还有没有更多的2，所以得接着向后查找，直至找到第一个不是2的数据，也就是3，找到3之后，停止检索，这就是普通索引的检索过程。
唯一索引就不一样了，因为唯一性，不可能存在重复的数据，所以在检索到我们的目标数据之后直接返回，不会像普通索引那样还要向后多 查找一次，从这个角度来看，唯一索引是要比普通索引快的，但是当普通索引的数据都在一个页内的话，其实也并不会快多少。在数据的插入方面，唯一索引可能就稍逊色，因为唯一性，每次插入的时候，都需要将判断要插入的数据是否已经存在，而普通索引不需要这个逻辑，并且很重要的一点是唯一索引会用不到change buffer（见下文）。
「6.不要盲目加索引」
在工作中，你可能会遇到这样的情况：这个字段我需不需要加索引？。对于这个问题，我们常用的判断手段就是：查询会不会用到这个字段，如果这个字段经常在查询的条件中，我们可能会考虑加个索引。但是如果只根据这个条件判断，你可能会加了一个错误的索引。我们来看个例子：假设有张用户表，大概有100w的数据，用户表中有个性别字段表示男女，男女差不多各占一半，现在我们要统计所有男生的信息，然后我们给性别字段加了索引，并且我们这样写下了sql：
select * from user <span class="hljs-built_in">where</span> sex=<span class="hljs-string">"男"</span>
如果不出意外的话，InnoDB是不会选择性别这个索引的。如果走性别索引，那么一定是需要回表的，在数据量很大的情况下，回表会造成什么样的后果？我贴一张和上面一样的图想必大家都知道了：
主要就是大量的IO，一条数据需要4次，那么50w的数据呢？结果可想而知。因此针对这种情况，MySQL的优化器大概率走全表扫描，直接扫描主键索引，因为这样性能可能会更高。
「7.索引失效那些事」
某些情况下，因为我们自己使用的不当，导致mysql用不到索引，这一般很容易发生在类型转换方面，也许你会说，mysql不是已经支持隐式转换了吗？比如现在有个整型的user_id索引字段，我们因为查询的时候没注意，写成了：
select xx from user <span class="hljs-built_in">where</span> user_id=<span class="hljs-string">"1234"</span>
注意这里是字符的1234，当发生这种情况下，MySQL确实足够聪明，会把字符的1234转成数字的1234，然后愉快的使用了user_id索引。 但是如果我们有个字符型的user_id索引字段，还是因为我们查询的时候没注意，写成了：
select xx from user <span class="hljs-built_in">where</span> user_id=1234
这时候就有问题了，会用不到索引，也许你会问，这时MySQL为什么不会转换了，把数字的1234转成字符型的1234不就行了？ 这里需要解释下转换的规则了，当出现字符串和数字比较的时候，要记住：MySQL会把字符串转换成数字。也许你又会问：为什么把字符型user_id字段转换成数字就用不到索引了? 这又要说到B+树索引的结构了，我们知道B+树的索引是按照索引的值来分叉和排序的，当我们把索引字段发生类型转换时会发生值的变化，比如原来是A值，如果执行整型转换可能会对应一个B值（int(A)=B）,这时这颗索引树就不能用了，因为索引树是按照A来构造的，不是B，所以会用不到索引。
索引优化
「1.change buffer」
我们知道，当更新一条数据时，我们应该首先确定该数据页是否在内存中。如果是，则直接更新相应的内存页。如果没有，我们只能到磁盘上将相应的数据页读入内存，然后进行更新。有什么问题吗？
[En]
We know that when updating a piece of data, we should first determine whether the page of the data is in memory. If so, update the corresponding memory page directly. If not, we can only go to the disk to read the corresponding data page into memory, and then update it. What's the problem?
- 去磁盘的读这个动作稍显的有点慢
- 如果同时更新很多数据，那么即有可能发生很多离散的IO
为了解决这种情况下的速度问题， _change buffer_出现了，首先不要被buffer这个单词误导，change buffer除了会在公共的buffer pool里之外，也是会持久化到磁盘的。当有了change buffer之后，我们更新的过程中，如果发现对应的数据页不在内存里的话，也不去磁盘读取相应的数据页了，而是把要更新的数据放入到change buffer中，那change buffer的数据何时被同步到磁盘上去？如果此时发生读动作怎么办？首先后台有个线程会定期把change buffer的数据同步到磁盘上去的，如果线程还没来得及同步，但是又发生了读操作，那么也会触发把change buffer的数据merge到磁盘的事件。
需要注意的是并不是所有的索引都能用到changer buffer，像主键索引和唯一索引就用不到，因为唯一性，所以它们在更新的时候要判断数据存不存在，如果数据页不在内存中，就必须去磁盘上把对应的数据页读到内存里，而普通索引就没关系了，不需要校验唯一性。change buffer越大，理论收益就越大，这是因为首先离散的读IO变少了，其次当一个数据页上发生多次变更，只需merge一次到磁盘上。当然并不是所有的场景都适合changer buffer，如果你的业务是更新之后，需要立马去读，changer buffer会适得其反，因为需要不停地触发merge动作，导致随机IO的次数不会变少，反而增加了维护changer buffer的开销。
「2.索引下推」
前面我们说了联合索引，联合索引要满足最左原则，即在联合索引是[A,B]的情况下，我们可以通过以下的sql用到索引：
select * from table <span class="hljs-built_in">where</span> A=<span class="hljs-string">"xx"</span><br>select * from table <span class="hljs-built_in">where</span> A=<span class="hljs-string">"xx"</span> AND B=<span class="hljs-string">"xx"</span>
事实上，联邦索引还可以使用最左侧前缀的原则，即：
[En]
In fact, federated indexes can also use the principle of leftmost prefix, that is:
select * from table <span class="hljs-built_in">where</span> A like <span class="hljs-string">"赵%"</span> AND B=<span class="hljs-string">"上海市"</span>
但是这里需要注意的是，因为使用了A的一部分，在MySQL5.6之前，上面的sql在检索出所有A是"赵"开头的数据之后，就立马回表（使用的select *），然后再对比B是不是"上海市"这个判断，这里是不是有点懵？为什么B这个判断不直接在联合索引上判断，这样的话回表的次数不就少了吗？造成这个问题的原因还是因为使用了最左前缀的问题，导致索引虽然能使用部分A，但是完全用不到B，看起来是有点"傻"，于是在MySQL5.6之后，就出现了索引下推这个优化（Index Condition Pushdown）,有了这个功能以后，虽然使用的是最左前缀，但是也可以在联合索引上搜索出符合A%的同时也过滤非B的数据，大大减少了回表的次数。
「3.刷新邻接页」
在说刷新邻接页之前，我们先说下脏页，我们知道在更新一条数据的时候，得先判断这条数据所在的页是否在内存中，如果不在的话，需要把这个数据页先读到内存中，然后再更新内存中的数据，这时会发现内存中的页有最新的数据，但是磁盘上的页却依然是老数据，那么此时这条数据所在的内存中的页就是脏页，需要刷到磁盘上来保持一致。所以问题来了，何时刷？每次刷多少脏页才合适？如果每次变更就刷，那么性能会很差，如果很久才刷，脏页就会堆积很多，造成内存池中可用的页变少，进而影响正常的功能。所以刷的速度不能太快但要及时，MySQL有个 _清理线程_会定期执行，保证了不会太快，当 _脏页太多_或者 _redo log已经快满_了，也会立刻触发刷盘，保证了及时。
在脏页刷盘的过程中，InnoDB这里有个优化：如果要刷的脏页的邻居页也脏了，那么就顺带一起刷，这样的好处就是可以减少随机IO，在机械磁盘的情况下，优化应该挺大，但是这里可能会有坑，如果当前脏页的邻居脏页在被一起刷入后，邻居页立马因为数据的变更又变脏了，那此时是不是有种多此一举的感觉，并且反而浪费了时间和开销。更糟糕的是如果邻居页的邻居也是脏页...，那么这个连锁反应可能会出现短暂的性能问题。
「4.MRR」
在实际业务中，我们可能会被告知尽量使用覆盖索引，不要回表，因为回表需要更多IO，耗时更长，但是有时候我们又不得不回表，回表不仅仅会造成过多的IO，更严重的是过多的离散IO。
select * from user <span class="hljs-built_in">where</span> grade between 60 and 70
现在要查询成绩在60-70之间的用户信息，于是我们的sql写成上面的那样，当然我们的 grade字段是有索引的，按照常理来说，会先在grade索引上找到grade=60这条数据，然后再根据grade=60这条数据对应的id去主键索引上找，最后再次回到grade索引上，不停的重复同样的动作...， 假设现在grade=60对应的id=1，数据是在 page_no_1上，grade=61对应的id=10，数据是在 page_no_2上，grade=62对应的id=2，数据是在 page_no_1上，所以真实的情况就是先在page_no_1上找数据，然后切到page_no_2，最后又切回page_no_1上，但其实 id=1和 id=2完全可以合并，读一次page_no_1即可，不仅节省了IO，同时避免了随机IO，这就是MRR。当使用MRR之后，辅助索引不会立即去回表，而是将得到的主键id，放在一个buffer中，然后再对其排序，排序后再去顺序读主键索引，大大减少了离散的IO。
往期精彩：
Original: https://www.cnblogs.com/jiazhuangdongbiancheng/p/15495243.html
Author: 假装懂编程
Title: 小心陷入MySQL索引的坑