mysql索引与全表扫描

最新推荐文章于 2024-09-28 21:43:12 发布

chang_hf

最新推荐文章于 2024-09-28 21:43:12 发布

阅读量3.3k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/chang_li/article/details/86506387

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

mysql中建立索引的一些原则

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

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

避免全表扫描的方法

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，
如：select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
应尽量避免在 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
in 和 not in 也要慎用，否则会导致全表扫描，
如：select id from t where num in(1,2,3)
对于连续的数值，用 between替代：select id from t where num between 1 and 3
下面的查询也将导致全表扫描：select id from t where name like '%李'
若要提高效率，可以考虑全文检索。
ps: %在右侧不会导致全表扫描
避免在索引列上使用计算，也就是说，应尽量避免在 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%'
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)
任何地方都不要使用select * from t ，用具体的字段列表代替*
用>=替代>

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

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

注意

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