mysql索引

索引简介：

无特殊情况，提起索引多数指的是B-Tree索引，innodb使用的是B+Tree索引，一种树形结构
索引分类：
    B-Tree/B+Tree:innodb(只支持B+Tree)，primary key、unique key、key
    哈希索引:memory引擎
    R-Tree空间索引:MyISAM引擎
    FULLTEXT全文索引:MyISAM引擎支持，适用MATCH AGAINST操作而不是普通的where条件
索引的优点:
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序(order by)和临时表
3.索引可以将随机IO变为顺序IO

B+Tree索引

B+树索引分为两种形态，一种叫做聚簇索引（clustered index ），一种叫做非聚簇索引（secondary index）
    这两种索引的共同点是内部都是B+树，高度都是平衡的，叶节点存放着所有数据。不同点是叶节点是否存放着一整行数据。
    这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式。
(1) 聚集索引clustered index
    Innodb存储引擎表是索引组织表，即表中数据按主键顺序存放。而聚集索引就是按每张表的主键构造一颗B+树。并且叶节点存放整张表的行记录数据。每张表只能有一个聚集索引（一个主键）。
    聚集索引的另一个好处是它对于主键的排序查找和范围的速度非常快。叶节点的数据就是我们要找的数据。      
    主键排序查找：例如我们要找出最新的10条团购订单，由于B+树是双项链表，我们可以迅速找到最后一个页，并取出10条记录，我们用Explain进行分析：      
    12:41:32 tuangou> explain select * from groupon_so order by id desc limit 10\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: groupon_so
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 8
              ref: NULL
             rows: 10
            Extra: 
    1 row in set (0.00 sec)    
    主键范围查找：如果要通过主键查找某一范围内的数据，通过叶节点的上层中间节点就能得到页的范围，之后直接读取数据页即可：
    12:50:19 tuangou> explain select * from groupon_so where id>10000000 and id<12000000\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: groupon_so
             type: range
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 8
              ref: NULL
             rows: 4301486
            Extra: Using where
    1 row in set (0.00 sec)  
(2) 辅助索引secondary index
    辅助索引（也称非聚集索引）。叶级别不包含行的全部数据，叶级别除了包含行的键值以外，每个索引行还包含了一个书签（bookmark），该书签告诉innodb存储引擎，哪里可以找到与索引对应的数据。
    辅助索引的存在并不影响数据再聚集索引中的组织，因此一个表可以有多个辅助索引。当通过辅助索引查找数据时，innodb会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键。然后再通过主键索引找到一行完整的数据。  
(3) B+树索引的管理
    索引的创建和删除可以用两种方式。一种是alter table,另一种是create/drop index     
    alter table 创建和删除索引的语法为：
    ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
      | ADD {INDEX|KEY} [index_name]
            [index_type] (index_col_name,...) [index_option] ...

    ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
      | DROP PRIMARY KEY
      | DROP {INDEX|KEY} index_name     
    create/drop index的语法为：
    CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
        [index_type]
        ON tbl_name (index_col_name,...)

    DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name   
    MySQL索引注意的问题：对于MySQL索引的添加和删除操作，MySQL先是创建一张加好索引的临时表，然后把数据导入临时表，再删除原表，把临时表重命名为原表。
    Innodb存储引擎从Innodb Plugin版本开始，支持一种快速创建索引的方法（只限于辅助索引，主键索引仍需要建临时表）。首先对表加S锁，在创建的过程中不需要重建表，但是由于上了S锁，在创建索引的过程中只能进行查询操作，不能更新数据。
    一次查询只能使用一个索引，且索引数据都是排序存放的

innodb索引

innodb使用的就是BTree索引，按索引类型分为primary key、unique、普通索引
1. 普通索引
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
2. 唯一索引unique
普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个”员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简 化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在 某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯 一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
3. 主索引primary
在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY而不是UNIQUE。
4. 外键索引
如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
5. 复合索引
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使 用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。
6. 索引的长度
在为CHAR和VARCHAR类型的数据列定义索引时，可以把索引的长度限制为一个给定的字符个数(这个数字必须小于这个字段所允许的最大字符个数)。这 么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置 为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。
在为BLOB和TEXT类型的数据列创建索引时，必须对索引的长度做出限制；MySQL所允许的最大索引长度是255个字符。
查询和索引的优化
7.索引的优化
只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令 之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
在不确定应该在哪些数据列上创建索引的时候，人们从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键 字，MySQL将不是去执行那条SELECT命令，而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出 来。
在EXPLAIN命令的输出结果里，第1列是从数据库读取的数据表的名字，它们按被读取的先后顺序排列。type列指定了本数据表与其它数据表之间的关联 关系(JOIN)。在各种类型的关联关系当中，效率最高的是system，然后依次是const、eq_ref、ref、range、index和 All(All的意思是：对应于上一级数据表里的每一条记录，这个数据表里的所有记录都必须被读取一遍–这种情况往往可以用一索引来避免)。
possible_keys数据列给出了MySQL在搜索数据记录时可选用的各个索引。key数据列是MySQL实际选用的索引，这个索引按字节计算的长 度在key_len数据列里给出。比如说，对于一个INTEGER数据列的索引，这个字节长度将是4。如果用到了复合索引，在key_len数据列里还可 以看到MySQL具体使用了它的哪些部分。作为一般规律，key_len数据列里的值越小越好(意思是更快)。
ref数据列给出了关联关系中另一个数据表里的数据列的名字。row数据列是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。row 数据列里的所有数字的乘积可以让我们大致了解这个查询需要处理多少组合。
最后，extra数据列提供了与JOIN操作有关的更多信息，比如说，如果MySQL在执行这个查询时必须创建一个临时数据表，就会在extra列看到 using temporary字样。

mysql 联合索引详解

联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
如果不按照索引的最左列进行查找，则无法使用到索引
如果某个列使用了范围查询，则其左侧的字段无法使用索引，如where a=1 and b like 'love%' c='2'则只能a和b使用到索引，c无法走索引
两个或更多个列上的索引被称作复合索引。
利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

eg:建立 姓名、年龄、性别的复合索引。
create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);
按值查找
优: select * from test where a=10 and b>50 //精确匹配并范围匹配
优: select * from test where a=50  //精确匹配
按顺序查找
优: select * from test order by a  //按照a进行排序，但是a有索引，直接就是索引查找，无需排序，索引都是排序存放的
差: select * from test order by b
差: select * from test order by c
优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c
优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c
例如下面的复合索引:
    |姓名|年龄|性别|
起作用的查询:
a.单独查询姓名
b.查询姓名和年龄
c。查询姓名、年龄和性别
不起作用的查询:
a.单独查年龄或者单独查性别
b.查年龄和性别

索引原则
1.索引越少越好
原因：主要在修改数据时，第个索引都要进行更新，降低写速度。
2.最窄的字段放在键的左边
3.避免file sort排序，临时表和表扫描.