MySQL索引基础

MySQL索引基础

1. 二分查找法与平衡二叉树
2. b+tree结构
3. 索引的利弊与定义

1. 二分查找算法与平衡二分树

二分查找法前面简单的提了一下，就是每一次查找的时候就会先查找中间值，然后进行值的判断，小于往左找，大于往右找，因此减少查找的数量

但是实际上二叉树它有很多种方式

如上图中的方式我我们也可以称之为二叉树，但是这种二叉树的方式效率比较低，不满足二叉树的定义。
平衡二叉树的查找性能是比较高的，通常来说，需要1次或则更多次的左旋和右旋来得到插入或更新后树的平衡性。

2、B+tree的结构

btree是MySQL最长用的一种类型，它每个节点都会存储对应的数据
B-tree是innodb使用的一种树的类型，它是从B-tree演变而来，而B+tree的数据是存储再根节点中，而不是每个节点都会存储数据

B+tree是再二叉树的基础上演变来的，B+tree的定义实际上是比较复杂的，B+tree视为磁盘或其他直接存取辅助设备的设计的一种平衡查找树，再B+tree中，所有记录节点都是按键值的大小顺序存放同一层的叶子节点上，有个叶子节点指针进行链接

b+tree回根据指定的索引列的值排序：5,10,15,20,25,30,50,55,60,65,75,80,85,90

2.1：主索引和辅助索引

MySQL再建立索引的时候，例如一个索引有多个重复的值，首先会去从，如果索引是无需的先会排序并创建一棵树，然后将对应索引的值数据存放再页中，如果select后面需要查询的字段数据，再索引对应中找不到该字段，就会进行回表操作

再innodb存储引擎中，每一个索引都对应一颗b+tree，innodb的索引主要分为主索引和辅助索引

1. 主索引：包含记录的文件按照某个key指定的顺序排序，这个key就是主索引，也就是主键，也被称为聚簇索引，因为无法同时把数据行存放再两个不同的地方，所以一个表只能有一个聚簇索引，再innodb中，主索引的叶子节点存的是整行数据（存放的是主键索引对应的数据）这也innodb中的表一定要有一个主索引（主键索引）。
2. 辅助索引：某个key指定的顺序与文件记录的物理顺序不同，这个key就是辅助索引，**innodb中的辅助索引再叶子节点中并不存储实际的数据，只包含主键的索引id（主索引存储的是对应的数据，而辅助索引存储的是主键的id，然后根据主键id回表查询数据）**这就意味着如果使用辅助索引进行数据的查找，只能查到主索引，然后根据这个主索引再次扫描一下主索引的树，进行一次回表的操作

Innodb的表要求必须有一个主键，那么可能有人会将身份证号这种唯一性的标识作为索引，这样做就大错特错，刚刚说到主键也被称为聚簇索引，它是要按照顺序进行排序（主键必须是有序的）如果将身份证号作为主键，不能保证每次插入数据都是按照身份证的顺序进行排序（每次新增心数据的索引，都会改变索引树的结构，比如插入一个性别为女的数据，它会再性别女的值id索引树种插入这数据，如果是有序的，直接添加，无序的还需要进行一步排序操作）这就使得每次主键的插入都变得随机，可能导致每次插入一条数据都会引起页分裂的问题，所以再表结构定义的时候，应该使用一个聚集性的key作为索引，如果真的没有的话，可以使用一个 auto incrment 代理作为主索引。

2.2 联合索引的结构假设如下就是我们的数据表数据 user

假设给age和gender建立联合索引：

# 给user表建立索引
alter table user add index name,age);

那么对应的数据结构（建立成功后，会把对应索引值以及主键id存储再叶子节点种）

联合索引再查询的时候，比如要找Alice，这两条记录， where name = ‘alice’ and age = 100
MySQL会先根据name查询找到了两条数据，然后再根据age找到 id = 2的这条数据（最总是找到索引id拿到数据）

解释B+tree的特点，自增主键是连续的，再插入过程中尽量减少页分裂，即使要进行页分裂，页只会分裂很少一部分，并且能减少数据的移动，每次插入都是插入到最后，总之就是减少分裂和移动的频率。

2.3：回表

回表主要是因为与使用辅助索引，但是因为所需要的数据不再辅助索引中而产生的回表情况（其实就是，如果select 后面查询的字段列，不再索引中，就会引起回表操作）针对于之前的SQL分析一下：

select count(*),avg(monthsalary)  from customers1 where gender = 0;
alter table customers1 add index gender(gender); // 添加gender索引

再上面已经给gender建立了索引，但是再使用explain分析的是extra为空（就是回表操作）再使用gender索引进行查询时大致可以分为两个步骤

1. 从索引gender对应的B+tree中取出gender值为0的记录数据（建立索引的时候，已经保存了gender索引值以及主键id）
2. 但是因为gender对应的B+tree的记录中只包含了gender这个字段（因为只存储了索引值以及主键id）而查询中需要monthsalary（而再select后面又要查询 monthsalary 这个字段值）这就意味着该查询就没办法直接通过使用gender所记录的数据（而这样就无法直接通过gender来查询这条记录，这条记录没有包含monthsalary这字段值）这时需要从上一步中获取到每一条记录的id字段对应到的聚簇索引对应再B+tree中找到完整的用户记录，也就是我们通常的索引的回表(会根据索引id从新进行查询并返回)
   
   
   根据第一步种获取到的记录的id字段的值并不相连，而聚簇索引中的记录是根据id（也就是主键）的顺序排列的，索引根据这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布再不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机I/O，一般情况下，顺序I/O比随机I/O性能高很多，索引步骤1的执行可能很快，而步骤2就慢一些，所以这个索引gender的查询有这么两个特点：

1. 会使用两个B+tree索引，一个二级索引，一个聚簇索引，访问二级索引使用顺序I/.O，访问聚簇索引使用随机I/O
2. 需要回表的记录越多，使用的二级索引的性能就 越低，甚至让某些查询使用全表扫描也不使用二级索引，比方说gender值为0的用户记录数量占全部记录数量90%以上，那么如果使用gender索引的话，有90%多的id值需要回表，还不如直接扫描聚簇索引

优化器会实现对表中的记录计算一些统计数据，然后根据这个信息判断回表的记录数，如果回表很多就可能会改变全表扫描，通常来说是回表越少越好。

建立的联合索引customers1的数据情况如下图：

这样创建之后就称为了覆盖索引 unsing index，对于没有事情来说就直接从索引中获取到了我们所需要的数据，不行回表以及从数据表中获取（上图建立索引的时候，一并把monthsalary索引一起存储到叶子中）

3. 索引的利弊于定义

索引好处：可以提高对于数据查询的效率，降低数据库的Io，同时也会提高排序和分组对于CPU资源的消耗

索引弊端：当创建一个索引之后，比如table创建了要给idx_city 的索引，这个时候，当数据表table内容一旦发生了改变之后，MySQL就会重新调整索引的结构，如果数据库的字段是一些字符串类型可能调整会稍微的一点点，并且是每一个索引都会相应的调整

3.1:挑选索引- 建立的一些规则

1. 索引应该创建在搜索、排序、分组等操作所设计的数据列上，只会输出报告里出现的数据列不是好的候选，换句话说，哪些where子句、关联检索中的from子句、order by 或 group by 子句中出现过的数据列最适合用来创建索引，只在select关键字后面的输出列清单里出现过的数据列不是好的选择，

select
    col_a                    - 考虑
from
    tb1 left join tb2
on
    tb1.col_b = tb1.col_c    - 推荐
where
    tb1.col_b = xxx          - 推荐

2. 尽量使用唯一化索引，数据值在数据列中的分布情况是一个很值得考虑的因素，建立在惟化数据列（即数据列中的取值各不相同）上的索引有着最好的效果，如果数据列里很多彼此重复的值，建立在其上的索引就不会有好的效果，比如说，如果某个数据列里存放着很多各不相同的年龄值，建立在其上的索引就能把不同的数据里很好的区分开，如果某个数据列里存放的是用来表示性别的M和F两种，建立在其上的索引恐怕就帮不上多大。如果数据值的分布比较均匀，那么，不管使用哪个值（M或F）进行搜索都回匹配到大于50%的数据行，在这种情况下，MySQL可能根本就不会使用建立在这个数据列上的索引-- 一旦查询优化程序发现某个数据值超过30%的数据行里都有出现的时候，它通常回放弃使用相关的索引而进行一次全表扫描.

备注：这里需要注意的并不是说性别就不能创建索引，而是指不推荐单独创建索引，但是我们可以创建一个唯一的包含性别的唯一索引。比如：select count(*),avg(monthsalary) from customers1 where gender = 0;就可以创建一个idx_gender_monthsalary的索引

性别相关的查询在系统中还是会比较多的并且在联合索引中加上列没有太大的问题，索引创建索引页可以但是切记每次要单独创建一个性别的索引，不过我们还是很多情况下不会使用到性别

列如以下：

set profling = 1;

mysql> select count(*),avg(monthsalary) from customers1 group by city;
11 rows in set (6.76 sec)


show profiles; 查看sql执行记录
+----------+------------+----------------------------------------------------------------+
| Query_ID | Duration   | Query                                                          |
+----------+------------+----------------------------------------------------------------+
|        1 | 6.76294425 | select count(*),avg(monthsalary) from customers1 group by city |
+----------+------------+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

# 根据查询id查询sql执行记录
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000070 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000877 |
| init                 | 0.000022 |
| System lock          | 0.000008 |
| optimizing           | 0.000003 |
| statistics           | 0.000010 |
| preparing            | 0.000008 |
| Creating tmp table   | 0.000023 |
| Sorting result       | 0.000004 |
| executing            | 0.000002 |
| Sending data         | 6.761648 |
| Creating sort index  | 0.000107 |
| end                  | 0.000006 |
| query end            | 0.000011 |
| removing tmp table   | 0.000011 |
| query end            | 0.000005 |
| closing tables       | 0.000011 |
| freeing items        | 0.000092 |
| cleaning up          | 0.000021 |
+----------------------+----------+
20 rows in set, 1 warning (0.00 sec)

然后使用explain 检测SQL的执行使用了什么，
会发现查询时间特别的长

会发现使用了全表扫描，并没有使用到相应的索引idx_gender_monthsalary ，而看表使用的索引是有索引存在的

但是我们可以通过一个技巧给操作用上idx_gender_monthsalary 这个索引，需要修改一下SQL，其实就是用上了where gender in

select count(*),avg(monthsalary) from customers1 where gender in (0,1) group by city;

其实也就快了1秒

可以看到感觉得到效率是有一定的提升，这主要因为查询的条件不限制性别，我们可以通过 添加 where gender in 的方法来让MySQL选择索引，这样并不会过滤任何行，不过上面的方法也有一定的问题，就是 in() 列表太持仓不是很合适

这个案例显示了一个基本原则，考虑表上素有的选项，当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化，如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否优化原来的查询，应该同时优化查询和索引找到最佳的平衡，而不是闭门造车去设计最完美的索引

但是我们可以修改一下这个索引为（gender,city,monthsalary）

alter table customers1 drop index idx_gender_monthsalary;
alter table customers1 add index idx_gender_city_monthsalary(gender,city,monthsalary);

试试优化后的效果执行时间

在这里插入代码片

再试试之前的

这里不会影响到

上面根据城市查询都是最快的速度，city也成功添加了索引

3. 尽量对比较短的并且有序数字的值进行索引，当对一个字符串数据列进行索引的时候，只要有可能，就应该指定一个前缀长度，比如说，假设一个char(200)数据列，那么，如果大多数数据值的前10个或前20个字节时彼此不同的，就不要对整个数据列进行索引 ， 只对前10个或前0个字节进行索引可以节省大量的空间，而且会使查询进行的更快，比较短的索引需要的磁盘IO操作比较少，对他们进行比较的速度也更快，更重要的使、键值阅读，索引缓冲区容纳的键值也就越多，而MySQL同时保存在内部里的索引项越多。