1.1 索引的作用
类似于一本书的目录，起到优化查询的功能
1.2 索引的类型（笔试）
BTREE索引 （最常用）
RTREE索引
HASH索引
全文索引
1.3 BTREE索引的细分类（算法）
B-TREE
B+TREE
B*TREE（默认）
1.4 BTREE索引的功能分类
聚集索引（集群索引）
辅助索引（二级索引） ※※※※
1.5 Btree是如何构建的？
1.5.1 辅助索引
建立索引：
alter table t1 add index idx_name(name);
1>将name列的所有值取出来,进行自动排序
2>将排完序的值均匀的落到16KB叶子节点数据页中,并将索引键值所对应的数据行的聚集索引列值
3>向上生成枝节点和根节点
1.5.2 聚集索引
1>默认是按照主键生成聚集索引.没有主键,存储引擎会使用唯一键；如果都没有,会自动生成隐藏的聚集索引.
2>数据在存储是,就会按照聚集索引的顺序存储到磁盘的数据页.
3>由于本身数据就是有序的,所以聚集索引构建时,不需要进行排序.
4>聚集索引直接将磁盘的数据页,作为叶子节点.
5>枝节点和根节点只会调取下层节点主键的最小值
1.5.3 辅助索引和聚集索引的区别？（重点）
1.6 辅助索引细分
单列辅助索引
联合索引
唯一索引
1.7 索引树的高度（越低越好）
1.7.1 表的数据量级大
（1）分区表
（2）分库分表（分布式架构）----目前最流行的
1.7.2 索引键值的长度
（1）尽可能的选择列值短的列创建索引
（2）采用前缀索引
1.7.3 数据类型的选择（选择合适）
1.8 索引的管理
1.8.1 准备压力测试的数据
create database test charset utf8mb4;
use test;
create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;
插入100w条数据：
call rand_data(1000000);
commit;
1.8.2 进行压力测试
1>没有添加索引之前的测试
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='LMhi'" 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: 683.581 seconds
Minimum number of seconds to run all queries: 683.581 seconds
Maximum number of seconds to run all queries: 683.581 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@db01 ~]#
2>添加索引之后的测试
alter table t100w add index idx_k2(k2);
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='LMhi'" 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: 4.542 seconds
Minimum number of seconds to run all queries: 4.542 seconds
Maximum number of seconds to run all queries: 4.542 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@db01 ~]#
1.8.2 索引命令操作
1>查询索引
desc student;
show index from student\G
Key：
PRI（主键）
UNI（唯一索引）
MUL（辅助索引）
2>创建索引
（1）创建单列索引：
alter table student add index idx_name(sname);
（2）创建联合索引：
alter table student add index idx_sname_sage_ssex(sname,sage,ssex);
（3）创建前缀索引：
alter table student add index idx_sname(sname(5));
（4）创建唯一索引：
alter table student add unique index idx_tel(telnum);
3>删除索引
alter table student drop index idx_name;
1.9 explain（desc）-----重点
1.9.1 作用：
抓取优化器优化过的执行计划
1.9.2 执行计划的分析
wenjuan[test]>wenjuan[test]>desc select * from t100w where k2 != 'asdf';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | idx_k2 | NULL | NULL | NULL | 997381 | 86.34 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[test]>
说明：
table：以上SQL语句涉及到的表 ***
type：查询的类型（全表扫描---ALL、索引扫描、查不到数据---NULL） *****
possible_keys：可能会用到的索引 ***
key：使用到的索引 ****
key_len：索引的覆盖长度 *****
Extra：额外的信息 ****
1.9.3 type详细说明（重点）
1> ALL： 全表扫描 , 不会走任何索引
（1）查询条件,没建索引
oldguo[test]>explain select * from test.t100w where k2='VWtu'
oldguo[test]>desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
（2）有索引不走
以下这几种情况：
desc select * from t100w where k2 != 'asdf';
desc select * from t100w where k2 like '%aa%';
desc select * from t100w where k2 not in ('asda','asas');
desc select * from t100w;
desc select * from t100w where 1=1;
注意: !=和not in 如果是主键列,是走range.
2> index 全索引扫描
wenjuan[test]>desc select k2 from t100w;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | index | NULL | idx_k2 | 17 | NULL | 997381 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[test]>
===========从range开始,索引才有价值的============
3> range 索引范围查询
（1）所有索引：> ,<, >=, <= ,like , between and
wenjuan[world]>desc select * from city where id<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[world]>
wenjuan[world]>desc select * from city where countrycode like 'CH%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 3 | NULL | 397 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[world]>
in , or
wenjuan[world]>desc select * from city where countrycode in ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 3 | NULL | 637 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[world]>
（2）聚集索引：!= 、not in
wenjuan[test]>desc select * from world.city where id != 10;
wenjuan[test]>desc select * from world.city where id not in (10,20);
说明：
- B+tree 索引能额外优化到：> ,<, >=, <= ,like , between and
- in 和 or 享受不到b+tree额外的优化效果的,所以我一般情况会将in , or 进行改写：
desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; wenjuan[world]>desc select * from city where countrycode='CHN' -> union all -> select * from city where countrycode='USA'; +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | 1 | PRIMARY | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL | | 2 | UNION | city | NULL | ref | CountryCode | CountryCode | 3 | const | 274 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) wenjuan[world]>
4> ref（辅助索引等值查询）:
wenjuan[world]>desc select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
wenjuan[world]>
5> eq_ref：多表连接查询中,非驱动表on的条件列是主键或者唯一键
wenjuan[world]>desc select a.name,b.name from city as a join country as b on a.countrycode=b.code where a.population<100;
6> const(system)：主键或唯一键的等值
wenjuan[world]>desc select * from city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
wenjuan[world]>
7> NULL： 获取不到数据
wenjuan[world]>desc select * from city where id=100000000000000;
1.9.4 possible_keys（可能会用到的索引）
NULL：没有和查询条件匹配的索引条目
有值：有查询条件匹配的索引条目，但是没走，大部分原因是语句查询方式不符合索引应用条件
1.9.5 key（使用到的索引）
最终使用的索引，可以帮助我们判断是否走了合适的索引
1.9.6 key_len（索引的覆盖长度）：在联合索引应用的判断时，会经常看
| 字符集 | 一个字符最大存储长度占几个字节 |
|---|---|
| utf8 | 3 |
| utfmb4 | 4 |
| 不同类型索引 | 有not nulll时（非空） | 没有指定not null时（为空） |
|---|---|---|
| int | 最大4个字节 | 最大4+1个字节 |
| tinyint | 最大1个字节 | 最大1+1个字节 |
| char(10) | 最大4*10个字节 | 最大4*10+1个字节 |
| varchar(10) | 最大4*10+2个字节 | 最大4*10+2+1个字节 |
说明：
- 有非空约束时,key_length就是最大字节长度
- 在没有非空约束时: 字符最大长度+1
- varchar类型,需要额外在最大字符长度+2(存储字符长度的最长度占位)
联合索引准备：
-- 创建测试表
create table t1(a int not null ,b char(10) not null ,c varchar(10) not null )charset utf8mb4;
wenjuan[test]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | NO | | NULL | |
| b | char(10) | NO | | NULL | |
| c | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
wenjuan[test]>
-- 创建索引
wenjuan[test]>alter table t1 add index idx(a,b,c);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
wenjuan[test]>show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | idx | 1 | a | A | 0 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx | 2 | b | A | 0 | NULL | NULL | | BTREE | | |
| t1 | 1 | idx | 3 | c | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
wenjuan[test]>
（1）最完美的查询情况
desc select * from t1 where a=1 and b='a' and c='a';
desc select * from t1 where b='1' and a=1 and c='a';
desc select * from t1 where c='1' and a=1 and b='a';
desc select * from t1 where c='1' and b='a' and a=1;
desc select * from t1 where a=1 and c='a' and b='a';
desc select * from t1 where b='1' and c='a' and a=1;
结论：
当查询条件中,包含了索引列中所有的列条件,并且都是等值的查询,那么无关排列顺序,都可以走全联合索引优化;
原因：优化器会自动调整顺序,达到最佳的优化效果。所以，我们重点需要关注的是联合索引建立的顺序,从左到右,唯一值越多的列放在最左边
（2）部分索引（查询条件中,哪些因素会key_len长度）
--------1>按照索引的建立顺序,在查询条件中,少了任意一个中间列,后续列都无法走索引
wenjuan[test]>desc select * from t1 where a=1 and c='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx | idx | 4 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[test]>
wenjuan[test]>desc select * from t1 where a=1 and b like 'a%' and c='a';
-------2>在条件查询中间,出现不等值查询时,从不等值列开始,所有列都无法使用联合索引 (暂存)
优化方法：将不等值列放在最后.
-------3>如果有多子句的条件查询(必须是联合索引)，按照子句的执行顺序,建立联合索引.
1.9.7 Extra：额外的信息
using filesort ===> 排序不走索引,走的额外排序
wenjuan[(none)]>use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
wenjuan[world]>desc select * from world.city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[world]>
原因：在 group by ,order by,distinct等.
一般优化的方法是,和where条件的列建立联合索引
扩展：
以json的方式显示执行计划，可以通过used_key_parts查看使用的索引部分
wenjuan[test]>desc format=json select * from t1 where b='1' and c='a' and a=1;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "t1",
"access_type": "ref",
"possible_keys": [
"idx"
],
"key": "idx",
"used_key_parts": [
"a",
"b",
"c"
],
"key_length": "86",
"ref": [
"const",
"const",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "88"
},
"used_columns": [
"a",
"b",
"c"
]
}
}
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
wenjuan[test]>
未完……
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
