Mysql索引

最近一周的工作都集中在慢sql的治理上，大部分都是基于索引进行优化，所以做了下述的总结。 1. explain 介绍explain（执行打算），应用 explain 关键字能够模仿优化器执行sql查问语句，从而晓得 MySQL 是如何解决sql语句。explain 次要用于剖析查问语句或表构造的性能瓶颈。 通过 explain + sql 语句能够晓得如下内容： 表的读取程序。（对应id）数据读取操作的操作类型。（对应select_type）哪些索引能够应用。（对应possible_keys）哪些索引被理论应用。（对应key）表间接的援用。（对应ref）每张表有多少行被优化器查问。（对应rows）explain 执行打算蕴含字段信息如下：别离是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra 12个字段。每个字段对应的介绍如下。能够先建几张表举例。上面建表各自举例子： xCREATE TABLE `blog` ( `blog_id` int NOT NULL AUTO_INCREMENT COMMENT '惟一博文id--主键', `blog_title` varchar(255) NOT NULL COMMENT '博文题目', `blog_body` text NOT NULL COMMENT '博文内容', `blog_time` datetime NOT NULL COMMENT '博文公布工夫', `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `blog_state` int NOT NULL COMMENT '博文状态--0 删除 1失常', `user_id` int NOT NULL COMMENT '用户id', PRIMARY KEY (`blog_id`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE TABLE `user` ( `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户惟一id--主键', `user_name` varchar(30) NOT NULL COMMENT '用户名--不能反复', `user_password` varchar(255) NOT NULL COMMENT '用户明码', PRIMARY KEY (`user_id`), KEY `name` (`user_name`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE TABLE `discuss` ( `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论惟一id', `discuss_body` varchar(255) NOT NULL COMMENT '评论内容', `discuss_time` datetime NOT NULL COMMENT '评论工夫', `user_id` int NOT NULL COMMENT '用户id', `blog_id` int NOT NULL COMMENT '博文id', PRIMARY KEY (`discuss_id`)) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf81. id示意查问中执行select子句或者操作表的程序，id的值越大，代表优先级越高，越先执行。针对上面sql例子： ...

并不是索引越多越好，索引是一种以空间换取工夫的形式，所以建设索引是要耗费肯定的空间，况且在索引的保护上也会耗费资源。本文首发我的集体博客mysql索引不失效 这里有张用户浏览商品表，建表语句： CREATE TABLE `product_view` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `server_id` int(11) NOT NULL, `duration` int(11) NOT NULL, `times` varchar(11) NOT NULL, `time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `time` (`time`), KEY `user_product` (`user_id`,`product_id`) USING BTREE, KEY `times` (`times`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;能够看出目前这张表是有3个索引的： 我往这张表外面导入了10万多条记录。 mysql不走索引的状况1、like查问以“%”结尾(如果结尾、后果都有“%”，也不会应用索引，走的是全表扫描)； 我这里用了列出了4种状况，发现都是全表扫描，不走索引的。可能因为times取值不够离散，索引没有走索引。 2、or语句前后没有同时应用索引; 首先看看or语句前后同时应用索引： 查问走索引了。 再看看or语句前后没有同时应用索引，product_id是索引字段，server_id不是索引字段： 是没有走索引的。 3、组合索引中不是应用第一列索引；（不合乎最左匹配准则） 这张表索引为工夫time和一个组合索引： ...

最近收到一个 Sentry 报警，如下 SQL 查问超时了。 select * from order_info where uid = 5837661 order by id asc limit 1执行 show create table order_info 发现这个表其实是有加索引的： CREATE TABLE `order_info` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned, `order_status` tinyint(3) DEFAULT NULL, ... 省略其它字段和索引 PRIMARY KEY (`id`), KEY `idx_uid_stat` (`uid`,`order_status`),) ENGINE=InnoDB DEFAULT CHARSET=utf8实践上执行上述 SQL 会命中 idx_uid_stat 这个索引，但理论执行 explain 查看 explain select * from order_info where uid = 5837661 order by id asc limit 1能够看到它的 possible_keys（此 SQL 可能波及到的索引） 是 idx_uid_stat，但实际上（key）用的却是全表扫描。 ...

引言 内容为慕课网的《高并发 高性能 高可用 MySQL 实战》视频的学习笔记内容和集体整顿扩大之后的笔记，这一节的内容是对于InnoDb的存储构造进阶理解，同时介绍为什么会应用B+索引作为最终数据结构，然而实际上InnoDb在具体实现中也并没有齐全遵循B+的格局，而是在外部做了很多“手脚”，这也是所谓实践和实际之间的差别。 如果内容比拟难，能够追随《Mysql是怎么样运行》集体读书笔记专栏补补课，集体也在学习和同步更新中。 地址如下：https://juejin.cn/column/7024...。 索引组织表 InnoDb 的所有表都是索引组织表，索引组织表有如下的定义： 不是一种“组织表”，而是由“索引”组织的表，索引即数据数据即索引，InnoDb中表默认都会主键程序寄存，同时依照肯定的规定排序，默认的索引组织模式被称为聚簇索引。 什么是索引？ 索引能够简略了解为目录，相似于咱们书中的目录页，帮咱们疾速定位具体的内容，对于数据库某一列或者多列进行预排序的数据结构，留神这是一种数据结构目标是为了放慢数组的搜寻速度。 然而索引也有问题，那就是目录自身也须要占用存储空间并且随着数据的收缩而收缩，同时如果索引应用的不失当也会呈现问题，比方如果咱们的目录索引的内容全都是截然不同的会呈现“索引生效”问题，此时索引成果大打折扣，不如间接搜寻数据。 主键定义和主键索引 在Mysql的Inndb存储引擎中，应用的主键索引也被称为聚簇索引： InnoDb 的存储引擎表中每张表必须有一个主键，表中有一个非空惟一索引即为主键。如果存在多个非空惟一索引并且没有定义主键，抉择第一个定义的索引，若所有条件不满足则InnoDb在数据行中主动创立一个6个字节的指针暗藏列作为主键，并且这个主键外部是自增的使得记录能够依照程序进行存储。 上面用视频中的案例举例探讨的上面这个表主键是什么？ 从下面的截图能够看到，字段a没有定义惟一索引，尽管它是非空的然而并不是惟一的所以不是主键，b尽管是最先定义的，然而他不是非空所以也不能作为索引，而d和 c尽管都是惟一索引并且都是非空列，依据多个非空索引取第一个定义索引为主键的规定，最终主键为字段d。 B+树索引 B+树的索引构造是InnoDb的根底构造，上面是传统的B+树的构造： Btree 应用B+树作为索引的数据结构。B+树的高度为2-4层，查找数据非常快。B+树索引将非叶子节点所谓索引节点，叶子节点为数据节点，数据节点之间用链表串联实现优化范畴查问。Inndo的B+树和传统B+树的区别如下： InnoDb底层参考的是b+ 树，然而其实不完全相同，节点被称之为数据页和索引页，然而实际上索引页数据页除了数据类型不同基本一致，也就是索引即数据，数据即索引，索引它分为聚簇索引和辅助索引，聚簇索引最大特点是寄存键是主键ID，而主键ID依据肯定的规定生成或者在建表的时候指定，然而肯定会有一个主键索引，也就说一个表肯定存在主键。而辅助索引应用的是主键为索引字段的值，数值寄存的是索引主键。同层的数据页之间应用的是双向链表，索引页也是应用双向链表，这和B+树的数据结构是不一样的，传统B+ 树只在最底层的叶子节点为链表的设计。聚簇索引 聚簇索引指的是依据表的主键构建一个B+ 树。叶子节点间接寄存行数据而不是放指针，然而实际上叶子节点自身也是数据页只不过寄存的是指针而已。 上面的案例图仅仅为最毛糙的角度观察mysql的数据页设计，理论内容要远比这张图简单很多： 聚簇索引的特点非叶子节点存储的是索引，叶子节点则为数据，从左到右排序，在页决裂的时候，会把主键较大的值挪动到对应的数据页。索引页之间应用链表进行连贯，而叶子节点理论的数据存储区域，对立应用链条表进行串联。所以能够发现除开最顶层，所有的层级页和页之间是由链表之间链接的。每一个数据页蕴含infimum数据行代表以后数据页的第一个节点也就是最小值，supermum代表最初一个节点也就是最大值，这两个“行记录”是Mysql设计者的一个小把戏，目标是不便数据的查找和不同数据页之间的串联，也就是说每一个数据页默认至多有两个“虚构”数据行。所有的数据页号会组成一个页目录，依照最大数据的数据页号进行排序，页目录外面从小到大寄存了主键的id值，通过值找到对应的数据页内容，用于疾速定位数据所在的数据页。Innodb 默认为主键索引也就是聚簇索引。为什么要应用从大到小的程序进行排序？ 其实次要是为了应用二分查找办法疾速定位和查找数据页，进步查找的效率。留神因为晚期Mysql版本中的索引设计只能依照升序的形式进行排列，导致聚簇索引少数为升序的索引，在8.0的版本中失去优化。 辅助索引 辅助索引的存在模式： 和主键索引的设计一样，然而key寄存的是索引字段的值，值是主键值。辅助索引依据建设的索引除联结索引的状况外均为有几个索引建设几颗B+ 树。辅助索引相当于一颗新的B+ 树。 主键索引： 主键索引也叫聚簇索引，因为底层应用了B+ 树的设计构造，所以Mysql必然有主键并且以主键作为索引的模式。主键索引指的是键为主键，值为数据一种 索引模式。一旦创立表则零碎默认会存在一颗以主键索引的B+ 树。回表是什么？ 当辅助索引进行查问的时候因为查问的后果为主键的值，所以须要依据主键的值再去聚簇索引依据二分法查找一遍，这时候等于须要再查一遍聚簇索引，实质上是查了两次B+ 树，所以叫回表。 上面的示意图是一次回表操作： 假如咱们须要搜寻值为5的数据，首先会在二级索引通过二分遍历“槽”的模式找到具体所在的数据行，这个数据行保留索引值之外还存储了主键的值，所以这里须要拿到主键的值回到聚簇索引中找到理论存储的行记录。 然而如果查找条件和查找列都为索引值实际上会应用“笼罩索引”的查找形式，不须要回表操作。 索引算法 对于刚刚接触B+树的同学看到这些数据结构可能会懵圈，同时也不分明为什么要设计这么个简单的玩意，所以在课程中引入了各种数据结构来介绍为什么最终抉择了B+树的构造，上面咱们来简略比照各种常见的数据结构来理解为什么最初抉择了B+ 树这种数据结构。 ...

springboot实战电商我的项目mall4j （https://gitee.com/gz-yami/mall4j) java商城零碎源码 mysql-索引（InnoDB）InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的根本单位，一个页的默认大小为 16KB，它由七局部形成：File Header: 页的通用信息 Page Header: 页的专有信息 Infimum + Supremun: 零碎生产的记录，存储页内最大和最小的记录 User Records: 存储用户记录 Free Space: 页中为应用的空间 Page Directory: 存储页中记录的地位 File Trailer: 校验页是否残缺 // 查看页的大小（单位：字节）show status like 'innodb_page_size';页的数据数据排序次要是依附表的主键id，这也是创立表的时候倡议要创立一个主键，但这并不是强制性，理论创立表的过程中会发现没有指定主键也能胜利的创立一个表，其实用户没有指定主键的时候，InnoDb每一列的进行循环试图逐列去寻找一列所有元素都不反复的作为主键，如果切实找不到InnoDn就会保护一个暗藏列来作为主键。为什么创立表的时候会举荐应用整型的自增的主键？首先数据在页中是以单向链表的形式进行存储，如果应用uuid之类的作为主键，其自身是无序的而数据在页中的寄存是有序的，所以在每次进行插入的数据依据排序规定大概率会在链表的两头，如果这时该页的大小已满16k，就须要依据插入的数据来从新调整后续页的数据；与之相比应用整型的自增的主键就具备很显著的劣势了，因为自增的性质在失常状况下新插入的数据的主键id会大于上一条数据的主键id而直接插入到链表开端的地位，所以新的数据就不须要大规模的去调整已有页的数据。 在InnoDB中，表数据组织形式是主键汇集索引，并且因为一个表只能有一个主键， 所以也只能有一个汇集索引。其余索引的构造则是通过索引键值加主键值组合来惟一确定一条记录，这些数据在逻辑上间断的，但从从物理存储构造上来看，汇集索引的存储并不是间断的。这其中有两点：1、叶子节点中蕴含着列的数据，并且是通过双向链表进行链接的，而页依照主键的程序排序； 2、每个页中的记录也是通过双向链表进行保护的，物理存储上能够同样不依照主键存储。 汇集索引其实就是主键索引，InnoDB中的数据是面向主键索引进行数据存储的。而汇集索引就是依照每张表的主键来结构一棵B+Tree，同时叶子节点中存储的是整张表的行记录信息，也能够将汇集索引的叶子节点称为数据页。因而，汇集索引的这个个性，决定了索引组织表中的数据也是索引的一部分。和B+Tree的数据结构一样，每个数据页都通过一个双向的链表来进行链接。须要着重留神的是，在InnoDB的B+Tree索引数据结构中，只有在叶子节点上寄存的是残缺的每行记录，而在非数据页的索引页中，寄存的仅仅是主键值及指向数据页的偏移量，而不是一个残缺的行记录。主键索引结构图 二级索引结构图 Mysql中的检索分为两种，一种是索引检索，还有一种是全表检索。在有索引时mysql会优先应用索引进行检索，没有创立索引或索引生效时应用全表检索，应用索引进行检索毫无疑问是会比全表检索的速度更快，但数据表每减少一个索引都要开拓出一片空间来存储B+树索引数据结构，如果一张表的数据量很大而符合条件的后果又很少，那么不加索引会引起致命的性能降落。然而也不是什么状况都非得建索引不可，比方一些字段可能就只有两个值，建索引不仅没什么劣势，还会影响到更新速度，这被称为适度索引。主键索引叶子节点中蕴含了列的数据，在进行检索时检索的字段不会导致索引生效，并且在范畴检索时也能应用索引。而二级索引在检索时就须要留神检索的字段防止应用*，尽量只返回须要检索的数据。二级索引还可能通过联结索引来缩小索引创立的数量，能够应在查问时有多个字段总是同时呈现则这些字段就能够作为复合索引,造成索引笼罩能够进步查问的效率。springboot实战电商我的项目mall4j （https://gitee.com/gz-yami/mall4j) java商城零碎源码

建设索引与查问优化关键字：区分度组合索引创立【举荐】建组合索引的时候，区分度最高的在最右边。正例：如果 where a=? and b=?，a 列的简直靠近于惟一值，那么只须要单建 idx_a 索引即可。阐明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： where c>? and d=? 那么即便 c 的区分度更高，也必须把 d 放在索引的最前列，即建设组合索引 idx_d_c。含糊查问4. 【强制】页面搜寻严禁左含糊或者全含糊，如果须要请走搜索引擎来解决。阐明：索引文件具备 B-Tree 的最左前缀匹配个性，如果右边的值未确定，那么无奈应用此索引。order by 查问【举荐】 如果有 order by 的场景，请留神利用索引的有序性。 order by 最初的字段是组合索引的一部分，并且放在索引组合程序的最初，避免出现 file_sort 的状况，影响查问性能。正例：where a=? and b=? order by c; 索引：a_b_c反例：索引如果存在范畴查问，那么索引有序性无奈利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。笼罩索引优化波及概念：回表查问，汇集索引，一般索引笼罩索引介绍InnoDB： 有两大索引，汇集索引和一般索引；汇集索引存储行数据，一般索引存储主键值回表查问，以一般索引查问行的全副数据，必须走两张索引表，先到一般索引获取主键，再到汇集索引获取行数据。笼罩索引，须要查问的数据在索引信息外面曾经全副蕴含，不须要再回表。举例create table user (id int primary key,name varchar(20),sex varchar(5),index(name))engine=innodb; 表user的一般索引name，查问1，只有name信息，不必回表，因为nane曾经在索引中select name from user where name='xxx';查问2，sex信息须要从行记录里获取，须要回表select name,sex from user where name ='xxx'; 查问2的笼罩索引优化，对标user建设索引index(name, sex) ...

转自MySQL索引总结

《MySQL面试小抄》索引考点二面总结我是肥哥，一名不业余的面试官！ 我是囧囧，一名踊跃找工作的小菜鸟！ 囧囧示意：小白面试最怕的就是面试官问的知识点太抽象，本人无奈疾速定位到关键问题点！！！本期次要面试考点 面试官考点之谈谈索引保护过程？页决裂？页合并？面试官考点之简述一下查问时B+树索引搜寻过程？面试官考点之什么是回表？面试官考点之什么是索引笼罩？应用场景？面试官考点之什么状况下会索引生效？面试官考点之哪些状况下，可能会面临索引生效的问题？面试官考点之or走索引和索引生效别离是什么场景？面试官考点之哪些状况下须要创立索引？面试官考点之联结索引之最左前缀准则？面试官考点之索引下推场景？ 面试官考点之谈谈索引保护过程？页决裂？页合并？B+树为了保护索引有序性，在插入删除的时候须要做必要的保护，必要时候可能波及到页决裂，页合并过程！ 首先假如每个叶子节点（数据页或磁盘块）只能存储3条索引和数据记录，如图 状况1、新增行记录，ID=3，此时【数据页1】未满，只须要在data2后新增ID=3的行记录，B+树整体构造不须要进行调整 状况2、新增行记录，ID=8，此时【数据页2】已满，这时候须要申请一个新的数据页，而后移动局部数据过来。这个过程称为页决裂。页决裂过程耗费性能，同时空间利用率也升高了 有决裂就有合并，当相邻两个页因为删除了数据，利用率很低之后，会将数据页做合并。合并的过程，能够认为是决裂过程的逆过程。 当相邻两个页因为删除了数据，利用率很低之后，会将数据页做合并。合并的过程，能够认为是决裂过程的逆过程。 【数据页2】删除了ID=7,ID=8的行记录，此时【数据页2】【数据页3】利用率很低，将进行页合并。 面试官考点之简述一下查问时B+树索引搜寻过程？筹备一张用户表，其中id为主键，age为一般索引 CREATE TABLE `user` ( `id` int(11) PRIMARY KEY, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL KEY `idx_age` (`age`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;select * from user where age=22 简述一下B+树索引搜寻过程？ 假如要查问的记录 id=5,name="张三",age=22MySQL为每个索引别离保护了一棵B+Tree索引树， 主键索引非叶子节点保护了索引键，叶子节点存储行数据； 非主键索引也称为二级索引，非叶子节点存储主键； B+树索引搜寻过程 搜寻条件 age=22，可走idx_age索引，首先加载idx_age索引树，找到age=22的记录，获得id=5 回表搜寻，加载主键索引树，找到id=22的记录，获得整行数据 面试官考点之什么是回表？idx_age二级索引树找到主键id后，回到id主键索引搜寻的过程,就称为回表。 并非所有非主键索引搜寻，都须要进行回表搜寻，也就是上面要说的索引笼罩。 面试官考点之什么是索引笼罩？应用场景？在下面提到的例子中，因为查问后果所须要的数据只在主键索引上有，所以不得不回表。 如果在查问的数据列外面，间接从索引列就能取到想要的后果，就不须要再回表去查，也称之为索引笼罩！ 索引笼罩的长处 能够防止对Innodb主键索引的二次查问能够防止MyISAM表进行零碎调用能够优化缓存,缩小磁盘IO操作批改一下上述栗子，满足索引笼罩条件？select id, age from user where age=22查问的信息，id，age都能够间接在idx_age 索引树中获取，不须要回表搜寻。 因为笼罩索引能够缩小树的搜寻次数，显著晋升查问性能，所以应用笼罩索引是一个罕用的性能优化伎俩。 索引是一把双刃剑，提供疾速排序搜寻的同时，索引字段的保护也是要付出相应的代价的。 因而，在建设冗余索引来反对笼罩索引时就须要衡量思考了 ...

数据库为什么须要索引呢？咱们都是晓得数据库的数据都是存储在磁盘上的，当咱们程序启动起来的时候，就相当于一个过程运行在了机器的内存当中。所以当咱们程序要查问数据时，必须要从内存进去到磁盘外面去查找数据，而后将数据写回到内存当中。然而磁盘的io效率是远不如内存的，所有查找数据的快慢间接影响程序运行的效率。而数据库加索引的次要目标就是为了应用一种适合的数据结构，能够使得查问数据的效率变高，缩小磁盘io的次数，晋升数据查找的速率，而不再是愣头青式的全局遍历。 那索引为啥要用B+Tree的数据结构呢？如果咱们简略的想的话，想要疾速的查找到数据，感觉hash表是最快的，依据key，hash到某个槽位上，间接一次查找就能够精确的找到数据的地位，这多快呀。然而咱们在做业务时，往往只须要一条的数据需要很少，大部分的需要都是依据肯定的条件查问一部分的数据，这个时候hash显示不是很适合。 咱们再思考树，比方二叉树，均衡二叉树，红黑树，B树等，他们都是二分查找，找数也快，然而不论是均衡二叉树还是优化后的红黑树，说到底他们都是二叉树，当节点多了的时候，它们的高度就会高呀，我找一个数据。根节点不是，那就找下一层，下一层还没有我就再去找下一层，这样造成的结果就是我找一个数据可能要找好几次，而每一次都是执行了一次磁盘的io，而咱们的索引的目标就是要缩小磁盘io呀，这样设计可不行。那咱们是不是把高度变矮就能够了呢？所以咱们再思考下B树。首先简略介绍下B树的数据结构：首先看看B树的定义。 每个节点最多有m-1个关键字（能够存有的键值对）。根节点起码能够只有1个关键字。非根节点至多有m/2关键字。每个节点中的关键字都依照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都雷同。每个节点都存有索引和数据，也就是对应的key和value。所以，根节点的关键字数量范畴：1 <= k <= m-1，非根节点的关键字数量范畴：m/2 <= k <= m-1。 这里的m示意阶数，阶数示意了一个节点最多有多少个孩子节点，所以形容一颗B树时须要指定它的阶数。 咱们再举个例子来阐明一下下面的概念，比方这里有一个5阶的B树，根节点数量范畴：1 <= k <= 4，非根节点数量范畴：2 <= k <= 4。 上面，咱们通过一个插入的例子，解说一下B树的插入过程，接着，再解说一下删除关键字的过程。 1.2 B树插入插入的时候，咱们须要记住一个规定：判断以后结点key的个数是否小于等于m-1，如果满足，直接插入即可，如果不满足，将节点的两头的key将这个节点分为左右两局部，两头的节点放到父节点中即可。 例子：在5阶B树中，结点最多有4个key,起码有2个key（留神：上面的节点对立用一个节点示意key和value）。 插入18，70，50,40 插入22 插入22时，发现这个节点的关键字曾经大于4了，所以须要进行决裂，决裂的规定在下面曾经讲了，决裂之后，如下。 接着插入23，25，39 决裂，失去上面的。 所以B树每一层的节点数会变多，雷同的数据量的话，B树会比二叉树高度更低，须要的io次数就会变少，所以合乎咱们的索引需要。那MySQL最初为什么抉择了B+树呢，比B树更优的中央在哪里呢？咱们先看看B+树与B树不同的中央： B+树叶子节点蕴含了这棵树的所有键值，非叶子节点不存储数据，只存储索引，数据都存储在叶子节点。而B树是每个节点都存有索引和数据。B+树每个叶子结点都存有相邻叶子结点的指针，叶子结点自身依关键字的大小自小而大程序链接。如图： 第一点：当非叶子节点只存索引key而不存data时，就能够使得非叶子节点的占用空间变少，雷同容量的节点能够存储更多的索引，那同样是三层的B+树，阶数就会变多，就会比B树存更多的数据。第二点：B+树叶子节点存有相邻叶子节点的指针，想要了解这个指针的益处，咱们的先晓得磁盘读取数据时往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存。这样做的理论依据是计算机科学中驰名的局部性原理： 当一个数据被用到时，其左近的数据也通常会马上被应用。程序运行期间所须要的数据通常比拟集中。预读的长度个别为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区宰割为间断的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位替换数据。当程序要读取的数据不在主存中时，会触发一个缺页异样，此时零碎会向磁盘收回读盘信号，磁盘会找到数据的起始地位并向后间断读取一页或几页载入内存中，而后异样返回，程序持续运行。 当初再看B+树叶子节点的指针，咱们就明确了它的用途，预读的时候能够保障间断读取的数据有序。 可能还有的同学提过B*树，它是在B+树根底上，为非叶子结点也减少链表指针。集体感觉没用B星树可能是感觉没必要吧，咱们在非叶子节点又不存data，data都在叶子节点，非叶子节点了链表指针用不上。 一些花里胡哨的概念聚簇索引和非聚簇索引：下面咱们提到B+树的叶子节点存了索引key的数据data，然而mysql不同的引擎存data的抉择是不一样的，MyISAM是将索引文件和实在的数据文件分两个文件各种寄存，索引文件中存的data是该索引key对应的数据在数据文件中的地址值，而InnoDB则是将正式的数据存在了叶子节点中。所以聚簇和非聚簇就是辨别叶子节点存的data是不是实在的（能够了解为叶子节点挤不挤？） 回表：回表也简略，然而得先明确主键索引和一般索引，下面咱们所的叶子节点存实在的数据，那是只有主键索引才是这么存的，一般索引它存的data是主键索引的key。那这样咱们就好了解了。比方我当初给一张表的name字段建了个一般索引，我想select * from table where name = 'test'，这个时候咱们找到test节点的时候，拿到的key只是这行数据对应的主键key，咱们要失去整行的数据只能拿着这个key再去主键索引树再找一次。这个操作就叫做回表。 最左匹配准则： 当咱们新建了一个组合索引时，比方（name+age），查问时应用 where name = xx and age = xx时会走组合索引，而where age = xx and name =xx则不会走。这是因为MySQL创立联结索引的规定是首先会对联结索引的最右边第一个字段排序，在第一个字段的排序根底上，而后在对第二个字段进行排序。

越致力，越侥幸，本文已珍藏在GitHub中JavaCommunity, 外面有面试分享、源码剖析系列文章，欢送珍藏，点赞https://github.com/Ccww-lx/JavaCommunity数据库索引在平时的工作是必备的，怎么建索引，怎么应用索引，能够进步数据的查问效率。而且在面试过程，数据库的索引也是必问的知识点，比方： 索引底层构造选型，那为什么抉择B+树？不同存储引擎的索引的体现模式有哪些？索引的类型 组合索引存储形式查问形式最左前缀匹配准则笼罩索引是什么？看着这些，能说出多少，了解多少呢？因而咱们须要去探索其内在原理。 那索引是什么？索引的目标为了减速检索数据而设计的一种扩散存储（索引经常很大，属于硬盘级的货色，所以是扩散存储）的数据结构,其原理以空间换工夫。而疾速检索的实现的实质是数据结构，通过不同数据结构的抉择，实现各种数据疾速检索，索引有哈希索引和B+树索引。 索引底层构造选型，那为什么抉择B+树？数据库索引底层选型归根到底就是为进步检索效率，那么就须要思考几个问题： 算法工夫复杂度是否存在排序磁盘IO与预读NOTE： 思考到磁盘IO是十分昂扬的操作，计算机操作系统做了一些优化，当一次IO时，不光把以后磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为部分预读性原理通知咱们，当计算机拜访一个地址的数据的时候，与其相邻的数据也会很快被拜访到。每一次IO读取的数据咱们称之为一页(page)。哈希表( Hash Table,散列表 )哈希表是依据键（Key）而间接拜访在内存存储地位的数据结构。 通过计算一个对于键值的函数，将所需查问的数据映射到表中一个地位来拜访记录，这放慢了查找速度。尽管查问工夫复杂度为O(1)，但存在着碰撞问题，最坏状况会导致工夫简单急剧减少; 而且哈希表其只适宜精准key（等于）检索，不适宜范畴式检索，范畴检索就须要一次把所有数据找进去加载到内存，没有效率，因而不适宜Mysql的底层索引的数据结构。 一般的二叉查找树为了优化高效范畴查问，且工夫复杂度小，引入二叉查找树 二叉查找树的工夫复杂度是 O(lgn)，因为数据已排序好了，所以范畴查问是能够高效查问， 但会存在的问题：左右子节点的深度可能相差很大，最极其的状况只有左子树或者右子树，此时查找的效率为O(n)，检索性能急剧下降，因而也不适宜Mysql的底层索引的数据结构。 均衡二叉树（AVL树）为了优化二叉树左右子树深度相差太大的问题，咱们引入了均衡二叉树，即左右子节点的深度差不超过1均衡二叉树看来如同适宜，能够实现： 能够实现范畴查找、数据排序查问性能良好O(logn) NOTE:上图中一个磁盘块，代表硬盘上的一个存储地位然而咱们还有一个最重要因素须要思考，磁盘IO与预读，且数据库查问数据的瓶颈在于磁盘 IO,应用均衡二叉树依据索引进行查找时，每读一个磁盘块就进行一次IO，这样没有实现计算机的预读，导致检索效率，总结出均衡二叉树作为索引的问题（上图中一个磁盘块，代表硬盘上的一个存储地位）： 太深了（即它只有二条路），深度越大进行的IO操作也就越多太小了，每一次IO才查问磁盘块这么一点数据，太节约IO了。操作系统规定一次IO最小4K，Mysql一次IO 16K，而图上的磁盘块能显著达不到4KB+树为了优化磁盘IO和预读，缩小IO操作，条路太少了，那么换成多条路，那么会想到应用B树和B+树，但B树每个节点限度最多存储两个 key，也会造成IO操作过于频繁，因而优化思路为：尽可能在一次磁盘 IO 中多读一点数据到内存，那么B+树也该出场： B+树一个节点能存很多索引，且只有B+树叶子节点存储数据相邻节点之间有一些前驱后继关系叶子节点是顺序排列的绝对于B树，B+树的劣势有： B+树扫库扫表的能力更强 B树的数据是寄存在每一个节点中的，节点所在的物理地址又是随机的，所以扫表的话，进行的是随机IOB+树的数据是寄存在叶子节点的，且在一个叶子节点中的数据是间断的，所以扫表的话，进行的绝对的程序IOB+树的磁盘读写能力更强，枝节点不保留数据，而保留更多的关键字。一次IO就能读出更多的关键字B+树的排序能力更强，B+树的叶子节点存储的数据是曾经排好序的索引的体现模式索引在不同的存储引擎中体现模式步一样， 最常见的是： Innodb 引擎中体现为汇集索引形式 （索引和数据是寄存在同一个文件的）Myisam引擎中体现为非汇集索引形式 （索引和数据是寄存在两个文件中的）汇集索引形式（InnoDB存储引擎）InnoDB存储引擎中，索引和数据是寄存在同一个文件的，属于汇集索引 。而且InnoDB会主动建设好主键 ID 索引树, 因而建表时要求必须指定主键的起因。 其中，主键索引（汇集索引）的叶子节点记录了数据，而不是数据的物理地址。辅助索引的叶子节点寄存的是主键key。所以当利用辅助索引查找数据时，实际上查了两遍索引（辅助索引和主键索引）: 先查问辅助索引树找出主键而后在主键索引树中依据主键查问数据 非汇集索引形式（Myisam存储引擎）Myisam存储引擎中，索引和数据是寄存在两个文件中的，属于非汇集索引 。不论是主键索引还是辅助索引，其叶子节点都是记录了数据的物理地址。 MySQL的索引类型MySQL索引能够分为： 一般索引(index): 减速查找惟一索引： 主键索引：primary key ：减速查找+束缚（不为空且惟一）惟一索引：unique：减速查找+束缚 （惟一）联结索引： primary key(id,name):联结主键索引unique(id,name):联结惟一索引index(id,name):联结一般索引全文索引full text :用于搜寻很长一篇文章的时候，成果最好。其中，次要了解一下联结索引的问题，存储构造，查问形式。 联结索引联结索引，多个列组成的索引叫做联结索引，单列索引是非凡的联结索引。其存储构造如下： <font color='red'>对于联结索引来说其存储构造只不过比单值索引多了几列，组合索引列数据都记录在索引树上，（不同的组合索引，B+树也是不同的），且存储引擎会首先依据第一个索引列排序后，其余列再顺次将相等值的进行排序。</font> NOTE:叶节点第一排，按程序排序好，第二列，会基于第一列排序好的，将第一列相等的再下一列再排序，顺次类推。<font color='red'>联结索引查问形式，存储引擎首先从根节点（个别常驻内存）开始查找，而后再顺次在其余列中查问，直到找到该索引下的data元素即ID值，再从主键索引树上找到最终数据。</font> 而且联结索引其抉择的准则： 最左前缀匹配准则（常常应用的列优先）离散度高的列优先宽度小的列优先最左前缀匹配准则最左前缀匹配准则和联结索引的索引构建形式及存储构造是有关系的。根据上述了解剖析，能够得出联结索引只能从多列索引的第一列开始查找索引才会失效，比方: 假如表user上有个联结索引(a,b,c)，那么 select * from user where b = 1 and c = 2将不会命中索引起因是联结索引的是存储引擎先按第一个字段排序，再按第二个字段排序，顺次排序。 ...

index索引用于疾速查找具备特定列值的行，如果没有索引，mysql必须从第一行开始，扫描全表找到对应的行。表越大，破费越多。如果表中有相干的索引，mysql能够疾速确定要在数据文件中查找的地位。大多数mysql索引（primary key，unique，index和fulltext）存储在B-trees。空间数据类型的索引应用R-trees；MEMORY表还反对hash indexes；InnoDB对fulltext应用倒排表。MySQL应用索引进行以下操作： 通过where条件疾速查问。最优匹配。如果能够在多个索引之间进行抉择，则MySQL通常会应用查找最小行数的索引。如果表具备多列索引，那么优化器能够应用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引 (col1, col2, col3)，你能够索引的搜寻有(col1)， (col1, col2)以及(col1, col2, col3)。执行联接时从其余表中检索行。如果申明雷同的类型和大小，MySQL能够更无效地在列上应用索引。在这种状况下， VARCHAR与 CHAR被认为是雷同的，如果它们被申明为雷同的大小。例如， VARCHAR(10)和 CHAR(10)是雷同的大小，然而 VARCHAR(10)和 CHAR(15)不是。对于非二进制字符串列之间的比拟，两个列应应用雷同的字符集，不同的字符集将导致索引生效。例如，将utf8列与latin1列进行比拟会排除应用索引。如果不能不通过转换间接比拟值，则比拟不同的列（例如，将字符串列与工夫或数字列进行比拟）可能会阻止应用索引。对于给定的值，如数值列的值为1，它可能比拟等于在字符串列，例如任何数量的值 '1'，' 1'， '00001'，或'01.e1'，导致索引生效。在索引列应用MIN()或 MAX()。mysql预处理器将进行优化，预处理器会查看您是否正在索引呈现的所有要害局部上应用。在这种状况下，MySQL为每个表达式执行一次键查找，并将其替换为常量，所有表达式都用常量替换实现后，查问将立刻返回。排序或分组查问（order by， group by）应用最左匹配索引（order by key1, key2）；如果倒序排序（order by key1, key2 desc）,将按相同程序应用索引key。某些状况下，mysql会间接从索引中获取数据，而不必查问表；例如：只查问索引列数据。留神：当开启列长事务时，可能导致该优化生效，回表查问。当全表扫描快于走索引查问时，mysql也不会走索引。covering index查问的所有列都蕴含在索引中时，mysql不会扫描表（即不会回表查问），这种状况mysql定义为covering index；InnoDb引擎下，开启事务时，将不会应用这种优化查问。 参考资料8.5.2 Optimizing InnoDB Transaction Management

背景我置信大家在数据库优化的时候都会说到索引，我也不例外，大家也基本上能对数据结构的优化答复个一二三，以及页缓存之类的都能扯上几句，然而有一次阿里P9的一个面试问我：你能从计算机层面开始说一下一个索引数据加载的流程么？（就是想让我聊IO） 我当场就逝世了....因为计算机网络和操作系统的基础知识真的是我的盲区，不过前面我恶补了，废话不多说，咱们就从计算机加载数据聊起，讲一下换个角度聊索引。 如果感觉看完文章有所播种的话，能够关注我一下哦知乎：秃顶之路 b站：linux亦有归途每天都会更新咱们的公开课录播以及编程干货和大厂面经或者间接点击链接c/c++ linux服务器开发高级架构师来课堂上跟咱们讲师面对面交换须要大厂面经跟学习纲要的小伙伴能够加群973961276获取 注释MySQL的索引实质上是一种数据结构让咱们先来理解一下计算机的数据加载。 磁盘IO和预读： 先说一下磁盘IO，磁盘读取数据靠的是机械运动，每一次读取数据须要寻道、寻点、拷贝到内存三步操作。 寻道工夫是磁臂挪动到指定磁道所须要的工夫，个别在5ms以下； 寻点是从磁道中找到数据存在的那个点，均匀工夫是半圈工夫，如果是一个7200转/min的磁盘，寻点工夫均匀是600000/7200/2=4.17ms； 拷贝到内存的工夫很快，和后面两个工夫比起来能够忽略不计，所以一次IO的工夫均匀是在9ms左右。听起来很快，但数据库百万级别的数据过一遍就达到了9000s，显然就是劫难级别的了。 思考到磁盘IO是十分昂扬的操作，计算机操作系统做了预读的优化，当一次IO时，不光把以后磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为当计算机拜访一个地址的数据的时候，与其相邻的数据也会很快被拜访到。 每一次IO读取的数据咱们称之为一页(page)，具体一页有多大数据跟操作系统无关，个别为4k或8k，也就是咱们读取一页内的数据时候，实际上才产生了一次IO。 （忽然想到个我刚毕业被问过的问题，在64位的操作系统中，Java中的int类型占几个字节？最大是多少？为什么？） 那咱们想要优化数据库查问，就要尽量减少磁盘的IO操作，所以就呈现了索引。 索引是什么？MySQL官网对索引的定义为：索引(Index)是帮忙MySQL高效获取数据的数据结构。 MySQL 中罕用的索引在物理上分两类，B-树索引和哈希索引。 本次次要讲BTree索引。 BTree索引BTree又叫多路均衡查找树，一颗m叉的BTree个性如下： 树中每个节点最多蕴含m个孩子。除根节点与叶子节点外，每个节点至多有[ceil(m/2)]个孩子（ceil()为向上取整）。若根节点不是叶子节点，则至多有两个孩子。所有的叶子节点都在同一层。每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1 。![](data:image/svg+xml;utf8,<?xml version="1.0"?><svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="800" height="600"></svg>)这是一个3叉（只是举例，实在会有很多叉）的BTree结构图，每一个方框块咱们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，紫色代表的是磁盘块中的数据key，黄色代表的是数据data，蓝色代表的是指针p，指向下一个磁盘块的地位。 来模仿下查找key为29的data的过程： 1、依据根结点指针读取文件目录的根磁盘块1。【磁盘IO操作1次】 2、磁盘块1存储17，35和三个指针数据。咱们发现17<29<35，因而咱们找到指针p2。 3、依据p2指针，咱们定位并读取磁盘块3。【磁盘IO操作2次】 4、磁盘块3存储26，30和三个指针数据。咱们发现26<29<30，因而咱们找到指针p2。 5、依据p2指针，咱们定位并读取磁盘块8。【磁盘IO操作3次】 6、磁盘块8中存储28，29。咱们找到29，获取29所对应的数据data。 由此可见，BTree索引使每次磁盘I/O取到内存的数据都施展了作用，从而进步了查问效率。然而有没有什么可优化的中央呢？ 咱们从图上能够看到，每个节点中不仅蕴含数据的key值，还有data值。而每一个页的存储空间是无限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查问时的磁盘I/O次数，进而影响查问效率。 B+Tree索引B+Tree是在B-Tree根底上的一种优化，使其更适宜实现外存储索引构造。在B+Tree中，所有数据记录节点都是依照键值大小程序寄存在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样能够大大加大每个节点存储的key值数量，升高B+Tree的高度。 ![](data:image/svg+xml;utf8,<?xml version="1.0"?><svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="800" height="600"></svg>)B+Tree绝对于B-Tree有几点不同： 非叶子节点只存储键值信息， 数据记录都寄存在叶子节点中， 将上一节中的B-Tree优化，因为B+Tree的非叶子节点只存储键值信息，所以B+Tree的高度能够被压缩到特地的低。 具体的数据如下： InnoDB存储引擎中页的大小为16KB，个别表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也个别为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大略存储16KB/(8B+8B)=1K个键值（因为是估值，为不便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引能够保护10^3 10^3 10^3 = 10亿 条记录。（这种计算形式存在误差，而且没有计算叶子节点，如果计算叶子节点其实是深度为4了） 咱们只须要进行三次的IO操作就能够从10亿条数据中找到咱们想要的数据，比起最开始的百万数据9000秒不晓得好了多少个华莱士了。 而且在B+Tree上通常有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环构造。所以咱们除了能够对B+Tree进行主键的范畴查找和分页查找，还能够从根节点开始，进行随机查找。 数据库中的B+Tree索引能够分为汇集索引（clustered index）和辅助索引（secondary index）。 下面的B+Tree示例图在数据库中的实现即为汇集索引，汇集索引的B+Tree中的叶子节点寄存的是整张表的行记录数据，辅助索引与汇集索引的区别在于辅助索引的叶子节点并不蕴含行记录的全副数据，而是存储相应行数据的汇集索引键，即主键。 当通过辅助索引来查问数据时，InnoDB存储引擎会遍历辅助索引找到主键，而后再通过主键在汇集索引中找到残缺的行记录数据。 ![](data:image/svg+xml;utf8,<?xml version="1.0"?><svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="800" height="600"></svg>)不过，尽管索引能够放慢查问速度，进步 MySQL 的解决性能，然而过多地应用索引也会造成以下弊病： 创立索引和保护索引要消耗工夫，这种工夫随着数据量的减少而减少。除了数据表占数据空间之外，每一个索引还要占肯定的物理空间。如果要建设聚簇索引，那么须要的空间就会更大。当对表中的数据进行减少、删除和批改的时候，索引也要动静地保护，这样就升高了数据的保护速度。留神：索引能够在一些状况下减速查问，然而在某些状况下，会升高效率。索引只是提高效率的一个因素，因而在建设索引的时候应该遵循以下准则： 在常常须要搜寻的列上建设索引，能够放慢搜寻的速度。在作为主键的列上创立索引，强制该列的唯一性，并组织表中数据的排列构造。在常常应用表连贯的列上创立索引，这些列次要是一些外键，能够放慢表连贯的速度。在常常须要依据范畴进行搜寻的列上创立索引，因为索引曾经排序，所以其指定的范畴是间断的。在常常须要排序的列上创立索引，因为索引曾经排序，所以查问时能够利用索引的排序，放慢排序查问。在常常应用 WHERE 子句的列上创立索引，放慢条件的判断速度。当初大家晓得索引为啥能这么快了吧，其实就是一句话，通过索引的构造最大化的缩小数据库的IO次数，毕竟，一次IO的工夫真的是太久了。。。 总结就面试而言很多常识其实咱们能够很容易就把握了，然而要以学习为目标，你会发现很多货色咱们得深刻到计算机根底上能力发现其中神秘，很多人问我怎么记住这么多货色，其实学习自身就是一个很无奈的货色，既然咱们不能不学那为啥不好好学？去学会享受呢？最近我也在恶补根底，前面我会开始更新计算机根底和网络相干的常识的。

1、前言MySQL数据库管理系统自身就是一个文件管理系统，尽管它的实现形式的确比较复杂，但实质上是要通过拜访磁盘能力实现数据的存储与检索。所以如果咱们想要进一步理解MySQL索引的的话，磁盘相干的操作也是须要大抵理解一下的。 2、硬盘的读写原理--------------------磁盘的硬件组成--------------------一般说来，硬盘都是由盘片、磁头、盘片主轴、管制电机、磁头控制器、数据转换器、接口、缓存等几个部份组成。--------------------硬盘的基本概念---------------------- 2.1 盘片硬盘由多个盘片组成，盘片个别用铝合金资料做基片，高速硬盘也可能用玻璃做基片，通常有2-3个盘片。-- 2.2 盘面每一个盘片都有两个盘面(Side)，即上、下盘面，个别每个盘面都会利用，都能够存储数据，成为无效盘片，也有极个别的硬盘盘面数为复数。每一个这样的无效盘面都有一个盘面号，按程序从上至下从0开始顺次编号。在硬盘零碎中，盘面号又叫磁头号，因为每一个无效盘面都有一个对应的读写磁头，因为磁盘通常有2-3个盘片，故盘面号为0-3或0-5。-- 2.3 磁头每一个无效盘面都有一个对应的读写磁头，作用就是将对盘面进行读写操作。-- 2.4 磁道磁盘在格式化时被划分成许多同心圆，这些同心圆轨迹叫做磁道(Track)。磁道从外向内从0开始程序编号。硬盘的每一个盘面有300-1024个磁道。-- 2.5 柱面所有盘面上的同一磁道形成一个圆柱，通常称做柱面(Cylinder)，每个圆面上的磁头由上而下从0开始编号。数据的读/写按柱面进行，即磁头读/写数据时首先在同一柱面内从0盘面开始进行操作，顺次向下在同一柱面的不同盘面上进行操作，只有当同一柱面上所有的盘面全副读/写结束后，磁头才转移到下一柱面(同心圆再往里的柱面)。为什么磁盘的读写操作是依照柱面进行？起因是选取盘面(磁头)只需通过电子切换即可，而选取柱面则必须通过机械切换。又因为电子切换比在机械上磁头向邻近磁道挪动快得多，所以磁盘的读写操作是依照柱面进行。也就是说，在进行写操作时，在某个柱面的某个盘面的磁道写满数据后，切换到同一柱面的下一个盘面的磁道上接着写，一个柱面写满后，才移到下一个柱面的0盘面。读数据也依照这种形式进行，这样就进步了硬盘的读/写效率。-- 2.6 扇区每个磁道被等分为若干个弧段，这些弧段便是硬盘的扇区(Sector)，扇区是硬盘的最小读写单元。一个扇区有两个次要局部：存储数据地点的标识符和存储数据的数据段。 标识符：包含组成扇区三维地址的三组数字 柱面号：扇区所在的磁道; 盘面号：扇区所在的盘面; 扇区号：扇区在磁道上的地位。也叫块号; 数据段：512个字节的数据-- 2.7 盘块操作系统与磁盘打交道的最小单位是磁盘块。因为扇区的内容比拟小，且数目泛滥，导致操作系统在寻址时比拟艰难，所以操作系统就将相邻的扇区(2的N次方个扇区)组合在一起，造成一个块，再对块进行整体的操作。--------------------磁盘的拜访流程--------------------1、获取数据的物理地址：当操作系统须要从磁盘读取数据时，操作系统会将数据的逻辑地址传给磁盘，磁盘的控制电路依照寻址逻辑将逻辑地址翻译成物理地址(柱面号、盘面号、扇区号)。2、从指定扇区读取数据：在获取了数据的物理地址之后，接下来从扇区上读取的数据。首先磁盘须要找到柱面与盘面，即磁头须要挪动对准相应柱面下的磁道，这个过程叫做寻道，所消耗工夫叫做寻道工夫。而后指标扇区旋转到磁头下，即磁盘旋转将指标扇区旋转到磁头下，这个过程消耗的工夫叫做旋转工夫。最初将扇区中的数据传输到操作系统的内存中，所消耗工夫叫做传输工夫。3、即一次访盘申请(读/写)实现过程由三个动作组成： 寻道提早（工夫）：指定磁头挪动定位到指定柱面的指定磁道 旋转提早（工夫）：期待指定扇区从磁头下旋转通过 数据传输（工夫）：数据在磁盘与内存之间的理论传输 --------------------磁盘的拜访原理--------------------零碎将文件存储到磁盘上时，按柱面、磁头、扇区的形式进行，即最先读/写第0磁道的第0磁头下(也就是第0盘面的第0磁道)的所有扇区。而后是同一柱面的下一磁头，……，一个柱面存储满后就推动到下一个柱面，直到把文件内容全副写入磁盘。文件的记录在同一盘组上寄存时，应先集中放在一个柱面上，而后再程序寄存在相邻的柱面上，对应同一柱面，则应该按盘面的秩序程序寄存。即：从上到下，而后从外到内。数据的读/写按柱面进行，而不按盘面进行。零碎也以雷同的程序读出数据。读出数据时通过通知磁盘控制器要读出扇区所在的柱面号、磁头号和扇区号（物理地址的三个组成部分）进行。磁盘控制器则 间接使磁头部件步进到相应的柱面，选通相应的磁头，期待要求的扇区挪动到磁头下。在扇区到来时，磁盘控制器读出每个扇区的头标，把这些头标中的地址信息与期待检出的磁头和柱面号做比拟（即寻道），而后，寻找要求的扇区号。待磁盘控制器找到该扇区头标时，依据其工作是写扇区还是读扇区，来决定是转换写电路， 还是读出数据和尾部记录。找到扇区后，磁盘控制器必须在持续寻找下一个扇区之前对该扇区的信息进行后处理。如果是读数据，控制器计算此数据的ECC码，然 后，把ECC码与已记录的ECC码相比拟。如果是写数据，控制器计算出此数据的ECC码，与数据一起存储。在控制器对此扇区中的数据进行必要解决期间，磁 盘持续旋转。--------------------局部性原理与磁盘预读--------------------因为存储介质的个性，磁盘自身存取就比主存慢很多，再加上机械运动消耗，磁盘的存取速度往往是主存的几百分分之一，因而为了提高效率，要尽量减少磁盘I/O。为了达到这个目标，磁盘往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存。这样做的理论依据是计算机科学中驰名的局部性原理：　1、当一个数据被用到时，其左近的数据也通常会马上被应用。　2、程序运行期间所须要的数据通常比拟集中。因为磁盘程序读取的效率很高（不须要寻道工夫，只需很少的旋转工夫），因而对于具备局部性的程序来说，预读能够进步I/O效率。预读的长度个别为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区宰割为间断的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位替换数据。当程序要读取的数据不在主存中时，会触发一个缺页异样，此时零碎会向磁盘收回读盘信号，磁盘会找到数据的起始地位并向后间断读取一页或几页载入内存中，而后异样返回，程序持续运行。3、索引的根本应用--------------------索引简介--------------------数据库在保留数据时，除了保留自身之外，还保护着一个满足特定查找算法的数据结构，这些数据结构以某种形式指向数据，这样就能够在这些数据结构的根底上实现高级查找算法，这种数据结构就是索引。简而言之，索引就是帮忙MySQl高效获取数据的数据结构。--------------------索引语法------------------------------一般索引的创立语法------------ 形式一：间接创立索引CREATE INDEX index_name ON table_name (column_name);-- 形式二：批改表构造时增加索引ALTER TABLE table_name ADD INDEX index_name ON column_name;-- 形式三：创立表时间接增加索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL, INDEX id_dept_deptname (dept_name))----------主键索引的创立语法------------ 数据库会主动为主键字段设置主键索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL)--------------------惟一索引的创立语法---------------------- 形式一：间接创立索引CREATE UNIQUE INDEX index_name ON table_name (column_name);-- 形式二：批改表构造时增加索引ALTER TABLE table_name ADD UNIQUE INDEX index_name ON column_name;-- 形式三：创立表时间接增加索引-- 独自设置惟一索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL, UNIQUE INDEX id_dept_deptname (dept_name))-- 字段增加惟一束缚之后，数据库会主动为存在惟一束缚的列设置惟一索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) UNIQUE DEFAULT NULL)----------组合索引的创立语法------------ 创立表时间接增加索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL, manager_id INTEGER, INDEX idx_deptnameManagerid (dept_name,manager_id))-- 批改表构造时增加索引ALTER TABLE table_name ADD INDEX index_name (column_name,column_name);----------索引的查问删除语法------------ 查问索引SHOW INDEX FROM table_name;-- 删除索引DROP INDEX index_name ON table_name;

前言：这篇文章次要讲 explain 如何应用，还有 explain 各种参数概念，之后会讲优化一、Explain 用法模仿Mysql优化器是如何执行SQL查问语句的，从而晓得Mysql是如何解决你的SQL语句的。剖析你的查问语句或是表构造的性能瓶颈。 语法：Explain + SQL 语句; 如：Explain select * from user; 会生成如下 SQL 剖析后果，上面具体对每个字段进行详解 二、id是一组数字，代表多个表之间的查问程序，或者蕴含子句查问语句中的程序，id 总共分为三种状况，顺次详解 id 雷同，执行程序由上至下 id 不同，如果是子查问，id 号会递增，id 值越大优先级越高，越先被执行 id 雷同和不同的状况同时存在 三、select_typeselect_type 蕴含以下几种值 simpleprimarysubqueryderivedunionunion resultsimple简略的 select 查问，查问中不蕴含子查问或者 union 查问 primary如果 SQL 语句中蕴含任何子查问，那么子查问的最外层会被标记为 primary subquery在 select 或者 where 里蕴含了子查问，那么子查问就会被标记为 subQquery，同三.二同时呈现 derived在 from 中蕴含的子查问，会被标记为衍生查问，会把查问后果放到一个长期表中 union / union result如果有两个 select 查问语句，他们之间用 union 连起来查问，那么第二个 select 会被标记为 union，union 的后果被标记为 union result。它的 id 是为 null 的 四、table示意这一行的数据是哪张表的数据 五、typetype 是代表 MySQL 应用了哪种索引类型，不同的索引类型的查问效率也是不一样的，type 大抵有以下品种 ...

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最风行的关系型数据库管理系统之一，在 WEB 利用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。 明天咱们就来聊聊MySQL的索引。只管MySQL有许多长处，然而在海量数据的状况下，性能方面的体现还是会让人捉急，这时候就轮到MySQL的索引出场了。我会以抛出问题而后解决问题的形式来进行本次分享。比方：什么是索引？索引能够做什么？为什么应用索引能够提高效率？MySQL反对哪些索引类型？什么状况下应不建或少建索引？什么是联结索引？为什么说B+比B树更适宜理论利用中操作系统的文件索引和数据库索引？ 一、什么是索引？官网解释：索引(Index)是帮忙MySQL高效获取数据的数据结构。 艰深了解：索引是一种非凡的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们蕴含着对数据表里所有记录的援用指针。 二、索引能够做什么？首先，索引不是万能的，索引能够放慢数据检索操作，但会使数据批改操作变慢。每次批改数据记录，索引就必须刷新一次。为了在某种程度上补救这一缺点，许多 SQL 命令都有一个 DELAY_KEY_WRITE 项。这个选项的作用是临时禁止 MySQL 在该命令每插入一条新记录和每批改一条现有之后立即对索引进行刷新，对索引的刷新将等到全副记录插入/批改结束之后再进行。在须要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。 三、为什么应用数据索引能提高效率？数据索引的存储是有序的在有序的状况下，通过索引查问一个数据是无需遍历索引记录的极其状况下，数据索引的查问效率为二分法查问效率，趋近于 log2(N)四、MySQL反对哪些索引类型？咱们这里说的索引类型并不是指“主键索引”、“外键索引”这些，而是索引底层的数据结构。MySQL的索引数据结构反对以下两种： B-Tree 索引。B+树是一个均衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针互相链接，是有序的，如下图： Hash 索引。哈希索引就是采纳肯定的哈希算法，把键值换算成新的哈希值，检索时不须要相似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的，如下图所示： * 哈希索引的劣势：等值查问，哈希索引具备绝对优势（前提是：没有大量反复键值，如果大量反复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）* 哈希索引不实用的状况： * 不反对范畴查问 * 不反对索引实现排序 * 不反对联结索引的最左前缀匹配规定五、什么状况下应不建或少建索引？咱们都晓得什么时候应该应用索引，那么，什么时候不应该应用索引呢？咱们下面说到，索引并不是万能的，所以，索引必定也有不实用的场景。以下几个场景的时候，咱们应该尽量不建或者说少建索引： 表记录太少。常常插入、删除、批改的表。数据反复且散布均匀的表字段，如果一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的散布概率大概为50%，那么对这种表A字段建索引个别不会进步数据库的查问速度。常常和主字段一块查问但主字段索引值比拟多的表字段。六、什么是联结索引？联结索引是两个或更多个列上的索引。对于联结索引，MySQL反对从左到右的应用索引中的字段，一个查问能够只应用索引中的一部份，但只能是最左侧局部。例如索引是key index (a,b,c)，能够反对a 、 a,b 、 a,b,c 这3种组合进行查找，但不反对 b,c进行查找，当最左侧字段是常量援用时，索引就非常无效。 利用索引中的附加列，您能够放大搜寻的范畴，但应用一个具备两列的索引不同于应用两个独自的索引。复合索引的构造与电话簿相似，人名由姓和名形成，电话簿首先按姓氏对进行排序，而后按名字对有雷同姓氏的人进行排序。如果您晓得姓，电话簿将十分有用；如果您晓得姓和名，电话簿则更为有用，但如果您只晓得名不晓得姓，电话簿将没有用途。七、为什么说B+比B树更适宜理论利用中操作系统的文件索引和数据库索引？B+的磁盘读写代价更低。B+的外部结点并没有指向关键字具体信息的指针，因而其外部结点绝对B树更小。如果把所有同一外部结点的关键字寄存在同一盘块中，那么盘块所能包容的关键字数量也越多。一次性读入内存中的须要查找的关键字也就越多。相对来说IO读写次数也就升高了。B+-tree的查问效率更加稳固。因为非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。八、结语看到这里，你应该曾经把MySQL的索引常识都过了个大略，心愿本文能帮到你，happy hacking。接下来听首听首歌放松一下吧。真的爱你 BEYOND - BEYOND

原文链接：blog.ouyangsihai.cn >> MySQL的B+树索引的概念、使用、优化及使用场景 0 前言这篇文章不会讲解索引的基础知识，主要是关于MySQL数据库的B+树索引的相关原理，里面的一些知识都参考了MySQL技术内幕这本书，也算对于这些知识的总结。对于B树和B+树相关的知识，可以参考我的这篇博客：面试官问你B树和B+树，就把这篇文章丢给他 1 索引的管理索引有很多中类型：普通索引、唯一索引、主键索引、组合索引、全文索引，下面我们看看如何创建和删除下面这些类型的索引。 1.1 索引的创建方式索引的创建是可以在很多种情况下进行的。 直接创建索引CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length))[UNIQUE|FULLLTEXT]：表示可选择的索引类型，唯一索引还是全文索引，不加话就是普通索引。table_name：表的名称，表示为哪个表添加索引。column_name(length)：column_name是表的列名，length表示为这一列的前length行记录添加索引。 修改表结构的方式添加索引ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))创建表的时候同时创建索引CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , PRIMARY KEY (`id`), [UNIQUE|FULLLTEXT] INDEX index_name (title(length)))1.2 主键索引和组合索引创建的方式前面讲的都是普通索引、唯一索引和全文索引创建的方式，但是，主键索引和组合索引创建的方式却是有点不一样的，所以单独拿出来讲一下。 组合索引创建方式 创建表的时候同时创建索引CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , PRIMARY KEY (`id`), INDEX index_name(id,title))修改表结构的方式添加索引ALTER TABLE table_name ADD INDEX name_city_age (name,city,age); 主键索引创建方式主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。 ...

上一篇已经讲了索引的基本类型，这一篇主要介绍下如何选择更高效的索引类型。 独立的列现在有下面一张学生成绩表 CREATE TABLE `student` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(20) NOT NULL, `last_name` varchar(20) NOT NULL, `created_at` timestamp NOT NULL, `updated_at` timestamp NOT NULL, `score` int(3) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `created_at` (`created_at`) KEY `score` (`score`)) ENGINE=InnoDB DEFAULT CHARSET=utf8现在我们要根据学生成绩查询学生姓名，这是一个很简单的查询。select first_name,last_name from student where score=99;这条sql就使用到了索引score。但是我们通常会看到很多查询不恰当的使用到索引，最后就导致mysql没办法使用到索引。如果查询中的不是独立的，则Mysql不会使用到索引，独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。如select first_name,last_name from student where score+1=100;这个查询是不能使用到索引的。再如：select first_name from student where TO_DAYS(NOW())-TO_DAYS(created_at)>0;也是不能使用到索引的。 前缀索引有时候需要索引的列是很长的字符串，如果直接创建索引会使索引变的很大这样就变的比较慢了。改进方式现在能想到就是前面说到的哈希索引，但是有时候哈希索引是不适合的。这个时候就有了前缀索引：把列开始的部分字符串作为索引，这样可以大大的节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引选择性指：不重复的索引值和数据表总数的比值。索引的选择性越高，那么索引的查询效率越高。唯一索引的选择性最高为1，性能也是最好的。对于很长的VARCHAR，TEXT这样的列，如果要作为索引的话，那么必须使用前缀索引。那么怎么选择合适的前缀索引呢。诀窍在于要选择足够长的前缀以保证比较高的索引选择性，同时又不能太长，因为索引越短，索引空间越小。如：一张订单表，要为联系人手机号做前缀索引，这个适合需要分析多少长度的前缀索引,可以查询每个长度的 SELECT COUNT(DISTINCT LEFT(phone,3))/COUNT(*) AS pre3,COUNT(DISTINCT LEFT(phone,4))/COUNT(*) AS pre4,COUNT(DISTINCT LEFT(phone,5))/COUNT(*) AS pre5,COUNT(DISTINCT LEFT(phone,6))/COUNT(*) AS pre6,COUNT(DISTINCT LEFT(phone,7))/COUNT(*) AS pre7,COUNT(DISTINCT LEFT(phone,8))/COUNT(*) AS pre8 FROM orders;+--------+--------+--------+--------+--------+--------+| pre3 | pre4 | pre5 | pre6 | pre7 | pre8 |+--------+--------+--------+--------+--------+--------+| 0.0026 | 0.0216 | 0.1397 | 0.3274 | 0.4533 | 0.4533 |+--------+--------+--------+--------+--------+--------+1 row in set (0.10 sec)可以看到在长度为7的时候，选择性的提升已经很小了。这个时候，我们就可以考虑取7这个值了。当然这里只是一个举例，在实际场景中，手机号的长度完全可以直接作为普通索引的。前缀索引是比较小而且快，但是Mysql不能用前缀索引作为group by 和order by，也不能做覆盖扫描（所以查询必须回表）。 ...

索引对于良好的性能非常关键，尤其是在数据量越来越大的时候。恰当的索引对性能的帮助是非常巨大的，不恰当的索引不禁不能对性提升有帮助，当数据量达到一定级别的时候还可能造成性能的下降。所以了解索引对Mysql性能优化有着至关重要的作用。Mysql索引基本类型有 B-Tree,哈希索引,全文索引,空间数据索引(R-Tree)。其中B-Tree、哈希、全文索引是我们经常用到的。 B-Tree索引B-Tree索引是我们口中经常说的索引类型（有些存储引擎中使用的是B+Tree。如InnoDB）。每个引擎对于BTREE索引使用方式是不一样的。如 MyISAM引擎使用的是前置压缩技术，这样索引会变的很小。而InnoDB则是按照原有的数据格式来存储的。MyISAM索引是通过数据的物理位置来找到被索引的行，而InnoDB则是根据被索引的行的主键来找到被索引行的。B-Tree索引的所有值都是按顺序存储的，并且每个叶子节点到根节点的距离是相同的。下面给出一个简单的示意图 假设有下表： CREATE TABLE student( first_name varchar(20) not null, last_name varchar(20) not null, age tinyint(3) not null, created_at timestamp not null, key(first_name ,last_name));可以使用到B-Tree索引的查询 全值匹配 全值匹配指对索引中的所有列进行匹配。如查询姓名是 zhang san的人 select * from student where first_name='zhang' and last_name='san'; 这里使用了索引的第一列与第二列匹配最左前缀，如查询姓为张的人 select * from student where first_name='zhang' ;这里使用了索引的第一列匹配列前缀，也可以值匹配某一列的开头部分,如 select * from student where first_name='zha' ;这里使用了索引的第一列匹配范围值,如 select * from student where first_name>'bao' and first_name<'zhang';这样也会使用到索引的第一列只访问索引的查询，如果查询条件是 select first_name,last_name from student where first_name='zhang' ;那么查询就只会访问索引，而不会再去根据主键回表查询数据。这里需要注意的是；B-Tree 索引需要根据最左前缀查询，如果不是按照索引的最左列开始查询，那么是不会使用到索引的。例如:select * from student where last_name='san';select * from student where first_name like '%ha%'; 这样的sql是没办法命中索引的。对于第二条sql如果需要使用索引，那么应该改为 select * from student where first_name like 'ha%';哈希索引哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才会使用到索引，只有Memory引擎才支持哈希索引。假设有下表： ...

在数据库中，如果索引太多，应用程序的性能可能会受到影响，如果索引太少，又会对查询性能产生影响。所以，我们要追求两者的一个平衡点，足够多的索引带来查询性能提高，又不因为索引过多导致修改数据等操作时负载过高。文章会从，B+树索引，索引的分类，哈希索引，全文索引，这个几个方面讲解 B+树索引 索引的查找索引的插入索引的删除索引的分类 聚集索引辅助索引联合索引覆盖索引哈希索引 哈希算法自适应哈希索引全文索引 倒排索引全文检索索引缓存全文索引的一些限制InnoDB支持3种常见索引，我们接下来要详细讲解的就是 B+ 树索引，哈希索引，全文索引。 B+树索引1、B+树中的B不是代表的二叉（Binary） ，而是代表平衡（Balance），因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。 2、B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有的记录节点都是按照键值大小顺序存在同一层的叶子节点，由叶子节点指针进行相连。 3、B+树在数据库中的特点就是高扇出，因此在数据库中B+树的高度一般都在2~4层，这也就是说查找一个键值记录时，最多只需要2到4次IO,当前的机械硬盘每秒至少可以有100次IO，2~4次IO意味着查询时间只需要0.02~0.04秒。 4、B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找的键值所在行的页，然后数据库把页读到内存，再内存中进行查找，最后找到要查找的数据。 5、数据库中B+树索引可以分为，聚集索引和非聚集索引，但是不管是聚集索引还是非聚集索引，其内部都是B+树实现的，即高度是平衡的，叶子节点存放着所有的数据，聚集索引和非聚集索引不同的是，叶子节点是否存储的是一整行信息。每张表只能有一个聚集索引。 6、B+树的每个数据页（叶子节点）是通过一个双向链表进行链接，数据页上的数据的顺序是按照主键顺序存储的。 先来看一个B+树，其高度为2，每页可以放4条记录，扇出为5。 图：一颗高度为2的B+树 索引的查找B+树索引使用二分法查找，也称折半查找法，基本思想就是：将记录有序化（递增或递减）排列，在超找过程中采用跳跃式方式查找，既先以有序数列的中心点位置比较对象，如果要查找的元素小于该元素的中心点元素，则将待查找的元素缩小为左半部分，否则为右半部分，通过一次比较，将查找区间缩小一半。 如图所示，从有序列表中查找 48，只需要3步： 图：二分法查找 索引的插入B+树的查找速度很快，但是维护一颗平衡的B+树代价就是非常大的，通常来说，需要1次或者多次左旋右旋来保证插入后树的平衡性。 B+树的插入为了保持树的平衡，需要做大量的页（叶子节点）的拆分，页的存储基本都在磁盘，页的拆分意味着磁盘的操作，所以应该尽量减少页的拆分，在采用自增长ID，作为主键，会大量的减少页的拆分，提升的性能。 B+树 插入的三种情况 Leaf Page满Index Page满操作NoNo直接将记录插入叶子节点YesNo1、拆分Leaf Page 2、将中间的节点放入到Index Page中 3、小于中间节点的记录放左边4、大于或等于中间节点的记录放右边YesYes1、拆分Leaf Page 2、小于中间节点的记录放左边 3、大于或等于中间节点的记录放右边 4、拆分Index Page 5、小于中间节点的记录放左边6、大于中间节点的记录放右边7、中间节点放入上一层Index Page图：一颗高度为2的B+树 我们用实例来分析B+树的插入。 （1）我们插入28这个键值，发现当前Leaf Page和Index Page都没有满，我们直接插入就可以了。 （2）这次我们再插入一条70这个键值，这时原先的Leaf Page已经满了，但是Index Page还没有满，符合表（B+树 插入的三种情况）的第二种情况，这时插入Leaf Page后的情况为50、55、60、65、70。我们根据中间的值60拆分叶节点。将中间节点放入到Index Page中。 （3）因为图片显示的关系，这次我没有能在各叶节点加上双向链表指针。最后我们来插入记录95，这时符合表（B+树 插入的三种情况）讨论的第三种情况，即Leaf Page和Index Page都满了，这时需要做两次拆分。 可以看到，不管怎么变化，B+树总是会保持平衡。但是为了保持平衡，对于新插入的键值可能需要做大量的拆分页（split）操作，而B+树主要用于磁盘，因此页的拆分意味着磁盘的操作，应该在可能的情况下尽量减少页的拆分。因此，B+树提供了旋转（rotation）的功能。 索引的删除B+树使用填充因子（fill factor） 来控制树的删除变化，50%是填充因子可设的最小值，B+树的删除也同样必须保证删除后树的平衡性，删除的过程中会涉及，合并叶子节或兄弟节点，但是都是为了保持树的平衡。 索引的分类在了解B+树索引的本质和实现后，我们看看索引分为几类，聚集索引，辅助索引，联合索引，覆盖索引 聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点存储整张表的行记录数，也将聚集索引的叶子节点成为“数据页”，聚集索引的特性决定了表中的行记录数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表进行链接。 数据页只能按照一颗B+树进行排序，因此每张表只能有一个聚集索引，由于数据页定义了逻辑顺序，聚集索引能够很快的在数据页访问指针进行范围的查找数据。 ...

免费MySQL课程：阿里云大学——开发者课堂MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。 普通索引创建索引这是最基本的索引，它没有任何限制。它有以下几种创建方式： CREATE INDEX indexName ON mytable(username(length));如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。 修改表结构(添加索引) ALTER mytable ADD INDEX [indexName] ON (username(length))创建表的时候直接指定 CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)));删除索引的语法 DROP INDEX [indexName] ON mytable;唯一索引它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： 创建索引 CREATE UNIQUE INDEX indexName ON mytable(username(length))修改表结构 ALTER table mytable ADD UNIQUE [indexName] (username(length))创建表的时候直接指定 CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)));使用ALTER 命令添加和删除索引有四种方式来添加数据表的索引：ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 ...

这篇文章的知识点来自于极客时间专栏<<MySQL实战45讲>>，本文持续更新。 索引索引的目的：提高查询效率。 常见索引模型：哈希表、有序数组、搜索树哈希表：键 - 值(key - value)对。哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置哈希冲突(多个 key 值经过哈希函数的换算，会出现同一个值的情况)的处理办法：链表哈希表适用场景：只有等值查询的场景,比如 Memcached 及其他一些 NoSQL 引擎。 有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))有序数组查询效率高，更新效率低,往中间插入一个记录就必须得挪动后面所有的记录，成本太高。有序数组的适用场景：静态存储引擎。 二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子二叉搜索树：查询时间复杂度O(log(N))，更新时间复杂度O(log(N))数据库存储大多不适用二叉树，因为树高过高，会适用N叉树 MySQL表引擎InnoDB中的索引模型：B+Tree索引类型：主键索引、非主键索引主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引) 主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表),所以在应用中尽量使用主键查询。 一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。 自增主键使用自增主键的好处： 自增主键一般用整型做主键，则二级索引叶子节点只要 4 个字节；而如果使用业务字段做主键，比如字符串类型的身份证号，每个二级索引的叶子节点得占用约 20 个字节。插入数据时，自增主键能保证有序插入，避免了页分裂。从性能和存储空间方面考量，自增主键往往是更合理的选择。 使用业务字段做主键的使用场景： 只有一个索引该索引必须是唯一索引这就是典型的 KV 场景。由于没有其他索引，所以不用考虑其他索引的叶子节点大小的问题. 重建索引重建索引的目的：节省空间重建索引的办法：alter table T engine=InnoDB 联合索引覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度。 参考资料极客时间MySQL实战45讲

我们已经讲过了MySQL InnoDB索引原理和算法，这里来说下如何管理和使用B+树索引以及一些新的特性。 B+ 树索引的管理我们在InnoDB引擎中常用的索引基本都是B+ 树索引。 创建和删除索引它的创建和删除有两种方法： # 方式一：alter table, 此时index和key均可以，如果要求所有值均不重复，加上uniquealter table tbl_name add [unique] index|key index_name (index_col_name,...);alter table tbl_name drop index|key index_name;# 方式二：create/drop index, 此时只能用indexcreate index index_name on tbl_name (index_col_name,...);drop index index_name on tbl_name;修改索引MySQL没有提供修改索引的命令，我们一般先删掉索引，再重建同名索引达到修改的目标。 查看索引我们在查看数据表描述时可以看到都有哪些索引，有三种方法： # 方法一：查看创建表的语句mysql> show create table serviceHost;| Table | Create Table | t | CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(20) DEFAULT NULL, `b` varchar(20) DEFAULT NULL, `c` varchar(20) DEFAULT NULL, `d` varchar(20) DEFAULT NULL, `e` varchar(20) DEFAULT NULL, `f` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`), KEY `idx_b` (`b`), KEY `idex_cde` (`c`,`d`,`e`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |1 row in set (0.00 sec)可以看到该表中有4个索引，主键集合索引(id)，唯一辅助索引(a)，单列辅助索引(b)，组合辅助索引(c,d,e)。 ...

MySQL Online DDL导致全局锁表案例分析我这边遇到了什么问题?线上给某个表执行新增索引SQL, 然后整个数据CPU打到100%, 连接数暴增到极限, 最后导致所有访问数据库的应用都奔溃. SQL如下: ALTER TABLE `book` ADD INDEX `idx_sub_title` (`sub_title` ASC);能看到什么? '10063293', 'root', '10.0.0.1:35252', 'novel', 'Query', '50', 'Waiting for table metadata lock', 'ALTER TABLE `lemon_novel`.`book` \nADD INDEX `idx_sub_title` (`sub_title` ASC)''10094494', 'root', '172.16.2.112:42808', 'novel', 'Query', '31', 'Waiting for table metadata lock', 'SELECT \n book_trend.book_id AS book_id, 很奇怪, 这两边都在等"Waiting for table metadata lock" 反手查一下"Waiting for table metadata lock"是什么MySQL出现Waiting for table metadata lock的原因以及解决方法mysql: Waiting for table metadata lockHow do I find which transaction is causing a “Waiting for table metadata lock” state?MySQL:8.11.4 Metadata LockingMySQL:14.13.1 Online DDL Operations初步的一些结论看下来下面的一些结论: ...

欢迎关注公众号：【爱编程】如果有需要后台回复2019赠送1T的学习资料哦！！ 背景在这个快速发展的时代，时间变得越来越重要，也流逝得非常得快，有些人长大了，有些人却变老了。稍不留神，2019已经过完了三分之一。回首这四个月收获什么，懂得了什么？欢迎留言分享给我哟。 **言归正传：MySQL的查询怎么才能更快，更合理？除了加索引还有什么可以学习的呢？** 原理要想更好地学习某样东西，从其原理和运作方式入手更容易掌握。道理你们都懂，我就不废话了。 MySQL发送查询请求，到底做了什么工作？下图是MySQL查询执行流程图： 客户端发送一条查询给服务器。服务器先检查查询缓存，如果命中了缓存，则立刻返回查询在缓存中的结果。否则会进入下一个阶段。3.服务端进行SQL解析、预处理、再由优化器生成对应的执行计划。4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。5.将结果返回给客户端。 是什么导致MySQL查询变慢了？对于MySQL，最简单的衡量查询开销的三个指标如下： 响应时间扫描的行数返回的行数没有哪个指标能够完美地衡量查询的开销，但它们大致反映了MySQL在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。 查询慢的原因基本都是：我们的不合理操作导致查询的多余数据太多了。常见原因有以下： 1.查询不需要的记录。2.多表关联时返回全部列3.总是取出全部列常用优化技巧1.用索引最简单且见效最快的方式就是给你的条件加索引（主键索引，普通索引，唯一索引等）。注：索引是要另开辟一块空间存储的，所以不能不要钱滴都加索引。 2.关联子查询MySQL的子查询实现是非常糟糕的。比如下面的 SELECT * FROM book WHERE book_id IN (SELECT book_id FROM author WHERE author_id = 1)MySQL对IN()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有包含author_id 为1的book_id。 或许你想MySQL的运行时这样子的： SELECT GROUP_CONCAT(book_id) FROM author WHERE author_id = 1SELECT * FROM book WHERE book_id IN (1,21,3,45,656,766,213,123)但是，MySQL会将相关的外层表压到子查询中的，就是下面的样子： SELECT * FROM book WHERE EXISTS (SELECT * FROM author WHERE author_id = 1 AND book.book_id = author.book_id)原因：因为子查询需要book_id ，所以MySQL认为无法先执行这个子查询，而是先对book 进行全表扫描，然后再根据book_id进行子查询。具体可以EXPLAIN该SQL进行分析。 建议：1.使用左外连接（LEFT OUTER JOIN）代替子查询。 ...

从数据结构角度1、B+树索引(O(log(n)))：关于B+树索引，可以参考 MySQL索引背后的数据结构及算法原理2、hash索引：a 仅仅能满足"=",“IN"和”<=>“查询，不能使用范围查询b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引c 只有Memory存储引擎显示支持hash索引3、FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）4、R-Tree索引（用于对GIS数据类型创建SPATIAL索引）从物理存储角度1、聚集索引（clustered index）2、非聚集索引（non-clustered index）从逻辑角度1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值2、普通索引或者单列索引3、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合4、唯一索引或者非唯一索引5、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建CREATE TABLE table_name[col_name data type][unique|fulltext|spatial][index|key]index_name[asc|desc]1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；2、index和key为同义词，两者作用相同，用来指定创建索引3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；6、asc或desc指定升序或降序的索引值存储

索引是什么索引就像是一本书的目录索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。优点与缺点优点大大加快查询速度所有字段类型均可以设置索引缺点创建和维护索引需要时间，数据量越多，耗时越多索引占用存储空间，数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度使用原则和场景索引不是越多越好，需要视情况而定频繁更新的表应尽量少的索引频繁用于查询的字段进行构建索引数据量小的字段尽量不要使用索引，查询所有数据花费的时间比遍历索引的数据要短，索引将没有优化效果字段不同值少的字段尽量不要使用索引，如性别字段仅有男女两个不同值。索引分类注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换MEMORY/HEAP存储引擎：支持HASH和BTREE索引1. 单列索引一个索引只包含单个列，但一个表中可以有多个单列索引1.1. 普通索引MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。1.2. 唯一索引索引列中的值必须是唯一的，但是允许为空值1.3. 主键索引是一种特殊的唯一索引，不允许有空值2. 组合索引在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合3. 全文索引全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录4. 空间索引空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL索引创建和删除创建建表时创建CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|…] [INDEX|KEY] [索引名字] (字段名[length]) [ASC|DESC]示例：CREATE TABLE NewTable ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR (255) NOT NULL, name VARCHAR (255) NOT NULL, sex TINYINT NOT NULL DEFAULT 0, address VARCHAR (255) NULL, PRIMARY KEY (id), # 主键索引 INDEX name (name) USING BTREE, # 普通索引 UNIQUE INDEX username (username) USING BTREE # 唯一索引 INDEX u_n_a (username, name,address) USING BTREE # 组合索引);已存在表创建ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]示例：ALTER TABLE testADD PRIMARY KEY (id), # 主键索引ADD INDEX name (name) USING BTREE , # 普通索引ADD UNIQUE INDEX username (username) USING BTREE , # 唯一索引ADD INDEX u_n_a (username, name, address) USING BTREE ; # 组合索引删除索引ALTER TABLE 表名 DROP INDEX 索引名。示例：ALTER TABLE testDROP PRIMARY KEY,DROP INDEX username,DROP INDEX name,DROP INDEX u_n_a;更新索引先删后建ALTER TABLE testDROP INDEX username ,ADD UNIQUE INDEX username1 (username) USING BTREE ,DROP INDEX name ,ADD INDEX name2 (name) USING BTREE ,DROP INDEX u_n_a ,ADD INDEX u_a_n (username, address, name) USING BTREE ; ...

背景MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。本人从13年7月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升，遇到的问题千奇百怪，五花八门，匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。一个慢查询引发的思考select count() from task where status=2 and operator_id=20839 and operate_time>1371169729 and operate_time<1371174603 and type=2;系统使用者反应有一个功能越来越慢，于是工程师找到了上面的SQL。并且兴致冲冲的找到了我，“这个SQL需要优化，给我把每个字段都加上索引”。我很惊讶，问道：“为什么需要每个字段都加上索引？”“把查询的字段都加上索引会更快”，工程师信心满满。“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以operate_time需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。多数情况下，我们知道索引能够提高查询效率，但应该如何建立索引？索引的顺序如何？许多人却只知道大概。其实理解这些概念并不难，而且索引的原理远没有想象的那么复杂。MySQL索引原理索引目的索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？索引原理除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。磁盘IO与预读前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。索引的数据结构前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。详解b+树如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。b+树的查找过程如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。b+树性质1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。慢查询优化关于MySQL索引原理是比较枯燥的东西，大家只需要有一个感性的认识，并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询，了解完索引原理之后，大家是不是有什么想法呢？先总结一下索引的几大基本原则：建索引的几大原则1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count()，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。回到开始的慢查询根据最左匹配原则，最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引；其中status、operator_id、type的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析；比如还有如下查询：select * from task where status = 0 and type = 12 limit 10;select count() from task where status = 0 ;那么索引建立成(status,type,operator_id,operate_time)就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则查询优化神器 - explain命令关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。慢查询优化基本步骤0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）3.order by limit 形式的sql语句让排序的表优先查4.了解业务方使用场景5.加索引时参照建索引的几大原则6.观察结果，不符合预期继续从0分析几个慢查询案例下面几个例子详细解释了如何分析和优化慢查询。复杂语句写法很多情况下，我们写SQL只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对mysql的执行计划和索引原则有非常清楚的认识，请看下面的语句：select distinct cert.emp_id from cm_log cl inner join ( select emp.id as emp_id, emp_cert.id as cert_id from employee emp left join emp_certificate emp_cert on emp.id = emp_cert.emp_id where emp.is_deleted=0 ) cert on ( cl.ref_table=‘Employee’ and cl.ref_oid= cert.emp_id ) or ( cl.ref_table=‘EmpCertificate’ and cl.ref_oid= cert.cert_id ) where cl.last_upd_date >=‘2013-11-07 15:03:00’ and cl.last_upd_date<=‘2013-11-08 16:00:00’;0.先运行一下，53条记录 1.87秒，又没有用聚合语句，比较慢53 rows in set (1.87 sec)1.explain+—-+————-+————+——-+———————————+———————–+———+——————-+——-+——————————–+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+————+——-+———————————+———————–+———+——————-+——-+——————————–+| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary || 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer || 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where || 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |+—-+————-+————+——-+———————————+———————–+———+——————-+——-+——————————–+简述一下执行计划，首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录；然后查表扫描了63727条记录，分为两部分，derived表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表，并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据，首先全表扫描employee表13317条记录，然后根据索引emp_certificate_empid关联emp_certificate表，rows = 1表示，每个关联都只锁定了一条记录，效率比较高。获得后，再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据，返回的数据绝大部分cm_log都用不到，因为cm_log只锁定了379条记录。如何优化呢？可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢？仔细分析语句不难发现，其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。优化过的语句如下：select emp.id from cm_log cl inner join employee emp on cl.ref_table = ‘Employee’ and cl.ref_oid = emp.id where cl.last_upd_date >=‘2013-11-07 15:03:00’ and cl.last_upd_date<=‘2013-11-08 16:00:00’ and emp.is_deleted = 0 unionselect emp.id from cm_log cl inner join emp_certificate ec on cl.ref_table = ‘EmpCertificate’ and cl.ref_oid = ec.id inner join employee emp on emp.id = ec.emp_id where cl.last_upd_date >=‘2013-11-07 15:03:00’ and cl.last_upd_date<=‘2013-11-08 16:00:00’ and emp.is_deleted = 04.不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致5.现有索引可以满足，不需要建索引6.用改造后的语句实验一下，只需要10ms 降低了近200倍！+—-+————–+————+——–+———————————+——————-+———+———————–+——+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————–+————+——–+———————————+——————-+———+———————–+——+————-+| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where || 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where || 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where || 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | || 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where || NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |+—-+————–+————+——–+———————————+——————-+———+———————–+——+————-+53 rows in set (0.01 sec)明确应用场景举这个例子的目的在于颠覆我们对列的区分度的认知，一般上我们认为区分度越高的列，越容易锁定更少的记录，但在一些特殊的情况下，这种理论是有局限性的。select * from stage_poi sp where sp.accurate_result=1 and ( sp.sync_status=0 or sp.sync_status=2 or sp.sync_status=4 );0.先看看运行多长时间,951条数据6.22秒，真的很慢。951 rows in set (6.22 sec)1.先explain，rows达到了361万，type = ALL表明是全表扫描。+—-+————-+——-+——+—————+——+———+——+———+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——-+——+—————+——+———+——+———+————-+| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where |+—-+————-+——-+——+—————+——+———+——+———+————-+2.所有字段都应用查询返回记录数，因为是单表查询 0已经做过了951条。3.让explain的rows 尽量逼近951。看一下accurate_result = 1的记录数：select count(),accurate_result from stage_poi group by accurate_result;+———-+—————–+| count() | accurate_result |+———-+—————–+| 1023 | -1 || 2114655 | 0 || 972815 | 1 |+———-+—————–+我们看到accurate_result这个字段的区分度非常低，整个表只有-1,0,1三个值，加上索引也无法锁定特别少量的数据。再看一下sync_status字段的情况：select count(),sync_status from stage_poi group by sync_status;+———-+————-+| count() | sync_status |+———-+————-+| 3080 | 0 || 3085413 | 3 |+———-+————-+同样的区分度也很低，根据理论，也不适合建立索引。问题分析到这，好像得出了这个表无法优化的结论，两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当sync_status 0、3分布的很平均，那么锁定记录也是百万级别的。4.找业务方去沟通，看看使用场景。业务方是这么来使用这个SQL语句的，每隔五分钟会扫描符合条件的数据，处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多，1000个左右。了解了业务方的使用场景后，优化这个SQL就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。5.根据建立索引规则，使用如下语句建立索引alter table stage_poi add index idx_acc_status(accurate_result,sync_status);6.观察预期结果,发现只需要200ms，快了30多倍。952 rows in set (0.20 sec)我们再来回顾一下分析问题的过程，单表查询相对来说比较好优化，大部分时候只需要把where条件里面的字段依照规则加上索引就好，如果只是这种“无脑”优化的话，显然一些区分度非常低的列，不应该加索引的列也会被加上索引，这样会对插入、更新性能造成严重的影响，同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键，我们只有知道这个业务场景，才能更好地辅助我们更好的分析和优化查询语句。无法优化的语句select c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_id from contact c inner join contact_branch cb on c.id = cb.contact_id inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 10802 and oei.org_category = - 1 order by c.created_time desc limit 0 , 10;还是几个步骤。0.先看语句运行多长时间，10条记录用了13秒，已经不可忍受。10 rows in set (13.06 sec)1.explain+—-+————-+——-+——–+————————————-+————————-+———+————————–+——+———————————————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——-+——–+————————————-+————————-+———+————————–+——+———————————————-+| 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using filesort || 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index || 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | || 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | |+—-+————-+——-+——–+————————————-+————————-+———+————————–+——+———————————————-+从执行计划上看，mysql先查org_emp_info表扫描8849记录，再用索引idx_userid_status关联branch_user表，再用索引idx_branch_id关联contact_branch表，最后主键关联contact表。rows返回的都非常少，看不到有什么异常情况。我们在看一下语句，发现后面有order by + limit组合，会不会是排序量太大搞的？于是我们简化SQL，去掉后面的order by 和 limit，看看到底用了多少记录来排序。select count()from contact c inner join contact_branch cb on c.id = cb.contact_id inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 10802 and oei.org_category = - 1 +———-+| count(*) |+———-+| 778878 |+———-+1 row in set (5.19 sec)发现排序之前居然锁定了778878条记录，如果针对70万的结果集排序，将是灾难性的，怪不得这么慢，那我们能不能换个思路，先根据contact的created_time排序，再来join会不会比较快呢？于是改造成下面的语句，也可以用straight_join来优化：selectc.id,c.name,c.position,c.sex,c.phone,c.office_phone,c.feature_info,c.birthday,c.creator_id,c.is_keyperson,c.giveup_reason,c.status,c.data_source,from_unixtime(c.created_time) as created_time,from_unixtime(c.last_modified) as last_modified,c.last_modified_user_idfromcontact cwhereexists (select1fromcontact_branch cbinner joinbranch_user buon cb.branch_id = bu.branch_idand bu.status in (1,2)inner joinorg_emp_info oeion oei.data_id = bu.user_idand oei.node_left >= 2875and oei.node_right <= 10802and oei.org_category = - 1wherec.id = cb.contact_id)order byc.created_time desc limit 0 ,10;验证一下效果 预计在1ms内，提升了13000多倍！10 rows in set (0.00 sec)本以为至此大工告成，但我们在前面的分析中漏了一个细节，先排序再join和先join再排序理论上开销是一样的，为何提升这么多是因为有一个limit！大致执行过程是：mysql先按索引排序得到前10条记录，然后再去join过滤，当发现不够10条的时候，再次去10条，再次join，这显然在内层join过滤的数据非常多的时候，将是灾难的，极端情况，内层一条数据都找不到，mysql还傻乎乎的每次取10条，几乎遍历了这个数据表！用不同参数的SQL试验下：select sql_no_cache c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_id from contact c where exists ( select 1 from contact_branch cb inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 2875 and oei.org_category = - 1 where c.id = cb.contact_id ) order by c.created_time desc limit 0 , 10;Empty set (2 min 18.99 sec)2 min 18.99 sec！比之前的情况还糟糕很多。由于mysql的nested loop机制，遇到这种情况，基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。通过这个例子我们可以看到，并不是所有语句都能优化，而往往我们优化时，由于SQL用例回归时落掉一些极端情况，会造成比原来还严重的后果。所以，第一：不要指望所有语句都能通过SQL优化，第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况。慢查询的案例就分析到这儿，以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行，涉及到16个表join的“垃圾SQL”，也遇到过线上线下数据库差异导致应用直接被慢查询拖死，也遇到过varchar等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。写在后面的话本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现，任何数据库层面的优化都抵不上应用系统的优化，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”参考文献：1.《高性能MySQL》2.《数据结构与算法分析》 ...