本篇文章给大家带来的内容是关于mysql数据库优化的方法总结，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。
学无止境，数据库优化分为方方面面，在这里，我进行了比较全的总结，分享给正在工作或者学习的同行们。
数据库的优化分为以下七大方面：
1、表的设计要符合三范式（适当的反三范式也可以）；
2、添加适当的索引，索引对查询速度影响很大，必须添加索引（主键索引，唯一索引，普通索引，全文索引）；
3、添加适当存储过程，触发器，事务等；
4、读写分离（主从数据库）；
5、对sql语句的一些优化，（查询执行速度比较慢的sql语句）；
6、分表分区（分表：把一张大表分成多张表。分区：把一张表里面的分配到不同的区域存储）；
7、对mysql服务器硬件的升级操作。
接下来我将具体讲解优化的方式。
一、三范式
第一范式：
原子性：表里面的字段不能再分割，只要是关系型数据库，就天然的自动满足第一范式
关系型数据库（有行和列的概念）：mysql、sql server、oracle、db2、infomix、sybase、postgresql，在设计时，先有库->表->字段->具体记录（内容）：在存储数据时，要设计字段。
非关系型数据库（泛指nosql数据库）：memcache、redis、momgodb等。
第二范式：
一个表中没有完全相同的记录，通过一个主键即能解决
第三范式：
表中不能存储冗余数据
反三范式设计：
| ID | 相册名称 | 相册浏览量 |
| 1 | 生活照 | 100 |
| 2 | 工作照 | 100 |
| ID | 照片名称 | 相册ID | 浏览量 |
|---|---|---|---|
| 1 | 我的小狗 | 1 | 49 |
| 2 | 我的小猫 | 1 | 51 |
| 3 | 我的同事 | 2 | 100 |
如果要算一个相册的浏览量，我们可以在相册表中添加相册浏览量字段，浏览照片的时候同时更新相册浏览量。
二、开启慢查询
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里面有文件锁，在实际的项目中多数使用文件锁，因为表锁，会阻塞，当对一些表添加写锁后，其他进程就不能操作了。这样会阻塞整个网站，会拖慢网站的速度。
相关推荐：
