本文共 2069 字，大约阅读时间需要 6 分钟。

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的记录。

注意

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

转载地址：http://cfaxb.baihongyu.com/