sql优化

1.建表优化

 1.1尽量使用数值替代字符串类型

1、正例

1. 主键（id）：primary key优先使用数值类型int，tinyint
2. 性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint

2、理由

1. 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
2. 而对于数字型而言只需要比较一次就够了；
3. 字符会降低查询和连接的性能，并会增加存储开销

　1.2 使用varchar代替char

1、反例

?

1	`address` char(100) DEFAULT NULL COMMENT '地址'

2、正例

?

1	`address` varchar(100) DEFAULT NULL COMMENT '地址'

3、理由

1. varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
2. char按声明大小存储，不足补空格；
3. 虽然 char 的存取数度还是要比 varchar 要快得多，（因为其长度固定，方便程序的存储与查找），但是定宽的存储空间可能导致表和相关索引比平常大出许多，所以尽量使用varchar类型。

　1.3数据库和表的字符集尽量统一使用UTF8

（1）可以避免乱码问题；

（2）可以避免，不同字符集比较转换，导致的索引失效问题；

2.合理的建立索引

　　1.1 首先应考虑在 where 及 order by 涉及的列上建立索引，同时不要在有大量重复数据的字段上，比如性别，排序字段应创建索引

　　1.2 索引不是越多越好，如果满足组合索引，就不要使用多个单列索引。（组合索引建立满足最左前缀规则）

　　1.3  索引不会包含有NULL值的列   只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

　　1.4  如果某个字段很长，但是前部分长度的字段就可以确定大部分唯一，我们可以指定一个前缀长度建立索引。就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3. 规范的使用索引

　　1. 避免在使用的时候，导致索引失效。

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

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

　　4.应尽量避免在 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

　　5.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

　　6. 规则有很多，我们记不清的话，可以使用explain分析你SQL执行计划 并对慢sql作出优化

4. 优化sql的写法

 1.inner join 、left join、right join，优先使用inner join  

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；
• 使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
• 这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；
• 且连接的表不要超过5个（影响性能，sql的业务逻辑体现也不明细）

2. 提高group by语句的效率

　　1、反例

　　先分组，再过滤

?

1 2 3

select job, avg（salary） from employee group by job having job ='develop' or job = 'test';

　　2、正例

　　先过滤，后分组

?

1 2 3

select job，avg（salary） from employee where job ='develop' or job = 'test' group by job;

　　3、理由

　　可以在执行到该语句前，把不需要的记录过滤掉

3.批量插入性能提升

　　默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。