全中国只有一所学校, 就是党校 -- 其它的学校都是分校[email protected]陈丹青 (艺术家)

MySQL索引详解

MySQL 2018-03-18 浏览量: 764 字数统计: 5037 最后更新: 2018-03-24 18:27

文章目录[显示]

MySQL索引

什么是索引?

相当于书目录 用于快速检索

索引的优点

  1. 提高数据的检索效率
  2. 提高表和表之间join的效率
  3. 利用唯一性索引,保证了数据的唯一性
  4. 提高排序和分组效率

缺点

  1. 消耗更多物理存储
  2. 数据变更时,索引也需要更新,会降低一定的更新效率,表上有多个索引,大量insert时cpu的%sys会很高

应该创建索引

  1. 经常检索的列
  2. 经常用于表连接的列
  3. 经常排序/分组的列

索引不使用建议

  1. 基数很低的列 (例如 是和否 男和女 0和1)
  2. 更新频繁但检索不频繁的列
  3. BLOB/TEXT等长内容列
  4. 很少用于检索的列

索引类型:

按逻辑分类

  • 唯一索引
  • 非唯一索引
  • 单列索引
  • 多列索引

按物理存储分类

  • 聚集索引
  • 非聚集索引

按数据结构分类

  • B+树
  • HASH索引(Mermory默认的索引)
  • 空间索引(很少用)
  • fractal tree索引 (是一种面向磁盘I/O优化的数据结构,也叫分形树索引,是TokuDB里面的概念)

联合索引

MySQL联合索引:从左匹配原则( 假设存在组合索引 t1 a1a2(a1,a2),查询语句select from t1 where a1=1 and a2=2能够使用该索引。查询语句select from t1 where a1=1也能够使用该索引。但是 查询语句select * from t1 where a2=2不能够使用该索引,如果想要使用 a2列进行查找,必需出现a1等于某值才能用。)

  • 适合where条件中的多列组合
  • 有时候 还可以用于避免回表(覆盖索引)
  • mysql还支持多列不同排序规则

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要,最好给这些列创建复合索引。

一般情况下不鼓励使用like操作,like “%aaa%” 不会使用索引,而like “aaa%”可以使用索引。

联合索引建议

  • where条件中 经常同时出现的列放在联合索引中
  • 把选择性(过滤性/基数)最大的列放在联合索引的最左边

Hash索引

也叫散列索引,MySQL中,只有Memory存储引擎显示支持hash索引,是Memory表的默认索引类型,但Memory表也可以使用B-Tree索引。Memory存储引擎支持非唯一hash索引,如果多个值有相同的hash code,索引把它们的行指针用链表保存到同一个hash表项中。

特点:

  • 建立在hash表的基础上,只对使用索引的每个值精确查找有用。

缺点:

  • 由于索引仅包含hash code和记录指针,所以,MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的,不会对性造成太大的影响。
  • 不能使用hash索引排序。
  • Hash索引不支持键的部分匹配,因为是通过整个索引值来计算hash值的,所以只能精确查找。
  • Hash索引只支持等值比较,例如使用=,IN( )和<=>。对于WHERE price>100并不能加速查询。

B 树

B-Tree索引进行全关键字、关键字范围和关键字前缀查询,当然,如果想使用索引,你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。

使用前提:

数组是要排序的. 没有排序就不能用二叉查找,因为二叉树原理就是排序之后找中间值。在InnoDB中找记录,其实就是找到页之后,利用row offset array (偏序),用二叉树进行定位。二叉树效率很高,数据量越大越好。

由于B-树中的节点都是顺序存储的,所以可以利用索引进行查找(找某些值),也可以对查询结果进行order by 。

B-tree缺点:

  • 查询必须从索引的最左边的列开始,如果1,2,3列为索引,你不能利用2索引查找。
  • 不能跳过某一索引列。例如不能利用2,3 索引查找。
  • 存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为where XX1=XX AND XX2 like 'XX%' and XX3=XXX,此查询只会使用索引中的前两列,因为like 是范围查询。

B+树

B+树用来存储数据,来取得面向块设备的存储。特别是文件系统,和B Tree比较,B+树所有记录都存放在叶子节点,键值都存放在非叶节点。 而 B tree是各个层级都会存放记录的。

特点:

  • B+ tree有很高的扇出,有很好的IO性能。(扇出是指针)
  • 当叶子节点中的记录满了时,就要进行分列,把中间节点提取出来,放到上一层级,然后会这个页进行分裂,一个页就变成了两个页。
  • B+tree每个叶子节点之间有指针进行串联,这样,如果进行扫描,直接从一个页扫到另一个页就可以了。 叶子节点满了就分列。
  • 所有叶子结点包含了全部关键字的信息,及指向这些关键字记录的指针,叶子结点本身依赖关键字的大小从小到大顺序连接。
  • B+tree Height (一般都是 2- 3 层 ,层数低,高扇出)决定了I/O 低,从而决定了性能,不论是磁盘的random read(随机),还是sequential read(顺序)。

B+树有两种索引形式:

  • 聚集索引(Clustered index) (叶子节点存放的是整条记录。(主键就是聚集索引)
  • 非聚集索引(Non clustered index)也叫(Secondary index)(叶子节点存放的是行标示符(row identifier)

聚集索引 VS 非聚集索引:
(根节点存的是键值和point)

Clustered index key = 4 bytes
Secondary index key = 4 bytes
Key pointer = 6 bytes (指针)
Average row length = 300 bytes
Page size = 16K = 16384 bytes
Average node occupancy = 70% ( both for leaf and index page )(平均节点填充率70%)
Fan-out for clustered index = 16384 * 70% / ( 4+6 ) = 1000 (扇出)
Fan-out for secondary index = 16384 * 70% / ( 4+ 6 ) = 1000
Average row per page for clustered index = 16384 * 70% / 300 = 35
Average row per page for clustered index = 16384 * 70% / ( 4 + 6 ) = 1000

索引组织表与B+

  • IOT(索引组织表):数据都是通过索引的方式进行组织的,逻辑上有序,页与页之间也是逻辑有序的。书签查找特性。(自增的话是物理有序的)。还具有高扇出特性,一般在DB中B+树的高度在2-3层左右,也就意味着只需要2-3次的I/O操作,现在磁盘每秒差不多在100次IO左右,2-3次意味着查询时间只需0.02-0.03秒。
    Innodb 就是索引组织表,而myisam 是堆表。

所有的二级索引(里面存的是其他键值和主键值) 通过辅助索引查找记录的话,需要查两次记录。先根据二级索引查到叶子节点,找到主键值,然后根据主键值索引查到主键值对应的完整的记录。

可以把index理解成表,表就是Index,index就是表。
先找到id,再根据id找到字段,查了两个表,理解为查了两个index。
比如这个表:
CREATE TABLE User (
userid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30),
registdate DATETIME,
email VARCHAR(50),
PRIMARY KEY (userid),
UNIQUE KEY idx_username (username),
KEY idex_registdate (registdate)
);
这个表 理解为有3个表:
CREATE TABLE User (
userid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30),
registdate DATETIME,
email VARCHAR(50),
PRIMARY KEY (userid)
);
CREATE TABLE idx_username (
userid INT NOT NULL,
username VARCHAR(30),
PRIMARY KEY (username,userid)
);
CREATE TABLE idx_registdate (
userid INT NOT NULL,
registdate DATETIME),
PRIMARY KEY (registdate,userid)
);

要select email where key(registdate=xx),就理解为先查idx_registdate这个表,找到对应主键值后,再去User表中 根据主键值找到对应的记录。

对一张表插入一条记录,也就是对3个表insert 记录,而且是以事务的形式插入的,操作一个index,一条row插入了,其实是多个row插入了,而且是一个事务,要么所有插入,要不就都rollback。

B+Tree Index in MyISAM:

heap table(堆表)

  • 所有索引都是二级索引,主键和普通索引的区别仅仅为是否允许为null 和 是否唯一 。
  • 在myisam中,主键和普通索引的访问成本一样。好处就是快。 (innodb的二级索引比聚集索引性能差)
  • 叶子节点存的是 键值 和 row identifier(保存的是物理地址指针,指向的是data)

缺点

由于 row identifier 存的是物理位置,如果row位置发生改变了,就需要新的物理位置存放记录,这时,所有index地址都需要改变。(所有point都需要更新)


B+ VS Hash

  1. 大量不同数据等值精确查询,哈希索引的效率要比B+树高
  2. hash索引不支持模糊查找
  3. hash索引不支持联合索引中的最左匹配规则
  4. hash索引不支持排序
  5. hash索引不支持范围查询
  6. 大多数的场景下,都会范围查询 排序 分组等查询特性,用b+树索引就可以
  7. 等值查询用 hash索引
  8. 在mysql里 hash索引只能应用于 HEAP/MEMORY引擎表

聚集索引

聚集索引是一种索引,该索引中键值的逻辑顺序决定了表数据行的物理顺序。(是按照每张表的主键构造一颗B+,并且叶节点存放着整张表的行记录数据,也让聚集索引也是索引的一部分。)在很多情况下,查询优化器非常倾向于采用聚集索引,因为聚集索引能够让我们在索引的叶节点上直接找到数据。

注意:

  • 每张表只能建一个聚集索引,除了TokuDB引擎
  • innodb 聚集索引即表 表即聚集索引
  • myiam没有聚集索引的概念

聚集索引优先选择列

  1. 含有大量非重复值的列
  2. 数据连续(顺序)存储的列

不建议的聚集索引

  1. 修改频繁的列
  2. 新增数据太过离散随机的列

主键索引

  • 主键索引就是聚集索引
  • 主键由表中的一个或多个字段组成,它的值用于唯一标识表中的某一条记录
  • 在表引用中 主键在一个表中引用来自于另一个表中特定记录
  • 保证数据的完整性 ,加快数据的操作速度
  • 主键值不能重复 也不能包含NULL

主键选择建议

  1. 对业务透明,且无意义,免受业务变化的影响
  2. 主键要很少修改和删除
  3. 主键最好的自增的
  4. 不要具有动态属性 例如最后修改时间戳
  5. 主键索引最好不用cahr或者varchar

INNODB聚集索引选择次序原则 (显示声明的主键 ,没有的话 是第一个不包含null值的唯一索引,再没有的话 就用 内置rowid )

唯一索引

  • 不许具有索引值相同的行,从而禁止重复的索引或键值
  • 严格意义上讲 应该叫唯一约束
  • 在唯一约束上和主键一样

和主键索引不同的方面

  1. 唯一索引可以有空值(null)
  2. 一个表只能有一个主键,但可以有多个唯一索引
  3. innodb表中主键必须是聚集索引,但聚集索引可能不是主键
  4. 唯一索引约束可以临时禁用,但主键不行

前缀索引

部分索引的原因

  • char/varchar 太长全部做索引的话,效率太差,存在浪费
  • blob/text类型 不能整列作为索引列

部分索引选择建议

  • 先统计一个平均值,然后再在平均值的基础上适当的增加几个 长度,然后再看一下 能覆盖多少,一般80% 就足够了,甚至也可以大一些 覆盖 90%的长度就基本上可以满足绝大多数的场景,就可以创建部分的索引 。
  • 遵循2/8原则(覆盖80%数据就OK)

怎么统计平均值

比如这个列最大的长度是五十个字符,然后我们可以统计一下这个列实际的存储的字符数,平均下来可能只有15个字符,那我们就可以去判断一下,如果我的长度是17 18 19 ,那我们看他能覆盖 百分之多少的数据量,大概能覆盖80% 的数据量就足够了,那我们就可以创建,比如说这几个长度的部分索引,就没有必要对整个列进行索引了。

缺点

无法利用前缀索引完成排序(因为如果这个列的实际长度是23 ,但是我们只创建了前面17 字符的长度的话,那么正好很不巧的是 有两个数据,他前面实际的长度都是 17 个字符的话,就没有办法完成排序了,需要把整个列全部拿出来才能得到结果)


外键约束(几乎不用)

目的& 前提

  • 确保存储在外键表中的数据是一致性 完整性
  • 前提 本表列须与外键列类型相同(外键须是外表的主键)

外键选择原则

  • 为关联字创建外键
  • 所有的键必须唯一
  • 避免使用符合键
  • 外键总是关联唯一的键字段

缺点是

  • 更多的行锁等待,有更大的可能性导致行锁等待,而且有更大的可能性导致 死锁的发生,所以一般建议少用外键。

全文索引

  • 5.6 以前 fulltext只支持myisam引擎
  • 5.6 也开始支持innodb引擎
  • 5.7以前 中文支持很差
  • 优先使用 shpinxlucenesolr等实现中文检索

索引管理

  • 创建索引的时候可以指定索引的类型,比如说using Btree(Btree 有时候是一个B+树的缩写,并不是真正的表示 Btree )
  • 根据最左匹配原则,一个索引是另一个索引的子集,所以说只能用到部分索引,(比如 c2 是c2c3的一个冗余索引 )(但是冗余索引并不一定就是真的冗余,有时候也有一定的用途)
  • 在5.7以前,可以用percona 的工具里面的 pt-duplicate-key-checker 这个工具来检查或者使用 5.7以后的schema_redudant_indexes 来看有没有冗余索引
  • pt-index-usage 是用来查索引的利用率的,也是需要用percona 的分支版本才能查看,因为 plugerin可以统计每个索引的利用率,但是如果是官方版本的话,除非是5.7以后,5.7以后可以看schema 的 schema_unused_indexes 才可以看到底哪些索引是没有用的。

创建删除索引

alter table test1 add index(name);
alter table t1 add unique index sid(id);
alter table t3 add PRIMARY KEY (id); # 主键索引
alter table t1 add index abc(id,name); # 联合索引
create table test1(id int(4) NOT NULL,name varchar(5),info char(5),INDEX(id));
create table t1(id int(4),UNIQUE index uniqidx(id); # 唯一索引
create table t2(id int(4),name varchar(8),age varchar(8) , index mauti(id,name)); # 联合索引
drop index name on test1;

使用索引

select * from t2 use index(PRIMARY);
select * from t2 force index(PRIMARY);
user index =建议使用
force index =强制使用

甚至可以这样

`select ... from table_name force index(IDX1,IDX2) where ...
select ... from table_name force index(IDX1) force index for join (IDX2) where ....
select ... from table_name force index(IDX1) use/force/ignore index for join/order by/group by(ignore 表示忽略某些索引)(可以指定索引用于 join 或者是排序 分组等等)


索引上其他的概念

覆盖索引

通过索引数据结构 即可直接返回值,不需要回表
执行计划中,显示关键字 using index

  • type = all(表示全表扫描)
  • type = index (表示全索引扫描)(除非你能用到覆盖索引,加上全索引扫描,否则只是普通的全索引扫描,其实效率是很差的,还不如做全表扫描)

为什么一个update 用到了索引,还是全表扫描??????

因为我们利用普通的索引进行全索引扫描的话,普通索引他的存储的顺序跟我们表的聚集索引的顺序有可能是不一致的,虽然你用普通索引进行全索引扫描,但是普通的索引全索引扫描他的顺序其实和表的实际的存储的顺序可能是完全不一致的,反而会导致这个全索引扫描效率更差,因为你扫描索引以后需要再进行回表,回表以后,这个数据的读反而是更随机的离散IO的话,反而效率会更低,所以更推荐使用全表扫描,全表扫描相当于是对整个data page 直接做整个page的读取,速度反而是更快的。

如果explain 输出的 Extra: Using index condition, 而不是Using index,这说明,使用的检索方式为二级检索,也就是有回表的概念,所以会有一定的性能消耗。

sql回表

回表就是 一条select语句 中 where中的字段有索引,先通过索引定位记录行的rowid,然后通过rowid回到表中读取相关数据

例如

select * from test1 where id ='1'; #id字段有索引,但是需要回表取行的所有记录

select id,name from test1 where id >='1'; # id和name 是联合索引,这种情况不需要回表


myisam 索引特点

  1. myisam 是无序的 堆组织表
  2. myisam的索引都是指向到数据的物理地址
  3. 索引的key对应的数据 数据值是无序的

分析:

  1. myisam 里面的主键索引和唯一索引其实是一样的,首先这个索引也是一个基本的B+ 树索引
  2. 主键索引为 辅助索引唯一的区别就是 主键索引不能为空,而 myisam 索引可以为空
  3. 但是最后的叶子结点指向的都是行 row_number,所以说myisam 主键索引和 辅助索引并没有什么区别,主要的区别在于innodb 的索引。

普通索引和唯一索引 那个更快?
唯一索引更快,因为,普通索引 即使数据唯一 也会范围扫描


innodb 索引特点

  • innodb 主键索引,主键列、事务ID、rollback pointer 、其他的就是非指针列,也就是innodb的主键索引或者是聚集索引, 跟着的是整行数据。innodb 的主键 叶子结点指向的是row data。
    而普通索引 ,key 是 列值 ,value是 对应主键的值,提高数据检索扫描的数据 。如果没有主键用唯一索引 没有唯一索引用rowid。

innodb 的 普通索引(也就是 辅助索引)

普通索引的叶子结点最终是整个索引树的 (主键的值+列的值)

innodb 主键索引 VS 辅助索引

innodb 的主键 叶子结点指向的是row data
innodb的 普通索引指向的叶子结点是 主键的值+列的值


索引里面NULL是怎么处理的

innodb_stats_method:nulls_equal ,表示所有的null都被认为是 相同的被放在同一个里,或者而是所有的null都是不同的也就是 nulls_unequal,表示的是每一个null都是 不同值)

myisam 默认是所有的null 是 不同的,而innodb里面默认的所有的null都是 相同的。


什么时候索引是不可用的

  • 通过索引扫描的记录超过了30% ,变成了全表扫描,因为基于辅助索引的扫描,很有可能他的随机扫描 是基于随机的扫描,效率反而 更低,所以说直接变成了全表扫描,还不如直接变成全表扫描来的更快。
  • 联合索引中,第一个索引(或者说是最左边的索引)列使用范围查询(这个时候 右边的 就用不到索引了,只能用到部分索引)
  • 联合索引中,第一个查询条件不是最左索引列。
  • 模糊查询条件列最左以通配符% 开始
  • heap (也就是内存表)表使用hash 索引时,使用范围 检索或者order by ,
  • 多表关联时,排序字段不属于驱动表,无法利用索引完成排序
  • 两个独立索引,其中一个用于检索,一个用于排序(只能用到一个)
  • join 查询的时候,关联列数据类型不一致也会导致索引不可用(int 和bigint 可能会导致不可用)(传递的字符集不一致也可能导致索引不可用)(也就是说table join的列一定要一致)。

索引怎么影响insert效率的

  1. MyISAM批量导入数据时,先disable keys;
  2. InnoDB批量导入数据时,先删除主键外的其他索引,导入完成后再创建索引;

说白了就是:有索引就会影响更新和插入的效率,在批量数据写入或者更新的时候我们可以临时先把索引删掉,等所有的数据写完了 更新完了,再把索引加回来。

类型的隐式转换

比如 c1 是一个varchar列,而select 的时候 放的类型是int类型,这个时候 需要把这个int类型转换成asii 码,转换的过程中就没有办法使用索引,也就是说虽然有索引,但是没有办法使用索引,所以使用的type是 all(全表扫描)

mysql> create table t1(id int ,c1 varchar(10),c2 varchar(20),primary key (id),ke
y c1(c1));
Query OK, 0 rows affected (0.47 sec)

mysql> desc select * from t1 where c1=11\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: c1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

如果是反过来的话 :一个int列,select 的时候传入的是 'XXX' 的话是没有问题的,也就是说不会发生类型的隐式转换 ,会把 'XXX' 转换成0,查询的结果就是 where 列=0 的一个效果


写在最后:

其实我很抵触鸡汤,但这些日子,有些矫情,每当自己快要放弃的时候,就感觉整个世界都好像欠我的什么,这个时候 我都会跑去刷一遍赖大的经历,刷一遍大家奋斗的经历,每次感受完就像打了鸡血一样,原来,大家都在奋斗,其实你一点都不孤独,奋斗的路上有你有我有他,也许在前进的路上会很艰辛,会遭遇到无数的挫折,相信这些都是铺垫,也许美好才刚刚开始,加油。

小蜗牛 说:
Freedom is the source from which all meaning and all values spring .


文章版权归 原文作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权| 转载必须包含本声明,并以超链接形式注明原文作者和本文原始地址: https://www.tougetu.com/2018/03/mysql-index-1.html

还不快抢沙发

添加新评论

代码 Pastebin Gist 加粗 删除线 斜体 链接 签到