MySql 学习笔记三：常用SQL优化- 数据库

一、group by

在使用group by 分组查询是，默认分组后，还会排序，可能会降低速度.
比如:
这里写图片描述
在group by 后面增加 order by null 就可以防止排序.

二、join与子查询

有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

三、如何选择mysql的存储引擎？

在开发中，我们经常使用的存储引擎 myisam / innodb/ memory
myisam 存储: 默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高。其优势是访问的速度快。比如 bbs 中的发帖表，回复表.

INNODB 存储: 提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间。保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

问 MyISAM 和 INNODB的区别

事务安全
查询和添加速度
支持全文索引
锁机制
外键 MyISAM 不支持外键， INNODB支持外键.

Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.
这里写图片描述

如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

对于MyISAM表，OPTIMIZE TABLE按如下方式操作：

如果表已经删除或分解了行，则修复表。
如果未对索引页进行分类，则进行分类。
如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。

对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

使用—skip-new或—safe-mode选项可以启动mysqld。通过启动mysqld，您可以使OPTIMIZE TABLE对其它表类型起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

OPTIMIZE TABLE语句被写入到二进制日志中，除非使用了自选的NO_WRITE_TO_BINLOG关键词（或其别名LOCAL）。已经这么做了，因此，用于MySQL服务器的OPTIMIZE TABLE命令的作用相当于一个复制主服务器，在默认情况下，这些命令将被复制到复制从属服务器中。

四、大批量插入数据

对于MyISAM：

//防止一边插入数据一边建索引
alter table table_name disable keys;
alter table table_name enable keys;3

对于Innodb：

将要导入的数据按照主键排序
set unique_checks=0,关闭唯一性校验（防止一边插入一边校验）。
set autocommit=0,关闭自动提交。

五、选择合适的数据类型

在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性。deciaml 不要用float
对于存储引擎是MyISAM的数据库,如果经常做删除和修改记录的操作，要定时执行optimize table table_name;功能对表进行碎片整理。
日期类型要根据实际需要选择能够满足应用的最小存储的早期类型
选择适当的字段类型，特别是主键
选择字段的一般原则是保小不保大，能用占用字节小的字段就不用大字段。比如主键，建议使用自增类型，这样省空间,空间就是效率!按4个字节和按32个字节定位一条记录，谁快谁慢太明显了。涉及到几个表做join时，效果就更明显了。
建议使用一个不含业务逻辑的id做主角

六、数据库参数配置

最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大：

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

对于myisam，需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数
在my.ini修改端口3306，默认存储引擎和最大连接数
如果你的机器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql
读写分离（mark一下，未用过）
如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。

Master
　　Slave1
　　Slave2
　　Slave3
主库master用来写入，slave1—slave3都用来做select，每个数据库分担的压力小了很多。
　要实现这种方式，需要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理，对程序来读写哪些数据库是透明的。官方有个mysql-proxy。新浪有个amobe for mysql，也可达到这个目的，结构如下：