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 辅助索引的分类
-
单列索引
-
联合索引
- 说明:使用多个列组合成一个索引,遵循最左原则
- 查询条件中,必须要包含最左列
- 建立联合索引时,一定要选择重复值少的列作为最左列
-- 譬如创建一个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(合并),此时辅助索引就是最新的