-
介绍
索引相当于一本书中的目录.起到优化查询(where order by group by …)目的. -
类型
Btree : b-tree,b+tree(b+tree,b*tree),Balance-tree
Rtree
HASH
fulltext -
btree细分
聚簇索引
辅助索引: 单列,联合,前缀…
唯一索引 -
索引的管理命令
4.0 索引建立之前压测:
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query=“select * from test.t100w where k2=‘VWlm’” engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 627.508 seconds
Minimum number of seconds to run all queries: 627.508 seconds
Maximum number of seconds to run all queries: 627.508 seconds
Number of clients running queries: 100
Average number of queries per client: 20
4.1 查看索引
desc world.city;
Key
PRI —> 主键索引(聚簇索引)
MUL —> 辅助索引
UNI —> 唯一索引
mysql> show index from world.city;
±------±-----------±------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
±------±-----------±------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| city | 0 | PRIMARY | 1 | ID | A | 4188 | NULL | NULL | | BTREE | | | YES | NULL |
| city | 1 | CountryCode | 1 | CountryCode | A | 232 | NULL | NULL | | BTREE | | | YES | NULL |
±------±-----------±------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
Table : 表名
Key_name : 索引名
Column_name: 列名
Cardinality :基数 ,不重复的值的个数(根据统计信息,采样获得的错略值).越大越好.
量化方法: Cardinality/总行数 建议 80% 以上
4.2 索引创建
a. 单列索引
mysql> alter table world.city add index i_pop(population);
b. 联合索引
mysql> alter table world.city add index i_c_p(countrycode,population);
c. 前缀
mysql> alter table world.city add index i_name(name(10));
d. 主键索引
mysql> create table aa (id int);
mysql> alter table aa modify id int not null primary key auto_increment;
d. 唯一索引
mysql> alter table aa add telnum char(11);
mysql> alter table aa add unique index i_tel(telnum);
4.3 删除索引
mysql> alter table aa drop index i_tel;
4.4 索引建立之后压测:
mysql> alter table test.t100w add index i_k2(k2);
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query=“select * from test.t100w where k2=‘VWlm’” engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 7.460 seconds
Minimum number of seconds to run all queries: 7.460 seconds
Maximum number of seconds to run all queries: 7.460 seconds
Number of clients running queries: 100
Average number of queries per client: 20
- B+tree查找算法介绍
5.1 平衡
不管查找哪个数,需要查找次数理论上是相同的.对于一个三层b树来讲,理论上查找每个值都是三次IO.
5.2 擅长范围查找
讲究快速锁定范围.
B+tree,加入了双向指针(头尾相接),进一步增强范围查找.减少对于ROOT和NON-LEAF的访问次数.
5.3 构建过程
叶子: 先将数据排序,生成叶子节点.
枝 : 保存叶子节点的范围(>=1 <5)+指针(→)
根 : 保存枝节点范围+指针
叶子节点和枝节点都有双向指针.
- MySQL 索引如何应用Btree算法
6.1 名词认识
page: 数据页,默认16KB
extent: 区(簇),默认是1MB,连续的64Pages
IOT : 索引组织表.MySQL在存数据时是按照索引(聚簇索引)组织和存储.
6.2 MySQL 索引如何应用Btree ******
6.2.1. 聚簇索引
a. 介绍
clustered Index ?
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
b. 聚簇索引构建过程
IOT组织表:
存储数据时,数据行会按照ID逻辑有序的,在连续的page上进行存储(同一个区内的数据可以保证物理有序)
1.叶子leaf
数据行所在的数据页,构成了叶子节点.
2. non-leaf 内部节点
叶子结点的ID值范围+指针
3. root
no-leaf ID范围+指针
c. 聚簇索引能够起到哪些优化效果.
6.2.2 辅助索引
a. 介绍
Secondary Indexes ?
All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.
For guidelines to take advantage of InnoDB clustered and secondary indexes, see Section 8.3, “Optimization and Indexes”.
b. 辅助索引构建过程
alter table t1 add index idx(name)
- 从原表中获取:索引列(name)+ID值
- 安装索引列值(name)从小到大排序,生成叶子节点中.
- 枝节点:叶子节点的name范围+指针
- 根节点:枝节点name的范围+指针
c. 如何起到优化查询的效果
基于name 列进行条件时.
- 根据name列的条件值,在辅助索引扫描,获取到ID
- 拿着ID回表查询,最中获得想要的数据页
6.2.3 联合索引
idx(name,age)
a. 构建过程
叶子节点: 获取ID+name+age ,按照name和age组合排序. 将有序的值存储到连续的数据页中.
枝节点 : 获取叶子节点name列值范围+指针.
根节点 : 获取枝节点 name值的范围+指针.
b. 如何提供查询优化
例如:
where name = and age=
- 按照name条件值,扫描根节点和枝节点,找到叶子结点.
- 根据叶子节点内容在做age 条件过滤,最终获得ID
- 回表查询,根据ID扫描聚簇索引,最终得到数据页.
c. 联合索引的最左原则
- 建立联合索引时,选择基数大的作为最左列.
- 查询条件中必须包含索引中的最左列
彩蛋: 如何判断列的基数
mysql> select count(distinct num) from t100w;
±--------------------+
| count(distinct num) |
±--------------------+
| 577046 |
±--------------------+
mysql> select count(distinct k1) from t100w;
±-------------------+
| count(distinct k1) |
±-------------------+
| 1225 |
±-------------------+
6.3 回表的问题
6.3.1 什么是回表查询?
从辅助索引扫描完之后,再根据ID聚簇索引扫描的过程.
6.3.2 回表会带来什么影响?
a. IO 增多.
IO 指标?
IOPS? —> 每秒IO的次数,定值.
吞吐量? —> 300M/s
b. 随机IO
6.3.2 怎么减少回表?
a. 索引覆盖
b. 精细化查询条件+合理的联合索引
c. 调整优化器算法.
6.4 索引树高度
一般3层B+tree,可以存储2000w左右数据.建议4层以内
6.4.1 影响因素
数据行数多
索引长度过长
主键值过长
6.4.2 解决方案
a. 分库分表(分区表,中间件),数据归档(pt-archiver)
b. 数据类型合适简短的,前缀索引
c. 规划简单主键.
- 执行计划
7.1 介绍
explain:
SQL在执行时,优化器优化后,选择的cost最低的执行方案.
7.2 获取
mysql> desc select * from t100w where k1=‘aa’;
±—±------------±------±-----------±-----±--------------±-----±--------±------±-----±---------±----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±------±-----±---------±----------------------+
| 1 | SIMPLE | t100w | NULL | ref | idx1 | idx1 | 9 | const | 1030 | 100.00 | Using index condition |
±—±------------±------±-----------±-----±--------------±-----±--------±------±-----±---------±----------------------+
7.3 看
table : sql语句操作的表.多表时有意义.
type : 查找类型,全表\索引
possible_keys : 可能会用的索引
key : 最终选择的索引
key_len : 索引覆盖长度.联合索引有意义
rows : 估算值,要扫描的行数
Extra : 额外信息
7.4 type 输出的分析
7.4.1 ALL 全表扫描
mysql> desc select * from t100w ;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 100.00 | NULL |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from t100w where id=10;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 10.00 | Using where |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------------
mysql> desc select * from t100w where k1 like ‘%a%’;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 11.11 | Using where |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------------+
7.4.2 index 全索引扫描
mysql> desc select name,population from world.city;
7.4.3 range 索引范围扫描 (> ,< ,>=,<=,like ,in ,or)
mysql> desc select * from world.city where id<10;
mysql> desc select * from world.city where countrycode like ‘CH%’;
mysql> desc select * from world.city where countrycode in (‘CHN’,‘USA’);
7.4.4 ref 辅助索引等值
desc select * from world.city where countrycode=‘CHN’
union all select * from world.city where countrycode=‘USA’;
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘world’
–query=" select * from city where countrycode in (‘CHN’,‘USA’)" engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘world’
–query=“select * from city where countrycode=‘CHN’ union all select * from city where countrycode=‘USA’” engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
eq_ref
多表连接时,非驱动表的连接条件是主键或唯一键（a join b a是驱动表 b是非驱动表）
select * from a join b on a.id=b.aid
const(system)
主键或唯一键的等值查询.
7.4.4 key_len
a. 介绍
索引覆盖长度(联合索引)
idx(a,b,c) ----> a+b+c
b. 影响因素
字符集:
utf8 ,字符串列(char(10)) , N3
utf8mb4,N4
非空 :
NN : 忽略
没有NN: +1
数据类型:
tinyint 1
int 4
char(10)
varchar(10) +2
timestamp 4
datetime 8
例子:
CREATE TABLE student (
stuid int NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
stuname varchar(32) NOT NULL COMMENT ‘姓名’,
stuage tinyint unsigned NOT NULL DEFAULT ‘99’ COMMENT ‘年龄’,
gender char(1) NOT NULL DEFAULT ‘m’ COMMENT ‘性别’,
district enum(‘bj’,‘sh’,‘tj’,‘sz’) NOT NULL DEFAULT ‘bj’ COMMENT ‘区域’,
PRIMARY KEY (stuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
4
32*4+2
1
7.4.5 联合索引应用细节
idx(a,b,c)
a. 完全覆盖
where a= and b= and c=
where b= and c= and a=
mysql> desc select * from student where stuname=‘aa’ and stuage in (10,20) and gender=‘M’;
where a= and b= order c=
where b= and c= and a=
where a= and b= and c>
b. 部分覆盖
mysql> desc select * from student where stuname=‘aa’ and stuage>10 and gender=‘M’;
索引扫描到stuage不会继续扫描gender，为部分覆盖 可以优化联合索引，也可以将stuage>10范围查询放到最后
where a= and b=
where a=
where a= and c=
c. 不覆盖
b= and c=
b=
c=
d. 最左原则
- 选择基数大的列作为最左列
- 查询条件中必须包含最左列条件.
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query="select * from t100w where num=279106 and k1!=‘E0’ and k2=‘VWtu’ " engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
7.4.6 extra
filesort : 查询中出现了额外的排序.
order by
group by
- 扩展
8.1 8.0 在索引中的新特性
a. 不可见索引
alter table t1 alter index idx_name visible;
alter table t1 alter index idx_name invisible;
b. 倒序索引
where a order by b ,c desc
idx(a,b,c desc)
8.2 索引自优化倒序
AHI : 自适应的hash索引
a. 自适应,根据缓冲区中索引页的热度,自动生成HASH索引表
b. 快速锁定热点索引页在内存的地址.
Change buffer (以前叫insert buffer)
存储辅助索引的变更.
将来需要自动在内存中进行merge（合并）.
8.3 优化器算法
a. 查询优化器算法:
mysql> select @@optimizer_switch;
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on
b. 设置优化器算法:
mysql> set global optimizer_switch=‘index_condition_pushdown=off’;
hits方式:
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
配置文件:
my.cnf
8.3.1 ICP : Index Condition Pushdown
idx(k1,num,k2)
Index Condition Pushdown(ICP)
ICP是mysql使用索引从表中检索行数据的一种优化方式
5.6版本之后，如果where条件可以使用索引，mysql会把这部分过滤操作存放到存储引擎层，
存储引擎通过索引过滤，把满足的行从表中读取出，
ICP能减少引擎层访问基表的次数和server层访问存储引擎的次数
优化器算法:
例子 :
mysql> set global optimizer_switch=‘index_condition_pushdown=off’;
mysql> desc select * from t100w where k1=‘Vs’ and num<27779 and k2=‘mnij’;
±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | t100w | NULL | range | idx | idx | 14 | NULL | 29 | 10.00 | Using where |
±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±------------+
1 row in set, 1 warning (0.00 sec)
mysql> set global optimizer_switch=‘index_condition_pushdown=on’;
mysql> desc select * from t100w where k1=‘Vs’ and num<27779 and k2=‘mnij’;
±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±----------------------+
| 1 | SIMPLE | t100w | NULL | range | idx | idx | 14 | NULL | 29 | 10.00 | Using index condition |
±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±----------------------+
1 row in set, 1 warning (0.00 sec)
压测:
a. 开ICP 2000次语句压测 索引顺序不调整
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query=" select * from t100w where k1=‘Vs’ and num<27779 and k2=‘mnij’" engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
4.580 seconds
4.569 seconds
4.431 seconds
4.433 seconds
4.391 seconds
b. 关 ICP 2000次语句压测 索引顺序不调整
5.327
5.516
5.267
5.330
5.293 seconds
c. 索引顺序优化 压测
4.251
4.143
8.3.2 MRR (multi range read)
https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html
老师讲解
辅助索引条件查询时,先扫描辅助索引,获得ID值,放在read_rnd_buffer中,由MRR进行排序后,回表查询.
网上查询
是优化器将随机IO转化为顺序IO以降低查询过程IO开销的一种手段
查询辅助索引是，首先把查询结果按照主键进行排序，按照主键的顺序进行书签查找
mrr=on cost_base=off
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query=" select * from test.t100w where k1 between ‘qq’ and ‘rr’ " engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 171.710 seconds
Minimum number of seconds to run all queries: 171.710 seconds
Maximum number of seconds to run all queries: 171.710 seconds
Number of clients running queries: 100
Average number of queries per client: 20
mrr=on cost_base=on
- 索引应用规范
9.1 建立索引的原则（DBA运维规范）
(1) 必须要有主键,业务无关列。
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引列,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
mysql> select count(distinct left(name,19)) from city;
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
select * from schema_unused_indexes
where object_schema not in (‘sys’,‘mysql’,‘information_schema’,‘performance_schema’);
select * from sys.schema_redundant_indexes
where table_schema not in (‘sys’,‘mysql’,‘information_schema’,‘performance_schema’)\G
(6) 索引维护要避开业务繁忙期，建议用pt-osc。
(7) 联合索引最左原则
9.2 不走索引的情况（开发规范）
9.2.1 没有查询条件，或者查询条件没有建立索引
select * from t1 ;
select * from t1 where 1=1;
作业:
SQL审核和审计. yearning.io github, inception
9.2.2 查询结果集是原表中的大部分数据，应该是15-25%以上。
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。
可以通过精确查找范围，达到优化的效果。
1000000
500000 and
9.2.3 索引本身失效，统计信息不真实（过旧）
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建
现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? —>索引失效,统计数据不真实
innodb_index_stats
innodb_table_stats
mysql> ANALYZE TABLE world.city; 强制系统重新收集统计信息
9.2.4 查询条件使用函数在 索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询
9.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
mysql> desc select * from b where telnum=110;
mysql> desc select * from b where telnum=‘110’;
9.2.6 <> ，not in 不走索引（辅助索引） ******
9.2.7 like “%_” 百分号在最前面不走
8496
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
