MYSQL千万级数据量的优化方法积累

转自：http://blog.sina.com.cn/s/blog\_85ead02a0101csci.html

1划分数据库和表

很明显，主表（即非常重要的表，如用户表）的无限制增长将严重影响性能。数据库和表分区是一个很好的解决方案，这是一种性能优化方法。目前的情况是，我们有一个1000超过一万条记录的用户表members,查询非常慢，我同事的方法是将其散列到100分别从表中members0到members99然后，根据mid将记录分发到这些表中，Niubi的代码大致是这样的：

<?php

for($i=0;$i< 100; $i++ ){

//echo "CREATE TABLE db2.members{$i} LIKE db1.members
";

echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid0={$i}
";

}

?>

2在不停止机器的情况下进行改装mysql表结构

同样还是members前期设计的表结构并不完全合理，随着数据库的持续运行，其冗余数据也明显增加。我的同事使用以下方法来处理它：

首先创建一个临时表：

CREATE TABLE members_tmp LIKE members

然后修改members_tmp表结构是新结构，然后使用上面的结构for循环导出数据，因为1000一次导出一万的数据是不正确的，mid它是一个主键，一个区间的导数，基本上是一次性导出5一万块，我把它留在这里

然后重命名新表并将其替换为：

RENAME TABLE members TO members_bak,members_tmp TO members;

就是这样，基本上不需要停机更新表结构就可以实现任何损失，但实际上RENAME周期表是锁定的，因此选择在线低时操作是一项技能。经过此操作，原始8G有这么多手表，它突然变成了2G多

还提到mysql中float使用字段类型时出现的奇怪现象是pma中看到的数字根本不能用作查询条件

3、常用SQL语句优化：

1. 数据库 ( 表 ) 设计合理

我们的手表设计应符合 3NF 3 范式 ( 标准化模式 ) , 有时我们需要适当的逆范式

2. sql 语句优化 ( 索引，常用提示 .)

3. 数据的配置 ( 缓存设大 )

4. 适当的硬件配置和操作系统 ( 读写分离 .)

数据的 3NF

1NF : 它是原子性的，不可分割的 .( 只要使用关系数据库，它就会自动符合 )

2NF: 在满足 1NF 基于此，我们考虑是否满足要求 2NF: 只要表的记录满足唯一性 , 也是说 , 同一表不能有相同的记录 , 一般来说，我们是 在表中设计主键 .

3NF: 在满足 2NF 基于此，我们考虑是否满足要求 3NF 我们的现场信息可以通过相关关系获得 .( 通常，我们通过外键处理它 )

逆范式 : 为什么我们需要颠倒正常形式 :

( 相册功能对应的数据库设计 )

适当的逆范式 .

sql 语句优化

sql 有多少种类型的语句

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

dml( 数据操作语言 )[insert delete upate ]

select

dtl( 数据交易声明 ) [commit rollback savepoint]

dcl( 数据控制声明 ) [grant revoke]

show status 命令

此命令可以显示您的 mysql 数据库的当前状态 . 我们主要关心的是 “com” 开头的说明

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

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

显示连接到数据库的次数

show status like Connections;

我们在这里优化的重点是 慢查询 . ( 默认情况下，它是 10 ) mysql5.5.19

显示查看慢查询的情况

show variables like ‘long_query_time’

出于教学目的，我们将创建一个庞大的表格 (mysql 存储过程 )

目的是看看如何处理它。在海量表中，查询速度非常快 !

select * from emp where empno=123456;

要求：如何在项目中查找慢查询 select , mysql 数据库支持在日志中记录慢查询语句，用于程序员分析 . ( 但是，请注意，默认情况下它不会启动 .)

步骤 :

1. 像这样开始 mysql

进入到 mysql 安装目录

2. 启动 xx>bin\mysqld.exe –slow-query-log 这点注意

测试 , 例如，我们可以

select * from emp where empno=34678 ；

用了 1.5 秒，我现在正在优化 .

快速体验 : 在 emp 表的 empno 建立索引 .

alter table emp add primary key(empno);

// 删除主键索引

alter table emp drop primary key

然后，再次检查以使其更快 .

l 索引原则

介绍一个非常重要的工具 explain, 该分析工具可以分析 sql 分析语句 , 可以预测您的 sql 执行效率 .

他的基本用法是 :

explain sql 语句 \G

// 根据返回的信息，我们知道 , 该 sql 语句是否使用索引以及要检索的记录数 , 您可以看到排序方法 .

l 在哪一列上添加索引更合适

① 向经常查询的列添加索引 .

② 列的数据仅包含几个值 , 不适合索引 .

③ 内容变化频繁，不适合索引

l 索引的类型

① 主键索引 ( 将列设置为主键是主键索引 )

② 唯一索引 (unique) （即列具有唯一性，也是索引）

③ index （正常指数）

④ 全文索引 (FULLTEXT)

select * from article where content like ‘% 李连杰 %’;

hello, i am a boy

l 你好，我是男孩 => 中文 sphinx

⑤ 复合索引 ( 将多个列合并在一起 )

create index myind on 表名 ( 列 1, 列 2);

l 如何创建索引

如果创建 unique / 普通 /fulltext 索引

1. create [unique|FULLTEXT] index 索引名 on 表名 ( 列名 ...)

2. alter table 表名 add index 索引名 ( 列名 ...)

// 如果要添加主键索引

alter table 表名 add primary key ( 列 ...)

删除索引

1. drop index 索引名 on 表名

2. alter table 表名 drop index index_name;

3. alter table 表名 drop primary key

显示索引

  show index(es) from 表名

  show keys from 表名

  desc 表名

如何查询表的索引

show indexes from 表名

l 使用索引的注意事项

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

索引可用于以下情况：1 对于创建的多列索引，只要查询条件使用最左侧的列，一般都会使用索引。2 对于使用 like 如果查询是 ‘ �a ’ 不会使用索引 aaa% ’ 将使用索引。

下表不使用索引：1 如果有条件 or 即使其中有条件索引，也不会使用。2 对于多列索引，如果未使用第一部分，则不会使用该索引。3 ， like 查询是以 % 开头4 如果列类型为字符串，请确保使用引号引用条件中的数据。否则，将不会使用索引。5 ，如果 mysql 如果估计使用全表扫描比使用索引快，则不要使用索引。

l 如何检查索引是否有效

结论 : Handler_read_key 越大越少

Handler_read_rnd_next 越小越好

fdisk

find

l MyISAM 和 Innodb 有什么区别

1. MyISAM 不支持外键 , Innodb 支持
2. MyISAM 不支持事务 , 不支持外键 .
3. 数据信息的不同存储和处理方法 . 如果存储引擎是 MyISAM 为三个文件创建一个表 .., 如果是 Innodb 然后只有一个文件 *.frm, 数据存储到 ibdata1 ）

对于 MyISAM 数据库需要定期清理

optimize table 表名

l 常见的 sql 优化手法

1. 使用 order by null 禁用排序

比如 select * from dept group by ename order by null

2. 在精度要求较高的应用中，建议使用定点数 (decimal) 存储数值以确保结果的准确性

3. 如果字段是字符类型索引，请确保在将其用作条件查询时添加它 单引号 否则，索引无效。

4. 如果未使用主键索引，请再次查询 for update 这种情况可能会导致表锁定。容易造成干扰。

1000000.32 万

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

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

问 ? 在 php 中 ,int 如果是有符号数字，则最大值 . int- 4*8=32 2 31 -1

l 表格的水平划分

l 垂直拆分表

如果数据库的存储引擎是 MyISAM 创建表时，最后三个文件 . .frm 记录表结构 . .myd 数据 *.myi 这是索引 .

mysql5.5.19 默认情况下，他的数据库文件的版本放在 （看 my.ini 文件中的配置 . ）

l 读写分离