Mysql调优

schema与数据类型优化

整数类型：

可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。
整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂。

字符和字符串类型

char是定长，varchar是可变长度，varhcar虽然比char节省空间，但是如果一个varchar列经常被修改，而且每次被修改的数据长度不同，会引起行迁移现象，这会造成多余的I/O。所以varchar适用于存储长度波动较大的数据，且很少更新的场景，char适用于长度不变，短且经常更新的字符串，如：MD5摘要。

使用mysql自建类型而不是字符串来存储日期和时间

使用枚举类型代替字符串

有时可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表。

特殊类型数据

使用整型存储IP地址，mysql给出了现成的转换函数：
select INET_ATON("192.15.2.2")
select INET_NTOA("3222209026")

适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加。

合理使用范式和反范式

被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。
这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。缺点是，冗余的同时需要确保数据的一致性，此字段如果要发生修改，要同时更新两张表。

索引

索引失效的几种情况

模糊查询时通配符放在最前面（like '%XX'或者like '%XX%'）

索引不允许为null，所以where的判断条件如果对字段进行了null值判断，将导致数据库放弃索引而进行全表查询

select id from t where num is null 

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值无法比较，无法确定null出现在索引树的叶子节点位置。）

where后面的索引列使用了!= 或者 <> 操作符

where后面的索引列进行了表达式或函数操作

组合索引没有遵循最左匹配原则

索引优化

尽量利用覆盖索引机制，避免回表，减少I/O次数

尽量使用主键查询，避免回表，减少I/O次数

在需要索引很长的字符串的时候使用前缀索引

前缀索引是一种能使索引更小更快的有效方法，但是缺点是：mysql无法使用前缀索引作order by 和 group by

尽量使用索引来做排序

在组合索引中，只有order by字句的顺序遵循最左匹配原作的时候，才会使用到索引排序。
在查询需要关联多张表的时候，只有order by字句引用的字段全部为第一张表的时候，才会使用到索引排序。

更新十分频繁，数据区分度不高的字段不适合建索引

更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能。
类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据。

表连接最好不要超过3张，join的字段，要保持数据类型一致，保证join的字段有索引

能使用limit的时候尽量使用limit

组合索引的字段数不允许超过5个