MYSQL千万级数据量的优化方法积累(max_allowed_packet最大内存占用设置)
3、常用SQL语句优化：
- 数据库(表)设计合理
我们的表设计要符合3NF 3范式(规范的模式) , 有时我们需要适当的逆范式
- sql语句的优化(索引，常用小技巧.)
- 数据的配置(缓存设大)
- 适当硬件配置和操作系统 (读写分离.)
数据的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数据库支持把慢查询语句，记录到日志中，程序员分析. (但是注意，默认情况下不启动.)
步骤:
- 要这样启动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 (列...)
删除索引
- drop index 索引名 on 表名
- alter table 表名 drop index index_name;
- 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区别是什么
- MyISAM 不支持外键, Innodb支持
- MyISAM 不支持事务,不支持外键.
- 对数据信息的存储处理方式不同.（如果存储引擎是MyISAM的，则创建一张表，对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1）
对于 MyISAM 数据库，需要定时清理
optimize table 表名
l 常见的sql优化手法
- 使用order by null 禁用排序
比如 select * from dept group by ename order by null
- 在精度要求高的应用中，建议使用定点数(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 读写分离
Warning: PDO::query(): MySQL server has gone away in E:\WWW\test\test3.php on
line 170
Warning: PDO::query(): Error reading result set's header in E:\WWW\test\test3.PHP on line 170
当一次性插入2000条数据时 便报了此错误。
解决方法：
查看资料，发现了 max_allowed_packet 参数，
官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。
查看MySQL max_allowed_packet的值
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 1
- 2
- 3
- 4
- 5
- 6
可以看到是4M，然后调大为256M(1024*1024*256)
mysql> set global max_allowed_packet=268435456;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 268435456 |
+--------------------+-----------+
1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
修改后执行导入，一切正常，解决问题。
注意：
使用set global命令修改 max_allowed_packet 的值，重启mysql后会失效，还原为默认值。
如果想重启后不还原，可以打开 my.cnf 文件，添加 max_allowed_packet = 256M 即可。
注意事项：1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。
2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。
MySQL是目前使用很广泛的数据库，在使用过程中，有时我们会遇到一个与 "max_allowed_packet" 相关的报错，那如何解决这个问题呢？ 看如下经验。
方法/步骤
-
先看看与这个错误相关的后台异常 ：
Packet for query is too large (1706 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
一旦数据库出现这个问题，基本执行任何查询语句，后台都会报这个错！（除非查询所得数据量特别少）！
-
先登录数据库，查看 max_allowed_packet 这个参数值
连接数据库服务器，登录 mysql， 执行命令 ：
show variables like '%max_allowed_packate%'
即可查看这个系统参数目前的值 ！ 我的系统，目前显示只有 1024 字节，确实太小了！
-
-
-
通过 mysql 命令更改这个值到 100M ！
连接数据库服务器，登录 mysql， 执行命令 :
set global max_allowed_packet = 100*1024*1024
退出后，重新登录 mysql ，再次查看这个系统项的值， 注意，必须重新登录mysql，否则这个值还是显示为原始值（缓存）！
修改后，这个配置项显示为 100 M ！
-
-
-
-
修改配置文件--Windows
我们刚才的修改是针对运行环境，但如果我们的配置文件不变，则重启数据库后，这个值又恢复为原始值了，对于 Windows 下的 MySQL，我们需要修改其配置文件 my.ini , 这个文件在 MySQL 的安装根目录下。
注意: MySQL 配置文件分为几个段 (Section)， 包括 [mysql], [mysqld], [mysqldump] 等， 我们需要在 [mysqld] 段下添加配置项 ：
max_allowed_packet = 100M
-
-
-
-
修改配置文件--Linux
在 Linux 中，MySQL 对应的配置文件是 my.cnf , 我们在Linux终端输入如下命令 ：
mysql --help | grep my.cnf
可以查看 MySQL 所使用的 my.cnf 列表（优先级列表，先找到的配置文件生效），MySQL 会逐个目录寻找这个文件，直到找到为止！
我们逐个目录找到我们系统 MySQL 所使用的 my.cnf 配置文件，找到对应的配置项，修改为 100M 即可。
END
上一篇： java输入输出--文件操作
下一篇： Unity实现物体跟随鼠标移动