mysql索引

mysql的索引

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

myisam存储引擎，数据文件、索引文件、表结构文件分开存储

innodb存储引擎，数据和索引存储在一个文件中

这是因为索引使用的数据结构，一般为B+Tree，目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B-Tree介绍

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：
d为大于1的一个正整数，称为B-Tree的度。
h为一个正整数，称为B-Tree的高度。
每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
所有叶节点具有相同的深度，等于树高h。
key和指针互相间隔，节点两端是指针。
一个节点中的key从左到右非递减排列。
所有节点组成树结构。
每个指针要么为null，要么指向另外一个节点。
如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key_1)，其中v(key_1)为node的第一个key的值。
如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(key_m)，其中v(key_m)为node的最后一个key的值。
如果某个指针在节点node的左右相邻key分别是key_i和key_{i+1}且不为null，则其指向节点的所有key小于v(key_{i+1})且大于v(key_i)。

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如 MySQL 就普遍使用B+Tree实现其索引结构。
与B-Tree相比，B+Tree有以下不同点：
每个节点的指针上限为2d而不是2d+1。
内节点不存储data，只存储key；叶子节点不存储指针。

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

FULLTEXT

起初全文索引Myisam存在，而innodb在MySQL5.6引入了全文索引
全文索引，按照我现在的理解，主要是为了处理mysql的like语句，因为使用like语句存在三种情况
1. like %word%
2. like %word
3. like word%
1和2，不可以通过其他类型的索引进行查询，即通配符在like语句后的参数中的开始，这个时候使用mysql查询语句，如果索引格式不是全文索引，是不可以通过索引查询的，会进行全表扫描。
而3可以使用其他索引

HASH

hash索引只能做等于查找，但是无论多大的hash表，查找复杂度都是O(1)
（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。 
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。 
（2）Hash 索引无法被用来避免数据的 排序 操作。 
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算； 
（3）Hash 索引不能利用部分索引键查询。 
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。 
（4）Hash 索引在任何时候都不能避免表扫描。 
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。 
（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 
对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

BTREE

mysql默认的索引类型

RTREE

这个类型的索引很少在mysql中使用

索引格式

默认mysql一个表16个索引。通过配置可以进行修改。

索引种类

普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索

创建索引有优点，也有缺点，这是任何事情必然具有的两面性。

优点：

1 大大减少查询时间

缺点：

1 建立索引，删除索引需要数据库相应。并且数据表数据行数越多，花费时间越长

2 索引同样是需要空间的

索引的使用

索引的建立格式

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名（字段名[(长度)] [ASC|DESC]）;
参数说明
UNIQUE、FULLTEXT和SPATIAL都是可选参数，分别用于表示唯一性索引、全文索引和空间索引。

使用注意

索引分为单列索引和联合索引。
1 单列索引，在查询语句的使用中，仅适合于单列where
2 联合索引，适合多个where条件
    例如，建立的user表中具有name,sex,age字段，索引为user(name,age,sex),排序为asc,sex 1男2女
    以下是使用索引的查询
        select * from user where name = 'user'
        select * from user where name = 'user' and age = 18
        select * from user where name = 'user' and age = 18 and sex = 1
    不使用索引的查询
        1.最佳左前缀原则
        　　组合索引，不按索引定义时制定的顺序 的最左列开始
        2.like ‘%Z’ 
        　　like模糊查询时， 以%开头，导致索引失效
        3.范围之后全失效（> <）
        　　如果是主键或者索引列是整数，索引不会失效
        4.遇到null值，索引失效
        5.索引列上的显式或者隐式运算，导致索引失效
        6.order by
        　　由于查询只使用一个索引，因此，如果where语句使用了索引，order by语句不会使用
        7.不在一个方向的order by导致索引失效
        　　查询使用索引必须 全asc 或 全desc
        8.组合索引，中间跳过了某一列，后面的列全失效
        9.is null is not null != <> 都会导致索引失效，如果这些用在主键列上，仍会使用索引
        10.字符串类型不加单引号，导致索引失效
        　　不加单引号，会有隐式的类型转化（索引列上的计算会导致索引失效）
        11.用or连接导致索引失效
        　　or条件有未建立索引的列导致索引失效