mysql性能优化培训

一概述

数据库属于IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。

而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读

取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一

步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。

所以我们先从MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化

二表结构优化：

由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作IO 的时候是以page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，

就会使每个page中可存放的数据行数增大，那么每次IO 可访问的行数也就增多

了。反过来说，处理相同行数的数据，需要访问的page 就会减少，也就是IO 操

作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中

存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数

据命中的几率，也就是缓存命中率。

优化原则：使数据量最小，性能的瓶颈在于磁盘性能，数据量越小，磁盘读取数据的速度就越快，同时，在较小的列上建立索引，索引文件占用的资源也会更少。

1.尽可能地使用最有效(最小)的数据类型。

MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间

要少25%

2.尽量使用NOT NULL

NULL 类型比较特殊，SQL 难优化。虽然MySQL NULL类型和Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极

大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外

的存放空间。很多人觉得NULL 会节省一些空间，所以尽量让NULL来达到节省IO

的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带

来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所

以尽量确保DEFAULT 值不是NULL，也是一个很好的表结构设计优化习惯。

3.只创建你确实需要的索引。

索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的

列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好

的索引压缩。

4.垂直拆分

保证经常查询、显示的主要字段在一张表中，其他的信息放在明细表中。此类优化更利于常用字段频繁查询时，提高系统性能。例如教师信息表，经常用到的字

段非常有限，每次查询浪费大量的内存资源加载不使用的信息，对性能造成一定影

响。

5.水平拆分

当表中数据量非常大时，可以根据具体的业务情况，按照某个字段进行分类，存储在不同的表甚至是数据库中，使得某个表或库的数据量在一个比较小的范围内，一次来提高查询性能。

三索引的使用

1.索引对单个表查询的影响

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越

多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文

件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快

100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免

磁盘寻道。

例如对下面这样的一个student表：

很慢。例如，我们查找出所有english成绩不及格的学生：

对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。

如果，我们为english列创建一个索引,

此索引存储在索引文件中，包含表中每行的english列值，但此索引是在english 的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描(如二分查找法)。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。

因此在执行下述查询

mysql>SELECT name,english FROM user WHERE english<60;

其结果为：

你应该可以发现，这个结果与未索引english列之前的不同，它是排序的。2.索引对多个表的影响

假如有三个无索引的表t1、t2、t3，分别只包含列c1、c2、c3，每个表分别由含有数值1 到1000 的1000 行组成。查找对应值相等的表行组合的查询如下所示：

此查询的结果应该为1000 行，每个组合包含3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与WHERE 子句相配的那些组合。可能的组合数目为1000×1000×1000(十亿)，比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有1000 行的情形。如果每个表中有一百万行时，将会怎样?很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：

1) 如下从表t1 中选择第一行，查看此行所包含的值。

2) 使用表t2 上的索引，直接跳到t2 中与来自t1 的值匹配的行。类似，利