Mysql索引

定义：索引是帮助数据库获得数据的排好序的数据结构

 

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变成顺序I/O

索引只有帮助存储引擎快速查找到记录，带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，就不适合索引。因为全表扫描来的更直接，索引还需要维护，开销也不小。

 

而对于特大型的表，建立和使用索引的代价随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录。例如可以使用分区，或者可以建立元数据信息表等。对于TP级别的数据，定位单条记录的意义不大，索引经常会使用块级别元数据技术来替代索引。

 

一些数据结构分析

• 二叉树：查找速度有一定提升，缺点是当索引列顺序递增是会退化为链表，失去作为索引的意义
• 红黑树：自动平衡的二叉树，解决了普通二叉树退化链表的问题，但是大量数据造成树的深度过高，查找效率依然不够
• B-tree：一个节点存储多个数据，并可以有多个子节点。叶节点的深度相同，指针为空。所有索引元素不重复，节点中的数据索引从左到右递增。
• B+tree：对B-tree的优化。可以理解为多叉平衡树。非叶子节点不存储data，只有冗余的索引，这样每个节点就可以存储更多的索引。叶节点包含所有的索引，之间存在指针，提高区间访问性能，方便范围查找
• Hash：存储数据的hash值，对于精确查询性能非常高，但是不支持范围查询。 

基本上都会采取B+tree的数据结构构建索引。mysql中索引每个节点设置为16kb，按照bigint大小8byte，加上指向子节点的指针6byte，计算可得每个节点能存储1170个索引。叶节点存储索引+data，按照每个1kb计算可存储16个元素。按照这个数据来计算，深度为3的B+tree索引就可以管理2000W+的数据。

存储引擎

每个表可设置不同的存储引擎。

myisam

底层存储表文件有三部分，frm（表结构），MYI（索引），MYD（数据）。索引文件和数据文件是分离的（非聚集）

innodb

底层存储文件两部分，frm（表结构），ibd（索引加数据）。表数据文件本身就是按B+tree组织的一个索引结构文件。叶节点包含了完整的数据记录（聚集索引）

innodb引擎有一个特殊的功能“自适应哈希索引”，当innodb注意到一些索引值被使用的非常频繁时，且符合哈希特点（如每次查询的列都一样），它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的，内部行为。

如果一张表是innodb存储引擎但是没有设置索引，那么数据库会从左至右挑选一个合适的列来组织数据文件，没有合适的字段会后台生成一个列。

聚集索引

最好使用自增长的int类型，乱序有如下缺点。

• 写入的目标页可能已经刷新到磁盘上并从缓存中移除，或者还没有加载到缓存中，这样innodb在插入前不得不先找到并从磁盘读取目标页到内存中。导致了大量的随机I/O。
• 因为写入是乱序的，innodb不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
• 由于频繁的页分裂，页会变得稀疏被不规则地填充，所以最终数据会有碎片。

联合索引

索引最左前缀原理 

在一个多列BTree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列等待。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY ,GROUP BY,DISTINCT等子句的查询需求。

当不需要考虑排序和分组时，可以考察该列的离散性，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化where条件的查询。

无法使用索引

• 索引列进行数学运算或函数运算
• 未遵守最左前缀原则
• or条件后一列没有索引
• where条件使用not <> !=
• 字段类型不匹配；