MySQL之索引

0. B+ Tree

1 聚簇索引

作用：有了聚簇索引后，将来插入的数据行，在同一个区内，都会按ID值的顺序，有序地在磁盘存储数据
(1) 若表中设置了主键，则主键列就会被自动作为聚簇索引
(2) 如果没有主键，则会选择做了唯一索引的列作为聚簇索引
(3) 以上都没有，则会自动生成隐藏主键作为聚簇索引，这个字段长度为6个字节，类型为长整型
(*) 聚簇索引必须在建表时指定才有意义，一般是表的无关列（ID）

-----------------------------------------------------------------------------

建树过程：
(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚簇索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点

2 辅助索引

作用：使用普通列作为索引，对非聚簇索引列之外的查询条件进行优化，InnoDB的所有辅助索引都引用主键作为data域
建树过程：
(1) 索引是基于表中,列(索引键)的值生成的B树结构
(2) 首先提取此列所有的值,进行自动排序
(3) 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4) 然后生成此索引键值所对应得后端数据页的指针
(5) 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

回表：
1. 如果辅助索引能够完全覆盖我们的查询结果(select_list)时，就不需要回表了
2. 如果不能完全覆盖查询结果时，则会使用聚簇索引(回表)扫描，最终得到想要的结果
如何减少回表：
1. 将查询尽可能用主键查询
2. 设计合理的辅助索引(用联合索引使查询结果尽可能全覆盖)
3. 更精确的查询调节 + 联合索引
4. 优化器算法: MRR

3 聚簇索引和辅助索引构成区别

聚集索引只能有一个,非空唯一,一般是主键
辅助索引,可以有多个,是配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构
辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录（需要回表操作）

4 辅助索引的分类

• 单列索引

• 联合索引

  • 说明：使用多个列组合成一个索引，遵循最左原则
    1. 查询条件中，必须要包含最左列
    2. 建立联合索引时，一定要选择重复值少的列作为最左列

  -- 譬如创建一个a,b,c三个列组合成的联合索引时，会自动创建a、ab、abc三个索引
  -- 全覆盖情况：
  select * from t1 where a=x and b=xx and c=xxx;
  select * from t1 where a in (x) and b in (xx) and c in (xxx);
  select * from t1 where b=xx and c=xxx and a=x;
  select * from t1 where a=x and b=xx group by c;
  
  -- 部分覆盖情况：
  select * from t1 where a=x;					-- 走a
  select * from t1 where a=x and b=xx;		-- 走ab
  select * from t1 where a=x and c=xxx;		-- 走a
  select * from t1 where a=x and b(>|<|>=|<=|like)xx and c=xxx;	-- 走ab
  select * from t1 where a=x group by b；		-- 走a
  

• 前缀索引

  • 说明：针对于选取索引列值过长，导致索引树增高，可以选择大字段前面的部分字符作为索引生成条件

5 索引树高度的影响因素

原因：索引存放在页中，页的大小限制了索引的数量，当索引过大时会导致索引树增高，读取数据时需要读取更多的索引数据页。MySQL中建议索引树高度为3~4层，大约能存800w条数据

• 索引字段较长：前缀索引
• 数据行过多：分区表、归档表（pt-acrchive），分布式架构（大企业中用）
• 不合适的数据类型：选择合适的数据类型，遵循合适、够用原则
• 对不定长的字符数据用varchar可以节省索引空间

6 索引创建原则

• 按照业务需求创建合适的索引，将索引建立在经常使用 where 、group by、order by、join on 等子句的条件上
• 创建索引的字段应尽可能无重复、字段小、不经常更新
• 如果冗余索引过多，表的数据变化时有可能也会引起索引的频繁更新，会阻塞很多正常业务更新的请求
• 索引过多，会导致优化器选择出现偏差
• 尽量不要在同一个字段上建立多个索引

7 索引管理命令

-- 查询表的索引情况
-- PRI：聚簇索引、主键
-- MUL：辅助索引
-- UNI：唯一索引
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

-- 创建普通索引
mysql> alter table city add index idx_name(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 创建联合索引
mysql> alter table city add index idx_n_c(name,countrycode);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_n_c     |            1 | Name        | A         |        4001 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_n_c     |            2 | CountryCode | A         |        4056 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)

-- 建立前缀索引
mysql> alter table city add index idx_d(district(5));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_d       |            1 | District    | A         |        1224 |        5 | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

-- 删除索引
mysql> alter table city drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

8 压力测试

-- 导入100w的测试表
mysql> source t100w.sql

-- 压力测试：在未建立索引的情况下
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='780p'" engine=innodb \
--number-of-queries=200 -uroot -p -verbose
=================================================================
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 46.838 seconds
	Minimum number of seconds to run all queries: 46.838 seconds
	Maximum number of seconds to run all queries: 46.838 seconds
	Number of clients running queries: 100
	Average number of queries per client: 2

-- 建立索引
mysql> alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (2.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 压力测试：建立索引后
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='780p'" engine=innodb \
--number-of-queries=200 -uroot -p -verbose
=================================================================
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 0.086 seconds
	Minimum number of seconds to run all queries: 0.086 seconds
	Maximum number of seconds to run all queries: 0.086 seconds
	Number of clients running queries: 100
	Average number of queries per client: 2

9 索引的更新

例如 insert、update、delete 数据
对于聚簇索引会立即更新
对于辅助索引不是实时更新的
在 Innodb 内存结构钟，加入了 insert buffer(会话)，现在把那本叫 change buffer
change buffer 的作用是临时缓冲辅助索引需要的数据更新
当下一次需要查询改变后的数据使用到辅助索引时，会在内存中将原数据和缓冲的数据在内存钟merge(合并)，此时辅助索引就是最新的
r client: 2

9 索引的更新

例如 insert、update、delete 数据
对于聚簇索引会立即更新
对于辅助索引不是实时更新的
在 Innodb 内存结构钟，加入了 insert buffer(会话)，现在把那本叫 change buffer
change buffer 的作用是临时缓冲辅助索引需要的数据更新
当下一次需要查询改变后的数据使用到辅助索引时，会在内存中将原数据和缓冲的数据在内存钟merge(合并)，此时辅助索引就是最新的

---

InnoDB与MyISAM主键索引和辅助索引的结构区别