MySQL-索引的数据结构及优缺点

为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构。MySQL在进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则全表扫描，建索引目的就是为了减少磁盘I/O次数，加快查询效率。

索引及其优缺点

索引概述

索引本质：索引是数据结构。这些数据结构以某种方式指向数据，这样可以在这些数据结构的基础上实现高效查找算法。 索引是在存储引擎实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有类型的索引。同时存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。

优点

1、提高数据检索的效率，降低数据库的I/O成本 2、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性 3、可以加速表和表之间的连接。对于有依赖关系的子表和父表联合查询时，可以提高查询速度 4、在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗

缺点

1、创建索引和维护索引要耗费时间 2、索引需要占磁盘空间，存储在磁盘上 3、虽然索引大大提高了查询 速度，同时也会降低更新表的速度

InnoDB中的索引

常见索引概念

聚簇索引 并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点）。 特点 1、使用主键值的大小进行记录和页的排序 页内的记录是按照主键大小顺序排成一个单向链表 各个存放用户记录的页排序成一个双向链表 存放目录项的页也排成一个双向链表 2、B+树的叶子节点存储的是完整的用户记录 优点 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引获取数据比非聚簇索引更快 聚簇索引对于主键的排序查找和范围查找更快 因为聚簇索引排列顺序，查询显示一定范围数据时不用从多个数据块中提取数据，节省了大量I/O操作 缺点 插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式，否则会出现页分裂，严重影响性能 更新主键的代价很高，因为将会导致被更新的行移动 二级索引需要两次索引查找

非聚簇索引与聚簇索引的区别就是叶子节点放的是主键

索引的代价

空间上的代价 每建立一颗B+树，每一颗B+树的每一个节点都是一个数据页，一个页默认占用16kb的存储空间，一颗很大的B+树由许多数据页组成 时间上的代价 每次对表中数据进行增删改操作时，都需要去修改各个B+树索引

索引的声明与使用

索引的分类

从功能逻辑分为普通索引、唯一索引、主键索引、全文索引 从物理实现方式分聚簇索引和非聚簇索引 从作用字段个数分单列索引和联合索引

普通索引 在创建索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型

唯一索引 使用UNIQUE参数可以设置唯一性索引，在创建唯一性索引时限制该索引的值必须是唯一的，但允许有空值。一张数据表里可以有多个唯一索引

主键索引 主键索引就是一种特殊的唯一性索引，不能为空且最多只有一个主键索引

单列索引 在单个字段创建索引

多列索引 多个字段组合上创建一个索引，使用组合索引时需要遵循最左前缀集合

全文索引 利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法智能的筛选出我们想要的搜索结果。全文索引适合大型数据集，对于小的数据集，用处不大。 使用参数FULLTEXT设置全文索引

索引的创建

查看索引 show create table 表名； show index from 表名；

在创建表的定义语句中指定索引列，使用alter table语句或者create index

创建表时 隐式创建索引 添加约束的时候自动创建 显示创建索引 create table table_name( name varchar(30), [unique | fulltext | spatial] [index | key] (col_name [length]) [asc | desc]) alter table alter table 表名 add index 索引名(字段名) create index create index 索引名 on 表名(字段名)

索引的删除

alter table … drop index … drop index … on table_name

索引的设计原则

1.针对数据量较大,且查询比较频繁的表建立索引 2.针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引 3.尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高 4.如果是字符串类型的字段,字段的长度较长,可以针对字段的特点建立前缀索引 5.尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率 6.要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率 7.如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它.当优化器知道每列是否包含NULL值时,它可以更好的确定那个索引最有效的用于查询