MySQL的使用及优化

前言

最近听了公司里的同事做的技术分享，然后觉得对自己还是挺有帮助的。都是一些日常需要注意的地方，我们目前在开发过程中，其实用不到MySQL太深的内容的。只是能适用我们日常开发的知识就可以了。所以我将自己的理解和学习总结也写出来，供大家一起分享。

大体分四部分：

数据库优化概述
数据库表设计
索引原理及优化
可扩展性设计

数据库优化概述

优化金三角

做数据库优化一般是由以下几种方式：

成本和效果成反比。

服务器硬件

增强服务器的硬件方式不同的方式：例如增加磁盘配置（SSD，PCRE），增大内存，增加CPU配置等。增强服务器硬件在一定的阶段内确实可以达到不错的效果，但是并不是长久之计，如果不注重下面的三种策略，一味的增加硬件配置，会适得其反。

系统及数据库配置

随着系统硬件的不断更新迭代，数据库的配置也是不断变化的。例如以前的机械硬盘性能并不很好，所以数据库的配置并没有设置太高。当服务器普遍的都是SSD后数据库的系统配配置也是可以随之变化的。另外随着业务的变化以及数据量的增长，数据库的配置也是随着变化的。但是这部分的配置带来的效果并不太明显，和增加服务器硬件类似。

数据库表设计及规划

数据库的表在设计之初就应该考虑好了以后的规划。不然当发现数据库产生瓶颈了再去优化，成本会很高。所以也需要开发人员能通过对业务的深刻理解来对数据库做好长远的规划。

SQL及索引优化

对SQL语句以及索引的优化可以说是成本最低的了，效果也是非常显著的。

这四部分内容，总有人觉得SQL及索引优化是最重要的，但是本人觉得最重要的是数据库表设计以及规划，如果能根据业务将表设计好了，根本是不需要进行索引优化的。如果数据库没有规划好，再好的DBA给你做SQL优化，效果也是杯水车薪的。

MySQL逻辑架构

上面这幅图是MySQL的基本逻辑架构图，主要分为四层。

连接层

通过MySQL的连接地址去访问MySQL的数据库，以及对访问信息的校验。

服务层

对SQL语句的校验，以及对SQL的优化和优化策略选择，最后发送到执行器去执行SQL。还包括MySQL的查询缓存也在这一层。

引擎层

MySQL是插件式存储引擎，最终将数据存到硬盘时不同的引擎有不同的组织方式。上面列出了一些引擎，常见InnoDB，MyISAM等，只要符合MySQL的接口规范，MySQL是支持自定义的引擎。

存储系统层

这部分主要是数据存储，将数据存到磁盘，磁盘的IO读写等过程。

数据库表设计

引擎的选择

请使用InnoDB存储引擎，慎用MyISAM引擎。

上图是InnoDB引擎和MyISAM引擎的一些区别对比。

ACID事务支持：由于我们介绍这次介绍MySQL的时候是以OLTP（on-line transaction processing：联机事务处理）为主的，而非OLAP（On-Line Analytical Processing：联机分析处理），所以事务处理是很重要的，这也就是为什么强烈要求使用InnoDB引擎的一个原因。

锁粒度：MyISAM支持的锁粒度是表级锁，表级锁的意思是指当一张数据表被锁住后，其他的对这张表的操作（DML）都要等着前一个锁释放了才可以执行。所以当并发量高时用户体验是很不好的。而InnoDB引擎的行级锁，只是对表的一部分数据进行加锁，所以能很好的支持并发，降低了对同一张表的操作冲突。

外检约束：虽然InnoDB支持外键，MyISAM不支持外键。但是也不建议在日常的使用过程中用外键，因为每次操作外键时都要去检查一下外键关联的数据。

全文索引：InnoDB引擎不支持全文索引，但是MyISAM支持。但是在数据库中建立全文索引其实并不是什么好的策略，还是建议如果需要建立全文索引的时候考虑使用搜索引擎工具如：ElasticSearch，Solr等。

崩溃安全：InnoDB支持崩溃安全，MyISAM是不支持的崩溃安全的。

什么是崩溃安全呢？

举个例子

巴特西