面试官问“你的SQL能力怎么样？”时应该如何回答？

作者 | 哪吒

来源 |哪吒编程(ID：gh_61b183bcf690)

面试官的这个问题，还是很好回答的，关于sql，说什么都行。

如果是我，我肯定说一些最重要的，比如sql的执行过程，如何检测一个sql的性能，如何进行sql的优化等。

一、先了解一下MySQL查询的执行过程

MySQL在查询时，它是由很多子任务组成的，每个子任务都会消耗一定的时间，如果要想优化查询，实际上要优化其子任务，可以消除一些子任务、减少子任务的执行次数、让子任务执行的更快。

MySQL查询的执行过程：从客户端到服务器、然后在服务器进行解析、生成执行计划、执行、返回结果给客户端。

执行是最重要的阶段，包括调用存储引擎检索数据、调用后的数据处理、排序、分组等；

查询需要在不同的地方花费时间，包括网络、CPU计算、生成统计信息、生成执行计划、锁等待等，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上花费时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

不必要的额外操作、不必要的重复操作、某些操作执行的太慢都是查询慢的原因，优化查询的目的就是减少和消除这些操作所花费的时间。

二、是否查询了不需要的数据

有些查询会查询很多不需要的数据，查询之后，程序中并未使用，这样不但会给MySQL服务器带来额外的负担，还会增加网络开销，也会消耗应用服务器的CPU和内存资源，简而言之，吃多少拿多少。

千万不要有“把数据都查出来，用Java代码过滤”的想法。

禁止使用select * 进行查询。

三、衡量查询开销的几个重要指标

1、响应时间

响应时间可以分为服务时间和排序时间。

• 服务时间指数据库处理这个查询真正花费的时间；
• 排队时间指服务器因为等待某些资源而没有真正执行查询的时间，比如等待IO操作、等待行锁。

2、扫描的行数和返回的行数

较短的行的访问速度更快，内存中的行比磁盘中的行的访问速度要快得多。

理想情况下扫描的行数和返回的行数是相同的。但这种情况并不多见，比如关联查询的时候，服务器必须扫描更多的行才能得到结果，因此，越多的表关联，性能越低。

3、扫描的行数和访问类型

MySQL可以通过多种方式查询并返回结果集，速度从慢到快，扫描的行数由多到少，依次为全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用。

最常用的优化方式是为查询增加一个合适的索引，索引可以让MySQL以最高效、扫描行数最少的方式找到需要的记录。

4、一般可以通过explain的Extra列查看查询的优劣

一般MySQL能够使用以下三种方式应用where条件，从好到坏依次为：

1. 在索引中使用where条件过滤不匹配的记录，这是在存储引擎层完成的；
2. 使用索引覆盖扫描，也就是Extra中出现Using index，直接从索引中过滤不需要的记录并返回命中的结果，这是在MySQL服务器层完成的，无须再回表查询记录；
3. Extra中出现Using where，这是在MySQL服务器层完成的，MySQL需要先从数据表读取记录，然后过滤。

Extra中出现Using where时，可以通过如下方式优化：

1. 使用索引覆盖扫描，把所有需要的列都放到索引中，这样就不用回表查询了；
2. 改变表结构，比如使用汇总表；
3. 重写sql，让MySQL优化器能够以更优化的方式执行这个sql；

四、再和面试官说一下避免索引失效的一些原则

1. 复合索引，不要跨列或无序使用（最佳左前缀）；
2. 符合索引，尽量使用全索引匹配；
3. 不要在索引上进行任何操作，例如对索引进行（计算、函数、类型转换），索引失效；
4. 复合索引不能使用不等于（!=或<>）或 is null（is not null），否则索引失效；
5. 尽量使用覆盖索引（using index）；
6. like尽量以常量开头，不要以%开头，否则索引失效；如果必须使用%name%进行查询，可以使用覆盖索引挽救，不用回表查询时可以触发索引；
7. 尽量不要使用类型转换，否则索引失效；
8. 尽量不要使用or，否则索引失效；

五、再聊一下锁机制

1、操作分类

读写：对同一个数据，多个读操作可以同时进行，互不干扰。

写锁：如果当前写操作没有完毕，则无法进行其它的读写操作。

2、操作范围

表锁：一次性对一张表整体加锁。

如MyISAM存储引擎使用表锁，开销小、加锁快、无死锁；但锁的范围大，容易发生冲突、并发度低。

行锁：一次性对一条数据加锁。

如InnoDB存储引擎使用的就是行锁，开销大、加锁慢、容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复读）

lock table 表1 read/write，表2 read/write，...

查看加锁的表：

show open tables;

3、MyISAM表级锁的锁模式

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改前，会自动给涉及的表加写锁。

所以对MyISAM表进行操作，会有如下情况发生：

（1）对MyISAM表的读操作（加读锁），不会阻塞其它会话（进程）对同一表的读请求。但会阻塞对同一表的写操作。只有当读锁释放后，才会执行其它进程的写操作。

（2）对MyISAM表的写操作（加写锁），会阻塞其它会话（进程）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

4、行锁的注意事项

1. 如果没有索引，行锁自动转为表锁。
2. 行锁只能通过事务解锁。
3. InnoDB默认采用行锁

优点：并发能力强，性能高，效率高

缺点：比表锁性能损耗大

高并发用InnoDb，否则用MyISAM。