mysql索引与全表扫描

约定不等于承诺〃 2022-03-26 08:08 277阅读 0赞

### mysql中建立索引的一些原则 ###

有索引的好处是搜索比较快，但是在有索引的前提下进行插入、更新操作会很慢，所以

1.  先存数据，再建索引
2.  不要对数据量小的数据表建立索引，数据量超过300的表应该有索引  
    对于规模小的数据表建立索引，不仅不会提高查询效率，相反使用索引查找可能比简单的全表扫描还要慢，而且建索引还会占用一部分的存储空间
3.  当对表的查询操作比更新操作频率更高时，对该表建立索引
4.  在不同值较少的字段上不必要建立索引，如性别字段
5.  对查询操作中使用频繁的字段建立索引
6.  表的主键、外键必须有索引
7.  经常出现在Where后面的字段，特别是大表的字段，应该建立索引
8.  经常进行GROUP BY、ORDER BY的字段上建立索引
9.  索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

### 避免全表扫描的方法 ###

1.  对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
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替代：`select id from t where num between 1 and 3`
6.  下面的查询也将导致全表扫描：`select id from t where name like '%李'`  
    若要提高效率，可以考虑全文检索。  
    ps: `%`在右侧不会导致全表扫描
7.  避免在索引列上使用计算，也就是说，应尽量避免在 where 子句中对字段进行表达式操作和函数操作，这将导致引擎放弃使用索引而进行全表扫描。  
    如：`select id from t where num/2=100`改为`select id from t where num=100*2`  
    `select id from t where substring(name,1,3)='abc'` 改为`select id from t where name like 'abc%'`
8.  exists 代替 in ，exists返回值是true或false，用于判断子查询返回结果集是否为空。  
    `select num from a where num in(select num from b)`  
    替换为`select num from a where exists (select 1 from b where b.num=a.num)`
9.  任何地方都不要使用`select * from t` ，用具体的字段列表代替\*
10. 用>=替代>

高效: SELECT id FROM  EMP  WHERE  DEPTNO >=4 
    低效: SELECT id FROM  EMP  WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。  
12. 用Where子句替换having子句

### 注意 ###

1.  一个表的索引数最好不要超过6个，若太多则应考虑一些不常用的列上建索引是否有必要。
2.  尽量使用数字型字段，若字段只含数值信息尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
3.  尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
4.  避免频繁创建和删除临时表，以减少系统表资源的消耗。
5.  在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
6.  如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 `truncate table` ，然后 `drop table` ，这样可以避免系统表的较长时间锁定。
7.  尽量避免大事务操作，提高系统并发能力。
8.  尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。