MySql一些有用的知识点[1]
摘要
-
MySql知识点介绍:执行计划相关、索引相关、用户管理、线程状态、常用操作、表信息相关、系统变量、mysql中的数据类型对应java实体数据类型、数据库导出与导入,忘记root密码后如何重置等等
-
本文基于
mysql-8.0.30
,https://dev.mysql.com/doc/refman/8.0/en/
执行计划相关
什么是执行计划
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。
EXPLAIN
语句来帮助我们查看某个查询语句的具体执行计划,我们需要搞懂EXPLAIN
的各个输出项都是干嘛使的,从而可以有针对性的提升我们查询语句的性能。
通过使用EXPLAIN
关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析查询语句或是表结构的性能瓶颈。
通过EXPLAIN
我们可以知道:
⚫ 表的读取顺序
⚫ 数据读取操作的操作类型
⚫ 哪些索引可以使用
⚫ 哪些索引被实际使用
⚫ 表之间的引用
⚫ 每张表有多少行被优化器查询
查看执行计划
执行计划的语法其实非常简单:在SQL查询的前面加上
EXPLAIN
关键字就行
1 | mysql> explain select * from employees where id = 1 |
-
以下示例用到两张表,employees与employees2表结构相同
1 | show create table employees\G |
重点属性说明
id
id列的编号是 select 的序列号,每个SELECT关键字都对应一个唯一的 id,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的。
id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。
select_type
select_type 查询的类型,表示对应行是简单还是复杂的查询。
- 1)SIMPLE:简单查询。查询不包含子查询和union
1 | # 单表查询 |
- 2)PRIMARY:复杂查询中最外层的 select
- 3)UNION:在 union 中的第二个和随后的 select查询,不依赖于外部查询的结果集
- 4)UNION RESULT:UNION 结果集
1 | explain select * from employees union select * from employees2 |
- 5)SUBQUERY:包含在 select 中的子查询(不在 from 子句中),不依赖于外部查询的结果集
1 | explain select * from employees where id in (select id from employees2) or name = 'a' |
- 6)DEPENDENT UNION:UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
- 7)DEPENDENT SUBQUERY:子查询中的第一个 select 查询,依赖于外部查询的结果集
1 | explain select * from employees where id in (select id from employees2 where name = 'a' union select id from employees where age =20) |
- 8)DERIVED:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
1 | explain select * from (select age,count(*) countNum from employees group by age) tmp where countNum > 3 |
- 9)MATERIALIZED:物化子查询,子查询物化就是将子查询的结果缓存在内存或临时表中,
<subquery2>
表示就是物化后的表
1 | explain select * from employees where name not in (select name from employees2) |
- 10)UNCACHEABLE SUBQUERY: 结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估,出现极少。
- 11)UNCACHEABLE UNION:UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询,出现极少。
1 | explain select * from employees where id = (select id from employees2 where id= @@sql_log_bin) |
table
table列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时,table列是
当有 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 | explain select * from employees where id = 1 |
- eq_ref: 主键索引或唯一索引关联查询
1 | explain select * from employees inner join employees2 on employees.id = employees2.id |
- ref: 相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀
1 | explain select * from employees inner join employees2 on employees.name = employees2.name |
- range: 范围扫描,如 in(), between ,> ,<, >=
1 | explain select * from employees where id > 1 |
- index: 扫描全索引,一般扫描的是某个二级索引,二级索引一般比较小,所以这种通常比ALL快一些。
1 | explain select name,position from employees where age = 20 |
- ALL: 即全表扫描,扫描你的聚簇索引的所有叶子节点。
1 | explain select * from employees |
其它不常出现的也做简单说明
-
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计算规则如下:
- 字符串,char(n)和varchar(n),5.0.3以后版本中,n均代表字符数,而不是字节数,如果是utf-8,一个数字 或字母占1个字节,一个汉字占3个字节
char(n):如果存汉字长度就是 3n 字节
varchar(n):如果存汉字则长度是 3n + 2 字节,加的2字节用来存储字符串长度,因为 varchar是变长字符串 - 数值类型
tinyint:1字节
smallint:2字节
int:4字节
bigint:8字节 - 时间类型
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 | # 基于主键索引扫描的rows是46398,此时filtered=100,因为没有其它查询条件,此时表示过滤的记录占比总的扫描rows是100% |
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 | # mysql8是4k,mysql5是1k |
优化总结
1、MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index 效率高,filesort效率低。
2、order by满足两种情况会使用Using index:
- order by语句使用索引最左前列。
- 使用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 | mysql> explain select * from actor where id = 1; |
关联查询inner join
之显式连接
VS 隐式连接
,从执行计划上看两者是一回事,推荐带on的显示查询
1 | # 显式连接1 |
索引相关
B+Tree(B-Tree变种)
-
非叶子节点不存储data,只存储索引(冗余),目的是为了放更多的索引,减少树的高度,提高查询效率,非叶子结点由主键值和一个指向下一层的地址的指针组成。
-
叶子节点包含所有索引字段,聚集索引包含全部字段,非聚集索引包含索引中的字段,叶子结点中由一组键值对和一个指向该层下一页的指针组成,键值对存储的主键值和数据
-
叶节点之间通过双向链表链接,提高区间访问的性能
-
在B+树中,一个结点就是一页,MySQL中InnoDB页的大小默认是16k,Innodb的所有数据文件(后缀为 ibd 的文件),其大小始终都是 16384(16k)的整数倍。
1 | mysql> SHOW VARIABLES LIKE 'innodb_page_size'; |
计算机在存储数据的时候,最小存储单元是扇区,一个扇区的大小是 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 | mysql> show variables like 'innodb_adaptive_hash_index'; |
通过show engine innodb status\G
命令可以查看AHI的使用情况
1 | mysql> show engine innodb status\G |
-
全文检索之倒排索引(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 | > show create table employees\G |
前缀索引
如果索引的字段类型很长,如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 | mysql> desc actor; |
-
已有表索引维护
创建索引
1 | # 方式1 |
删除索引
1 | # 方式1 |
函数索引
-
mysql8.0.13及以后的版本开始支持函数式索引,即创建索引的时候可以使用mysql提供的函数(不支持自定义函数)
1 | # 注意,创建函数索引时,要在外层有一对括号,表示表达式 |
注意查询时也要使用函数才能使用索引
1 | explain select * from actor where upper(name) = 'A'; |
函数索引的限制条件
- 函数索引实际上是作为一个隐藏的虚拟列实现的,因此其很多限制与虚拟列相同,如下:
- 函数索引的字段数量受到表的字段总数限制
- 函数索引能够使用的函数与虚拟列上能够使用的函数相同
- 子查询,参数,变量,存储过程,用户定义的函数不允许在函数索引上使用
- 虚拟列本身不需要存储,函数索引和其他索引一样需要占用存储空间
- 函数索引可以使用 UNIQUE 标识,但是主键不能使用函数索引,主键要求被存储,但是函数索引由于其使用的虚拟列不能被存储,因此主键不能使用函数索引
- 如果表中没有主键,那么 InnoDB 将会使其非空的唯一索引作为主键,因此该唯一索引不能定义为函数索引
- 函数索引不允许在外键中使用
- 空间索引和全文索引不能定义为函数索引
- 对于非函数的索引,如果创建相同的索引,将会有一个告警信息,而函数索引则不会
- 如果一个字段被用于函数索引,那么删除该字段前,需要先删除该函数索引,否则删除该字段会报错
-
函数索引实际上就是mysql帮我们在表上创建了一个隐藏的虚拟列,我们也可以通过自建虚拟列,然后在该虚拟列上创建普通索引来实现相同的效果
1 | ALTER TABLE actor ADD COLUMN upper_name varchar(15) GENERATED ALWAYS AS ((upper(left(name,15)))) VIRTUAL; |
索引条件下推
什么是索引条件下推,这里举例说明:
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 by
和group by
时没办法使用前缀索引
- 但是要注意,
-
where
与order by
冲突时优先where
- where可以缩小查询范围,会使排序的成本会小很多
-
基于慢sql查询做优化
- 线上系统一定要开启慢sql,然后定期对慢sql就行索引优化
ONLY_FULL_GROUP_BY
-
MySql5.7.5及以上版本将
sql_mode
的ONLY_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 | # 查询 |
以下为sql_mode常用值的含义
1 | ONLY_FULL_GROUP_BY:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中 |
也可是使用 ANY_VALUE()函数
来解决查询属性没有出现在group by中的情况,此时即使没有禁用ONLY_FULL_GROUP_BY也不会报错。
1 | select update_time,any_value(name),count(*) from actor group by update_time; |
用户管理
-
创建用户并授权
1 | # 创建用户并初始密码 |
-
删除用户
1 | # 方式1 |
-
授予root用户
system_user
权限,否则在做一些授权操作时会提示没有权限
1 | mysql> grant system_user on *.* to 'root'@'localhost'; |
-
查看当前登录的用户
1 | mysql> SELECT USER(); |
开启管理员独立端口登录:33062
-
从 MySQL 8.0.14 开始,MySQL 服务器允许专门为管理连接配置 TCP/IP 端口。这为用于普通连接的网络接口上允许的单个管理连接提供了一种替代方法,即使已经建立了 max_connections 连接,也就是说即使max_connections已经达到最大值,该端口依旧可以登录。
-
只有在启动时设置了 admin_address 系统变量以指示管理接口的 IP 地址时,该接口才可用。如果未指定 admin_address 值,则服务器不维护管理界面。
-
只有
SERVICE_CONNECTION_ADMIN
权限的用户才允许连接。没有限制管理连接的数量。
1 | vim /etc/my.cnf |
1 | mysql> show variables like 'admin%'; |
1 | netstat -tunpl | grep 3306 |
查看线程状态
1 | # 会显示用户,客户端ip,访问的数据库,执行的命令及其状态等信息 |
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 | mysql> show databases; 显示所有数据库 |
表信息相关
查看建表语句(包括之后对表的修改)
1 | mysql> show create table actor\G |
查看表信息
方式1
1 | mysql> select * from information_schema.TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='tbl_test_info'\G |
方式2
1 | mysql> use test_db; |
查看表字段信息
1 | # 方式1 |
基于其它表创建新的表
1 | # 只创建表结构,完整表结构 |
系统变量
可以设置在配置文件中,也可以通过set命令进行设置
mysql配置文件中大部分变量都可以通过set命令设置,注意重启mysql后会失效,要及时更新配置文件
-
设置变量示例
1 | # 查看全部全局变量 |
-
MySQL 8.0 版本支持在线修改全局参数持久化,通过加上
PERSIST
关键字,可以将调整持久化到新的配置文件中,再次重启 db 还可以应用到最新的参数。
1 | mysql> set persist max_connections=300; |
-
这种方式并不会修改mysql的配置文件,而是在datadir路径下创建一个
mysqld-auto.cnf
文件,在数据库启动时,会首先读取它配置文件,最后才读取mysqld-auto.cnf文件。不要手工修改这个文件。
1 | more mysqld-auto.cnf |
-
可手动删除mysqld-auto.cnf文件或将
persisted_globals_load
变量设置为off来避免该文件的加载。
1 | mysql> show variables like 'persisted_globals_load'; |
-
持久化为默认值: 全局变量持久化为默认值。注意,是默认值,而不是修改前的值
1 | mysql> set persist max_connections=default; |
-
清空持久化变量: 只会清空mysqld-auto.cnf中的配置,不会改变已经持久化的变量值
1 | mysql> reset persist; |
1 | more mysqld-auto.cnf |
-
修改只读变量,对于read only的参数,修改参数后需要重启才能生效
1 | mysql> set persist innodb_log_file_size=2073741824; |
1 | more mysqld-auto.cnf |
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 | 如果整形数据没有负数,如ID号,建议指定为UNSIGNED无符号类型,容量可以扩大一倍。 |
-
日期和时间
类型 | 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 | TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是TIME类型不仅可以用于表示一天的时间,还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 24 小时,或者甚至为负)。 |
-
字符串–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 | 字符串的长度相差较大用VARCHAR;字符串短,且所有值都接近一个长度用CHAR。 |
命令行执行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 | # 更新和删除都没有输出结果的 |
执行一个sql文件
1 | # 可以将多条sql语句写入一个sql文件,比如初始化建表脚本等,可以按sql语句的顺序一条一条的执行 |
数据库导出与导入
全量导出
1 | # 包含建库、建表和数据语句 |
导出指定数据库
1 | # 包含建表和数据语句 |
重要参数
-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 | # 包含建表和数据语句 |
导出指定数据库时排除某些表
1 | # 包含建库、建表和数据语句 |
导出结构不导出数据 -d
1 | # 包含建表语句 |
导出数据不导出结构 -t
1 | # 包含数据语句 |
导出sql查询结果
1 | # 这个实际上是命令行执行sql语句的命令 -e 参数指定要执行的sql语句 |
导出json格式的数据
1 | # 这个实际上是命令行执行sql语句的命令 -e 参数指定要执行的sql语句,输出结果为每行一个json格式,而不是整体是json格式,要转成完整的json格式,需要每行后面加个逗号,去掉第一行标题行,以及开头和结尾加上 {[]} |
导入数据
1 | mysql -uroot -p < all_databases.sql |
导入导出CSV
导出CSV
-
需要先开通导出权限,默认关闭
secure_file_prive=null
限制mysql不允许导出,默认值secure_file_priv=/path/
限制mysql的导出只能发生在默认的/path/目录下secure_file_priv=""
不对mysql的导出做限制,可以导出到任意目录
1
2
3
4
5
6mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+- 这是一个只读变量,需要修改mysql的配置文件,这里我们设置为
secure_file_priv=""
-
命令行执行导出
1 | mysqldump -uroot -p DBName tableName --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --tab=/mysqldump_dir |
-
sql执行导出
1 | # 导出全部字段 |
这种方式只会导出数据文件,同时可以指定要导出的字段
导入CSV
-
需要先开通导入权限,默认关闭
1 | mysql> show variables like 'local_infile'; |
-
mysql
和mysqld
都需要配置开通,不要配置到client
下,因为只有mysql
命令支持该参数
1 | [mysql] |
-
命令行导入
此方法要求文件名称"table1.csv"必须为表名称"table1"
1 | mysqlimport --local -uroot -p DBName -i --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' ./table1.csv |
-
sql执行导入
此方法不要求文件名称与表名称一致
警告⚠️
明明配置文件中已经配置了local_infile=ON
,但是通过sql执行load data local infile
时还是会报错呢?
这就说明客户端运行时没有找到默认的配置文件,所以还是建议把my.cnf
放到/etc
目录下吧
也可以在登录时加上--local-infile
1 | mysql -uroot -p --local-infile |
1 | mysql> load data local infile '~/file.csv' ignore into table table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; |
mysql时区设置
-
查看当前mysql时区
1 | # SYSTEM表示使用系统时区 |
-
查看系统时区
1 | ➜ date +"%Z %z" |
-
设置时区
- 临时设置
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
30select 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 | SELECT @@lc_time_names; |
忘记root密码后如何重置
-
先关闭mysql服务
1 | sudo systemctl stop mysqld |
-
以不检查权限的方式启动MySQL,即开启无权限模式
1 | sudo mysqld_safe --skip-grant-tables & |
-
登录mysql,此时不需要密码即可登录
1 | mysql -u root -p |
-
重置root密码为空
1 | # 选择mysql数据库 |
-
停止以
--skip-grant-tables
模式运行的 MySQL,重新启动 MySQL 服务
1 | # 关闭mysql服务,此时不需要密码 |
-
重新登录mysql并设置root密码
1 | # 此时依旧不需要密码即可登录 |