MySql一些有用的知识点[1]

摘要

  • MySql知识点介绍:执行计划相关、索引相关、用户管理、线程状态、常用操作、表信息相关、系统变量、mysql中的数据类型对应java实体数据类型、数据库导出与导入,忘记root密码后如何重置等等

  • 本文基于mysql-8.0.30https://dev.mysql.com/doc/refman/8.0/en/

执行计划相关

什么是执行计划

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划,我们需要搞懂EXPLAIN的各个输出项都是干嘛使的,从而可以有针对性的提升我们查询语句的性能。
通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析查询语句或是表结构的性能瓶颈。
通过EXPLAIN我们可以知道:
⚫ 表的读取顺序
⚫ 数据读取操作的操作类型
⚫ 哪些索引可以使用
⚫ 哪些索引被实际使用
⚫ 表之间的引用
⚫ 每张表有多少行被优化器查询

查看执行计划

执行计划的语法其实非常简单:在SQL查询的前面加上EXPLAIN关键字就行

1
2
3
4
5
6
mysql> explain select * from employees where id = 1
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1 | SIMPLE | employees | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
  • 以下示例用到两张表,employees与employees2表结构相同

1
2
3
4
5
6
7
8
9
10
11
12
show create table employees\G
***************************[ 1. row ]***************************
Table | employees
Create Table | CREATE TABLE `employees` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100004 DEFAULT CHARSET=utf8mb3 COMMENT='员工记录表'

重点属性说明

id

id列的编号是 select 的序列号,每个SELECT关键字都对应一个唯一的 id,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的。
id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。

select_type

select_type 查询的类型,表示对应行是简单还是复杂的查询。

  • 1)SIMPLE:简单查询。查询不包含子查询和union
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
# 单表查询
explain select * from employees
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| 1 | SIMPLE | employees | <null> | ALL | <null> | <null> | <null> | <null> | 92796 | 100.0 | <null> |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+

# inner join 连接查询
explain select * from employees inner join employees2 on employees.id = employees2.id
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| 1 | SIMPLE | employees2 | <null> | ALL | PRIMARY | <null> | <null> | <null> | 10 | 100.0 | <null> |
| 1 | SIMPLE | employees | <null> | eq_ref | PRIMARY | PRIMARY | 4 | test.employees2.id | 1 | 100.0 | <null> |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+

# 效果同inner join
explain select * from employees,employees2 where employees.id = employees2.id
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| 1 | SIMPLE | employees2 | <null> | ALL | PRIMARY | <null> | <null> | <null> | 10 | 100.0 | <null> |
| 1 | SIMPLE | employees | <null> | eq_ref | PRIMARY | PRIMARY | 4 | test.employees2.id | 1 | 100.0 | <null> |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
  • 2)PRIMARY:复杂查询中最外层的 select
  • 3)UNION:在 union 中的第二个和随后的 select查询,不依赖于外部查询的结果集
  • 4)UNION RESULT:UNION 结果集
1
2
3
4
5
6
7
8
explain select * from employees union select * from employees2
+--------+--------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------+--------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+-----------------+
| 1 | PRIMARY | employees | <null> | ALL | <null> | <null> | <null> | <null> | 92796 | 100.0 | <null> |
| 2 | UNION | employees2 | <null> | ALL | <null> | <null> | <null> | <null> | 10 | 100.0 | <null> |
| <null> | UNION RESULT | <union1,2> | <null> | ALL | <null> | <null> | <null> | <null> | <null> | <null> | Using temporary |
+--------+--------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+-----------------+
  • 5)SUBQUERY:包含在 select 中的子查询(不在 from 子句中),不依赖于外部查询的结果集
1
2
3
4
5
6
7
explain select * from employees where id in (select id from employees2) or name = 'a'
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+-------------+
| 1 | PRIMARY | employees | <null> | ALL | idx_name_age_position | <null> | <null> | <null> | 92796 | 100.0 | Using where |
| 2 | SUBQUERY | employees2 | <null> | index | PRIMARY | idx_name_age_position | 140 | <null> | 10 | 100.0 | Using index |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+-------------+
  • 6)DEPENDENT UNION:UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
  • 7)DEPENDENT SUBQUERY:子查询中的第一个 select 查询,依赖于外部查询的结果集
1
2
3
4
5
6
7
8
9
 explain select * from employees where id in (select id from employees2 where name = 'a' union select id from employees where age =20)
+--------+--------------------+------------+------------+--------+-------------------------------+---------+---------+--------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------+--------------------+------------+------------+--------+-------------------------------+---------+---------+--------+--------+----------+-----------------+
| 1 | PRIMARY | employees | <null> | ALL | <null> | <null> | <null> | <null> | 92796 | 100.0 | Using where |
| 2 | DEPENDENT SUBQUERY | employees2 | <null> | eq_ref | PRIMARY,idx_name_age_position | PRIMARY | 4 | func | 1 | 10.0 | Using where |
| 3 | DEPENDENT UNION | employees | <null> | eq_ref | PRIMARY,idx_name_age_position | PRIMARY | 4 | func | 1 | 10.0 | Using where |
| <null> | UNION RESULT | <union2,3> | <null> | ALL | <null> | <null> | <null> | <null> | <null> | <null> | Using temporary |
+--------+--------------------+------------+------------+--------+-------------------------------+---------+---------+--------+--------+----------+-----------------+
  • 8)DERIVED:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
1
2
3
4
5
6
7
explain select * from (select age,count(*) countNum from employees group by age) tmp where countNum > 3
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+------------------------------+
| 1 | PRIMARY | <derived2> | <null> | ALL | <null> | <null> | <null> | <null> | 92796 | 100.0 | <null> |
| 2 | DERIVED | employees | <null> | index | idx_name_age_position | idx_name_age_position | 140 | <null> | 92796 | 100.0 | Using index; Using temporary |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+------------------------------+
  • 9)MATERIALIZED:物化子查询,子查询物化就是将子查询的结果缓存在内存或临时表中,<subquery2>表示就是物化后的表
1
2
3
4
5
6
7
8
explain select * from employees where name not in (select name from employees2)
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+---------------------+-------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+---------------------+-------+----------+-------------------------+
| 1 | SIMPLE | employees | <null> | ALL | <null> | <null> | <null> | <null> | 92796 | 100.0 | <null> |
| 1 | SIMPLE | <subquery2> | <null> | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 75 | test.employees.name | 1 | 100.0 | Using where; Not exists |
| 2 | MATERIALIZED | employees2 | <null> | index | idx_name_age_position | idx_name_age_position | 140 | <null> | 10 | 100.0 | Using index |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+---------------------+-------+----------+-------------------------+
  • 10)UNCACHEABLE SUBQUERY: 结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估,出现极少。
  • 11)UNCACHEABLE UNION:UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询,出现极少。
1
2
3
4
5
6
7
explain select * from employees where id = (select id from employees2 where id= @@sql_log_bin)
+----+----------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+----------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | employees | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | <null> |
| 2 | UNCACHEABLE SUBQUERY | employees2 | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | Using index |
+----+----------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

table

table列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时,table列是 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查 询。
当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的 select 行id。

type

type表示关联类型或访问类型,从最优到最差分别为:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
出现比较多的是: system > const > eq_ref > ref > range > index > ALL,一般来说,得保证查询达到range级别,最好达到ref。
NULL表示mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。

类型说明

  • const, system: 常量查询,只查询一条记录
1
2
3
4
5
6
explain select * from employees where id = 1
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1 | SIMPLE | employees | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
  • eq_ref: 主键索引或唯一索引关联查询
1
2
3
4
5
6
7
explain select * from employees inner join employees2 on employees.id = employees2.id
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| 1 | SIMPLE | employees2 | <null> | ALL | PRIMARY | <null> | <null> | <null> | 10 | 100.0 | <null> |
| 1 | SIMPLE | employees | <null> | eq_ref | PRIMARY | PRIMARY | 4 | test.employees2.id | 1 | 100.0 | <null> |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
  • ref: 相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀
1
2
3
4
5
6
7
explain select * from employees inner join employees2 on employees.name = employees2.name
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+----------------------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+----------------------+------+----------+--------+
| 1 | SIMPLE | employees2 | <null> | ALL | idx_name_age_position | <null> | <null> | <null> | 10 | 100.0 | <null> |
| 1 | SIMPLE | employees | <null> | ref | idx_name_age_position | idx_name_age_position | 74 | test.employees2.name | 1 | 100.0 | <null> |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+----------------------+------+----------+--------+
  • range: 范围扫描,如 in(), between ,> ,<, >=
1
2
3
4
5
6
explain select * from employees where id > 1
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| 1 | SIMPLE | employees | <null> | range | PRIMARY | PRIMARY | 4 | <null> | 46398 | 100.0 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
  • index: 扫描全索引,一般扫描的是某个二级索引,二级索引一般比较小,所以这种通常比ALL快一些。
1
2
3
4
5
6
explain select name,position from employees where age = 20
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| 1 | SIMPLE | employees | <null> | index | idx_name_age_position | idx_name_age_position | 140 | <null> | 92796 | 10.0 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
  • ALL: 即全表扫描,扫描你的聚簇索引的所有叶子节点。
1
2
3
4
5
6
explain select * from employees
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| 1 | SIMPLE | employees | <null> | ALL | <null> | <null> | <null> | <null> | 92796 | 100.0 | <null> |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+

其它不常出现的也做简单说明

  • fulltext: 全文索引,不推荐使用

  • ref_or_null: 不仅想找出某个二级索引列的值等于某个常数的记录,还想把该列的值为NULL的记录也找出来,如WHERE order_no= 'abc' OR order_no IS NULL;

  • index_merge: 一般情况下对于某个表的查询只能使用到一个索引,在某些场景下可以使用索引合并的方式来执行查询

  • unique_subquery: 是针对在一些包含IN子查询的查询语句中,如果查询优化器决定将IN子查询转换为EXISTS子查询,而且子查询可以使用到主键进行等值匹配

  • index_subquery: 与unique_subquery类似,只不过访问⼦查询中的表时使⽤的是普通的索引

possible_keys

possible_keys列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有值,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
如果该列是NULL,则没有相关的索引。
某些情况下,比如类型是index时,可能会出现 possible_keys 列是空的,而 key 列展示的是实际使用到的索引,这是因为index的意思就是扫描二级索引。
possible keys 列中的值并不是越多越好,可能使用的索引越多,查询优化器计算查询成本时就得花费更长时间,所以如果可以的话,尽量删除那些用不到的索引。

key

key列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引,则该列是 NULL。

key_len

这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。
key_len计算规则如下:

  1. 字符串,char(n)和varchar(n),5.0.3以后版本中,n均代表字符数,而不是字节数,如果是utf-8,一个数字 或字母占1个字节,一个汉字占3个字节
    char(n):如果存汉字长度就是 3n 字节
    varchar(n):如果存汉字则长度是 3n + 2 字节,加的2字节用来存储字符串长度,因为 varchar是变长字符串
  2. 数值类型
    tinyint:1字节
    smallint:2字节
    int:4字节
    bigint:8字节
  3. 时间类型
    date:3字节
    timestamp:4字节
    datetime:8字节
    如果字段允许为 NULL,需要1字节记录是否为 NULL。
    索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

ref

ref列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id

rows

rows列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。
如果查询优化器决定使用全表扫描的方式对某个表执行查询时,执行计划的 rows 列就代表预计需要扫描的行数。
如果使用索引来执行查询时,执行计划的 rows 列就代表预计扫描的索引记录行数。

filtered

查询优化器预测有多少条记录满⾜其余的搜索条件,即基于全表扫描或索引扫描计算出要扫描的rows后,满足其余的查询条件的记录数在这些rows中所占的百分比,这也是一个预估值。比如下面这个查询计划:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 基于主键索引扫描的rows46398,此时filtered=100,因为没有其它查询条件,此时表示过滤的记录占比总的扫描rows100%
explain select * from employees where id > 10
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| 1 | SIMPLE | employees | <null> | range | PRIMARY | PRIMARY | 4 | <null> | 46398 | 100.0 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+

# 如果在加一个条件 age = 20,此时表示该过滤条件占比 rows 的百分比是 10%
explain select * from employees where id > 10 and age = 20
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| 1 | SIMPLE | employees | <null> | range | PRIMARY | PRIMARY | 4 | <null> | 46398 | 10.0 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+

Extra

Extra列展示的是额外信息,常见信息如下:
1)Using index:使用覆盖索引,索引排序
2)Using where:使用 where 语句来处理结果,并且查询的列未被索引覆盖,出现Using where 只是表示 MySQL 使用 where 子句中的条件对记录进行了过滤,并不表示当前sql没有使用索引,也不表示一定使用全表扫描
3)Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围
4)Using temporary:mysql需要创建一张临时表来处理查询,比如去重、排序、分组、Union之类的,需要使用索引优化
5)Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序,需要使用索引优化
6)Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段

filesort文件排序方式

单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;
双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID,然后在 sort buffer 中进行排序,排序完后需要再次取回其它需要的字段;
MySQL通过比较系统变量 max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果字段的总长度小于max_length_for_sort_data ,那么使用单路排序模式;
如果字段的总长度大于max_length_for_sort_data ,那么使用双路排序模式;
如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序,但不能因为这个就随便增大sort_buffer(默认1M),mysql很多参数设置都是做过优化的,不要轻易调整。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# mysql8是4k,mysql5是1k
mysql> show global variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 4096 |
+--------------------------+-------+

# 这里设置的4M
mysql> show global variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| sort_buffer_size | 4194304 |
+------------------+---------+

优化总结

1、MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index 效率高,filesort效率低。
2、order by满足两种情况会使用Using index:

  1. order by语句使用索引最左前列。
  2. 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则。
4、如果order by的条件不在索引列上,就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意,where高于having,能写在where中 的限定条件就不要去having限定了。

查看优化后的sql

mysql8.0以前的版本需要explain extended select * from actor where id = 1;

1
2
3
4
5
6
7
8
9
mysql> explain select * from actor where id = 1;
# 显示优化后的sql
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '1' AS `id`,'a' AS `name`,'2017-12-22 15:27:18' AS `update_time` from `test`.`actor` where true |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

关联查询inner join显式连接 VS 隐式连接,从执行计划上看两者是一回事,推荐带on的显示查询

1
2
3
4
5
6
7
# 显式连接1
select * from film_actor inner join film on film_actor.film_id = film.id;
# 显式连接2,有on会先执行on的关联,之后在进行where过滤,推荐关联关系放到on里面
select * from film_actor inner join film where film_actor.film_id = film.id;

# 隐式链接
select * from film_actor,film where film_actor.film_id = film.id;

索引相关

B+Tree(B-Tree变种)

  • 非叶子节点不存储data,只存储索引(冗余),目的是为了放更多的索引,减少树的高度,提高查询效率,非叶子结点由主键值和一个指向下一层的地址的指针组成。

  • 叶子节点包含所有索引字段,聚集索引包含全部字段,非聚集索引包含索引中的字段,叶子结点中由一组键值对和一个指向该层下一页的指针组成,键值对存储的主键值和数据

  • 叶节点之间通过双向链表链接,提高区间访问的性能

  • 在B+树中,一个结点就是一页,MySQL中InnoDB页的大小默认是16k,Innodb的所有数据文件(后缀为 ibd 的文件),其大小始终都是 16384(16k)的整数倍。

1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

计算机在存储数据的时候,最小存储单元是扇区,一个扇区的大小是 512 字节,而文件系统(例如 XFS/EXT4)最小单元是块,一个块的大小是 4KB。InnoDB 引擎存储数据的时候,是以页为单位的,每个数据页的大小默认是 16KB,即四个块。

B+Tree可以存放多少条数据,为什么是2000万?

  • 指针在InnoDB中为6字节,设主键的类型是bigint,占8字节。一组就是14字节。
  • 计算出一个非叶子结点可以存储16 * 1024 / 14 = 1170个索引指针。
  • 假设一条数据的大小是1KB,那么一个叶子结点可以存储16条数据。
  • 两层B+树可以存储1170 x 16 = 18720条数据。
  • 三层B+树可以存储1170 x 1170 x 16 = 21902400条数据,约为2000万。
  • 四层B+树可以存储1170 x 1170 x 1170 x 16 = 25625808000条数据,约为256亿。
  • 如不考虑磁盘IO,B+树的查找与其层数(树的高度)和阶数(节点的最大分支数)有关,因为数据都在叶子节点,所以查找数据必须从树的根节点开始,通过2分法定位其所在的分支,一层一层的查找(每层都是2分法定位其所在的分支),直到最后一层的叶子节点定位数据。
  • 在查找不超过3层的B+Tree中的数据时,一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。
  • 一般不建议单表的数据量超过2000万,因为每查找一个页,都要进行一次IO,而磁盘的速度相比内存而言是非常的慢的,比如常见的7200RPM的硬盘,摇臂转一圈需要60/7200≈8.33ms,换句话说,让磁盘完整的旋转一圈找到所需要的数据需要8.33ms,这比内存常见的100ns慢100000倍左右,这还不包括移动摇臂的时间。所以在这里制约查找速度的不是比较次数,而是IO操作的次数。

B+Tree查找的时间复杂度(比较次数)计算方法

  • B+Tree的查找时间复杂度的计算方法是每层通过2分法查找的次数M * 树的高度H

  • 假设B+Tree中总的数据量为N,阶数为R,则 M = log2R,H = logRN,则M * H = log2R * logRN = log2N

  • 举例,比如有一课B+Tree的总的数据量是65536,最大分支为16,则N=65536,R=16,M = log2R = log216 = 4,H = logRN = log1665536 = 4,则 M * H = log2N = log265536 = 16,即最多查找16次就可以找到对应的数据

  • 注意这里仅是计算内存中查找的比较次数,而没有考虑每次加载数据页到内存的IO成本,而实际上IO成本才是制约mysql查找快慢的关键因素,所以mysql每次IO都会将查询页附近的几个页一并加载到内存,以此减少IO次数

索引分类

  • 聚集索引/聚簇索引/密集索引
    InnoDB中使用了聚集索引,就是将表的主键用来构造一棵B+树,并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据,数据即索引。
    由于聚集索引是利用表的主键构建的,所以每张表只能拥有一个聚集索引。
    聚集索引的叶子节点就是数据页。换句话说,数据页上存放的是完整的每行记录。
    因此聚集索引的一个优点就是:通过聚集索引能获取完整的整行数据。
    另一个优点是:对于主键的排序查找和范围查找速度非常快。
    如果我们没有定义主键呢?MySQL会使用唯一性索引,没有唯一性索引,MySQL也会创建一个隐含列RowID来做主键,然后用这个主键来建立聚集索引。

  • 辅助索引/二级索引/非聚集索引/稀疏索引
    上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用,因为B+树中的数据都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办?
    我们一般会建立多个索引,这些索引被称为辅助索引/二级索引,辅助索引也是一颗B+树。
    对于辅助索引(Secondary Index,也称二级索引、非聚集索引),叶子节点并不包含行记录的全部数据。
    叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了相应行数据的聚集索引主键,用于回表查询。
    辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引,有几个辅助索引就会创建几颗B+树。
    MyISAM存储引擎,不管是主键索引,唯一键索引还是普通索引都是非聚集索引。

当通过辅助索引来寻找数据时,InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引(聚集索引)来找到一个完整的行记录。这个过程也被称为回表。
也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树–一次辅助索引,一次聚集索引。

  • 联合索引/复合索引
    前面我们对索引的描述,隐含了一个条件,那就是构建索引的字段只有一个,但实践工作中构建索引的完全可以是多个字段。
    所以,将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引,比如index(a,b)就是将a,b两个列组合起来构成一个索引。
    联合索引只会建立1棵B+树。

  • 自适应哈希索引(Adaptive Hash Index,AHI)
    由mysql自己维护,对于经常被访问的索引,mysql会创建一个hash索引,下次查询这个索引时直接定位到记录的地址,而不需要去B+树中查询。
    AHI默认开启,由innodb_adaptive_hash_index变量控制,默认8个分区,最大设置为512。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON |
+----------------------------+-------+
mysql> show variables like 'innodb_adaptive_hash_index_parts';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index_parts | 8 |
+----------------------------------+-------+

通过show engine innodb status\G命令可以查看AHI的使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show engine innodb status\G

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 4 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 1 buffer(s)
Hash table size 276707, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
  • 全文检索之倒排索引(FULLTEXT)
    数据类型为 char、varchar、text 及其系列才可以建全文索引。
    每张表只能有一个全文检索的索引
    不支持没有单词界定符(delimiter)的语言,如中文、日语、韩语等。
    由于mysql的全文索引功能很弱,这里不做详细介绍,推荐使用ES等专业的搜索引擎。

MySQL有哪些索引类型?

  • 从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引(现在MyISAM和InnoDB 引擎都支持了)和R-Tree索引(用于对GIS数据类型创建SPATIAL索引);
  • 从物理存储角度可分为聚集索引(clustered index)、非聚集索引(non-clustered index);
  • 从逻辑角度可分为主键索引、普通索引,或者单列索引、多列索引、唯一索引、非唯一索引等等。

覆盖索引/索引覆盖

  • InnoDB存储引擎支持覆盖索引(covering index,或称索引覆盖),即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。

  • 使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作。

  • 覆盖索引可以视为索引优化的一种方式,而并不是索引类型的一种。

  • 除了覆盖索引这个概念外,在索引优化的范围内,还有前缀索引、三星索引等。

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
> show create table employees\G
***************************[ 1. row ]***************************
Table | employees
Create Table | CREATE TABLE `employees` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='员工记录表'

> EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+------+-----------------------+--------+---------+--------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+--------+---------+--------+-------+----------+-------------+
| 1 | SIMPLE | employees | <null> | ALL | idx_name_age_position | <null> | <null> | <null> | 92796 | 0.5 | Using where |
+----+-------------+-----------+------------+------+-----------------------+--------+---------+--------+-------+----------+-------------+

> EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| 1 | SIMPLE | employees | <null> | range | idx_name_age_position | idx_name_age_position | 74 | <null> | 46398 | 1.0 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+

前缀索引

如果索引的字段类型很长,如varchar(255),此时创建的索引就会非常大,而且维护起来也非常慢,此时建议使用前缀索引,就是只对该字段的前面一些字符进行索引。
阿里的Java编程规范中也提到,在varchar上建立索引时,必须指定索引长度,没必要对全字段建立索引,建议索引的长度不超过20。
可以使用select count(distinct left(列名, 索引长度))/count(*) from tableName的区分度来确定,一般大于90%即可。

三星索引

  • 一星(缩小查询范围): 索引将相关的记录放到一起则获得一星,即索引的扫描范围越小越好;

  • 二星(排序): 如果索引中的数据顺序和查找中的排列顺序一致则获得二星,即当查询需要排序,group by、 order by,查询所需的顺序与索引是一致的(索引本身是有序的);

  • 三星(覆盖索引): 如果索引中的列包含了查询中需要的全部列则获得三星,即索引中所包含了这个查询所需的所有列(包括 where 子句 和 select 子句中所需的列,也就是覆盖索引)。

注意

  • 一个索引就是一个B+树,索引让我们的查询可以快速定位和扫描到我们需要的数据记录上,加快查询的速度。

  • 一个select查询语句在执行过程中一般最多能使用一个二级索引来加快查询,即使在where条件中用了多个二级索引。

索引的代价

  • 空间上的代价
    这个是显而易见的,每建立一个索引都要为它建立一棵B+树,每一棵B+树的每一个节点都是一个数据页,一个页默认会占用16KB的存储空间,一棵很大的B+树由许多数据页组成会占据很多的存储空间。

  • 时间上的代价
    每次对表中的数据进行增、删、改操作时,都需要去修改各个B+树索引。B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。
    不论是叶子节点中的记录,还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。
    而增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行一些记录移位,页面分裂、页面回收的操作来维护好节点和记录的排序。
    如果我们建了许多索引,每个索引对应的B+树都要进行相关的维护操作,这必然会对性能造成影响。

所以,索引虽然可以加快我们的查询效率,但也不是创建的越多越好,一般来说,一张表不要超过7个索引为宜。

索引的创建与删除

  • 查看索引

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
mysql> desc actor;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(45) | YES | | NULL | |
| update_time | datetime | YES | MUL | NULL | |
+-------------+-------------+------+-----+---------+-------+

mysql> show index from actor;
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor | 0 | PRIMARY | 1 | id | A | 2 | <null> | <null> | | BTREE | | | YES | <null> |
| actor | 0 | unique_name_time_order | 1 | name | D | 3 | 15 | <null> | YES | BTREE | | | YES | <null> |
| actor | 0 | unique_name_time_order | 2 | update_time | A | 3 | <null> | <null> | YES | BTREE | | | YES | <null> |
| actor | 1 | index_update_time | 1 | update_time | A | 1 | <null> | <null> | YES | BTREE | | | YES | <null> |
| actor | 1 | index_name | 1 | name | A | 3 | 15 | <null> | YES | BTREE | | | YES | <null> |
| actor | 1 | index_name_desc | 1 | name | D | 3 | 15 | <null> | YES | BTREE | | | YES | <null> |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
参数说明:
Table: 表名称
Non_unique: 是否为唯一索引,0表示唯一索引,1表示非唯一索引
Key_name: 索引名称
Seq_in_index: 联合索引中的字段顺序,从1开始计算
Column_name: 字段名称
Collation: 表示索引是升序还是降序,默认创建索引是升序A,降序为D
  • 已有表索引维护

创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 方式1
create index index_name on actor (name(15));
# 创建降序索引,默认升序
create index index_name_desc on actor (name(15) desc);
create unique index unique_name on actor (name(15));
create unique index unique_name_time on actor (name(15),update_time);
create unique index unique_name_time_order on actor (name(15) desc,update_time asc);

# 方式2
alter table actor add index index_name(name(15));
alter table actor add index index_name(name(15) desc);
alter table actor add unique index unique_name(name(15));
alter table actor add unique index unique_name_time(name(15),update_time);
alter table actor add unique index unique_name_time_order(name(15) desc,update_time asc);

删除索引

1
2
3
4
5
6
7
8
9
10
11
# 方式1
drop index index_name on actor;
drop index unique_name on actor;
drop index unique_name_time on actor;

# 方式2
alter table actor drop index index_name;
alter table actor drop index unique_name;
alter table actor drop index unique_name_time;
# 同时删除多个索引
alter table actor drop index index1,drop index index2,drop index index3;

函数索引

  • mysql8.0.13及以后的版本开始支持函数式索引,即创建索引的时候可以使用mysql提供的函数(不支持自定义函数)

1
2
3
4
5
6
7
8
9
10
# 注意,创建函数索引时,要在外层有一对括号,表示表达式
alter table actor add index index1((upper(name)));
# 前缀
alter table actor add index index1((upper(left(name,15))));
# 排序
alter table actor add index index2((upper(name)) desc);
# 联合索引,函数索引+普通索引
alter table actor add index index3((upper(name)) desc,update_time asc);
# 联合索引,函数索引+函数索引
alter table actor add index index4((upper(name)) desc,(year(update_time)) asc);

注意查询时也要使用函数才能使用索引

1
2
3
4
5
6
explain select * from actor where upper(name) = 'A';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+--------+
| 1 | SIMPLE | actor | <null> | ref | index3 | index3 | 138 | const | 1 | 100.0 | <null> |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+--------+

函数索引的限制条件

  • 函数索引实际上是作为一个隐藏的虚拟列实现的,因此其很多限制与虚拟列相同,如下:
  • 函数索引的字段数量受到表的字段总数限制
  • 函数索引能够使用的函数与虚拟列上能够使用的函数相同
  • 子查询,参数,变量,存储过程,用户定义的函数不允许在函数索引上使用
  • 虚拟列本身不需要存储,函数索引和其他索引一样需要占用存储空间
  • 函数索引可以使用 UNIQUE 标识,但是主键不能使用函数索引,主键要求被存储,但是函数索引由于其使用的虚拟列不能被存储,因此主键不能使用函数索引
  • 如果表中没有主键,那么 InnoDB 将会使其非空的唯一索引作为主键,因此该唯一索引不能定义为函数索引
  • 函数索引不允许在外键中使用
  • 空间索引和全文索引不能定义为函数索引
  • 对于非函数的索引,如果创建相同的索引,将会有一个告警信息,而函数索引则不会
  • 如果一个字段被用于函数索引,那么删除该字段前,需要先删除该函数索引,否则删除该字段会报错
  • 函数索引实际上就是mysql帮我们在表上创建了一个隐藏的虚拟列,我们也可以通过自建虚拟列,然后在该虚拟列上创建普通索引来实现相同的效果

1
2
3
4
5
6
7
8
9
10
11
12
ALTER TABLE actor ADD COLUMN upper_name varchar(15) GENERATED ALWAYS AS ((upper(left(name,15)))) VIRTUAL;
alter table actor add index virtual_upper(upper_name desc);
explain select * from actor where upper_name = 'A';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+--------+
| 1 | SIMPLE | actor | <null> | ref | virtual_upper | virtual_upper | 48 | const | 1 | 100.0 | <null> |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+--------+

# 删除虚拟列前要先删除其对应的索引
ALTER TABLE actor DROP INDEX virtual_upper;
ALTER TABLE actor DROP COLUMN upper_name;

索引条件下推

什么是索引条件下推,这里举例说明:
SELECT * FROM s1 WHERE order_no > 'z' AND order_no LIKE '%a';
其中的order_no > 'z'可以使用到索引,但是 order_no LIKE '%a'却无法使用到索引

  • 在MySQL5.6之前的版本中,是按照下边步骤来执行这个查询的:
    1、先根据 order_no> 'z'这个条件,从二级索引 idx_order_no 中获取到对应的二级索引记录。
    2、根据上一步骤得到的二级索引记录中的主键值进行回表(因为是 select *),找到完整的用户记录再检测该记录是否符合 key1 LIKE '%a'这个条件,将符合条件的记录加入到最后的结果集。

  • MySQL5.6之后的版本开始支持索引下推,其执行步骤如下:
    1、先根据 order_no> 'z'这个条件,定位到二级索引 idx_order_no 中对应的二级索引记录。
    2、对于指定的二级索引记录,先不着急回表,而是先检测一下该记录是否满足 order_no LIKE '%a'这个条件,如果这个条件不满足,则该二级索引记录压根儿就没必要回表。
    3、对于满足 order_no LIKE '%a'这个条件的二级索引记录执行回表操作。

  • 回表操作其实是一个随机 IO,比较耗时,所以上述修改可以省去很多回表操作的成本。这个改进称之为索引条件下推(英文名:ICP ,Index Condition Pushdown)。

  • 如果在查询语句的执行过程中将要使用索引条件下推这个特性,在执行计划的 Extra 列中将会显示Using index condition

索引合并

过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引,但存在有特殊情况下也可能在一个查询中使用到多个二级索引,MySQL中这种使用到多个索引来完成一次查询的执行方法称之为:索引合并/index merge。

  • 索引合并算法有如下三种:

    • 1.Intersection合并: 将从多个二级索引中查询到的结果取交集,某些特定的情况下才可能会使用到Intersection索引合并

      • 情况一:等值匹配
      • 情况二:主键列可以是范围匹配
    • 2.Union合并: 使用不同索引的搜索条件之间使用OR连接起来的情况,某些特定的情况下才可能会使用到Union索引合并

      • 情况一:等值匹配
      • 情况二:主键列可以是范围匹配
      • 情况三:使用Intersection索引合并的搜索条件

      就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集,比方说这个查询: SELECT * FROM order_exp WHERE insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’ OR (order_no = ‘a’ AND expire_time = ‘b’);

    • 3.Sort-Union合并: 先按照二级索引记录的主键值进行排序,之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并,很显然,这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程

索引设计原则

  • 代码先行,索引后上

    • 一般应该等到主体业务功能开发完毕,把涉及到该表相关sql都要拿出来分析之后再建立索引
  • 联合索引尽量覆盖条件

    • 比如可以设计一个或者两三个联合索引(尽量少建单值索引),让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段,还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则
  • 不要在小基数字段上建立索引

    • 比如性别字段,其值不是男就是女,那么该字段的基数就是2,对这种小基数字段建立索引的话,还不如全表扫描
  • 长字符串可以采用前缀索引

    • 但是要注意,order bygroup by时没办法使用前缀索引
  • whereorder by冲突时优先where

    • where可以缩小查询范围,会使排序的成本会小很多
  • 基于慢sql查询做优化

    • 线上系统一定要开启慢sql,然后定期对慢sql就行索引优化

ONLY_FULL_GROUP_BY

  • MySql5.7.5及以上版本将sql_modeONLY_FULL_GROUP_BY模式默认设置为打开状态,会导致一些错误

  • 当使用GROUP BY查询时,出现在SELECT字段后面的只能是GROUP BY后面的分组字段,或使用聚合函数包裹着的字段,否则会报错如下信息:

1
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  • 当使用ORDER BY查询时,不能使用SELECT DISTINCT去重查询。否则会报错如下信息:

1
Expression #1 of ORDER BY clause is not in SELECT list, references column 'database.table.column' which is not in SELECT list; this is incompatible with DISTINCT

禁用ONLY_FULL_GROUP_BY

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
# 查询
mysql> select version(), @@sql_mode;
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| version() | @@sql_mode |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| 8.0.30 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------+-----------------------------------------------------------------------------------------------------------------------+

# name 不在group by 中,则报错
mysql> select update_time,name,count(*) from actor group by update_time;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.actor.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

# name 在group by中时,查询正常
mysql> select update_time,name,count(*) from actor group by update_time,name;
+---------------------+------+----------+
| update_time | name | count(*) |
+---------------------+------+----------+
| 2017-12-22 15:27:18 | a | 1 |
| 2017-12-22 15:27:18 | b | 1 |
| 2017-12-22 15:27:18 | c | 1 |
+---------------------+------+----------+

# DISTINCTORDER BY 查询时报错
mysql> select DISTINCT(t.name) FROM tbl_test t where t.id = 28 ORDER BY t.create_time desc LIMIT 6;
(3065, "Expression #1 of ORDER BY clause is not in SELECT list, references column 'testdb.t.create_time' which is not in SELECT list; this is incompatible with DISTINCT")

# 禁用ONLY_FULL_GROUP_BY
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

# 再次查询,结果正常,注意这里name只会保留分组结果中第一条记录的值
mysql> select update_time,name,count(*) from actor group by update_time;
+---------------------+------+----------+
| update_time | name | count(*) |
+---------------------+------+----------+
| 2017-12-22 15:27:18 | a | 3 |
+---------------------+------+----------+

# 永久禁用
my.cnf中[mysqld]中添加
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

以下为sql_mode常用值的含义

1
2
3
4
5
6
7
8
9
10
ONLY_FULL_GROUP_BY:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO:该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
STRICT_TRANS_TABLES:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
NO_ZERO_IN_DATE:在严格模式下,不允许日期和月份为零
NO_ZERO_DATE:设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
PIPES_AS_CONCAT:将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

也可是使用 ANY_VALUE()函数 来解决查询属性没有出现在group by中的情况,此时即使没有禁用ONLY_FULL_GROUP_BY也不会报错。

1
2
3
4
5
6
select update_time,any_value(name),count(*) from actor group by update_time;
+---------------------+-----------------+----------+
| update_time | any_value(name) | count(*) |
+---------------------+-----------------+----------+
| 2017-12-22 15:27:18 | a | 3 |
+---------------------+-----------------+----------+

用户管理

  • 创建用户并授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 创建用户并初始密码
mysql> CREATE USER 'username'@'%' IDENTIFIED BY '123456';
# 修改密码
mysql> ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
# 授权
mysql> GRANT all privileges ON *.* TO 'username'@'%';
# 刷新数据库
mysql> FLUSH PRIVILEGES;
# 查询用户权限
mysql> show grants for username@"%"\G
*************************** 1. row ***************************
Grants for username@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `username`@`%`
*************************** 2. row ***************************
Grants for username@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `username`@`%`

# 查询当前系统用户
mysql> select user,host from mysql.user;

# 允许远程访问
mysql> update mysql.user set host='%' where user='username';
mysql> FLUSH PRIVILEGES;
  • 删除用户

1
2
3
4
5
# 方式1
mysql> delete from user where user='username' and host='%';
mysql> FLUSH PRIVILEGES;
# 方式2
mysql> drop user 'username'@'%';
  • 授予root用户system_user权限,否则在做一些授权操作时会提示没有权限

1
2
3
mysql> grant system_user on *.* to 'root'@'localhost';
mysql> flush privileges;

  • 查看当前登录的用户

1
2
3
4
5
6
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+

开启管理员独立端口登录:33062

  • 从 MySQL 8.0.14 开始,MySQL 服务器允许专门为管理连接配置 TCP/IP 端口。这为用于普通连接的网络接口上允许的单个管理连接提供了一种替代方法,即使已经建立了 max_connections 连接,也就是说即使max_connections已经达到最大值,该端口依旧可以登录。

  • 只有在启动时设置了 admin_address 系统变量以指示管理接口的 IP 地址时,该接口才可用。如果未指定 admin_address 值,则服务器不维护管理界面。

  • 只有 SERVICE_CONNECTION_ADMIN 权限的用户才允许连接。没有限制管理连接的数量。

1
2
3
4
5
6
vim /etc/my.cnf
[mysqld]
# 开启管理员使用33062端口访问数据库的权限
# 当最大连接数达到最大值时,管理员依旧可以使用该端口登录数据库
# mysql -uroot -P33062 -p 这里表示在mysql服务器上可以登录,如果要在其它机器上访问,请设置对应的ip地址,注意这里不能设置为'%'
admin_address='localhost'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show variables like 'admin%';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| admin_address | localhost |
| admin_port | 33062 |
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| admin_tls_ciphersuites | |
| admin_tls_version | TLSv1.2,TLSv1.3 |
+------------------------+-----------------+
1
2
3
4
netstat -tunpl | grep 3306
tcp 0 0 127.0.0.1:33062 0.0.0.0:* LISTEN 12037/mysqld
tcp6 0 0 :::33060 :::* LISTEN 12037/mysqld
tcp6 0 0 :::3306 :::* LISTEN 12037/mysqld

查看线程状态

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
# 会显示用户,客户端ip,访问的数据库,执行的命令及其状态等信息
mysql> show processlist;
+-----+-----------------+--------------------+----------+---------+--------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+--------------------+----------+---------+--------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 628889 | Waiting on empty queue | NULL |
| 438 | root | localhost:56114 | mysql | Query | 0 | init | show processlist |
| 439 | root | 1.119.161.30:53840 | novel_db | Sleep | 5 | | NULL |
| 440 | root | 1.119.161.30:53849 | NULL | Sleep | 5 | | NULL |
+-----+-----------------+--------------------+----------+---------+--------+------------------------+------------------+
# 或者,两者等价
mysql> select * from information_schema.processlist;

# 查看每个客户端连接数
mysql> select id,client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip,id from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;
+-----+--------------+------------+
| id | client_ip | client_num |
+-----+--------------+------------+
| 440 | 1.119.161.30 | 2 |
| 5 | localhost | 2 |
+-----+--------------+------------+

# 找出所有执行时间超过 5 分钟的线程,拼凑出 kill 语句,方便后面查杀
mysql> select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
+--------------------------+
| concat('kill ', id, ';') |
+--------------------------+
| kill 5; |
+--------------------------+

# 关闭连接,注意kill只能关闭当前正在执行DML的操作,DDL不能关闭
mysql> kill 440;

State说明
通过show processlist;查看线程状态非常有用,这可以让我们很快地了解当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进行优化。
在一个繁忙的服务器上,可能会看到大量的不正常的状态,例如statistics正占用大量的时间。这通常表示,某个地方有异常了。
线程常见的状态有很多,比如:

  • statistics: 服务器正在计算统计信息以研究一个查询执行计划。如果线程长时间处于此状态,则服 务器可能是磁盘绑定执行其他工作。
  • Creating tmp table: 该线程正在内存或磁盘上创建临时表。如果表在内存中创建但稍后转换为磁盘表,则该操作期间的状态将为 Copying to tmp table on disk
  • Sending data: 线程正在读取和处理SELECT语句的行,并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问(读取),因此它通常是给定查询生命周期中运行时间最长的状态。
  • 其它状态参考:https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

常用操作

1
2
3
4
5
6
7
8
9
mysql> show databases; 显示所有数据库
mysql> use dbname; 打开dbname数据库
mysql> show tables; 显示数据库dbname中所有的表;
mysql> describe tablename; 显示表mysql数据库中tablename表的列信息,也可以简写为 desc tablename;
mysql> desc tablename; 同上
mysql> select version(); 查看数据库版本号
mysql> select database(); 查看当前选中的数据库
mysql> select now(); 查看数据库当前时间
mysql> select user(); 查看当前登录用户

表信息相关

查看建表语句(包括之后对表的修改)

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show create table actor\G
*************************** 1. row ***************************
Table: actor
Create Table: CREATE TABLE `actor` (
`id` int NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

# 只查看字段信息
mysql> desc actor;

查看表信息

方式1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from information_schema.TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='tbl_test_info'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test_db
TABLE_NAME: tbl_test_info
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 199
AVG_ROW_LENGTH: 7986
DATA_LENGTH: 1589248
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 49152
DATA_FREE: 4194304
AUTO_INCREMENT: 394
CREATE_TIME: 2022-09-08 03:52:27
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_bin
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT: 测试表

方式2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> use test_db;
mysql> show table status like 'tbl_test_info'\G
*************************** 1. row ***************************
Name: tbl_test_info
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 199
Avg_row_length: 7986
Data_length: 1589248
Max_data_length: 0
Index_length: 49152
Data_free: 4194304
Auto_increment: 394
Create_time: 2022-09-08 03:52:27
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_bin
Checksum: NULL
Create_options:
Comment: 测试表

查看表字段信息

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
# 方式1
mysql> show full columns from test_db.tbl_test_info;
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+
| id | int | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | 主键 |
| source_id | int | NULL | NO | MUL | NULL | | select,insert,update,references | 来源id |
| source | int | NULL | NO | | NULL | | select,insert,update,references | 来源,1:百度 |
| name | varchar(100) | utf8mb4_bin | NO | | NULL | | select,insert,update,references | 分类 |
| name_cn | varchar(100) | utf8mb4_bin | NO | | NULL | | select,insert,update,references | 分类-中文 |
| age | varchar(100) | utf8mb4_bin | NO | | NULL | | select,insert,update,references | 适合的年龄段 |
| bookNum | int | NULL | YES | | 0 | | select,insert,update,references | 该分类下小说的数量 |
| create_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | select,insert,update,references | 创建时间 |
| update_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | 更新时间 |
+-------------+--------------+-------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+-----------------------------+

# 方式2
mysql> select COLUMN_NAME 列名, COLUMN_TYPE 数据类型,DATA_TYPE 字段类型,CHARACTER_MAXIMUM_LENGTH 长度,IS_NULLABLE 是否为空,COLUMN_DEFAULT 默认值,COLUMN_COMMENT 备注 ,column_key 约束 from information_schema.columns where table_schema='test_db' and table_name='tbl_test_info';
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+
| 列名 | 数据类型 | 字段类型 | 长度 | 是否为空 | 默认值 | 备注 | 约束 |
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+
| id | int | int | NULL | NO | NULL | 主键 | PRI |
| source_id | int | int | NULL | NO | NULL | 来源id | MUL |
| source | int | int | NULL | NO | NULL | 来源,1:百度 | |
| name | varchar(100) | varchar | 100 | NO | NULL | 分类 | |
| name_cn | varchar(100) | varchar | 100 | NO | NULL | 分类-中文 | |
| age | varchar(100) | varchar | 100 | NO | NULL | 适合的年龄段 | |
| bookNum | int | int | NULL | YES | 0 | 该分类下小说的数量 | |
| create_time | timestamp | timestamp | NULL | NO | CURRENT_TIMESTAMP | 创建时间 | |
| update_time | timestamp | timestamp | NULL | NO | CURRENT_TIMESTAMP | 更新时间 | |
+-------------+--------------+--------------+--------+--------------+-------------------+-----------------------------+--------+

基于其它表创建新的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 只创建表结构,完整表结构
mysql> create table table_new like table_old;
# 将老表数据导入新表,要求新表和老表的表结构必须一模一样
mysql> insert into table_new select * from table_old;
# 将老表数据导入新表,自己关联新表和老表的字段
mysql> insert into table_new(字段1,字段2,…….) select 字段1,字段2,……. from table_old;
# 清空表数据
mysql> truncate table table_new;
# 删除表
mysql> drop table table_new;

# 创建新表的同时将老表数据导入,这种建表方式不会创建索引,不推荐使用
mysql> create table table_new select * from table_old;
# 只会创建表结构,同样不会创建索引等信息
mysql> create table table_new select * from table_old where 1=2;

系统变量

可以设置在配置文件中,也可以通过set命令进行设置

mysql配置文件中大部分变量都可以通过set命令设置,注意重启mysql后会失效,要及时更新配置文件

  • 设置变量示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 查看全部全局变量
mysql> show global variables\G

# 查看指定变量,如wait_timeout,也可以使用%进行模糊匹配
# 默认8小时,客户端超过这个时间没有向服务端发送命令,服务器端会自动断开连接
mysql> show global variables like "wait_timeout";
# 定值查询可以使用如下sql形式
mysql> select @@wait_timeout;
# 模糊匹配
mysql> show variables like '%log_bin%';

# 设置全局变量,只要mysql不重启就会一直有效,注意,客户端需要重新连接才能生效
mysql> set global wait_timeout=288000;
mysql> set @@global.wait_timeout=288000;

# 当前会话有效
mysql> set session wait_timeout=288000;
# 当前会话可简写为如下形式
mysql> set wait_timeout=288000;
  • MySQL 8.0 版本支持在线修改全局参数持久化,通过加上 PERSIST 关键字,可以将调整持久化到新的配置文件中,再次重启 db 还可以应用到最新的参数。

1
mysql> set persist max_connections=300;
  • 这种方式并不会修改mysql的配置文件,而是在datadir路径下创建一个mysqld-auto.cnf文件,在数据库启动时,会首先读取它配置文件,最后才读取mysqld-auto.cnf文件。不要手工修改这个文件。

1
2
more mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "300", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1665567597776824}}}}
  • 可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载。

1
2
3
4
5
6
mysql> show variables like 'persisted_globals_load';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| persisted_globals_load | ON |
+------------------------+-------+
  • 持久化为默认值: 全局变量持久化为默认值。注意,是默认值,而不是修改前的值

1
mysql> set persist max_connections=default;
  • 清空持久化变量: 只会清空mysqld-auto.cnf中的配置,不会改变已经持久化的变量值

1
mysql> reset persist;
1
2
more mysqld-auto.cnf
{"Version": 2}
  • 修改只读变量,对于read only的参数,修改参数后需要重启才能生效

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> set persist innodb_log_file_size=2073741824;
ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable
mysql> set persist_only innodb_log_file_size=2073741824;
ERROR 3630 (42000): Access denied; you need SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN privileges for this operation
# 先授权
mysql> GRANT SYSTEM_VARIABLES_ADMIN,PERSIST_RO_VARIABLES_ADMIN ON *.* TO 'root'@'%';
mysql> set persist_only innodb_log_file_size=2073741824;
mysql> show variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| innodb_log_file_size | 268435456 |
+----------------------+-----------+
1
2
more mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"innodb_log_file_size": {"Value": "2073741824", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1665568764002942}}}}

mysql中的数据类型对应java实体数据类型

  • 数值类型

类型 java类型 大小 范围(有符号) 范围(无符号) 用途 备注
TINYINT Boolean/int/Integer 1 字节 (-128, 127) (0, 255) 小整数值
SMALLINT int/Integer 2 字节 (-32768, 32767) (0, 65535) 大整数值
MEDIUMINT int/Integer 3 字节 (-8388608, 8388607) (0, 16777215) 大整数值
INT或 INTEGER int/Integer/Long 4 字节 (-2147483648, 2147483647) (0, 4294967295) 大整数值
BIGINT int/Integer/Long/BigInteger 8 字节 (-9233372036854775808, 9223372036854775807) (0, 18446744073709551615) 极大整数值 BigInteger最大值没有限制,内部是int[]数组,缺点是速度慢
FLOAT float/Float 4 字节 (-3.402823466E+38, -1.175494351E-38),0,(1.175494351E-38,3.402823466351E+38) 0, (1.175494351E-38, 3.402823466E+38) 单精度 浮点数值
DOUBLE double/Double 8 字节 (-1.7976931348623157E+308, -2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.7976931348623157E+308) 0, (2.2250738585072014E-308, 1.7976931348623157E+308) 双精度 浮点数值
DECIMAL BigDecimal 对DECIMAL(M,D)如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 高精度小数值 用于货币计算等高精度场景

优化建议

1
2
3
4
5
6
如果整形数据没有负数,如ID号,建议指定为UNSIGNED无符号类型,容量可以扩大一倍。
建议使用TINYINT代替ENUM、BITENUM、SET。
避免使用整数的显示宽度,也就是说,不要用INT(10)类似的方法指定字段显示宽度,直接用INT。
DECIMAL最适合保存准确度要求高,而且用于计算的数据,比如价格。但是在使用DECIMAL类型的时候,注意长度设置。
建议使用整形类型来运算和存储实数,方法是,实数乘以相应的倍数后再操作。
整数通常是最佳的数据类型,因为它速度快,并且能使用AUTO_INCREMENT。
  • 日期和时间

类型 java类型 大小 范围(有符号) 范围(无符号) 用途 备注
DATE Date/LocalDate 3 1000-01-01 到 9999-12-31 YYYY-MM-DD 日期值
TIME Date/LocalDateTime 3 ‘-838:59:59’ 到 ‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR Date 1 1901 到 2155 YYYY 年份值
DATETIME Date/LocalDateTime 8 1000-01-01 00:00:00 到 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP Date/LocalDateTime 4 1970-01-01 00:00:00 到 2038-01-19 03:14:07 YYYYMMDDhhmmss 混合日期和时间值,时间戳

优化建议

1
2
3
4
5
6
7
8
9
TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是TIME类型不仅可以用于表示一天的时间,还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 24 小时,或者甚至为负)。
MySQL能存储的最小时间粒度为秒。
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
用MySQL的内建类型DATE、TIME、DATETIME来存储时间,而不是使用字符串。
当数据格式为TIMESTAMP和DATETIME时,可以用CURRENT_TIMESTAMP作为默认(MySQL5.6以后), MySQL会自动返回记录插入的确切时间。
TIMESTAMP是UTC时间戳,与时区相关。
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数,与时区无关,你存了什么,读出来就是什么。
除非有特殊需求,一般的公司建议使用TIMESTAMP,它比DATETIME更节约空间,但是像阿里这样的公司一般 会用DATETIME,因为不用考虑TIMESTAMP将来的时间上限问题。
有时人们把Unix的时间戳保存为整数值,但是这通常没有任何好处,这种格式处理起来不太方便,我们并不推荐 它。
  • 字符串–String

类型 java类型 大小 用途
CHAR String 0-255字节 定长字符串,char(n)当插入的字符串实际长度不足n时, 插入空格进行补充保存。在进行检索时,尾部的空格会被 去掉。
VARCHAR String 0-65535字节 变长字符串,varchar(n)中的n代表最大列长度,插入的字符串实际长度不足n时不会补充空格
TINYBLOB byte[] 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT String 0-255字节 短文本字符串
BLOB byte[] 0-65535字节 二进制形式的长文本数据
TEXT String 0-65535字节 长文本数据
MEDIUMBLOB byte[] 0-16777215字节 二进制形式的中等长度文本数据
MEDIUMTEXT String 0-16777215字节 中等长度文本数据
LONGBLOB byte[] 0-4294967295字节 二进制形式的极大文本数据
LONGTEXT String 0-4294967295字节 极大文本数据

优化建议

1
2
3
4
5
6
7
8
字符串的长度相差较大用VARCHAR;字符串短,且所有值都接近一个长度用CHAR。
CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。
varchar 和 text 都可以存储变长字符串且字符串长度上限为65535字节,但是text不能设置默认值,且存储和查询速度慢、仅支持前缀索引。
当varchar大于某些数值的时候,其会自动转换为text,大于varchar(255)变为 tinytext,大于varchar(500)变为 text,大于varchar(20000)变为 mediumtext。
那些要用来计算的数字不要用VARCHAR类型保存,因为可能会导致一些与计算相关的问题。换句话说,可能影响到计算的准确性和完整性。
尽量少用BLOB和TEXT,如果实在要用可以考虑将BLOB和TEXT字段单独存一张表,用id关联。
BLOB系列存储二进制字符串,与字符集无关。TEXT系列存储非二进制字符串,与字符集相关。
BLOB和TEXT都不能有默认值。

命令行执行sql语句

执行单条sql语句 : -e

  • 查询结果保存到文件

1
mysql -uroot -p -DdbName -e "SELECT t.chapter_num as num, t.title as title FROM chapter_info t where t.book_id = 10  order by t.chapter_num asc" > result.txt
  • 执行更新或删除语句

1
2
3
# 更新和删除都没有输出结果的
mysql -uroot -p -DdbName -e "update chapter_info set title='new title' where book_id = 10"
mysql -uroot -p -DdbName -e "delete from chapter_info where book_id = 10"

执行一个sql文件

1
2
# 可以将多条sql语句写入一个sql文件,比如初始化建表脚本等,可以按sql语句的顺序一条一条的执行
mysql -uroot -p -DdbName < init.sql

数据库导出与导入

全量导出

1
2
# 包含建库、建表和数据语句
mysqldump -uroot --all-databases --triggers --routines --events -p > all_databases.sql

导出指定数据库

1
2
3
4
5
# 包含建表和数据语句
mysqldump -uroot -p DB1 > DB1.sql

# 包含建库、建表和数据语句[推荐]
mysqldump -uroot -p --databases DB1 DB2 > databases.sql

重要参数
-B, --databases Dump several databases. 备份多个数据库
-R, --routines Dump stored routines (functions and procedures). 备份函数和存储过程
-E, --events Dump events. 备份events
–triggers Dump triggers for each dumped table. 备份每个表的触发器

导出指定数据库的指定表

1
2
# 包含建表和数据语句
mysqldump -uroot -p DB1 --tables table1 table2 > tables.sql

导出指定数据库时排除某些表

1
2
# 包含建库、建表和数据语句
mysqldump -uroot -p --databases DB1 DB2 --ignore-table=DB1.table1 --ignore-table=DB2.table2 > tables.sql

导出结构不导出数据 -d

1
2
# 包含建表语句
mysqldump -uroot -p -d DB1 --tables table1 table2 > tables.sql

导出数据不导出结构 -t

1
2
# 包含数据语句
mysqldump -uroot -p -t DB1 --tables table1 table2 > tables.sql

导出sql查询结果

1
2
# 这个实际上是命令行执行sql语句的命令 -e 参数指定要执行的sql语句
mysql -uroot -p -DdbName -e "SELECT t.chapter_num as num, t.title as title FROM chapter_info t where t.book_id = 10 order by t.chapter_num asc" > result.txt

导出json格式的数据

1
2
3
4
5
6
7
8
9
# 这个实际上是命令行执行sql语句的命令 -e 参数指定要执行的sql语句,输出结果为每行一个json格式,而不是整体是json格式,要转成完整的json格式,需要每行后面加个逗号,去掉第一行标题行,以及开头和结尾加上 {[]}
mysql -uroot -p -DdbName -e "SELECT JSON_OBJECT('num', t.chapter_num, 'title', t.title, 'content',t.content) FROM chapter_info t where t.book_id = 10 order by t.chapter_num asc" > result.json
每行结尾加逗号:sed -i 's/"}/"},/' result.json
删除文件第一行:sed -i '1d' result.json
第一行上面插入{[ :sed -i '1i {[' result.json
最后一行下面插入]}:sed -i '$a ]}' result.json

一行命令搞定:sed -i -e 's/"}/"},/' -e '1d' -e '2i {[' -e '$a ]}' result.json
注意这里插入"{["时要在第二行上插入,因为是同时处理的,此时还没有完成删除第一行的操作

导入数据

1
2
3
4
5
6
mysql -uroot -p < all_databases.sql
mysql -uroot -p < databases.sql

# -D指定要导入的数据库,此时数据库必须先创建好
mysql -uroot -p -Dbdname < DB1.sql
mysql -uroot -p -Dbdname < tables.sql

导入导出CSV

导出CSV

  • 需要先开通导出权限,默认关闭

    • secure_file_prive=null 限制mysql不允许导出,默认值
    • secure_file_priv=/path/ 限制mysql的导出只能发生在默认的/path/目录下
    • secure_file_priv="" 不对mysql的导出做限制,可以导出到任意目录
    1
    2
    3
    4
    5
    6
    mysql> show variables like 'secure_file_priv';
    +------------------+-------+
    | Variable_name | Value |
    +------------------+-------+
    | secure_file_priv | NULL |
    +------------------+-------+
    • 这是一个只读变量,需要修改mysql的配置文件,这里我们设置为secure_file_priv=""
  • 命令行执行导出

1
2
3
4
5
6
7
8
9
10
11
mysqldump -uroot -p DBName tableName --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --tab=/mysqldump_dir

# 参数说明:
--fields-enclosed-by='"':每个字段以空字符结尾。
--fields-terminated-by=',':字段间以逗号分隔。
--lines-terminated-by='\r\n':以回车符为每行的结束。
--tab=/mysqldump_dir:生成文件的路径,确保有写权限
生成的文件包含两个,一个是数据文件:tableName.txt,一个是建表语句:tableName.sql

# 如果同时导出多张表,则也会分别生成相应的2个文件。
mysqldump -uroot -p DBName table1 table2 --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --tab=/mysqldump_dir
  • sql执行导出

1
2
3
4
5
# 导出全部字段
mysql> select * from tableName into outfile '/mysqldump_dir/tableName.csv' fields terminated by ',' ENCLOSED BY '"' lines terminated by '\r\n';

# 导出部分字段
mysql> select 字段1,字段2 from tableName into outfile '/mysqldump_dir/tableName.csv' fields terminated by ',' ENCLOSED BY '"' lines terminated by '\r\n';

这种方式只会导出数据文件,同时可以指定要导出的字段

导入CSV

  • 需要先开通导入权限,默认关闭

1
2
3
4
5
6
7
8
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
# 开通
mysql> set GLOBAL local_infile=1;
  • mysqlmysqld都需要配置开通,不要配置到client下,因为只有mysql命令支持该参数

1
2
3
4
5
[mysql]
local_infile=ON

[mysqld]
local_infile=ON
  • 命令行导入
    此方法要求文件名称"table1.csv"必须为表名称"table1"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysqlimport --local -uroot -p DBName -i --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' ./table1.csv

# 参数说明
-L, --local : 读取的所有文件都是基于执行命令时所在的客户端。
-i, --ignore :如果唯一索引重复则忽略后面的数据, 如果希望覆盖前面的数据,需要使用 -r 参数,两个参数不能同时出现。
--fields-enclosed-by='"':每个字段以空字符结尾。
--fields-terminated-by=',':字段间以逗号分隔。
--lines-terminated-by='\r\n':以回车符为每行的结束。

# 此时由于没有指定数据与字段对应方式,所以默认按表中字段顺序导入,如果要指定导入的字段,需要增加 --columns 参数,逗号分隔
mysqlimport --local -uroot -p DBName -i --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --columns=字段1,字段2,字段3 ./table1.csv

# 如果数据文件中的头部有title,可以指定忽略掉头部的几行,--ignore-lines=1:忽略掉第一行。
mysqlimport --local -uroot -p DBName -i --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --ignore-lines=1 ./table1.csv
  • sql执行导入
    此方法不要求文件名称与表名称一致

警告⚠️
明明配置文件中已经配置了local_infile=ON,但是通过sql执行load data local infile时还是会报错呢?
这就说明客户端运行时没有找到默认的配置文件,所以还是建议把my.cnf放到/etc目录下吧
也可以在登录时加上--local-infile

1
mysql -uroot -p --local-infile
1
2
3
4
5
6
7
8
9
10
11
12
mysql> load data local infile '~/file.csv' ignore into table table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

# 跳过表头
mysql> load data local infile '~/file.csv' ignore into table table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ignore 1 lines;

# 指定字段
mysql> load data local infile '~/file.csv' ignore into table table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ignore 1 lines (字段1,字段2,字段3);

#参数说明:
ignore : 如果唯一索引重复则忽略后面的数据, 如果希望覆盖前面的数据,需要使用 replace 参数,两个参数不能同时出现。
ignore 1 lines : 忽略开头的行数
(字段1,字段2,字段3) : 关联的字段顺序

mysql时区设置

  • 查看当前mysql时区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# SYSTEM表示使用系统时区
SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+

show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | SYSTEM |
+------------------+--------+
  • 查看系统时区

1
2
date +"%Z %z"
UTC +0000
  • 设置时区

    • 临时设置
    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
    select now()
    +---------------------+
    | now() |
    +---------------------+
    | 2022-10-26 08:38:03 |
    +---------------------+

    set time_zone='+08:00'

    SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | SYSTEM | +08:00 |
    +--------------------+---------------------+

    show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name | Value |
    +------------------+--------+
    | system_time_zone | UTC |
    | time_zone | +08:00 |
    +------------------+--------+

    select now()
    +---------------------+
    | now() |
    +---------------------+
    | 2022-10-26 16:38:29 |
    +---------------------+
    • 永久设置
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    修改配置文件 /etc/my.cnf

    [mysqld]
    # 设置时区,默认系统时区
    default-time-zone='+08:00'


    SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | +08:00 | +08:00 |
    +--------------------+---------------------+

    show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name | Value |
    +------------------+--------+
    | system_time_zone | UTC |
    | time_zone | +08:00 |
    +------------------+--------+
  • 客户端连接时也要设置时区serverTimezone=Asia/Shanghai

mysql地区(区域)设置

  • lc_time_names系统变量指示的区域设置控制用于显示日期和月份名称和缩写的语言。此变量影响DATE_FORMAT()DAYNAME()MONTHNAME()函数的输出。

  • lc_time_names不影响STR_TO_DATE()GET_FORMAT()函数。

  • lc_time_names值不影响FORMAT()的结果,默认值都是’en_US’,此函数采用可选的第三个参数设置区域,允许的区域设置值与lc_time_names系统变量的合法值相同

  • MySQL服务器区域设置支持请查看https://dev.mysql.com/doc/refman/8.0/en/locale-support.html

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
SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US |
+-----------------+

SELECT DAYNAME('2020-01-01'), MONTHNAME('2020-01-01'),DATE_FORMAT('2020-01-01','%W %a %M %b')
+-----------------------+-------------------------+-----------------------------------------+
| DAYNAME('2020-01-01') | MONTHNAME('2020-01-01') | DATE_FORMAT('2020-01-01','%W %a %M %b') |
+-----------------------+-------------------------+-----------------------------------------+
| Wednesday | January | Wednesday Wed January Jan |
+-----------------------+-------------------------+-----------------------------------------+

SET lc_time_names = 'de_DE';

SELECT DAYNAME('2020-01-01'), MONTHNAME('2020-01-01'),DATE_FORMAT('2020-01-01','%W %a %M %b')
+-----------------------+-------------------------+-----------------------------------------+
| DAYNAME('2020-01-01') | MONTHNAME('2020-01-01') | DATE_FORMAT('2020-01-01','%W %a %M %b') |
+-----------------------+-------------------------+-----------------------------------------+
| Mittwoch | Januar | Mittwoch Mi Januar Jan |
+-----------------------+-------------------------+-----------------------------------------+

# 可见lc_time_names无论设置为什么,format函数使用的local默认值都是en_US
select format(1450028.123,2),format(1450028.123,2,'en_US'),format(1450028.123,2,'de_DE');
+-----------------------+-------------------------------+-------------------------------+
| format(1450028.123,2) | format(1450028.123,2,'en_US') | format(1450028.123,2,'de_DE') |
+-----------------------+-------------------------------+-------------------------------+
| 1,450,028.12 | 1,450,028.12 | 1.450.028,12 |
+-----------------------+-------------------------------+-------------------------------+

忘记root密码后如何重置

  • 先关闭mysql服务

1
2
3
sudo systemctl stop mysqld
# 或者直接kill进程
sudo kill -9 $(ps -ef | grep mysqld | grep -v grep | awk '{print $2}')
  • 以不检查权限的方式启动MySQL,即开启无权限模式

1
sudo mysqld_safe --skip-grant-tables &
  • 登录mysql,此时不需要密码即可登录

1
mysql -u root -p
  • 重置root密码为空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 选择mysql数据库
USE mysql;

# 修改密码的方法是更新mysql.user表中的authentication_string字段。
# 请注意,您应该使用mysql_native_password作为您的插件名称。这是因为MySQL8.0更改了默认的身份验证插件。
# 这是修改密码的SQL语句,这里将root密码置为空,即清除root密码
# 为什么这里要设置为空,而不是设置一个新的密码,原因是UPDATE语句不支持mysql_native_password,
# 需要使用ALTER USER语句,但是此时是无权限模式,其不支持ALTER USER语句,所以我们先设置为空,然后关闭无权限模式后再重新设置密码。
UPDATE user SET authentication_string=null WHERE User='root';

# 刷新权限:
FLUSH PRIVILEGES;
# 退出MySQL
exit;
  • 停止以 --skip-grant-tables 模式运行的 MySQL,重新启动 MySQL 服务

1
2
3
4
5
# 关闭mysql服务,此时不需要密码
sudo mysqladmin -u root -p shutdown

# 启动mysql服务
sudo systemctl start mysqld
  • 重新登录mysql并设置root密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 此时依旧不需要密码即可登录
mysql -u root -p

# 设置root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';

# 也可以加上远程登录的密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'newpassword';

# 刷新权限:
FLUSH PRIVILEGES;
# 退出MySQL
exit;

# 此时登录就要密码了
mysql -u root -p

# 查询用户密码
select user,host,authentication_string from mysql.user;