MySQL索引使用

MySQL索引使用

[整理人： 整理时间:2010-2-20]

情况：有的时候对一个表创建了索引，但是可能没有用上索引。现在来分析一下到底是什么情况下索引可以用到什么情况下索引又用不到了。

一、索引的意义：
优点：
索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时必须从第一个记录开始扫描表中所有记录，表里面的记录数量越多，这个操作的代价就越高。

缺点：
索引要占用磁盘空间；且任何写操作涉及的索引个数多的话会引起降速，因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。

另外索引会占据磁盘空间。特别是一些大表创建组合索引的时候更加要注意这一点！

 

二、索引理论学习

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

相关的创建语句：

单列的情况：create index one_column on testtb(c1);

多列的情况：create index mult_column on testtb(c1,c2);

2.1 索引类型：
(1)普通索引

这是最基本的索引，它没有任何限制。创建的语法就是上面所写的方法了！

删除的语句：DROP INDEX [indexName] ON mytable;

(2)唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))

PS：允许空，但是这个列的值必须唯一，如果事先有值而且有重复的值就不可以创建这样的索引了。

ERROR 1062 (23000): Duplicate entry '0' for key 1   报类似于这样的错误！

(3)主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

PS：默认在创建一个主键的时候就自动会有一个主键索引出来！

(4)组合索引
为了形象地对比单列索引和组合索引，为表添加多个字段：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：
ALTER TABLE mytable ADD INDEX name_city_age (username(10),city,age);

PS：在创建索引的时候还可以指定一个字段其大小值!而且如果在插入的值中超过了索引的长度值的话还是可以成功保存进来的
建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

PS：哪怕是你插入的值超过了10也是可以成功被插入进来的

 

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

PS：当一个表中同时存在多个单列索引的时候，MySQL就会考虑使用最有效率的那个单列索引值。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
usernname,city ,age username  ,city usernname
为什么没有 city，age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：
SELECT * FROM mytable WHREE username="admin" AND city="郑州"

SELECT * FROM mytable WHREE username="admin"
而下面几个则不会用到：
SELECT * FROM mytable WHREE age=20 AND city="郑州"

SELECT * FROM mytable WHREE city="郑州"

PS：在使用组合索引的时候要注意在什么情况下可以用到索引，而在什么情况下又用不到索引。这个就要分析最左前缀的法则处理了！

 

7)使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：
◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

PS：如果某一列的默认值为NULL的话，就不可以在此列上面创建索引了。因为即使你创建了索引也用不上
◆使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

PS：在创建索引的语句的时候要注意定义好该字段的长度值
◆索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

PS：对于有多列的组合排序操作的时候要考虑使用复合索引技术
◆like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

PS：要注意什么时候会用到索引，什么时候用不到索引
◆不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;

PS：计算之后就用不到索引了
◆不使用NOT IN和<>操作
以上，就对其中MySQL索引类型进行了介绍。

 

结论：通过上面的理论我们学习到了 索引的类型、组合索引的时候如何排列才能用到索引值。另外要注意的是在创建完索引之后如何写SQL语句才能用到索引值。提高查询效率

 

三、什么时候使用索引

分析DEMO

假如有这样的一条SQL语句

 SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果在 col1 和 col2 上有一个多字段索引的话，就能直接取得对应的记录了。如果在 col1 和 col2 分别有独立的索引，那么优化程序会先找到限制最多的那个索引，然后根据哪个索引能找到更少的记录就决定使用哪个索引。

PS： 这就体现出来了组合索引的高效性。通过组合索引能够加快查询的速度。如果分别创建索引的话MYSQL就会按照哪种方式能够找到最少记录的决定使用哪个索引值的！

 

如果表里有一个多字段索引的话，那么该索引的任何最左前缀部分都可以被优化程序用来检索记录。例如，在 (col1, col2, col3) 上有一个索引，那么按字段组合 (col1), (col1, col2), 和 (col1, col2,col3) 搜索的时候都会用到索引。

PS：在上面我们也讲到了 最左前缀 原则。即如果有组合索引的时候要注意 我们的SQL语句的写法一定要考虑到最左前缀的方式。如果写法上面的次序有乱的话就可能用不到索引了。

理解最左前缀的一个示例：

 

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果在 (col1, col2, col3) 上有一个索引，只有第一个查询用到索引了。第二和第三个尽管包括了索引字段，但是 (col2) 和 (col2, col3) 并非索引 (col1, col2, col3) 的最左前缀部分。

PS：如果在(col1, col2, col3) 建立的组合索引的话所谓的最左前缀的含义就是

以col1打头的都可以用到这个组合索引。即会出现如下的情况

(col1), (col1, col2), 和 (col1, col2,col3)

 

所以如果你在写SQL语句的时候一定要注意好这种次序！

 

有些时候尽管有可用的索引，MySQL也不会用到它们。一种情况是优化程序认为如果使用索引会需要检索更大部分的表记录 (这时候，扫描表可能更快，因为这支需要更少的搜索)。尽管如此，如果有一个查询用 LIMIT 限制只检索部分记录，MySQL就一定会使用索引，因为这样能更快检索到更少记录来返回给结果。

 

 

四、其它有关MYSQL索引内容

相关优化的内容见我的博客：

http://blog.chinaunix.net/u2/84280/showart_1962374.html

另见广告：