经常检索的字段，才设置成索引，INDEX；不让数据库做全表扫描
索引提升查询速度，让查询走 索引加速

不要把SELECT子句写成 SELECT *

• 因为返回的数据太多了，
• SQL要把 * 转换为全部的字段，再去查询 ```sql

  bad

  select * from user;

good

select id, name from user;

<a name="UnUgt"></a>
### 谨慎使用 LIKE模糊查询
- 不要把 %写在开头
- %代表全部字符，会跳过做全表扫描
```sql
# bad
select name from user where name like '%S%';
# more better
select name from user where name like 'S%';

对 ORDER BY排序的字段设置索引

select name from user order by number;

尽量少用 != 运算符

• 无法利用二叉树机制，变成了全表扫描 ```sql

  bad 全表扫描

  select name from user where age != 20;

good

select name from user where age < 20 and age > 20;

<a name="k0LHU"></a>
### 尽量少用 OR 运算符
- OR表达式后面的查询，会跳过索引
- union all 优化
```sql
select name from user where age = 20 or age = 30;
# 优化
select name from user where age = 20
union all
select name from user where age = 30;

尽量少用 IN 和 NOT IN 运算符

• 会让 mysql跳过索引，进行全表扫描
• 索引是个二叉树，null值是不会排序的
• union all优化 sql ```sql

  bad

  select name from user where age is not null;

good

select name from user where age >= 0; select name from user where age = -1; 非空约束

<a name="Qx8PG"></a>
### 避免条件语句中的数据类型转换
```sql
select name from user where salary*12 >= 100000;
select name from user where salary >= 100000 / 12

在表达式左侧使用运算符和函数都会让索引失效

# year() 函数让索引失效
select name from user where year(hiredate) >= 2000;
# 优化
select name from user where
hiredate >= '2000-01-01 00:00:00';