笔记mysql优化
数据库优化工作
1 数据库（表）设计合理
我们的表设计要符合3NF 3范式 有时需要适当的逆范式
2 sql语句的优化
3 数据库参数修改，配置
4 适当的硬件配置和操作系统
数据的 3NF
1NF 就是具有原子性，不可分割（只要使用的是关系型数据库，自动符合）
2NF 在满足1NF的基础上，我们考虑是否满足2NF，只要表的记录满足唯一性，也是说，你的同一张表，不可能出现完全相同的记录，一般说我们在表中设计一个主键既可。
3NF 在满足2NF的基础上，我们考虑是否满足3NF，即我们的字段信息可以通过关联的关系，派生即可（通常我们通过外键来处理）
逆范式举例：
相册功能举例：（正确逆范式举例）
（错误逆范式举例）
sql语句的优化
面试题：sql语句有几类
DDL（数据定义语言（define））（create alter drop）
DML（数据操作语言）（insert delete update）
SELECT （单独）
DTL（数据事物语句）（commit rollback savepoint）
DCL（数据控制语句）（grant revoke）
show status 命令
该命令可以显示你的mysql数据库的当前状态，我们主要关系的是“com”开头的指令
mysql> show status like "Com%"; <=>show session status like "Com%";
//显示当前操作台对数据库的操作次数
mysql>show global status like "Com%"; //显示数据库从启动带现在的操作次数
mysql> create table tem(id int)engine=MyISAM;
显示连接数据库次数
mysql> show status like 'Connections';
显示服务器工作的时间（单位秒）
mysql> show status like 'Uptime';
显示慢查询的次数（默认是10）
mysql> show status like 'Slow_queries';
可以自定义慢查询时间
我们优化的重点在 慢查询 （默认是10s）
修改默认的慢查询时间： mysql> set long_query_time=1;
显示慢查询的情况
mysql> show variables like "long_query_time";
建立一个海量表：
压力测试脚本
mysql> set names gbk;
#定义一个新的命令结束符合
delimiter $$
在海量表中，查询速度很快：
mysql> select * from emp where empno=123456;
需求：如何在一个项目中，找到慢查询的select， mysql数据库支持包慢查询语句，记录到日志中，供程序员分析。（但是注意：默认情况下，不启用）
1，要这样启动mysql
进入到mysql安装目录bin
2，启动 xx>bin\mysqld.exe –slow-query-log
mysql> set long_query_time=1; 修改默认慢查询时间
快速体验，在表里面加索引
(增加索引，select变快，但是insert，update， delete变慢)
(磁盘占用)
索引原理：
建立主键索引：
mysql> alter table emp add primary key (empno);
删除索引：
mysql> alter table emp drop primary key;
介绍一款非常重要工具explain，这个分析工具可以对sql语句进行分析，可以预测你的sql执行效率。
分析：
mysql> explain select *from emp where empno=456723\G;
执行的效率.
他的基本用法是:
explain sql语句\G
//根据返回的信息，我们可知,该sql语句是否使用索引，从多少记录中取出,可以看
到排序的方式.
如果你的数据库的存储引擎室MyISAM的，则当创建一个表后，三个文件：*frm 记录表结构 *myd 数据 *myi 索引
在什么列上添加索引：
1，比较频繁的作为查询条件字段应该创建索引（经常查询的列）
select *from emp where empno =1;
2，唯一性 太差 的字段不适合创建索引，即使作为频繁的查询条件
select *from emp where sex='男'；
3，更新比较频繁的，不合适加索引
select *from emp where logincount = 1;
4，不会出现在where字句中字段不该创建索引
索引的种类
1，主键索引 （把某列或者多列设为主键，则就是主键索引）
2，唯一索引 （unique）（即该列具有唯一性，同时又是索引）
3，index （普通索引）
4，全文索引（FULLTEXT）（英文中使用）（只有MyISAM引擎支持）
sphinx+中文分词 coreseek （中文中使用）
（文章管理系统）
select *from where content like "%成龙%"；
5，综合使用=>复合索引 （多列合在一起使用）
create index myind on 表名 （列1，列2）；
查看某表的索引
mysql> show indexes from aa\G;
索引管理
1，如果创建 unique/index/fulltext 索引
create unique/index/fulltext 索引名 on 表名 （列名）
alter table [table_name] add INDEX [index_name] ([column_name]);
//如果添加主键索引
alter table [table_name] add primary key [column_name];
2，删除索引
drop index [index_name] on [table_name];
alter table [table_name] drop index [index_name];
alter table [table_name] drop primary key;
3，显示索引
show index(es) from [table_name];
show keys from [table_name];
desc [table_name];
使用索引的注意事项
查询要使用索引最重要的条件是查询条件中需要使用索引。
下列几种情况下有可能使用到索引：
1，对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2，对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引
‘aaa%’ 会使用到索引。
下列的表将不使用索引：
1，如果条件中有or，即使其中有条件带索引也不会使用。
2，对于多列索引，不是使用的第一部分，则不会使用索引。
3，like查询是以%开头
4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
如何检测你的索引是否有效
结论: Handler_read_key 越大越少
Handler_read_rnd_next 越小越好
常用的SQL优化
大批量插入数据
对于MyISAM
alter table table_name disable keys; （keys不可用后再导入数据）
loading data;
alter table table_name enable keys;
对于Innodb
1， 将要导入的数据按照主键排序
2， set unique_hcecks=0, 关闭唯一性校验。
3， set autocommit=0， 关闭自动提交
常用的SQL优化
大批量插入数据
对于MyISAM
alter table table_name disable keys; （keys不可用后再导入数据）
loading data;
alter table table_name enable keys;
对于Innodb
1， 将要导入的数据按照主键排序
2， set unique_hcecks=0, 关闭唯一性校验。
3， set autocommit=0， 关闭自动提交
MyISAM和Innodb区别是什么
1，MyISAM不支持外键，Innodb支持
2，MyISAM不支持事务，不支持外键
3，对数据信息的存储处理方式不同 （如果存储引擎室MyISAM的，则创建一张表，对应三个文件。如果是Innodb则创建一张表，对应一个文件 *.frm,数据存放在ibdata1）
对于MyISAM数据库，需要定时清理
mysql> optimize table user; (清理user表)
常见的sql优化手法
1，使用order by null 禁用排序
mysql> select *from dept group by dname order by null;（groupby 默认排序）
2，有些情况下，可以使用连接来替代子查询。
因为使用囧，MySQL不需要再内存中创建临时表
3，如果想要在含有or的查询语句中使用索引，则or之间的每个添加列都
必须要到索引，如果没有索引，则应该考虑增加索引
select *from [table_name] where 条件1 = ‘’or 条件2 =‘’；
4，选择合适的存储引擎，MyISAM与Innodb区别
MyISAM：以读操作也插入操作为主，很少更新和删除操作，并且对于事务的完整性，并发性要求不是很高，其优势是访问的速度快。
InnoDB：提供了具有提交，回滚和崩溃恢复能力的事务安装，但是对比MyISAM，写的处理效率差一些，并且会占用更多的磁盘空间。
5，在精度要求高的应用中，建议使用定点数（decimal）来存储数值，以保证结果的准确性。（特别对于财务系统）
100000.32万
create table sal(t1 float(10.2)); (不使用浮点数)
create table sal(t1 decimal(10.2)); (使用decimal)
6，对于使用存储引擎室MyISAM的数据库，如果经常做删除和修改记录的操作，要定时执行 optimize table [table_name]; 功能：对整个表进行碎片整理。
7，日期类型要根据实际需要选择能够满足应用的最小存储的早期类型。
create table bbs (id int, con varchar(1024), pub_time int);
使用数值存时间，使用date函数转换即可
date（‘Ymd’, 时间-3*24*60*60）
问?在php中 ,int 如果是一个有符号数，最大值. int- 4*8=32 2 的31次方 -1
表的水平划分
表的垂直分割
【试题内容】、【答案信息】两个表，最初是作为几个字段添加到【试题信息】里的，可以看到试题内容和答案这两个字段很长，在表里有3万记录时，表已经占 了1G的空间，在列试题列表时非常慢。经过分析，发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件，分页显示试题详细内容。而每 次检索都是这几个表做join，每次要扫描一遍1G的表。我们完全可以把内容和答案拆分成另一个表，只有显示详细内容的时候才读这个大表，由此 就产生了【试题内容】、【答案信息】两个表。
选择合适的字段类型，特别是主键
原则：保小不保大， 能用小的就不用大的
几个表做join时，效果就更明显了。
es1234 , 建议使用一个不含业务逻辑的id做主角
文件、图片等大文件用文件系统存储
数据库只存储路径。图片和文件存放在文件系统，甚至单独放在一台服务器(图床).
数据库参数配置
最重要的参数就是内存，
innodb引擎：所以下面两个参数调的很大
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G
myisam引擎：需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数
合理的硬件资源和操作系统
1，如果你的机器内存超过4G，那么，应当采用64位的操作系统和64位的mysql
2，读写分离
如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。
数据库优化工作
1 数据库（表）设计合理
我们的表设计要符合3NF 3范式 有时需要适当的逆范式
2 sql语句的优化
3 数据库参数修改，配置
4 适当的硬件配置和操作系统
数据的 3NF
1NF 就是具有原子性，不可分割（只要使用的是关系型数据库，自动符合）
2NF 在满足1NF的基础上，我们考虑是否满足2NF，只要表的记录满足唯一性，也是说，你的同一张表，不可能出现完全相同的记录，一般说我们在表中设计一个主键既可。
3NF 在满足2NF的基础上，我们考虑是否满足3NF，即我们的字段信息可以通过关联的关系，派生即可（通常我们通过外键来处理）
逆范式举例：
相册功能举例：（正确逆范式举例）
（错误逆范式举例）
sql语句的优化
面试题：sql语句有几类
DDL（数据定义语言（define））（create alter drop）
DML（数据操作语言）（insert delete update）
SELECT （单独）
DTL（数据事物语句）（commit rollback savepoint）
DCL（数据控制语句）（grant revoke）
show status 命令
该命令可以显示你的mysql数据库的当前状态，我们主要关系的是“com”开头的指令
mysql> show status like "Com%"; <=>show session status like "Com%";
//显示当前操作台对数据库的操作次数
mysql>show global status like "Com%"; //显示数据库从启动带现在的操作次数
mysql> create table tem(id int)engine=MyISAM;
显示连接数据库次数
mysql> show status like 'Connections';
显示服务器工作的时间（单位秒）
mysql> show status like 'Uptime';
显示慢查询的次数（默认是10）
mysql> show status like 'Slow_queries';
可以自定义慢查询时间
我们优化的重点在 慢查询 （默认是10s）
修改默认的慢查询时间： mysql> set long_query_time=1;
显示慢查询的情况
mysql> show variables like "long_query_time";
建立一个海量表：
压力测试脚本
mysql> set names gbk;
#定义一个新的命令结束符合
delimiter $$
在海量表中，查询速度很快：
mysql> select * from emp where empno=123456;
需求：如何在一个项目中，找到慢查询的select， mysql数据库支持包慢查询语句，记录到日志中，供程序员分析。（但是注意：默认情况下，不启用）
1，要这样启动mysql
进入到mysql安装目录bin
2，启动 xx>bin\mysqld.exe –slow-query-log
mysql> set long_query_time=1; 修改默认慢查询时间
快速体验，在表里面加索引
(增加索引，select变快，但是insert，update， delete变慢)
(磁盘占用)
索引原理：
建立主键索引：
mysql> alter table emp add primary key (empno);
删除索引：
mysql> alter table emp drop primary key;
介绍一款非常重要工具explain，这个分析工具可以对sql语句进行分析，可以预测你的sql执行效率。
分析：
mysql> explain select *from emp where empno=456723\G;
执行的效率.
他的基本用法是:
explain sql语句\G
//根据返回的信息，我们可知,该sql语句是否使用索引，从多少记录中取出,可以看
到排序的方式.
如果你的数据库的存储引擎室MyISAM的，则当创建一个表后，三个文件：*frm 记录表结构 *myd 数据 *myi 索引
在什么列上添加索引：
1，比较频繁的作为查询条件字段应该创建索引（经常查询的列）
select *from emp where empno =1;
2，唯一性 太差 的字段不适合创建索引，即使作为频繁的查询条件
select *from emp where sex='男'；
3，更新比较频繁的，不合适加索引
select *from emp where logincount = 1;
4，不会出现在where字句中字段不该创建索引
索引的种类
1，主键索引 （把某列或者多列设为主键，则就是主键索引）
2，唯一索引 （unique）（即该列具有唯一性，同时又是索引）
3，index （普通索引）
4，全文索引（FULLTEXT）（英文中使用）（只有MyISAM引擎支持）
sphinx+中文分词 coreseek （中文中使用）
（文章管理系统）
select *from where content like "%成龙%"；
5，综合使用=>复合索引 （多列合在一起使用）
create index myind on 表名 （列1，列2）；
查看某表的索引
mysql> show indexes from aa\G;
索引管理
1，如果创建 unique/index/fulltext 索引
create unique/index/fulltext 索引名 on 表名 （列名）
alter table [table_name] add INDEX [index_name] ([column_name]);
//如果添加主键索引
alter table [table_name] add primary key [column_name];
2，删除索引
drop index [index_name] on [table_name];
alter table [table_name] drop index [index_name];
alter table [table_name] drop primary key;
3，显示索引
show index(es) from [table_name];
show keys from [table_name];
desc [table_name];
使用索引的注意事项
查询要使用索引最重要的条件是查询条件中需要使用索引。
下列几种情况下有可能使用到索引：
1，对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2，对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引
‘aaa%’ 会使用到索引。
下列的表将不使用索引：
1，如果条件中有or，即使其中有条件带索引也不会使用。
2，对于多列索引，不是使用的第一部分，则不会使用索引。
3，like查询是以%开头
4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
如何检测你的索引是否有效
结论: Handler_read_key 越大越少
Handler_read_rnd_next 越小越好
常用的SQL优化
大批量插入数据
对于MyISAM
alter table table_name disable keys; （keys不可用后再导入数据）
loading data;
alter table table_name enable keys;
对于Innodb
1， 将要导入的数据按照主键排序
2， set unique_hcecks=0, 关闭唯一性校验。
3， set autocommit=0， 关闭自动提交
常用的SQL优化
大批量插入数据
对于MyISAM
alter table table_name disable keys; （keys不可用后再导入数据）
loading data;
alter table table_name enable keys;
对于Innodb
1， 将要导入的数据按照主键排序
2， set unique_hcecks=0, 关闭唯一性校验。
3， set autocommit=0， 关闭自动提交
MyISAM和Innodb区别是什么
1，MyISAM不支持外键，Innodb支持
2，MyISAM不支持事务，不支持外键
3，对数据信息的存储处理方式不同 （如果存储引擎室MyISAM的，则创建一张表，对应三个文件。如果是Innodb则创建一张表，对应一个文件 *.frm,数据存放在ibdata1）
对于MyISAM数据库，需要定时清理
mysql> optimize table user; (清理user表)
常见的sql优化手法
1，使用order by null 禁用排序
mysql> select *from dept group by dname order by null;（groupby 默认排序）
2，有些情况下，可以使用连接来替代子查询。
因为使用囧，MySQL不需要再内存中创建临时表
3，如果想要在含有or的查询语句中使用索引，则or之间的每个添加列都
必须要到索引，如果没有索引，则应该考虑增加索引
select *from [table_name] where 条件1 = ‘’or 条件2 =‘’；
4，选择合适的存储引擎，MyISAM与Innodb区别
MyISAM：以读操作也插入操作为主，很少更新和删除操作，并且对于事务的完整性，并发性要求不是很高，其优势是访问的速度快。
InnoDB：提供了具有提交，回滚和崩溃恢复能力的事务安装，但是对比MyISAM，写的处理效率差一些，并且会占用更多的磁盘空间。
5，在精度要求高的应用中，建议使用定点数（decimal）来存储数值，以保证结果的准确性。（特别对于财务系统）
100000.32万
create table sal(t1 float(10.2)); (不使用浮点数)
create table sal(t1 decimal(10.2)); (使用decimal)
6，对于使用存储引擎室MyISAM的数据库，如果经常做删除和修改记录的操作，要定时执行 optimize table [table_name]; 功能：对整个表进行碎片整理。
7，日期类型要根据实际需要选择能够满足应用的最小存储的早期类型。
create table bbs (id int, con varchar(1024), pub_time int);
使用数值存时间，使用date函数转换即可
date（‘Ymd’, 时间-3*24*60*60）
问?在php中 ,int 如果是一个有符号数，最大值. int- 4*8=32 2 的31次方 -1
表的水平划分
表的垂直分割
【试题内容】、【答案信息】两个表，最初是作为几个字段添加到【试题信息】里的，可以看到试题内容和答案这两个字段很长，在表里有3万记录时，表已经占 了1G的空间，在列试题列表时非常慢。经过分析，发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件，分页显示试题详细内容。而每 次检索都是这几个表做join，每次要扫描一遍1G的表。我们完全可以把内容和答案拆分成另一个表，只有显示详细内容的时候才读这个大表，由此 就产生了【试题内容】、【答案信息】两个表。
选择合适的字段类型，特别是主键
原则：保小不保大， 能用小的就不用大的
几个表做join时，效果就更明显了。
es1234 , 建议使用一个不含业务逻辑的id做主角
文件、图片等大文件用文件系统存储
数据库只存储路径。图片和文件存放在文件系统，甚至单独放在一台服务器(图床).
数据库参数配置
最重要的参数就是内存，
innodb引擎：所以下面两个参数调的很大
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G
myisam引擎：需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数
合理的硬件资源和操作系统
1，如果你的机器内存超过4G，那么，应当采用64位的操作系统和64位的mysql
2，读写分离
如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29734436/viewspace-1280687/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
mysql 性能优化--基础参数
请登录后发表评论
登录
全部评论
