MySQL优化七大步骤，面试经常问？却还搞不懂SQL优化？这一篇文章帮你解决。

MySQL优化七大步骤

需要优化可能出现的原因。

一条SQL语句的执行时间变长，可能是由于以下几个原因导致的。
数据量变多，这种情况可以考虑读写分离和分库分表；关联了太多的表SQL语句本身的问题，应该对SQL进行优化；服务器性能下降，此时就需要对MySQL本身进行调优，可以通过修改my.cnf配置文件进行优化。

1.SQL语句的优化

explain字段查看

type
type字段描述表的连接方式。主要有以下几种，从上到下，性能依次下降。
system：表只有一行数据。
const：表中最多只有一行匹配的数据，常出现于将主键或者unique索引作为查询条件的语句。(eg : SELECT * FROM tbl_name WHERE primary_key=1; )
eq_ref：主键或非空唯一索引扫描，对于ref_table表中的每一个键值，other_table中最多只有一条记录与之匹配。(eg : SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;)
ref：非唯一索引扫描(eg : SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;)
fulltext：使用全文索引。
ref or null：类似于ref，但是MySQL会额外扫描包含空值的行。(eg : SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;)
index merge：对多个索引进行扫描然后将他们各自的结果进行合并。(eg:SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;)
unique subquery：在类似于value IN (SELECT primary_key FROM single_table WHERE some_expr)的子查询中，替代eq_ref。其中子查询中的primary_key 是唯一索引。
index subquery：在类似于value IN (SELECT key_column FROM single_table WHERE some_expr)的子查询，替代ref。其中子查询中的==key_column 是非唯一索引。
range: 使用索引检索指定范围的行。(eg:SELECT * FROM tbl_name WHERE key_column IN (10,20,30); )
index: 扫描整个索引树。
all：扫描全表，以找到匹配的行。
.possible_keys
显示可能应用在这张表上的索引，一个或者多个。查询涉及到的字段，若涉及索引，则该索引会被列出来，但不一定被使用。
3.7.key
实际使用的索引。
key_len
表示使用了索引的长度，单位为字节。该字段可以用来检查，sql语句是否充分的使用上了索引，该字段越大越好。
ref
显示表中的哪些列用来和索引进行比较。ref的之也有可能会是一个常数。
rows
mysql认为在查询时必须要检查的行数。显然地，该值越小越好。
Limit例如
filtered
表示存储引擎返回的数据在经过server过滤后还剩下多少，是一个百分比。
extra
这个字段包含MySQL解析查询地额外信息（重要）。该字段的取值比较多，这里只介绍几个比较重要的：
Using filesort：说明MySQL会对数据使用一个外部索引进行排序，而不是按照表内的索引顺序进行读取。MySQL中无法按照索引进行排序的操作称为"文件排序"。（eg：select * from emp where id < 1000 order by deptid;其中deptid并没有建立索引）
Using temporary：使用了临时表来保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组group by。（eg：select count(*) from emp GROUP BY emp.deptid;其中deptid并没有建立索引）
Using index：利用索引进行了排序或分组。
Using where：表明使用了where过滤
Using join buffer：使用了连接缓存
impossible where：where子句的值总是false，根据该条件不能查到任何数据。（eg：select * from table_a where 0 > 1;）

总结

通过对explain结果各个字段的学习，其中有些字段可以用来指导我们进行SQL优化，

一些经验和原则可以总结如下：

id字段，一个id值，表示一趟查询，查询的趟数越少越好;
type字段，应该尽量避免全表扫描；
key_len字段，越大越好；
rows字段，越小越好；
extra字段，尽量避免using filesort和using temporary。
换句话说，SQL优化就是通过修改SQL，增加索引等手段，使得这些指标尽可能地满足上述的要求。

2.索引的优化（索引失效问题）

1.不要让字段的默认值为NULL。

复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

3.索引列排序

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5.不要在列上进行运算

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
理论上每张表里面最多可创建16个索引

6.字符串不加单引号

7.小表驱动大表

3.表结构优化（选用适用的字段属性）

为何要表结构优化
数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以减少 IO 次数可以在很大程度上提高数据库操作的性能。
由于MySQL数据库是基于行存储的数据库，而数据库IO操作的时候是以 page 的方式，也就是说，如果我们每行记录所占用的空间量减小，就会使每个 page 中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。我们无法改变数据库中需要存储的数据，但是我们可以在数据的存储方式方面做一些优化。
两个方面

一、数据类型的选择：

（2）对于定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。

1、适当拆分：

我们可能希望将一个完整对象对应一张数据库表，这对于应用程序开发来说是很友好的，但有时可能会在性能上带来较大的问题。当我们的表中存在类似于 TEXT 或者是很大的 varchar 类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们可以将其拆分到另外的独立表中，以减少常用数据表所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

2、适度冗余：

冗余确实这样做会增大每条记录的大小，降低每条记录中可存放数据的条数，但是在有些场景下我们仍然还是不得不这样做，比如：被频繁引用且只能通过 Join连接 2张（或者以上）大表的方式才能得到的独立小字段，这样的场景由于每次Join连接仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

3、尽量使用 not null：

（1）null 类型比较特殊，SQL 难优化。虽然 MySQL null 类型和 Oracle 的 null 有差异，会进入索引中，但如果是一个组合索引，那么这个 null 类型的字段会极大影响整个索引的效率。
（2）很多人觉得 null 会节省一些空间，所以尽量让 null 来达到节省IO的目的，但是大部分时候这会适得其反，因为对于允许为 null 的字段，mysql 会多需要一个1字节记录是否为 null；同时也带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，数字可以默认0，字符串默认“”。

4.事务的处理优化

适量降低事务隔离级别！

5.锁表的优化

InnoDB行锁优化建议：

1.尽可能让所有数据检索都通过索引来完成，

避免升级为表级锁定，用explain查看是否使用符合预期的索引，如果不走索引，加的行锁也是表锁。

2.合理设计索引，

可以缩小行锁的锁定范围，避免造成不必要的锁定影响其他Query执行

3.尽可能减少基于范围的数据检索过滤条件，

避免间隙锁锁定不该锁定的记录

4.控制事务大小，

减少锁定的资源量和锁定时间长度

5.尽量使用较低级别的事务隔离

6.系统配置的优化，

操作系统的连接数，资源回收，内存设置，慢查询设置，MySQL连接数设置，等等

慢查询默认是关。
连接数。

thread_cache_size缓存线程 优化

thread_cache_size：当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户，而不是销毁(前提是缓存数未达上限)。

即可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。

查询thread_cache_size

– 查询服务器 thread_cache_size 配置
show variables like ‘thread_cache_size’;

设置线程缓存数量 thread_cache_size

如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。

对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

物理内存设置规则：

通过比较Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

7.硬件的优化

服务器硬件，网络带宽等等，运维工程师