1.索引概述
1.1 索引介绍
1.2 演示
1.2.1 无索引情况
1.2.2 有索引情况
此时我们在进行查询时,只需要扫描三次就可以找到数据了,极大的提高的查询的效率。
备注: 这里我们只是假设索引的结构是二叉树,介绍一下索引的大概原理,只是一个示意图,并不是索引的真实结构,索引的真实结构,后面会详细介绍。
1.3 特点
其实建立索引就是空间换时间的一种方法。
2.索引结构
2.1 概述
注意: 我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。
2.2 二叉树索引
- 顺序插入时,会形成一个链表,查询性能大大降低。
- 大数据量情况下,层级较深,检索速度慢。
- 大数据量情况下,层级较深,检索速度慢。
2.3 B-Tree
知识小贴士 : 树的度数指的是一个节点的子节点个数。
- 5阶的B树,每一个节点最多存储4个key,对应5个指针。
- 一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂。
- 在B树中,非叶子节点和叶子节点都会存放数据。
2.4 B+Tree
- 绿色框框起来的部分,是索引部分,仅仅起到索引数据的作用,不存储数据。
- 红色框框起来的部分,是数据存储部分,在其叶子节点中要存储具体的数据。
打开刚才那个网站:
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
2.5 Hash
MySQL中除了支持B+Tree索引,还支持一种索引类型---Hash索引。
2.5.1 结构
2.5.2 特点
- Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,...)
- 无法利用索引完成排序操作
- 查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引
2.5.3 存储引擎支持
思考题: 为什么 InnoDB 存储引擎选择使用 B+tree 索引结构 ?A. 相对于二叉树,层级更少,搜索效率高;B. 对于 B-tree ,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;C. 相对 Hash 索引, B+tree 支持范围匹配及排序操作;
3. 索引分类
3.1 索引分类
3.2 聚集索引&二级索引
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
- 聚集索引的叶子节点下挂的是这一行的数据 。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
- ①. 由于是根据name字段进行查询,所以先根据name='Arm'到name字段的二级索引中进行匹配查 找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
- ②. 由于查询返回的数据是*,所以此时,还需要根据主键值10,到聚集索引中查找10对应的记录,最终找到10对应的行row。
- ③. 最终拿到这一行的数据,直接返回即可。
回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。
那么会有人问,二级索引哪来的?二级索引是当你建立复合索引的时候,只要查找的字段在复合索引中,并且满足最左前缀法则,就会优先考虑二级索引查找。这里的复合索引和最左前缀法则,都后面会详细讲解。
思考题:以下两条 SQL 语句,那个执行效率高 ? 为什么 ?A. select * from user where id = 10 ;B. select * from user where name = 'Arm' ;备注 : id 为主键, name 字段创建的有索引;解答:A 语句的执行性能要高于 B 语句。因为 A 语句直接走聚集索引,直接返回数据。 而 B 语句需要先查询 name 字段的二级索引,然后再查询聚集索引,也就是需要进行回表查询。
思考题:InnoDB 主键索引的 B+tree 高度为多高呢 ?假设 :一行数据大小为 1k ,一页中可以存储 16 行这样的数据。 InnoDB 的指针占用 6 个字节的空间,主键即使为 bigint ,占用字节数为 8 。高度为 2 :n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 11701171* 16 = 18736也就是说,如果树的高度为 2 ,则可以存储 18000 多条记录。高度为 3 :1171 * 1171 * 16 = 21939856也就是说,如果树的高度为 3 ,则可以存储 2200w 左右的记录。
3.3 索引语法
3.3.1 创建语法
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
中括号里面是索引类型,UNIQUE唯一索引和FULLTEXT全文索引,有人问,那主键索引呢?主键索引一般自动创建。
3.3.2 查看索引
SHOW INDEX index_name ON table_name;
3.3.3 删除索引
DROP INDEX index_name ON table_name ; 1
3.3.4 案例演示
create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,
'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,
'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2',
'0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24,
'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',
30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29,
'1', '4', '2003-05-26 00:00:00');
CREATE INDEX idx_user_name ON tb_user(name);
这是创建一个二级索引。
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);
CREATE INDEX idx_email ON tb_user(email);
show index from tb_user;
3.4 SQL性能分析
3.4.1 执行频率
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
这里面使用7个_符号作为占位符,进行模糊查询。
Com_delete: 删除次数
Com_insert: 插入次数
3.4.2 慢查询日志
如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
systemctl restart mysqld
select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
13.35sec
SHOW VARIABLES LIKE 'slow_query_log_file';
3.4.3 profile详情
SELECT @@have_profiling ;
SET profiling = 1;
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看指定SQL各个阶段的耗时情况 :
3.4.4 explain
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
3.5 索引的使用
3.5.1 验证索引效率
select * from tb_sku where id = 1\G;
-- \G是成行显示
SELECT * FROM tb_sku WHERE sn = '100000003145001';
create index idx_sku_sn on tb_sku(sn) ;
然后再次执行相同的SQL语句,再次查看SQL的耗时。
SELECT * FROM tb_sku WHERE sn = '100000003145001';
3.5.2 最左前缀法则
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
我们可以看到实际走的索引是我们刚创建的联合索引
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
explain select * from tb_user where profession = '软件工程' and status = '0';
思考题:explain select * from tb_user where profession = ' 软件工程 ';explain select * from tb_user where age = 31 and status = '0';explain select * from tb_user where status = '0';explain select * from tb_user where profession = ' 软件工程 ' and status = '0';当执行 SQL 语句 : explain select * from tb_user where age = 31 andstatus = '0' and profession = ' 软件工程 ' ; 时,是否满足最左前缀法则,走不走上述的联合索引,索引长度?可以看到,是完全满足最左前缀法则的,索引长度 54 ,联合索引是生效的。注意 : 最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段 ( 即是第一个字段 ) 必须存在,与我们编写 SQL 时,条件编写的先后顺序无关。
3.5.3 范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。
explain select * from tb_user where profession = '软件工程' and age > 30 and status
= '0';
explain select * from tb_user where profession = '软件工程' and age >= 30 and
status = '0';
所以,在业务允许的情况下,尽可能的使用类似于 >= 或 <= 这类的范围查询,而避免使用 > 或 <
3.6 索引失效情况
3.6.1 索引列失效情况
一、不要在索引列上进行运算操作, 索引将失效。
A. 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
B. 当根据phone字段进行函数运算操作之后,索引失效。
explain select * from tb_user where substring(phone,10,2) = '15'; 1
二、字符串不加引号
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= 0;
explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;
三、模糊查询
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
四、or连接条件
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
create index idx_user_age on tb_user(age);
最终,我们发现,当or连接的条件,左右两侧字段都有索引时,索引才会生效。
五、数据分布影响
select * from tb_user where phone >= '17799990005';
select * from tb_user where phone >= '17799990015';
explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;
六、当然,不满足最左法则和使用范围查询的也是其中之一
3.7 SQL提示
当对于一个字段,既有联合索引,又有单独属于它自己的索引,mysql会自动选择走哪一个索引。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工
程';
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工
程';
explain select * from tb_user force index(idx_user_pro) where profession = '软件工
程';
3.8 覆盖索引
explain select id, profession from tb_user where profession = '软件工程' and age =
31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'
and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
上述几条语句执行结果如下:
id是主键,是一个聚集索引。 name字段建立了普通索引,是一个二级索引(辅助索引)。
B. 执行SQL : select * from tb_user where id = 2;
根据id查询,直接走聚集索引查询,一次索引扫描,直接返回数据,性能高。
C. 执行SQL:selet id,name from tb_user where name = 'Arm';
D. 执行SQL:selet id,name,gender from tb_user where name = 'Arm';
思考题:一张表 , 有四个字段 (id, username, password, status), 由于数据量大 , 需要对以下SQL 语句进行优化 , 该如何进行才是最优方案 :select id,username,password from tb_user where username = 'itcast';答案 : 针对于 username, password 建立联合索引 , sql 为 : create index idx_user_name_pass on tb_user(username,password); 这样可以避免上述的SQL语句,在查询的过程中,出现回表查询。
3.9 前缀索引
3.9.1 语法
create index idx_xxxx on table_name(column(n)) ;
create index idx_email_5 on tb_user(email(5));
3.9.2 前缀长度
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
上述代码是计算选择性的,选择性公式:
1
,表示每个记录的索引值都是唯一的。
3.9.3 前缀索引的查询流程
3.10 单列索引与联合索引
在查询出来的索引中,既有单列索引,又有联合索引。
接下来,我们来执行一条SQL语句,看看其执行计划:
create unique index idx_user_phone_name on tb_user(phone,name);
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。
如果查询使用的是联合索引,具体的结构示意图如下:
3.11 索引设计原则
- 针对于数据量较大,且查询比较频繁的表建立索引。
- 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
- 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
4.SQL优化
4.1 插入数据
4.1.1 优化方案一
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
4.1.2 优化方案二
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
4.1.3 优化方案三
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
4.2 大批量插入数据
-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;
主键顺序插入性能高于乱序插入
4.3 主键优化
4.3.1 数据组织方式
行数据,都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图(mysql专栏中的存储引擎文章):
4.3.2 页分裂
4.3.2.1 主键顺序插入效果
②. 第一个页没有满,继续往第一页插入
③. 当第一个也写满之后,再写入第二个页,页与页之间会通过指针连接
④. 当第二页写满了,再往第三页写入
4.3.2.2 主键乱序插入效果
②. 此时再插入id为50的记录,我们来看看会发生什么现象
会再次开启一个页,写入新的页中吗?
不会。因为,索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后。
但是47所在的1#页,已经写满了,存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。
但是并不会直接将50存入3#页,而是会将1#页后一半的数据,移动到3#页,然后在3#页,插入50。
上述的这种现象,称之为 "页分裂",是比较耗费性能的操作。
4.3.2.3 页合并
目前表中已有数据的索引结构(叶子节点)如下:
删除数据,并将页合并之后,再次插入新的数据21,则直接插入3#页
这个里面所发生的合并页的这个现象,就称之为 "页合并"。
知识小贴士:MERGE_THRESHOLD :合并页的阈值,可以自己设置,在创建表或者创建索引时指定。
4.3.2.4 索引设计原则
- 满足业务需求的情况下,尽量降低主键的长度。
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
- 业务操作时,避免对主键的修改。
4.4 order by
explain select id,age,phone from tb_user order by age ;
explain select id,age,phone from tb_user order by age, phone ;
由于 age, phone 都没有索引,所以此时再排序时,出现Using filesort, 排序性能较低
C. 创建索引
-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);
D. 创建索引后,根据age, phone进行升序排序
explain select id,age,phone from tb_user order by age; 1
explain select id,age,phone from tb_user order by age , phone;
explain select id,age,phone from tb_user order by age desc , phone desc ;
F. 根据age, phone进行降序一个升序,一个降序
explain select id,age,phone from tb_user order by age asc , phone desc ;
为了解决上述的问题,我们可以创建一个索引,这个联合索引中 age 升序排序,phone 倒序排序。
G. 创建联合索引(age 升序排序,phone 倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
升序/降序联合索引结构图示:
4.4.1 order by优化原则
4.5 group by
explain select profession , count(*) from tb_user group by profession ;
然后,我们在针对于 profession , age, status 创建一个联合索引。
create index idx_user_pro_age_sta on tb_user(profession , age , status);
explain select profession , count(*) from tb_user group by profession ;
再执行如下的分组查询SQL,查看执行计划:
4.5.1 设计原则
4.6 limit
explain select * from tb_sku t , (select id from tb_sku order by id
limit 2000000,10) a where t.id = a.id;
子查询里面虽然也用到了limit,但是他是根据主键索引查询,查询速度快。
4.7 conut
按照效率排序的话, count( 字段 ) < count( 主键 id) < count(1) ≈ count(*) ,所以尽量使用 count(*) 。
4.8 update
我们主要需要注意一下update语句执行时的注意事项。
update course set name = 'javaEE' where id = 1 ;
update course set name = 'SpringBoot' where name = 'PHP' ;
InnoDB 的行锁是针对索引加的锁,不是针对记录加的锁 , 并且该索引不能失效,否则会从行锁升级为表锁 。
索引和sq优化这一部分到这就告一段落,所有知识点,全在这,吃透,面试不成问题!