1、如何定位慢查询?
- 介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢,压测的结果大概5秒钟)
- 我们系统中当时采用了运维工具(Skywalking),可以监测出哪个接口,最终因为是sql的问题
- 在mysql中开启了慢日志查询,我们设置的值就是2秒,一旦sql执行超过了2秒就会记录到日志中(调试阶段)
2、那这个sql语句执行很慢,如何分析?
可以采用MySql自带的分析工具 EXPLAIN
- 通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)
- 通过type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或全盘扫描
- type这条sql的连接的类型,性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
- 通过extra建议判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复
3、了解过索引吗?
- 索引(index)是帮助MySql高效获取数据的数据结构(有序)
- 提高数据检索的效率,降低数据库的IO成本(不需要全表扫描)
- 通过索引列对数据排序,降低数据排序的成本,降低了CPU的消耗
4、索引的底层数据结构了解过吗?
MySql的InnoDB引擎采用的B+树的数据结构来存储索引
- 阶数更多,路径更短
- 磁盘读写代价B+树更低,非叶子节点只存储指针,叶子节点存储数据
- B+数便于扫库和区间查询,叶子节点是一个双向链表
5、B树和B+树的区别是什么?
1)在B树中,非叶子节点和叶子节点都会存放数据,而B+树的所有的数据都会出现在叶子节点,在查询的时候,B+树查找效率更加稳定
2)在进行范围查询的时候,B+树效率更高,因为B+树都在叶子节点存储,并且叶子节点是一个双向链表
6、什么是聚簇索引?什么是非聚簇索引?
- 聚簇索引(聚集索引):数据与索引放在一块,B+树的叶子节点保存了整行数据,有且只有一个
- 非聚簇索引(二级索引):数据与索引分开存储,B+数的叶子节点保存对应的主键,可以有多个
聚集索引选取规则:
1)如果存在主键,主键索引就是聚集索引
2)如果不存在主键,将使用第一个唯一索引作为聚集索引
3)如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
7、直到什么是回表查询吗?
通过二级索引找到对应的主键值,到聚集索引中查找整行数据,这个过程就是回表
8、知道什么叫覆盖索引吗?
覆盖索引指的是 查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到
- 使用id查询,直接走聚集索引查询,一次索引扫描,直接返回数据,性能高
- 如果返回的列中没有创建索引,有可能会触发回表查询,所以应尽量避免使用select *
9、MySql超大分页怎么处理?
超大分页一般都是在数据量比较大时,我们使用了limit分页查询,并且需要对数据进行排序,这个时候效率就很低,我们可以采用覆盖索引和子查询来解决。先分页查询数据的id字段,确定了id之后,再用子查询来过滤,只查询这个id列表中的数据就可以了,因为查询id的时候,走的覆盖索引,所以效率可以提升很多
10、索引创建原则有哪些?
1)数据量较大,且查询比较频繁的表,一般单表超过10万数据的表
2)常作为查询条件,排序,分组的字段建立索引
3)尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
4)要控制索引的数量,索引越多,维护索引结构的代价就越大,会影响增删改的效率
5)尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
6)如果是字符串类型的字段,字段的长度较长,可以针对字段的特点建立前缀索引
11、什么情况下索引会失效?
①、违反最左前缀法则
如果索引了多列,要遵守最左前缀法则,指的就是查询从索引的最左前列开始,并且不跳过索引中的列。如果出现跳跃某一列,那么只有最左列索引生效;
②、范围查询右边的列,不能使用索引
如果使用范围查询的话,那么最右边的索引失效
③、不要在索引列上进行运算操作,索引将失效
④、字符串不加单引号,造成索引失效
⑤、以%开头的like模糊查询,索引会失效,以%结尾的like的模糊查询,索引不会失效
判断索引是否失效,我们可以使用explain 加在sql语句前面然后查看数据返回结果进行判断
12、sql的优化的经验 (重点)
1)表的设计优化(参考阿里开发手册)
① 比如设置合适的数值(tinyint int bigint),要根据实际情况选择
② 比如设置合适的字符串类型(char和varchar)char定长效率高,varchar可变长度,效率较低
2)sql语句优化
① 避免直接使用select * ,为了使用覆盖索引,避免回表查询
② sql要避免造成索引失效的写法
③ 尽量使用union all代替union,union会多一次过滤,效率低
④ 避免在where子句中对字段进行表达式操作
⑤ Join优化,能用innerjoin,就不用left join right join,一定要以小表为驱动
3)主从复制、读写分离
如果数据库的使用场景读的操作比较多的时候,为了避免写的操作所造成的性能影响,可以采用读写分离的架构,读写分离解决的是,数据库的写入,影响了查询的效率
13、union all和union的区别?
union 比 union all多一次过滤操作,union all会将两个查询语句的结果合并起来,并且重复的会继续显示,而union则会将两个查询结构中的重复结果过滤掉,比union all多一次过滤操作
14、事务的特性是什么?ACID
- 原子性:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性:事务完成时,必须使所有的数据都保持一致状态
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
举例转账的操作即可
15、并发事务有哪些问题?
我们在项目的开发中,多个事务并发进行是经常发生的,并发也是必然的,有可能导致一些问题:
1)脏读,当一个事务正在访问数据库并且对数据进行了修改,而这种修改还没有提交到数据库中,这时另一个事务也访问了这个数据,因为这个数据是还没有提交的数据,那么另外一个事务读到的这个数据是“脏数据”,依据这个“脏数据”所做的操作可能是不正确的。
2)不可重复读:比如在一个事务内多次读取同一数据,在这个事务还没有结束时,另一个事务也访问该数据,那么,在第一个事务中的两次读数据之间,由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样,这就发生了在一个事务内两次读到的数据是不一样的情况,因为称为不可重复读。
3)幻读:幻读和不可重复读类似,它发生在一个事务T1读取了几行数据,接着另一个并发事务T2插入了一些数据时,在随后的查询中,第一个事务T1就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。
16、怎么解决以上这些问题呢?
解决方案是对事务进行隔离
MySQL支持四种隔离级别,分别有:
- READ UNCOMMITTED 未提交读:什么都解决不了
- READ COMMITTED 读已提交:解决脏读
- REPEATABLE READ 可重复读:默认隔离级别,解决 脏读,不可重复读
- SERIALIZABLE 串行化:解决脏读,不可重复读,幻读
随着隔离级别的上升,效率会逐渐降低,我们常用的就是默认的隔离级别:可重复读
17、undo log 和redo log的区别?
- redo log:记录的是数据页的物理变化,服务宕机可用来同步数据
- undo log:记录的是逻辑日志,当事务回滚时,通过逆操作恢复原来的数据
- redo log:保证了事务的持久性,undo log 保证了事务的原子性和一致性
18、事务中的隔离性是如何保证的呢?[你解释一下MVCC?]
MySql中的多版本并发控制。指维护一个数据的多个版本,使得读写操作没有冲突
1)隐藏字段:
①.trx_id(事务id),记录每一次操作的事务id,是自增的
②.roll_pointer(回滚指针),指向上一个版本的事务版本记录地址
2)undo log:
①.回滚日志,存储老版本数据
②.版本链:多个事务并行操作某一行记录,记录不同事务修改数据的版本,通过roll_pointer指针形成一个链表
3)readView:解决的是一个事务查询选择版本的问题
- 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据
- 不同的隔离级别快照度是不一样的,最终的访问的结果不一样
- RC:每一次执行快照读时生成ReadView
- RR:仅在事务中第一次执行快照读时生成ReadView,后续复用
19、主从同步原理
MySql主从复制的核心就是二进制日志binlog文件(DDL数据定义语言,DML数据操作语言)
分三步:
1)Master主库在事务提交时,会把数据变更记录在二进制日志文件Binlog中
2)从库读取主库的二进制日志文件 Binlog,写入到从库中继日志 Relay Log中
3)slave重做中继日志中的事件,将改变反应它自己的数据。
20、项目用过分库分表吗?
1)业务介绍
- 根据自己简历上的项目,想一个数据量较大的业务(请求数多或业务累计大)
- 达到了什么样的量级(单表1000万或超过20G )
2)具体拆分策略
- 水平分库,将一个库的数据拆分到多个库中,解决海量数据存储和高并发的问题
- 水平分表,解决单表存储和性能的问题
- 垂直分库,根据业务进行拆分,高并发下提高磁盘IO和网络连接数
- 垂直分表,冷热数据分离,多表互不影响
22、MyISAM和InnoDB的区别?
MyISAM: 不支持事务和外键约束,占用空间较小,访问速度快,表级锁定,适用于不需要事务处理、单独写入或查询的应用场景。(写入和查询不一起使用的场景)
InnoDB: 支持事务处理、外键约束、占用空间比MyISAM 大,支持行级锁定,读写开发能力较好,适用于需要事务处理、读写频繁的应用场景。