《高性能MySQL》第五章

索引优化是对查询性能优化最有效的手段
索引是在存储引擎层实现的，不同引擎索引的工作方式可能不同，不是所有存储引擎都支持所有类型的索引。没有统一的标准。
B-Tree
B+Tree，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
myisam使用前缀压缩技术使索引更小。innodb则按照原格式存储。
myisam通过数据的物理位置进行索引，innodb根据主键引用被索引的行。




B-TREE
结构
指针 key  指针
中间的key为下层叶子节点的第一个值。
索引引导列
索引的列的顺序很重要
有些查询条件会导致只能使用索引的前几列，不能跳过中间的列。




哈希索引
需要精确匹配索引所有列的查询才有效。
哈希索引将哈希值存储在索引中，同时在哈希表中保存指向每个数据行的指针
只有memory引擎显式支持哈希索引（也是它的默认索引类型）
哈希索引只需存储对应的哈希值，所以索引结构十分紧凑，这也让哈希索引查找速度非常快。
不存储字段值，所以无法避免回表。但行一般缓存到内存中，所以性能影响不明显。
不是按索引值存储，也就无法排序。
只支持等值比较
星形查询非常适合用


可以使用伪哈希索引，本质还是BTREE索引，只不过存储的是属性值的哈希值，只需要再查询条件where中指定使用哈希函数。（貌似不能设为唯一索引啊，因为有可能会有哈希冲突，不同值，相同哈希值。）
此时不需要使用太复杂的哈希函数，比如MD5等，会生成非常长字符串，浪费大量空间，比较时也更慢。
简单哈希函数即可，哈希冲突在一个可接受范围内，同时又能提供更好的性能。
最好返回整数，而不是字符串
要避免冲突问题，必须在where条件中带入哈希值和对应列值。




空间数据索引
myisam支持，无需前缀查询，可以从所有维度来索引数据。

全文索引

一般情况下，对于BLOB, TEXT或者很长的varchar，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。只需选择足够长的前缀创建索引即可，保证了较高的选择性，但又不会太长。前缀的基数应该接近于完整列的基数。

mysql原生不支持后缀索引，不过可以把字符反转后存储，然后基于此建立前缀索引。

在多个列上建立独立的但列索引大部分情况下并不能提高mysql的查询性能。
索引联合union   一般是or操作
索引相交intersection    一般是and操作


索引合并？


多列索引的顺序至关重要，关系到升序降序扫描，order by,group by,distinct等操作。
一般将选择性最高的列放到索引的最前列。但有时候也要考虑避免随机IO和排序。


聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
innodb的聚簇索引实际上在同一结构中保存了BTREE索引和数据行。一个表只能有一个聚簇索引。
节点页只包含索引列，叶子页包含了行的全部数据。
存储引擎负责实现索引，所以不是所有的存储引擎都支持聚簇索引。
如果没有定义主键，innodb会选择一个唯一的非空索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
缺点：
如果数据全放在内存中，访问顺序就不重要的，聚簇索引也就没什么优势了。
插入速度。按照主键的顺序插入是加载最快的。如果不是，则在插入之后optimize table命令重新组织一下表。
更新代价高
页分裂
二级索引可能更大，因为在二级索引的叶子节点包含了引用行的主键列。这样的策略减少了当出现行迁移或者数据也分裂时二级索引的维护工作。
二级索引访问需要两次索引查找，而不是一次。二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。自适应hash索引能减少这样的重复工作。






myisam按照数据插入的顺序存储在磁盘上。行号。
非主键索引页不会存储主键值。
都是普通BTREE索引。




innodb，如果主键列是一个列前缀索引，innodb也会包含完整的主键列和剩下的其他列。
使用innodb时，应尽可能的按主键顺序插入数据，并且尽可能的使用单调递增的聚簇键值来插入新行。
innodb_autoinc_lock_mode参数


覆盖索引：索引包含所以需要查询的字段值。无须回表。
mysql只能使用BTREE索引做为覆盖索引，因为哈希索引，空间索引，全文索引都不存储索引列的值。




按索引顺序读取数据时，每扫描一条索引就回表查询一次对应的行，基本上都是随机IO，所以通常比全表扫面要慢。


myisam使用前缀压缩来减少索引的大小。默认只压缩字符串，可以修改参数对整数也压缩。




innodb只有在访问行的时候才会加锁。而索引能减少innodb访问的行数，从而减少锁的数量。但这只有当innodb在存储引擎层能过滤掉所有不需要的行时才有效。
如果索引无法过滤掉无效的行，则innodb检索到数据并返回给服务器层以后，mysql服务器才能应用where子句，去过滤无效的行。这时已经无法避免锁定行了，innodb已经锁住了这些行，到适当的时候才释放。
5.1及以后版本，在服务端过滤掉之后就释放锁，早期版本innodb在事务提交后才释放锁。
innodb在二级索引上使用共享读锁，但在访问主键索引时，需要排他写锁。？？？？？？？？？？？？？

5.4

范围查询（> <）和多个等值查询(in ( , , ,)列表)的效率不同

对于范围查询，mysql无法使用组合索引中范围列后面的其他索引，但对于“多个等值条件查询”则没有这个限制。

check table来检查是否发生了表损坏。check table通常能找出大多数的表或索引错误。

可以使用repair table来修复损坏的表

innodb表的设计保证了它一般不会出现损坏，一旦出现了损坏，肯定是发生了严重的错误。

可以通过设置innodb_force_recovery参数进入innodb的强制恢复模式来修复数据。

mysql优化器通过两个API来了解存储引擎的索引值的分部信息，从而决定如何使用索引。

mysql的优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少航。

如果表统计信息没有，或者不准确，优化器就很可能做出错误的决定。

可以通过analyze table来重新生成统计信息解决这个问题。

innodb引擎通过抽样的方式来估算统计信息。所以不是很准确。

随机读取少量索引页，然后以此为样本计算索引的统计信息。老版innodb样本页面数是8。新版本可以通过参数innodb_stats_sample_pages来设置样本也的数量。

innodb计算索引统计信息的条件

1.表首次打开   2.anaylze table 3.表的大小发生大的变化

此外还有一些条件出发索引统计信息的更新，参数innodb_stats_on_metadata

官方的5.6版本开始，统计信息才被固化到系统表中存储，以前都是放在内存中，并不存储。

减少索引和数据的碎片

行碎片

行间碎片

剩余空间碎片

myisam这三种碎片都有可能产生

innodb不会出现短小的行碎片，因为innodb会移动短小的行并重写到另一个片段中。

可以通过optimize table或者导入导出的方式来重新整理数据。

‘