1. 什么是索引
相当于书中的目录,起到优化查询的功能。
条件类查询: where group by order by join on distinct
2. 索引的类型
Btree : B树索引(Balance Tree)。 InnoDB , MyISAM
Hash : 哈希索引。 Memory相关引擎。InnoDB中也会维护自己的AHI(自适应)的hash索引。
FullTEXT: 全文索引。一般是在大字段使用。一般用ES数据库存储大字段。
GIS : 地理位置索引。一般MongoDB可以替代。
Rtree :
3. MySQL为什么要使用Btree查找算法
二叉树
平衡树
可以快速锁定,需要查找数据的范围。
4. MySQL 索引怎么应用的BTREE结构
4.1 聚簇索引
4.1.0
段 : 一个表就是一个段,会由多个区构成
区 : 又称之为簇,由连续的64个页构成,默认1M
页 : 一个数据页是16KB,MySQL IO的最小单元
4.1.1 生成条件
(1)创建InnoDB表时,如果设置了主键 ID列 PK。自动把ID列作为聚簇索引列。
(2)没有主键,会选择第一个唯一键(UK)作为聚簇索引列
(3)如果都没有,生成隐藏列(ROWID),作为聚簇索引列。
4.1.2 作用
(1)在存储数据时,InnoDB 申请连续的磁盘空间,进行存储。
(2)数据在存储时,按照聚簇索引值的顺序在磁盘有序存储。
以上的作用,又被称之为聚簇索引组织存储数据表。简称:索引组织表。
尽可能保证 从逻辑层 —》 物理层 的数据有序性
4.1.3 聚簇索引的B树构建
(1) 数据行在存储时,按照聚簇索引顺序逻辑顺序和物理顺序有序存储
(2) 叶子节点,就是数据行所在的数据页,数据即索引。
(3) 非叶子节点构建,存储了叶子结点ID范围+指针
(4) 根节点,存储了非叶子节点的ID范围+指针
4.1.4 B-tree,B+tree ,B*tree
B+tree : 在叶子结点加入双向指针
B*tree : 在非叶子节点加入双向指针
目的: 1. 减少IO次数 2. 减少IO量级 3.减少随机IO
4.2 辅助索引
普通索引,主要的作用:优化非聚簇索引列的条件查询。
4.2.1 辅助索引构建过程原理
(1)“提取” 辅助索引列+ID列值
(2)按照辅助索引列值进行从小到大排序
(3)将有序的值均匀存储到数据页中,生成叶子节点
(4)将叶子节点中的辅助索引列值的范围+指针,生成非叶子节点
(5)提取下层非叶子节点的范围+指针,生成根节点
4.2.2 辅助如何加速查询
按照辅助索引列作为查询条件时。
(1)通过扫描需要的索引页,获取到查询条件对应的ID值。
(2)拿着ID值,再回到聚簇索引进行扫描,最终获取数据行。又被称之为:回表查询。
4.2.3 回表查询
回表来的问题:
从辅助索引扫描得出ID值之后,回到聚簇索引扫描的过程。
IO次数、IO量会增多、随机IO会增多。
怎么解决回表的问题:
-
不产生回表
覆盖索引: 理论上是把所有查询的条件数据都包含在辅助索引中。
这是比较理想的想法,现实情况不太好保证。 -
减少回表
将可能让辅助索引得出的ID值是唯一或者较少个的。
(1)查询条件尽量精准。
(2)MRR技术,将辅助索引扫描到的ID,先排序再一次性回表。
5. 影响索引树的高度因素
聚簇索引: 一般建议2-3层为佳。三层聚簇索引可以存储2000w+的数据行。
辅助索引:3-4 层为佳
5.1 数据行越多
分区表(逻辑)、历史数据归档(pt-archive)、分库分表(物理层次)
5.2 数据类型影响
varchar char
enum
5.3 索引列值长度
前缀索引。
6. 索引的管理操作
6.1 查看表的索引
desc city;
Key
-----
PRI
UNI
MUL
mysql> show index from city\G
6.2 创建索引
原则: 经常作为查询条件的列作为索引列。
创建单列索引:
mysql> alter table city add index idx_name(name);
创建联合索引:
mysql> alter table city add index i_n_d_p(name,district,population);
创建前缀索引(字符串列):
mysql> alter table city add index i_nn(name(5));
6.3 删除索引
mysql> alter table city drop index idx_name;
mysql> alter table city drop index i_n_d_p;
mysql> alter table city drop index i_n;
mysql> alter table city drop index i_nn;
7. 压力测试
7.1 导入 t100w.sql
7.2 建立索引之前的压测
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=2000 -uroot -p123 -verbose
7.3 建立索引
alter table test.t100w add index idx_k2(k2);
7.4 再次对比
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=2000 -uroot -p123 -verbose
8. 执行计划分析-explain(desc)
8.1 分析什么执行计划
优化器最终选择的执行计划。
8.2 如何获取
mysql> desc select * from t100w where k2='780P';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t100w | NULL | ref | idx_k2 | idx_k2 | 17 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
8.3 如何分析?
table :针对的表
type :查询类型,反馈:是否走索引,索引级别?
possible_keys :可能会用到的索引
key :真正走的索引
key_len :索引的覆盖长度,判断联合索引使用情况。
rows :需要扫描的数据行数(预估)
Extra :额外信息
8.4 type输出详解
8.4.1 ALL 不走任何索引,全表扫描
例子:
查询条件没有索引:
mysql> desc select * from t100w where k1='ab';
不等值的条件查询
mysql> desc select * from t100w where k2 !='abcc';
mysql> desc select * from t100w where k2 not in ('abcc','abdd');
like '%aa%'前面带%的模糊查询
mysql> desc select * from t100w where k2 like '%aa%';
8.4.2 index 全索引扫描
mysql> desc select k2 from t100w;
8.4.3 range 索引范围扫描 (> < >= <= ,like , in ,or)
mysql> desc select * from city where id<10;
mysql> desc select * from city where countrycode like 'Ch%';
mysql> desc select * from city where countrycode in ('CHN','USA');
如果查询条件重复值少的话,可以考虑将其改写为以下语句,
mysql> desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
8.4.4 ref 辅助索引等值查询
mysql> desc select * from city where countrycode='CHN';
8.4.5 eq_ref 多表连接中非驱动的关联条件是主键或唯一键
mysql> desc select * from city left join country on city.countrycode=country.code ;
8.4.6 const(system) 主键或唯一键等值查询
mysql> desc select * from city where id=10;
8.4.7 NULL 查不到数据
mysql> desc select * from city where id=1000000000;
8.5 key_len 索引覆盖长度
8.5.1 作用
索引应用的长度。
如果单列索引比较长,需要评估是否需要前缀索引。
如果是联合索引,帮助判断应用了几部分。
idx(a,b,c)
select where a b c
8.5.2 key_len的计算
key_len是索引列的定义的最大预留长度。
key_len和哪些因素有关?
字符集
utf8 : 最多3个字节字符
utf8mb4 :最多4个字节字符
数字类型: int tinyint
数据类型 是否非空 key_len
int not null 4
tinyint not null 1
int 4+1
tinyint 1+1
字符串类型: char varchar (ut8mb4)
数据类型 是否非空 key_len
char(10) not null 10*4
varchar(10) not null 10*4+2
char(10) 10*4+1
varchar(10) 10*4+2+1
create table t1 (
n1 int not null ,
n2 int ,
c1 char(10) not null,
c2 varchar(24)
)charset=utf8mb4;
alter table t1 add index idx(n1,n2,c1,c2);
问:
select 索引的四个列都被应用到了,key_len
4+5+4*10+24*4+2+1 =148
8.5.3 联合索引的应用细节
idx(a,b,c,d)
联合索引是遵循最左原则。
(1) 在查询中,应用到索引,至少要有最左列
(2) 最左列是第一过滤条件,一般选择重复值少的列作为最左列。
8.5.3.1 全部覆盖
select * from t100w where a=? and b=? and c=? and d=?
select * from t100w where b=? and c=? and d=? and a=?
select * from t1 where n2=20 and c1='a' and c2='b' and a in (1,2);
8.5.3.2 部分覆盖
select * from t100w where a=? and b=? and c=? ====> abc
select * from t100w where a=? and b=? and d=? ====> ab
select * from t100w where a=? and b>? and c=? ====> ab
8.5.3.3 不走索引
查询中,缺失了最左列
bc
b
c
d
bcd
bd
多子句 : 联合索引设计,需要按照子句顺序建立联合索引。
8.5.3.4 通过压测工具 测试联合索引全部覆盖,部分覆盖
mysql> alter table t100w add index idx(num,k2,k1);
压测2000次:
mysql> desc select * from t100w where num=641631 and k1 > 'aa' and k2='rsEF';
mysql> alter table t100w add index idx(num,k1,k2);
压测2000次:
mysql> desc select * from t100w where num=641631 and k1 > 'aa' and k2='rsEF';
8.5.4 extra 说明
Using filesort 此次查询中发生了额外的排序操作
mysql> desc select * from world.city where countrycode='CHN' order by population limit 5;
mysql> alter table world.city add index idxx(countrycode,population);
9 索引应用规范
9.1 建立索引的原则
9.1.0 说明
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。
9.1.1 (必须的) 建表时一定要有主键,一般是个无关自增列数字列。
降低索引树高度。
9.1.2 选择唯一性索引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话,可能存在同名现象,从而降低查询速度。
优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
9.1.3(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段
排序操作会浪费很多时间。
where A B C ----》 A B C
in
where A group by B order by C
A,B,C
如果为其建立索引,优化查询
注:如果经常作为条件的列,重复值特别多,可以建立联合索引。
9.1.4 尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引。
9.1.5 限制索引的数目
索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
(2) 修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用
9.1.6 删除不再使用或者很少使用的索引(percona toolkit)
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
2.1.7 大表加索引,要在业务不繁忙期间操作
2.1.8 尽量少在经常更新值的列上建索引
2.1.9 建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3)最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5)降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx) (6) 索引维护要避开业务繁忙期
9.2 不走索引的情况(开发规范)
9.2.1 没有查询条件,或者查询条件没有建立索引
9.2.2 查询结果集是原表中的大部分数据,应该是15-25%以上。
查询的结果集,超过了总数行数25%,优化器觉得就没有必要走索引了。
MySQL的预读功能有关。
limit
精确范围
1000000
>500000 and < 600000
9.2.3 索引本身失效,统计信息不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下,有可能会出现索引失效。
一般是删除重建
现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? —>索引失效,,统计数据不真实
innodb_index_stats
innodb_table_stats
9.2.4 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
例子:
错误的例子:select * from test where id-1=9;
正确的例子:select * from test where id=10;
算术运算
函数运算
子查询
9.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
这样会导致索引失效. 错误的例子:
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| telnum | varchar(20) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>
9.2.6 <> ,not in 不走索引(辅助索引)
EXPLAIN SELECT * FROM teltab WHERE telnum <> '110';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119');
mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';
单独的>,<,in 有可能走,也有可能不走,和结果集有关,尽量结合业务添加limit
or或in 尽量改成union
EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成:
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'
9.2.7 like “%_” 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引
%linux%类的搜索需求,可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品
优化器针对索引的算法
1 自优化能力
1.1 MySQL索引的自优化-AHI(自适应HASH索引)
MySQL的InnoDB引擎,能够创建只有Btree。
AHI作用:
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当与索引的索引。
1.2 MySQL索引的自优化-Change buffer
比如insert,update,delete 数据。
对于聚簇索引会立即更新。
对于辅助索引,不是实时更新的。
在InnoDB 内存结构中,加入了insert buffer(会话),现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据,会在内存中进行merge(合并)操作,此时辅助索引就是最新的。
1.3 8.0 版本 自优化能力
不可见索引。invisable index
倒叙索引。
(a,b desc )
select * from t1 where a order by b desc ;
2 可选的优化器算法-索引
2.1 优化器算法查询
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
2.2 如何修改?
1. my.cnf
2. set global optimizer_switch='batched_key_access=on';
3. hints
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
2.3 index_condition_pushdown
介绍: 索引下推 ,5.6+ 加入的特性
idx(a,b,c)
where a = and b > and c =
作用: SQL做完过滤后,只能用a,b的部分辅助索引,将c列条件的过滤下推到engine层,进行再次过滤。排除无用的数据。
最终去磁盘上拿数据页。
大大减少无用IO的访问。
测试1: ICP开启时
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=ON'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
测试2:ICP关闭时:
idx(k1,k2)
mysql> SET global optimizer_switch='index_condition_pushdown=OFF'
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
测试3:优化索引
idx(k2,k1)
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k1 > 'Za' and k2='rsEF'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
具体参考 :
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/index-condition-pushdown/
2.4 MRR : Multi Range Read
2.4.1 作用
减少回表。
2.4.2 开关方法
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
2.5 SNLJ
例子:
A join B
on A.xx = B.yy
伪代码:
for each row in A matching range {
block
for each row in B {
A.xx = B.yy ,send to client
}
}
以上例子,可以通过 left join 强制驱动表。
2.6 BNLJ
在 A和B关联条件匹配时,不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了, CPU消耗,减少了IO次数
In EXPLAIN output,
use of BNL for a table is signified
when the Extra value contains Using join buffer (Block Nested Loop)
2.7 BKA
主要作用,使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式:
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陆生效。