MySQL

安装

略

启动

略

连接

客户端工具连接 - 略

命令连接 mysql -h主机名 -P端口号 -u用户名 -p密码

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使对这个用户的权限做了修改，也不会影响已经存在连接的权限，只有新建的连接才会使用新的权限设置。

MySQL执行流程图：

日志

MySQL 的设计者引入了 WAL 技术（Write-Ahead Logging），即先写日志，再写磁盘。

日志写入过程

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明： username：你将创建的用户名

host：指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%

password：该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

指定IP段可以访问 CREATE USER 'shu'@'172.18.0.%' IDENTIFIED BY '123456';

例子:

CREATE USER 'shu'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'xiao'@'223.15.236.120' IDENTIFIED BY '123456';
CREATE USER 'yuan'@'%' IDENTIFIED BY '123456';
CREATE USER 'shuang'@'%' IDENTIFIED BY '';
CREATE USER 'test'@'%';

# 刷新权限
flush privileges;    //刷新权限

授权

8.0+

# 赋予权限，按照5.7的写法会报错，以下为 8.0 的写法
grant all on *.* to `shuxiaoyuan_uat`@`106.15.238.9` with grant option;

# 8.0 要先创建用户，然后再给用户添加权限
CREATE USER 'php80_docker'@'172.18.0.6' IDENTIFIED BY 'Shuxiaoyuan@666';
grant all privileges on *.* to 'php80_docker'@'172.18.0.6';


# 删除权限
REVOKE all privileges ON databasename.tablename FROM 'username'@'host';

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

8.0 以下

GRANT privileges ON databasename.tablename TO 'username'@'host';

说明： privileges：用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL

databasename：数据库名

tablename：表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*.

username：需要分配的用户

host：指定用户登录的主机

例子:

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';

# 所有数据库的所有权限，在任意主机上
GRANT ALL ON *.* TO 'pig'@'%';

# social数据库的所有权限，在任意主机上
GRANT all ON social.* TO 'pig'@'%';

# 注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

# 刷新权限
flush privileges;    //刷新权限

设置与更改用户密码

8.0+ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

8.0以下 SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用 SET PASSWORD = PASSWORD("newpassword");

撤销用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

说明: privilege databasename tablename - 同授权部分.

例子: REVOKE SELECT ON *.* FROM 'pig'@'%';

注意: 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的): GRANT SELECT ON test.user TO 'pig'@'%';, 则在使用 REVOKE SELECT ON *.* FROM 'pig'@'%'; 命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是 GRANT SELECT ON *.* TO 'pig'@'%'; 则 REVOKE SELECT ON test.user FROM 'pig'@'%'; 命令也不能撤销该用户对test数据库中user表的Select 权限.

具体信息可以用命令 SHOW GRANTS FOR 'pig'@'%'; 查看

删除用户

DROP USER 'username'@'host';

如果发现双击打开某张表的时候很慢，至少要3秒。

原因是：当远程访问mysql时, mysql会解析域名, 所以会导致访问速度很慢, 会有2,3秒延时！

解决办法：修改mysql安装目录下的my.ini，加上下面这个配置可解决此问题。在[mysqld]下加入：skip-name-resolve

保存退出后重启mysql服务。

然后访问速度就和本地一样快啦。

8.0 有些东西写法不一样

# 报错一：修改密码，添加权限等，报错如下
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

解决：grant system_user on *.* to 'root'@'127.0.0.1';

附表：MySQL中的操作权限

还没百度，稍后加上

数据库安装配置等略过，保证版本一致，配置一致 基于行的复制和基于语句的复制

一、创建复制帐号，主从都配置这个帐号

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'Password!123';

二、打开主数据库的二进制日志并制定一个独一无二的服务器ID（修改my.cnf文件）

vim /etc/my.cnf
## 输入一下内容
log_bin    =    mysql-bin
server_id    = 1  #一般是取IP地址末8位，但是要确保它是唯一并且不变的 

重启MySQL，查看是否已经创建 show master status

三、备库上面也要增加类似配置

以下配置不都是必须的，实际上，只有server_id是必须的

vim /etc/my.cnf

log_bin    =    mysql-bin    # 为了简便，直接设置和主表一样，默认是根据机器名来命名
server_id    = 2
relay_log = /var/lib/mysql/mysql-relay-bin    # 指定中继日志的位置和命名
log_slave_updates = 1    # 允许备库将其重放的事件也记录到自身的二进制日志中，数据库自身会增加额外的工作量
read_only = 1  # 配置选项，不清楚，看书本439页

四、启动复制，不需要修改my.cnf文件，直接用语句，运行下面语句并不会立马开始运行复制

CHANGE MASTER TO MASTER_HOST = '192.168.1.6',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'Password!123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

运行 show slave status;来检查复制是否正确执行

运行下面语句之后就会开始启动复制，建议你看完之后再执行这条语句 运行MySQL命令START SLAVE;开始复制

1. 以上的建立的基础是：默认主库和从库都是刚刚安装好的，并且都是默认数据，不过一般情况下不是这样
2. 一般情况下都是一台服务器运行了一段时间，然后再启动复制同步，这个时候从库上面是没有数据的。
3. 主从复制是从配置了二进制文件的时候开始记录的，没开启之前的数据都是不会同步的（重启服务会重新生成一个新的二进制文件）。
4. 这个时候就需要将主库的数据线备份到从库来初始化从库。备份有很多方式，冷备份，热备份，mysqldump等都可以
5. 有错误就停止了，所以要保证数据一致性 简单说下数据备份，保证一致问题

• 最简单的，直接关闭主库，然后将数据备份到备库，可以采用高效复制文件的方法将数据传送到从库
• 采用热备份，如果仅使用了MyISAM表，可以在主库运行时采用mysqlhotcopy或者rsyncLaura复制数据，这里不详细展开说明
• 如果只包含了InnoDB表，可以使用mysqldump命令来转储主库数据并将其加载到备库，然后设置相应的二进制日志坐标 mysqldump --single-transaction --all-databases --master-data=1--host=server1 \ | mysql --host=server2
• 使用快照或者备份
• 使用percona Xtrabackup
• 使用另外的备库

可以运行 SHOW PROCESSLIST;查看线程，如下查看主库的 查看从库的

问题

如果数据没同步，查看错误日志 正确无错误应该是如下图所示

InnoDB 和 MyISAM 区别

• InnoDB 支持事务、行级锁，MyISAM 都不支持

• InnoDB 支持行级锁，MyISAM 只支持表锁

• InnoDB支持外键，MyISAM 不支持

• InnoDB是聚簇索引（叶子节点存数据），MyISAM是非聚簇索引（叶子节点存指针）

• 如果用InnoDB是必须有主键的，主键建议用自增的 id 而不用 uuid，用 uuid 会使得索引变慢。

• MyISAM 提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择

• InnoDB 支持热备份

• MyISAM 崩溃后数据恢复很差

InnoDB

采用 MVCC 来支持高并发，并且实现了四个标准的事务隔离级别，并且通过 间隙锁 策略防止 幻读 的出现，所以不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定

基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键索引，所以主键过大的话，其他的索引都会很大

MyISAM

可以存储的行记录数，而 InnoDB 只是估计值

不支持行级锁，读取加共享锁，写入加排他锁，会发生并发插入（在表有读取查询的时候，也可以往表中插入新的记录）

支持全文索引，压缩表（不会对表进行修改操作，比如日志类）

数据以紧密格式存储，所以在某些场合下的性能很好

MyISAM 索引实现

MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引 MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。如图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

InnoDB 索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

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

如何选择存储引擎

• 事务 如果需要事务，那么 InnoDB（或者XtraDB），如果不需要事务，并且只有 select 和 insert 操作，那么 MyISAM 还不错的选择，一般日志型都用 MySIAM

• 备份 如果你需要在线热备份，那么 InnoDB 就是基本的要求，如果不需要热备份，那么都无所谓

转换表引擎

• 最常规的做法： alter table mytable engine = InnoDB; 这个适用任何引擎，可是有个很大的问题，需要执行的时间较长，MySQL 会按照行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的 I/O 能力，同时会在原表上加读锁，所以对有些表操作时需要格外小心。

• 第二种办法 这个就是导入导出，在导出的SQL文件中修改存储引擎

• 第三种办法 先创建一个新的存储引擎的表，然后利用 insert .... select 语法来导入数据

# 如果数据量不大，可以这样操作，数据量大，最好分批处理
create table innodb_table like myisam_table;
alter table innodb_table engine = InnoDB;
insert into innodb_table select * from myisam_table;

# 分批处理
start transaction;
insert into innodb_table select * from myisam_table where id between x and y;
commit ;

聚簇索引

大表分页

之所以会出现这个问题，是因为MySQL limit 机制

思路就是根据一个索引了

• 如果能确定主键索引，并且连续，那就直接计算出分页范围

• 如果主键不连续，那就多传字段过来，比如上条最后一个主键ID

• 如果没有主键索引，那就根据创建时间来，还需要告诉上次分页这个时间有多少条

概述

• 更小的通常更好，如果只存 0-200，那么 tinyint unsigned 是一个非常不错的选择

注：有符号无符号使用相同的存储空间，并且具有相同的性能。int(10) 和 int(11)，存储和计算来说是一样的，只是显示的字符不同。可以考虑使用 bigint 代替 decimal 和 float

• 越简单越好，比如整型比字符型要好，比如时间采用内置数据类型，而不是自己定义字符串来存，比如存IP，可以用整型而不是字符串

注： DATETIME 和 TIMESAMP 都是时间类型，可 TIMESAMP 比 DATETIME 少一半的存储空间，所以允许的时间范围要小很多。但是会根据时区变化，这个有好也有坏，需要注意

• 尽量避免 NULL

注：InnoDB 使用单独的位（bit）存储 NULL 值，所以对于稀疏数据，有很好的空间效率，这个不适合 MyISAM。稀疏数据指：大部分值为 NULL，只有少数行的列有非 NULL 的值

varchar 和 char

varchar

比定长会节省空间，但是如果建表时使用了 ROW_FORMAT=FIXED 那么每一行都会使用定长存储，会很浪费空间

会需要 1 或 2 个额外字节来记录字符串长度：如果列的最大长度小于等于 255 个字节，则只使用 1 个字节来记录，否则会使用 2 个字节来记录

什么时候使用 varchar 是合适的：

• 字符串列的最大长度比平均长度大很多；
• 列更新很少，碎片化不是问题；
• 使用了 utf8 utf8mb4等这样复杂的字符集

char

什么时候使用 char 是合适的：

• 定长的，比如 MD5 值等

注：末尾空格会截断的问题

BLOB 和 TEXT

字符类型：TINYTEXT ,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT 二进制类型：TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB

BLOB 和 TEXT 家族之间仅有的不同就是： BLOB：存储的是二进制数据，没有排序规则或字符集 TEXT：存储的是字符数据，有字符集和排序规则

使用枚举代替字符串，我建议最好不用枚举

MySQL 在内部会将每个枚举值在列表中的位置保存为整数，在表文件中保存一个映射关系，所以，可以自己枚举，这样还少了映射表，还有需要注意的是，如果枚举的是 1,2,3 这样的话，那么这种映射表的双重性就很容易导致混乱，尽量不要这样做。排序是按照内部存储的整数来排序的，而不是你定义的字符串来排序的

日期和时间类型

DATETIME

采用8个字节存储空间，能存储从 1001 - 9999 年，与时区无关

TIMESAMP

采用4个字节存储空间，能存储从 1970 - 2038 年，与时区有关 内部保存的是 UNIX 时间戳，可以通过内置函数 FROM_UNIXTIME() 将 Unix 时间戳转换成日期，使用 UNIX_TIMESTAMP 将日期格式转换成时间戳

事务的四个特性 ACID 缺一不可

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。

可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

幻读

幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

• 读未提交（READ UNCOMMITTED）
• 读提交 （READ COMMITTED）
• 可重复读 （REPEATABLE READ）
• 串行化 （SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

查看事务隔离级别 ：show variables like 'transaction_isolation';

读未提交

读未提交，其实就是可以读到其他事务未提交的数据，但没有办法保证你读到的数据最终一定是提交后的数据，如果中间发生回滚，那就会出现脏数据问题，读未提交没办法解决脏数据问题。更别提可重复读和幻读了，想都不要想。

读提交

读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用 commit 命令之后的数据。那脏数据问题迎刃而解了。

每个 select 语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的。

读提交解决了脏读的问题，但是无法做到可重复读，也没办法解决幻读。

可重复读

可重复是对比不可重复而言的，上面说不可重复读是指同一事物不同时刻读到的数据值可能不一致。而可重复读是指，事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题。

可重复读做到了，这只是针对已有行的更改操作有效，但是对于新插入的行记录，就没这么幸运了，幻读就这么产生了。我们看一下这个过程：

串行化

串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

MVVC (多版本并发控制)

并发加锁

加锁的过程要分有索引和无索引两种情况

• 有索引 比如这条语句：update user set age=11 where id = 1 id 是这张表的主键，是有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，然后干净利落的加上行锁就可以了。

• 无索引 而这条语句 update user set age=11 where age=10 表中并没有为 age 字段设置索引，所以， MySQL 无法直接定位到这行数据。那怎么办呢，当然也不是加表锁了。MySQL 会为这张表中 所有行加行锁 ，没错，是所有行。但是呢，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引，如果真的出现这种情况，那很难保证并发度

参考链接

• MySQL事务隔离级别和实现原理（看这一篇文章就够了！）

悲观锁

顾名思义，就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。

悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select .... for update 来实现悲观锁。

注意，使用悲观锁，需要关闭mysql的自动提交功能，将 set autocommit = 0

注意，mysql 中的行级锁是基于索引的，如果 sql 没有走索引，那将使用表级锁把整张表锁住。

悲观锁在并发控制上采取的是先上锁然后再处理数据的保守策略，虽然保证了数据处理的安全性，但也降低了效率。

乐观锁

顾名思义，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。

如果发现冲突了，则返回错误信息给用户，让用户自已决定如何操作。

乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。

查看当前连接数

show status like 'Threads%';

Threads_running 当前连接数

按客户端 IP 分组，看哪个客户端的链接数最多

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 （此处 5分钟 可根据自己的需要调整SQL标红处）

select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

查看数据库最大连接数

show global variables like 'max_conn%';

max_connections 设置的最大连接数

临时设置最大连接数

临时设置到 200 个连接数 set GLOBAL max_connections=200

显示当前运行的连接数

官方文档：SHOW PROCESSLIST语句

show processlist 或 show full processlist

• id：用户登录 MySQL 时，系统分配的 connection_id 可以使用函数 connection_id() 查看，可以通过 kill 命令，加上这个 Id 值将这个线程杀掉

• user：显示当前用户，如果不是 root 这个只显示用户权限范围的 SQL 语句

• host：显示这个查询语句是从哪个地方发起的，可以用来跟踪出现问题语句的用户

• DB：显示这个进程目前连接的是哪个数据库

• command：显示当前连接的执行的命令，这个比较复杂，见下文描述

• time：这个状态持续的时间，单位 秒

• state：显示使用当前连接的 sql 语句的状态，很重要的列。state 描述的是语句执行中的某一个状态。一个 sql 语句，以查询为例，可能需要经过 copying to tmp table、sorting result、sending data 等状态才可以完成

• info：显示这个sql语句，是判断问题语句的一个重要依据，默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist

Command 值详解

模拟连接数过多

#!/bin/bash
set j=2
while true
do
        let "j=j+1"
/usr/local/mysql/bin/mysqlslap -a -c 500 -i 10 -uroot -p1234.com
done

explain 详解

表 xinhua_ciyu 数据条数 264434

字段 ciyu 无索引的情况

字段 ciyu 有索引(index)的情况

连表查询

• a 表数据：238
• b 表数据：88

字段简述

id

SELECT识别符。这是SELECT的查询序列号

• id相同时，执行顺序由上至下

• 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

• id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

表示查询中每个 select 子句的类型

• SIMPLE(简单SELECT，不使用UNION或子查询等)

• PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

• UNION(UNION中的第二个或后面的SELECT语句)

• DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

• UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

• SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

• DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

• DERIVED(派生表的SELECT, FROM子句的子查询)

• UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

partitions

版本5.7以前，该项是 explain partitions 显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）

type，这个字段很重要

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有（性能从好到坏）：system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL

• system 表中只有一行数据或者是空表，这是 const 类型的一个特例。且只能用于myisam 和 memory 表。如果是 Innodb 引擎表，type 列在这个情况通常都是 all 或者 index

• const 最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描

• eq_ref 多表 join 时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了 system 和 const 之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作 join 联接时会使用此类型。 eq_ref 可用于使用 = 操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。

注：相对于下面的 ref 区别就是它使用的唯一索引，即主键或唯一索引，而 ref 使用的是非唯一索引或者普通索引。eq_ref 只能找到一行，而 ref 能找到多行。

• ref 对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用 ref 类型（也就是说，此联接能够匹配多行记录）ref 可用于使用 = 或 <=> 操作符作比较的索引列。

• fulltext 使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql 不管代价，优先选择 使用全文索引

• ref_or_null 跟 ref 类型类似，只是增加了 null 值的比较。实际用的不多。

• index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见 and ，or 的条件使用了不同的索引，官方排序这个在 ref_or_null 之后，但是实际上由于要读取多个索引，性能可能大部分时间都 不如range

• unique_subquery 用于 where 中的 in 形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。该类型替换了下面形式的 IN 子查询的 ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery 该联接类型类似于 unique_subquery。适用于非唯一索引，可以返回重复值。

• range 索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 或者 like 等运算符的查询中。

• index: 索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra 中显示 Using index，反之，如果在索引上进行全表扫描，没有Using index 的提示。

# 此表只有一个 name 列索引。
# 因为查询的列 name 上建有索引，所以如果这样 type 走的是 index
mysql> explain select name from testa;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | index | NULL          | idx_name | 33      | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set

# 因为查询的列 cusno 没有建索引，或者查询的列包含没有索引的列，这样查询就会走 ALL 扫描，如下：
mysql> explain select cusno from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

# 包含有未见索引的列
mysql> explain select * from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

• all 全表扫描，性能最差。

possible_keys

查询可能使用到的索引都会在这里列出来

key

查询真正使用到的索引。

select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len

查询用到的索引长度（字节数）。

如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

不损失精确性的情况下，长度越短越好。

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows，这个最直观

MySQL 估计需要扫描的行数（估计值），越少越好，这个非常直观的显示SQL 的好坏

filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。这个字段不重要

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

• distinct：在select部分使用了distinc关键字

• Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

• Using index："覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

• Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

索引：是存储引擎用于快速查找记录的一种数据结构

缺点：

• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果，例如性别，学历等等
• 会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件
• 建立索引会占用磁盘空间的索引文件，空间不值钱，放心整
• 对于非常小（多小？？？）的表，大部分情况下简单的全表扫描更高效

索引类型

普通索引

这是最基本的索引，它没有任何限制，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。

唯一索引

普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引

它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

与之类似的，外键索引（如果不是必要，最好不用，外键个数也不能太多）

如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

联合索引 - 最左前缀

建表的时候，name 为 varchar(20)，建立索引时可以指定为 10 ，因为大部分 name 都是 10 以内，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度 `ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

A、B、C 建立联合索引，其实相当于建立了如下索引：A，B，C、A，B、A

• 这两条查询会用到索引：

SELECT * FROM users WHREE A = "admin" AND B = "shuxiaoyuan"
SELECT * FROM users WHREE A = "admin"

• 这两个查询不会用到索引：

SELECT * FROM users WHREE C = 20 AND B = "shuxiaoyuan"
SELECT * FROM users WHREE B = "shuxiaoyuan"

住：为什么不将他们三个分别建立一个索引呢？虽然你建立了三个，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引，这样效率会远远低于我们的组合索引

全文索引

全文索引是MyISAM的一个特殊索引类型，它查找的是文本中的关键词，主要用于全文检索。

空间(R-Tree)索引

MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

Hash索引

哈希索引基于哈希表实现，只有精确索引所有列的查询才有效。

建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL的 B-Tree 只对 <，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE才会使用索引。

例如：SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

索引的正确使用和设计

参考链接：浅谈MySQL的B树索引与索引优化

优先使用自增key作为主键

自增一般是 int 等整数型，key比较紧凑，这样m可以非常大，而且索引占用空间小。最极端的例子，如果使用50B的varchar（包括长度），那么m = 4 * 1024 / 54m = 75.85 ~= 76，深度最大 log(76/2)(10^7) = 4.43 ~= 5，再加上cache缺失、字符串比较的成本，时间成本增加较大。同时，key由4B增长到50B，整棵索引树的空间占用增长也是极为恐怖的（如果二级索引使用主键定位数据行，则空间增长更加严重）。

自增的性质使得新数据行的插入请求必然落到索引树的最右侧，发生节点分裂的频率较低，理想情况下，索引树可以达到“满”的状态。索引树满，一方面层高更低，一方面删除节点时发生节点合并的频率也较低。

尽量使用数字型（简单的数据类型）

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

尽量不要让字段的默认值为NULL

在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。 索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时尽量不要让字段的默认值为NULL，应该指定列为NOT NULL，除非你想存储NULL。你应该用0、一个特殊的值或者一个空串代替空值。

前缀索引和索引选择性

对串列进行索引，如果可能应该指定一个前缀长度。

使用唯一索引

考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例如对性别设置索引就没有意义

使用组合索引代替多个列索引

一个多列索引（组合索引）与多个列索引MySQL在解析执行上是不一样的（上文有说），如果在 explain 中看到有索引合并（即MySQL为多个列索引合并优化），应该好好检查一下查询的表和结构是不是已经最优。

注意重复/冗余的索引、不使用的索引

MySQL允许在相同的列上创建多个索引，无论是有意还是无意的。大多数情况下不需要使用冗余索引。

大文本搜索，尽量使用全文索引

如果对大的文本进行搜索，使用全文索引而不要用使用 like ' %…% '

能扩展就不要新建索引

如果已有索引(a, b)，则不需要再建立索引(a)，但是如果有必要，则仍然需考虑建立索引(b)。

like语句不要以通配符开头，会放弃索引

不要在列上进行运算

# 无法使用索引
select actor_id from sakila.actor where actor_id+1=5;
select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

尽量不要使用 NOT IN <> !=操作

应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描，对于 not in，可以用not exists或者（外联结+判断为空）来代替；很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换： select num from a where exists(select 1 from b where num=a.num) 对于<>，用其它相同功能的操作运算代替，如a<>0改为 a>0 or a<0

选择区分度搞的列作索引

如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。当索引列有大量数据重复时,查询可能不会去利用索引，如一表中有字段sex，male、female 几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

这个好理解，比如性别，只有男女，这个就不适合建立索引

区分度的公式是 count(distinct 索引字段) / count(*)，表示字段不重复的比例，比例越大区分度越好。唯一键的区分度是1，而一些状态、性别字段可能在大数据面前的区分度趋近于0。

这个值很难确定，一般需要join的字段要求是0.1以上，即平均1条扫描10条记录。

or条件

用 or 分割开的条件， 如果 or 前的条件中的列有索引， 而后面的列中没有索引， 那么涉及到的索引都不会被用到，应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 假设num1有索引，num2没有索引，查询语句 select id from t where num1=10 or num2=20 会 放弃使用索引，可以改为这样查询： select id from t where num1=10 union all select id from t where num2=20，这样虽然 num2 没有使用索引，但至少 num1 会使用索引，提高效率

最左前缀 原则

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的。

使用索引排序时，ORDER BY也要遵守“最左前缀”原则

避免取所有字段 *

任何地方都不要使用 select * from table，用具体的字段列表代替 *，不要返回用不到的任何字段

如果列类型是字符串

那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的，因为MySQL 默认把输入的常量值进行转换以后才进行检索。 例如：

实现原理

哈希索引

B 树索引

B+ 树索引

物理存储

聚簇索引

聚簇索引的主键索引的叶子结点存储的是键值对应的数据本身；辅助索引的叶子结点存储的是键值对应的数据的主键键值。

引用百度百科：聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。

• 主键索引存储方式：

• 对应的辅助索引为：（键名称，大概的意思）：

• 主键索引和辅助索引检索过程

非聚簇索引

非聚簇索引的主键索引和辅助索引几乎是一样的，只是主索引重复重复，替换空值，他们的叶子结点都存储指向键值对应的数据的物理地址。

百度百科：非聚簇索引，索引的一种。索引分为聚簇索引和非聚簇索引两种。建立索引的目的是加快对表中记录的查找或排序。索引顺序与数据物理排列顺序无关。 非聚簇索引，叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。

首先，主键索引和辅助索引的叶子结点都存储着键值对应的数据的物理地址，这说明无论是主键索引还是辅助索引都能够通过直接获得数据，而不需要像聚簇索引那样在检索辅助索引时还可以绕过一圈。

同时还说明一个点，叶子结点存储的是物理地址，那么表示数据实际上是存在另一个地方的，并不是存储在B +树的结点中。这说明非聚簇索引的数据表和索引表是分开存储的。

• 非聚簇索引的检索过程

索引性能测试与索引优化

只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快——不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

EXPLAIN

在不确定应该在哪些数据列上创建索引的时候，人们从EXPLAIN SELECT 命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的 SELECT 命令加一个 EXPLAIN 关键字作为前缀而已。有了这个关键字，MySQL 将不是去执行那条SELECT 命令，而是去对它进行分析。MySQL 将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出来。

查看索引使用情况：

• 如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

• Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描， Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

字符集

字符集表示存储到数据库的字符以何种格式编码（所谓编码指的是字符映射到二进制数据的过程，解码则是对应的逆操作）以及支持的编码字符范围，常见的字符集有 ASCII、ISO 8859-1（在 ASCII 字符集上进行扩展，对应 MySQL 中的 latin1）、GBK、UTF8 等，在 MySQL中可以通过 show charset 命令查看系统支持的所有字符集：

可以看到 utf8 的 Maxlen 为 3，utf8mb4 的 Maxlen 为 4

排序规则

而排序规则（或者叫做比较规则）表示存储到数据库的字符以何种方式比较大小进行排序，如果没有指定排序规则，每个字符集有着默认的排序规则，见上图 Default collation 字段，雌此外，你也可以通过 show collation 命令查看 MySQL 支持的所有排序规则：

排序规则都有固定的格式：字符集_语言_后缀

• 字符集表示字符对应的编码字符集，对应前面指定的 charset 设置，即某个字符集需要使用与之对应的排序规则；

• 语言表示使用的自然语言，比如 spanish、polish 等，general 则是通用的；

• 后缀一般是 ci，表示排序比较的时候字符不区分大小写，如果是 cs 则表示区分大小写，上图中还可以看到 bin 后缀，表示以二进制格式比较，通常我们都使用 ci。

慢查询基础：优化数据访问

是否请求了不需要的数据

• 查询了不需要的记录，比如取条数要加上 limit ，不然MySQL会返回所有数据，然后丢弃绝大部分数据

• 多表关联时只返回需要的列，而不是所有表的所有列

• 任何时候都不要 select * 除非系统做了缓存

• 不要重复查询相同的数据，如果发现需要查询相同的数据，那么最好用缓存

是否存在扫描额外的记录

响应时间

响应时间 = 服务器时间 + 排队时间

• 服务器时间：指数据库处理这个查询真正花了多长时间

• 排队时间：服务器因为等待某些资源而没有真正执行查询的时间，一般是 I/O 和锁等待，实际情况很复杂

扫描的行数和返回的行数

理想情况下，扫描的行数和返回的行数应该是相同的，不过这个基本上不可能，一般在 1:1 到 10:1 之间

扫描的行数和访问类型

根据主键 ID 查询

字段 ciyu 有唯一索引时：和主键ID基本一致

字段 ciyu 无索引时：如下图

关于 EXPLAIN 各字段的解释，请看单独章

重构查询的方式

一个复杂查询还是多个简单查询

切分查询

将一个大查询分解成多个小查询

比如定期删除数据，如果一次性删除所有行，那么会锁住很多数据，占满事务日志，耗尽系统资源，阻塞很多小的但很重要的查询

每次删除一万条，每次删除后暂停一会再执行下一次删除，可以将服务器上原本一次性的压力分散到一个很长的时间内

delete from messages where created_at < DATE_SUB(NOW(),INTERVAL 3 MONTH);

# 可以拆分为
rows_affected = 0
do {
	rows_affected = do_query (
		"delete from messages where created_at < DATE_SUB(NOW(),INTERVAL 3 MONTH) limit 10000"
	)
} while rows_affected > 0

分解关联查询

select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag_tag = 'mysql';

# 可以分解成
select * from tag where tag = 'mysql';
select * from tag_post where tag_id  = 1234;
select * from post where post.id in (123,345,456,9089,8904);

优势在哪里：

• 让缓存的效率更高，可以很方便的缓存单表查询对应的结果对象。例如，tag已经被缓存了，那么可以直接跳过第一个查询，如果缓存了 ID 为 123、 345、 456 的数据，那么第三个 IN 语句就可以少几个ID，还有 MySQL 的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分之后，如果某个表的变化很小，那么基于该表的查询就可以重复利用查询缓存的结果了。

• 将查询分解后，执行单个查询可以减少锁的竞争

• 在应用层做关联，可以很方便的对数据库进行拆分，更容易做到高性能和可扩展

• 查询本身效率可能会有所提高，在这个例子中，使用 IN 代替关联查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的关联要更高效

• 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录，应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据，这样的重构还可能会减少网络和内存的消耗

• 更进一步，这样做相当于在应用层实现了哈希关联，而不是使用 MySQL 的嵌套循环关联，某些场景哈希关联的效率要高很多

MySQL 查询执行路径

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API执行查询。
5. 返回结果给客户端。

开启慢查询日志

临时开启（永久开启修改配置文件）

# 开启
set global slow_query_log = on;

# 关闭
set global slow_query_log = off;

设置慢查询时间

# 超过 1 秒的 SQL 都将被记录
set long_query_time = 1;

慢查询日志分析工具

##pt-query-digest

附录：一些配置

准备表和数据

• 准备表

CREATE TABLE `test_tmp` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `openid` VARCHAR(50) NOT NULL COMMENT 'openID',
    `unionid` VARCHAR(50) NULL DEFAULT NULL COMMENT 'unionID',
    `task_id` INT(11) NOT NULL COMMENT '领取的任务ID',
    `state` INT(11) NOT NULL DEFAULT '1' COMMENT '1进行中 2已完成 3失败',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) COMMENT='临时测试表' ENGINE=InnoDB;

• 准备数据

for ($i = 0; $i < 1000; $i++) {
    $data[] = [
        'openid'     => 'user_' . mt_rand(1, 100),
        'unionid'    => md5('shuxiaoyuan' . mt_rand(1, 100)),
        'task_id'    => mt_rand(1, 10),
        'state'      => mt_rand(1, 3),
        'created_at' => date('Y-m-d H:i:s'),
        'updated_at' => date('Y-m-d H:i:s'),
    ];
}

DB::table('test_tmp')->insert($data);

查询

• 查询出重复的字段和重复的数目，并按照重复的数量排序

SELECT openid,COUNT(openid) AS a FROM test_tmp GROUP BY openid HAVING COUNT(*) > 1 ORDER BY a DESC;

查询结果如下所示：

• 有多少人领取了重复的任务（就是 openID 和 task_id 两个字段重复）并计算重复的个数

SELECT openid,task_id,COUNT(*) FROM test_tmp GROUP BY openid,task_id HAVING COUNT(*) > 1;

查询结果如下所示：

可以进一步验证查询结果是否正确，经验证，正确无误

表名和表结构数据：users

方案一

删除 unionid 字段重复的数据，并保留 ID 最小的

DELETE FROM users
WHERE id NOT IN (
 SELECT u.min_id
 FROM (SELECT MIN(id) AS min_id FROM users GROUP BY unionid) AS u
);

删除后的结果

实际测试说明：本机测试 表中数据条数：417936 重复的 unionid 条数：2188 重复的 unionid 条数总数：19179(14796x1 + 4x2 + 3x5 + 2180x2) 最终删除数据：16991 执行时间：1′12″6

方案二

表名和表结构数据：users

执行删除命令

DELETE u1
FROM
	users AS u1,users AS u2
WHERE u1.unionid=u2.unionid AND u1.id > u2.id;

删除后的数据

1、找出想保留数据的id，这一步是最关键的一步

# id 是自增的情况，很简单
SELECT MIN(id) AS min_id FROM users GROUP BY unionid;

# id 不是自增，如 UUID 时，就需要根据插入时间来排序了，别告诉我你这个也没有
SELECT min(created_at) time,openid FROM channel GROUP BY openid;

2、把其他的数据删除

附录：其他操作，随便说一下

• 查询重复数据（多字段）

select * from miniapp_user_activity_infos_temp a where (a.openid,a.map_id,a.created_at) IN
(
 select openid,map_id,created_at from miniapp_user_activity_infos_temp
 group by openid,map_id,created_at having count(*) > 1
 )

• 查出数据（保留ID最小的一条）

select * from channel where id IN (select min(id) from channel GROUP BY openid);

• 看有多少条数据

select count(1) from channel where id In (select min(id) from channel GROUP BY openid);

附录：https://www.jb51.net/article/116677.htm

case when then 用法

表结构

有如下表结构：

准备表结构和数据

创建表

CREATE TABLE `table1` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`event_id` INT(10) UNSIGNED NOT NULL,
	`store_id` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`status` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`) USING BTREE
)

填充数据

INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('1', 'A001', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('1', 'A002', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('1', 'A003', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('2', 'A001', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('2', 'A002', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('2', 'A003', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('3', 'A001', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('3', 'A002', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('3', 'A003', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('4', 'A001', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('4', 'A002', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('4', 'A003', '0');

需求

需求：根据 event_id 查出如下对应信息，参与状态为 status 字段，1 已完成，0 未完成

思路一：采用 case when then

select

store_id,count(store_id),

sum(CASE `status` WHEN '1' then '1' ELSE  '0' end ) as num1,

sum(CASE `status` WHEN '0' then '1' ELSE  '0' end ) as num0

FROM table1

WHERE

event_id = 1

GROUP BY store_id

思路二：使用 if

参考链接：https://blog.csdn.net/lilong329329/article/details/81664451

项目中用到的真实查询 SQL

select

a.store_id,count(a.store_id),

sum(CASE `status` WHEN '1' then '1' ELSE  '0' end ) as num1,

sum(CASE `status` WHEN '0' then '1' ELSE  '0' end ) as num0,

b.type

FROM t_rush_buy_event_successfuls as a LEFT JOIN t_store as b

on a.event_id = b.event_id and a.store_id = b.serial_number

WHERE

a.event_id = 156

GROUP BY a.store_id

多表查询的小技巧

SELECT DATE_SUB(CURDATE(),INTERVAL 90 DAY);


SELECT  CURDATE();

查询每个月的收入

select year(consume_date) 年, month(consume_date) 月,
round(sum(money),2) 收入
from account_book
where type = 1
group by year(consume_date),month(consume_date);

查询 6 个月前的数据

# CURDATE()：当前日期，2020-04-20
# DATE_SUB(CURDATE(), INTERVAL 6 MONTH)：6个月前，2019-10-20
select * from account_book where consume_date > DATE_SUB(CURDATE(), INTERVAL 6 MONTH)

附录：Date 函数相关的衍生

Date 函数

查找最近 N 个(月，天，年)的数据

• 从当前的日期开始的 N 个月前的日期：DATE_SUB(CURDATE(), INTERVAL N MONTH)

DATE_SUB() 函数

DATE_SUB() 函数从日期减去指定的时间间隔

具体函数参数可以参考该链接：DATE_SUB() 函数

示例：

# 时间格式为 2019-01-01 12:11:10
select date_format(reserve_time,'%Y%m%d %H%i') as minute, count(*) as number from t_rush_buy_event_records where event_id = ? group by minute order by count(1) desc limit 1

# 表中记录的时间格式为时间戳
select from_unixtime(milli_draw_time,'%Y%m%d %H%i') as minute, count(*) as number from t_rush_buy_event_records where event_id = ? and type = 2 group by minute order by count(1) desc limit 1

将 A 表数据完整的复制到 B 表中

快速创建备份表

# 如有必要，先删除 B 表
DROP TABLE IF EXISTS B;

# 创建新表，跟旧表数据结构保持一致
CREATE TABLE 新表名 LIKE 旧表

表结构完全一样

INSERT INTO B表 SELECT* FROM A表;

表结构不一样

insert into 表1 (列名1,列名2,列名3) select 列1,列2,列3 from 表2

临时插入表数据，由于有外键约束，导致插入失败，可以临时关闭外键

查看当前外键状态: SELECT @@FOREIGN_KEY_CHECKS;

临时关闭: SET FOREIGN_KEY_CHECKS = 0;

临时开启: SET FOREIGN_KEY_CHECKS = 1

清空有外键约束的表

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE t_members;
SET FOREIGN_KEY_CHECKS = 1;

导入有外键约束的表，先全局禁用外键约束，然后再开始外键约束

或者在开头加入 SET FOREIGN_KEY_CHECKS = 0;

增

删

改

# 更新存在的字段
update t_members set channel_extend = json_set(channel_extend,"$.path","pages/index/index") where id = 5447;

# 更新不存在的字段，是添加这个键值对
update t_members set channel_extend = json_set(channel_extend,"$.time","202008081225") where id = 5447;

# 更新 json 里面还有 json 的
update t_members set channel_extend = json_set(channel_extend,"$.params.channel","CVD") where id = 5447;

查

• json字段名->'$.json属性' 进行查询

单条件查询

# 查询 json 字段（channel_extend）中 scene = 1017 的
SELECT id,channel_extend FROM  t_members WHERE  channel_extend->'$.scene'=1017;

多条件查询

SELECT id,channel_extend FROM  t_members WHERE  channel_extend->'$.scene'=1017 AND  channel_extend->'$.path'='pages/memberCenter/pages/screen/index'

关联表查询

时间日期相关函数

NOW()

返回当前的日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-04-19 14:34:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now(),curdate(),curtime();
+---------------------+------------+-----------+
| now()               | curdate()  | curtime() |
+---------------------+------------+-----------+
| 2022-04-19 14:36:15 | 2022-04-19 | 14:36:15  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)

CURDATE()

返回当前的日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-04-19 |
+------------+
1 row in set (0.00 sec)

CURTIME()

返回当前的时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:35:17  |
+-----------+
1 row in set (0.00 sec)

DATE()

DATE() 函数提取日期或日期/时间表达式的日期部分

EXTRACT()

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

语法:EXTRACT(unit FROM date)

• date 参数是合法的日期表达式。
• unit 参数可以是下列的值：

DATE_ADD()

DATE_ADD() 函数向日期添加指定的时间间隔。

语法:DATE_ADD(date,INTERVAL expr type)

• date 参数是合法的日期表达式。
• expr 参数是您希望添加的时间间隔。
• type 参数可以是下列值：

DATE_SUB()

从日期减去指定的时间间隔

格式：DATE_SUB(date,INTERVAL expr type)

• date 参数是合法的日期表达式。
• expr 参数是您希望添加的时间间隔。
• type 参数可以是下列值：

示例：

mysql> SELECT DATE_SUB('2022-06-06',INTERVAL 1 DAY);
+---------------------------------------+
| DATE_SUB('2022-06-06',INTERVAL 1 DAY) |
+---------------------------------------+
| 2022-06-05                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);
+------------------------------------+
| DATE_SUB(CURDATE(),INTERVAL 1 DAY) |
+------------------------------------+
| 2022-04-18                         |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2022-01-04 12:00:09',INTERVAL 1 second);
+---------------------------------------------------+
| DATE_SUB('2022-01-04 12:00:09',INTERVAL 1 second) |
+---------------------------------------------------+
| 2022-01-04 12:00:08                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

DATEDIFF()

DATE_FORMAT()

创建表

CREATE TABLE `t_games` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`member_id` VARCHAR(255) NOT NULL COMMENT '会员ID' COLLATE 'utf8mb4_unicode_ci',
	`openid` VARCHAR(255) NOT NULL COMMENT 'openid' COLLATE 'utf8mb4_unicode_ci',
	`date` DATE NOT NULL COMMENT '玩游戏的日期',
	`time` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '游戏耗时',
	`nick_name` VARCHAR(255) NULL DEFAULT NULL COMMENT '姓名' COLLATE 'utf8mb4_unicode_ci',
	`avatar_url` VARCHAR(255) NULL DEFAULT NULL COMMENT '头像' COLLATE 'utf8mb4_unicode_ci',
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)COMMENT='参与游戏记录';

写入数据

INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (1, '1', '1', '2022-05-07', 100, 'xxx1', '111', '2022-05-07 20:52:39', '2022-05-07 20:52:39');
INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (2, '1', '1', '2022-05-07', 50, 'xxx2', '111', '2022-05-07 20:56:13', '2022-05-07 20:56:13');
INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (3, '2', '2', '2022-05-07', 50, 'xxx3', '222', '2022-05-07 20:58:32', '2022-05-07 20:58:32');
INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (4, '2', '2', '2022-05-07', 49, 'xxx4', '222', '2022-05-07 20:59:52', '2022-05-07 20:59:52');
INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (5, '3', '3', '2022-05-07', 49, 'xxx5', '333', '2022-05-07 21:00:30', '2022-05-07 21:00:30');
INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (6, '3', '3', '2022-05-07', 52, 'xxx6', '333', '2022-05-07 21:01:19', '2022-05-07 21:01:19');
INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (7, '4', '4', '2022-05-07', 49, 'xxx7', '444', '2022-05-07 21:17:41', '2022-05-07 21:17:41');
INSERT INTO `t_games` (`id`, `member_id`, `openid`, `date`, `time`, `nick_name`, `avatar_url`, `created_at`, `updated_at`) VALUES (8, '4', '4', '2022-05-07', 15, 'xxx8', '444', '2022-05-07 21:38:08', '2022-05-07 21:38:08');

查询每个用户游戏过关时间最短的

## 查询查询每个用户过关时间最短的时间是多少
SELECT member_id,MIN(`time`) AS min_time FROM t_games GROUP BY member_id;

## 将上述结果按照游戏过关时间进行排序
SELECT member_id,MIN(`time`) AS min_time FROM t_games GROUP BY member_id ORDER BY min_time DESC;

查询每个用户最后一次游戏时间

方法一

SELECT * FROM t_games WHERE id IN
(
SELECT MAX(id) FROM `t_games` GROUP BY member_id
);

弊端：显而易见，采用的是 IN 查询，所以在 IN 的数量太大的情况下，会直接报错，IN 可能会产生大量碎片

方法二

SELECT t1.*
FROM t_games t1
INNER JOIN (
    SELECT member_id, MAX(date) AS max_date
    FROM t_games
    GROUP BY member_id
) t2 ON t1.member_id = t2.member_id AND t1.date = t2.max_date;

查询需求

查询用户 指定月 各 类型的支出 总额

如下表结构

CREATE TABLE `sxy_consume_books` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
	`type` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '1支出，2收入，4不计入收支',
	`type_id` BIGINT(20) NOT NULL COMMENT '类型ID',
	`channel_id` BIGINT(20) NOT NULL COMMENT '渠道ID',
	`money` BIGINT(20) UNSIGNED NOT NULL COMMENT '金额,单位分',
	`date` DATE NOT NULL COMMENT '消费日期',
	`year` YEAR NOT NULL DEFAULT '2023' COMMENT '年',
	`month` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '月',
	`comment` TEXT NULL DEFAULT NULL COMMENT '备注' COLLATE 'utf8mb4_unicode_ci',
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	`deleted_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `sxy_consume_books_user_id_index` (`user_id`) USING BTREE,
	INDEX `date` (`date`) USING BTREE,
	INDEX `sxy_consume_books_year_month_index` (`year`, `month`) USING BTREE
)
COMMENT='记账本明细'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;

如下数据

INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (1, 1, 1, 1, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:31:47', '2023-04-17 16:31:47', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (2, 1, 1, 1, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:37', '2023-04-17 16:46:37', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (3, 1, 1, 2, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:39', '2023-04-17 16:46:39', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (4, 1, 1, 2, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:40', '2023-04-17 16:46:40', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (5, 1, 1, 3, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:46:41', '2023-04-17 16:46:41', NULL);
INSERT INTO `sxy_consume_books` (`id`, `user_id`, `type`, `type_id`, `channel_id`, `money`, `date`, `year`, `month`, `comment`, `created_at`, `updated_at`, `deleted_at`) VALUES (6, 1, 1, 4, 1, 100, '2023-04-17', '2023', 4, '测试', '2023-04-17 16:48:10', '2023-04-17 16:48:10', NULL);

查询1

SELECT type_id,SUM(money) AS '总消费'
FROM sxy_consume_books
WHERE user_id = 1 AND `type` = 1 AND `year` = 2023 AND `month` =  4
GROUP BY type_id
WITH ROLLUP;

结果1

批量删除多张表，有统一前缀的

# 拼接 SQL，将数据库名和表前缀替换掉即可
select concat('drop table ', TABLE_NAME, ';') from information_schema.TABLES
where TABLE_SCHEMA = 'test' and TABLE_NAME like 'sxy_order_%'

示例

• 获取指定范围的随机数 round(rand()*(max-min)+min)

• 获取指定范围的整数 FLOOR(i+RAND()*(j–i+1))

• 更新字段为随机值 UPDATE test_user_comments SET reply_id = FLOOR(10+RAND()*(100-10)) where id > 66;

• 随机取一条数据，更新字段为随机值 ``

示例如下：

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7765779064750093 |
+--------------------+
1 row in set (0.00 sec)

mysql> select round(1.3456456,5);
+--------------------+
| round(1.3456456,5) |
+--------------------+
|            1.34565 |
+--------------------+
1 row in set (0.00 sec)

mysql> select floor(1.4356);
+---------------+
| floor(1.4356) |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select ceiling(2.457);
+----------------+
| ceiling(2.457) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(rand()*(100-20)+20);
+---------------------------+
| round(rand()*(100-20)+20) |
+---------------------------+
|                        74 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select floor(10+rand()*(100-10+1));
+-----------------------------+
| floor(10+rand()*(100-10+1)) |
+-----------------------------+
|                          59 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_user_comments SET reply_id = FLOOR(10+RAND()*(100-10)) where id < 10;
Query OK, 9 rows affected (0.10 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql>

根据字段的排序集来排序的

whereIn 时，in 中的字段顺序无意义，会根据这个字段的排序

表结构：

CREATE TABLE `t_gift_cards` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`code` VARCHAR(64) NOT NULL COMMENT 'code' COLLATE 'utf8mb4_unicode_ci',
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `gift_cards_code_unique` (`code`) USING BTREE,
);

concat()函数

功能：将多个字符串连接成一个字符串。

语法：concat(str1, str2,...)，返回结果为连接参数产生的字符串，如果有任何一个参数为 NULL，则返回值为 NULL

3、举例： select concat(area,fr,best_history_data) from test_concat order by id limit 5;

如果想在字段间加分隔符，需要在每两个字段间都增加一个分隔符，比较麻烦：

select concat(area,',',fr,',',best_history_data) as test_result from test_concat order by id limit 5;

concat_ws()函数

功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）

语法：concat_ws(separator, str1, str2, ...)

说明：第一个参数指定分隔符。需要注意的是分隔符不能为 null，如果为 null，则返回结果为 null。

select concat_ws(',',area,fr,best_history_data) from test_concat order by id limit 5;

注意：和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回 NULL

group_concat()函数

功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

测试table：

select * from test_table;

1.根据area分组，拼接每个区域各个指标的指标值

select group_concat(fr,best_history_data) from test_table group by area;

2.增加分割符

select group_concat(fr,best_history_data separator '|') from test_table group by area;

3.结合concat_ws()函数，在fr与best_history_data之间增加分割符-

select group_concat(concat_ws('-',fr,best_history_data) separator '|') from test_table group by area;

4.根据best_history_data进行排序

select group_concat(concat_ws('-',fr,best_history_data) order by best_history_data desc separator '|') from test_table group by area;

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

如果需要Stored Generated Golumn的话，可能在Virtual Generated Column上建立索引更加合适

综上，一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式，注意不 能建立虚拟列和真实列的联合索引 语法：

<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

创建虚拟字段

alter table `table1` add user_name varchar(50) generated always as (concat(last_name,first_name)) COMMENT '虚拟字段，请勿手动插入或更新';

添加索引

alter TABLE table1 add index idx_name(user_name);

查询

select * from table1 where user_name = '舒孝元';

在删除数据库中的数据时，常常会存在这样几种情况：

• 已经确定删除整张表和数据，也不需要恢复数据了，用 drop；

• 删除数据不删除表，但是删除之后还有可能会后悔，用 delete；

• 只想删除数据，不用删除表，删除数据后不会后悔了，用 truncate；

• 执行速度：drop> truncate > delete

用法

• DROP TABLE 表名;

drop 是直接将表格删除，无法找回。将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index)；

• DELETE FROM 表名;

delete 可以和 where 子句连用删除指定行；

delete 的效果相当于一行行删除，所以可以 rollback;

delete 不会删除索引 （新插入的数据将在删除数据的索引后继续增加）；

• TRUNCATE TABLE 表名;

truncate 是删除表中所有数据，但不能与 where 一起使用；

truncate 删除后不记录 mysql日志，因此不可以 rollback，更不可以恢复数据；

truncate 删除后将重建索引；

truncate 不会触发任何 DELETE触发器；

外键约束无法删除

如有有外键约束，无法删除时，可以临时关闭外键约束，更详细的关于关闭外键约束的，请看 一些实用查询-->外键约束关闭

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE pcc_telescope_entries;
TRUNCATE TABLE pcc_telescope_entries_tags;

说明注意事项，先将 binlog 日志备份到其他地方再操作，在恢复的时候，这个恢复的binlog也会写入到binlog中，所以最好新建一个biglog文件

# 获取指定库的日志文件 -d 指定数据库 -r 指定输出在哪个文件
mysqlbinlog -d test binlog.000010 -r test.sql

# 禁止恢复过程产生日志 -D
mysqlbinlog -d test -D binlog.000010 -r test1.sql

# 只显示SQL语句 -s
mysqlbinlog -s binlog.000010 -r test2.sql