Loading

MySQL索引

MySQL索引

MySQL索引基本操作

# 1.什么是索引
- 官方定义：一种帮助mysql提高查询效率的数据结构
- 索引数据结构

- 索引的优点：
	1、大大的加快数据查询速度
- 索引的缺点：
	1、维护索引需要耗费数据库资源
	2、索引需要占用磁盘空间
	3、当对表的数据进行增删改的时候，因为要维护索引，速度会收到影响

# 2.索引分类
- 主键索引，唯一索引，复合索引，普通索引
- a.主键索引
	设定为主键后数据库会自动建立索引，innodb为聚簇索引
	不允许有空值
	
- b.单值索引 单列索引 普通索引
	即一个索引只包含单个列，一个表可以有多个单列索引

- c.唯一索引
	索引列的值必须唯一，但允许有空值
	唯一索引索引值可以存在null，但只能存在一个null

- d.复合索引
	即一个索引包含多个列

- e.Full Text 全文索引（MySQL5.7版本之前，只能用于MYISAM引擎）
	全文索引类型为FULLTEXT，再定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR,VARCHAR,TEXT类型列上创建。MySQL只有MYISAM存储引擎支持全文索引。

# 3.索引的基本操作
1. 主键索引 自动创建
- 主键索引是在建表时自动创建

- 建表
    create table t_user(
        id varchar(20) primary key,
        name VARCHAR(20)
    );

- 查看索引
	show index from t_user;

image-20210331222532424

2. 单例索引
- 建表时创建
    create table t_user1(
    id varchar(20) primary key,
    name varchar(20),
    key(name)
    );

- 建表后创建
	create index name_idex on t_user(name);
	
- 删除索引
	drop index 索引名 on 表名

image-20210401185858984

3. 唯一索引
- 建表时创建
	create table t_user2(
	id varchar(20) PRIMARY KEY,
	name varchar(20),
	unique(name)
	);
	
- 建表后创建
	create unique index name_index on t_user2(name);

image-20210401190529676

# 复合索引
- 建表时创建
	create table t_user3(
	id varchar(20) primary key,
	name varchar(20),
	age int,
	key(name,age)
	);

- 建表后创建
	create index name_age_index on t_user3(name,age);

# 经典面试题
- 现创建了如下复合索引（name,age,bir）
- 索引的使用匹配需要符合最左前缀原则，如：
- age，index：不能使用索引
- bir，age，name：不能使用索引
- name，age：能使用索引
- name：能使用索引
- age：不能使用索引
- 所谓的最左前缀原则就是匹配索引的时候，最左边的索引一定要存在

- 基于上述问题，mysql的引擎为了更好的利用索引，在查询过程中，会动态调整索引
- 如：age，name，bir本来是不能利用索引的，会被优化为name，age，bir这样就能使用索引了

image-20210401192620879

索引的底层原理

# 索引底层原理
- 搭建环境
- 建表
    create table t_emp(
        id varchar(20) PRIMARY KEY,
        name varchar(20),
        age int
    );
- 插入数据
    INSERT INTO t_emp VALUES(5,'e',23);
    INSERT INTO t_emp VALUES(7,'g',23);
    INSERT INTO t_emp VALUES(6,'f',23);
    INSERT INTO t_emp VALUES(2,'b',23);
    INSERT INTO t_emp VALUES(4,'d',23);
    INSERT INTO t_emp VALUES(3,'c',23);
    INSERT INTO t_emp VALUES(1,'a',23);

- 查询
	select * FROM t_emp;

image-20210401200932889

# 思考
- 为什么上面的数据明明没有按顺序插入，为什么查询时却是有顺序的呢？

- MySQL底层为主键自动创建索引，插入的时候会针对索引的字段进行排序
- 也就是MySQL底层正在存储是这样的
- 为什么要进行排序呢？
- 因为排序之后查询一条数据就相对比较快了。如：查询id=3的数据我们按照顺序查找只需按顺序找到3就行了，如果没有排序就是大海捞针了。

image-20210401202118509

# 为了进一步提高效率索引又进行了优化SQL
- 设想，如果按照链表这种方式存储，现存了1000条数据，如今要查找id为1000的数据，我们都知道链表的查找是很慢的，只能从头到尾依次遍历。所以需要对其进行优化。

- 就是基于页的形式进行管理索引
- 如：查询id=4时，直接先比较页，先去页目录中查找，然后再去数据目录中查找

image-20210401203159019

# 为什么这种索引称之为B+树数据结构呢，什么是B+树呢？
- 参考资料：https://www.cnblogs.com/lianzhilei/p/11250589.html

image-20210401203348309

img

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时会导致每个节点（即下一页）能存储的key的数量很少，当存储的数量很大时会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率，在B+Tree中，所有数据记录节点都是按照键值大小顺序放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息。
  2. 所有叶子节点之间都有一个链指针。
  3. 数据记录都存放在叶子节点中
  • InnoDB存储引擎页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），也就是说一个页（B+Tree中的一个节点）中大概存储16KB/（8B+8B）=1K个键值（因为是估算，为计算方便，这里的k取值为10^3）。也就是说一个深度为3的B+Tree索引，可以维护10^3*10^3*10^3=10亿条记录。
  • Inked20210401200555_LI
  • 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时将根节点常驻内存，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
- 3层，查1-3次
- 2次：基于主键查找，第一层常驻内存的，不调用磁盘IO
- 3次：通过普通索引查找，通过普通索引查找到主键，通过主键查找对应的字段

# 聚簇索引和非聚簇索引
- 聚簇索引：将数据存储与索引放到一块，索引结构的叶子节点保存了行数据
- 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

注意：在innodb中，在聚簇索引上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引，唯一索引。辅助索引节点存储的不再是行的物理地址，而是主键值，辅助索引方法数据总是需要二次查找。

image-20210404125507752

1、InnoDB中

  • InnoDB使用的是聚簇索引，将主键组织到一颗B+树种，而行数据就存储在叶子节点上，若使用where id = 14这样的条件查找主键，则按照B+树的检索算法，即可查找到对应的叶节点，之后获得行数据。
  • 若对Name列进行条件索引，则需要两个步骤：第一步在辅助B+树中检索Name，找到其叶子节点获取对应的主键，第二步使用主键在主索引B+树中再执行一次B+树索引操作，最终找到叶子节点即可获得整行数据。（重点在于通过其他键需要辅助索引）
  • 聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键（类似oracle中的RowID）来作为聚簇索引。如果已经设置了主键又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后回复主键即可。

2、MYISAM

  • MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助索引B+树存储了辅助键，表数据存储在独立的地方，这两棵B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

  • image-20210404132707465

# 使用聚簇索引的优势
- 问题：每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

- 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页会又多条数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成凡哥维纳，不必访问磁盘，这样主键和行数据是一起被载入内存的，找到叶子节点就可以将行数据返回了，如果按照主键Id来住址数据，获得数据更快，而非聚簇索引则每次都要进行磁盘I/O的操作，不能利用缓存器。

- 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址，好处是当行数据发生变化时（删，改，增），索引树的节点页需要分裂变化；或者我们需要查找的数据，在上一行IO读写的缓存中没有，需要发生一次新的IO操作，可以避免对辅助索引的维护工作，只需要维护索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了索引占用的存储空间大小。

# 聚簇索引需要注意什么？
- 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变化大，消耗更多的时间和资源。

- 建议使用int类型的主键，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

# 为什么主键通常采用自增id
- 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的放在磁盘上。如果主键不是自增的Id，那么可以想象，它会干些什么，不断的调整数据的物理地址，分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免，但如果是自增的，那就简单了，它只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。

# 什么情况下无法利用索引呢？
- 1.查询语句中使用Like关键字
		在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用，如果“%”不是在第一个位置，索引会被使用。
		
- 2.查询语句中使用多列索引
		多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。最左原则。
		
- 3.查询语句使用OR关键字
		查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR浅后有一个条件的列不是索引，那么查询中将不适用索引。
posted @ 2021-04-04 14:19  nuoxin  阅读(70)  评论(0)    收藏  举报