MySQL存储引擎和索引

_小菜鸟
114 阅读11分钟

MySQL存储引擎

MySQL是插件式的存储引擎，也就是它的存储引擎是可更换的，支持很多不同类型的的存储引擎。

我们create创建的那些表一般有三个东西：表的结构、数据、索引。那么它怎么存储以及存储方式是什么？

存储引擎直接影响上面的存储方式，简单来说就是不同的存储引擎数据的存储方式是不一样的，因为不管是表的结构还是索引，都是和表相关的数据。

image.png

show engines;//可以看到当前我们安装的MySQL版本所支持的存储引擎，以及支持事务

image.png MyISAM和InnoDB是比较常见的存储引擎，MySQL5.7之前默认用的是MyISAM，之后默认用的是InnoDB存储引擎，非常强大。

和表相关的存储引擎、默认字符集在配置文件里配置MySQL Server服务启动的时候都要去加载它的配置文件，Window下是my.ini，Linux下是my.cnf。

image.png MyISAM存储引擎数据和索引是单独存放的，InnoDB存储引擎数据和索引是一块存放的。

面试如果介绍存储引擎就着重介绍MyISAM和InnoDB就可以了，可以从锁机制、支持的索引类型、外键、事务来说。

为什么InnoDB存储引擎下建一个表即使没有加主键它也会自动生成一个主键，而MyISAM加主键才会产生主键，没有加主键就不会生成主键？

因为对于MyISAM来说，数据和索引是单独存放的，所以你加主键了会有主键索引存储到索引文件里面，没有加主键索引文件就不会存。而InnoDB数据和索引是存放在一块的，没有索引数据就没地方放，因为数据就是在索引树上放着的。

各存储引擎的区别： image.png

image.png 我们经常写代码要考虑并发的线程安全问题，所以当我们代码上开多个线程或者多个进程，同时向MySQL同一张表进行增删改查的时候，MySQL Server也要做一些并发的控制，所以要加锁，比如CAS（无锁机制，但是它实现了锁的功能、互斥锁、自旋锁、读写锁。

MySQL索引

当表中的数据量达到几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。

由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘I/O操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此掌握良好的索引创建原则非常重要！

image.png

1. 索引分类

索引是创建在表上的，是对数据库表中的一列或者多列值进行排序的一种结果。索引的核心是提高查询速度！

索引从物理上可以分为聚集索引和非聚集索引。

索引从逻辑上划分：

image.png

2. 索引的创建和删除

创建表的时候指定索引字段

create table index1(id INT,
    name VARCHAR(20),
    sex ENUM('male','female'),
    INDEX(id),//普通索引
    INDEX(id,name),//多列索引
    INDEX 'index_sex'(sex),//可以起个索引名index_sex，如果没有起名会默认生成索引名，想知道叫什么名字可以show create table 表名
);

在已经创建的表上添加索引

create [unique] index 索引名 on 表名(属性名(length) [ASC|DESC]);

删除索引

drop index 索引名 on 表名;

3. 索引的执行过程

image.png

image.png

image.png

给name添加索引

image.png 自己添加索引，到时候以这个字段作为过滤条件不一定能使用到索引，因为MySQL Server还有个优化。他如果发现使用索引得到的数据基本上也是所有数据的百分之七八十左右的话，是不会使用索引的，他觉得如果你通过索引找出来的数据量基本上和整表扫描的数据量差不多的话，还不如直接做整表搜索，因为它要读索引的话，首先要读索引文件花费磁盘I/O，还要扫描索引数，数据取不完的话最后还是得到整个数据表上取数据，所以还不如直接去整表取数据。

image.png 对于字符串列加索引的时候，一般会选择一个索引的长度key_len，只要前key_len个字符能区分每一列的数据就可以了。因为如果用整个的字符串全部作为索引的话，长度比较长，最终会导致索引文件比较长，到时候使用索引的时候先要从索引文件花费磁盘I/O把索引文件里面的索引数据读到内存上，所以索引文件大花费磁盘I/O次数比较多。

image.png

存储引擎的索引和数据都是有缓存的，同样的操作第一次花费的时间明显长而第二次明显短。因为同样的查询中间没有增加删除更新的话，第一次查询的结果会放在数据缓存或者索引缓存里面，就是内存上，第二次查询就不用花费磁盘I/O重新从磁盘上去读相应的数据或者索引了，效率比较快。但是效率再快还是表较长的。

image.png 为什么给passwd创建索引了但是没有使用到，passwd是varchar类型，而我们写的默认是整型类型，所以对于MySQL Server来说在这里做这个过滤的时候，涉及了类型转换。如果sql过滤条件涉及类型转换，就不能用到索引了，另外过滤条件的值用到了MySQL函数了也用不到索引了。 手动类型强转：

image.png

image.png 索引的优化：

  • 经常用来做where过滤条件的字段要考虑加索引
  • 字符串列创建索引的时候，尽量规定索引长度key_len，而不能让索引值的长度过长，只要前key_len个字符能区分每一列的数据就可以了
  • 索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了。

面试中如果问到索引，从实践角度去说，我记得做项目的时候建立过这样一些表，做过这样一些操作，也遇到相应的一些性能问题，总结了我在开发过程中需要去优化的索引的一些问题点，我主要说下这块。。。

4. B树索引

涉及磁盘的读取、磁盘的数据往内存上加载、涉及磁盘到内存的数据结构的构建，一般都会采用B树结构。

image.png

image.png 数据和索引都是放在磁盘上的，当MySQL Server在家进行一些相应的查询时，是不可能直接从磁盘上读取数据，要请求磁盘上的数据肯定得通过操作系统把磁盘上的数据加载到内存中，然后MySQL Server才能访问相应的数据。也就是说我们运行的进程要访问相应的数据或者索引需要花费磁盘I/O，先把数据或者索引读到内存中，磁盘I/O当然是少了好，因为磁盘I/O和内存I/O相比，效率低了很多。

我们做一个知识的交叉对比，在学C和C++的时候，用malloc或者new向操作系统分配内存的时候，假如说我们分配了4B，并不是只从操作系统拿了4B，这样效率太低了。内存管理（向操作系统申请物理内存）是按页面（4k或者8k）为单位，也就是说我们通过malloc/new申请4B时，实际上我们向操作系统(也就是内核kernel)请求分配内存，内核是按页面分配的，按页面分配后，比如说反悔了2*4k两个页面，应用程序只需要4B，那么剩下的8k-4B就被C库（就是libc.so）或者C++库（libc++.so）的malloc实现ptmalloc或者tcmalloc来管理，这样做的好处就是下一次用程序再去通过malloc/new申请内存的时候，不用再陷入内核空间申请内存，直接在用户空间，从C/C++库里在上一次剩余的空间里分配应用程序需要的字节数，等把C/C++库里管理的内存用光了，下一次再申请就会继续向kernel里申请，以页面为单位返回给应用程序，然后把剩余的再管理起来。

同样地，当我们花费磁盘I/O往内存里读数据的时候，也是按块来读取的，不是说在磁盘上读取4B就真的只取4B拿过来，好不容易通过中断进行空间切换从用户空间切换到内核空间，如果只分配4B就结束了，下次读取再继续的话，这样不断地从用户空间到内核空间切换，效率非常低，所以磁盘I/O读数据到内存上，是请求操作系统帮我们完成，按块（一般是16k，也是内存页面的整数倍）读取。

假设有2000w数据或者索引，要进行读取，如果说它是个平衡的二叉树（AVL），树的层高有log2000w，即构建下来大概25层，在最坏的情况下，即这些节点都不在一个磁盘块上，分别在不同的磁盘块那么我们要搜索一个数据的话，读取一个索引要花费25次磁盘I/O，这是挺多的。

如果说在这里用B树来构建这个数据结构，来存储我们从磁盘上读取出来的索引内容的话，假如说m=500，这个平衡树最多需要3层。

m一般取多少合适呢？最好的是一次磁盘I/O读取的磁盘块内容，刚好存储在B树的一个节点中。一般B+树不会超过三层。

为什么一个快速搜索基本上我们都会见到B/B+树结构？因为这种结构花费的磁盘I/O次数最少，因为从磁盘中读数据最害怕花费磁盘I/O，因为这个效率慢。

image.png

当我们相应的做一些查询操作时，比如select * from student where uid = 5;用到了uid的索引，他就会去读取索引的内容，先请求存储引擎，再请求内核kernel，然后花费磁盘I/O，从磁盘读索引文件读取到内存中，最后用索引的数据构建B树加速搜索。

image.png

这个得看用的是什么存储引擎，要是用的MyISAM，data存储的是磁盘上的地址，通过找到索引值然后在磁盘上找到包含索引值的记录，然后通过地址在磁盘上数据文件里读取相应的数据。如果是InnoDB，数据和索引在一块存放，索引树上放的就是数据，存储的是数据本身的内容。

image.png 如果说m是一颗500的B树，一个节点有500个指针域、499个数据域。我们想去找28的时候，是通过二分搜索来的，时间复杂度O(logn)，而AVL树花费的时间也是这么多，但用B树比AVL树好在非常少的磁盘I/O。

所以说select * from student where uid = 5;为什么就快呢？如果uid没有索引，那就会整表搜索。

select * from student where name='linfeng';如果name没有索引的话，在MyISAM存储引擎下，相当于就是把整个表的数据文件查一遍，效率很低，如果后面加limit 1可以优化一点，就是搜到第一个name='linfeng'这一行就停止了，如果给name建一个索引，当再去执行这个select语句，MySQL Server分析器就知道name有索引，就会加载name的索引，把相应的索引数据加载到内存构建B树。

5. B+树索引

为什么MySQL(MyISAM和InnoDB)索引底层选择B+树而不是B树？ image.png 由于上面这三个原因，选择B+树来构建索引。

image.png

image.png

avatar
文章
阅读
粉丝