介绍:
索引是是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
概述
在数据库的搜索语句中,select * from 表名 where 条件;通常情况下,会进行全表扫描,效率极低
优缺点
索引结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
我们平常说的索引,如果没有特别指明,都是指B+树结构组织的索引。
几种数据结构
二叉树
若顺序插入
二叉树的缺点:顺序插入时,会形成一个链表,查询性能大大降低,大数据情况下,层级较深,检索速度慢。
AVL树(平衡二叉树)
特点:
本身首先是一棵二叉搜索树。
带有平衡条件:每个结点的左右子树的高度之差的绝对值(平衡因子)最多为1
AVL树,本质上是带了平衡功能的二叉查找树(二叉排序树,二叉搜索树)
缺点
插入数据需要做旋转操作,插入效率低。查询效率高。
节点过深。
红黑树(一种含有红黑结点并能自平衡的二叉树查找树):
红黑树缺点:因为书的高度随着数据量增加而增加,所以大数据量情况下,层级较深,检索速度较慢。
B-Tree(多路平衡查找树)
特点:每个结点都会存储对应数据。
例如一颗最大度数为5的b-tree(每个结点最多存储4个key,5个指针)。
B+Tree
特点:只有叶子结点会存储数据,叶子结点形成了一个单向链表,且链表数据自小而大连接。
只有叶子结点存储数据的好处,方便扫库,只需要扫描一遍叶子节点即可。B+树更适合再区间查询的情况,所以通常用于数据库索引。
例如最大度数为4的b+tree
MySQL中的索引结构
B+Tree
MySQL索引数据结构对经典的b+Tree进行优化,在原B+Tree的基础上,增加了一个指向相邻叶子结点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
Hash
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的桔位上,然后存储在hash表中。
假如我们想要为表的name创建哈希索引的数据结构,首先算出每一行的哈希值,再拿到name的值,通过hash函数计算出对应的哈希值,然后存储到对应位置,如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
特点
Hash索引只能用于对等比较(=,in),不支持范围查询 (between,>,<,...)
无法利用索引完成排序操作。
查询效率高,通常只需要一次检索就可以了,效率通常要高于B+tree索引
Hash索引在查询等值时非常快。
如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题
存储引擎支持
在MySQL中,支持hash索引的是Memory引,而lnnoDB中具有自适应hash功能,hash索引是存储引警根据B+Tre索引在指定条件下自动构建的。
思考:为什么InnoDB存储引擎选择使用B+Tree索引结构?
B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,而B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。
B+树的磁盘读写代价更低:B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B(B-)树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。
索引分类
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
从存储结构上来划分:BTree索引(B-Tree或B+Tree索引),Hash索引,full-index全文索引,R-Tree索引。这里所描述的是索引存储时保存的形式,
聚集索引选取规则:
如果存在主键,主键索引就是聚集索引。
如果不存在主键,将使用第一个唯一 (UNIQUE)索引作为聚集索引
如果表没有主键,或没有合适的唯一索引,则innoDB会自动生成一个rowid作为隐藏的聚集索引。
例如:
当我们进行 select * from user where name='Arm';
首先二级索引,找到Arm保存的id,然后进行回表操作,进行聚集索引,然后找到对应的行数据。
InnoDB中每张表有且仅有一个聚簇索引(就是主键索引),InnoDB中的二级索引是非聚簇索引。
InnoDB中的二级索引的叶子结点中存的是索引列的值和主键值,所以在使用二级索引查询的时候,首先通过二级索引查找到主键值,然后再根据主键值到主键索引的叶子结点中查到对应的整行数据。
思考:是否非聚簇索引一定会进行回表查询吗???
这肯定是不一定的,加入二级索引对应的叶子结点上存有想要查找的值,则不需要回表查询。
例如对上表进行 select id from user where name='Arm';,在索引的结点查询到id则不会进行回表。
索引语法:
创建语法
CREATE[UNIQUE | FULLTEXT ] INDEX index_nam ON table_name ( index_col_name,.... );
一个索引可以关联多个字段,多个字段同时建立一个索引,叫做联合索引。
在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。
思考:为什么需要注意联合索引中的顺序?
MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。
具体原因为:
MySQL使用索引时需要索引有序,假设现在建立了"name,age,phone"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照phonel进行排序。
当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推。因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
思考:为什么要建立联合索引?或者说联合索引的好处?
减少建立索引的开销。建一个(a,b,c)的联合索引,相当于建立了(a),(a,b),(a,b,c)三个索引。
覆盖索引,无需回表。童谣呦复合索引(a,b,c),如果有如下的select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
缩小筛选范围。有1000W条数据的表,有如下select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W*10%=100w 条数据,然后再回表从100w条数据中找到符合b=2 and c= 3的数据,然后再排序,再分页;如果是复合索引,通过索引筛选出1000w *10% *10% *10%=1w,然后再排序、分页。
创建索引
CREATE [ UNIOUE | FULLTEXT] INDEX index name ON table name (index col name,... )
查看索引
SHOW INDEX FROM table name;
删除索引
DROP INDEX index name ON table name
SQL执行频率
MySQL客户端连接成功后,通过 show sesioniglobal] status 命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
SHOW GLOBAL STATUS LIKE ’Com_______';后面是七个下划线
思考:什么是最左前缀法则?
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效。
最左前缀原则就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。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的顺序可以任意调整。
思考:什么情况索引会失效?
范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。例如select * from tb_user where profrssion='软件工程' and age>30 and status=6;(假设profession,age,status是联合索引),这时候发现status索引失效,那么能不能规避这个情况呢???如果业务允许的情况下,我们可以用(>=,<=)代替(>,<)。
索引列操作
不要再索引列上进行运算操作,索引将失效。例如select * from user where phone='11111111111',
此时pbone的单列索引生效,但是如果我们想要进行select * from user where substring(phone,10,2)=15;
假如有字符串数据,在输入时没有加单引号,可以查询出来,但是索引失效。
模糊匹配
如果仅仅是尾部迷糊匹配,索引不会失效,如果时头部模糊匹配,索引失效。
OR连接
用OR分隔开的条件,如果OR前的条件中的列有索引,而后面没有索引,那么涉及的索引都没有被用到。也就是
数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
简而言之就是当查询时,MySQL内部会自动分析是否用索引会更快,如果会更快则用索引,反之放弃索引进行全表扫描。
怎么查看MySQL语句有没有用到索引?
explain或者desc命令获取MySQL如何执行
直接在select语句之前加上关键字
explain / desc SELECT 字段列表 FROM 表名 WHERE 条件;
EXPLAIN执行计划各字段意义
id:SQL查询中的序列号。id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行。
select_type:查询的类型,可以是 以下类型:
table:每个查询对应的表名
possible_key:查询中可能用到的索引 * (可以把用不到的删掉,降低优化器的优化时间) * 。
type(重要):这是最重要的字段之一,显示查询使用了何种类型。从最好到最差的连接类型依次为:NULL,system,const,eq_ref,ref,fulltext,eq_ref,ref,index,range,index,ALL
此时注意,
虽然NULL是效率最高的,但是业务中一般不可能为NULL,
访问系统表是system,
根据主键或者唯一索引是const,
使用非唯一性索引是ref,
range:索引范围查询,常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
用了索引,但是还是对索引进行扫描时index,
all是全表扫描。
possible_keys:查询可能使用到的索引都会在这里列出来
key:查询真正使用到的索引。
key_len:查询用到的索引长度(字节数)
SQL提示
use index(给MySQL提供建议使用某索引,但MySQL不一定接受)
explain select* from tb user use indexidx user pro) where profession = 软件工程;
ignoreindex(给MySQL提供建议忽略某索引,但MySQL不一定接受)
explain select* from tb userignore index(idx user pro) where profession = 软件工程
force index(给MySQL强制使用某索引,但MySQL不一定接受)
explain select * from tb userforce index(idx user pro) where profession = 软件工程
前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘10,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
语法
create index idx xxxx on table name(column(n)); n表示截取字符串前面的n个字符来构建索引
前缀长度
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性,性能也是最好的。
select count(distinct email) / count(*) from tb user;
select count(distinct substring(email,1,5) / count(*) from tb user ;
索引设计原则
1. 针对于 数据量较大,且查询比较频繁的表建立索引。
2. 针对于常作为查询条件 (where)、排序 (order by)、分组 (group by)操作的字段建立索引。
3. 尽量选择 区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
4. 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立 前缀索引。
5. 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
6. 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7. 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个2索引最有效地用于查询。