5. 高性能MySQL --- 创建高性能索引

  索引，mysql 中也叫键，是存储引擎用于快速找到记录的一种数据结构。

5.1 索引基础
	mysql中，其先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。
	索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为mysql只能高效的使用索引的最左前缀。

	5.1.1 索引的类型
		在mysql中，索引是在存储引擎层实现的而不是服务器层实现的。所以，并没有统一的索引标准：不同的存储引擎的索引的工作方式
	  不大一样，也不是所有的存储引擎都支持所有的类型的索引。

	  1.B-Tree 索引：
	  	当人们谈论索引的时候，没有特别指明，一般都是指 B-Tree 索引。
	  	存储引擎以不同的方式使用 B-Tree 索引，性能也各不相同。例如，MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原数据
	  格式进行存储。再如，MyISAM 索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
	    B-Tree 意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
	    B-Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。
	  根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下一层节点，
	  这些指针实际上定义了子节点页中值的上限和下限。
	    叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。树的深度和表的大小直接相关。
	    B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。
	    B-Tree 索引适合用于全键值，键值范围和键前缀查找，其中键前缀查找只适合用于根据最左前缀的查找。
	    
	    key(last_name, first_name, dob)
	    1.全值匹配  
	    2.匹配最左前缀 
	    3.匹配列前缀
	    4.匹配范围值
	    5.精确匹配某一列并范围匹配到另外一列 : 第一列全匹配，第二列范围匹配
	    6.只访问索引的查询

	    因为索引树中的节点是有序的，所以除了按值查找外，索引还可以用于查询中的 order by 操作(按序查找)。

	    B-Tree 索引的限制：
	    	1.如果不是按照索引的最左列开始查找，则无法使用索引。
	    	2.不能跳过索引中的列
	    	3.如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找

	  2.哈希索引：
	  	哈希索引基于哈希表实现，只有精确匹配索引所有的列的查询才能有效。对于每一行的数据，存储引擎会对所有的索引列计算一个哈希码，哈希码是一个较小
	  的值，并且不同的键值的行计算出来的哈希码也是不一样的。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存执行每个数据行的指针。
	    在 mysql 中，只有 Memory 引擎显示支持哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也支持 B-Tree 索引。值得一提的是，
	  Memory 引擎是支持费唯一哈希索引的，如果多个列的哈希值相同，索引会以链表的方式存放在多个记录指针到同一个哈希条目中。

	    限制：	
	    	1.哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快
	    	2.哈希索引数据并不是按照索引顺序存储的，所以也就无法用于排序
	    	3.哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希索引的。
	    	4.哈希索引只支持等值比较查询，包括 =，IN()，<=>，也不支持任何范围查询
	    	5.访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有合适的行
	    	6.如何哈希冲突很多的话，一些索引维护操作的代价会很高

	    InnoDB 引擎有个特殊的功能叫做 '自适应哈希索引'。当 InnoDB 注意到某些索引值被使用的非常频繁时，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引，
	  这就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的，内部的行为，用户无法控制或者配置，不过可以关闭。
	    
	    自定义哈希索引：
	    	使用 crc32() 新的一列。记住不要使用 md5()或sha1()，这2个是强加密函数，计算出来的哈希值是非常长的字符串，会浪费大量的空间。

	  3.空间数据索引
	  	MyISAM 支持空间索引，可以用作地理数据存储。和 B-Tree 不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效的使用任何维度来
	  组合查询。必须使用 mysql 的 GIS 相关函数 MBRCONTAINS() 等来维护。(PostgreSQL)

	  4.全文索引
	  	全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有许多注意的细节，如
	  停用词，词干，复数，布尔搜索等。类似搜索引擎做的事情，而不是简单的 where 匹配。
	    相同的列上同时创建全文索引和基于值的 B-Tree 索引不会又冲突，全文索引适用于 match against 操作，而不是普通的 where 条件操作。

	  5.其他索引类别
	  	形数索引，聚簇索引，覆盖索引等。


5.2 索引的优点
		索引可以让服务器快速的定位到表的指定位置。
		最常见的 B-Tree 索引，按照顺序存储数据，所以 mysql 可以用来做 order by 和 group by 操作。因为数据是有序的，所以 B-Tree 也就会将相关的列值都存储在
	  一起。

	    优点：
	    	1.索引大大减少了服务器需要扫描的数据量
	    	2.索引可以帮助服务器避排序和临时表
	    	3.索引可以将随机IO变成顺序IO

	    索引是最好的解决方案吗：
	    	索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其他带来的额外工作时，索引才是有效的。
	    	对于非常小的表，大部分情况下简单的全表扫描更高效。对于中大型的表，索引就非常有效。但对于特大型的表，简历和使用索引的代价随之增长。
	      这种情况下，则需要一种技术可以直接区分查找出需要的一组数据，而不是一条条的记录匹配，比如分区技术。
	        如果表的数量特别多，可以建立一个元数据信息表，用来查询要用到的某些特性。经常会使用块级别的元数据技术来替代索引。

	
5.3 高性能索引策略
	5.3.1 独立的列
		独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。如 where actor_id + 1 = 5;

	5.3.2 前缀索引和索引选择性
		有时候需要索引很长的字符列，这会让索引变的大且慢。一个策略是前面提到的模拟哈希索引。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引
	  效率。但是这样也会降低索引的选择性。索引的选择性是指，不重复的索引值(也称基数)和数据表的记录总数(#T)的比值。范围从 1/#T 到1 之间。索引的选择性越高则查询
	  效率越高。因为索引选择性高的索引可以让 mysql 在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
	    一般情况下某列前缀的索引选择性也是足够高的，足以满足查询性能。对于 blob,text或很长的 varchar类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整
	  长度。
	    alter table 表名 add key (city(7)) // 如何增加前缀索引

	  缺点：
	  	mysql 无法使用索引前缀做 order by, group by，也无法使用前缀索引做覆盖扫描。

	5.3.3 多列索引
		1.当服务器对多个索引做相交操作时(通常有多个 and 条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
		2.当服务器需要对多个索引做联合操作时(通常有多个 or 条件)，通常需要消耗大量cpu和内存资源在算法的缓存，排序和合并操作上。特别是
		  当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
		3.更重要的是，优化器不会把这些计算到'查询成本'中，优化器只关心随机页面读取。这会使得成本被'低估'，导致该执行计划还不如直接走全表扫描。
		  这样做不但会消耗更多的cpu和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询往往会忽略对并发性的影响。通常来说，还不如像
		  mysql4.1 或者更早的时代一样，将查询改成 union 的方式往往更好。

	5.3.4 选择合适的索引列顺序
		  哈希或者其他类型的索引并不会像 B-Tree 索引一样按顺序存储数据。
		  在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以，索引可以按照升序或者降序进行扫描。
		  对于如何选择索引的列顺序，有一个经验法则：将选择性最高的列放在索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这
		时候索引的作用只是用于优化 where 条件的查找。在这种情况下，这样的设计的索引确实能够最快的过滤出需要的行，对于在 where 子句中只是用了索引部分
		前缀列的查询来说选择性也更高。然后，性能不只是依赖于所有索引列的选择性(整体基数)，也和查询条件的具体值有关,也就是和值的分布有关。

	5.3.5 聚簇索引
		  聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引
		和数据行。
		  当表有聚簇索引的时候，它的数据行实际上存放在索引的叶子页中。术语 '聚簇' 表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在2个不同
		的地方，所以一个表只能有一个聚簇索引。
		  因为是存储引擎负责实现索引的，因此不是所有的存储引擎都支持聚簇索引。
		  叶子页包含了行的全部数据，但是节点页只包含了索引项。
		  InnoDB 通过主键聚集数据，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

		  聚集数据的优点：
		  	1.可以把相关的数据保存在一起。
		  	2.数据访问更快
		  	3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值

		  缺点：
		  	1.聚簇索引最高限度的提高了IO密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了
		  	2.插入速度严重依赖插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。但是如果不是按照主键顺序加载数据，那么在加载完成后最好
		  	  使用 optimize table 命令重新组织一下。
		  	3.更新聚簇索引的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置
		  	4.基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临 '页分裂'的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，
		  	  存储引擎会将该页分裂成2个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
		  	5.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
		  	6.二级索引(非聚簇索引)可能比想象的更大，因为在二级索引中的叶子节点包含了引用行的主键列。
		  	7.二级索引访问需要2次索引查找，而不是一次。二级索引中保存的 '行指针' 的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的
		  	  主键值。这意味着，通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获取对应的主键值，然后根据这个主键值去聚簇索引中查到对应的行。对于 InnoDB
		  	  ，自适应哈希索引能够减少这样重复的工作。

		  InnoDB 和 MyISAM 的数据分布比较：
		  	聚簇索引和非聚簇索引的数据分布是有区别的，以及对应的主键索引和二级索引的数据分布也有区别。

		  	1.MyISAM 的数据分布：
		  		是按照数据插入的顺序存储在磁盘上的。事实上，MyISAM 中主键索引和其他索引在结构上并没有什么区别。主键索引就是一个名为 primary 的唯一非空索引。

		    2.InnoDB 的数据分布：
		    	因为InnoDB 支持聚簇索引，所以使用非常不同的方式存储同样的数据。
		    	聚簇索引的每一个叶子节点都包含了主键值，事务ID,用于事务和 MVCC 的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键
		      列和剩余的其他列。
		        还有一点和 MyISAM 的不同的是，InnoDB 的二级索引和聚簇索引很不相同。InnoDB 二级索引的叶子节点中存储的不是 '行指针'，而是主键值，并以此作为指向
		      行的'指针'。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动
		      行时无需更新二级索引中的这个 '指针'.

		  在 InnoDB 表中按主键顺序插入行：
		  	  如果正在使用 InnoDB 表且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用 auto_increment 自增列。
		  	这样可以保证数据行是按顺序写入的，对于根据主键做关联操作的性能也更好。
		  	  最好避免随机的聚簇索引，特别是对 IO 密集型的应用。
		  	  uuid 主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长；另外一方面也是由于页分裂和碎片导致的。
		  	  因为主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB默认的最大填充因子是页大小的 15/16,留出部分空间用于
		  	以后修改)，下一条记录就会写入新的页中。一旦数据按照这种顺序方式加载，主键页就会近似于被顺序的记录填满，这也正是期望的结果。
		  	  uuid 聚簇索引的表插入数据，有什么不同？因为新行的主键值不一定比之前插入的大，所以InnoDB 无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置
		  	---通常是已有数据的中间位置---并且分配空间。这会增加很多额外的工作，并导致数据分布不够优化。
		  	  缺点：
		  	  	1.写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机IO。
		  	  	2.因为写入是乱序的，InnoDB 不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量的数据，一次插入最少需要修改3个页面而不是一个页。
		  	  	3.由于频繁的页分裂，页会变得稀疏并被不规则的填充，所以最终数据会有碎片。
		  	  使用 InnoDB 时应该尽可能的按照主机顺序插入数据，并且尽可能的使用单调增加的聚簇键来插入新行。

		  	  顺序的主键什么时候会造成更坏的结果？
		  	  对于高并发工作负载，在 InnoDB 中按照主键顺序插入可能会造成明显的争用。主键的上界会造成 '热点'。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁争用。
		  	另外已给热点可能是 auto_increment 锁争用。如果遇到这个问题，则可能需要重新考虑设计表或者应用，或者更改 innodb_autoinc_lock_mode 配置。

	5.3.6 覆盖索引
		  通常大家都会根据查询的 where 条件来创建合适的索引，不过这只是索引优化的一个方面。mysql 也可以使用索引来直接获取列的数据，这样就不需要再读取数据行。如果索引的叶子
		节点中已经包含了要查询的数据，那么久没有必要再回表查询了。如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为 '覆盖索引'.
		  覆盖索引的好处：	
		  	1.索引条目通常远小于数据行的大小，所以如果只需要读取索引，那mysql就会极大的减少数据访问量。这对缓存的负载是非常重要的，因为这种情况下响应时间大部分花在数据的拷贝
		  	  上。覆盖索引对于IO密集型的应用也有帮助，因为索引比数据更小，更容易全部放到内存中。尤其对于MyISAM，因为它能使用压缩索引变的更小。
		  	2.因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多。
		  	3.一些存储引起如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据
		  	  访问中的最大开销的场景。
		  	4.由于InnoDB的聚簇索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的
		  	  二次查询。

		  不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等都存储索引列的值。所以，mysql 只能使用 B-Tree 索引做覆盖索引。
		此外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。
		  当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时，在 explain 的 extra 列可以看到 'Using Index'的信息。
		  索引覆盖查询还有很多陷阱导致无法实现优化。mysql 查询优化器会在查询之前判断是否有一个索引能够进行覆盖。假设索引覆盖了 where 条件中的所有字段，但部署整个查询涉及
		的字段。如果查询条件为假，mysql 5.5 和更早的版本也总是会回表获取数据行的。
		  mysql 不能在索引中使用 like 操作。这是底层 api 的限制。mysql 能在索引中做最左前缀匹配的 like 比较，因为该操作可以简单的转换为简单的比较操作，但是如果通配符
		开头的 like 查询，存储引擎就无法做比较匹配了。这种情况下，mysql 服务器只能提取数据行的值而不是索引值来做比较了。

		  延迟关联：
		  	通过覆盖索引返回主键，再根据主键与原表关联。延迟了对列的访问。

	5.3.7 使用索引扫描来做排序
		  mysql 有2种方式生成有序的结果：通过排序操作或者按索引顺序扫描。如果 explain 出来的 type 值为 'index'，则说明 mysql 使用了索引扫描来做排序。
		  扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么久不得不每扫描一条索引记录就都需要回表
		查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的速度通常比顺序的全表扫描慢，尤其是IO密集型的工作负载时。
		  mysql 可以使用同一个索引即满足排序，又用于查找行。
		  只有当索引的列顺序和 order by 子句的顺序完全一致时，并且所有列的排序方向(倒序或者顺序)都一样时，mysql 才能够使用索引来对结果进行排序。如果需要关联多张
		表，则只有当 order by 子句引用的字段全部为第一张表时，才能使用索引做排序。order by 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，mysql
		都需要执行排序操作，而无法利用索引排序。
		  有一种情况下 order by 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。

	5.3.8 压缩(前缀压缩)索引
		  MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大的提高性能。默认只压缩字符串，但通过参数设置也可以对整数进行压缩。
		MyISAM 压缩每个索引块的方法是，先完全保存索引块的第一个值，然后将其他值和第一个值进行比较得到相同的前缀的字节数和剩余的不同的后缀部分，把这部分存储起来即可。
		例如，索引块中的第一个值是 'perform',第二个值是 'performance'，那么第二个值的前缀压缩之后存储的是类似 '7,ance'这样的形式。
		  压缩前缀使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以 MyISAM 查找时无法在索引块使用二分查找从而只能从头开始扫描。正序的
		速度还不错，但是如果是倒序的---例如，order by desc --- 就不是很好了。

	5.3.9 冗余和重复索引
		  mysql 允许在相同的列上创建多个索引。mysql 需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个考虑，这会影响性能。
		  重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。
		  冗余索引和重复索引有些不同。如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此，索引(A,B)也可以当做索引(A)来使用。如果
		再创建索引(B,A)就不是冗余索引了。索引(A,ID),ID 是主键，也是冗余索引。对于InnoDB 来说，主键列已经饱和在了二级索引当中了，所以这也是冗余了。
		  一般来说，增加新的索引会导致 insert,update,delete 等操作速度变慢。

	5.3.10 未使用的索引
		  除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，应该删除。

	5.3.11 索引和锁
		  索引可以让查询锁定更少的行。
		  InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。但这只有当InnoDB 在存储引擎层能够过滤掉所有不需要的行时，
		才会有效。如果索引无法过滤掉无效的行，那么在 InnoDB 检索到数据并返回给服务器层的以后，mysql 服务器才能应用 where 子句。这时候已经无法避免锁定行了：InnoDB
		已经锁定了这些行，到适当的时候才会释放。

		  InnoDB 在二级索引上使用共享锁，但在访问主键索引需要使用排他锁。这消除了使用覆盖索引的可能性，并且使得 select for update 比 lock in share mode 或
		非锁定查询要慢很多。

5.4 索引案例学习
	  1.尽可能的将需要做范围查询的列放到索引的后面
	  2.每增加一个 in() 的条件，优化器需要做的组合都将以指数形式增加
	  3.避免多个范围条件
	    explain 的 type = range, 有2种情况，一个是 where id > 5; 一个是 id in(1,2,4,5).第二个查询是多个等值条件查询。这2种查询的效率是不同的，对于范围查询，
	  mysql 无法再使用范围后面的其他索引列了，但是对于多个等值条件的查询，则没有这个限制。

5.5 维护索引和表
	  维护表有3个目的：	
	  	1.找到并修复损坏的表
	  	2.维护准确的索引统计信息
	  	3.减少碎片

	  对于 MyISAM 来说，表损坏通常是系统崩溃导致的。损坏的表hi导致查询返回错误的结果或者莫须有的主键冲突等问题，严重的时候还会影响数据库的崩溃。
	  check table 表名 // 检查
	  repair table 表名  // 修复

	  如果 InnoDB 引擎的表出现了损坏，那么一定是发生了严重的问题。因为它一般不会损坏。可以通过设置 innodb_force_revovery 参数进入 InnoDB 的强制
	恢复模式来修复数据。

	  mysql 的查询优化器会通过2个api来了解存储引擎的索引值的分布信息，以决定如何使用索引。分别是 recordings_in_range()，通过向存储引擎传入2个边界值获取在
	这个范围内大概有多少记录。对于某些存储引擎，该接口返回精确值，如 MyISAM。有些是返回估算值，如InnoDB.
	  第二个api 是 info()，该接口返回各种类型的数据，包括索引的技术。
	  如果存储引擎向优化器提供的扫描行数信息是不正确的数据，或者执行计划本身台复杂以致无法准确的获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描的
	行数。mysql 优化器使用的是基于成本的模型，而衡量成本的主要指标是一个查询需要扫描多少行。如果没有标的统计信息，或者统计信息不准确，优化器可能做出错误的决定。
	可能通过运行 analyze table 来重新生成统计信息解决这个问题。

	  每种存储引擎实现索引统计信息的方式不同，所以需要 analyze table 的频率也因不同的引擎而不同：
	  1.memory 引擎根本没有存储引擎统计信息
	  2.MyISAM 将索引统计信息存储在磁盘中，analyze table 需要进行一次全索引扫描来计算索引基数。整个过程中需要锁表
	  3.InnoDB 也不在磁盘中存储索引统计信息，而是通过随机的索引访问进行评估将其存储在内存中。

	  show index from 表名 //查看索引信息

	减少索引和数据的碎片:
		B-Tree 索引可能会导致碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。
		3种数据碎片：
			1.行碎片
			2.行间碎片
			3.剩余空间碎片
		optimize table 表名

    总结：
    	1.尽量避免单行查找
    	2.尽可能使用原生顺序从而避免额外的排序操作
    	3.尽可能使用覆盖索引

        如何判断一个索引是否合理？
        1.按响应时间来对查询进行分析
        2.检查这些 sql 的 schema，sql 和索引结构，判断是否扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机IO访问数据，是否有太多
          回表查询那些不在索引中的操作。
        3.如果一个查询无法从可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提升性能。如果不行，也可以看看是否能够重写查询

索引策略：
单列索引
多列索引
前缀索引和索引选择性

聚簇索引
非聚簇索引

排序
索引排序

覆盖索引
延迟关联

在服务器层， 用 where 过滤的数据已经锁定。

InnoDB 二级索引是共享锁，主键索引式排他锁

InnoDB 基于主键索引行，插入最好按自增主键插入，随机的话需要为新航查找合适的位置，写入乱序，增加页分裂，导致碎片增多
MyISAM 通过物理位置索引行， 按数据的插入顺序存储在磁盘

B- Tree 限制：
	1.不是从索引的最左列开始，就无法使用索引
	2.不能跳过中间的列
	3.查询中有某个列的查询范围，则右边的列无法使用索引查找

哈希索引(Memory索引)：
空间数据索引：
全文索引:
聚簇索引：
	优点：
		1.把相关数据保存在一起
		2.数据访问更快
		3.使用覆盖索引的查询可以直接使用叶节点中的主键
	缺点：
		1.插入速度严重依赖于插入速度，按主键插入最快
		2.更新聚簇索引的列的代价很高
		3.插入新行，或者主键被更新，可能导致页分裂
		4.聚簇索引可能导致全表扫描
		5.二级索引(非聚簇索引)可能比想象更大，因为二级索引包含了引用行的主键
		6.二级索引访问需要二次索引查找(二级索引保存的是主键值)
		

正确的创建索引：
	1.独立的列(不能为表达式)
	2.前缀索引和索引选择性(不重复的值与表记录总数的比值)

3.选择合适的索引顺序
4.将选择性最高的列放在索引前列

覆盖索引(一个索引包含所有要查询的字段)：
	1.无需回表
	2.因为索引是按顺序存储的，范围查询比随机从磁盘读取每一行io要少的多
	3.对于 InnoDB 避免了二次查询

覆盖索引必须存储列的值，所以哈希，内存不存储值。
Extra : Using index

不要查询所有的列；
只能在最左前缀匹配 like

延迟关联 ： 通过覆盖索引取得主键，再根据主键与原表关联

MySQL 有2种方式生成有序结果：
	1.排序操作
	2.按索引顺序扫描

如果 explain 的 type 为 index 说明使用了 索引顺序扫描。
扫描索引本身很快，但如果索引不能覆盖全部的查询列，就会回表，导致随机 io，因此按索引顺序读取数据的顺序比全表扫描慢。

只有当索引的列顺序与 order by 字句的顺序完全一样，并且所有列的排序顺序一样(全为正序，或全为倒序)，mysql 才会使用索引对结果排序。order by 字句和查找查询的限制一样。
当 where 字句为常量，order by 可以不满足最左前缀。

MyISAM 压缩前缀索引：减少索引大小，让更多索引可以放入内存
保存索引块中的第一个值，其他值和第一个值比较，
如，第一个值是 perform, 第二个只是  performance ,第二个压缩后的值就是 ，"7,ance"
压缩前缀倒序扫描就变慢了，因为后一个值依赖前面一个。


索引让查询锁定更少的行。

经验：
1.经常查询的，但索引选择性小的，也可以放在索引的前面几列，如sex in(m,f)等，
2.需要范围查找的，放最后
3.谨慎使用 in, 多一个in条件，组合指数增长
4.避免多范围查找

1.索引基础

2.索引的类型

哈希索引：

其他索引：

---

索引的优点：

聚簇索引优点：
	更新代价很高
	插入速度依赖插入顺序
	页分裂
	可能导致全表扫描比较慢
	二级索引需要访问2次

---

3.高性能的索引策略

1.单列索引 ：
2.前缀索引 ：
3.多列索引 ： 出现多 and ,意味着需要一个多列索引；有时候or 不如union
4.选择合适的索引顺序 

myisam: 主键索引包含主键值和物理地址，二级索引包含索引值和物理地址

innodb:聚簇索引就是表，包含了主键值，事务id,用于事务和mvvc的回滚指针，剩余列。二级索引包含主键值。

InnoDB 尽可能按主键顺序插入，不按顺序会导致页分裂，而且之前刷到磁盘的列，可能由于页分裂，被重新读取。

顺序的主键在高并发下可能会导致锁竞争。

覆盖索引：

使用索引扫描来排序：

压缩(前缀压缩)索引：

冗余和重复索引：

未使用的索引：

索引和锁：

---

---

维护索引和表：

---

https://zhuanlan.zhihu.com/p/24309634

http://www.oschina.net/question/565065_86338

http://blog.jobbole.com/24006/

http://www.cnblogs.com/oldhorse/archive/2009/11/16/1604009.html

http://blog.csdn.net/lijiaz5033/article/details/50129723

http://www.cnblogs.com/chengJAVA/p/6113691.html 覆盖索引

http://blog.csdn.net/chen199199/article/details/50535059 延迟关联

http://www.cnblogs.com/chenwenbiao/archive/2012/06/06/2537508.html select for update