MySQL索引详解

1.索引概述

概念：存储引擎用于快速找到数据记录的数据结构，帮助mysql 高效获取数据的数据结构。

目的：减少磁盘IO的次数，加快查询速率。

演示：

无索引全表扫描，有索引比如建立b+tree能更加快速的查找。

优势：提高数据检索的效率，降低IO成本，通过索引对数据 进行排序降低排序的成本

劣势：更新数据比较慢，索引需要占据额外的空间。

2.索引的结构

数据查找的方式（没有索引）

1.在一个数据页的查找（数据页是数据库的基本单位，默认大小16kb，类似书架的概念）

•             以主键为搜索条件（主键为递增的），可以使用二分，迅速定位
•             以其他列为搜索条件，不能保证有序，从最小记录开始依次遍历单链表

2.在很多数据页查找数据

• 定位数据所在的页
• 从所在页中查找相应记录

知识小贴士：

       磁盘IO是非常耗费时间的，查找数据首先将数据从磁盘中读入内存（整个数据页），再做相           应    的查找操  作。

 索引的设计方案

   1.使用行格式来存储记录，Compact行格式示意图

 2.将记录放在页中，会根据主键id排序，示意图

 

3.多个页之间，再页上建立目录项（有最小主键值key和页号page_no），示意图

 

4.建立目录页

 

  B+Tree                            大部分引擎都支持该索引

  Hash索引                         底层哈希表实现，只有精确匹配查询索引列才有效，不支持范围查询

  R-Tree（空间索引）        主要用于地理空间类型，MYISAM引擎

  Full-text（全文索引）      建立倒排索引快速匹配文档

   二叉树结构缺点：可能出现不平衡情况，大量数据情况下，层级深

   红黑树缺点：大量数据情况下，层级深检索速度慢

B-Tree(多路平衡查找树)

B+Tree  

  非叶子节点起到索引作用，叶子节点存放数据

  叶子以单链表的形式

  MySql索引数据结构对B+Tree进行了优化，在原有基础上增加了一个指向相邻节点的链表指针，    提高区间访问的性能。

Hash索引

  采用hash算法，将key转换成hash值，映射到对应的哈希表中，传哈希冲突，使用链表解决。

  特点：支持=,in,不支持范围查询，无法利用hash索引排序，在不产生hash冲突的情况下效率高

  支持的存储引擎：Memory引擎，InnoDB拥有自适应的hash功能。

索引分类

1.分类

•   主键索引：默认创建，只能有一个
•   唯一索引，常规索引，全文索引：可以有多个

2.  根据索引的存储形式可以分类，在InnoDB中

•     聚集（聚簇）索引（clustered index），只有一个，必须有，将数据存储与索引放在一起，  索引的叶子节 点存放行数据
•     二级索引（Secondary index），可以有多个，分开存储，索引的叶子节点存储的是对应的主键

3.   聚簇索引简单了解

优点

缺点

限制

聚集索引选取的规则：

       如果存在主键索引，主键索引就是聚集索引。

       不存在主键索引，第一个唯一索引为聚集索引

        没有主键，唯一索引，InnoDB会自动生成rowid作为隐藏的聚集索引。

  回表查询：现根据二级索引找到主键值，再根据主键值到聚集索引拿到数据。

问题;

select * from user where id=1;
select * from user where name="aa";

  那个效率高？

  首先会根据name 的值取二级索引查到对应id,再根据id取聚集索引查找对应行数据。

  所以id效率高

问题：

  InnoDB的B+Tree的高度？

索引语法

  创建索引

create index index_name  on table_name (index_col_name,...);

  查看索引

show index from table_name;

  删除索引

drop index  index_name from table_name

  案例：

    现有一张试卷信息表examination_info，其中包含各种类型试卷的信息。为了对表更方便快捷地      查  询，需要在examination_info表创建以下索引，规则如下：

    在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在        tag列创建全文索引full_idx_tag。

create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(tag);

索引性能分析

  命令，查看当前数据库，insert,update,select,delete的访问频次

show [session|global] status like 'Com_______'

  慢查询日志