1.索引的本质
如果发现一条执行很慢的sql,首先考虑查执行计划,看索引是否命中。
索引是帮助Mysql高效获取数据的排好序的数据结构。
索引的数据结构:
- 哈希表
- 对索引的key进行一次hash运算就可以定位出数据存储的位置
- 很多时候hash索引要比B+Tree索引更高效
- 仅能满足”=”,”IN”的数据查找,
不支持范围查询
- 二叉树(如果数据是递增的,会形成单支数,影响查询效率,因为索引的节点也是存储在磁盘上)
- B-Tree
- 红黑树(又叫二叉平衡树,在数据量大的时候,依然没有解决数高的问题)
- B+Tree:
- 非叶子节点不存储data,只存储索引(冗余)和索引在磁盘的地址,可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接,提高区间访问的性能
- 每一页的默认大小是16K,
- Mysql高版本的时候,非叶子节点保存在内存中,不需要去加载IO。只有加载叶子节点的时候,才会去加载IO
2.索引的存储
Mysql在磁盘中的存储文件：
如果是MYISAM存储引擎,在data目录下会有3个文件:
.frm:存储表结构
.myd:存储数据的文件,文件后缀以存储引擎+d(data)结尾
.myi:如果建索引,则存在该文件存储索引。文件后缀以存储引擎+i(index)结尾。
如果是Innodb引擎,由于数据和索引是通过B+Tree组织的,在data目录下会有2个文件:
.frm:存储表结构
.ibd:存储数据和索引
MYISAM和Innodb索引的查找
聚集索引包含了完整的数据记录。Innodb的主键索引就是聚集索引。
Innodb存储引擎,如果是主键索引树,行数据是存储在索引树的叶子节点的。在进行数据查找的时候,可以直接在内存中查找到数据,不需要通过IO到磁盘查找。如果是普通索引树,普通索引树的叶子节点存储的是主键的值,可能需要通过回表操作到主键索引树进行查找数据。
MYISAM存储引擎,数据和索引是存在不同的文件中,查找数据是先通过.myi查找到数据所在行的内存地址,再通过内存地址到.myd文件中查找数据。
聚集索引和非聚集索引
聚集索引包含了完整的数据记录。如Innodb的主键索引,叶子节点存储的是完整的行数据,它就是聚集索引。
而Innodb的普通索引,叶子节点存储的是主键的值,或者MYISAM引擎,数据和索引是存储在不同的文件,它就是非聚集索引。
区分聚集索引和非聚集索引主要就是看是否包含了完整的数据记录。
为什么建议Innodb表必须建主键,并且推荐使用整形的自增主键?
Innodb的存储引擎,是通过B+Tree来组织数据的。如果没有建主键,Innodb引擎会自动找一个唯一的列作为主键。如果找不到唯一的列,Innodb将会维护一个6字节的rowid作为主键。如果我们自己建主键,Innodb就不会进行这些操作,从而提高性能。
使用整形存储,比使用字符存储,更加节省存储空间。而且在查找数据进行比较的时候,整形的比较比字符串(uuid)的效率高,因为字符串还要转换成ASCII来比较。
使用自增主键,插入数据的时候,直接在最后添加数据,而不会产生页分裂的情况。
为什么非主键索引结构叶子节点存储的是主键值?
一致性和节省存储空间。
为什么B+Tree的非叶子节点不存数据?
索引树的每个节点存储的是页,而页是有固定大小的。如果在每个节点中存数据,那么一个页能存储的索引就少了,从而增加了树高,增加了查找数据的效率。
3.innodb后台进程
innodb存储引擎是多线程的模型,后台进程作用有：
- 负责刷新内存池中的数据,保证缓冲池中的数据是最近的数据
- 将已修改的数据文件刷新到磁盘中
后台进程可以分为以下4类:
- Master Thread : 负责将缓冲池中的数据
异步刷新到磁盘,保证数据的一致性。 - IO Thread : 负责写IO请求的回调。在Innodb1.2以后的版本中,有4个read thread,4个write thread,1个log thread,1个insert buffer thread.可以通过
show variables like '%innodb_%io_thread'来查看threads的数量。 - Purge Thread : 负责UNDO页的回收。在Innodb1.1版本之前,UNDO页的回收由Master Thread来完成。在Innodb1.2版本后,支持多个Purge Thread,可以在配置文件中设置
innodb_purge_threads=4来独立启动Purge Thread.通过show variables like '%innodb_purge_thread%'可以查看Purge Thread的数量。 - Page Cleaner Thread : 负责脏页数据的刷新。在Innodb1.1版本之前,脏页的回收由Master Thread来完成。在Innodb1.2版本后,脏页数据的刷新放到独立的线程中执行,从而减轻Master Thread的工作,提高Innodb引擎的性能。
