本帖最后由 HW Developer 于 2017-9-7 09:54 编辑1、 引言数据库几乎是所有的系统都离不开的，对于一般的小型应用来说，很少有开发人员会在数据库上花费太多的精力；但是在实际业务中我们却发现，在一个快速增长的web系统中，最先成为性能瓶颈的往往就是数据库，有时候我们不得不在数据库的优化上花费大量的时间。在数据库的诸多优化手段中，索引的正确使用和优化是非常关键的一部分；下面将以MySQL数据库为例，系统的介绍一下索引的一般结构和优化方法；虽然在不同数据库下，细节可能会有些差异，但是道理都是通用的。2、 索引概述2.1 索引的类型 数据库中的索引类型有多种划分方式；按照其中所包含的字段数量，那么索引可分为单索引和组合索引（又叫复合索引）两种；单索引是指只包含单个字段的索引，组合索引顾名思义就是多个字段组合在一起的索引；如果按照索引中所包含的字段类型来分，则可以分为主键索引和辅助索引；主键索引当然就是在主键上创建的索引，辅助索引则是在主键以外的其他字段上创建的索引；如果按照索引节点与物理记录的对应方式来分，则可分为**索引和非**索引，前者是指索引节点中直接包含了数据记录的索引方式，也即是说，整张表的记录顺序都是按照索引结构来组织的；而后者的索引节点中，只包含一个指向物理记录的指针（地址）；在MySQL中，不同的存储引擎索引结构也不太一样；比如MyISAM存储引擎中，不管是主键索引还是辅助索引，都属于非**索引；而在Innodb引擎中，主键索引属于**索引，非主键索引则采用了非**索引的方式，也就是二级索引。后面将会详细介绍MySQL中索引的存储结构和查找原理；2.2 索引的实现 常用的数据查找方式有三种：顺序查找、Hash查找和Tree查找；这三种类型中，顺序查找最简单，但是速度也最慢，在大数据量下无法使用；而hash查找虽然快速，而且无论数据量多大，只要算法合适，基本上可以做到O(n)复杂度，其中n=数据条数/冲突域长度；但是它有一个最大的缺点就是不支持范围查找；这在关系型数据库的sql规范中是不能接受的；如果使用树型结构，则支持遍历，支持范围查找；而对于二叉树来讲，其效率可做到log(n)，而且性能也不会随着数据量的快速增长而下降太多，是比较理想的数据索引结构。2.3 索引的作用方式 在不使用索引时，系统会对整张表的数据进行扫描；一般是通过主键进行遍历（有的表可能连主键都没有，则会按照记录存放的物理顺序遍历），取出每一条记录，然后判断对应的字段值是否满足需求，如果满足，则添加到结果集里；如果不满足，则继续扫描后续记录；在应用了索引的情况下，如果查询条件只有一个时，情况很简单，系统会去扫描对应索引，找到目标记录（和通过主键查找差不多，主要的区别就是索引上可能没有唯一性约束，所以在找到第一条记录后，还需要继续向后取出链上符合条件的其他记录）。但是当查询条件多余一个时，情况会稍微复杂一点；下面我们以包含两个查询条件的情况进行说明；假如我们在一张名为test的表上执行以下查询语句：select * from test where a=1 and b=2;这时候可能的查询方案共有三种：1. 直接取出满足a=1和b=2的记录；2. 同时取出a=1和b=2的记录，然后二者进行merge；3. 先取出a=1的记录，然后在这些记录里筛选出b=2的记录；4. 先取出b=2的记录，然后在这些记录里筛选出a=1记录；其中3和4本质上是一样的，只不过顺序不同，这里单独列出来只不过想强调一下其在不同索引情况下的区别；很明显，以上方案中1的效率是最高的，但是条件也比较苛刻，只有当建立了a、b上的组合索引时，才可能采取1的方案；对于2来说，一般只有当a和b两个字段上分别都建有索引时才有可能会这样执行，否则显而易见，其效率将会是很低的；如果只在a字段或b字段建了索引，那么很显然适合用3或4的方案；那么我们不妨想一想，如果在a和b字段都建了单索引的情况下，会不会也可能走3或者4的方案呢？答案是肯定的；这种情况主要取决于这两个字段的区分度（数据库中一般会有个Cardinality的概念，其原意是指实体关系模型中的1:N或者N:N这样的映射关系，这里可以简单地理解为其代表着数据的区分度）；所谓的字段区分度是指，表中所有记录的这个字段上一共会有多少不同的值，也即用这个字段来区分不同记录的程度如何；如果这个字段的不同取值越多，说明区分度越高，那么在这个字段上建索引效果也越好，反之就越差。字段区分度对查询方式的具体影响，后边会详细介绍。 3、 MySQL索引具体结构3.1 引子 MySQL的索引都是由底层存储引擎来管理的，每种引擎的具体实现也不太一样，但基本就是b-tree和b+tree两种；下面主要介绍一下我们日常熟悉的MyISAM引擎和Innodb引擎的索引结构。首先我们模拟一张表，并以这张表为例来介绍索引的实际结构；该表名为user，主要包含了用户的一些个人基本信息，如id、姓名、年龄、联系方式等，具体结构如下表所示：表名user数据量100w功能描述主要存储用户的基本信息，如id、姓名、年龄、联系方式等；字段名（新增）数据类型（精度范围）空/非空缺省值字段含义idBIGINT(20)N用户idnameVARCHAR(32)N姓名genderTINYINT(4)N性别ageINT(8)N年龄telVARCHAR(16)N联系电话gmt_createDATETIMEN创建时间3.2 MyISAM的索引实现 MyISAM引擎使用的B+Tree的结构来构造索引。B+Tree的特点就是一条查询路径上所有节点的数据都会同时保存在叶节点上，同时每个叶节点间会以指针相连，这样也可以通过叶节点向后顺序遍历后续所有节点，为范围查询提供了方便；在MyISAM的索引中，叶节点的data域存放的是数据记录的地址。下图模拟了user表中age字段上的单索引结构（这张图只给出索引的原理结构，并不代表实际存储的物理结构）： 在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，都如上图所示；只是主索引要求key是唯一的，而辅助索引的key可以重复；根据前面的定义，MyISAM的索引属于非**索引。由于在索引节点中仅仅保存了数据记录的指针（地址），所以在搜索的时候，系统首先按照B+Tree的检索的算法搜索对应的key的节点，如果该节点存在，则取出其data域的值，然后以该值为地址，再到表的数据存储区读取相应记录。 3.3 Innodb的索引实现 Innodb同样使用B+Tree作为索引结构，但具体实现方式却与MyISAM有一些不同。首先就是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，在树的每个叶节点保存对应的数据记录，而仅仅是一个记录地址。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。如下图所示： 从图中可以看到树的每个叶节点都包含了完整的数据记录，这种索引方式就是我们前面讲的**索引；因为InnoDB的数据文件本身要按主键排序，所以在创建InnoDB表时必须要有主键，如果没有显式指定，那么系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则系统自动为该表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。还有一个与MyISAM索引的不同点就是，在InnoDB的辅助索引叶节点的data域中，存储的是对应记录的主键id而不是记录的地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。所以在Innodb引擎中，同样是user表age字段上的索引，其结构将如下图所示： Innodb中主键索引的这种实现方式使得按主键的查询十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后再到主键索引中查询对应记录。了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主健索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在**新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，效率非常低，而使用自增字段作为主键则是一个很好的选择。 4、 MySQL索引实例分析4.1 准备工作4.1.1 explain命令 Explain命令是MySQL提供的一个帮助查询优化器制定sql执行计划的功能，其他很多的数据库也都各自提供了类似的命令；在实际开发中，我们可以事先通过explain来分析我们的sql在不同的索引结构下是如何执行的；也可以帮助开发人员在线上环境分析慢查询，从而对sql和索引进行优化； 使用explain命令会输出类似下面的结果：这里先解释下explain解析结果中各个字段的属性含义；id代表该条语句在整个语句解析中的顺序，当存在嵌套查询时，explain解析出来的结果是多条的；select_type代表查询类型，如子查询、UNION等；table顾名思义指的是表的名称；type指的是查找数据的方式，如全表扫描、读常量、索引扫描等；possible_keys代表该条语句可以使用的索引；key代表实际使用索引；key_len代表使用的索引长度，对于组合索引，可能只是用到了其中的部分字段；ref指的是引用类型，rows是解析器估算的最多需要扫描的记录条数；extra用来说明一个额外信息，上边这条语句中extra只有一个Using where，不过别急，下边我们就会看到更多的。后面我们就使用explain命令对一些具体的sql语句进行分析，来说明不同的索引结构和sql写法会产生什么样的执行效果。4.1.2 数据准备 shop_group_item表是这次现网系统中使用的一张表，我们后面的分析都是基于该表进行的，所使用的数据都是从线上导出的真实数据，部分sql也是目前生产环境中正在使用的；下表是shop_group_item的schema结构：表名shop_group_item所在数据库1000w功能描述主要存储用户加入到Group中的商品信息。字段名（新增）数据类型（精度范围）空/非空缺省值字段含义idBIGINT(20)N主键idgroup_idBIGINT(20)NGroup iduser_idBIGINT(20)N用户iditem_idBIGINT(20)N商品iditem_titleVARCHAR(120)N商品标题item_picBIGINT(120)N商品图片item_pic_widthINTEGER(5)Y商品图片长度item_pic_heightINTEGER(5)Y商品图片宽度item_priceBIGINT(20)N商品价格contentVARCHAR(1024)Y最新评论extra_propsVARCHAR(1024)Y扩展属性gmt_createDATETIMEN记录修改时间gmt_modifiedDATETIMEN记录修改时间目前该表的线上索引结构如下图所示（这里只列出了下文主要涉及到的一些字段，省略了部分不必要字段）： 我们可以看出，shop_group_item这张表一共有三个索引，一个是PRIMARY，也就是主键索引；剩下两个都是组合索引，idx_group_id_item_id这个包含group_id，item_id和status三个字段，另个一idx_group_id_uid_status包含了group_id，user_id，status，gmt_create四个字段，这里要特别注意一下seq_in_index这个字段，它表明了对应column在索引中的顺序，这直接决定着后边查询时的索引应用效果；因为线上的sql语句数量较少，类型也基本确定，所以shop_group_item表只建了一个主键索引和两个组合索引；下面为了方便对比测试，我们先将两个组合索引drop掉，再手工为shop_group_item表添加几条单索引（用于测试的记录数量为10w）； 我们分别添加了idx_group_id（包含group_id字段）、idx_user_id（包含user_id字段）、idx_status（包含status字段）三条单索引；最终，shop_group_item这张表上的索引情况如下（只展示了主要字段）： 下面，我们就通过不同sql对应的执行计划来分析一下MySQL索引的一些特性，以及为什么会有这样的特性；4.2 单索引查询4.2.1 无索引查询 首先我们看下用一个没有任何索引的字段进行查询；explain select * from shop_group_item whereitem_price=444; 从sql解析中我们可以看出，MySQL无法为这条语句应用任何索引，只能去走全表扫描，遍历所有记录，找出所有符合要求的。4.2.2 主键查询 作为对比，我们尝试下通过主键id直接查询记录；下面是我们线上正在使用的一条sql：explain select * from shop_group_item where id=12003; 我们的where条件中只有一个id字段，所以MySQL理所当然的使用了主键索引；这种情况下效率是非常高的，语句执行几乎没有花费什么时间，其执行结果如下图：4.2.3 非主键查询 下面我们来看下非主键索引的效率情况：explain select * from shop_group_item wheregroup_id=12003; 这里我们看到，该sql和只用主键id查询的解析结果基本一样，用时也很少；唯一的一点差别就是type值不一样，这是因为idx_group_id这个索引没有像主键的那样的唯一性约束，系统无法判断group_id=12003的记录的准确数量，所以无法标志成const。4.2.4 多条件索引合并 我们再来看下另一条线上语句的执行情况，里边涉及到了多条件查询；explain select * from shop_group_item wheregroup_id=144030 and user_id=157860040;由于结果全部输出太长，所以这里只贴出我们关心的部分： 我们可以看到，在只对group_id和user_id分别建了单索引的情况下，数据库选择了索引合并的方式，即前文我们提到的方案2；该sql解析的Extra字段如下： 我们可以看到，数据库是通过intersect的方式去处理索引结果**并的；4.2.5 多条件非索引合并 对于上例中多条件查询的情况，数据库是不一定会按照索引合并的方式来处理；explain select * from shop_group_item wheregroup_id=144030 and status=1; 这里我们可以看出，虽然在group_id和status两个字段上都建有单索引，但是status上的索引并没有被选用，而只应用了group_id上的索引；数据库为什么会有这样不同的处理结果呢？我们不妨回顾一下我们前面给出的这几个索引的基本信息；我们会发现idx_group_id这个索引的Cardinality字段值为24711，idx_user_id为32949，而status字段上的idx_status仅有4，也就是说，status这个字段仅有4个不同的值，区分度非常低！也就是说这个条件对记录的过滤能力非常差，所以虽然该字段建有索引，但是数据库却干脆弃之不用了！另一点我们需要注意的是这条sql解析的Extra字段中出现了Using where这样的信息，表明该查询光走一次索引查询是搞不定的，还要进一步应用where子句中的查询条件来进行处理。4.3 组合索引查询4.3.1 组合索引结构 组合索引在我们日常的开发中也会经常用到；本质上组合索引和单索引并没有区别，只不过在组合索引中，每个b+tree节点中的包含了多个字段的数据；如下图所示： 组合索引在多条件查询的索引应用中，有很多需要注意的地方的，下面仍以上例中的数据库为例进行说明；首先，我们先将前面我们drop掉的两条组合索引加上； 加完之后，shop_group_item表上的索引结果如下： 这里需要说明的是，虽然我们并没有对表中记录进行增减，但是对应的索引的Cardinality值却发生了变化，这是因为数据库对这个值只是进行了粗略的统计，而且是每隔一段时间去更新一次，或者手动触发的时候去更新的，所以这个值只能用来作为参考，并不是精确值；4.3.2 多索引的选择 我们重复一下2中的查询，看看在既有单索引有有组合索引的情况下，数据库会如何选择；explain select * from shop_group_item wheregroup_id=12003;这里我们仍截取我们关心的字段情况： 我们发现，这条查询可以应用的索引有三个，虽然其中包括两个组合索引，但是MySQL最后还是选择了idx_group_id这条单索引，我们再对比一下前图的Cardinality值，发现在这三个索引中，应用到group_id字段的Cardinality值均为25155，也就是说从区分度上来讲，后两个组合索引并不比第一个单索引更好，而在扫描组合索引的索引节点时，显然效率更低一些，所以数据库很聪明的选择了单索引去查询；为了对比，我们再来看看下面这条sql的解析情况；explain select * from shop_group_item where group_id=151272 and user_id= 907638037; 当使用group_id和user_id两个查询条件时，MysQL判断出有4个索引可以使用，分别是可应用于group_id字段的idx_group_id和idx_group_id_item_id；可应用于user_id字段的idx_user_id以及可以同时应用于这两个字段的组合索引idx_group_id_uid_status；解析器很明智的选择了匹配度最高的一个；从后边的key_len也可以看出，虽然idx_group_id_uid_status这个索引包含有三个字段，但是由于条件里只有group_id和user_id两个条件，所以只应用了索引中的前两个字段；4.3.3 查询条件顺序 在不建索引的情况下，where子句中查询条件的顺序可能会对查询效率有影响；那么在建有索引的情况下呢？我们来看下边的例子：我们只需要4.2.2中第二条查询的查询条件顺序调换一下即可；explain select * from shop_group_item whereuser_id=157860040 and group_id=144030; 由此可以推测，where子句中查询条件的顺序调换是不影响索引的应用的；而事实上也确实是如此的，优化器并不关心查询条件的具体顺序，在执行前它会很好的去调整以匹配最合适的索引。4.3.4 最左前缀匹配 既然在多条件查询时组合索引效率更高，那么是不是只要是包含在索引中的字段就会应用相应的索引呢？我们来看下面的sql：explain select * from shop_group_item whereuser_id=157860040 and status=1; 很可惜，idx_group_id_uid_status这个组合索引并没有起作用，只有group_id上的单索引被使用了；其实在我们了解了上边的组合索引的结构之后，就应该能想明白了为什么了；因为B+Tree实现的组合索引这种索引结构，其本身就是要求最左前缀匹配的，当查询条件中不包含索引中的第一个字段时，系统是无法在树中确定查找的入口的，如果要强制去使用索引，那么只能从根节点去遍历，这样的话效率反而不如使用单索引了；还有下面的情况：explain select * from shop_group_item wheregroup_id=144030 and status=1 and gmt_create=´2012-12-07 14:51:36´; 看起来貌似很可惜，我们就差了一个user_id字段，但是解析器很无情的直接放弃了idx_group_id_uid_status这个索引，而是使用了idx_group_id_item_id，同样key_len为8，也就是说只用到了索引中的group_id字段；那么如果我们强制使用idx_group_id_uid_status这个索引结果又会如何呢？我们来看一下：explain select * from shop_group_item forceindex(idx_group_id_uid_status) where group_id=144030 and status=1 andgmt_create=´2012-12-07 14:51:36´; 解析器很听话的使用了idx_group_id_uid_status，但是我们看到，key_len仍然是8，group_id后边的字段并没有生效；其实我们仔细想想，根据组合索引的结构，解析器只能使用最左前缀匹配，所以没生效是必然的事情，否则查询的效率就不升反降了。4.4 Order by查询 前面简单介绍了符合索引应用时的一些规则，下面我们再看一下在我们日常开发中也会经用到的order by对索引的应用情况；4.4.1 无索引order by 首先我们先来看一个在没有建索引的字段上使用order by的例子；shop_group_item表中有一个item_price字段，用来记录宝贝的价格，下面我们就对他进行排序：explain select * from shop_group_item wheregroup_id=144030 order by item_price; 在索引的应用上，只使用了group_id字段上的单索引idx_group_id，和我们预期的情况完全一样；但是我们在看下它的Extra字段： Using where是我们并不陌生的，因为item_price字段信息没有在索引数据中，所以无法直接返回结果集；但是这次多了一个Using filesort是我们以前没见过的；它的意思是通过索引拿到结果集后，为了处理后边的order by，还必须要进行一次文件排序；但是需要说明的是，这里的filesort并不一定就是磁盘排序，也可能完全是基于内存，只有当需要排序的记录比较多，无法全部在内存中完成时，才会使用磁盘；4.4.2 有索引order by 看完了上边的情况，我们当然要再试一下建有索引的字段是如何处理排序的；explain select * from shop_group_item wheregroup_id=144030 order by status; 虽然status字段上也建有索引，但是执行计划并没有像3中的那样，使用索引merge，而且Using filesort也还存在，也就是说，status上的排序并没有使用索引；其实我们仔细回忆一下前边讲过的索引的结构就会明白，在这种排序字段不在查询条件中的情况，理论上讲也是无法应用索引排序的；那我们不妨再看下排序字段同时也在查询条件中的情况会如何制定执行计划；explain select * from shop_group_item wheregroup_id=144030 order by group_id; 果然，不出我们所料，不但Using filesort没有了，就连Using where也消失了；因为排序字段就是起作用的索引字段本身，所以系统直接使用了索引的结果，根本就不需要再进一步处理了，而且因为也没有其他查询限定，所以也不需要在应用where子句的条件进一步处理了；4.4.3 组合索引order by 在单索引上，我们了解了执行计划是如何制定的，那么在组合索引上情况又如何呢？explain select * from shop_group_item wheregroup_id=144030 order by user_id; 解析器这次并没有选择group_id字段上的单索引，而是很明智的选择了idx_group_id_uid_status这个组合索引，但是我们要注意一下它的key_len字段，只有8，也就是说，查询使用的索引字段只有group_id，并不包括user_id字段；根据组合索引的结构就不难理解，order by其实类似于范围查询，没办法通过索引一次性定位，必须要对结果进行进一步处理，所以在Extra信息里还出现了Using where； 但即便是这样，其效率也是很高的；而这里查询字段group_id和排序字段user_id是索引idx_group_id_uid_status的前两个字段，符合最左前缀匹配原则，所以可以直接使用索引顺序，不管正序还是倒序，都不需要再重新做一次filesort了。4.4.4 order by的最左匹配 通过前面的例字我们知道了多where条件查询组合索引的最左前缀匹配原则，那么存在order by的情况是不是也这样呢？我们来看一下：explain select * from shop_group_item wheregroup_id=144030 and status=0 order by gmt_create; 可以看到，在应用索引的时候，MySQL并没有选择idx_group_id_uid_status这个包含gmt_create的索引，而是选择了idx_group_id_item_id，而且仍然只有第一个字段即group_id生效了； 再来看他的Extra信息：其中仍有Using filesort信息，也就是说，最后的排序并没有用到索引，而是又重新排了一次序；那有人会问，是不是因为查询应用idx_group_id_item_id这个索引中不包含gmt_create才会这样呢？那我们不妨试一下强制使用idx_group_id_uid_status的情况： explain select * from shop_group_item forceindex(idx_group_id_uid_status) where group_id=144030 order by gmt_create;可以看到，这种情况下MySQL仍然使用了文件排序；其实我们分析一下组合索引的结构即可明白，虽然索引是有序的，但是索引中的节点并不是按照gmt_create这个字段有序排列的，而是索引所有字段的一个整体有序；对于单个字段来讲，只是在这个字段所处的当前层次内才是有序的；4.5 索引覆盖4.5.1 准备工作 在数据库查询中，如果我们很好地应用了索引，那么将使我们的sql效率得到极大的提升，可能以前令人头疼的性能瓶颈一下子就解决了！但是人都是不知足的，我们总会想：还有没有办法更快点？答案是肯定的！下面我们就来看下索引覆盖的情形；为了能让在不同的数据量下sql在执行时间上的对比更强烈一些，我们特意将shop_group_item这张表的记录数增加到100万，和前边一样，这些数据都是来自生产环境的真实数据，所以不存在特殊性的问题；同时，为了方便对比，我们为这张表取了个新的名字shop_group_item_t，但是表结构前后是完全一样的。4.5.2 主键索引效率 首先，我们再将1中的sql在shop_group_item_t中重新执行一遍；select * from shop_group_item_t where id=12003;在我们的测试中，这条sql的执行时间是0.03 sec（在关闭查询结果缓存的情况下），相较于上次shop_group_item表10w条记录0.00sec的情形，可以说已经慢了很多了；4.5.3 非主键索引效率 同样，我们还要再尝试一下再非主键索引上的情况；我们将4.3.2中的第二条查询在shop_group_item_t表上再执行一下，一共查询到10条记录，耗时0.06sec（关闭缓存多次重复执行的时间基本相同）；而同样的语句和结果在只有10w条记录的shop_group_item表上执行时耗时为0.00sec；其实从上边两个简单的例子就可以看出，即便是恰当的使用了索引，在数据量比较大的情况下，sql的执行消耗也会相应增大；这还仅仅是最简单的单条件查询！如果数据量和sql的执行频率再进一步增加，可以想象，我们的数据库会很快出现瓶颈，我们必须进一步优化！4.5.4 分页查询（屌丝写法） 在平时的开发过程中，分页查询是我们经常写的一类sql；最普通的分页写法一般是这样的：select * from shop_group_item_t where group_id =107127 and status = 0 order by gmt_create desc limit 1,20;下面我们看下这条sql的执行计划是什么样的；首先看他的索引应用情况： 这个执行计划和我们前边讲得一样，没有什么特别；接下来再看下Extra信息： 从以上sql的执行计划我们可以推测出数据库的执行过程：首先，从idx_group_id_item_id中检索出group_id=107127的节点，然后根据节点上存储的主键id取出对应的记录，然后在应用查询条件中的status=0对这些记录进行过滤，最后再将过滤后的记录按照gmt_create进行降序排列，取出其中的前20条，返回给客户端；在我们的测试中，这条sql的执行时间是0.27 sec（在关闭查询结果缓存的情况下），应该说比较慢了，何况我们现在只有100万的记录，符合group_id=107127和status=0的记录也就几百条；4.5.5 分页查询（文艺写法） 上例中的分页sql可谓是最“屌丝”的写法了，在sql review时，一般都会被dba打回，要求改写成如下所示的“文艺”分页写法：select * from (select id from shop_group_item_t wheregroup_id = 107127 and status = 0 order by gmt_create desc limit 1,20 ) a,shop_group_item_t s where a.id = s.id;乍一看，sql语句长了不少，但是在实际执行时，相同测试环境下这条sql所耗费的时间几乎为0！果然够文艺！那么为什么这个效率就会高呢？下边来看下它的执行计划；因为宽度问题，我们还是分两屏贴出： sql执行计划的执行顺序是按照其id从大到小执行的，也就是说，首先被执行的是id=2的计划，它对应我们上述sql语句最内层的查询；我们看到，其select_type值为DERIVED，涵义是指该查询为派生表的select查询（from子句的子查询），我们同时也看到虽然应用了idx_group_id_uid_status索引，但是其type和rows分为ALL和990872；对比最开始1中全表扫描查询的所需时间，这里显然没有使用全表扫描，而是应用了索引，所以explain出来的执行计划并不是绝对准确无误的；在两个id为1的执行计划中，第一个table为表名这个是个派生表，其实也就是a表，这个表走的是全表扫描，一共涉及到20条记录；而s表则走了主键索引，同时ref字段值为a.id，type字段为eq_ref，表示使用了a.id这个列的值来精确匹配主键索引筛选出需要的行；所以整个sql的执行流程如下：a、 首先从索引idx_group_id_uid_status中筛选出所有group_id = 107127的索引节点，然后从这些节点中再找出status=0的节点，之后按照gmt_create字段对剩下的节点排序（因为idx_group_id_uid_status包含了group_id、status、gmt_create这些字段，所以之前的筛选都只需在索引节点中进行）最后取出前20条数据的id值（id为主键，也包含在索引节点中，所以这一步仍在索引中进行）放到临时表derived2中；b、 顺序遍历derived2中的每条记录中的id值，然后用这个值去s（由shop_group_item_t表派生而来）表中根据主键索引查询对应记录；c、 最后将查到的所有记录返回给客户端；4.5.6 执行计划准确吗 下面我们把上例中最里层的sql拿出来单独做一下解析；explain select id from shop_group_item_t wheregroup_id = 107127 and status = 0 order by gmt_create desc limit 1,20;下面我们看一下它的执行计划： 这个执行计划看起来就很正常了，仍然使用了idx_group_id_uid_status这个索引的第一个字节，ref为常量匹配，读取的记录为30条；此外，Extra中还出现了Using where和Using index，前者根据我们16.a中的分析，出现是很正常的，而Using index表示返回给客户端的结果集中所需要的字段都可以从索引中拿到，不需要再去表中查询，根据我们前面的分析来看，这个也是很正常的；4.5.7 索引覆盖原理 上例中“文艺”分页写法的例子，其实就是前面我们所说的索引覆盖（index coverage），即索引中的字段数据即可覆盖结果集中的所需字段，这样查询时只扫描索引即可，不需要再到数据表中重新取数据了；在sql优化中，一条很重要的原则就是尽量减少“回表”；所谓回表，是指sql执行过程中，无法在索引中取到全部需要的数据，必须要到表中重新将所需的字段取出来，然后再进行后续操作；因为表中记录的数据一般都是在磁盘上的，很少能从缓存直接取到，所以而这个回表的操作是非常耗时间的，尤其是回表的次数和数据条数比较多时候更是如此！索引覆盖是非常好的避免回表的方法；一般来讲，数据库会尽量将索引放在缓存中，即便需要从磁盘中读取，索引的查找效率也很高的；所以如果能从索引中取到所有需要的数据，那就可以避免到回表这部分的磁盘I/O，性能将会大大提升；当然，也不是说应该把表中的所有字段都包含到索引里边，因为索引的维护开销也是很大的；每一次写操作，都需要更新对应的索引数据，包括添加索引记录，B+树结构调整，节点分裂、移动等，所以实际产生的磁盘I/O量可能是写操作数量的几倍甚至更多；所以，究竟建什么样的索引，包含哪些字段，是需要综合多方面因素来权衡的，包括以下几个方面：a、 业务逻辑需要什么样的sql语句；b、 每条sql语句的执行频率如何；c、 sql语句的查询条件和执行频率主要决定了建什么样的索引；d、 schema结构（字段类型、区分度等）影响着索引的效率；而反过来，索引优化方式也会逆向反馈sql语句和程序代码的写法，甚至不得已还要调整业务逻辑；这就是我们前面所说的向上反馈。 5、 总结 以上主要介绍了数据库索引的一些东西，只是整个数据层优化的一部分，甚至限于时间和篇幅，连这一部分都没有介绍全面，比如说索引的磁盘结构，distinct的优化、join的优化等等，也并没有分析sql执行时的具体IO情况；本文的目的旨在总结平时工作和学习的一些所得，也算是抛砖引玉的作用，欢迎有兴趣的同学一起讨论！

本帖最后由 jimmy 于 2017-9-1 17:42 编辑关于 Microsoft SQL Server Microsoft SQL Server 是由美国微软公司所推出的关系数据库解决方案，最新的版本是 SQL Server 2016， 已经在 2016 年 6 月 1 日发布。数据库的内置语言原本是采用美国标准局（ANSI）和国际标准组织（ISO）所定义的 SQL 语言，但是微软公司对它进行了部分扩充而成为作业用 SQL（Transact-SQL）。 几个初始版本适用于中小企业的数据库管理，但是近年来它的应用范围有所扩展，已经触及到大型、跨国企业的数据库管理。 [table=98%,silver] 版本号年份发布名称代号1.0 (OS/2)1999SQL Server 1.0-4.211993SQL Server 4.21-6.01995SQL Server 6.0SQL956.51996SQL Server 6.5Hydra7.01998SQL Server 7.0Sphinx-1999SQL Server 7.0 OLAP工具Plato8.02000SQL Server 2000Shiloh8.02003SQL Server 2000 64-bit版本Liberty9.02005SQL Server 2005Yukon10.02008SQL Server 2008Katmai10.252009SQL AzureCloudDatabase10.502010SQL Server 2008 R2Kilimanjaro（aka KJ）11.02012SQL Server 2012Denali12.02014SQL Server 2014-13.02016SQL Server 2016-数据库服务的启动 [table=98%,lightblue] 通过服务启动我的电脑 -》 管理 -》 服务 -》 SQLServer -》 启动通过 SQLServer 配置管理器启动开始 -》 所有程序 -》 SQLServer -》 配置工具 -》 SQLServer 配置管理器 -》 启动通过命令行启动命令行执行 net start mssqlserv数据库结构 物理结构 [table=98%,lightblue] 主数据文件xxx.mdf包含数据库启动信息，存储一部分数据,一个数据库只能有一个主数据文件辅助数据文件xxx.ndf用来存储组数据文件未能存储的数据和一些数据对象日志文件xxx.ldf存储数据库更新的日志信息文件组逻辑概念，没有具体文件将文件分组，类似于文件夹的存在，默认只存在一个 Primary 文件组逻辑结构[table=98%,lightblue] 表视图索引存储过程 etc数据库命名规则 [table=98%,lightblue] 第一个字符必须是字母或"_"、"@"、"#"不能使用 T-SQL 的保留字不允许嵌入空格或者其他特殊字符数据库分类 [table=98%,lightblue] 系统数据库 (可以理解为用户数据库的数据库)[table=98%,seagreen]master记录 SQLServer 当中系统级别的信息 登录账户 系统配置 数据库文件的存放位置等model模板数据库msdbSQLServer 的代理作业，备份还原信息tempdb临时数据库，存储临时表，临时存储过程resource隐藏并且只读的数据库 包含了 SQLServer 当中所有的系统对象的物理结构信息 这些系统对象的逻辑信息包含在各个数据库的 sys 架构下面用户数据库用户自己创建的数据库创建数据库时需要指定的属性 [table=98%,lightblue] 文件名称存放位置分配的初始空间 (最小 3 MB)属于哪个文件组文件增长 (可按大小或者百分比指定增长速度)文件容量设置 (可以指定最大值或者不受限)兼容级别 (新版本向下兼容可以指定到具体的兼容版本)访问限制 (多用户，单用户，特定用户)可读写属性 (默认可读写，可以改为只读)数据库的创建方法 命令行创建数据库范例 [code] USE master -- 指向当前所操作数据库 GO CREATE DATABASE TEST -- 数据库名称 TEST ON PRIMARY -- 主文件组 ( NAME=′Test_Data′, -- 主文件逻辑名 FILENAME=′E:\test\Test_Data.mdf′, -- 主文件物理名 SIZE=5MB, -- 主文件初始大小 MAXSIZE=100MB, -- 主文件的增长最大值 FILEGROUWTH=15% -- 主文件增长率 ), -- 第一个数据文件结束 FILEGROUP FG -- 第二个文件组 ( NAME=′FG_Test_Data′, -- 主文件逻辑名 FILENAME=′E:\test\FG_Test_Data.ndf′, -- 主文件物理名 SIZE=5MB, -- 主文件初始大小 MAXSIZE=100MB, -- 主文件的增长最大值 FILEGROUWTH=15% -- 主文件增长率 ), LOG ON ( NAME=′Test_Log1′, -- 日志文件逻辑名 FILENAME=′E:\test\Test_Log1.ldf′, -- 日志文件物理名 SIZE=5MB, -- 日志文件初始大小 FILEGROUWTH=0 -- 未启用自动增长 ), -- 日志 2 的具体描述 ( NAME=′Test_Log2′, -- 日志文件逻辑名 FILENAME=′E:\test\Test_Log2.ldf′, -- 日志文件物理名 SIZE=5MB, -- 日志文件初始大小 FILEGROUWTH=0 -- 未启用自动增长 ) GO [/code] 数据库的删除方法 命令行删除数据库范例 [code] USE master GO IF EXISTS(SELECT * FROM sysdatabases WHERE name = ´TEST´) DROP DATABASE TEST GO[/code]