共计 3207 个字符，预计需要花费 9 分钟才能阅读完成。
在面试的环节中，面试官问到：你是如何设计你的表结构的，画一下 E - R 图？接着又继续深挖，如果有慢查询，你是如何优化你的 sql 的？
今天，我就来和大家讲讲要怎么回答这道问题。首先，我们要稳住不要慌，自己是自己亲手做的项目，第一个问题应该都不大，第二个问题就需要在面试之前做好充分的准备啦…
在回答问题之前先要了解查询的流程：查询是由一系列的子任务组成的，包括从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组。为了完成这些任务，查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划、锁等待操作。进行一些不必要的额外操作时或者某些重复执行某些额外操作会消耗大量的时间。
查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量的数据，大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，可以通过以下两个步骤来分析：
确认应用程序是否在检索大量超过需要的数据。
确认 MySQL 服务器是否在分析大量超过需要的数据行。
上面的都是理论，在实践中，MySQL 的优化主要涉及 SQL 语句及索引的优化、数据表结构的优化这三个方面。
SQL 语句的优化：
1、少用子查询
尽量少用子查询，因为子查询会产生临时表；除非像 count(*)临时表很小的。
2、少用 SELECT *
每次看到 SELECT * 都需要用怀疑的眼光审视，是否真的需要返回全部的列？取出全部的列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的 I /O、内存和 CPU 的消耗。
3、查询必要的记录
一个常见的错误是常常会误以为 MySQL 只会返回需要的数据，实际上 MySQL 却是先返回全部结果集再进行计算，建议在查询后面加上 LIMIT。
4、不要重复查询相同的数据
不断执行相同的查询，然后每次都会返回完全相同的数据。可以采用的方案是初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。
5、COUNT 查询优化
COUNT() 聚合函数的作用：统计某一个列值的数量，也可以统计行数。需要注意的是统计列值时要求列值是非空的 (不统计 NULL)，COUNT() 查询尽可能少的行。
举个例子：如果我们直接查 id>100 的记录，涉及到的有两千多万行记录扫描。但是由于 COUNT()特性，我们可以用 count() – (id<100)的做法，这样扫描的行就只有 100 行了。
6、Where 子句中，where 表之间的连接必须写在其他 Where 条件之前，那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。
7、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。
8、避免在索引列上使用计算。
9、避免在索引列上使用 IS NULL 和 IS NOT NULL。
10、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
11、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
12、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
索引优化
1、关联查询优化
确保 ON 或则 USING 子句的列上有索引。创建索引时就要考虑关联的顺序，当表 A 和表 B 用列 c 关联的时候，如果优化器关联顺序是 B、A，就只需要在表 A 上建立索引，没用的索引会占用存储。
2、GROUP BY 和 DISTINCT 优化
GROUP BY 和 DISTINCT 的优化最有效的就是使用索引。所有对于分组的列一定要建立索引。比如：
select product, count(*) from orders group by product;
这样的一个查询，对 product 要建立索引。
3、LIMIT 分页优化
进行分页操作时，通常都会通过偏移量来查询某些数据。然后再加上解释的 order by，性能一般都不错。对于 order by 的列 一定要加上索引。但是对于 limit 10000，10 这样检索目标 10 条记录必须先先查询前面的 10000 条记录。代价很高，这种时候优化最简单办法就是使用覆盖索引。
注意索引失效的情况，
1)以“%”开头的 LIKE 语句，模糊匹配
2)OR 语句前后没有同时使用索引
3) 数据类型出现隐式转化（如 varchar 不加单引号的话可能会自动转换为 int 型）
优化数据库
1、数据表结构优化的建议
如何根据系统将要执行的查询语句来设计数据库表，反范式的设计可以加快某些类型的查询，添加计数表和汇总表是一种很好的查询优化方式。
选择优化数据类型的几条建议：
更小的通常更好，尽量使用可以正确存储数据的最小数据类型，因为占用更少的磁盘、内存和 CPU 缓存。
简单最好，选择整数而不是字符串，选择 MySQL 内建的类型而不是字符串来存储时间和日期，使用整数来存储 IP 地址。
尽量避免 NULL，很多表都包含可为 NULL 的列，这是因为 NULL 是列的默认值，需要指定列为 NOT NULL。
整数类型数据一般用 int，对于布尔类型的数据用 tinyint，但是整数计算一般是使用 64 位的 BIGINT 整数。
在需要对小数进行精确计算时，比如说存储财务数据才使用 DECIMAL（浮点存储的 float 和 double 类型计算不精确），但是 DECIMAL 计算的代价很高，可以考虑使用 BIGINT 代替 DECIMAL，将小数的位数乘以相应的倍数即可。
varchar 和 char
当需要存储可变长的字符串用 varchar，比使用 char 存储更节省空间，varchar 使用 1 或者 2 个额外的字节来记录长度。至于用 char 来存储适用于下列几种情况，一是需要存储很短的字符串时（存储只有 Y 和 N 的值时），二是所有的值接近固定长度（存储 MD5 值），三是经常需要变更的值。
BIT
在 MySQL5.0 之前，BIT 是 TINYINT 的同义词，在 MySQL5.0 以及更新的版本，是一个完全不同的数据类型。BIT 类型的新行为：（1）可以使用 BIT 列在一列中存储一个或者多个 true/false 值。MySQL 把 BIT 当做字符串类型，而不是数字类型。当检索 BIT(1)的值时，结果是一个包含二进制 0 或者 1 的字符串，而不是 ASCII 的“0”或“1”。
SET
如果需要保存很多的 true/false 值，可以考虑合并这些列到一个 SET 数据类型，它在 MySQL 内部是一系列打包的位的集合来表示的。
使用枚举代替常用的字符串类型，因为 MySQL 在存储枚举时非常紧凑，MySQL 把每个枚举的值保存为整数，并且在表的.firm 文件中保存“数字 - 字符串”映射关系的“查找表”。
DATATIME 存储的范围更广，保存的值从 1001 年到 9999 年，精确到秒，与时区无关，使用 8 个字节的存储空间，使用一种可排序、无歧义的格式显示时间，TIMESTAMP 类型保存了从 1970 年 1 月 1 日午夜以来的秒数，使用 4 个字节的存储空间，只能表示从 1970 年到 2038 年，依赖于时区，空间效率更高，推荐使用 TIMESTAMP
对于 BOLB 和 TEXT 类型他们都是为了存储很大的数据而设计的字符串，分别采用二进制和字符串方式存储。
不能有太多的列
单个查询最好在 12 个表以内做关联
当遇到未知值的时候不要害怕使用 NULL
在实际的应用中需要混用范式和反范式，使用部分范式化的 schema、缓存表、以及其他的技巧，最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。
修改.frm 文件来加快 ALTER TABLE 操作的速度
选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL，例如’省份’、’性别’最好适用 ENUM
使用连接 (JOIN) 来代替子查询
用联合 (UNION) 来代替手动创建的临时表
锁定表、优化事务处理
