mysql使用总结

一、MySQL基础数据类型

1.数值数据类型

MySQL的数值数据类型支持SQL标准的数值类型，其中包括精确数值数据类型（INTEGER, SMALLINT, DECIMAL）和近似数值数据类型（FLOAT, REAL, and DOUBLE）

1.1精确整数数值类型

MySQL支持SQL标准的 INTEGER (or INT) 和SMALLINT，同时也支持非SQL标准的 TINYINT, MEDIUMINT和BIGINT。各数据类型需要存储空间（字节）以及取值范围见如下表格：

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-263`	`0`	`263-1`	`264-1`

1.2精确定点数据类型

DECIMAL和NUMERIC用于存储精确数值类型的值，通常用于精确计算。MySQL使用二进制格式存储DECIMAL的值。DECIMAL类型定义如DECIMAL(precision, scale)，其中precision表示包含整数和小数部分总的有效位数，scale表示小数点后位数，也就是说对于DECIMAL(5,2)表示的有效范围是：-999.99到999.99。DECIMAL(M)等价于DECIMAL(M,0)，DECIMAL等价于DECIMAL(10,0)，也就是说默认的整数位数是10。

1.3近似浮点数据类型

MySQL提供两种近似浮点数值类型，分别是FLOAT和DOUBLE，MySQL使用4个字节存储FLOAT，8个字节存放DOUBLE。实际使用中不应该使用近似浮点类型，而使用精确定点类型DECIMAL。《阿里巴巴 Java 开发手册》也禁止使用FLOAT和DOUBLE。

1.4位值数据类型

MySQL提供BIT数据类型用于存储位值，BIT(M)表示存储M位的值。M范围是1到64。指定位的值可以使用b'value'，其中value只能由0和1组成。如果指定的值的位数小于字段位数，则前面自动补0。BIT(M)占用存储空间是(M+7)/8个字节，这个计算方式与ENUM、SET类似，简单的说如果存储1到8位则占用1个字节存储空间，如果存储9到16位则占用2个字节存储空间，以此类推。

可以使用等值的十进制整数或者b'value'的形式给二进制字段赋值，BIT类型会将整数转化为二进制数来存储。如果存储时数据位数小于字段指定位数则前面会补0。

通常使用BIT(1)来指定是否删除字段，实际上使用BIT(1)和使用TINYINT占用的存储空间都是1个字节，使用BIT(1)的好处是可以限定是否删除字段必须是0或1，所以还是建议使用BIT(1)来指定是否删除字段。

BIT(M)通常用于数据具有多种类型，但是只使用一条记录来存储的情况。比如我们可以按位表示数据从属的类型（从右往左），第1位表示类型1，第2位表示类型2，第3位表示类型3，如果数据从属于类型1和类型3则BIT类型的值是b'101'或5，检索同时从属于类型1和类型3的记录可以使用5来检索数据。当然我们也可以使用字符串来存储这类数据，但是字符串占用存储空间还是要远远大于BIT类型。

2.日期时间类型

MySQL提供用于表示日期时间的字段类型是：DATE, TIME, DATETIME, TIMESTAMP和YEAR。其中DATE使用3个字节存储；DATETIME使用8字节存储；TIMESTAMP使用4字节存储；YEAR使用1字节存储。

2.1日期类型 - DATE, DATETIME, and TIMESTAMP

DATE类型只有日期部分，没有时间部分，MySQL使用YYYY-MM-DD格式来检索和显示Date类型的值。DATE类型取值范围是'1000-01-01' 到'9999-12-31'。

DATETIME类型包含日期和时间部分，MySQL使用YYYY-MM-DD HH:MM:SS格式来检索和显示DATETIME类型的值，DATETIME类型取值范围是：'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。

TIMESTAMP类型也包含日期和时间部分，MySQL使用YYYY-MM-DD HH:MM:SS格式来检索和显示TIMESTAMP类型的值，TIMESTAMP类型取值范围是：'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC。

对于日期数据最好使用DATE或者DATETIME存储，从存储空间来说对于YYYY-MM-DD格式字符串占用10个字节存储空间，对于YYYY-MM-DD HH:MM:SS占用19个存储空间，而DATE和DATETIME分别占用3和8个字节，所以使用DATE和DATETIME来存储日期存储效率时较高的。从数据校验来说如果日期格式不对或者超出日期范围对于DATE和DATETIME来说会报错无法入库。

2.2DATETIME和TIMESTAMP字段自动初始化和自动更新

对于DATETIME和TIMESTAMP列，可以设置初始值或者自动更新值为当前时间。通过使用DEFAULT CURRENT_TIMESTAMP来指定不为DATETIME和TIMESTAMP列赋值时，该列使用当前时间赋值；通过使用ON UPDATE CURRENT_TIMESTAMP来指定当其他列的值有更新（必须是改变为非原值的更新才有效）时，该列自动更新为更新时间。当在程序中显式的为（声明为DEFAULT CURRENT_TIMESTAMP的列）赋值为null时，TIMESTAMP列会自动更新到当前时间，而DATETIME列不会，值为null。

3.字符串数据类型

MySQL提供字符串类型有：CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM 和 SET

3.1 CHAR和VARCHAR类型

CHAR和VARCHAR类型分别使用CHAR(M)和VARCHAR(M)来声明，M指定可以存储的字符的个数，这个字符包含任意字符，M并不是字节数，而是字符数，这点与SQL Server数据库不同。

CHAR和VARCHAR长度范围M取值范围不同： CHAR是定长字符串类型，长度范围0到255；VARCHAR是变长字符串类型，长度范围是0到65535，实际长度可能达不到65535，和使用字符集以及具体的数据有关。

CHAR(M)占用存储空间是M*w个节点，其中w是所使用字符集中最大长度字符。utf8使用1到3个字节存储字符串，utf8mb4使用1到4个字节存储字符串。所以如果使用utf8字符集则CHAR(M)占用3M个字节的存储空间，如果使用utf8mb4字符集则CHAR(M)占用4M个字节的存储空间。

VARCHAR(M)占用存储空间是1+Mn或者2+Mn其中Mn是字符串本身所占存储空间大小，以utf8和utf8mb4为例，这两个字符集都是变长字符集，不同字符使用不同存储空间。除了字符串本身所占存储空间还需要加上1到2个字节的存储空间，1还是2取决于字符串本身所占存储空间是小于255个字节还是大于255个字节。使用LENGTH(str:varchar)用于获取存储字符串的字节数（需要注意不是实际存储的字节数，实际存储的字节数需要加上1或2）

CHAR和VARCHAR存储字符串方式不同：CHAR存储字符串时如果字符串不足指定长度，那么会在右侧添加剩余长度的空格，当检索或者获取CHAR类型的值时，尾部空格会移除。 VARCHAR使用1字节或者2字节长度前缀，然后跟上存储的数据，前缀表示字符串中字节数量。如果字符串值存储需要少于255字节空间，则前缀是1个字节，如果超过255字节则前缀是2个字节。

在utf8或者utf8mb4编码下，一个汉字占用3个字节，数字或字母占用1个字节。gbk编码下，一个汉字占用2个字节，数字或字母占用1个字节。CHAR_LENGTH(str:varchar)用于获取字符串中字符个数，LENGTH(str:varchar)用于获取存储字符串的字节数（需要注意不是实际存储的字节数，实际存储的字节数需要加上1或2），不同编码集下CHAR_LENGTH(str:varchar)返回相同结果，而LENGTH(str:varchar)则与字符集有关。这里utf8占用存储空间是1到3字节，utf8mb4占用1到4个字节，那为什么对于汉字两种字符集都是占用3个字节呢，这是因为utf8已经包含所有汉字，utf8mb4是utf8的超集，向下兼容所以必然相同。utf8mb4只是在utf8的基础上增加了emoji表情的支持。

3.2 枚举类型

枚举类型是指在声明列时明确指定允许值的列表，字段取值只能是其中的一个。枚举类型占用存储空间是1或2个字节（和字符串相比太省空间了）依据该枚举值在枚举集合中的数字编号（数字编号最大是65535）。

枚举类型有两个显著优点：

1、占用存储空间少，枚举值会以数字的形式存储，只占用1到2个字节，占用空间大小取决于枚举值在枚举集合中的数字编号（数字编号最大是65535），通常情况枚举值比较少，基本上就是占用1个字节存储空间。

2、可读性高，查询和输出时，存储在数据库中的枚举编号会自动转换为字面量，比单独的存储数字编号可读性要高很多。

枚举类型的潜在问题：

1、如果枚举值就是数字，则很容易和数据库中实际存储的数字编号混淆，应该避免。

2、枚举值排序时实际上是根据枚举字面量声明的前后顺序（枚举字面量对应的数字编号）如果实在要根据字母顺序则需要额外的函数转换为字符串排序。（Using ENUM columns in ORDER BY clauses requires extra care, as explained in Enumeration Sorting.）

3.3 集合类型

集合类型是指在声明列时明确指定允许值的列表，字段取值可以是其中的一个或多个。赋值时多个值使用英文逗号分隔，查询结果如果是多个值也是以逗号分隔的方式显示。Set列最多允许64个不同的取值。集合Set占用存储空间是1, 2, 3, 4, or 8 字节，具体依据集合Set中的元素个数，如果元素个数是N则该列占用存储空间是(N+7)/8，这与BIT(M)，ENUM判断占用存储空间的方法是一样的，也就是说存储1到8个元素占用1个字节，存储9到16个元素以此可类推。

SET集合存储数据时如果有多个值，SET会按照列允许值的声明顺序来排列数据，查询时也可以逗号分隔的方式来查询数据，需要注意查询时元素排列顺序也要与声明顺序相同。

SET字段中存储数据时允许插入空串，如果插入多个值时，存储的顺序会按照列允许值声明的顺序重新排列然后保存，SET列插入错误值SQL报错无法保存。

INSERT INTO tb_set_1(v_1) VALUES ('medium,small')

SET字段检索，可以借助FIND_IN_SET()函数来检索包含指定值（单值）的列，或者直接使用=来查询精确匹配指定单值或多值的列，多值使用逗号分隔。

SELECT * FROM tb_set_1 WHERE FIND_IN_SET('small,medium',v_1)>0;

二、mysql ACID

原子性

根据定义，原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。即要么转账成功，要么转账失败，是不存在中间的状态！
mysql是利用Innodb的undo log保证原子性，undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

隔离性

根据定义，隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
mysql利用的是锁和MVCC机制来保证隔离性。

持久性

根据定义，持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
mysql利用Innodb的redo log保证持久性。Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。如何解决这个问题呢？需要事务提交前直接把数据写入磁盘就行，但是只修改一个page的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理；同时一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。于是，mysql决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上,并且为顺序io)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

一致性

根据定义，一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。
数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。

三、MySQL 隔离级别

不可重复读是针对更新操作来讲的，而幻读是针对新增、删除操作来说的

Read Uncommitted

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。

Serializable

这是最高的隔离级别，它通过强制事务排序（但并不是多个事务串行执行）使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

MySQL解决幻读

当前读

当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录避免出现安全问题，只有在Read Repeatable、Serializable隔离级别才有，就是锁定范围空间的数据。
select ... lock in share mode, select ... for update以及更新操作都是当前读

快照读

单纯的select操作，不包括上述 select ... lock in share mode, select ... for update。

版本链

innodb存储引擎，聚簇索引记录中都包含两个必要的隐藏列
trx_id：每次对某条记录进行改动时，对会把对应的事务id赋值给trx_id隐藏列；
roll_pointer：每次对某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该记录修改前的信息；
每次在记录被改动时都会生成undo日志，undo记载就是改动前的记录信息

事务

当创建一个新的事务时, mysql会查询当前活跃的事务列表并生成一个新的readView,这个readview中creator_trx_id就是新事务的id，假设当前的事务id是200，mysql会去对比版本链和readView, 假设版本链数据为[1,50,100,150], 活跃列表为[100,150], 说明100和150都是未提交的活跃事务, 再向前一个节点50不在活跃事务列表说明事务50已经提交, 所以mysql最后读取到的时一个最新的不活跃事务对应的数据版本50;

readview

RR隔离级别下，在每个事务开始的时候，会将当前系统中所有活跃事务拷贝到一个列表中（read view）
RC隔离级别下，在每条语句开始的时候，会将当前系统中活跃事务拷贝到一个列表中（read view）
ReadView中主要包含当前系统中还有哪些活跃的事务（未提交的事务），把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids。
readview中四个比较重要的概念：
m_ids：表示在生成readview时，当前系统中活跃的事务id列表；
min_trx_id：表示在生成readview时，当前系统中活跃的读写事务中最小的事务id，也就是m_ids中最小的值；
max_trx_id：表示生成readview时，系统中应该分配给下一个事务的id值；
creator_trx_id：表示生成该readview的事务的事务id；
对于RR隔离级别，版本链比对规则：
如果被访问版本链中某个节点的 trx_id 属性值小于 m_ids 列表中最小的事务id，表明生成该版本的事务在生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id，表明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。
如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
ReadView只有在只读事务下是会复用的,当事务中执行了其他的增删改操作则会重新生成readview

next-key 锁解决幻读

将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的

四、MySQL 锁

锁类型

共享锁(S锁):假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A。
排他锁(X锁):假设事务T1对数据A加上共享锁，那么事务T2不能读数据A，不能修改数据A。

意向共享锁(IS锁):一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。
意向排他锁(IX锁):一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。

意向锁存在的目的

假设事务T1，用X锁来锁住了表上的几条记录，那么此时表上存在IX锁，即意向排他锁。那么此时事务T2要进行LOCK TABLE … WRITE的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。

mysql innodb下加锁原理

只在RR和Serializable隔离级别下才有间隙锁，mysql没有锁表的操作，只在RR和Serializable隔离级别下使用Next-Key Locks，也可以理解为是用了行锁+间隙锁来实现了锁表。如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行。

pId(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
7	ccc	200
执行语句(name列无索引)

select * from table where name = `aaa` for update

那么此时在pId=1,2,7这三条记录上存在行锁(把行锁住了)。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁(把间隙锁住了)。因此，给人一种整个表锁住的错觉。

RC/RU+条件列非索引条件下快照读不加锁，当前读加行锁。
RC/RU+条件列聚簇索引条件下快照读不加锁，当前读加行锁。
在条件列没有索引的情况下，尽管通过聚簇索引来扫描全表，进行全表加锁。但是，MySQL Server层会进行过滤并把不符合条件的锁当即释放掉，因此条件列加不加索引最终结果是一样的。但是RC/RU+条件列非索引比本例多了一个释放不符合条件的锁的过程。
RC/RU+条件列非聚簇索引条件下快照读不加锁，当前读加行锁（非聚簇索引和聚簇索引对应的记录上都加锁）

RR条件列非索引下快照读不加锁，当前读所有记录加锁。
RR条件列是聚簇索引快照读不加锁，当前读如果where后的条件为精确查询(=的情况)，那么只存在record lock。如果where后的条件为范围查询(>或<的情况)，那么存在的是record lock+gap lock。
RR条件列是唯一索引，跟聚簇索引一样，两个索引都加锁
RR条件列是非唯一索引，快照读不加锁，当前读record lock+ gap lock，(最终效果就是不能出现幻读)例如

pId(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
3	bbb	300
7	ccc	200
`select * from table where num = 200 lock in share mode`
是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。

Serializable条件下都是当前读

六、MySQL 索引

1.MySQL 索引原理

在Innodb中，Mysql中的数据是按照主键的顺序来存放的。按照每张表的主键来构造一颗B+树，叶子节点存放的就是整张表的行数据。

B+树的数据只出现在叶子节点上，B+树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得B+树非常适合做范围查询。

2.索引分类

2.1 按存储方式

MySQL B+树索引从存储方式来说可以分为两类，分别是聚簇索引（Clustered Indexes）*和*辅助索引/二级索引（Secondary Indexes）。聚簇索引和辅助索引都使用B+树的数据结构，区别在于叶子节点存放的数据不同。

聚簇索引是根据主键创建的一棵B+树，聚集索引的分支节点存放主键，叶子节点存放数据行。

辅助索引是根据索引键创建的一棵B+树，辅助索引的分支节点存储索引键，叶子节点存放索引键指向的主键。也就是如果使用辅助索引检索数据，找到索引键指向的主键后还需要通过主键值去聚簇索引中找到该主键对应的数据行，这个过程也叫做二次查找（回表查询）。除主键索引外，其他索引都是辅助索引

2.2按索引功能

主键索引：声明为PRIMARY KEY的字段称为主键索引，每个表只有一个主键索引。

唯一索引：声明为UNIQUE KEY的一个或多个字段称为唯一索引。

单值索引：声明为KEY/INDEX的单个字段称为单值索引。

联合索引/复合索引：声明为KEY/INDEX的多个字段称为联合索引/复合索引。

附：联合索引也是一颗B+树，与单值索引不同的是分支节点存放的索引的键值不是一个字段而是多个字段的值，联合索引遵循最左匹配原则

3.索引操作

3.1创建索引

创建表时创建索引

DROP TABLE IF EXISTS db_test.tb_index;
CREATE TABLE IF NOT EXISTS db_test.tb_index(
    id int AUTO_INCREMENT,
    id_card varchar(20) COMMENT '身份证号',
    name varchar(20) COMMENT '姓名',
    birth date COMMENT '出生年月',
    phone bigint COMMENT '手机号',
    sex enum('f','m') COMMENT '性别',
    location geometry NOT NULL COMMENT '地理位置',
    PRIMARY KEY (id),
    UNIQUE INDEX uk_id_card(id_card),
    UNIQUE INDEX uk_name_phone(name,phone),
    INDEX idx_name(name),
    INDEX idx_birth_sex(birth,sex),
    SPATIAL INDEX sdx_location(location)
) ENGINE InnoDB CHARSET utf8mb4 COMMENT '测试索引的表';

修改表时创建索引

-- 创建主键索引
ALTER TABLE db_test.tb_index_1 ADD PRIMARY KEY (id);

-- 创建唯一索引
ALTER TABLE db_test.tb_index_1 ADD UNIQUE INDEX uk_id_card(id_card);
ALTER TABLE db_test.tb_index_1 ADD UNIQUE INDEX uk_name_phone(name,phone);

-- 创建普通索引
ALTER TABLE db_test.tb_index_1 ADD INDEX idx_name(name);
ALTER TABLE db_test.tb_index_1 ADD INDEX idx_birth_sex(birth,sex);

-- 创建空间索引
ALTER TABLE db_test.tb_index_1 ADD SPATIAL INDEX sdx_location(location);

使用单独语句创建索引

CREATE INDEX不支持创建主键索引

-- 创建唯一索引
CREATE UNIQUE INDEX uk_id_card ON db_test.tb_index_2(id_card);
CREATE UNIQUE INDEX uk_name_phone ON db_test.tb_index_2(name,phone);

-- 创建普通索引
CREATE INDEX idx_name ON db_test.tb_index_2(name);
CREATE INDEX idx_birth_sex ON db_test.tb_index_2(birth,sex);

-- 创建空间索引
CREATE SPATIAL INDEX sdx_location ON db_test.tb_index_2(location);

3.2 查看索引

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name

3.3删除索引

修改表时删除索引

-- 删除主键索引
ALTER TABLE db_test.tb_index DROP PRIMARY KEY;

-- 删除其他索引
ALTER TABLE db_test.tb_index DROP INDEX uk_id_card;
ALTER TABLE db_test.tb_index DROP INDEX idx_name;
ALTER TABLE db_test.tb_index DROP INDEX sdx_location;

使用单独语句删除索引

-- 删除主键索引
DROP INDEX `PRIMARY` ON db_test.tb_index;

-- 删除其他索引
DROP INDEX uk_id_card ON db_test.tb_index;
DROP INDEX idx_name ON db_test.tb_index;
DROP INDEX sdx_location ON db_test.tb_index;

4.索引设计原则及分析

1、对经常需要做为WHERE、ON、ORDER BY、GROUP BY的字段创建索引，根据情况决定是创建单值索引还是联合索引。

2、如果索引字段的值特别长，尽量使用前缀索引，这样单个页存放的索引数目更多，索引树的高度也越低，查询效率更高。

3、尽量不要在经常更新的字段上创建索引，通常无意义而且构建B+树要进行左旋右旋来保持平衡，较耗费时间。

4、尽量在区分度高的字段上创建索引，如果某列重复值较多，则很可能查询优化器不使用该列的索引，而使用全表扫描（聚集索引查询效率高，辅助索引可能需要回表，顺序读比随机读高效）

5、限制索引数目，索引越多占用磁盘空间越大，修改表时对索引的重构和更新会很麻烦，很耗时。索引过多会影响查询优化器的选择速度。

6、删除不再使用或者很少使用的索引，索引的存在会将表的更新操作变的繁琐，并且占用磁盘空间，应定期检查不需要的索引，并将其删除。

7、索引维护要避开业务繁忙期。

5.索引优化

5.1 索引列不使用表达式或者函数

我们知道索引列如果是表达式的一部分或者函数的一部分是不走索引的，查询优化器并没有做等效处理，而且通常我们在接收到业务数据后不做任何处理，直接将参数传递给数据库执行，大多数情况是可以这么做，但是如果传递的参数对应的索引列需要使用表达式或者函数，那么我们最好在接口层面做这个处理，综合考虑参数和索引列然后做等值化处理，也就是做相应的转换。

5.2 ORDER BY的优化

在数据管理或者分析系统中排序是一个很常见的操作，文件排序通常出现在索引树无法完成排序的时候。当MySQL不能使用索引来完成排序的时候就要自己去排序，两种方式：如果数据量较小就在内存中排序，如果数据量较大就使用磁盘，MySQL将这个过程统一称为文件排序（filesort）。

文件排序慢的一个原因就是MySQL在进行文件排序时通常需要较大的空间，MySQL在排序时对每个排序记录都会分配一个足够长的定长空间来存放，这个定长空间必须能够容纳其中最长的字符串，所以实际排序使用的临时空间要远远大于原数据所占磁盘空间。

MySQL对于关联查询的排序处理通常分为两种情况，如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联其他表前就进行文件排序，extra可以看到Using filesort；如果ORDER BY来自多个表，则MySQL会将关联结果存放在临时表，待所有关联都结束后才进行文件排序，extra可以看到Using filesort; Using temporary。

从上面的分析我们也看到文件排序是一个耗时的工作，那么优化的一种方式就是综合考虑业务场景和索引性能的情况下，将排序字段和索引字段定义为联合索引。当然我们也应该意识到虽然多个排序字段都可以定义在一个联合索引中，但是如果排序方向不同，那么依然会出现文件排序。

其次如果排序字段分布在两个表，那么我们应该考虑如果这种场景使用很频繁那么是不是可以将其中一个表的字段冗余到另外一个表中。这种优化方式不一定适用大多数场景，实际工作中不能滥用。

如果在过滤条件和排序条件上创建了联合索引，但是还是出现文件排序或者不走索引的情况，要清晰认识是为什么，然后做出修改，比如一个规则就是排序顺序要按照联合索引列的顺序，这个我们在写排序SQL的时候就应该注意，而且要注意多字段排序方向不一致也会出现文件排序的情况。

5.3GROUP BY分组的优化

我们首先看下通常GROUP BY是如何执行分组的？MySQL通常会扫描全表，然后创建一个临时表用于存放数据，临时表中每个组的数据是连续的，然后依次扫描每个分组中的数据应用聚合函数，最后得出结果。根据MySQL执行GROUP BY我们就看到了优化点，既然GROUP BY要求分组数据连续那么我们是不是可以利用索引数据的有序性来满足这个条件，并且可以避免使用临时表。

使用索引完成GROUP BY操作的先决条件是GROUP BY分组列的顺序与索引列定义顺序一致。

从上面我们也看到如果使用了索引数据的有序性那么分组后再按指定顺序排序，是不耗费性能的，这里的启示就是如果需求需要按多字段分组，考虑组装SQL时分组或者排序字段的顺序。或者在某些情况下如果我们只有联合索引(A,B,C)，而且不能修改索引，那么按照B,C或者C排序时是否可以在排序时按照顺序加上其他字段以满足联合索引顺序，当然这个还是要评估性能，以实际情况为主。

5.4LIMIT分页的优化

分页操作也是在业务场景中常见的操作，不单是在数据管理页面，类似的ToC业务也会用到分页，比如下拉刷新，榜单也可以看作是分页排序的一个特例（排序后只要第一页）。分页通常是通过LIMIT M,N来实现，同时LIMIT通常会和ORDER BY一起使用，MySQL在会排序结果中找到第一个数量N就停止排序并返回结果，而不会对所有结果排序。当分页偏移量很大时，比如LIMIT 100000,10，MySQL会查询100010条记录后丢弃之前的10w条然后只返回10条数据，代价很高。

优化这种查询的一个办法是限制分页的数量控制偏移量，这在业务设计层面也是有一定道理的，用户会按照关注点指定顺序排序，很大偏移量的数据用户基本不会访问，反而是爬取数据的人更关心。

优化这种查询的另外一个办法是使用索引扫描，需要注意不是索引覆盖扫描，不要求所有返回字段一起建立联合索引，这也是不现实的，我们可以通过索引快速定位数据，然后再通过一次关联来返回所需的列，这种方式也叫做延迟关联，这种方式对大偏移量很有效果，性能提升明显。延迟关联的思路就是让MySQL尽可能扫描少的页面，获取需要访问的记录后再根据关联列返回原表查询需要的所有列。

-- 优化前
EXPLAIN
SELECT * FROM third_interest_beijing LIMIT 1000000,100;

-- 优化后
EXPLAIN
SELECT * FROM third_interest_beijing interest
INNER JOIN (SELECT fnid FROM third_interest_beijing LIMIT 1000000,100) t_id ON interest.fnid=t_id.fnid;

优化的另外一种思路就是将分页查询换成已知位置的查询，如果能够根据前一次分页结果结合页面数量能够界定分页范围，那么查询效率也是很高的。在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的，根据这个可以巧妙的降低全表或者索引扫描带来的扫描太多记录的情况。

SELECT   * 
FROM     third_interest_beijing
WHERE create_time > '2017-03-16 14:00:00'
ORDERBY create_time limit 10;

5.5 避免类型转换

SQL语句中查询变量和字段定义类型不匹配或者关联字段类型不匹配

5.6 关联更新删除

虽然 MySQL5.6 引入了物化特性（在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表，后续对子查询结果集的访问将直接通过临时表获得。与此同时，优化器还具有延迟物化子查询的能力，先通过其它条件判断子查询是否真的需要执行。物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。与之相对的执行方式是对外表的每一行都对子查询进行调用，其执行计划中的查询类型为“DEPENDENT SUBQUERY”），但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。

UPDATE third_interest_beijing o 
       JOIN  (SELECT o.id, o.status 
                     FROM   third_interest_beijing o 
                     WHERE  o.group = 123
                     ORDERBY o.parent, o.id 
                     LIMIT 1) t
         ON o.id = t.id 
SET status = 'applying'

5.7 MySQL 不能利用索引进行混合排序

应当尽量从业务上避免混合排序(asc和desc混在一起),实在不行要从其它方面想办法解决

5.8 EXISTS 子句

MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式，编写sql时去掉 exists 更改为 join，能够避免嵌套子查询。

5.9 小表驱动大表

以T1表 INNER JOIN T2表 ON condition为例，T1为驱动表，T2为被驱动表

关联字段上无索引时sql执行顺序为：首先扫描T1表，从T1表每次获取一条记录，然后扫描T2表将T1的这条记录和T2中的每条记录进行匹配，T1匹配T2的时候不知道何时停止扫描，所以需要扫描T2的每条记录。扫描成本为O(T1*T2)

T2表的连接条件上有索引时：首先扫描T1表，从T1表每次获取一条记录，然后T1表的每条记录并不需要扫描T2，只需要扫描T2表的索引即可得到联接条件的判断结果，所以时间复杂度为0(T1)。这个也是在关联条件ON上关联字段加索引的原因，但是从上面分析也看出只需要在被关联表（非驱动表/内部表）上关联字段加上索引即可。

对于内联接来说表的关联顺序可以互换，MySQL会选择扫描代价小的作为驱动表（外部表），具体原因可参考上面的描述，我们知道如果被关联表上关联字段存在索引的话，T1驱动T2的时间复杂度是0(T1)，T2驱动T1的时间复杂度是0(T2)，如果T1远远大于T2，那么MySQL查询优化器会选择T1作为驱动表，反之选择T2，当然这句话是有前提条件的，那就是被驱动表上关联字段上又索引。

5.10 条件下推

外部查询条件不能够下推到复杂的视图或子查询的情况有：

聚合子查询；
含有 LIMIT 的子查询；
UNION 或 UNION ALL 子查询；
输出字段中的子查询；

-- 优化前
SELECT * 
FROM   (SELECT target, 
               Count(*) 
        FROM   operation 
        GROUPBY target) t 
WHERE  target = 'rm-xxxx'
-- 优化后
SELECT target, 
       Count(*) 
FROM   operation 
WHERE  target = 'rm-xxxx'
GROUPBY target

中间结果集下推

初始sql

SELECT    a.*, 
          c.allocated 
FROM      ( 
              SELECT   resourceid 
              FROM     my_distribute d 
                   WHERE    isdelete = 0
                   AND      cusmanagercode = '1234567'
                   ORDERBY salecode limit20) a 
LEFTJOIN
          ( 
              SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated 
              FROM     my_resources 
                   GROUPBY resourcesid) c 
ON        a.resourceid = c.resourcesid

左连接中的主表优先作用查询条件，但是子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降；其实对于子查询 c，左连接最后结果集只关心能和主表 resourceid 能匹配的数据。因此我们可以重写语句如下

SELECT    a.*, 
          c.allocated 
FROM      ( 
                   SELECT   resourceid 
                   FROM     my_distribute d 
                   WHERE    isdelete = 0
                   AND      cusmanagercode = '1234567'
                   ORDERBY salecode limit20) a 
LEFTJOIN
          ( 
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated 
                   FROM     my_resources r, 
                            ( 
                                     SELECT   resourceid 
                                     FROM     my_distribute d 
                                     WHERE    isdelete = 0
                                     AND      cusmanagercode = '1234567'
                                     ORDERBY salecode limit20) a 
                   WHERE    r.resourcesid = a.resourcesid 
                   GROUPBY resourcesid) c 
ON        a.resourceid = c.resourcesid

子查询 a 在我们的SQL语句中出现了多次，如果mysql版本为8，可以使用WITH 语句重写

WITH a AS
( 
         SELECT   resourceid 
         FROM     my_distribute d 
         WHERE    isdelete = 0
         AND      cusmanagercode = '1234567'
         ORDERBY salecode limit20)
SELECT    a.*, 
          c.allocated 
FROM      a 
LEFTJOIN
          ( 
                   SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated 
                   FROM     my_resources r, 
                            a 
                   WHERE    r.resourcesid = a.resourcesid 
                   GROUPBY resourcesid) c 
ON        a.resourceid = c.resourcesid

5.11 提前缩小范围

初始sql

SELECT * 
FROM   my_order o 
       LEFTJOIN my_userinfo u 
              ON o.uid = u.uid
       LEFTJOIN my_productinfo p 
              ON o.pid = p.pid 
WHERE  ( o.display = 0 ) 
       AND ( o.ostaus = 1 ) 
ORDERBY o.selltime DESC
LIMIT0, 15

由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下

SELECT * 
FROM (
SELECT * 
FROM   my_order o 
WHERE  ( o.display = 0 ) 
       AND ( o.ostaus = 1 ) 
ORDERBY o.selltime DESC
LIMIT0, 15
) o 
     LEFTJOIN my_userinfo u 
              ON o.uid = u.uid 
     LEFTJOIN my_productinfo p 
              ON o.pid = p.pid 
ORDERBY  o.selltime DESC
limit0, 15

5.12 exists和in

in 先执行子查询，得到的结果集放到外层表的where中去匹配，exists遍历外层表然后一条一条去子查询中去匹配，具体使用哪个跟表大小及关联字段是否索引有关。

七、执行计划

MySQL执行计划提供了SQL语句如何执行的相关信息，借此可以分析SQL执行的性能，从而进一步优化调整，如果查询使用了索引还可以借助执行计划查询索引的使用情况。

MySQL执行计划适用于SELECT, DELETE, INSERT, REPLACE, and UPDATE等语句。MySQL执行计划显示优化器（optimizer）如何处理指定SQL语句，包括表如何连接以及连接顺序等等。

对于SELECT查询语句，执行计划可以通过使用SHOW WARNINGS语句来显示额外的执行信息。

1.执行计划输出信息说明

Column	JSON Name	Meaning
`id`	`select_id`	The `SELECT` identifier 多表连接时，id值越大越先执行，通常出现在嵌套查询中，相同数值，从上往下顺序执行。
`select_type`	None	The `SELECT` type
`table`	`table_name`	The table for the output row 查询的表名，如果查询使用别名则这里显示别名。
`partitions`	`partitions`	The matching partitions
`type`	`access_type`	The join type
`possible_keys`	`possible_keys`	The possible indexes to choose
`key`	`key`	The index actually chosen
`key_len`	`key_length`	The length of the chosen key 查询用到的索引的长度，常用于判断联合索引使用情况。
`ref`	`ref`	The columns compared to the index ref用于显示索引与哪些列或者常量进行比较，以便从表中选择行。
`rows`	`rows`	Estimate of rows to be examined 执行计划中估算的扫描行数,不是精确值。
`filtered`	`filtered`	Percentage of rows filtered by table condition 通过查询或者连接条件过滤的行数百分比
`Extra`	None	Additional information

id：查询标识符，多表连接时，id值越大越先执行，通常出现在嵌套查询，相同数值，从上往下顺序执行。

select_type：select_type表示查询的类型，常见值有SIMPLE（简单查询，不包含UNION和子查询）、UNION（连接查询）、SUBQUERY（子查询）。

key_len：查询用到的索引的长度，常用于判断联合索引使用情况。可以根据长度算出数据库实际使用的字段组合。由于key的存储格式，可为空的列的key要比非空列的key要大。

type：用于表示连接类型。const(system)：只匹配一条记录的查询，通常是主键查询或者唯一键的查询。eq_ref：多表连接中，连接条件是主键或者唯一键。ref：辅助索引的等值扫描（单表的普通索引查询或者连接条件是普通索引）。range : 索引范围扫描.。>、>=、<、<=、LIKE、IN、OR、BETWEEN AND。特别注意：与查询结果集数量有关，查询结果集数量稍大则变为全表扫描。Index:全索引扫描，需要扫描整个索引树。ALL：全表扫描。以上类型效率从高到底分别是：system>const>eq_ref>ref>range>index>all。

rows：执行计划中估算的扫描行数,不是精确值。

filtered：通过过滤或者连接条件估算的行数百分比，rows*filtered就是单表最终查询结果数或者多表连接时驱动表关联时循环的驱动表的行数。

2.type指标分析

type：用于表示连接类型，常见有system、const、eq_ref、ref、range、index、all（执行效率由高到底）。

const(system)：主键或唯一键的单表查询以及多表连接查询，多表连接中驱动表过滤条件是主键或者唯一键则驱动表的type是const，非驱动表连接条件都是主键或者唯一键则非驱动表的type是const。

ref：辅助索引的单表查询以及多表连接查询，驱动表过滤条件是辅助索引，非驱动表连接条件是辅助索引，type都是ref。

eq_ref：多表连接中，非驱动表连接条件是主键或者唯一键。

range：单表索引（主键、唯一、辅助索引）范围查询（>、<、>=、<=、IN、NOT IN 、BETWEEN..AND.. 等），多表连接时驱动表过滤条件是范围查询并且查询结果相对较少（官网的30%）则驱动表的type是range。

index：索引扫描而非索引查找，大多数索引树扫描比全表扫描效率高，这是因为索引树上只有索引键和主键，而全表扫描还有其他信息，IO相对多。

ALL：不走索引，全表扫描。

3.extra指标分析

extra提供了查询计划额外的信息，常见extra内容如下：

Using Index：索引覆盖，不需要回表查询（单表、多表都适用）

Using Index condition：使用了索引，但是索引不能覆盖需要回表查询（单表、多表都适用）

Using filesort; Using temporary：当使用索引无法完成排序时就会使用文件排序

Using where; Using join buffer (Block Nested Loop)：将非驱动表的数据放入join buffer，然后将驱动表符合条件的记录和join buffer中的记录进行比较，从而减少驱动表比较次数（减少比较次数百分比取决于一次放入join buffer中记录数）

4.rows和filtered指标分析