MySQL索引简介
时间:2014-12-30 13:54 来源:linux.it.net.cn 作者:IT
简介
用来提高查询速度的技术很多，其中最重要的是索引。索引是一种数据结构，能更快地定位到目标数据。在一个没有索引的数据表中，要查询一行记录，只能逐行匹配，是一个顺序查找的过程，时间复杂度为O(n).数据量小尚且可以接受，当数据达到百万，甚至更大的时候，光花在查询上的时间就得好几秒，这是不允许的。
如上图所示，如果在user_id上加索引，需要查询用户2031303960的宝贝，就不必一行一行查询，直接扫描索引，定位到了匹配行，只有一行满足要求。其他行我们不需要扫描。由此可见，索引可以提高查询效率的一个原因是可以过滤不相干的行，另一个原因是，不用再线性地查找，各种算法（二分查找，哈希等）都可以使用。
不同的存储引擎，索引实现细节不同，MyISAM中数据行和索引是分开存放的，由于一个表可以有多个索引，所有这些索引全部都放在一个索引文件里面。InnoDB却不同，存在表空间的概念，一个表空间有着自己的索引和数据。
索引分类
主键索引
MySQL中主键必须唯一且不能有空值，因此在主键上的索引也是唯一索引。一个表上的唯一索引可以有多个，但主键只有一个。一般在建表的同时会创建主键索引,InnoDB中，如果没有指定主键，会隐含性创建一个长度为6的索引。
唯一索引
唯一索引顾名思义，索引必须唯一，唯一索引中允许有空值出现。建立唯一索引的语法如下：
CREATE UNIQUE INDEX indexName ON tablename(columnname(length))
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
普通索引
这种索引没有任何限制，可以有重复数据，也可以为空。建立普通索引的语法如下：
CREATE INDEX indexName ON tablename(columnname(length))
普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
对于varchar类型的字段，索引长度的选取很重要，不能太短起不到索引的效果，也不能太长浪费了存储空间。一般应小于varchar实际长度。
组合索引
当有多个查询条件时，可以采用组合索引，索引可以覆盖多个数据列，可以有选择地使用其中一个索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使 用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。这就是所谓的最左前缀原则。
注意事项
1、全列匹配：当where语句中涉及到多列，且都是精准匹配（=或者in）时，理论上我们应该注意字段的顺序，但mysql会帮我们优化，采用最左前缀匹配。若需要查看一个语句是否有用索引可以在前面加上EXPLAIN关键字。
2、最左前缀匹配：select * from tablename where A=a and B=b and C=c and D=d这条语句，如果在A,B,D上建有索引，那么只在A,B上的索引才有效，D上的索引不会被用到。
3、如果中间某个字段没有索引，后面的索引不会生效。跟2类似，由于C上没有索引，索引D不会生效。可以在(C,D)上单独建一个辅助索引。
select * from tablename where A=a and B=b and D=d这条语句，如果在(A,B,C,D)四列上建有组合索引，A,B肯定能匹配上，由于没有按C进行查询，在D上只能按照where条件过滤，如果C的取值少，可以用in替换这条SQL:select * from tablename where A=a and B=b and C in() and D=d,这个索引可以合理地被利用。
4、某些like语句可以走索引，一般不推荐在sql中用like语句，稍不注意会导致全表扫描。对于title like 'keyword%'这种是可以用到索引的，其他情况比如title like '%keyword',title like '_keyword'不能走索引
5、范围查询，范围查询可以用到索引，必须是最左前缀，范围列后面的索引用不到。一条语句中最好把范围列放最后，不要出现多个范围列。
6、函数表达式 不要在列上直接套用函数或者表达式，这样用不到索引。
小结
索引能有效地提高查询速度，但并不是索引越多越好，索引会消耗存储空间，维护索引需要成本。可以在主键上建索引强调该列的唯一性，如果常按某一列查询，可以考虑建索引，在where子句上建索引提高过滤速度，在需要排序的字段上建索引可以直接利用索引的有序性。
(责任编辑：IT)
简介
用来提高查询速度的技术很多，其中最重要的是索引。索引是一种数据结构，能更快地定位到目标数据。在一个没有索引的数据表中，要查询一行记录，只能逐行匹配，是一个顺序查找的过程，时间复杂度为O(n).数据量小尚且可以接受，当数据达到百万，甚至更大的时候，光花在查询上的时间就得好几秒，这是不允许的。
如上图所示，如果在user_id上加索引，需要查询用户2031303960的宝贝，就不必一行一行查询，直接扫描索引，定位到了匹配行，只有一行满足要求。其他行我们不需要扫描。由此可见，索引可以提高查询效率的一个原因是可以过滤不相干的行，另一个原因是，不用再线性地查找，各种算法（二分查找，哈希等）都可以使用。
不同的存储引擎，索引实现细节不同，MyISAM中数据行和索引是分开存放的，由于一个表可以有多个索引，所有这些索引全部都放在一个索引文件里面。InnoDB却不同，存在表空间的概念，一个表空间有着自己的索引和数据。
索引分类主键索引
MySQL中主键必须唯一且不能有空值，因此在主键上的索引也是唯一索引。一个表上的唯一索引可以有多个，但主键只有一个。一般在建表的同时会创建主键索引,InnoDB中，如果没有指定主键，会隐含性创建一个长度为6的索引。
唯一索引
唯一索引顾名思义，索引必须唯一，唯一索引中允许有空值出现。建立唯一索引的语法如下：
CREATE UNIQUE INDEX indexName ON tablename(columnname(length))
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
普通索引
这种索引没有任何限制，可以有重复数据，也可以为空。建立普通索引的语法如下：
CREATE INDEX indexName ON tablename(columnname(length))
普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
对于varchar类型的字段，索引长度的选取很重要，不能太短起不到索引的效果，也不能太长浪费了存储空间。一般应小于varchar实际长度。
组合索引
当有多个查询条件时，可以采用组合索引，索引可以覆盖多个数据列，可以有选择地使用其中一个索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使 用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。这就是所谓的最左前缀原则。
注意事项
1、全列匹配：当where语句中涉及到多列，且都是精准匹配（=或者in）时，理论上我们应该注意字段的顺序，但mysql会帮我们优化，采用最左前缀匹配。若需要查看一个语句是否有用索引可以在前面加上EXPLAIN关键字。
2、最左前缀匹配：select * from tablename where A=a and B=b and C=c and D=d这条语句，如果在A,B,D上建有索引，那么只在A,B上的索引才有效，D上的索引不会被用到。
3、如果中间某个字段没有索引，后面的索引不会生效。跟2类似，由于C上没有索引，索引D不会生效。可以在(C,D)上单独建一个辅助索引。
select * from tablename where A=a and B=b and D=d这条语句，如果在(A,B,C,D)四列上建有组合索引，A,B肯定能匹配上，由于没有按C进行查询，在D上只能按照where条件过滤，如果C的取值少，可以用in替换这条SQL:select * from tablename where A=a and B=b and C in() and D=d,这个索引可以合理地被利用。
4、某些like语句可以走索引，一般不推荐在sql中用like语句，稍不注意会导致全表扫描。对于title like 'keyword%'这种是可以用到索引的，其他情况比如title like '%keyword',title like '_keyword'不能走索引
5、范围查询，范围查询可以用到索引，必须是最左前缀，范围列后面的索引用不到。一条语句中最好把范围列放最后，不要出现多个范围列。
6、函数表达式 不要在列上直接套用函数或者表达式，这样用不到索引。
小结
索引能有效地提高查询速度，但并不是索引越多越好，索引会消耗存储空间，维护索引需要成本。可以在主键上建索引强调该列的唯一性，如果常按某一列查询，可以考虑建索引，在where子句上建索引提高过滤速度，在需要排序的字段上建索引可以直接利用索引的有序性。
(责任编辑：IT) |