MySql优化

1.根据不同业务选用不同存储引擎(MyISAM/InnoDB)，一般情况下优先InnoDB。

2.优化表

　　1.使用正确合适的类型，不要将数字存储为字符串。

　　2.尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。

　　3.尽量使用NOT NULL，除非有特殊情况确实需要NULL。

　　4.varchar类型尽量固定长度

　　5.尽量使用short，integer的主键

3.创建合适的索引

　　1.索引不是越多越好，每个表控制在5个索引以内。范围条件的情况下，索引不起作用，比如where age<20

　　2.在变化很少的列上使用索引并不是很好，例如性别列。

　　3.越是短的值越好，最好使用integer。

　　4.相近的键值比随机好。Auto_increment就比uuid好

4.优化sql语句（例如：用字段名代替*,将大sql分为几个小sql，连接查询代替子查询，小表 jion 大表）
5.使用缓存，例如：memcached或者redis
6.主从复制或主主复制，读写分离；
7.mysql分区表，应用无需更改代码,但是sql语句需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区；
8.做垂直拆分，将一个大的系统分为多个小的系统，也就是分布式系统；
9.水平切分，针对数据量大的表，选择一个合理的sharding key,为了查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

10.优化my.cnf文件中的配置

　　1.key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。注意：该参数值设置的过大反而会是服务器整体效率降低！！！

　　2.max_allowed_packet = 20M用来控制其通信缓冲区的最大长度（限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。）

　　3.MySQL里面为了提高客户端请求创建连接过程的性能,提供了一个连接池也就是 Thread_Cache池,将空闲的连接线程放在连接池中,而不是立即销毁.这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接 线程,而是先去Thread_Cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程.

　　4.max_connections = 768  MySQL允许的最大连接进程数。如果在访问时经常出现Too Many Connections的错误提 示，则需要增大该参数值。太大的话会出现out of memory。

　　5.wait_timeout = 10  指定一个请求的最大连接时间

　　6.table_cache：MySQL在同一时间保持打开的table的数量。打开table开销比较大。一般设置为512。

　　7.query_cache_size： 用于缓存查询的内存大小。

　　8.innodb_buffer_pool_size：这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有　　　　InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。更精确一点，在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。

 

 

(一)MyISAM

　　MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非配置MySQL默认使用另外一个引擎。它不支持事务，也不支持外键，优点是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

　　数据文件和索引文件可以放置在不同的目录，平均分配IO，获取更快的速度。要指定数据文件和索引文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，文件路径需要使用绝对路径。

 

(二)InnoDB
　　InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
1)自动增长列：
　　InnoDB表的自动增长列可以手工插入，但是插入的如果是空或0，则实际插入到则是自动增长后到值。可以通过"ALTER TABLE...AUTO_INCREMENT=n;"语句强制设置自动增长值的起始值，默认为1，但是该强制到默认值是保存在内存中，数据库重启后该值将会丢失。可以使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入多条记录，那么返回的是第一条记录使用的自动增长值。
对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引到前面几列排序后递增的。
2)外键约束：
　　MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
      在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括restrict、cascade、set null和no action。其中restrict和no action相同，是指限制在子表有关联的情况下，父表不能更新；casecade表示父表在更新或删除时，更新或者删除子表对应的记录；set null 则表示父表在更新或者删除的时候，子表对应的字段被set null。
　　当某个表被其它表创建了外键参照，那么该表对应的索引或主键被禁止删除。
　　可以使用set foreign_key_checks=0;临时关闭外键约束，set foreign_key_checks=1;打开约束。