mysql 极限优化配置_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目前在阿里巴巴是内部项目，正在生产环境使用的。