sql优化小技巧

1. 避免大量使用select *

大量使用select *, 可能会出现大量的回表数据，导致性能变低。

2. 避免使用较多distinct

1. distinct 使用时会占用cpu较长时间,对于重复数据的比较，过滤会占用系统时间，如cpu时间。
2. 查询返回字段多时，用distinct时数据库会对重复数据进行过滤

3. where 后默认值替换为null

1. 数据库优化器发现：走索引比不走索引的成本要高，就会放弃索引。
2. 条件 != , <> , is null ，is not null 经常会被认为索引失效，这些都是优化器自动放弃的。

4. where 后避免使用 != ,<>

1. != , <> 经常会被认为索引失效 ,引擎可能会不用索引而全盘扫描。

5. where后避免使用OR搜索条件

使用OR搜索条件，会导致引擎放弃使用索引而进行全局扫描。

select * from student_information where age = 17 or age = 18;

可以替换成

select * from student_information where age = 17
union all
select * from student_information where age = 18 

6. group by 语句 效率优化

1. 先过滤，后分组，这样可以把不需要的记录先过滤掉
2. 如果有where语句，最好使用 where 语句位于分组前，目的是先把多余的数据过滤掉,少使用having过滤条件。

   select A,B,C ...  from tableName
   where condition ...
   group by condition

7. UNION ALL 操作符替代 UNION

1.UNION ALL 替代 UNION , 前者只是简单的将两个结果合并后返回。
2.UNION 会获取数据，包括重复的数据。

8. join 的表不宜过度

1. join 表的数量不应该超过3个。

连接表，优先用 inner join

1.inner join
内连接，只保留两张表中完全匹配的结果集
inner join 是等值连接，如果 和left join 相比，结果集相同，应优化inner join。
2. left join
会返回左表所有的行，即使在右表中没有匹配的数据。
用 left join 坐标数据结构尽可能小，条件尽量放在左边处理
3. right join
同上

扩展：如何优化join？

1. 尽量使用索引字段关联
2. “小表”驱动“达标”，“大表”作为驱动表。

9. 表必有主键

1. 主键最好用自增数字ID，效率最高
2. 更新频繁的列不适合做主键
3. 如果用 UUID，MD5 , HASH ,字符串列作为主键，则无法保证数据的顺序增长。

另外，禁止表中建立过多索引：MYSQL每张表最多建立16个索引。并不是索引越多越好，要合理建立。因为建立需要时间和村存储。

1. 一个表的最引最多不超过5个。
2. 索引表的数据有排序，排序需要时间。
3. insert / update 有时会重建索引。

10. 选取合适的字段类型

1. 涉及到钱的字段用 decimal
   非精准浮点：float,double
   精准浮点： decimal
       ① 底层存储的数据转成字符串形式存储，因此能保持精度
       ② 可存比 bigint 更大的数据
       ③ 在计算时不会丢失精度
       ④ 占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节。
2. char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间
   如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成char类型，长度是11字节
3. varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间
4. 数据库和表的字符集尽量统一使用UTF-8
   避免乱码问题，避免不同字符集转换，索引失效的问题。

 

     尽量遵守如下原则组织：
           ① 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
           ② 尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
           ③ 长度固定的字符串字段，用char类型。
           ④ 长度可变的字符串字段，用varchar类型。

11. 数据库使用规范

1. 表命名必须小写字母且下划线分割单词，例如 ：student_information
2. 数据库命名不要用数据库的关键词，长度不要超过32个字符
3. 遵循数据库三范式
4. 合理的反三范式使用（比如合理的冗余字段在一张表）

12.expalin 检查 mysql 语句

     EXPLAIN SELECT * FROM website; 

	含义
id	代表select 语句执行的 id，id 越大代表执行顺序越早，id 最小的最慢执行
select_type	对应的查询是简单还是复杂 ① SELECT SIMPLE：查询语句只有一个 select，没有其余子查询 ② PRIMARY：复杂查询最前面的 select 查询，是最外层的查询 ③ SUBQUERY：from 关键字前的子查询 ④ DERIVED：from 关键字后的子查询，也叫派生查询 ⑤ UNION：在 union 查询中，第二个及其后面的 select 语句，都是 UNION 类型
table	访问表
type	表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围。
possible_key	显示查询可能使用用到的索引
key	这一列显示mysql实际采用哪个索引来优化对该表的访问
key_len	显示了mysql在索引里使用的字节数
ref	显示了在key列记录的索引中，表查找值所用到的列或常量
rows	mysql估计要读取并检测的行数，注意这个不是结果集里的行数

索引优化

索引失效的常见原因
1. 不满足最左前缀原则
2. 范围索引列没有放最后
3. 使用了select *
4. 索引列有计算
5. 索引上使用了函数
6. like查询左边有%
……