脚本宝典收集整理的这篇文章主要介绍了

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,方便迁移数据库以及维护,使用intvarchar

  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.365select * 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. Selectfrom 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原则有什么区别?

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

总结

以上是脚本宝典为你收集整理的

MySQL常见问题总结

全部内容,希望文章能够帮你解决

MySQL常见问题总结

所遇到的程序开发问题,欢迎加入QQ群277859234一起讨论学习。如果觉得脚本宝典网站内容还不错,欢迎将脚本宝典网站推荐给程序员好友。 本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。

80%的人都看过