mysql索引

mysql底层采用b+树的数据结构存储数据：

数据只存储于叶子节点，基于mylsam存储引擎则存储的是数据地址，而基于innodb存储引擎的则存储的是索引行的其他数据。

1. 之所以不用树形结构因为其在某些特定情况下会退化为链表
2. 之所以不用红黑树是因为其在存储数据时会导致树的深度过深，导致叶子节点查询较慢，且因其为链式存储所以会导致大量磁盘io，所以采用了b+树的结构。其每层存储16kb的数据。树的第一层长期在内存中。
3. 看到了此数据结构最底层叶子节点和节点之间有一个箭头，其实应该是一个双向指针，目的就是为了当我们执行where num>3 and num<5类似这样的语句的时候避免重新查找，有这指针就可以直接顺序读取了。

mylsam存储引擎

首先不论是mylsam还是innodb其针对的都是数据库表的。

可以看见其叶子节点存储的是数据地址，而且我们在mysql的data文件夹下可以找到其对应的存储文件有三个（如果有索引的化）：

1. 以frm结尾的文件为表结构文件
2. 以MYD结尾的文件为数据
3. 以MYI结尾的文件为索引文件
   其查找过程先查看MYI有无，有则按照索引查找到数据地址，在去myd文件查找数据。所以其叶子节点存储的是数据地址。

innodb存储引擎

inndb叶子节点存储的四是除索引的本行其他数据。查看其文件夹可以看到两种类型文件分别为：

1. frm结尾的同mylsam
2. ibd结尾的文件存储的是数据结构+数据
   其实由上面的图既可以看出只有两个文件的原因，就是因为其叶子节点存储的不是数据地址了而是数据，所以在一个文件。

聚集索引和非聚集索引

聚集索引：叶子节点包含了完整的数据记录例如innodb
非聚集索引： 数据索引和数据分开存储。例如mylsam

为什么innodb表必须有主键，并且推荐使用整形自增主键

• 首先解释一下为什么必须有主见，因为innodb在底层存储数据时采用了b+树的形式，那么就必须有一个构成树的依据例如我们一开始图中的数字8、15…，依据这些数的大小来构成b树，如果我们提够了主键那么就会用主键作为构成树的依据，若没有主键那么数据库会从我们的字段中找一个符合唯一不重复，如果找不到那么数据库会自己维护一个。所以最好我们自己定义主键。
• 为什么推荐使用整形呢大概有两个理由：
  1、因为在构成树的时候避免不了比较大小，那么数字比较大小坑定大于字符串比较大小。
  2、数字占字节数比较小相对于字符串
• 为什么推荐用自增主键，因为b+树的最后一排叶子节点我们可以看到是按照从小到大的顺序存储的，现在我们假设存储的是1、2，4，5、6那么当我们在插入3的时候b+树为了保持平衡就会进行节点调整，导致增加了操作，所以为了避免其调整最好用自增主键。

联合索引

1. 什么是联合索引如下图：
   
   图1
   如上图我们看到其索引不只是有编号还有名字和日期，如上才有多个数据作为b树构建依据的就是联合索引。
2. 为什么要用联合索引：
   1、一个联合索引相当于多个索引即（1当3），例如联合索引 a,b,c其相当于a，a-b，a-b-c三个所以。因为每多一个索引，都会增加写操作的开销和磁盘空间的开销
   2、覆盖索引，我们要查找的数据有的时候可能就在我们的联合索引中那么就不需要在回表了。如果我们的表有联合索引a，b，c加入执行sql: select a,b,c from table where a=1 and b = 1。因为要查找的条件a=1，b=1就在我们的索引中能找到，那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略
   3、因为索引的条件多了那么必定缩小的范围就更细了，在一个更小的范围内查找那么必定提高速度。
3. 怎么创建联合索引呢：

. CREATE TABLE `test` (
	'aaa' VARCHAR (16) NOT NULL DEFAULT '',
	'bbb' VARCHAR (16) NOT NULL DEFAULT '',
	'ccc' INT (11) UNSIGNED NOT NULL DEFAULT 0,
	KEY `sindex` (`aaa`, `bbb`, `ccc`)
) ENGINE = MyISAM COMMENT = '';

这样就在 aaa、bbb、ccc 3列上建立联合索引了。

如果表已经建好了，那么就在phpmyadmin里面执行：

alert table test add INDEX `sindex` (`aaa`,`bbb`,`ccc`)  

就可以在这3列上建立联合索引了。

4. 何时才会使用联合索引：举例的sql语句样板如图1

1、 select id,name,time from table where id=1001;

	这个会走索引。

2、select id,name,time from table where name=staff;

	这个不会走。

3、select id,name,time from table where name=staff and id=1001 and time=2017-6-7;

这个也会走。

我们发现了要想走索引要么条件是id，要么是id+name，要么是id+name+time，而不能是time+name、或者time等。即如果联合索引为a，b，c那么只能是a或者a+b或者a+b+c。要满足这个顺序先满足了第一个才能去看第二个而不能跨级。第三个sql虽然写的时候并没有按照索引顺序是因为mysql给我们优化了。