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条件有未建立索引的列导致索引失效
引用内容链接
https://www.cnblogs.com/coshaho/p/7203186.html?utm_source=itdadao&utm_medium=referral
http://www.cnblogs.com/vicenteforever/articles/1789613.html
http://www.cnblogs.com/duanjiapingjy/p/9416137.html