MySQL的数据是如何存储的？聚簇索引我们先建下表CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'学号',`name`varchar(10)NOTNULLCOMMENT'学名',`age`int(11)NOTNULLCOMMENT'学生age',PRIMARYKEY(`id`),KEY`idx_name`(`name`))ENGINE=InnoDB;插入如下sqlinsertintostudent(`name`,`age`)value('a',10);insertintostudent(`name`,`age`)value('c',12);insertintostudent(`name`,`age`)value('b',9);insertintostudent(`name`,`age`)value('d',15);insertintostudent(`name`,`age`)value('h',17);insertintostudent(`name`,`age`)value('l',13);insertintostudent(`name`,`age`)value('k',12);insertintostudent(`name`,`age`)value('x',9);数据如下：mysql按页存储数据，每页大小为16k。在MySQL中，可以执行如下语句，查看一个pagesize的结果showglobalstatuslike'innodb_page_size'为16384，即16kb。在InnoDB存储引擎中，数据是以主键为索引来组织的。记录按照页面中主键从小到大的顺序，以单向链表的形式连接在一起。可能有朋友会问，建表的时候不指定主键怎么办？如果建表时没有显示定义的主键，InnoDB存储引擎会选择或创建如下主键。首先判断表中是否存在非空唯一索引。如果是这样，该列就是主键。如果有多个非空唯一索引，InnoDB存储引擎会选择建表时定义的第一个非空唯一索引作为主键。如果不满足上述条件，InnoDB存储引擎将自动创建一个6字节的指针作为索引页和页以双向链表的形式链接在一起。并且下一个数据页中用户记录的主键值必须大于上一个数据页中用户记录的主键值假设一个页面只能存储3条数据，数据存储结构如下.可见，当我们要查询一条数据或插入一条数据时，需要从第一页开始，依次遍历每一页的链表，效率不高。我们可以为这个页面做一个目录，保存主键和页码的映射关系，根据二分法快速找到数据所在的页面。但是这样做的前提是映射关系需要保存在一个连续的空间中，比如数组。如果这样做，将会出现以下问题。随着数据的增加，目录所需要的连续空间越来越大，这是不现实的。当一个页面的所有数据被删除时，对应的目录项也将被删除。以下目录项必须前移，成本太高。我们可以把目录数据放在一个类似于用户数据的结构中，如下所示。商品有2列，主键和页码。当数据很多的时候，目录项肯定也很多。毕竟一个page的大小是16k。我们可以为数据创建多个目录项，然后在目录项的基础上创建目录项。B+树也是聚簇索引，即数据和索引在一起。叶子节点存储所有列值以InnoDB的一个整型字段索引为例，这个N差不多是1200。当这棵树的高度为4时，它可以存储1200个3的值，已经是17亿了。考虑到树根处的数据块一直在内存中，在一个10亿行的表上的整数字段上的索引最多需要3次磁盘访问才能查找一个值。事实上，树的第二层很有可能在内存中，所以平均访问磁盘的次数就更少了。《MySQL实战45讲》非聚集索引聚集索引和非聚集索引很相似，区别如下聚集索引的叶子节点的值是所有列的值非聚集的叶子节点的值-聚簇索引是索引列+主键当我们查询姓名为h(学号、姓名、年龄)的用户信息时，由于索引是建立在姓名之上的，首先从姓名非-中找到对应的主键id聚簇索引，然后根据主键id从聚簇索引中找到对应的记录。从非聚簇索引中找到对应的主键值，然后在聚簇索引上查找对应记录的过程就是返回表联合索引/索引覆盖。假设teacher表定义如下，在name和age列上创建联合索引CREATETABLE`teacher`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'教师编号',`name`varchar(10)NOTNULLCOMMENT'教师姓名',`age`int(11)NOTNULLCOMMENT'老师的年龄',`ismale`tinyint(3)NOTNULLCOMMENT'是否男性',PRIMARYKEY(`id`),KEY`idx_name_age`(`name`,`age`))引擎=InnoDB；插入以下SQLinsertintoteeacher(`name`,`age`,`ismale`)value('aa',10,1);insertintoteeacher(`name`,`age`,`ismale`)value('dd',12,0);insertintoteeacher(`name`,`age`,`ismale`)value('cb',9,1);insertintoteeacher(`name`,`age`,`ismale`)value('cb',15,1);insertintoteeacher(`name`,`age`,`ismale`)value('bc',17,0);insertintoteeacher(`name`,`age`,`ismale`)value('bb',15,1);insertintoteacher(`name`,`age`,`ismale`)value('dd',15,1);insertintoteacher(`name`,`age`,`ismale`)value('dd',12,0);为姓名和年龄列创建联合索引。目录页由姓名列、年龄列和页码组成。作品。目录会先按姓名列排序，姓名列相同时按年龄列排序。数据页由三部分组成：姓名列、年龄列、主键值。同样，数据页会先按姓名列排序，姓名列相同时按年龄列排序。执行下面的语句时，会有一个回表的过程select*fromstudentwherename='aa';执行下面语句时，不会有回表的过程selectname,agefromstudentwherename='aa';为什么不用回桌子？因为idx_name_age索引的叶子节点存储的值是主键值、name值和age值，所以不需要回表就可以从idx_name_age索引中获取需要的列值，即，指数覆盖率。仔细看看联合索引的图，你就基本能明白为什么不满足最左前缀原则的索引会失效了？索引下推执行如下语句时select*fromstudentwherenamelike'Zhang%'andage=10andismale=1;5.6版本之前的执行流程如下，从idx_name_age索引上查找对应的主键值，然后回表查找对应的行，判断其他字段的值是否满足条件自《MySQL实战45讲》5.6引入了索引下推优化，在遍历索引的过程中可以对索引中包含的字段进行判断，直接过滤掉不符合条件的数据，减少索引的数量次返回表。下图来自《MySQL实战45讲》最左前缀的原则。查询的列与复合索引的列的顺序相同。查询不跨列构建数据，如下所示，其中联合索引建立在名称、地址和国家。CREATETABLE`people`(`name`varchar(50)NOTNULL,`address`varchar(50)NOTNULL,`country`varchar(50)NOTNULL,KEY`idx_name_addr_country`(`name`,`address`,`country`))ENGINE=InnoDBDEFAULTCHARSET=utf8;举几个例子，下面涉及一些explain相关的知识，后面会开长篇文章介绍。例1：说明select*frompeoplewherename="jack"andaddress="beijing"andcountry="china"type为ref，key_len为456=(50*3+2)*3，联合索引所有列使用示例2说明select*frompeoplewherename="jack"类型为ref，key_len为152=50*3+2，联合索引只使用name列。例3explainselect*frompeoplewhereaddress="beijing"typeisindex，表示查询时扫描整个索引。它不会加快查找速度。假设有如下联合索引键idx_a_b_c(a,b,c)SQL是否使用索引wherea=xandb=xandc=xiswherea=xandb=x是的，部分索引wherea=x是的，partialindexwhereb=x不是，不包括最左边的列名whereb=xandc=x不是，不包括最左边的列名如果你仔细阅读前面的联合索引是如何存储的，那么你必须把能看懂是否使用索引的介绍目录页按照abc列的顺序升序排列。先按a列排序，如果a列相同，则按b列排序，如果b列相同，则按c列排序。所以查询列值abc，那么就可以用这个排序规则，也就是会用到索引。如果只检查列值b，就不能使用这个排序规则，所以要遍历所有的记录来加快排序。最左前缀原则不仅用在查询中，也用在排序中。在MySQL中，生成有序结果集有两种方式：通过有序索引顺序扫描直接返回有序数据Filesort排序，对返回数据进行排序因为索引的结构是B+树，索引中的数据按照一定的顺序排序顺序排列，所以如果排序查询中可以使用索引，就可以避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Usingindex。凡是不直接通过索引返回排序结果的操作都是Filesort排序，即进行了额外的排序操作。当EXPLAIN分析查询时，Extra显示为Usingfilesort。当出现Usingfilesort时，性能损失较大。所以尽量避免Usingfilesort，先举2个例子，然后总结explainselect*frompeopleorderbynameExtra列只有Usingindex，即按照索引顺序扫描explainselect*frompeopleorderbyaddressInsertpicturedescriptionhereExtracolumnhasUsingfilesort总结：假设有如下联合索引，keyidx_a_b_c(a,b,c)orderby可以使用索引排序orderbyaorderbya,borderbya,b,corderbyadesc,bdesc,cdescwherea=constorderbyb,cwherea=constandb=constorderbycwherea=constandb>constorderbyb,corderby不能使用索引排序orderbyborderbycorderbyb,corderbyaasc,bdesc,cdesc//排序不一致whereeg=constorderbyb,c//丢失了a索引wherea=constorderbyc//丢失了b索引wherea=constorderbya,d//d不属于indexwhereain(...)orderbyb,c//rangequery原因不用解释了，相信大家一定明白联合索引索引覆盖的好处，减少了很多返回表的操作im证明查询效率，索引下推。索引列越多，通过索引过滤的数据就越少。对于一个有1000W条数据的表，有如下sql:select*fromtablewherecol1=1andcol2=2andcol3=3，假设每个条件都能过滤掉10%的数据，如果只有一个单值索引，然后通过这个索引可以筛选出1000w10%=100w条数据，然后回表从这100w条数据中找到满足col2=2和col3=3的数据；如果是联合索引，通过索引%=1w过滤掉1000w*10%*10%*10，效率提升可想而知！为什么索引会失败？当有人问我索引在什么条件下会失效时，我会背很多规则不对索引列进行操作或者使用函数导致模糊查询索引不会被使用，比如像%lee负条件索引不会使用索引，建议使用in。否定条件包括：!=、<>、notin、notexists、notlike等。索引按照一定的规则进行排序。如果在索引列上使用函数，或者像%li，具体取值未知，如何加快B+树上的查询速度？本文转载自微信公众号“Java知堂”，可通过以下二维码关注。转载本文请联系Java石塘公众号。
