MySQL常见问题总结

本文作者 TomorrowWu,原创文章,转载注明出处,博客地址 https://segmentfault.com/u/to... 第一时间看后续精彩文章。觉得好的话,顺手分享到朋友圈吧,感谢支持。

笔者最近在准备面试,觉得学习最好的方式就是把知道的东西通过博客写出来,一方面考察自己对某个知识点的理解,一方面督促自己查阅更多资料深入学习

我会总结出我在网上看到的面试题以及相应的答案,并且尽可能的讲原理,有错误的地方希望有大神基给予指正,读者如果有好的题目,也可以评论中提出,我将后续更新上去,谢谢

当然学习MySQL不仅仅是看一些面试题,最好还是看一些相关的书籍,比如比较好的就是<<高性能MySQL>>中文版,很详细,很厚,还未看完,还有简朝阳先生的<<MySQL性能调优与架构设计>>一书,当初泛读了一遍,等待深入研究中

MySQL平时是怎么分析效率?

1.常用的方法是explainSQL查看执行计划,根据查询计划知道是否使用了索引,以及是否进行全表扫描,以及查询的顺序等等全过程,依次我们可以建立适当的索引和连接查询调优、SQL语句拆分等

2.开启慢查询,记录执行时间长的SQL语句

SQL优化

1. 通常会在where、join on、order by等使用到的字段上加上索引
2. 避免查询时判断null,否则可能会导致全表扫描,无法使用索引;

解决方案:
在创建表时,字段尽量指定默认值,或者设置not null,不要给数据库留null

1. 避免使用or来连接查询条件,如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描,可以改用union或union all

优化案例

select id from t where num=10 or Name = 'admin'
//优化后
select id from t where num = 10 union all select id from t where Name = 'admin'

1. 避免like查询,否则可能导致全表扫描,可以考虑使用全文索引

1. 前置模糊索引 like '%abc' 势必会进行全表扫描; 2. like 'abc%'依旧有可能进行全表扫描,当部分DBMD中返回结果超过该表的80%时,就失去使用索引的意义数据库会自动改用全表扫描.(例如: where mobile like '1%')

1. 应尽量避免在 where 子句中使用 != 或<>操作符，否则导致全表扫描
2. 不使用select *,只查询必须字段,避免加载无用数据
3. 能用union all的时候就不用union,union过滤重复数据要耗费更多的CPU资源
4. where子句中使用变量参数,导致全表扫描

原理:SQL只有在运行时才会解析局部变量,优化程序必须在编译时选择访问计划,但是编译时变量值还未知,因此无法作为索引选择的输入项

//全表扫描
select id from t where num = @num
//强制使用索引
select id from t with(index(索引名)) where num = @num

1. 避免在where子句中对字段进行表达式操作,导致放弃索引进行全表扫描

select id from t where num/2 = 100
//修改为
select id from t where num = 100*2

1. 避免在where子句中对字段进行函数操作,导致放弃索引进行全表扫描

select id from t where substring(name,1,3) = ’abc’
//修改后
select id from t where name like 'abc%'

1. 索引并不是越多越好,索引可以提高查询效率,但插入和修改时可能会重建索引;一个表的索引数量最好不要超过6个
2. 字段类型尽量使用数字,不要设计成字符串,会降低性能,并增加存储开销

引擎在逐个比较字符串中每一个字符,对于数字只需要比较一次

1. 避免使用游标,效率很差,如果游标操作的数据超过1万行,就应该考虑改写
2. 尽量避免大事务操作,提高系统并发能力
3. 拆分大的 DELETE 或 INSERT 语句。因为这两个操作是会锁表的，别的操作都进不来了，有时候用for循环来一个个执行这些操作。
4. 获取唯一行时使用limit 1

引擎在找到第一个记录后就停止扫描记录,而不是遍历整个表或索引

1. 优先使用enum

少量state使用enum,方便迁移数据库以及维护,使用int和varchar

1. 表字段设置为固定长度(静态)的表更快
2. 使用procedure analyse()获取建议,优化表结构
3. 使用orm
   优点:代码量少,延迟加载,多个查询批处理到事务中,操作速度比单个查询快很多

缺点:级联查询

MySQL有哪些存储引擎,区别

InnoDB,MyISAM,Archive,Blackhole,CSV,Federated,Memory,Merge,NDB集群引擎等,还有一些第三方存储引擎

引擎	存储结构	存储空间	可移植性、备份及恢复	事务支持	AUTO_INCREMENT	表锁差异	全文索引	表主键	表的具体行数	CRUD操作	外键
InnoDB	所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB	需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引	免费的方案可以是拷贝数据文件、备份binlog,或者用mysqldump,在数据量达到几十G时就相对痛苦了	支持事务,外部键等高级数据库功能;具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全型表(transaction-safe ACID compliant)	必须包含只有该字段的索引;自动增长列必须是索引;如果是组合索引也必须是组合索引的第一列	行级锁;行锁大幅度提高了多用户并发操作性能;只有在where的主键时有效,非主键的where锁全表	5.6.4以后开始支持fulltext类型的全文索引	如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值	没有保存表的总行数,如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样	适合大量insert或update;delete从性能上InnoDB更优,但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令	支持外键
MyISAM	在磁盘上存储成三个文件,第一个文件的名字以表的名字开始,扩展名指出文件类型;.frm文件存储表定义;数据文件的扩展名为.MYD(MYData);索引文件的扩展名是.MYI(MYIndex)	可被压缩,存储空间较小;支持三种存储格式:静态表(默认,注意数据末尾不能有空格,会被去掉)、动态表、压缩表	数据是以文件的形式存储,跨平台的数据转移中很方便,在备份和恢复时可单独针对某个表进行操作	不支持事务,不支持外键;强调性能,每次查询具有原子性,执行速度比InnoDB快	可以和其他字段一起建立联合索引;自动增长列必须是索引,如果是组合索引,自动增长列可以不是第一列,它可以根据前面几列进行排序后递增	表级锁;select，update，delete，insert语句都会给表自动加锁,如果加锁后,表满足insert并发的情况下,可以在表尾部插入新数据	支持fulltext类型的全文索引	允许没有任何索引和主键的表存在,索引都是保存行的地址	保存有表的总行数,select count() from table;会直接取出出该值	适合有大量select	不支持外键

存储引擎选择的基本原则

引擎	原则
采用MyISAM引擎	1. R/W > 100:1,且update相对较少; 2,并发不高 3, 表数据量小 4, 硬件资源有限
采用InnoDB引擎	1, R/W比较小,频繁update大字段 2, 表数据量超过1000万,并发高 3,安全性和可用性要求高
采用Memory引擎	1,有足够的内存 2,对数据一致性要求不高,如在线人数和session等应用3, 需要定期归档数据

为什么select * from table where field = null 不能匹配空的字段

• not null的字段不能插入null,只能插入"空值"
• 空值是不占用空间的,null其实不是空值,而是要占用空间
• null会参与字段比较,对效率有一部分影响
• 对表的索引,不会存储null值,如果索引的字段可以为null,索引的效率会下降很多

空值不一定等于空字符串

例如电话号码等字段,空值表示不知道对方的手机号码,空字符表示后来取消了这个号码,等等

分页问题的优化

简单来说,避免数据量大时扫描过多的记录
解决方案:

方案	具体过程	原理	缺点
基于id分页	带上前一页最后一条记录的id去请求下一页数据,后端在去MySQL查询时,where条件加上 id>last_id limit 10,order by id	可以少去聚簇索引中拿很多数据,只拿需要的10条	需要产品上做一些妥协,无法进行指定页的跳转,加载数据时使用更多按钮
基于offset(偏移量)分页	先去二级索引中找出满足条件的offset+limit行记录的id,然后根据id去聚簇索引中找到对应的行记录,取出offset+limit行数据,最后丢掉offset行,只保留limit行,效率很差	因为去聚簇索引中访问了太多不必要的数据	效率差

优化案例:

select * from news order by id desc limit 1000000,10
耗时7.28秒

//方案1 0.365秒
select * from news 
where id >  (select id from news order by id desc  limit 1000000, 1)
order by id desc 
limit 0,10

方案2 ,适合id连续的系统,速度极快
select * from news 
where id  between 1000000 and 1000010 
order by id desc

延迟加载

类型	如何实现	使用场景	优点	缺点
延迟加载	分页SQL拆成两句,第一句先查询符合条件的id(查询的列都在二级索引中,不用访问聚簇索引中的数据行,效率很高) 第二个sql根据id去聚簇索引拿数据	解决offset过大导致的分页性能问题	8s变50ms,避免加载多余数据,浪费内存,网络传输	sql语句被多次发送执行,对DB性能有影响

案例:

SELECT
  *
FROM
  table_A USE INDEX (index_A)
WHERE
  A = xxx
  AND B = xxx
  AND C IN (xxx)
ORDER BY
  D DESC
LIMIT
  33380, 11

KEY `index_A` (`A`,`B`,`D`,`C`)

// 延迟加载后:
1.
SELECT
  table_A.id
FROM
  table_A USE INDEX (index_A)
WHERE
  A = xxx
  AND B = xxx
  AND C IN (xxx)
ORDER BY
  D DESC
LIMIT
  33380, 11;

2.
Select ＊ from table_A where id in (ids)

如何找出应用中开销最大的查询

慢查询日志

什么是ACID(原子性,一致性,隔离性,持久性)原则

• 原子性

一个事务包含多个操作，这些操作要么全部执行，要么全都不执行。实现事务的原子性，要支持回滚操作，在某个操作失败后，回滚到事务执行之前的状态。

• 一致性

事务使得系统从一个一致的状态转换到另一个一致状态。事务的一致性决定了一个系统设计和实现的复杂度

程度	详解
强一致性	读操作可以立即读到提交的更新操作
弱一致性	提交的更新操作,不一定立即会被读操作读到
最终一致性	是弱一致性的特例。事务更新一份数据，最终一致性保证在没有其他事务更新同样的值的话，最终所有的事务都会读到之前事务更新的最新值
单调一致性	如果一个进程已经读到一个值，那么后续不会读到更早的值
会话一致性	保证客户端和服务器交互的会话过程中，读操作可以读到更新操作后的最新值

• 隔离性

并发事务之间互相影响的程度，比如一个事务会不会读取到另一个未提交的事务修改的数据

• 持久性
  事务提交后，对系统的影响是永久的

什么是N+1问题

最常见的一个性能问题

举个例子，我们数据库中有两张表，一个是Customers，一个是Orders。Orders中含有一个外键customer_id，指向了Customers的主键id,想要得到所有Customer以及其分别对应的Order

// N+1方式
SELECT * FROM Customers;
SELECT * FROM Orders WHERE Orders.customer_id = #{customer.id}

//left join
 SELECT * FROM Customers LEFT JOIN Orders on Customers.id = Orders.customer_id;

什么是CQRS(Command Query Responsibility Segregation)?他和最早的Command-QuerySeparation原则有什么区别?

前者的读写责任分离,责任是根据具体业务来的,读不仅仅是指的数据库意义上的读操作,而是根据业务需求,为复杂业务时的读操作,专门建立数据库以供直接读取去展示界面;后者读写分离是针对数据库层次的,主数据库写,从数据库读