MySql优化

字段

1. 尽量使用 TINYINT、SMALLINT、MEDIUM_INT 作为整数类型，而非全是INT，如果是非负数，请加上UNSIGNED（无符号）

类型	大小	范围（有符号）	范围（无符号）
TINYINT	1字节	（-128,127）	(0，255)
SMALLINT	2字节	(-32 768，32 767)	(0，65 535)
MEDIUMINT	3字节	(-8 388 608，8 388 607)	(0，16 777 215)
INT或INTEGER	4字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
BIGINT	8字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)

2. varchar 的长度要分配真正需要的空间

3. 尽量使用timestamp而非datetime
   timestamp：
   （1）占用4个字节
   （2）允许为空值，但是不可以自定义值，所以为空值时没有任何意义
   （3） TIMESTAMP值不能早于1970或晚于2037
   （4）值以UTC格式保存：YYYYMMDD T HHMMSS Z(或者时区标识)
   （5）时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区
   （6） 默认值为CURRENT_TIMESTAMP()，其实也就是当前的系统时间
   （7）数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间
   （8）以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间。
   timestamp类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新。
   datetime：
   （1）占用8个字节
   （2）允许为空值，可以自定义值，系统不会自动修改其值
   （3） 实际格式储存
   （4）与时区无关
   （5）不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据
   （6）可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。
   datetime类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime字段的值都不会改变，除非你手动更改它。

4. 单表字段尽量不要超过20个

5. 用整形存储IP，mysql里存储ip，字符类型要设置成BIGINT
   mysql函数(IP转数字)：select INET_ATON(“192.168.0.1”)
   结果：3232235521
   mysql函数(数字转IP)：select INET_NTOA(3232235521)
   结果：192.168.0.1

6. 少用text字段，长度超过3000再考虑使用text，否者使用varchar

7. 避免使用null字段，很难查询优化并且占用额外索引空间

索引

1. 不要过度用索引，索引需要额外的磁盘空间，并降低写操作的性能，什么时候应该用索引可以根据执行计划分析
2. 字符字段建前缀索引，当字符串本身比较长，而且前几个字符就开始不相同。
3. 字符字段最好不要作为主键
4. 减少使用外键，由程序来控制约束
5. 尽量不用UNIQUE（唯一约束），由程序保证约束
6. 使用联合索引注意顺序和查询条件保持一致，同时删除不必要的单列索引
7. union、in、or 都能够命中索引，建议使用 in。查询的CPU消耗：or > in >union
8. 负向条件查询不能使用索引，可以优化为 in 查询。负向条件有：!=、<>、not in、not exists、not like 等。

查询sql

1. 通过开启慢查询日志来找出较慢的 SQL
2. 不做列运算： SELECT id WHERE age+1=10
3. sql 语句尽可能简单
4. 不用 SELECT *
5. OR 改写成 IN： OR 的效率是 n 级别， IN 的效率是 log(n) 级别
6. 不用函数和触发器，在应用程序实现
7. 避免%xxx式查询
8. 少用JOIN
9. 尽量避免在 WHERE 子句中使用 != 或 <> 操作符
10. 对于连续数值，使用 BETWEEN 不用 IN
11. 使用limit分页

配置参数优化