Mysql索引详解

一、索引定义

        数据库索引是数据库管理系统（DBMS）中一个排列的数据结构，以协助快速查询、更新数据库表中的数据。索引的实现通常使用B树及其变种B+树。

二、索引的优缺点

        优点：可以大大加快数据的检索速度，这也是创建索引最主要的原因。

使用索引，可以在查询中，使用优化隐藏器，提高系统的性能。

        缺点：创建索引和维护索引要耗费时间，具体地，当对表中数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增删改的执行效率。

索引要占用物理空间。

三、索引的类型

        1.主键索引：数据列不允许重复，不允许为null，一个表只能有一个主键。

        2.唯一索引：数据列不允许重复，允许为null，一个表允许多个列创建唯一索引。

                       alter table table_name add unique (column);  创建单列唯一索引。

                       alter table table_name add unique (column1,column2);创建组合唯一索引。

        3.普通索引：无唯一性的限制，允许为null值。

                        alter table table_name add index index_name (column1);创建单列普通索引

                        alter table table_name add index index_name (column1,column2);创建组合索引

        4.全文索引：目前搜索引擎使用的一种关键技术。

                        alter table table_name add fulltext (column1);创建全文索引。

四、索引的数据类型（Hash、B+树）

       1.Hash索引

        在mysql中使用Hash索引，主要是通过hash算法，将数据库字段数据转化成定长的hash值，与这条数据的行指针一并存入hash表对应的位置，如果发生hash碰撞，则在对应的hash键下以链表形式存储。

        特点：

        ① 不是根据数据顺序进行存放的，所以不能进行排序。

        ② 查询的时候要根据hash值进行查询，所以只能等值查询，不能进行范围查询。

        ③ 存在hash碰撞时，效率会降低。

        根据以上特点，不会显式使用哈希索引。

        2.二叉查找树

         二叉查找树（Binary Search Tree，BST）是一种二叉树，其中每个结点最多有两个子结点且具有二叉搜索树性质：左子树上所有结点的值均小于它的根结点的值以及右子树上所有结点的值均大于它的根结点的值。

        特点：左子树的节点 < 父节点

                   右子树的节点 > 父节点

        示例：插入数据：13,6,2,11,17,22,19,4,7,15

        二叉查找树可以支持较高的查询效率，也可以进行快速的插入，效率都比较高，但是查询效率与树的深度有关，有序插入数据的话容易造成斜树。

        示例：插入数据：1,2,3,4,5,6,7,8,9

       3.平衡二叉树 

       平衡二叉查找树（AVL 树）是带了自平衡功能的二叉查找树。当结点树为N时，它能够使 高度平衡为 O(log N)。

示例：顺序插入：1,2,3,4,5,6

        缺点： 磁盘块只有一个数据达不到4kb会浪费大量空间，增加了和磁盘交互的次数，消耗的时间会更多，例如查询3，会与磁盘有2次交互。

        4.多路平衡查找树（B-Tree）

        B-Tree是通过分裂和合并保持树的平衡。

        5.加强版多路平衡查找树（B+Tree）

        节点拥有的子树数量称为度。

        关键子树：N

        度（Degree）：N

        1<=key<28  [1,28} ——> P1

        28<=key<66  [1,28} ——> P2

        66<=key  [66,+∞} ——> P3

         特点：①n颗子树的节点包含n个关键字，不用来保存数据而是保存数据的索引。

                    ②所有的叶子节点中包含了全部的关键字的信息，及指向这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接，可以提高范围查找或排序的速率，同时数据只从叶子节点返回，查询的磁盘ID更加稳定。

五、索引的基本原理

        索引用来快速地寻找那些具有代表值的记录。如果没有索引，一般来说，执行查询时遍历整张表，索引的原理就是把无序的数据变成有序的查询。

六、索引算法

        B-Tree算法：mysql默认算法，不仅可以被用在=，=>,<=,>,<和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个可以通配符开头的常量。

                select * from user where name like 'jack%';使用索引。

                select * from user where name like '%jack';不使用索引。

        Hash算法：hash索引只能用于对等比较。

七、索引设计的原则

        1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列
        2.基数较小的类，索引效果较差，没有必要在此列建立索引
        3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
        4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

八、创建索引的原则

         1.最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d顺序可以任意调整。

        2.较频繁作为查询条件的字段才去创建索引

        3.更新频繁字段不适合创建索引

.        4.若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

        5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

        6.定义有外键的数据列一定要建立索引。

        7.对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

        8.对于定义为text、image和bit的数据类型的列不要建立索引。

九、创建索引的三种方式，删除索引
        第一种方式：在执行CREATE TABLE时创建索引

                CREATE TABLE user_index2 (
                    id INT auto_increment PRIMARY KEY,
                    first_name VARCHAR (16),
                    last_name VARCHAR (16),
                    id_card VARCHAR (18),
                    information text,
                    KEY name (first_name, last_name),
                    FULLTEXT KEY (information),
                    UNIQUE KEY (id_card)
                );
        第二种方式：使用ALTER TABLE命令去增加索引

                ALTER TABLE table_name ADD INDEX index_name (column_list);
                ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

       其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

        索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

        第三种方式：使用CREATE INDEX命令创建

        CREATE INDEX index_name ON table_name (column_list);
        CREATE INDEX可对表增加普通索引或UNIQUE索引。（不能创建PRIMARY KEY索引）

        删除索引

        根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

                alter table user_index drop KEY name;
                alter table user_index drop KEY id_card;
                alter table user_index drop KEY information;
        删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

        需要取消自增长再行删除：

        alter table user_index
        -- 重新定义字段
        MODIFY id int,
        drop PRIMARY KEY
但通常不会删除主键，因为设计主键一定与业务逻辑无关。

十、引擎

        1.Myisam引擎与InnoDB引擎区别

        InnoDB引擎：提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束。

        Myisam引擎：不提供事务的支持，也不支持行级锁和外键。

        Memory引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

        2.Myisam索引与InnoDB索引区别

        InnoDB索引是聚集索引，Myisam索引是非聚集索引。

        InnoDB索引的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

        Myisam索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

        InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时能做到覆盖索引会比较高。

        Myisam索引文件目录：

                .frm存储数据库中的表结构文件

                .MYD存储的是数据文件

                .MYI存储的是索引的文件

        InnoDB索引文件目录：   

                .frm存储数据库中的表结构文件

                .ibd存储的是数据以及索引

                主键索引

                 辅助索引

        辅助索引：存储索引和主键值                                 主键索引：存储索引和数据 

        辅助索引叶子节点存储的是索引和主键值，如果没有主键索引的话，数据库会自动维护一列作为主键索引。

十一、前缀索引     

        语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

        前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

        实操的难度：在于前缀截取的长度。

        我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录

十二、非聚簇索引一定会回表查询吗？

        不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

        举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

十三、联合索引

        MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

十四、覆盖索引

        是select的数据列，只用从索引中就能够取得，不必读取行数据。换句话说，查询列要被所建的索引覆盖。