原创 MySQL数据库如何建立索引？有哪些技巧？

众所周知，在MySQL数据库中，索引是存储引擎层实现的，它也是可以迅速找出记录的一种数据结构，以牺牲空间的方式来换取时间。那么MySQL数据库如何建立索引？有哪些技巧呢？本文将从创建索引的原因、原则和技巧三方面进行分析，为大家解决上述疑问。

一、为什么要创建索引呢？

1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。

3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

二、MySQL数据库创建索引的原则

1、 对于查询频率高的字段创建索引；

2、对排序、分组、联合查询频率高的字段创建索引；

3、索引的数目不宜太多；

4、若在实际中，需要将多个列设置索引时，可以采用多列索引；

5、选择唯一性索引；

6、尽量使用数据量少的索引；

7、尽量使用前缀来索引；

8、删除不再使用或者很少使用的索引。

三、MySQL数据库建立索引的技巧

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 就不要用 in 了：select id from t where num between 1 and 3

6、避免使用通配符。下面的查询也将导致全表扫描：select id from t where name like ‘李%’若要提高效率，可以考虑全文检索。

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

8、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

9、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

10、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

以上就是MySQL数据库建立索引的技巧讲解。创建索引可以极大地提高系统的性能，因此这也是MySQL数据库的一个需要重点掌握的知识点。 还有不明白的小伙伴可以上博学谷官网，深入学习MySQL数据库的相关知识点。