公司公司网站建设_湖北黄州疫情动态_微信管理软件哪个最好_信阳网站seo

目录

一.索引概述

二.索引结构

1）B+tree索引结构

（1）Btree

（2）B+tree

（3）B+tree索引

2）Hash索引结构

三.索引分类

四.索引语法

五.SQL性能分析

1）查看执行频次

2）慢日志查询

3）show profiles

4）explain

六.使用规则

1）最左前缀法则

2）索引失效情况

（1）范围查询

（2）索引列运算

（3）字符串类型数据不加单引号

（4）模糊查询

（5）or连接条件

（6）数据分布影响

3）SQL提示

4）覆盖索引&回表查询

5）前缀索引

         6）单列索引和联合索引

七.索引设计原则

八.索引总结

---

一.索引概述

二.索引结构

1）B+tree索引结构

（1）Btree

模拟Btree分裂过程：B-Tree Visualization

（2）B+tree

 模拟B+tree分裂过程：B+ Tree Visualization

（3）B+tree索引

2）Hash索引结构

三.索引分类

回表查询：首先在二级索引中查找，查找到对应的主键值，然后通过查找到的主键值到聚集索引中去查找数据。

四.索引语法

-- 准备工作
create table index_exam(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',phone varchar(13) comment '联系方式',profession varchar(2) comment '职业',age int comment '年龄',email varchar(20) comment 'email',status int comment '状态'
)comment '索引示例';

show index from index_exam; -- 查看表中索引create index index_exam_name on index_exam(name);
create unique index index_exam_phone on index_exam(phone);
create index index_exam_pro_age_sta on index_exam(profession,age,status);
create index index_exam_email on index_exam(email);drop index index_exam_name on index_exam; -- 删除索引

五.SQL性能分析

1）查看执行频次

2）慢日志查询

注：上诉查看慢日志文件中记录信息的路径是在linux系统下。

show variables like 'slow_query_log'; -- 查看慢查询日志是否开启

我的慢查询日志默认是开启状态，如下图

3）show profiles

select @@have_profiling; -- 查看是否支持profile
select @@profiling;  -- 查看profile是否打开，0为关闭
set profiling = 1;

4）explain

desc select * from emp where id=1;
explain select * from emp where id=1;

查询选修了MySQL课程的学生（子查询）：先查询id=3的course表，再查询id=2的student_course表，再查询id=1的<subquery2>二级查询，最后执行id=1的student表。

六.使用规则

1）最左前缀法则

explain select * from index_exam where profession='软件工程' and age='12' and status=1; -- type:ref； key：index_exam_pro_age_sta； key_len:21
explain select * from index_exam where age='12' and profession='软件工程' and status=1; -- type:ref； key：index_exam_pro_age_sta； key_len:21
explain select * from index_exam where profession='软件工程' and  status=1; -- type:ref； key：index_exam_pro_age_sta； key_len:11
explain select * from index_exam where profession='软件工程' and age='12' ; -- type:ref；key：index_exam_pro_age_sta； key_len:16
explain select * from index_exam where profession='软件工程'  ; -- type:ref；key：index_exam_pro_age_sta； key_len:11
explain select * from index_exam where age='12' and status=1; -- type:all; key：null,

2）索引失效情况

（1）范围查询

explain select * from index_exam where profession='软件工程' and age>12 and status=1; -- type:ref； key：index_exam_pro_age_sta； key_len:16
explain select * from index_exam where profession='软件工程' and age>=12 and status=1; -- type:ref； key：index_exam_pro_age_sta； key_len:21

（2）索引列运算

explain select * from index_exam where phone = '1555555555555'; -- type:const； key：index_exam_phone； key_len:55
explain select * from index_exam where substring(phone,10,2)='15'; -- type:all; key：null,

（3）字符串类型数据不加单引号

explain select * from index_exam where phone = '1555555555555'; -- type:const；possible_key:index_exam_phone； key：index_exam_phone； key_len:55
explain select * from index_exam where phone = 1555555555555; -- type:all；possible_key:index_exam_phone； key：null； key_len:null

（4）模糊查询

explain select * from index_exam where profession like '软件%'; -- type:range; prossible_key:index_exam_pro_age_sta; key:index_exam_pro_age_sta; key_len:11
explain select * from index_exam where profession like '%工程'; -- type:all；possible_key:null； key：null； key_len:null

（5）or连接条件

explain select * from index_exam where id=10 or age=23; -- type:all；possible_key:PRIMARY； key：null； key_len:null
explain select * from index_exam where phone='1555555555555' or age=23;-- type:all；possible_key:index_exam_phone； key：null； key_len:null

（6）数据分布影响

 

3）SQL提示

use index()为建议使用的索引，至于数据库实际用哪个，还需要数据库自己评估

ignore index()为忽略的索引，不使用指定的索引

force index()为强制使用的索引，数据库必须使用指定的索引

create unique index index_exam_pro on index_exam(profession);
explain select * from index_exam use index(index_exam_pro_age_sta) where profession='软件'; -- type:ref；possible_key:index_exam_pro_age_sta； key：index_exam_pro_age_sta； key_len:11
explain select * from index_exam use index(index_exam_pro) where profession='软件'; -- type:const；possible_key:index_exam_pro； key：index_exam_pro； key_len:11

4）覆盖索引&回表查询

（辅助索引即二级索引） 

小测试：

使用username和password建立联合索引，根据联合索引可直接查询id值，不需要进行回表查询。

5）前缀索引

create index idx_email_5 on index_exam(email(5));-- 截取email的前五个字符作为前缀，建立前缀索引

6）单列索引和联合索引

注：

（1）创建联合索引时要考虑书写的顺序，对性能有影响；

（2）单列索引容易发生回表查询，效率较低，联合索引可以有效避免回表查询，查询效率较高。

七.索引设计原则

八.索引总结