MySQL 优化总结

目录

Mysql的优化原则

SQL语言分为划分及操作关键字

通俗理解3NF

SQL语句优化

SQL优化的一般步骤

SQL语句优化-show参数

show status

显示连接数据库次数

显示慢查询

显示查看查询情况

开启慢日志

索引

索引原理

哪些列上适合添加索引

索引的使用

查询索引使用情况

SQL语句优化--explain分析问题

myisam和innodb区别

常用SQL优化

大数据SQL优化

优化group by语句

选择合适的数据类型

对大数据表优化

对表进行水平划分

对表进行垂直划分

文件、图片等大文件存储

数据库参数配置

合理的硬件资源和操作系统

读写分离

总结

---

 

Mysql的优化原则

1.数据库（表）设计合理

我们的表设计要符合3NF，但有时也需要适当逆范式

2.SQL语句优化

3.数据的配置

4.适当硬件配置和操作系统

SQL语言分为划分及操作关键字

SQL语言包括

数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询（DQL）四个部分。

数据定义：Create Table,Alter Table,Drop Table, Create/Drop Index等

数据操纵：insert,update,delete,

数据控制：grant,revoke

数据查询：select

通俗理解3NF

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可在分解。（只要是关系型数据库都满足1NF）

第二范式：2NF是对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性。

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

注意：

但是没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。

具体做法：在概念数据模型设计时遵循第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。（即我们的字段关系可以通过关联的关系，派生即可（通常我们通过外键来处理））

SQL语句优化

SQL优化的一般步骤

通过show status命令了解各种SQL的执行频率。

定位执行效率较低的SQL语句-(重点select)

通过explain分析低效率的SQL语句的执行情况

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

SQL语句优化-show参数

MySQL客户端连接成功后，通过使用show[session|global]status命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。

例子:show status like 'Com %’.

其中Com XXX表示XXX语句所执行的次数，重点注意:

Com_select,Com_insert,Com_update,Com_delete

通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主

还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

还有几个常用的参数便于用户了解数据库的基本情况。

Connections:试图连接MySQL服务器的次数

Uptime:服务器工作的时间(单位秒)

Slow_queries:慢查询的次数(默认是10)

show status

该命令可以显示你的 mysql数据库的当前状态,主要关心的是“com”开头的指令

show status like 'Com%’<=>  show session like 'Com%’//显示当前控制台的情况

show global status like ‘Com%'; //显示数据库从启动到 查询的次数

显示连接数据库次数

show connections;

显示慢查询

show status like 'Slow_queries’;

显示查看查询情况

Show variables like ‘long_query_time’;

慢查询默认10秒.

开启慢日志

编辑 MySQL 配置文件 my.cnf 或 my.ini，

通常位于 /etc/mysql 或 /usr/local/mysql/etc 目录下。

添加以下行：

slow_query_log = ON

slow_query_log_file = /var/log/mysql/mysql-slow.log

long_query_time = 10

其中：

slow_query_log 设置为 ON 以启用慢查询日志。

slow_query_log_file 指定慢查询日志文件的路径

long_query_time 设置执行时间超过此值的查询阈值（秒为单位）。

最后保存配置并重启 MySQL。

索引

索引原理

现实生活中图书馆对所有图书进行编号和分类及记录实际图书的位置。

索引可以加快我们查询速度，但这是以牺牲增删改为代价的。

哪些列上适合添加索引

较频繁的作为查询条件的字段应该创建索引

唯一性太差的字段不合适创建索引，即使频繁作为查询条件（如性别）

更新非常频繁的字段不合适创建索引

不会出现在where子句中的字段不应该创建索引

索引的使用

查询最重要的条件是查询条件中需要使用索引。

下列几种情况下有可能使用到索引：

1. 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2. or语句中所有条件都需要带有索引
3. 对于多列索引，没有使用第一列作为条件，也不会使用索引
4. 如果列类型是字符串，那一定要在条件中将数据使用引号包含，否则不使用索引。
5. Like查询以%开头
6. mysql使用全表扫描要比使用索引快，则不使用索引

查询索引使用情况

show status like ‘Handler_read%’

注意：

handler_read_key：表示使用索引查询到的次数，值越高越好。

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

SQL语句优化--explain分析问题

explain select * form emp where ename=“zrlcHd";

会产生如下信息:

select type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描的行数

Extra:执行情况的描述和说明

myisam和innodb区别

1. myisam表锁，不支持事务，易产生碎片要经常优化，读取速度较快，支持全文索引。
2. innodb行锁支持事务、外键，有崩溃恢复能力。
3. 对数据信息的存储处理方式不同。（存储引擎是myisam的，则创建一张表，存储三个文件。如果是innodb则只有一张文件 *.frm,数据存放到ibdata1）
4. 对于myisam数据库，需要定时清理（optimize table 表名）

常用SQL优化

大数据SQL优化

大批量插入数据

对于myisam:

Alter table table_name disable keys;

Loading data;

Alter table table_name enable keys;

对于innodb:

1. 将要导入的数据按照主键排序
2. set unique_checks=0,关闭唯一性校验
3. set autocommit=0,关闭自动提交

优化group by语句

默认情况，mysql对所有的group by vol1,col2进行排序。

这与在查询中指定order by col1,col2类似。

如果查询中包括group by但用户想要避免排序结果的消耗，

则可以使用order by null禁止排序。

有些情况下，可以使用连接来替代子查询

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

如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引。

选择合适的数据类型

在精度要求高的应用中，建议使用定点数（decimal）来存储数值，以保证结果的准确性

1000000.32万

create table sa1(t1 float(10,2));

create table sal2(t1 decimal(10,2))

对于存储引擎是myisam的数据库，如果经常做删除和修改记录的操作，要定时执行

optimize table table_name;功能对表进行碎片整理。

日期类型要根据实际需要选择能够满足应用的最小存储的早期类型

对大数据表优化

对表进行水平划分

如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能，那么把表按月拆分成12个，每个查询只查询一个表就够了。如果非要按照地域来分，即使把表拆的再小，查询还是要联合所有表来查，还不如不拆了。所以一个好的拆分依据是最重要的。UNION

对表进行垂直划分

有些表记录数并不多，可能也就2、3万条，但是字段却很长，表严重降低了性能。这占用空间很大，检索表时需要执行大量I/O，个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。(JOIN)类似于订单表和订单附表

文件、图片等大文件存储

数据库只存储路径。

图片和文件或者大的前端资源文件存放在文件系统，

OSS存储或者另外一个单独服务器（图床）

数据库参数配置

最重要的参数就是内存，我们主要用的innodb引擎，

所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M

Innodb_buffer_pool_size =1G

对于Myisam，需要调整key_buffer_size

当然调整参数要看状态，使用show status语句查看当前状态，以决定调整哪些参数

合理的硬件资源和操作系统

如果你的机器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql

读写分离

如果数据库压力很大，一台机器支撑不了，

就可以用mysql复制实现多台机器同步，将数据库的压力分散。

主库master用来写入，slave1-slave3都用来做select，每个数据库分担的压力小了很多。

要实现这种方式，需要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担（需要使用中间件来实现）。

总结

对于MySQL优化的一部分总结笔记。