这一篇说明大多数SQL优化的面试问题

一:创建的索引有没有被使用到

MySQL提供了explain命令来查看语句的执行计划。

二:SQL的生命周期

1. 应用服务器与数据库服务器建立一个连接
2. 数据库进程拿到请求sql
3. 解析并生成执行计划，执行
4. 读取数据到内存并进行逻辑处理
5. 通过步骤一的连接，发送结果到客户端
6. 关掉连接，释放资源

三:超大分页怎么处理

有两种方向解决:

1. 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),核心思想都一样,就是减少load的数据.

类似于select * from table where age 20 limit 1000000,10这种查询其实也是有可以优化的余地的.

1. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in(select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快.
2. 同时如果ID连续的好,我们还可以select* from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种, 但是核心思想都一样,就是减少load的数据.

2. 从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击

【推荐】利用延迟关联或者子查询优化超多分页场景。

四:mysql分页

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，

1. 第一个参数指定第一个返回记录行的偏移量，

2. 第二个参数指定返回记录行的 大数目。

3. 初始记录行的偏移量是 0(而不是 1)

   mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
   

   如果只给定一个参数，它表示返回 大的记录行数目：换句话说，LIMIT n 等价于 LIMIT 0,n。

   mysql> SELECT * FROM table LIMIT 5; //检索前5 个记录行
   

   为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1：

   mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
   

五:慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

配置项：slow_query_log

1. 可以使用show variables like ‘slov_query_log’ 查看是否开启，如果状态值为OFF，
2. 可以使用set GLOBAL slow_query_log = on来开启，
3. 它会在datadir下产生一个xxx-slow.log的文件

设置临界时间

1. 配置项：long_query_time
2. 查看：show VARIABLES like ‘long_query_time’，
3. 单位秒设置：set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将 慢的SQL优化掉查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

六:慢查询都怎么优化

查询的优化首先要搞明白慢的原因是什么？ **是查询条件没有命中索引？是 load了不需要的数据列？还是数据量太大？**所以优化也是针对这三个方向来的，

1. 首先分析语句，查看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
2. 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
3. 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

七:为什么要尽量设定一个主键？

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键。

设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

八:主键使用自增ID还是UUID

推荐使用自增ID，不要使用UUID。

九:字段为什么要求定义为not null？

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

十:如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar 来存储，这样可以节省空间且提高检索效率。

十一:优化查询过程中的数据访问

访问数据太多导致查询性能下降确定应用程序是否在检索大量超过需要的数据，可能是太多行或列确认MySQL服务器是否在分析大量不必要的数据行避免犯如下SQL语句错误

查询不需要的数据。解决办法：使用limit解决
多表关联返回全部列。解决办法：指定列名
总是返回全部列。

1. 解决办法：避免使用SELECT * 重复查询相同的数据。
2. 解决办法：可以缓存数据，下次直接读取缓存是否在扫描额外的记录。
3. 解决办法：使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
   • 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
   • 改变数据库和表的结构，修改数据表范式
   • 重写SQL语句，让优化器可以以更优的方式执行查询。

十二:优化长难的查询语句

MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。切分查询将一个大的查询分为多个小的相同的查询

一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。

1. 分解关联查询，让缓存的效率更高。
2. 执行单个查询可以减少锁的竞争。
3. 在应用层做关联更容易对数据库进行拆分。
4. 查询效率会有大幅提升。
5. 较少冗余记录的查询

十二:优化特定类型的查询语句

count()会忽略所有的列，直接统计所有列数，不要使用count(列名)

MyISAM中，没有任何where条件的count()非常快。

当有where条件时，MyISAM的count统计不一定比其它引擎快。

可以使用explain查询近似值，用近似值替代count(*) 增加汇总表使用缓存

十三:优化关联查询

确定ON或者USING子句中是否有索引。

确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引

十四:优化子查询

用关联查询替代

优化GROUP BY和DISTINCT

1. 这两种查询据可以使用索引来优化，是有效的优化方法关联查询中，使用标识列分组的效率更高
2. 如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
   WITH ROLLUP超级聚合，可以挪到应用程序处理

十五:优化LIMIT分页

LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的 大ID，下次查询时直接根据该ID来查询

十六:优化UNION查询

UNION ALL的效率高于UNION

十七:优化WHERE子句

从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

十八:为什么要进行数据库优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢

数据是存放在磁盘上的，读写速度无法和内存相比优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

19:数据库结构优化

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1. 将字段很多的表分解成多个表对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
2. 当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。增加中间表, 对于需要经常联合查询的表，可以建立中间表以提高查询效率。
3. 合理的加入冗余字段可以提高查询速度。
4. 表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

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

第一步:: 使用top命令,查看是否是mysqld占用引起得:

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

第二步:: 如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。

第三步:: 找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

第四步:: 一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

21:MySQL的复制原理

主从复制原理：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

22:主从复制的作用

1.主数据库出现问题，可以切换到从数据库。
2.可以进行数据库层面的读写分离。
3.可以在从数据库上进行日常备份。

23:MySQL主从复制解决的问题

数据分布：随意开始或停止复制，并在不同地理位置分布数据备份负载均衡：降低单个服务器的压力高可用和故障切换：帮助应用程序避免单点失败升级测试：可以用更高版本的MySQL作为从库

MySQL主从复制工作原理