mysql的一二级索引 mysql 二级索引原理

一：索引类型

索引影响where查询，order by排序。 分类很多：

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

二：索引原理（Innodb）

索引使用Page存储，在数据文件ibd文件中。 设计知识： 二分查找、hash和B+tree

全表扫描是把所有相关模块的数据都加入内存，使用循环找。 数据少的时候很快。

索引快要满足两个条件：

1. 时间复杂度少
2. 最重要的是磁盘IO少

二分查找树：

不用二分树的原因：

1. 虽然时间复杂度为O(log2n)，但是出现删除和增加数据后，有可能变为右边的线性树，复杂度就变成了O(n)；
2. 存储索引的数据块是存放在磁盘的，假如把每个节点看作是一个块（实际当然不是一一对应的），树越高，需要进行的磁盘IO次数就越多；

B树：

B树特点:

每个节点最多有m个孩子，称为m阶B树，如上，4阶B树(4个指针)。

对节点内采用二分法查找，关键字和data存放在一起。

相比于二分查找树，多阶B树的优点是，一个节点可以存储很多关键字（上图是两个），二分树只能存一个，这样大大减少了磁盘IO（IO次数=树的深度）

B+树：

B+树和B树的差别：

1. 非叶子节点中，关键字和指针数是一样的，，不过B+树每个块中没有data，存储的关键字更多，所以在B树的基础上，更是减少了磁盘IO。
2. 子结点中关键字的值是可以等于当前的关键字的，如上图中每个关键字值在子节点中都有
3. 数据都是存在叶子节点中，非叶子节点只作索引，意味着每一次查找都要从根节点到叶子节点才结束。所以查询效率更加稳定，时间复杂度都一样。
4. 叶子节点之间用链指针相连， 这样可以作范围统计，当定位到某个节点后，就可以从该节点横向作统计，而不用重新从根节点遍历， 这样大大提升了性能。

hash索引：

hash最快，直接根据key的hash值找到data。 什么叫做自适应hash呢，其实也是普通hash索引，只是InnoDB根据普通索引的访问频率和类型，自动为热点页索引创建的，叫做自适应。 hash需要精确值，而且存在碰撞问题，所以一般只适合于键值唯一的等值查询。

hash索引不支持范围查询，这是为什么呢 ？
是因为Hash操作并不能保证顺序性，所以值相近的两个数据，Hash值相差很远，被分到不同的桶中。

hash 索引无法做 like ‘xxx%’ 这样的部分模糊查询，这是为什么呢？
因为需要对 完整 key 做 Hash 计算，定位bucket，而模糊查询的时候，key不完整，无法用key定位到对应的桶找到对应的hash值。

hsah索引无法进行排序，这是为什么呢？
通过key计算出来的hash值，Hash值的大小关系并不一定和Hash运算前的键值完全一样。

hash索引不支持多联合索引，这是为什么呢？
多联合索引是根据最左匹配原则进行，而hash索引会将多列合并算出hash值，从而导致联合索引无法被利用。

hash索引存在hash碰撞，当重复的键过多的时候就会效率大大降低，不稳定

hash索引不是我们自己控制的，而是由数据库自身创建并使用，我们不能够对他进行干预

其他解释:

聚集索引（聚簇索引）： 索引值和data存放到一起。

非聚集索引（非聚簇索引）：索引值和data 分开放。

主键索引：主键为索引，和data一起。

辅助索引：索引值对应的data为主键，通过主键再找数据。

三：索引分析和优化

explain:

EXPLAIN SELECT * from user WHERE id < 3;

select_type：

• SIMPLE ：简单SELECT，不使用UNION或子查询等
• PRIMARY：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY
• UNION：UNION中的第二个或后面的SELECT语句
• DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
• UNION RESULT：UNION的结果
• SUBQUERY：SELECT子查询语句
• DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果

type：

• ALL：表示全表扫描，性能最差。
• index：表示基于索引的全表扫描，先扫描索引再扫描全表数据，这种情况下，是有序扫描，比ALL稍快。
• range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
• ref：表示使用普通索引进行单值查询。
• eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，在后面的表中只能查到一条记录
• const：表示使用主键或唯一索引做等值查询，常量查询。
• NULL：表示不用访问表，速度最快。

possible_keys：

• 表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

key：

• 表示查询时真正使用到的索引，显示的是索引名称。

rows：

• MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。

Extra：

Extra 表示很多额外的信息，各种操作会在 Extra 提示相关信息，常见几种如下：

• Using where ：表示查询需要通过索引回表查询数据。 普通索引先找到id，根据id回表查询。
• Using index ：表示索引本身就可以满足所需数据。 覆盖索引
• Using fifilesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using fifilesort建议优化

联合索引:

• 多个列组成的索引，因为多个单列索引在多条件查询时只会生效第一个索引，所以多条件联合查询时最好建联合索引。
• 最左匹配原则，如key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找（a,c,b，不管顺序如何，也会被执行优化器解析为a,b,c），但不支持 b,c进行查找，a,c只能用到a

LIKE查询：

• select * from user where name like '%o%';   //不起作用
• select * from user where name like 'o%';    //起作用
• select * from user where name like '%o';   //不起作用

索引与排序：

mysql的两种排序：

• filesort：查出结果，在缓存或磁盘进行排序，效率低。
• index：使用索引排序，  当order by中能使用到索引时：

• where中没有使用其他索引，order by有用到索引
• order by字段和where中的字段是联合索引，满足最左匹配，且where 中没有使用如>、<、in等范围条件
• 如果order by中多个字段，顺序要相同（asc,desc，不能有的字段使用asc，有的字段使用desc）
• where 和 order by中没有使用函数表达式

索引失效情况：

因索引是将值进行比较后排序生成的树来存储，所以如果值不确定时，，索引会失效

• 单列存储null 值
• 查询使用is null 或者 is not null，索引是根据顺序查找，null没有顺序
• 区分度很小
• like查询中，以%开头
• or查询，，可以用union (all)代替
• 使用 != ，+，- 等运算符
• 使用函数表达式如to_date()；
• 字符串没有用单引号
• 数据量太小，使用全表扫描更快
• 联合索引中，不满足最左原则，，或者在 > < between and 之后的列

四：查询优化

慢查询优化：

参数介绍：

• slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
• slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
• long_query_time ：慢查询阈值，当查询时间多于设定阈值的sql，会记录到日志。
• log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
• log_output：日志存储方式。

• （log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中，但是文件的性能更好）

日志分析工具：

mysqldumpslow-自带工具（统计信息较少，只能看到基本的一些情况）

•    -s, 是表示按照何种方式排序
•     c: sql被访问的次数
•     l: 执行sql时锁定的时间
•     r: 执行sql返回记录数
•     t: sql的查询时间
•     al:平均锁定时间
•     ar:平均返回记录数
•     at:平均查询时间
•    -t, 是top n的意思，返回按前面排序后的前n条信息；
•    -g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如:

得到返回记录集最多的10个SQL。

• mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

• mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。

• mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

• mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

分页查询优化

先来看两种情况：

• select * from user limit 10000,1;
• select * from user limit 10000,10;
• select * from user limit 10000,100;
• select * from user limit 10000,1000;
• select * from user limit 10000,10000;
• 记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多

• select * from user limit 1,100; select * from user limit 10,100; select * from user limit 100,100; select * from user limit 1000,100; select * from user limit 10000,100;
• 偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（mysql分页查询机制，每次都会从第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）

利用覆盖索引优化：

比如先查出id，再根据id作为条件查询。

补充: in 和 exists的差别

先说结论：外表大内表小，使用in。   反之，使用exists

in的机制是： 先将内表记录查出来并缓存（n条记录），再将外表记录查出来（m条记录），然后内存里循环比较，循环次数: m * n

exists的机制是： 先把外表记录查出来（m条记录），然后循环每一条外表记录，带着关联条件去看内查询是否有值（true 或 false）。

举例： 查询有订单的用户信息

1、 有100个用户，每个用户有10个订单。 

使用in查询： select * from user where id in (select userid from order)；   查询2次数据库（内表和外表各一次），循环 100 * 10 次作比较。

使用exists： select * from user  u where exists (select * from order where o.userid = u.id);  查询101次数据库（外表1次，有100个用户记录，循环每个用户带着用户id去查询内表订单）。

2、当一段时间后，每个用户有1000个订单。

使用in查询： select * from user where id in (select userid from order)；   查询2次数据库（内表和外表各一次），循环 100 * 1000次作比较。

使用exists： select * from user  u where exists (select * from order where o.userid = u.id);  查询101次数据库（外表1次，有100个用户记录，循环每个用户带着用户id去查询内表订单）。

比较1和2，当内表查询记录增多时，使用in的循环次数越来越大，效率越来越差，而exists查询次数不会变（以外表为基准）。   这说明，当内表记录大时适用exists，反之，使用in查询效率高。