mysql 相关总结

mysql 常用的操作这里就不详细说了，可以参考：MySQL常用操作

我们常用的是innoDB引擎，存储结构是每一行数据一个节点，节点与节点之间相互连接。

B+Tree索引能做到范围查找，而哈希索引做不到。

索引的数据结构：

B+Tree ：B+ 树的能够保持数据稳定有序，其插入与修改有着较稳定的时间复杂度。

磁盘的IO取决于B+树的高度；当B+树的数据项是复合的数据结构时，如（name,age,id）B+树是按照从左到右的顺序建立搜索树的。

覆盖索引：

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。那就不是覆盖索引了

mysql不能在索引中执行LIke操作。mysql能在索引中做最左前缀匹配的like比较，但是如果是通配符开头的like查询，存储引擎就无法做比较匹配。这种情况下mysql只能提取数据行的值而不是索引值来做比较，所以不建议这样使用。

聚簇索引：

聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面直接存储数据。

InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

为什么用B+树这种结构来实现索引呢？？
红黑树等结构也可以用来实现索引，但是文件系统及数据库系统普遍使用B+树结构来实现索引。mysql是基于磁盘的数据库，索引是以索引文件的形式存在于磁盘中的，索引的查找过程就会涉及到磁盘IO消耗，磁盘IO的消耗相比较于内存IO的消耗要高好几个数量级，所以索引的组织结构要设计得在查找关键字时要尽量减少磁盘IO的次数。为什么要使B+树，跟磁盘的存储原理有关。

为什么mysql的索引使用B+树而不是B树呢？？
（1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
（2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为int（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（10^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2_{4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

下面来看例子：

A、索引中的数据的顺序。

假如，现在有idx_abc(a,b,c)，则它的真实情况是这个样子的。

数据表中的原生数据顺序
索引中的数据顺序
id a b c
a b c id
1 a1 b1 c1
a1 b1 c1 1
2 a2 b1 c1
a1 b1 c2 7
3 a2 b2 c1
a1 b2 c1 4
4 a1 b2 c1
a1 b2 c2 6
5 a2 b1 c2
a1 b2 c2 8
6 a1 b2 c2
a2 b1 c1 2
7 a1 b1 c2
a2 b1 c2 5
8 a1 b2 c2
a2 b2 c1 3

有了索引，就可以替代where等了。

where a = ? and b > ? and c = ?，可以用上索引的a、b、c哪几列？

同理，能思考一下order by吗？

where a = ? and b = ? order by c

where a =? and b> ? order by c

where a =? order by c

理解索引片的概念，理解连续=会让索引片内有序。

B、二次回源。

索引里没有整行数据，因此，如果在select语句里如果有的列没有的话，会引起二次回源。

假如，现在有idx_abc(a,b,c)。

select c from tbl where a = ? order by b;【彻底不需要二次回源】

select c,d from tbl where a = ? order by b;【在返回结果时需要二次回源】

select c from tbl where a = ? and d = ? order by b;【在查询阶段就需要二次回源】