MySQL常见问题总结

摘要：实现事务的原子性，要支持回滚操作，在某个操作失败后，回滚到事务执行之前的状态。一致性事务使得系统从一个一致的状态转换到另一个一致状态。

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

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

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

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

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

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

通常会在where、join on、order by等使用到的字段上加上索引

避免查询时判断null,否则可能会导致全表扫描,无法使用索引;

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

避免使用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"

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

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

应尽量避免在 where 子句中使用 != 或<>操作符，否则导致全表扫描

不使用select *,只查询必须字段,避免加载无用数据

能用union all的时候就不用union,union过滤重复数据要耗费更多的CPU资源

where子句中使用变量参数,导致全表扫描

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

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

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

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

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

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

索引并不是越多越好,索引可以提高查询效率,但插入和修改时可能会重建索引;一个表的索引数量最好不要超过6个

字段类型尽量使用数字,不要设计成字符串,会降低性能,并增加存储开销

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

避免使用游标,效率很差,如果游标操作的数据超过1万行,就应该考虑改写

尽量避免大事务操作,提高系统并发能力

拆分大的 DELETE 或 INSERT 语句。因为这两个操作是会锁表的，别的操作都进不来了，有时候用for循环来一个个执行这些操作。

获取唯一行时使用limit 1

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

优先使用enum

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

表字段设置为固定长度(静态)的表更快

使用procedure analyse()获取建议,优化表结构

使用orm
优点:代码量少,延迟加载,多个查询批处理到事务中,操作速度比单个查询快很多

缺点:级联查询

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

not null的字段不能插入null,只能插入"空值"

空值是不占用空间的,null其实不是空值,而是要占用空间

null会参与字段比较,对效率有一部分影响

对表的索引,不会存储null值,如果索引的字段可以为null,索引的效率会下降很多

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

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

优化案例:

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

案例:

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)

慢查询日志

原子性

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

一致性

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

隔离性

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

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

最常见的一个性能问题

举个例子，我们数据库中有两张表，一个是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;

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