mysql中索引的建立及利弊的分析

当数据库中表的数据很大的时候，索引可以提高查询效率。（以上例student表为例）

表结构如下：

 Field   Type         Collation          Null    Key     Default  Extra         
 ------  -----------  -----------------  ------  ------  -------  --------------
 id      int(5)       (NULL)             NO      PRI     (NULL)   auto_increment
 name    varchar(30)  gb2312_chinese_ci  NO                                     
 age     int(3)       (NULL)             YES             (NULL)                 
 Iphone  varchar(11)  gb2312_chinese_ci  NO              (NULL)                 
 email   varchar(30)  gb2312_chinese_ci  YES             (NULL)                 
 QQ      varchar(9)   gb2312_chinese_ci  NO              (NULL) 

 

 # 1、建立普通索引的两种方式
 create index idnex1 on student(name(15));
 alter table student add index idnex1 (name(15));
 #char,varchar可以不设置长度，如果太长，最好设置长度;text和blob必须设置长度
 #name为30，但是一般名字长度不超过15个，故这里只在前15个字符上建立索引，这样会加速索引查询速度
 #还会减少索引文件的大小，提高insert update等的更新效率。
 #删除索引
 drop index indexname on student;

 student  CREATE TABLE `student` (                  
           `id` int(5) NOT NULL auto_increment,    
           `name` varchar(30) NOT NULL default '', 
           `age` int(3) default NULL,              
           `Iphone` varchar(11) NOT NULL,          
           `email` varchar(30) default NULL,       
           `QQ` varchar(9) NOT NULL,               
           PRIMARY KEY  (`id`),                    
           KEY `idnex1` (`name`(15))            
         ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 

 

  # 2、建立唯一索引,索引列的值必须唯一，但是允许有空值

  create unique index indexmail on student(email(10));
  alter table student add unique index indexmail (email(10));

  # 2.1 创建组合的唯一索引
  create unique index indexname_age_Iphone on student(name(10),age,Iphone);
  alter table student add unique index indexname_age_iphone (name(15),age,Iphone);

  student  CREATE TABLE `student` (                                         
           `id` int(5) NOT NULL auto_increment,                           
           `name` varchar(30) NOT NULL default '',                        
           `age` int(3) default NULL,                                     
           `Iphone` varchar(11) NOT NULL,                                 
           `email` varchar(30) default NULL,                              
           `QQ` varchar(9) NOT NULL,                                      
           PRIMARY KEY  (`id`),                                           
           UNIQUE KEY `indexmail` (`email`(10)),                          
           UNIQUE KEY `indexname_age_Iphone` (`name`(10),`age`,`Iphone`), 
           KEY `idnex1` (`name`(15))                                      
         ) ENGINE=InnoDB DEFAULT CHARSET=gb2312

 

  # 3 组合索引
  alter table student add index indexname_age_iphone (name(15),age,Iphone);
  #如果分别在name，age，Iphone上建立单列索引，让该表有3个单列索引，查询时和此组合索引的效率是
  #有所不同的，且远远低于此组合索引。原因是mysql在多个索引中，只能用到其中的那个它认为最有效的
  #的单列索引。上述组合索引的建立，实际是相当于分别建立了下面三组组合索引：
  name,age,Iphone
  name,age
  name
  #mysql的组合索引是最左前缀的结果，也就是从左到右开始组合，所以，并不是只要包含这三类的查询
  #都会用到索引。举个例子
  select * from student where name='jim' and age=18;
  select * from student where name='tom'
  #上面两个语句都会用到索引，下面的两个则不会用到
  select * from student where age=20 and Iphone='18665432112'
  select * from student where age=22

  # 4 建立索引的机制
  #学习了如何建立索引，更要理解，什么时候建立索引，建立什么样的索引更重要。一般来说，在where和join中
  #出现的列需要建立索引，因为mysql只对<,<=,=,>,>=,beween,in,以及like 'name%'时候使用索引。（因为在以
  #通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引）。例如:在name字段建立索引
  select * from student where name like 'T%' and age=20;#会使用索引
  select * from student where name like '%tom' and age=20;#不会使用索引

 

 # 5 索引的不足之处 （双刃剑）
  #上面都是建立索引的好处，但是过多的使用索引就会造成滥用索引。因此，索引有以下几大缺点：
  #（1）建立索引可以提高查询效率，同事会降低更新表的速度，比如对表进行insert、update和delete操作时，
  #mysql不仅要保存数据，还要保存一下索引文件。
  #（2）建立索引会占用磁盘空间的索引文件。一般情况下这个问题不会太严重，但是如果你在一个大表上建立了
  #多种组合索引，索引文件就会膨胀的很快，所以建立索引要合理。合理的索引只是提高查询速度的一个因数，
  #如果你的mysql有大量数据的表，就需要花时间研究建立最优秀的索引，或者优化查询语句。

  # 5 使用索引的注意事项
  #（1）索引不会包含有NULL值的列
  #只要列中包含有NULL值都将不会被包含在索引中，符合索引中只要有一列含有NULL值，那么这一列对此复合索引
  #就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  #（2）使用短索引
  #对字符串经行索引，如果可以指定一个前缀长度。例如有一个char(255)，在前10个或者20个字符内，多数值是
  #唯一的，那么就不要对整个列经行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  #（3）索引列的排序
  #mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
  #因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给出
  #这些列创建组合索引。
  #（4）like语句操作
  #一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like“%name%”不适用索引，而like
  #"name%"可以使用索引。
  #（5）不要在列上经行运算
  #select * from student where YEAR(adddate)<2012
  #将在每个行上经行运算，这将导致索引失效而经行全表扫描，此语句需要改为
  #select * from student where adddate<'2012-01-01'
  #（6）不适用NOT IN和<>操作
  #有关索引参考http://database.51cto.com/art/200910/156685.htm