
公众号:微客鸟窝
2022/03/25阅读:88主题:全栈蓝
面试必备(背)--MySQL八股文系列
1. 三大范式
-
第一范式:确保每列保持原子性,数据表中的所有字段值都是不可分解的原子值。 -
第二范式:确保表中的每列都和主键相关。 -
第三范式:确保每列都和主键列直接相关而不是间接相关。
1.1 反范式化
我们应从业务角度出发,设计出符合范式准则要求的表结构。
-
反范式化指的是通过增加冗余或重复的数据来换时间增加效率,违反第二第三范式。 -
反范式化可以减少关联查询时,join表的次数。 -
在一些场景下,可以通过 JSON 数据类型进行反范式设计,提升存储效率。
2. mysql的几种引擎,有什么区别
特性 | InnoDB | MyISAM | MEMORY |
---|---|---|---|
事物安全 | 支持 | 不支持 | 不支持 |
对外建的支持 | 支持 | 不支持 | 不支持 |
存储限制 | 64TB | 有 | 有 |
空间使用 | 高 | 低 | 低 |
内存使用 | 高 | 低 | 高 |
插入数据的速度 | 低 | 高 | 高 |
2.1 InnoDB、MyISAM 对比
-
InnoDB支持事务,MyISAM不支持。 -
InnoDB 支持外键,而 MyISAM 不支持。 -
InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键。MyISAM是非聚集索引,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 -
InnoDB 不保存表的具体行数。MyISAM 用一个变量保存了整个表的行数。 -
Innodb 有 redolog 日志文件,MyISAM 没有。 -
Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI。 -
Innodb:frm是表定义文件,ibd是数据文件。 -
Myisam:frm是表定义文件,myd是数据文件,myi是索引文件。 -
InnoDB 支持表、行锁,而 MyISAM 支持表级锁。 -
InnoDB 必须有唯一索引(主键),如果没有指定的话 InnoDB 会自己生成一个隐藏列 Row_id 来充当默认主键,MyISAM 可以没有。
3. 为什么要使用自增主键
-
1.普通索引的 B+ 树上存放的是主键索引的值,如果该值较大,会「导致普通索引的存储空间较大」 -
2.使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以,直接「按照顺序插入」,不用刻意维护 -
3.页分裂容易维护,当插入数据的当前页快满时,会发生页分裂的现象,如果主键索引不为自增 id,那么数据就可能从页的中间插入,页的数据会频繁的变动,「导致页分裂维护成本较高」
4. 什么是索引?
❝百度百科的解释:索引是对数据库表的一列或者多列的值进行排序一种结构,使用索引可以快速访问数据表中的特定信息。
❞
索引就一本书的目录,可以极大的提高我们在数据库的查询效率。
4.1 索引的优缺点?
优点:
-
大大加快数据检索的速度。 -
将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的) -
加速表与表之间的连接
缺点:
-
从空间角度考虑,建立索引需要占用物理空间 -
从时间角度 考虑,创建和维护索引都需要花费时间,例如对数据进行增删改的时候都需要维护索引。
4.2 索引的数据结构?
索引的数据结构主要有 B+ 树和哈希表,对应的索引分别为 B+ 树索引和哈希索引。InnoDB 默认的索引类型为 B+ 树索引。
4.3 索引的类型有哪些?
MySQL 主要的索引类型主要有 FULLTEXT,HASH,BTREE,RTREE。
-
FULLTEXT
-
FULLTEXT 即全文索引,MyISAM存储引擎和InnoDB存储引擎在MySQL5.6.4以上版本支持全文索引,一般用于查找文本中的关键字,多在CHAR,VARCHAR,TAXT 等数据类型上创建全文索引。全文索引主要是用来解决 WHERE name LIKE "%wekenw%"
等针对文本的模糊查询效率低的问题。
-
-
HASH
-
HASH 即哈希索引,哈希索引多用于等值查询,时间复杂夫为o(1),效率非常高,但不支持排序、范围查询及模糊查询等。
-
-
BTREE
-
BTREE 即 B+ 树索引,INnoDB存储引擎默认的索引,支持排序、分组、范围查询、模糊查询等,并且性能稳定。
-
-
RTREE
-
RTREE 即空间数据索引,多用于地理数据的存储,相比于其他索引,空间数据索引的优势在于范围查找。
4.4 索引的种类有哪些?
-
主键索引:数据列不允许重复,不能为NULL,一个表只能有一个主键索引 -
组合索引:由多个列值组成的索引。 -
唯一索引:数据列不允许重复,可以为NULL,索引列的值必须唯一的,如果是组合索引,则列值的组合必须唯一。 -
全文索引:对文本的内容进行搜索。 -
普通索引:基本的索引类型,可以为NULL
4.5 什么是聚簇索引,什么是非聚簇索引?
-
聚簇索引:将数据和索引放到一起存储,索引结构的叶子节点保留了数据行。 -
非聚簇索引:将数据进和索引分开存储,索引叶子节点存储的是指向数据行的地址。
4.6 索引的设计原则?
-
最适合创建索引的列是出现在 WHERE 或 ON 子句中的列,或连接子句中的列而不是出现在SELECT关键字后的列。 -
对于字符串进行索引,应该制定一个前缀长度,可以节省大量的索引空间。 -
索引列的基数越大、索引列的区分度越高,索引的效果越好。 -
尽量使用短索引,因为较小的索引涉及到的磁盘I/O较少,并且索引高速缓存中的块可以容纳更多的键值,会使得查询速度更快。 -
尽量利用最左前缀。 -
不要过度索引,每个索引都需要额外的物理空间,维护也需要花费时间,所以索引不是越多越好。
4.7 索引失效的场景有哪些?
-
不要在索引上做任何操作(计算、函数、自动/手动类型转换),不然会导致索引失效而转向全表扫描。 -
不能继续使用索引中范围条件(bettween、<、>、in等)右边的列 。 -
索引字段上使用(!= 或者 < >)判断时,会导致索引失效而转向全表扫描。 -
索引字段上使用 is null / is not null 判断时,会导致索引失效而转向全表扫描。 -
索引字段使用like以通配符开头(‘%字符串’)时,会导致索引失效而转向全表扫描,也是最左前缀原则。 -
索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描。 -
索引字段使用 or 时,会导致索引失效而转向全表扫描。
4.8 创建索引的语法:
首先创建一个表:create table t1 (id int primary key,username varchar(20),password varchar(20));
创建单个索引的语法:CREATE INDEX 索引名 on 表名(字段名)
索引名一般是:表名_字段名
给id创建索引:CREATE INDEX t1_id on t1(id);
创建联合索引的语法:CREATE INDEX 索引名 on 表名(字段名1,字段名2)
给 username 和 password 创建联合索引:CREATE index t1_username_password ON t1(username,password)
其中index还可以替换成 unique,primary key,分别代表唯一索引和主键索引
删除索引:DROP INDEX t1_username_password ON t1
5. 数据库的事务
5.1 什么是事务?其特性是什么?
事务是指是程序中一系列操作必须全部成功完成,有一个失败则全部失败
特性:
-
1.「原子性(Atomicity)」:要么全部执行成功,要么全部不执行。 -
2.「一致性(Consistency)」:事务前后数据的完整性必须保持一致。 -
3.「隔离性(Isolation)」:隔离性是当多个事务同事触发时,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。 -
4.「持久性(Durability)」:事务完成之后的改变是永久的。
5.2 事务的隔离级别?
-
1.「读已提交」:即能够「读取到那些已经提交」的数据。 -
2.「读未提交」:即能够「读取到没有被提交」的数据。 -
3.「可重复读」:可重复读指的是在一个事务内,最开始读到的数据和事务结束前的「任意时刻读到的同一批数据都是一致的」。 -
4.「可串行化」:最高事务隔离级别,不管多少事务,都是「依次按序一个一个执行」。
5.3 隔离性实现原理:
隔离性的实现原理比较特殊,是通过数据库锁的机制实现的。
隔离性分四个级别:
-
读未提交:一个事务可以读到另外一个事务未提交的数据。脏读
实现:事务在读数据的时候并未对数据进行加锁。
事务在发生更新数据的瞬间,必须先对其加 行级共享锁,直到事务结束才释放。
举例:事务A读取某行记录时(没有加锁),事务2也能对这行记录进行读取、更新。当事务B对该记录进行更新时,事务A读取该记录,能读到事务B对该记录的修改版本,即使该修改尚未被提交。
事务A更新某行记录时,事务B不能对这行记录做更新,直到事务A结束。
-
读已提交:一个事务可以读到另外一个事务提交的数据。不可重复读
实现:事务对当前被读取的数据加
行级共享锁
(当读到时才加锁),一旦读完该行,立即释放该行级共享锁;事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加
行级排他锁
,直到事务结束才释放。原理:事务A读取某行记录时,事务B也能对这行记录进行读取、更新;当事务B对该记录进行更新时,事务A再次读取该记录,读到的只能是事务B对其更新前的版本,或者事务B提交后的版本。 事务A更新某行记录时,事务B不能对这行记录做更新,直到事务1结束。
流程描述:事务A读操作会加上
共享锁
,事务B写操作时会加上排他锁
,当事务B正在写操作时,事务A要读操作,发现有排他锁,事务A就会阻塞,等待排他锁释放(事务B写操作提交才会释放),才能进行读操作。 -
可重复读
实现:事务在读取某数据的瞬间(就是开始读取的瞬间),必须先对其加
行级共享锁
,直到事务结束才释放;事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加
行级排他锁
,直到事务结束才释放。举例:事务A读取某行记录时,事务B也能对这行记录进行读取、更新;当事务B对该记录进行更新时,事务A再次读取该记录,读到的仍然是第一次读取的那个版本。 事务A更新某行记录时,事务B不能对这行记录做更新,直到事务1结束。
-
可串行化(Serializable) 写操作串联执行
实现:事务在读取数据时,必须先对其加
表级共享锁
,直到事务结束才释放;事务在更新数据时,必须先对其加
表级排他锁
,直到事务结束才释放。举例:事务A正在读取A表中的记录时,则事务B也能读取A表,但不能对A表做更新、新增、删除,直到事务A结束。 事务A正在更新A表中的记录时,则事务B不能读取A表的任意记录,更不可能对A表做更新、新增、删除,直到事务A结束。
原理:在读操作时,加
表级共享锁
,事务结束时释放;写操作时候,加表级独占锁
,事务结束时释放。
「MySQL的默认隔离级别是可重复读。」 数据库的隔离级别分别可以解决数据库的脏读、不可重复读、幻读等问题。

-
1.「脏读」
-
脏读指的是「读到了其他事务未提交的数据」,未提交意味着这些数据可能会回滚,也就是可能最终不会存到数据库中,也就是不存在的数据。读到了并一定最终存在的数据,这就是脏读。
-
-
2.「不可重复读」
-
对比可重复读,不可重复读指的是在同一事务内,「不同的时刻读到的同一批数据可能是不一样的」。
-
-
3.「幻读」
-
幻读是针对数据插入(INSERT)操作来说的。假设事务A对某些行的内容作了更改,但是还未提交,此时事务B插入了与事务A更改前的记录相同的记录行,并且在事务A提交之前先提交了,而这时,在事务A中查询,会发现「好像刚刚的更改对于某些数据未起作用」,但其实是事务B刚插入进来的这就叫幻读。
-
5.4 隔离级别是如何实现的?
事务的隔离机制主要是依靠锁机制和MVCC(多版本并发控制)实现的,提交读和可重复读可以通过MVCC实现,串行化可以通过锁机制实现。
6. 什么是MVCC,有什么作用?
MVCC:多版本并发控制,主要用来提高数据库的并发性能。
MVCC的作用就是在不加锁的情况下,解决数据库读写冲突问题,并且解决脏读、幻读、不可重复读等问题,但是不能解决丢失修改问题。
7. 数据库的锁
7.1 什么是数据库的锁?
当数据库有并发事务的时候,保证数据访问顺序的机制称为锁机制。
数据库的锁与隔离级别的关系?

7.2 数据库锁的类型有哪些?

MyISAM 默认采用表级锁,InnoDB 默认采用行级锁。
从锁的类别上区别可以分为共享锁和排他锁
-
共享锁:共享锁又称读锁,简写为S锁,一个事务对一个数据对象加了S锁,可以对这个数据对象进行读取操作,但不能进行更新操作。并且在加锁期间其他事务只能对这个数据对象加S锁,不能加X锁。 -
排他锁:排他锁又称为写锁,简写为X锁,一个事务对一个数据对象加了X锁,可以对这个对象进行读取和更新操作,加锁期间,其他事务不能对该数据对象进行加X锁或S锁。
7.3 什么是数据库的乐观锁和悲观锁,如何实现?
乐观锁:系统假设数据的更新在大多数时候是不会产生冲突的,所以数据库只在更新操作提交的时候对数据检测冲突,如果存在冲突,则数据更新失败。
乐观锁实现方式:一般通过版本号和CAS算法实现。
悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。通俗讲就是每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁。
悲观锁的实现方式:通过数据库的锁机制实现,对查询语句添加for updata。
7.4 什么是死锁?如何避免?
死锁是指两个或者两个以上进程在执行过程中,由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。在 MySQL 中,MyISAM 是一次获得所需的全部锁,要么全部满足,要么等待,所以不会出现死锁。在 InnoDB 存储引擎中,除了单个 SQL 组成的事务外,锁都是逐步获得的,所以存在死锁问题。
如何避免MySQL发生死锁或锁冲突:
-
如果不同的程序并发存取多个表,尽量以相同的顺序访问表。 -
在程序以批量方式处理数据的时候,如果已经对数据排序,尽量保证每个线程按照固定的顺序来处理记录。 -
在事务中,如果需要更新记录,应直接申请足够级别的排他锁,而不应该先申请共享锁,更新时在申请排他锁,因为在当前用户申请排他锁时,其他事务可能已经获得了相同记录的共享锁,从而造成锁冲突或者死锁。 -
尽量使用较低的隔离级别。 -
尽量使用索引访问数据,使加锁更加准确,从而减少锁冲突的机会。 -
合理选择事务的大小,小事务发生锁冲突的概率更低。 -
尽量用相等的条件访问数据,可以避免Next-Key锁对并发插入的影响。 -
不要申请超过实际需要的锁级别,查询时尽量不要显示加锁。 -
对于一些特定的事务,可以表锁来提高处理速度或减少死锁的概率。
8. B 树和 B+ 树的区别?
-
B 树中的内部节点和叶子节点均存放键和值,而 B+ 树的内部节点只有键没有值,叶子节点存放所有的键和值。
-
B+ 树的叶子节点是通过相连在一起的,方便顺序检索。

9. 数据库为什么使用 B+ 树而不是 B 树?
-
B 树适用于随机检索,而 B+ 树适用于随机检索和顺序检索。 -
B+ 树的空间利用率更高,因为 B 树每个节点要存储键和值,而 B+ 树的内部节点只存储键,这样 B+ 树的一个节点就可以存储更多的索引,从而使树的高度变低,减少了 I/O 次数,使得数据检索速度更快。 -
B+ 树的叶子节点都是连接在一起的,所以范围查找,顺序查找更加方便。 -
B+ 树的性能更加稳定,因为在 B+ 树中,每次查询都是从根节点到叶子节点,而在 B 树中,要查询的值可能不在叶子节点,在内部节点就已经找到。
9.1 什么情况适合使用 B 树呢?
因为 B 树的内部节点也可以存储值,所以可以把一些频繁访问的值放在距离根节点比较近的地方,这样就可以提高查询效率。
10. MySQL执行SQL语句的的流程?

-
1.通过连接器跟客户端「建立连接」。 -
2.通过查询「缓存查询」之前是否有查询过该 sql。 -
有则直接返回结果 -
没有则执行第3步
-
-
3.通过分析器「分析该 sql 的语义」是否正确,包括格式,表等等。 -
4.通过优化器「优化该语句」,比如选择索引,join 表的连接顺序。 -
5.「验证权限」,验证是否有该表的查询权限。 -
没有则返回无权限的错误 -
有则执行第6步
-
-
6.通过执行器调用存储引擎执行该 sql,然后返回「执行结果」。
10.1 一条SQL查询语句在MySQL中如何执行的?
-
先检查该语句是否有权限,如果没有权限,直接返回错误信息,如果有权限会先查询缓存(MySQL8.0 版本以前)。 -
如果没有缓存,分析器进行词法分析,提取 sql 语句中 select 等关键元素,然后判断 sql 语句是否有语法错误,比如关键词是否正确等等。 -
最后优化器确定执行方案进行权限校验,如果没有权限就直接返回错误信息,如果有权限就会调用数据库引擎接口,返回执行结果。
11. binlog、undolog、relaylog、redolog?
-
binlog 是归档日志,属于 Server 层的日志,是一个二进制格式的文件,用于「记录用户对数据库更新的SQL语句信息」。
主要作用:主从复制、数据恢复。
-
undolog 是 InnoDB 存储引擎的日志,用于保证数据的原子性,「保存了事务发生之前的数据的一个版本,也就是说记录的是数据是修改之前的数据,可以用于回滚」,同时可以提供多版本并发控制下的读(MVCC)。
主要作用:事务回滚、实现多版本控制(MVCC)。
-
relaylog 是中继日志,「在主从同步的时候使用到」,它是一个中介临时的日志文件,用于存储从master节点同步过来的binlog日志内容。
-
redolog 是 「InnoDB 存储引擎所特有的一种日志」,用于记录事务操作的变化,记录的是数据修改之后的值,不管事务是否提交都会记录下来。
可以做「数据恢复并且提供 crash-safe 能力」。当有增删改相关的操作时,会先记录到 Innodb 中,并修改缓存页中的数据,「等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中」。
12. 说说两阶段提交。
两阶段提交分为 prepare 和 commit 阶段:
-
准备阶段:事物 SQL 先写入 redo log buffer,然后做一个事物准备标记,在将log buffer 中的数据刷新到 redo log。
-
提交阶段:将事物产生的 binlog 写入文件,刷新磁盘。
-
再在 redo log 中做一个事物提交的标记,并把 binlog 写成功的标记也一并写入 redo log 文件。
场景分析两阶段提交如何保证数据库的一致性。
-
准备阶段,redo log 刷新到磁盘了,但是 binlog 写磁盘前发生了 mysql实例 crash,这时会发生怎样的操作呢?
-
即使 redo log 写盘成功了,但由于 binlog 未写入成功,需要执行回滚操作来保证数据库的一致性。
-
提交阶段,binlog 写盘成功了,这时 mysql 实例 crash了。这时 binlog 已经确保写成功了,我们在重启实例进行恢复的时候,只需要让 redo log 重做一次就可以了。
13. 分库分表相关
13.1 分库分表方案:
-
水平分库:以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中。 -
水平分表:以字段为依据,按照一定策略(hash、range等),将一个表中的数据拆分到多个表中。 -
垂直分库:以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。 -
垂直分表:以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。
13.2 常用的分库分表中间件:
-
sharding-jdbc -
Mycat
13.3 分库分表可能遇到的问题
-
事务问题:需要用分布式事务。 -
跨节点Join的问题:解决这一问题可以分两次查询实现。 -
跨节点的count,order by,group by以及聚合函数问题:分别在各个节点上得到结果后在应用程序端进行合并。 -
数据迁移,容量规划,扩容等问题。 -
ID问题:数据库被切分后,不能再依赖数据库自身的主键生成机制啦,最简单可以考虑UUID。 -
跨分片的排序分页问题。
13.4 数据库如何进行垂直拆分以及水平拆分的原理是什么?
垂直拆分
-
专库专用 一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同的数据库上面,这样也就将数据或者说压力分担到不同的库上面
优点:
-
拆分后业务清晰,拆分规则明确。 -
系统之间整合或扩展容易。 -
数据维护简单。
缺点:
-
部分业务表无法join,只能通过接口方式解决,提高了系统复杂度。 -
受每种业务不同的限制存在单库性能瓶颈,不易数据扩展跟性能提高。 -
事务处理复杂。
水平拆分
垂直拆分后遇到单机瓶颈,可以使用水平拆分。相对于垂直拆分的区别是:垂直拆分是把不同的表拆到不同的数据库中,而水平拆分是把同一个表拆到不同的数据库中。
相对于垂直拆分,水平拆分不是将表的数据做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中 的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,主要有分表,分库两种模式,
优点:
-
不存在单库大数据,高并发的性能瓶颈。 -
对应用透明,应用端改造较少。 -
按照合理拆分规则拆分,join操作基本避免跨库。 -
提高了系统的稳定性跟负载能力。
缺点:
-
拆分规则难以抽象。 -
分片事务一致性难以解决。 -
数据多次扩展难度跟维护量极大。 -
跨库join性能较差。
14. Mysql 主从之间是怎么同步数据的?
1.master 主库将此次更新的事件类型「写入到主库的 binlog 文件」中。 2.master 「创建 log dump 线程通知 slave」 需要更新数据。 3.「slave」 向 master 节点发送请求,「将该 binlog 文件内容存到本地的 relaylog 中」。 4.「slave 开启 sql 线程」读取 relaylog 中的内容,「将其中的内容在本地重新执行一遍」,完成主从数据同步。

14.1 同步策略
1.「全同步复制」:主库强制同步日志到从库,等全部从库执行完才返回客户端,性能差。 2.「半同步复制」:主库收到至少一个从库确认就认为操作成功,从库写入日志成功返回ack确认。
14.2 主从延迟要怎么解决?
主从复制分了五个步骤进行:

-
主库的更新事件(update、insert、delete)被写到binlog。 -
从库发起连接,连接到主库。 -
此时主库创建一个binlog dump thread,把binlog的内容发送到从库。 -
从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log。 -
还会创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db。
主从同步延迟的原因:
一个服务器开放N个链接给客户端来连接的,这样有会有大并发的更新操作, 但是从服务器的里面读取binlog的线程仅有一个,当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致,主服务器的SQL大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟。
主从同步延迟的解决办法
1.MySQL 5.6 版本以后,提供了一种「并行复制」的方式,通过将 SQL 线程转换为多个 work 线程来进行重放。 2.「提高机器配置」增加从服务器,目的分散读的压力,从而降低服务器负载。 3.在业务初期就选择合适的分库、分表策略,「避免单表单库过大」带来额外的复制压力 4.「避免长事务」。 5.「避免让数据库进行各种大量运算」。 6.对于一些对延迟很敏感的业务「直接使用主库读」。
15. 如何优化 SQL,说说你的 Sql 调优思路吧

-
「表结构优化」 -
拆分字段 -
字段类型的选择 -
字段类型大小的限制 -
合理的增加冗余字段 -
新建字段一定要有默认值
-
-
「索引方面」 -
索引字段的选择 -
利用好mysql支持的索引下推,覆盖索引等功能 -
唯一索引和普通索引的选择
-
-
「查询语句方面」 -
避免索引失效 -
合理的书写where条件字段顺序 -
小表驱动大表 -
可以使用force index()防止优化器选错索引
-
-
「分库分表」
16. 了解慢日志查询吗?统计过慢查询吗?对慢查询如何优化?
慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。
相关参数:
slow_query_log:是否开启慢日志查询,1表示开启,0表示关闭。 slow_query_log_file:MySQL数据库慢查询日志存储路径。 long_query_time:慢查询阈值,当SQL语句查询时间大于阈值,会被记录在日志上。 log_queries_not_using_indexes:未使用索引的查询会被记录到慢查询日志中。 log_output:日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。 如何对慢查询进行优化?
分析语句的执行计划,查看SQL语句的索引是否命中 优化数据库的结构,将字段很多的表分解成多个表,或者考虑建立中间表。 优化LIMIT分页。
17. 字段为什么要设置成 not null?
首先说一点,NULL和空值是不一样的,空值是不占用空间的,而NULL是占用空间的,所以字段设为NOT NULL后仍然可以插入空值。
字段设置成not null主要有以下几点原因:
NULL值会影响一些函数的统计,如count,遇到NULL值,这条记录不会统计在内。
B树不存储NULL,所以索引用不到NULL,会造成第一点中说的统计不到的问题。
NOT IN子查询在有NULL值的情况下返回的结果都是空值。
18. varchar和char的区别?
-
varchar表示变长,char表示长度固定。 -
存储容量不同,对于 char 来说,最多能存放的字符个数为255。对于 varchar,最多能存放的字符个数是 65532。 -
存储速度不同,char 长度固定,存储速度会比 varchar 快一些,但在空间上会占用额外的空间,属于一种空间换时间的策略。而 varchar 空间利用率会高些,但存储速度慢,属于一种时间换空间的策略。
18.1 为什么 VarChar 建议不要超过255?
-
当定义varchar长度小于等于255时,长度标识位需要一个字节(utf-8编码)。 -
当大于255时,长度标识位需要两个字节,并且建立的索引也会失效。
18.2 varchar(10)和int(10)代表什么含义?
-
varchar 的10代表了申请的空间长度,也是可以存储的数据的最大长度。 -
int 的10只是代表了展示的长度,不足10位以0填充。 -
int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示。
19. drop、delete和truncate的区别?
drop | delete | truncate | |
---|---|---|---|
速度 | 快 | 逐行删除,慢 | 较快 |
类型 | DDL | DML | DDL |
回滚 | 不可回滚 | 可回滚 | 不可回滚 |
删除内容 | 删除整个表,数据行、索引都会被删除 | 表结构还在,删除表的一部分或全部数据 | 表结构还在,删除表的全部数据 |
总结:删除整个表,使用drop,删除表的部分数据使用delete,保留表结构删除表的全部数据使用truncate。
20 对慢查询如何优化?
慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。
20.1 如何查找查询速度慢的原因?
-
记录慢查询日志,分析查询日志,可以使用pt-query-digest工具进行分析。
相关参数:
-
slow_query_log:是否开启慢日志查询,1表示开启,0表示关闭。 -
slow_query_log_file:MySQL数据库慢查询日志存储路径。 -
long_query_time:慢查询阈值,当SQL语句查询时间大于阈值,会被记录在日志上。 -
log_queries_not_using_indexes:未使用索引的查询会被记录到慢查询日志中。 -
log_output:日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。
-
show profile
set profiling=1; //开启,服务器上所有执行语句会记录执行时间,存到临时表中
show profiles
show profile for query 临时表ID
-
show status
show status 会返回一些计数器,show global status 会查看所有服务器级别的所有计数。 有时根据这些计数,可以推测出哪些操作代价较高或者消耗时间多。
-
show processlist
观察是否有大量线程处于不正常的状态或特征:

-
使用 explain 分析语句
分析慢语句是否命中索引:

(1)id 列:是 select 语句的序号,MySQL将 select 查询分为简单查询和复杂查询。
(2)select_type列:表示对应行是是简单还是复杂的查询。
(3)table 列:表示 explain 的一行正在访问哪个表。
(4)type 列:最重要的列之一。表示关联类型或访问类型,即 MySQL 决定如何查找表中的行。从最优到最差分别为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
(5)possible_keys 列:显示查询可能使用哪些索引来查找。
(6)key 列:这一列显示 mysql 实际采用哪个索引来优化对该表的访问。
(7)key_len 列:显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。
(8)ref 列:这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),func,NULL,字段名。
(9)rows 列:这一列是 mysql 估计要读取并检测的行数,注意这个不是结果集里的行数。
(10)Extra 列:显示额外信息。比如有 Using index、Using where、Using temporary等。
20.2 如何对慢查询进行优化?
-
分析语句的执行计划,查看SQL语句的索引是否命中。 -
优化数据库的结构,将字段很多的表分解成多个表,或者考虑建立中间表。 -
优化LIMIT分页。

作者介绍

公众号:微客鸟窝
php&go 开发