Mysql数据库优化的方法总结（必看）

学无止境，数据库优化分为方方面面，在这里，我进行了比较全的总结，分享给正在工作或者学习的同行们。

数据库的优化分为以下七大方面：

1、表的设计要符合三范式（适当的反三范式也可以）；

2、添加适当的索引，索引对查询速度影响很大，必须添加索引（主键索引，唯一索引，普通索引，全文索引）；

3、添加适当存储过程，触发器，事务等；

4、读写分离（主从数据库）；

5、对sql语句的一些优化，（查询执行速度比较慢的sql语句）；

6、分表分区（分表：把一张大表分成多张表。分区：把一张表里面的分配到不同的区域存储）；

7、对mysql服务器硬件的升级操作。

接下来我将具体讲解优化的方式。

一、三范式

第一范式：

原子性：表里面的字段不能再分割，只要是关系型数据库，就天然的自动满足第一范式

关系型数据库（有行和列的概念）：mysql、sql server、oracle、db2、infomix、sybase、postgresql，在设计时，先有库->表->字段->具体记录（内容）：在存储数据时，要设计字段。

非关系型数据库（泛指nosql数据库）：memcache、redis、momgodb等。

第二范式：

一个表中没有完全相同的记录，通过一个主键即能解决

第三范式：

表中不能存储冗余数据

反三范式设计：

如果要算一个相册的浏览量，我们可以在相册表中添加相册浏览量字段，浏览照片的时候同时更新相册浏览量。

二、开启慢查询

Mysql慢查询默认是关闭的，默认记录超过10秒的sql语句。

1.查看慢查询记录时间：

show variables like ‘long_query_time’;

2.修改慢查询时间：

set long_query_time=2;

3.通过如下的一个函数来进行测试：

benchmark(count,expr) 函数可以测试执行count次expr操作需要的时间

三、建立索引

1、主键索引的特点：

(1)一个表中最多只有一个主键索引

(2)一个主键索引可以指向多个列

(3)主键索引的列，不能有重复的值，也不能有null

(4)主键索引的效率高。

2、唯一索引的特点：

(1)一个表中可以有多个唯一索引

(2)一个唯一索引可以指向多个列，

(3)如果在唯一索引上，没有指定not null，则该列可以为空，同时可以有多个null,

(4)唯一索引的效率较高。

3、普通索引：

使用普通索引主要是提高查询效率

4、全文索引

mysql自带的全文索引mysql5.5不支持中文，支持英文，同时要求表的存储引擎是myisam。如果希望支持中文，有两个方案，

(1)使用aphinx中文版coreseek (来替代全文索引)

(2)插件mysqlcft。

添加索引主要的问题：

(1)较频繁的作为查询条件字段应该创建索引，唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，更新非常频繁的字段不适合创建索引

(2)不会出现在WHERE子句中字段不该创建索，索引是由代价的，虽然是查询速度提高了，但是，会影响增该删的效率。而且索引文件会占用空间。

四、分表、分区

垂直分表（内容主表+附加表）：

内容主表：存储各种数据的一些公共信息，比如数据的名称，添加时间等，

可以使用多个附加表，附加表存储一些数据的独特的信息。

主要原因：是内容主表里面的数据访问比较频繁。

特点：表结构不同

水平分表：

将表数据存在不同的表中。

特点：表结构相同

分区：

就是把一个表存储到磁盘不同区域，仍然是一张表。

基本的概念：

(1)Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

(2)List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。

(3)Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

(4)Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

分区表的限制：

(1)只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。

(2)最大分区数目不能超过1024。

(3)如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。

(4)按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多。

五、并发处理的锁机制

锁机制：在执行时，只有一个用户获得锁，其他用户处于阻塞状态，需要等待解锁。

mysql 的锁有以下几种形式：

表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎属于这种类型。

行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb属于这种类型。

表锁的演示：

1.对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的操作。

2.表添加读锁后，其他进程对该表只能查询操作，修改时会被阻塞。

3.当前进程，能够执行查询操作，不能执行修改操作。不能对没有锁定的表进行操作。

4.锁表的语法：

lock table 表名 read|write

5.也可以锁定多个表

6.对myisam表的写操作（加写锁），会阻塞其他进程对锁定表的任何操作，不能读写，

7.表加写锁后，则只有当前进程对锁定的表，可以执行任何操作。其他进程的操作会被阻塞。

行锁的演示：

1.innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb使用表锁。

2.开启行锁后，当前进程在针对某条记录执行操作时，其他进程不能操作和当前进程相同id的记录。

php里面有文件锁，在实际的项目中多数使用文件锁，因为表锁，会阻塞，当对一些表添加写锁后，其他进程就不能操作了。这样会阻塞整个网站，会拖慢网站的速度。

相关推荐：