第二个链接未整理全


MySQL什么时候适合建索引，什么时候不适合建索引

  • 1、什么是索引（本质：数据结构）
  • 2、适合创建索引条件
  • 3、不适合创建索引条件
  • 4、索引失效的情况


1、什么是索引（本质：数据结构）

索引是帮助MySQL高效获取数据的数据结构。

2、优势：

1、提高数据检索的效率，降低数据库IO成本
2、通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3、劣势：

降低更新表的速度，如对表进行update 、delete、insert等操作时，MySQL不仅要保存数据，还要保存一下索引文件每次添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

2、适合创建索引条件

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该建立索引

3、查询中与其他表关联的字段，外键关系建立索引

4、单键/组合索引的选择问题，组合索引性价比更高

5、查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率

3、不适合创建索引条件

1、表记录少的

2、经常增删改的表或者字段

3、where条件里用不到的字段不创建索引

4、过滤性不好的不适合建索引

4、索引失效的情况

1、尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3、应尽量避免在 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

4、下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

5、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

7、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id 
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

应改为:

select id from t where name like 'abc%' 
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

总结6和7，不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

8、不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

9、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。