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

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划， MySQL提供了explain命令来查看语句的执行计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多 相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划， 就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。
image.png
执行计划包含的信息id有一组数字组成。表示一个查询中各个子查询的执行顺序;

  • id相同执行顺序由上至下。
  • id不同，id值越大优先级越高，越先被执行。

  • id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

    1.1.select_type

    每个子查询的查询类型，一些常见的查询类型。

  • 1.SIMPLE：不包含任何子查询或union等查询

  • 2.PRIMARY：包含子查询最外层查询就显示为 PRIMARY
  • 3.SUBQUERY：在select或 where字句中包含的查询
  • 4.DERIVED：from字句中包含的查询
  • 5.UNION：出现在union后的查询语句中

    1.2.table

    查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id

1.3.partitions

表分区、表创建的时候可以指定通过那个列进行表分区

1.4.type

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到range级别，最好达到ref

1.4.1.const, system

mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system。

  1. #关闭mysql5.7新特性对衍生表的合 并优化
  2. set session optimizer_switch='derived_merge=off';
  3. explain select * from (select * from film where id = 1) tmp;

image.png

  1. show warnings;

image.png

1.4.2.eq_ref

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

  1. explain select * from film_actor left join film on film_actor.film_id = film.id;

image.png

1.4.3.ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

1.4.3.1.简单 select 查询，name是普通索引(非唯一索引)

  1. explain select * from film where name = "film1";

image.png

1.4.3.2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

  1.  explain select film_id from film left join film_actor on film.id = film_actor.film_id;

image.png

1.4.4.range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。 

  1. explain select * from actor where id>1;

image.png

1.4.5.index

扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

  1. explain select * from film;

image.png

选择二级索引而不选择主键索引的原因是因为，二级索引比较小，所以 优先选择二级索引

1.4.6.ALL

即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。 

  1. explain select * from actor;

image.png

1.5.possible_keys列

这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

1.6.key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

1.7.key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

1.8.ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常量)，字段名(例:film.id)

1.9.rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

1.10. Extra列

这一列展示的是额外信息。常见的重要值如下:

  • Using index:使用覆盖索引

    覆盖索引定义:mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index; 覆盖索引一般针对的是辅助索引，整个 查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值 

  1. explain select film_id from film_actor where film_id = 1;

image.png

如果有using index，那么效率可能会更高一些；覆盖索引就是，我们创建的辅助索引已经包含所需要的全部字段，不需要进行回表操作

  • Using index condition:查询的列不完全被索引覆盖，where条件中是一个前导列的范围; 

    1. explain select * from film_actor where film_id > 1;

    image.png

  • Using filesort:将用外部排序而不是索引排序

    数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。

2.大表数据查询，怎么优化

  1. 优化shema、sql语句+索引;
  2. 第二加缓存，memcached, redis;
  3. 主从复制，读写分离;
  4. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统， 也就是分布式系统;
  5. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动， 做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表;

    3.超大分页怎么处理?

    超大的分页一般从两个方向上来解决.
  • 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快.
  • 从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的 具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数 据库中,直接返回即可.
image.png

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

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

    5.主键使用自增ID还是UUID?

    推荐使用自增ID，不要使用UUID。 因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。 而且主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

    6.字段为什么要求定义为not null?

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

    7.如果要存储用户的密码散列，应该使用什么字段进行存储?

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

    8.MySQL数据库cpu飙升到500%的话他怎么处理?

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

    9.SQL语句的执行过程

    查询语句：
    1. select * from student  A where A.age='18' and A.name='张三';
    image.png
    结合上面的说明，我们分析下这个语句的执行流程：
    ①通过客户端/服务器通信协议与MySQL建立连接。并查询是否有权限
    ②Mysql8.0之前开看是否开启缓存，开启了Query Cache且命中完全相同的SQL语句，则将查询结果直接返回给客户端；
    ③由解析器进行语法语义解析，并生成解析树。如查询是select、表名tb_student、条件是id=’1’
    ④查询优化器生成执行计划。根据索引看看是否可以优化
    ⑤查询执行引擎执行SQL语句，根据存储引擎类型，得到查询结果。若开启了Query Cache，则缓存，否则直接返回。