MySQL优化自学手册

/*

* ------------------------------------------------------------------- * |-标题：MySQL优化自学手册

* |-整理: 杨白玉

* |-时间: 2015年9月25日

* ------------------------------------------------------------------- */

mysql优化

前提：数据库性能的优劣直接影响到程序的性能，所以数据库的设计与参数配置至关重要。

数据库优化的方式：

1、数据库设计

2、sql语句的优化

3、数据库参数的配置（扩展数据库的缓存或者数据库的空间）

4、恰当的硬件资源（钱的问题，有钱就能满足）

第一章数据库的设计

一、数据库的设计：

数据库的设计指的就是表的设计。设计要符合三范式（规范的模式），有时我们也需要适当的逆范式；

二、什么是三范式？

第一范式：1NF是对属性（可理解为字段）的原子性约束，要求属性具有原子性，不可再分。第二范式：2NF是对记录的唯一性约束，要求记录有唯一的标识，即实体的唯一性；

第三范式：3NF是对字段冗余的约束，即任何字段不能由其他字段派生出来，要求字段没有冗余，这是可以做到的。

然而，没有冗余的数据库未必是好的数据库，有时候为了提高运行的效率，我们也会使用适当的逆范式，方法就是：增加字段。

一般来说，1NF在关系型数据库中是自动满足的；

2NF通常通过主键自增的唯一性来约束。而且，记录本身也很少会完全一样；

3NF主要是在主从表中，不会出现相同的字段与字段值；

第二章 SQL语句的优化

一、SQL语句优化的步骤：

1、通过show status 命令了解各种sql的执行频率；

2、定位执行效率较低的SQL语句，主要集中在查询语句

3、通过explain分析低效率的sql语句的执行情况

4、确定问题并采取相应的优化措施

二、sql语句有几类？

ddl(数据定义语句)[create alter drop]

dml(数据操作语句)[insert delete update]

select

dtl(数据事物语句)[commit rollback savepoint]

dcl(数据控制语句)[grant revoke]

show status命令

该命令可以显示mysql数据库当前的状态，我们主要重点关注“Com”开头的指令。

1、显示数据库开启本次会话后到目前的信息：

show status like “Com%”; <=> show session status like “Com%”;

2、显示数据库从启动到目前的信息：

Show global status like “Com%”;

说明：通过上述命令，可以很容易的了解到当前的数据库是以插入更新为主还是以查询为主，以及各类sql大致的执行比例是多少。

3、显示连接数据库的次数，如果并发高的话，就需要考虑并发的问题：

show status like “Connections”;

4、mysql服务器工作的时间（单位是秒）

show status like “Uptime”;

5、慢查询

(1)、定位慢查询的次数（默认是10秒）

show status like “Slow_queries”;

(2)、慢查询是我们优化的重点，默认情况下是10s；

show variables like “long_query_time”;

(3)、设置慢查询的时间：

set long_query_time=值;

(4)、查询慢查询的次数：

show status like “slow%”;

分析：如何在项目中，找到慢查询的select语句，mysql数据库支持把慢查询语句，记录到日志中供程序员分析，默认是不启用的；

方法：

进入到mysql安装目录，切换到mysql的bin目录下，使用mysqld.exe启动；

命令：XX>mysqld.exe -show-query-log

知识扩展：如果你的数据库存储引擎是MyISAM的，则当创建一个表后，再磁盘中会生成3个文件：*.frm 记录表结构| *.myd 数据| *.myi 索引

数据库优化方式一：添加索引

1、索引介绍

对于提高数据库的性能，索引是绝佳选择，无需增加硬件、改变程序、调整sql语句，就能达到数据库优化的效果；然而，索引虽然提高了数据的查询效率，但却是以插入、更新、删除的速度为代价的，增加了大量的I/O，具体原因如下：

我们知道，索引是保存在*.myi文件中进行维护的，索引类似字典的目录，在查询的时候可以快速定位，而不需要去逐条查询，这明显加快了查询效率；然而数据库在每次执行增、删、改操作时，*.myi文件对应的每次需要去更新维护索引信息，这无疑就增加了I/O;

2、索引的类型

(1)、主键索引(primary key，一张表只能有一个主键)

(2)、唯一索引(unique，建表的同时设置，是没有效果的，需要建好表后，单独添加该索引)

(3)、普通索引(index)

(4)、全文索引(fulltext，只有myisam存储引擎支持)，sphinx+中文分词

(5)、复合索引(多列组合在一起，很少使用；格式：alter table 表名add index 索引名(字段1，

字段2...) )

3、索引的使用方式

3-1-1：添加主键索引：

alter table 表名add primary key(字段);

3-1-2：删除主键索引：

alter table 表名drop primary key;

3-2-1：创建“唯一索引、普通索引、全文索引”的方式：

carete [unique][fulltext][index] 索引名on 表名(字段);

alter table 表名add [unique][fulltext][index] 索引名(字段);

3-2-2：删除“唯一索引、普通索引、全文索引”的方式：

drop [unique][fulltext][index] 索引名on 表名;

alter table 表名drop [unique][fulltext][index] 索引名;

3-3-1：查询索引

show index from 表名;

show keys form 表名；

desc 表名；

4、使用索引的注意事项

4-1：对于使用like的查询，查询条件如果是”%查询关键词”不会使用到索引；

4-2：如果查询条件中有or，即使其中有条件带索引，其实也不会使用；

4-3：对于复合索引，只要查询条件使用了最左边的索引列，索引一般就会被使用；如果查询条件的索引不是使用的最左边的索引列，则不会使用索引；

4-4：如果字段的类型是字符串，那么一定要在查询条件中把查询关键词用引号引起来，否则不会使用到索引。

4-5：如果mysql估计使用全表扫描要比索引更快，则自动忽略不适用索引；

5、如何检测索引是否有效

命令：show status like “handler_read%”;

返回参数说明：

handler_read_key ：这个值越高越好，越高表示使用索引查询到的次数越多；

handler_read_rnd_next ：这个值越高，说明查询效率越低；

6、explain分析语句

格式：explain 查询语句\G

返回结果：

id SQL语句会使用的索引

select_type SIMPLE：普通查询

table 表名

type ALL：全表扫描，性能最次；system：系统表；const：表最多有一个匹配行

possible_keys 可能用到的索引类型（其中NULL表示无索引类型）

key 实际用到的索引类型（其中NULL表示无索引）

key_len 索引的长度（其中NULL表示索引长度为零，其实就是没有索引）

ref 无实际意义

rows 本次查询检索了多少条记录

Extra 查询细节信息

7、myisam和innodb的区别是什么？

(1)、myisam不支持外键，innodb支持；

(2)、myisam不支持事务，innodb支持；

(3)、对数据的保存方式不同：如果存储引擎是myisam，则创建一张表的时候，分别生成3个文件：*.frm | *.myd | *.myi；如果存储引擎是innodb，则创建一张表的时候，只生成一个文件：*.frm，而数据则存储在xxx/data/ibdata1文件中；

(4)、myisam存储引擎，在数据删除后，*.MYD文件大小并不会变小（即空间不会自动回收），随着*.MYD文件无限膨胀，最终结果会导致磁盘开销达到峰值，数据无法继续添加，这对于QQ等这等类大批量增删操作频繁的软件，很要命；所以，对于使用myisam存储引擎的数据

表，需要定期使用指令：“optimize table 表名;”进行空间回收；

(5)、myisam的查询速度要比innodb更快；

8、对于使用索引后的大批量插入数据，需要进行如下操作：

8-1：如果存储引擎是myisam：

//第一步：禁用索引，防止随着数据一条一条录入，而索引也一条一条添加；

alter table 表名display keys;

//第二步：大批量增加数据

loading data;

//第三步：启用索引，此时索引会自动给新增的数据添加索引

alter table 表名enable keys;

8-2：如果存储引擎是innodb：

//第一步：将需要导入的数据按照主键排序；

//第二步：关闭唯一性校验；

set unique_checks=0;

//第三步：关闭自动提交；

set autocommit=0

◆数据库优化方式二：group by语句中禁用order by

主要针对group by语句；

默认情况下，mysql对所有的group by col1,col2…都会进行进行文件排序，这个与在查询中使用order by col1,col2…类似。这明显是影响查询效率的，所以我们需要在使用group by 语句的同时，使用order by null禁止排序；

格式如下：select * from 表group by 字段order by null;

◆数据库优化方法三：尽量使用join查询代替子查询

因为使用join查询，mysql不需要在内存中创建临时表。

◆数据库优化方法四：

如果想在or的查询语句中利用索引，那么在or的每个条件都必须增加索引，否则不会使用到索引；

数据库优化方法五：字段类型、长度尽量使用恰当的类型、确定的存储数值，以保证结果的准确性；

比如：md5加密后的数据，长度为32；所以在保存用户密码的时候，只用char优于varchar;

第三章表的垂直分割和水平分割

一、水平分割

假设一张表中有100亿条QQ账号记录，我们可以把这一张表划分成100张，这100张表的字段完全一样。表然后每张表中只存储1亿条记录，这100张表的表名格式为：表名0、表名1、表名2…表名99；

说明：

表名后边的0、1、2…99这些标号是来源于：当前记录的ID%100来计算得知，范围为:0~99;

然后在添加记录的时候，根据当前记录的ID%100得到的值来决定往哪张表中添加记录；读取的时候也是同理；

原理图如下：

二、垂直分割：

主要方式：把一张表的字段分成几张表来保存；垂直分割比较常见，不再赘述；

第四章读写分离技术

读写分离的原理：

目的：给大型网站减轻查询压力；

做法及原理：在服务器端安装amoeba服务器(新浪自己做的)，这个工具可以自动判断sql语句是dml语句还是select语句；如果是dml语句，自动操作master主数据库，然后主数据库会通过mysql proxy把数据自动同步到slave从数据库；如果是select语句，就自动找一个当前压力比较小的slave从数据库进行查询操作，然后将结果返回，这就是读写分离技术；

原理图：

amoeba的汉语读法：爱魔棒

另外需要注意的是：在数据库读写分离技术中，数据库集群都是单独装到一太服务器上的；

知识扩展：Mysql.ini的有效配置文件内容：