文档介绍：概述
数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。
所以我们先从 MySQL 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化
表结构优化:
由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了。反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能。此外,由于我们的内存是有限的,增加每个page中存放的数据行数,就等于增加每个内存块的缓存数据量,同时还会提升内存换中数据命中的几率,也就是缓存命中率。
优化原则:使数据量最小,性能的瓶颈在于磁盘性能,数据量越小,磁盘读取数据的速度就越快,同时,在较小的列上建立索引,索引文件占用的资源也会更少。
尽可能地使用最有效(最小)的数据类型。
MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如,MEDIUMINT经常比INT好一些,因为MEDIUMINT列使用的空间要少25%
尽量使用 NOT NULL
NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异,会进入索引中,但如果是一个组合索引,那么这个NULL 类型的字段会极大影响整个索引的效率。此外,NULL 在索引中的处理也是特殊的,也会占用额外的存放空间。很多人觉得 NULL 会节省一些空间,所以尽量让NULL来达到节省IO的目的,但是大部分时候这会适得其反,虽然空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将IO量省下来,反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化****惯。
只创建你确实需要的索引。
索引对检索有好处,但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表,对它们做一个索引。第一个索引部分应该是最常用的列。如果从表中选择时总是使用许多列,应该首先以更多的副本使用列以获得更好
的索引压缩。
垂直拆分
保证经常查询、显示的主要字段在一张表中,其他的信息放在明细表中。此类优化更利于常用字段频繁查询时,提高系统性能。例如教师信息表,经常用到的字段非常有限,每次查询浪费大量的内存资源加载不使用的信息,对性能造成一定影响。
水平拆分
当表中数据量非常大时,可以根据具体的业务情况,按照某个字段进行分类,存储在不同的表甚至是数据库中,使得某个表或库的数据量在一个比较小的范围内,一次来提高查询性能。
索引的使用
索引对单个表查询的影响
索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。
例如对下面这样的一个student表:
mysql>SELECT * FROM student
Id
Name
English
Chinese
History
12
Tom
66
93
67
56
Paul
78
52
75
10
Marry
54
89
74
4
Tina
99
83
48
39
William
43
96
52
74
Stone
42
40
61
86
Smith
49
85
78
37
Black
49
63
47
89
White
94
31
52
这样,我们试图对它进行一个特定查询时,就不得不做一个全表的扫描,速度很慢。例如,我们查找出所有english成绩不及格的学生:
mysql>SELECT name,english FROM student WHERE english<60;
Name
English
Marry
54
William
43
Stone
42
Smith
49
Black
49
其中,WHERE从句不得不匹配每个记录,以检查是否符合条件。对于这个较