Mysql优化

1.常见索引列建议

出现在select,update,delete语句的where从句中的列

包含在order by ,group by ,distinct中的字段

多表的join关联列

2.避免建立冗余和重复索引

重复索引示例：primary key(id)、index(id)、unique index(id)

冗余索引示例：index(a,b,c)、index(a,b)、index(a)

3.优先选择符合存储需要的最小数据类型

4.避免使用TEXT，BLOG数据类型。（如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行，Mysql还要进行二次查询，是sql性能变差）

5.使用TIMESTAMP(4字节)或DATETIME（8字节）存储时间

6.财务相关金额类数据必须使用decimal类型（Decimal类型为精准浮点数，在计算时不会丢失精度）

7.限制每张表上的索引数量，建议但张表索引不超过5个。（索引不是越多越好，索引可以增加查询效率，但同样也会降低插入和更新效率。）

8.冷热数据分离，减小表的宽度

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）

9.不要在数据库中存储图片，文件等二进制数据

10.尽量避免使用外键约束。

不建议使用外键约束foregin key，但一定要在表与表之间的关联键上建立索引；

外键可以用于保证数据的参照完整性，但建议在业务端实现；

外键会影响父表和子表的写操作从而降低性能。

11.建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题 只传参数，比传递SQL语句更高效 相同语句可以一次解析，多次使用，提高处理效率。

12.避免使用前%号的查询条件

如a like %123%，（如果无前置%,只有后置%，是可以用到列上的索引的）

在我们查询中经常会用到select id,fnum,fdst from table_name where user_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

创建全文索引的sql语法是：

ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的sql语句是：

select id,fnum,fdst from table_name where match(user_name) against('zhangsan' in boolean mode);

13.禁止使用SELECT * 必须使用SELECT <字段列表> 查询

原因：

• 消耗更多的CPU和IO以网络带宽资源
• 无法使用覆盖索引
• 可减少表结构变更带来的影响

14.避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

• 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
• 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
• 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

15.避免使用JOIN关联太多的表

对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。

在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率Mysql最多允许关联61个表，建议不超过5个。

16.在明显不会有重复值时使用UNION ALL而不是UNION

UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
UNION ALL不会再对结果集进行去重操作

17.使用left join或 not exists来优化not in操作

  因为not in 也通常会使用索引失效。

18.使用合理的分页方式以提高分页的效率

列：select id,name from table_name limit 88888, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是88888。sql可以采用如下的写法：

 select id,name from table_name where id> 88888 limit 20

19.避免在 where 子句中对字段进行 null 值判断

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：    
select id from t where num is null    
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    
select id from t where num=0 
 

20.避免隐式类型转换

隐式转换会导致索引失效

where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型

21.对于联合索引来说，要遵守最左前缀法则，常用的查询字段放在最前面

22.应尽量避免在 where 子句中使用!=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描。

23.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：    
select id from t where num=10 or num=20    
可以这样查询：    
select id from t where num=10    
union all    
select id from t where num=20

24.in 和 not in 也要慎用，否则会导致全表扫描，如：    
select id from t where num in(1,2,3)    
对于连续的数值，能用 between 就不要用 in 了：    
select id from t where num between 1 and 3

25.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：    

select id from t where num/2=100    

应改为:    

select id from t where num=100*2  

26.用 exists 代替 in    
select num from a where num in(select num from b)    
用下面的语句替换：    
select num from a where exists(select 1 from b where num=a.num)

27.对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;  

select * from table1 where col1=A AND col2=B AND col3=D     如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引