MySQL优化（二） 优化诀窍

一、索引的使用

（1）查询要使用索引最重要的条件是查询条件中的字段建立了索引；

（2）下列几种情况可能使用到索引：

　　<1> 对于创建的多列索引，只要查询条件使用了最坐边的列，索引一般就会被使用；

　　<2> 对于使用 like 的查询，查询如果是 “%aaa” 则不会使用到索引；如果是“aaa%”，则会使用到索引；

（3）下列的表将不会使用索引：

　　<1> 如果条件中有 OR，即使其中有条件的列建立了索引，也不会被使用；换而言之，就是所有使用的字段都要建立索引；尽量避免使用OR，效率比较低；

　　<2> 对于多列索引，不是使用的第一部分，则不会使用索引；

　　<3> like 查询以 “%” 开头；

　　<4> 如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引；也就是列是字符串类型，就一定要

　　<5> 如果 MySql 估计使用全表扫描要比使用索引快，则不使用索引；

（4）查看索引的使用情况

　　Show status like 'Handler_read%‘

　　Handler_read_key：这个值越高越好，表示使用索引查询使用的次数；

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

（5）大批量插入数据（了解）

　　<1> 对于MyISAM：

　　　　alter table table_name disable keys;  关闭表的索引；如果在导入数据的时候不关闭就会一直在建立索引文件，使导入时间过长；导入完成之后再开启表的索引就好了；

　　　　alter table table_name enable keys;  开启表的索引

　　<2> 对于 Innodb：

　　　　将导入的数据按照主键排序；

　　　　set unique_checks = 0，关闭唯一性校验，否则每条数据都会检查；

　　　　set autocommit = 0，关闭自动提交；

（6）SQL语句优化的技巧

　　<1> 优化 Group by 语句

　　　　MySql 对所有的 group by col1, col2 进行分组查询时，默认分组之后，然后进行排序，可能会降低查询速度；

　　　　如果在查询中使用 group by ，但是用户想要避免排序造成的消耗，可以使用 order by null 来禁止排序；

　　　　即：select * from user group by age order by null;

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

　　　　select * from dept，employ where dept.deptno = employ.deptno; [简单方式]

　　　　select * from dept left join employ on dept.deptno = employ.deptno;  [左连外接，更好]

 

二、存储引擎的选择

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

 MyISAM：如果对表的事务要求不高，同时是以查询和添加为主，我们考虑使用这种；比如BBS的发帖表、回复表等；

 InnoDB：对事务的要求高，保存的数据都是重要数据，建议使用该存储引擎；比如订单表、账号表；

Memory：数据变化频繁，不需要入库，同时频繁的查询和修改，建议使用该存储引擎；

MyISAM的插入速度为什么高于 InnoDB？

答：MyISAM插入的时候放在表的最后，InnoDB 插入的时候会做一个适当的排序；MyISAM 不支持事务，InnoDB支持事务；

 【注意】如果使用了 MyISANA 存储引擎，如果经常做删除 或 修改表的记录，要定时执行 optimize table table_name 对表进行碎片整理；否则，有可能出现删除的数据没有删掉等情况。

 

2、MyISAM 和 InnoDB 的区别？

（1）事务：MyISAM 不支持，InnoDB 支持；

（2）查询添加速度：MyISAM 速度快（插入时直接放在表的最后），InnoDB 速度低（插入时会做事务的校验，适当的排序）；

（3）支持全文索引：MyISAM 支持，InnoDB 不支持；

（4）锁机制：MyISAM 表锁，InnoDB 行锁；

（5）外键：MyISAM 不支持外键，InnoDB支持；

 

 3、MySQL 的配置文件

max_connections = 1000 ，一般情况设置为1000，极限是2000左右；

三、读写分离

https://www.cnblogs.com/lin3615/p/5684891.html

1 程序修改mysql操作类
可以参考PHP实现的Mysql读写分离，阿权开始的本项目，以php程序解决此需求。
优点：直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配
缺点：自己维护更新，增减服务器在代码处理

2 amoeba
参考官网：http://amoeba.meidusa.com/
优点：直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案
缺点：自己分配账户，和后端数据库权限管理独立，权限处理不够灵活

3 mysql-proxy
参考 mysql-proxy。
优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号
缺点：字符集问题，lua语言编程，还只是alpha版本，时间消耗有点高

如果你不能安装软件来解决读写分离，那可以尝试阿权的项目解决思路。
如果你可以安装软件，那amoeba是不错的，mysql-proxy不太建议，目前只有alpha版本，效率还不太理想，amoeba目前在阿里巴巴是内部项目，正在生产环境使用的。