MySQL索引

什么是索引

在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。

在mysql中有多种索引类型

• 主键索引
  也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

• 普通索引(常规索引(INDEX或KEY))
  普通索引一般是在建表后再添加的

  • 单列索引

     ALTER TABLE `testDB`.`user` 
     ADD INDEX `idx_name`(`name`) USING BTREE
    

  • 组合索引

    ADD INDEX `idx_mult`(`name`, `address`) USING BTREE
    

  组合索引最左前缀原则
  例如上面我们创建了一个name, address的组合索引
  select * from user where name = ‘xxx’ 此时，会走索引
  select * from user where address = ‘xxx’ 则不会走索引

• 全文索引
  首先，全文索引主要针对文本文件，比如文章，标题，全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)

        create table c(  
            id int primary key auto_increment ,  
            title varchar(20),  
            content text,  
            fulltext(title,content)  
       )engine=myisam charset utf8;  
        insert into c(title,content) values  
            ('MySQL Tutorial','DBMS stands for DataBase ...'),  
            ('How To Use MySQL Well','After you went through a ...'),  
            ('Optimizing MySQL','In this tutorial we will show ...'),  
            ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),  
            ('MySQL vs. YourSQL','In the following database comparison ...'),  
            ('MySQL Security','When configured properly, MySQL ...');  

• 唯一索引

  ADD UNIQUE INDEX `idx_unique`(`en_name`);
  

  主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
  主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
  唯一性索引列允许空值，而主键列不允许为空值。
  主键列在创建时，已经默认为空值 + 唯一索引了。
  主键可以被其他表引用为外键，而唯一索引不能。
  一个表最多只能创建一个主键，但可以创建多个唯一索引。
  主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

MySQL索引方式？

一般有四种索引方式(BTREE，RTREE， HASH ，FULLTEXT)

• BTREE索引(一种将索引值按一定的算法，存入一个树形的数据结构中)
  MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址
  InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录

  • B-TREE索引

  

  • B+TREE索引

  

  B+Tree相对于B-Tree有几点不同:
  非叶子节点只存储键值信息。
  所有叶子节点之间都有一个链指针。
  数据记录都存放在叶子节点中。

• HASH索引

  • 仅支持"=","IN"和"<=>"精确查询，不能使用范围查询：
    由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash
  • 不支持排序：
    由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
  • 在任何时候都不能避免表扫描：
    由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
  • 检索效率高:
    索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引
  • 只有Memory引擎支持显式的Hash索引:
    它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引

• R-Tree索引
  R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

• FULLTEXT索引
  即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
  值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。
  它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

总结：

(1)对于BTREE这种Mysql默认的索引方式，具有普遍的适用性
(2)由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法，至少我是经常这么做的。
(3)对于一些搜索引擎级别的应用来说，FULLTEXT同样不是一个好的处理方法，Mysql的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。
   真要碰到这种问题，Apache的Lucene或许是你的选择。
(4)正是因为hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。如mysql数据库的内存版本Memsql，使用量很广泛的缓存工具Mencached，NoSql数据库redis等，都使用了hash索引这种形式。
   当然，不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。

扩展：

索引不会命中的情况：

• 1.索引无法存储null值

  a.单列索引无法储null值，复合索引无法储全为null的值。
  b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。

  • 为什么索引列无法存储Null值？

    a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值无法比较，无法确定null出现在索引树的叶子节点位置。）　
    b.如果需要把空值存入索引，方法有二：其一，把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找。其二，建立一个复合索引。例如 create index ind_a on table(col1,1);
    通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。　

• 不适合键值较少的列（重复数据较多的列）

  假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。
  再加上访问索引块，一共要访问大于200个的数据块。
  如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块少一些，肯定就不会利用索引了。

• 索引失效的几种情况

  1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
  2.对于多列索引，不是使用的第一部分，则不会使用索引
  3.like查询以%开头(例如where code like '%AB'条件是不会走索引的，而where code like 'AB%'条件是会走索引的)
  4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
  5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

MySQL的innodb存储引擎支持行级锁，innodb的行锁是通过给索引项加锁实现的，这就意味着只有通过索引条件检索数据时，innodb才使用行锁，否则使用表锁。