那么一般我们对MySQL优化会做些什么呢？我们来慢慢道来。

MySQL查询过程

我们先来大概预热一下MySQL查询过程。

1、当一个请求发送给MYSQL，这里MySQL客户端/服务端通信协议是“半双工”的，也就是同一时间要么客户端发消息给服务器要么服务器发给客户端，一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以查询语句和返回的结果不应该过大。

2、这里的查询缓存，是对SQL语句不变且数据库数据不变的情况下生效的，也就是其中任何一个发生变化，缓存会被清空重新获取。

3、MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

4、查询优化器：经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划（但这并不总是最可靠的，它有时候并没有那么聪明，比如order by将影响索引的选择，所以需要手动在where前面use index（x）指定索引来解决）。mysql使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

5、在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。

好了，看完MYSQL的查询过程是不是对它的了解清晰多了，如果你觉得还不是很深刻，别急，继续往下看。只是了解这个执行过程，对理解我们下面要讨论的优化内容有所帮助。

存储引擎的选择要合理

我们平常在创建表结构语句的语句中就已经指明了存储引擎。什么是存储引擎呢？是一种用来存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据，决定了存储数据的规则，方式。最终会发现不同存储引擎，其保存数据的文件格式和方式是不一样的。

目前的存储引擎主要有：MyISAM和InnoDB。那么两者有什么区别呢？

1、InnoDB

支持事务，行级锁定

，外键约束。事务安全型存储引擎。

2、MyISAM

ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。擅长与操作处理，高速读与写。

全文索引支持，仅仅支持表级锁定

，不支持高并发。

那么我们该如何选择存储引擎？InnoDB更加注重数据的完整性和安全性，并发性处理，擅长更新，删除。MyISAM注重高速查询及插入，擅长插入和查询。所以在抛除事务支持和数据安全性不谈，小马觉得写多用InnoDB，查多用MyISAM。哈哈，就是这么粗糙。

配置参数优化

优化是个循序渐进的过程，实际情况不同，就要对症下药。这里就不一一赘述MySQL的各个配置参数了。

MySQL的配置参数还是有点多的，重点来聊一下max_connections最大连接数的配置。这个参数的配置要建立在机器能支撑的情况下尽量大，但如果连接数越多，MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设置值。通常情况下可以 show variables like‘max_used_connection'查看当前状态的相应连接数，如果max_used_connection/max_connections*100%（理想值约等于85%，低于10%则max_connections设置过大了，相同则太小了）。曾经小马就遇到过一次抢红包场景下导致数据库连接数不足的情况。这里一般还会考虑长连接和短连接的情况，这里不展开了。

我们在MySQL查询过程中有介绍过查询缓存。如果我们要开启查询缓存，query_cache_type参数要开启，则需要关注一下query_cache_size等参数的设置是否合理。

字段类型的选择要恰到好处

这一块其实是最好理解的了，就是只设置需要的字段类型和长度，不要浪费资源。

比较简单，比如性别字段就是用TINYINT类型，用1和2来映射男女，不要傻傻的用char类型存男女值了，这样对存储和查询都不友好，而且也不宜使用int类型，否则就是资源浪费。如果能明确字符串长度并且是定长的，比如openid，那就用char不用varchar。主键或唯一键最好是要设置，有主键可以减少一次全表查询，而且毕竟ON DUPLICATE KEY UPDATE语句也非常好用。

这里埋一个彩蛋哈，当设置时间的数据类型为timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP 让其变更数据时间自更新时，在数据不发生改变的情况下是不会自动变更时间的（这里其实有个小知识点：update操作时MySQL在发现数据没变化其实根本也就没执行update操作的。它会先判断是否新旧值有变化，不变的话放弃执行update语句，保持原样），这点要非常小心。

题外话，int（11）和int（5）的区别是啥呢？

范式与逆范式

我们在刚学数据库原理的时候，有特别的这么一章，三范式。学了老半天，老师说，其实有的时候我们也必须破坏其中的某个范式来达到提高数据库性能的目的。什么？既然搞了一个三范式然后又让我可以不要遵守。是的，你学过国学《易经》吗？哈哈，否极泰来，物极必反，中庸，啊，跑题了。其实就是说有的时候我们也可以不必遵循满三范式，逆范式允许通过增加冗余或重复的数据来提高数据库的性能。比如原本需要两个表连接才能查询到副表的数据，可以直接将副表的数据在插入时直接也插入到主表，这样查询副表字段时就不用再进行表连接了，大大节约了查询时间，这个需要根据实际情况定夺哈，没有规定公式。就比如，我们刚刚的例子，如果本来就需要进行一次表连接查询所需要的字段，那就没必要再往主表里再写一次副表字段数据了。

索引优化

这是个大话题也是个大篇幅，同时也是面试中基本会被问到的知识点。敲黑板，必考。以下是两个比较基本的提问问题。

索引的类型：主键索引，唯一索引，普通索引，全文索引。当然还有一个组合索引，就不算在基本类型里了。

主键索引和唯一索引的区别：主键索引不能为空值，唯一索引允许空值（但因为唯一只能存在一条）；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

一般我们需要对哪些字段建立索引呢？首先，我们要理解，索引就相当于书本的目录，将数据的全局扫描范围缩减到目录范围内。所以，一般建立索引的字段是，where条件字段，还有表连接的join on字段（注意，这个效果很显著，亲测是秒级和毫秒级的区别），至于那些值比较单一的字段，比如男女1和2值的就没必要建立索引了，否则影响写操作效率。为啥这么说呢？因为这个就两个不同值，目录的效果作用不大，并且每次写插入操作db都要重新刷新重建一次索引，查性能没改善反而影响了写性能。那么问题来了，那些都是不同值的字段列创建索引不是也没有目录的效果吗，怎么就建议加索引呢？这里要介绍一下索引的结构了。

1、B-Tree索引：

Hash、B-Tree（B树）

两种数据结构。指的是mysql存储索引所采用的数据结构。其中，用户所维护的所有的索引结构 B-Tree结构。还有复合索引。

2、聚集索引(聚簇索引)：

B+Tree(B-Tree的变种)

，在innodb的存储引擎上，主键索引是与数据记录存储在一起的（聚簇在一起的）。

这里不做深究，我们简单点，小马就记得在使用navcat创建索引的时候，一般会有一个索引方法的选项，可选值是BTREE和HASH。那么该怎么选呢？粗糙一点呢，你就闭着眼睛选，正规的呢，你这样理解就可以了，如果这个索引字段是用于范围查询的，比如时间字段，一般会查询某日到某日，则选BTREE；而如果字段是用于确定值查询的，比如where 字段等于某个值的这种，那就选HASH。哈哈，是不是比较好理解，这里涉及到数据结构知识，原理就不展开了。自然这个也回答了上面遗留的问题，如果我们的列都是不同的值，那么我们就hash索引，这样不是O(1)的时间复杂度吗，比起没索引全表扫描自然高效不少。

需要注意的是，全文索引是为了解决like这类查询的匹配问题，但是mysql的全文索引几乎不用，因为它不支持中文，我们应该使用sphinx全文索引或者其他中间件来支持。

那么，我们的索引什么时候会失效呢？

1、对于多列索引，不是使用的第一部分，则不会使用索引。这就是我们通常说的最左原则。比如组合索引是name,age,sex，则语句where age=3索引失效，语句where age=3 and sex =1索引失效，语句where name='xiaoma' and sex=3索引有效。可以这样理解，组合索引是辆火车，要使用它必须要加载火车头，否则就是不能启动。因此，在平时我们写SQL时也要特别注意where条件字段的顺序要和索引列对上才行。

2、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。如主键是id，语句是where id=1 or name='xiaoma';则主键索引失效。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引，就是name也加索引。

3、like查询是以%开头。比如where name like '%xiaoma';则name 索引失效。但结尾是可以的，如where name like 'xiaoma%';则索引有效。

4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。

5、如果mysql估计使用全表扫描要比使用索引快,则不使用索引。注意我们上面提到的查询优化器，负责选出最优的执行计划。

我们如果排查自己创建的索引是否生效呢？只需要在查询语句前加上关键字Explain查看查询执行计划，如Explain select * from table 即可。可以查询是否全表扫表是否命中生效我们定义的索引列，以及查询执行的其他信息。各个字段的含义参考这里。

g啊，明显感觉篇幅有点长了，还有分区分表，主从复制，读写分离，负载均衡，编写合理SQL语句等还没介绍。小马觉得还是再起一个篇幅吧，先到这了，下篇文章再见。谢谢品阅，水平有限，欢迎指教。