分类： 技术

聚合函数 只要读取B+TREE最左边和最右边就能拿到min 和 max的值 非常的快

检索is null

1. 索引字段的附加信息：可以分为变长和定长数据类型讨论，当索引字段为定长数据类型，比如char，int，datetime，需要有是否为空的标记，这个标记需要占用1字节；对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节;

   (备注：当字段定义为非空的时候，是否为空的标记将不占用字节)

2. 同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8的一个字符3个字节

总结， key_len的长度计算公式：

key_len是表示得到结果集所使用的选择的索引的长度，但不包括order by，也就是说，如果order by也使用了索引则ket_len则不计算在内

usering index — 覆盖索引

usering filesort — 使用filesort排序算法，对查询结果进行排序（该排序工作无法通过索引的排序直接完成，简而言之，就是要排序的列无索引）

5.6版本以上，才支持EXPLAIN DELETE/UPDATE

MYSQL explain详解

http://blog.csdn.net/zhuxineli/article/details/14455029

单列索引

多列索引

写一个存储过程 随机取10W次 每次一条记录

建议：

1. InnoDB尽可能通过主键读取记录

2. InnoDB尽可能设计一个自增长的主键

3. MyISAM尽可能通过主键或唯一索引读取记录

MyISAM耗时是InnoDB的1.06倍，InnoDB耗时是MyISAM的94%

这里没有考虑高并发情况下的对比，

因为MyISAM是表级锁 粒度比较大 并发读写

MyISAM只能把索引放到内存里     

InnoDB可以把索引和数据全部放在内存里

28原则，20%的热点数据产生80%的访问

如果把20%的热点数据都放在内存里面，这80%的访问量就会非常快

所InnoDB的优势是非常大的

InnoDB能把索引放到内存里 

主键索引

B+Tree索引

所有叶子节点高度相同

叶子节点有双向链表指向，上/下一个叶子节点

每一个entry保存了整行数据，表即索引

TID用于事物控制（锁）

RP用于MVCC（rollback pointer）

InnoDB clustered index 规则

1. 主键

2. 第一个不包含null列的唯一索引

3. 内置的rowid

辅助索引

上面是索引键值，下面是索引对应行记录

MyISAM索引用的B+tree来存储数据，索引指针指向的是键值的地址(ROW ID)，地址存储的是数据，如图MyISAM的主键，叶子节点，指向rowid

再看下MyISAM的辅助索引

MyISAM的主键的索引结构和辅助索引的索引结构从本质上来说没有任何的区别

逻辑上讲 主键索引必须有唯一性 辅助索引可能有唯一性也可能没有唯一性 

MyISAM索引结构，不管主键索引还是辅助索引，都是如此

外键作用：保证数据一致性和完整性，控制两表之间的关联性外键可以使得，外键关联的表对应列可以是随之关联的删除或者设置成空值。

外键：外表的主键，在我们这个表里用的是普通索引，在外面的表用到的主键，这样才能关联起来。

外表对应关联的列必须是主键

例：有a b两个表

a表中存有客户号，客户名称

b表中存储每个客户的订单

a是主表，b是外表

有了外键后，只能在确信在b表中没有客户x的订单后，才可以在a表中删除客户x，避免客户都被删掉了，订单还存在，导致数据不一致。

外键太麻烦，带来索引效率会降低，冲突会多些。

建立外键的前提：本表的列必须与外键类型相同（外键必须是外表主键）

指定主键关键字：foreign key（列名）

引用外键关键字：referebces<外键表名<外键列名

时间触发限制：on delete 和 on update 可设参数 cascade（跟随外键改动），restrict（限制外表中的外键改动），set NULL设空值，set Default（设默认值），【默认】no action

部分索引的原因有：

char/varchar太长全部做索引的话，效率太差

或者blob/text类型不能整列做索引列，因此需要使用前缀索引

例：alert table t1 add index（name（7））；//name列上最左边创建7个字符长度

MySQL前缀索引能有效减小索引文件大小，提高索引的速度。

但是前缀索引也有他的坏处：MySQL不能再ORDER BY 或 GROUP BY 中使用前缀索引，也不能把他们用作覆盖索引

#全列选择性

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

#测试某一长度前缀的选择性

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

覆盖索引又可以称为索引覆盖。

• 解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

• 解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

• 解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

索引覆盖举例

• 索引覆盖是指建索引的字段正好是覆盖查询条件中所涉及的字段,这里需要注意的是,必须是从第一个开始覆盖,比如:

索引字段	条件字段	有没有覆盖
a,b,c	a,b	覆盖了
a,b,c	b,c	没有被覆盖

第一行满足，第二行不满足

• 例子： select<字段A,B….> from <数据表 T> where <条件字段C>。在MySQL中建立覆盖索引采用Create index idx on T(C,A,B)，建立组合索引时，字段的顺序很重要，要将条件字段C放在组合索引的第一位，把它做为在索引的上层结构的主要排序对象，且仅有它包含统计数据，也就是非子叶层查找出符合的记录，然后在存放有其他字段记录的子叶层读取所需要的数据。

小结

• 索引覆盖可以大大提高查询速度，在大数据量的时候尤其明显。