MYSQL篇--sql优化高频面试题

sql优化

1 如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

其实对于性能比较低的sql语句定位，最重要的也是最有效的方法其实还是看sql的执行计划，而对于mysql来说 它其实也是提供了explain这样的命令可以便于查询sql的执行计划，并且通过执行计划 我们能够看到sql的执行情况，包括是否使用索引，使用了什么样的索引，以及使用索引的一些相关信息
对于执行计划来说 它里面有几个非常关键的字段，
比如说有key字段，这个字段就表示是否用了索引，如果没用索引，key字段就为null；
同时还有type字段 它表示使用索引的类型，索引的效果从差到好一般是全表索引，–index全索引树扫描，–》range范围查询–》ref（使用非唯一索引进行查找数据）–》eq-ref（使用主键索引或者唯一索引关联等）
possible key 可能使用到的索引
key length 索引的长度
extra信息，比如说有 using index，using where

2 大表数据的查询如何进行优化？

1 首先对于大表数据，第一个思路还是说优化sql+去使用索引
2. 使用缓存–如果说已经优化了sql，还可以通过使用缓存，将一些不会发生变化的比如配置信息，历史数据信息放到缓存redis中去
3. 其次还可以做主从复制，读写分离，将大量的查询操作通过读库完成
4. 做垂直拆分，也就是按照模块之间的耦合度将系统和数据拆分成更细粒度
5. 做水平拆分， 这一步就需要选择一个合适的sharing key，同时为了有更好的查询效率，表结构也要有改动，应用也要改动，注意sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表

3 关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

其实在业务系统的开发中 我除了使用主键进行查询以外，别的其实都是会在测试库上查看对应的耗时和执行效率
而我们系统的慢查询统计都是运维在做的，他们会通过邮件或者短信电话等方式推送和反馈给我们

针对于慢查询的sql分析，我们一般的操作其实是从三方面入手，就是明确慢查询的原因到底是什么？是没有走索引？还是load了过多不需要的数据，还是表的数据量过大导致的

而这三个方向 也有对应的处理方式
1 首先我们拿到sql会看下当前load的数据中有没有多余字段，如果说是因为load了多余的行导致的查询过慢 我们就优化sql，进行重写
2 其次看下有没有走索引，就是通过分析sql的执行计划，获取索引的使用情况，如果说没有走索引，就修改语句，尽量去命中索引
3 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

4 如何去优化WHERE子句 ？

对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

SQL语句优化的一些方法如下：
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
1 select id from t where num is null ‐‐ 可以在num上设置默认值0，确保表中num列，没有null值，然后这样查询：select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
1 select id from t where num=10 or num=20 ‐‐ 可以这样查询：select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：
1 select id from t where num in(1,2,3) ‐‐ 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：select id from t where name like ‘%
李%’若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法
作为索引选择的输入项。如下面语句将进行全表扫描：
1 select id from t where num=@num ‐‐可以改为强制查询使用索引：select id from t with (index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
1 select id from t where num/2=100 ‐‐ 应改为:select id from t where
num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
1 select id from t where substring(name,1,3)=’abc’ ‐‐ name以abc开头的id应改
为: select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

5 MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确，
index-索引 是否缺失，或者实在是数据量太大造成。
一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

6 大表怎么优化？某个表有近千万数据，CRUD比较慢，如何优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比 如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围 内。；
2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
3. 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

还有就是通过分库分表的方式进行优化，主要有垂直分表和水平分表

1. 垂直分区： 根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

垂直分表
把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

适用场景
1、如果一个表中某些列常用，另外一些列不常用
2、可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数

缺点
有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
对于应用层来说，逻辑算法增加开发成本
管理冗余列，查询所有数据需要join操作

1. 水平分区： 保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

数据库水平拆分
水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。
水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。

《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

水平分表：
表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

适用场景
1、表中的数据本身就有独立性，例如表中分表记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用。
2、需要把数据存放在多个介质上。

水平切分的缺点
1、给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作
2、在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

下面补充一下数据库分片的两种常见方案：
客户端代理：分片逻辑在应用端，封装在jar包中，通
过修改或者封装JDBC层来实现。 当当网的 ShardingJDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat、360的Atlas、网易的DDB等等都是这种架构的实现。

分库分表后面临的问题
事务支持
分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程
序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库join
只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题
这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。
解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

数据迁移，容量规划，扩容等问题
来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了
Sharding扩容的难度。

ID问题
一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身
的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；
另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由.

一些常见的主键生成策略
UUID 使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于
UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。 Twitter的分布式自增ID算法
Snowflake 在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

跨分片的排序分页
般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。