9.1避免全表扫描
对査询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立素引。
9.2避免判断null值
应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确 保 表 中num列没有null值,然后这样査询：
select id from t where num=0
9.3避免不等值判断
应尽童避免在where子句中使用!=或<>操作符，否则存储引擎将放弃使用索引而进行全表扫描
9.4避免使用or逻辑
应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描.如：
select * from t where num = 10 or num = 20
可以这样查询：
select * from t where num = 10
union all
select * from t where num = 20
9.5 慎用in 和not in逻辑
in和not in也要慎用，否则可能会造成全表扫描
select id from t1 where num in (select id from t2 where id > 10)
此时外层查询会全表扫描，放弃索引，可以改为：
select id from t1, (select id from t2 where id > 10) t2 where t1.num = t2.id
此时索引被使用，可以明显提升查询效率。
关联查询用到了索引，ref 有值：springboot.b.user_id
mysql> explain select a.* from user a join user_detail b on a.id = b.user_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | springboot.b.user_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
第二行的子查询ref用到了索引，但是第三行的ref值是null
mysql> explain select a.* from user a where a.id in (select distinct user_id from user_detail);
+----+--------------+-------------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | <subquery2>.user_id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | user_detail | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+--------------+-------------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
9.6 注意模糊查询
下面的查询将导致全表扫描
select id from t where name like '%abcd%'
模糊查询如果是必要条件时，可以使用：
select id from t where name like 'abcd%'
此时索引将被使用。如果头匹配是必要逻辑，建议使用全文索引：ES，Solr等
9.7 避免查询条件中计算
避免where查询条件中进行表达式操作，这将导致存储引擎放弃使用索引而进行全表扫描。
select id from user where num / 2 = 100;
应改为
select id from user where num = 100 * 2;
9.8 避免查询条件中对字段进行函数操作
导致放弃索引使用全表扫描
select id from user where substring(name,1,3) = 'abc' -- 以abc开头的数据
select id from user where name like 'abc%'
9.9 where 子句“=”左边注意点
不要在where子句“=”左边进行算术运算、函数操作或其它表达式运算，这将导致引擎放弃索引。
9.10组合索引使用
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到改索引的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且尽可能让字段顺序与索引顺序一致。
比如有一条语句是这样的：
select * from users where area=’beijing’ and age=22;
如果我们是在area和age上的话，
由于，
所以虽然这样已经相对不做索引时全表扫描提高了很多效率，
但是如果在area、age两列上创建复合索引的话将带来更高的效率。
如果我们创建了(area, age,salary)的复合索引，
那么其实创建了(area,age,salary)、(area,age)、(area)
这被称为最佳左前缀特性。
创建：
因此我们在创建复合索引时应该将。
create table union_index(
id int(10) primary key auto_increment,
name varchar(20) not null,
age int(3) default 0 not null,
sex int(1) default 0 not null,
num int(10) default 0 not null
);
创建联合索引
CREATE INDEX name_age_num_index ON union_index (name,age,num);
插入数据
insert into union_index(name,age,sex,num) values('xiaochao',1,0,0);
insert into union_index(name,age,sex,num) values('xiao',2,1,1);
insert into union_index(name,age,sex,num) values('chao',22,0,3);
insert into union_index(name,age,sex,num) values('xiaoyi',23,1,4);
insert into union_index(name,age,sex,num) values('xiaotie',24,0,5);
mysql> select * from union_index;
+----+----------+-----+-----+-----+
| id | name | age | sex | num |
+----+----------+-----+-----+-----+
| 1 | xiaochao | 1 | 0 | 0 |
| 2 | xiao | 2 | 1 | 1 |
| 3 | chao | 22 | 0 | 3 |
| 4 | xiaoyi | 23 | 1 | 4 |
| 5 | xiaotie | 24 | 0 | 5 |
+----+----------+-----+-----+-----+
1. 全表扫描，没有用到索引
explain select id,name,age,sex,num from union_index;
explain select * from union_index;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | union_index | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
2. 主键索引
explain select id,name,age,sex,num from union_index where id = 1;
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | union_index | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3. 联合索引第一个字段做条件，使用了索引
explain select id,name,age,sex,num from union_index where name = 'xiao';
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | union_index | NULL | ref | name_age_num_index | name_age_num_index | 22 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
4. 联合索引首列 + 非索引字段 【索引生效】
非索引字段 + 联合索引首列 【索引生效】
非索引字段 + 联合索引首列 + 联合索引最后一列 【索引生效】（跨列）
联合索引首列 + 联合索引最后一列 【索引生效】（跨列）
联合索引最后一列 + 联合索引首列 【索引生效】（首列放后面）
总结： 联合索引只要首字段被使用了就会生效。
explain select * from union_index where sex=1 and name = 'xiao';
explain select * from union_index where name = 'xiao' and sex=1;
explain select * from union_index where sex=1 and name = 'xiao' and num = 1;
explain select * from union_index where num=1 and name = 'xxx';
explain select * from union_index where name = 'xxx' and num = 10;
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | union_index | NULL | ref | name_age_num_index | name_age_num_index | 22 | const | 1 | 20.00 | Using where |
+----+-------------+-------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
5. 联合索引非首字段 做条件 【索引不生效】
explain select id,name,age,sex,num from union_index where sex=1 and age=22 and num = 1;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | union_index | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
5. 主键索引 + 联合索引第一个字段 都生效
explain select id,name,age,sex,num from union_index where sex=1 and id = 4 and name = 'xiaoyi';
+----+-------------+-------------+------------+-------+----------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+----------------------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | union_index | NULL | const | PRIMARY,name_age_num_index | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+-------+----------------------------+---------+---------+-------+------+----------+-------+
9.11 不要做没有意义的查询
不要写一些没有意义的査询，如需要生成一个空表结构，
select coll,col2into #t from t where 1=0
这类代码不会返回任何结果集，但是会捎耗系统资源的，应改成这样
create table #t ()
9.12exists
很多时候用exists代替in是一个好巧选择
select num from a where numin(select num from b)
用下句替换
select num from a where exists(select 1 frrm b where id =a.num) //1:真，0：假
9.13索引也可能失效
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex、male、female几乎各占—半.那么即使在sex上建了索引也对査询效率起不了作用。
9.14表格字段类型选择
尽置使用数字型宇段，若只含数值信息的字段尽重不要设计为字符型，这会降低查询和连接的性能，幷会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每—个宇符，而对于数宇型而言只需要比较一次就够了。
固定长度用char
非固定长度用varchar
尽可能使用varchar代替char，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然更高些。
9.15 查询避免使用*
用详细字段名
网友评论