MYSQL的索引及底层原理详解
MYSQL中的索引及底层原理详解
索引介绍
1.有关概念：
定义：创建在表上，对数据库表中的⼀列或者多列的值进⾏排列得到的结果（相当于表中数据的向导）。
⼯作⽅式：⼀个表创建的索引以⽂件的形式存储下来，要⽤该表的数据就先把索引从磁盘上加载到内存（磁盘IO），从内存中先读索引，再根据索引对应的数据。
作⽤：提⾼查询效率（优势）
劣势：
索引也要存储，过多的索引会占⽤空间
索引并⾮越多越好，过多的索引会导致cpu使⽤率降低（过多的索引会导致cpu处理索引的时间过多，处理数据的时间相对少）
由于数据改动会影响索引的改动，过多的索引会引起磁盘IO频繁⽽造成cpu负载过重
2.索引的分类
普通索引：没有任何限制条件，可以给任意类型的字段添加普通索引
唯⼀性索引：使⽤unique修饰的字段，值是不能重复的，主键索引就⾪属于唯⼀性索引
主键索引：使⽤primary key修饰的字段MYSQL会⾃动创建为其创建索引，InnoDB存储引擎中不设置主键也会⾃动⼀个字段创建主键索引，⼀个表只能有⼀个主键索引
单列索引：在⼀个字段上创建的索引
多列索引：在表的多个字段上创建的索引
全⽂索引：使⽤fulltext参数设置全⽂索引，只⽀持char、varchar、text类型的字段上，常⽤于数据量⽐较⼤的字符串类型中，可以提⾼查询速度，只有myisam存储引擎⽀持空间索引：空间型数据的索引，使⽤spatial修饰
索引创建和删除的SQL语句：
创建：
1.创建表的时候创建索引
CREATE TABLE table_name(
属性数据类型,
[unique|fulltext|spatial|primary] [index|key] [索引名] (属性(属性长度) [asc|desc])
);
注意：primary后⾯必须是key⽽不能是index
索引名⼀般以 idx_属性名这样的形式命名
指定属性长度指建⽴前缀索引（例如：name属性值’abcde‘，长度3，即以'abc'为索引）
[asc|desc]：指定数据按索引升序还是降序排列
例：创建⼀个student表，表中有id、name、sex三个属性，id为索引，索引名为idx_id
create table student(
id int,
name varchar(20),
sex varchar(10),
index idx_id (id)
);
创建后⽤show create table 表名; 来看创建表的详细语句:
-----------------------------------------------+
student | CREATE TABLE student (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) DEFAULT NULL,
KEY idx_sex (sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±---------------------------------
第四⾏可以看出id是⼀个普通索引，索引名为idx_id
2.在已经创建的表上添加索引
2.1 ：create [unique|fulltext|spatial|primary] [index|key] [索引名] on 表名 (属性);
例：在student表中创建⼀个名为idx_name的以name为唯⼀性索引的索引
create unique index idx_name on student (name);
2.2：alter table 表名 add [unique|fulltext|spatial|primary] [index|key] [索引名] (属性);
例：在student表中创建⼀个名为idx_id的以id为主键索引的索引
alter table student add primary key idx_id (id);
-----------------------------------------------+
| student | CREATE TABLE student (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) NOT NULL,
PRIMARY KEY (id), UNIQUE KEYidx_name(name), KEYidx_sex(sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±---------------------------------
删除：
drop index 索引名 on 表名;
注意：⼀个表的主键索引不能这样删除，这样删除会报错
需要⽤alter table 表名 drop primary key；来删除
例：删除上⾯创建的idx_name，idx_id，idx_sex
drop index idx_name on student;
drop index ide_sex on student;
alter table student drop primary key;
-------------------------------------------------------------------+
| student | CREATE TABLE student (
id int(11) NOT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) DEFAULT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±------------------------------------------
分析有⽆索引SQL语句的执⾏过程：
⾸先student表中设置id为主键索引，并将其创建唯⼀性索引idx_id，为name创建唯⼀性索引idx_name
-----------------------------------------------------+
| student | CREATE TABLE student (
id int(11) NOT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY idx_id (id),
UNIQUE KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±-----------------------------
再插⼊⼀些记录
insert into student values(1,‘zhaolei’,‘nan’),(2,‘qiandian’,‘nan’),(3,‘sunfen’,‘nan’),(4,‘wulan’,‘nv’);
mysql> select * from student;
±—±---------±-----+
| id | name | sex |
±—±---------±-----+
| 1 | zhaolei | nan |
| 2 | qiandian | nan |
| 3 | sunfen | nan |
| 4 | wulan | nv |
±—±---------±-----+
再根据explain +SQL来分析SQL的执⾏计划:
1.为了看出效果，我们先把idx_name 删除掉：drop index idx_name on student;
再根据explain select * from student where name=‘zhaolei’\G 来查看select * from student where name=‘zhaolei’\G的执⾏计划
mysql> explain select * from student where name=‘zhaolei’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 25.00
Extra: Using where
±--------±---------------------------------
查看mysql索引
possible_keys和key表⽰本次查询没有⽤到索引，rows表⽰查询了4条数据，意味着进⾏了全表扫描，当数据量很⼤时，全表扫描效率很低。
2.给name字段添加索引：create index idx_name on student (name);
再根据explain select * from student where name=‘zhaolei’\G 来查看select * from student where name=‘zhaolei’\G的执⾏计划
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
partitions: NULL
type: ref
possible_keys: idx_name
key: idx_name
key_len: 63
ref: const
rows: 1
filtered: 100.00
Extra: NULL
±--------±---------------------------------
可以看到，此次查询命中了idx_name的索引，⽽且此次查询’zhaolei’只查询了⼀条数据,⾼效很多
索引的底层原理
MySQL⽀持两种索引，B-树索引和哈希索引
MySQL中innoDB存储引擎是基于B-树的存储结构（实际上是B+树）
先来看看这⼏种树
B-树：
B-树是⼀种多路搜索树，是⼀种m阶树（⼀个B-树的节点有多少个⼦节点，就可以把这个树称为⼏阶树，⼆叉树也可以叫做⼆阶树），图为三阶树。
B-树每个节点可存储多个数据（⼆叉树⼀个节点存储⼀个数据），存储同样的数据量B-树的⾼度（层数）是很低的
B-树⽆论是叶⼦节点还是⾮叶⼦节点都存数据和数据地址
B-树对数据查询的耗时情况不均匀，有可能在根节点就到数据，有可能在⼦节点或叶⼦节点才到数据（每个数据在树中只出现⼀次，要么在叶⼦节点，要么在⾮叶⼦节点（⼦节点和根节点））
B-树⼀个节点的⼤⼩⼀般和磁盘⼀个块（block块操作）的⼤⼩⼀致，这样在节点的读取或存储过程中，磁盘IO的次数最少（如果⼀个节点⼤⼩设为两个块，⼀个节点的存储就需要进⾏两次block块操作，如果⼀个节点⼩于⼀个块，⼜会造成⼀次块操作处理的数据少实际块利⽤率低的问题）
B+树：
B+树是B-树的变体，⾮叶⼦节点只存关键字，叶⼦节点存放关键字和数据（数据/数据地址），⽽B-树⽆论是叶⼦节点还是⾮叶⼦节点都存数据和数据地址
B+树有⼀条有序链表串联整个叶⼦节点
B * 树：
B*树是B+树的变体，区别在于它把⾮叶⼦节点的数据也⽤链表串起来
关于这⼏个树不再详细介绍，我是参考B树、B-树、B+树、B*树之间的关系理解的，个⼈感觉这位⽼兄总结的不错
MYSQL为什么采⽤B+树⽽不⽤B-树？
B-树的每个节点（叶⼦节点和⾮叶⼦节点）存储的是都关键字和对应的数据地址
B+树的⾮叶⼦节点存储关键字，不存储数据地址。对于相同⼤⼩的⾮叶⼦节点，B+树存储的关键字多，对于相同数量的数据的关键字，B+树使⽤更少的节点存，这样树的⾼度（层数）就更少，使⽤磁盘IO的次数也少，查询效率⾼
B+树的叶⼦节点存关键字和数据地址
B-树每⼀个节点都存储了关键字和数据地址，离根节点近的数据查询快，离根节点远的数据查询慢，耗时不均匀
⽽B+树所有的数据都在叶⼦节点上，因此在B+树上搜索关键字，耗时相对是均匀的，没有快慢之分
区间查B+树是很快的，因为B+树的叶⼦节点被连接成⼀个有序的链表结构，因此做整表查询和区间查询更快（先从根节点向下遍历树，到⼀个数据后，直接遍历链表得到其他数据，不⽤遍历整个树）
哈希索引：
底层实现是哈希表，哈希表不能保证数据有序，不适合进⾏区间查（因为哈希表的每个数据根据计算的散列码来存储，数据与数据之间⽆规律可循，⽆法保证有序，区间查也相当于查询整个表）
MYISAM和INNODB存储引擎的索引结构
MYISAM：
主键索引：
辅助（⾮主键）索引：
都是基于B+树的存储结构
索引和数据本⾝分开（⾮聚集索引）
主键索引和辅助索引的叶⼦节点都只存关键字和数据地址，区别在于主键索引的关键字是唯⼀不能重复
的
INNODB：
主键索引：
辅助（⾮主键）索引：
都是基于B+树的存储结构
索引和数据不分开（聚簇型索引）
主键索引：叶⼦节点中存关键字和相应的数据
辅助索引：叶⼦节点中存关键字和主键
对⽐两⼤存储引擎索引的查询过程
左边为INNODB，右边为MYISAM
可以看出：
INNODB引擎中根据主键查询时，直接到要查的关键字所在的叶⼦节点就可以直接从叶⼦节点种拿到所有数据；根据辅助索引查询时，先到要查的关键字所在的节点，节点中只能到辅助索引的数据和主键的数据，要查其他数据得根据该节点中的主键去主键索引中查
MYISAM引擎中⽆论是根据主键索引查还是根据辅助索引查，都会到要查的关键字的叶⼦节点，然后根据叶⼦节点中的数据地址去查所需数据
INNODB引擎中，每个表存储在硬盘包括两个⽂件：除了表结构⽂件，还需要⼀个 .ibd⽂件来存储数据和索引
MYISAM引擎中，每个表存储在硬盘包括三个⽂件：除了表结构⽂件，还需要⼀个 .myi⽂件来存储索引,⼀个 .myd⽂件来存储数据
系统(Linux或Windows)中查看数据库中存储的有关表的⽂件
命令⾏⽅式：
Linux中在/usr/local⽬录下MySQL安装的⽬录
Windows中在某个盘⾥到你安装MySQL的地⽅
然后 cd 上⾯到的⽬录
cd data
cd 库名
ls（linux） dir（Windows）
可以看到表名.frm（表结构⽂件）表名.ibd（索引+数据⽂件）--->该表的存储引擎是INNODB
可以看到表名.frm（表结构⽂件）表名.myi（索引⽂件）表名.myd（数据⽂件）--->该表的存储引擎是MYISAM
我的结果：只看了以INNODB引擎存储的表
2. ⿏标点击到MySQL存储的⽬录，再点击data，再点击数据库名
索引的设计原则：
可以看出，使⽤索引能提⾼查询效率，但是给表创建过多的索引，效率反⽽会降低，因此在设计表索引的时候，
需要遵循以下的设计原则：
1、给区分度⾼的字段创建索引 eg：学号、⾝份证号
2、给经常需要排序，分组和多表联合操作的字段创建索引
3、经常作为查询条件的字段创建索引
4、索引的数据不宜过多
5、使⽤数据量少的索引（如前缀索引，主要针对字符串索引，字符串类型的数据尽量创建前缀索引）
6、对于多列索引，优先指定最左边的列集
7、删除不再使⽤或者很少使⽤的索引
————————————————
区别：
1. InnoDB⽀持事务，MyISAM不⽀持，对于InnoDB每⼀条SQL语⾔都默认封装成事务，⾃动提交，这样会影响速度，所以最好把多条SQL语⾔放在begin和commit之间，组成⼀个事务；
2. InnoDB⽀持外键，⽽MyISAM不⽀持。对⼀个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，使⽤B+Tree作为索引结构，数据⽂件是和（主键）索引绑在⼀起的（表数据⽂件本⾝就是按B+Tree组织的⼀个索引结构），必须要有主键，通过主键索引效率很⾼。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过⼤，因为主键太⼤，其他索引也都会很⼤。
MyISAM是⾮聚集索引，也是使⽤B+Tree作为索引结构，索引和数据⽂件是分离的，索引保存的是数据⽂件的指针。主键索引和辅助索引是独⽴的。
也就是说：InnoDB的B+树主键索引的叶⼦节点就是数据⽂件，辅助索引的叶⼦节点是主键的值；⽽MyISAM的B+树主键索引和辅助索引的叶⼦节点都是数据⽂件的地址指针。
4. InnoDB不保存表的具体⾏数，执⾏select count(*) from table时需要全表扫描。⽽MyISAM⽤⼀个变量保存了整个表的⾏数，执⾏上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
那么为什么InnoDB没有了这个变量呢？
因为InnoDB的事务特性，在同⼀时刻表中的⾏数对于不同的事务⽽⾔是不⼀样的，因此count统计会计算对于当前事务⽽⾔可以统计到的⾏数，⽽不是将总⾏数储存起来⽅便快速查询。InnoDB会尝试遍历⼀个尽可能⼩的索引除⾮优化器提⽰使⽤别的索引。如果⼆级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会⽐较费时。可以建⽴⼀个记录总⾏数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上⾯提到的问题⼀样，如果此时存在多个事务的话这种⽅案也不太好⽤。如果得到⼤致的⾏数值已经⾜够满⾜需求可以尝试SHOW TABLE STATUS
5. Innodb不⽀持全⽂索引，⽽MyISAM⽀持全⽂索引，在涉及全⽂索引领域的查询效率上MyISAM速度更快⾼；PS：5.7以后的InnoDB⽀持全⽂索引了
6. MyISAM表格可以被压缩后进⾏查询操作
7. InnoDB⽀持表、⾏(默认)级锁，⽽MyISAM⽀持表级锁
InnoDB的⾏锁是实现在索引上的，⽽不是锁在物理⾏记录上。潜台词是，如果访问没有命中索引，也⽆法使⽤⾏锁，将要退化为表锁。
例如：
t_user(uid, uname, age, sex) innodb;
uid PK
⽆其他索引
update t_user set age=10 where uid=1;            命中索引，⾏锁。
update t_user set age=10 where uid != 1;          未命中索引，表锁。
update t_user set age=10 where name='chackca';    ⽆索引，表锁。
8、InnoDB表必须有唯⼀索引（如主键）（⽤户没有指定的话会⾃⼰/⽣产⼀个隐藏列Row_id来充当默认主键），⽽Myisam可以没有
9、Innodb存储⽂件有frm、ibd，⽽Myisam是frm、MYD、MYI
Innodb：frm是表定义⽂件，ibd是数据⽂件
Myisam：frm是表定义⽂件，myd是数据⽂件，myi是索引⽂件
如何选择：
1. 是否要⽀持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝⼤多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使⽤InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
4. MySQL
5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有⽬共睹的，如果你不知道⽤什么，那就⽤InnoDB，⾄少不会差。
InnoDB为什么推荐使⽤⾃增ID作为主键？
答：⾃增ID可以保证每次插⼊时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对⽐使⽤UUID）。如果使⽤字符串主键和随机主键，会使得数据随机插⼊，效率⽐较差。
innodb引擎的4⼤特性
插⼊缓冲（insert buffer),⼆次写(double write),⾃适应哈希索引(ahi)，预读(read ahead)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。