第二章：索引【mysql数据库-进阶篇】
最佳答案 问答题库1038位专家为你答疑解惑
2.索引
2.1 索引概述
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些
数据结构以某种方式引用(指向)数据，这样就 可以在这些数据结构.上实现高级查找算法,这种数据结构就是索引。
如上图所示，没有索引的表需要进行全表扫描，有索引查询数据高效；
索引的优缺点：
2.2 索引结构
2.2.1 概述
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:
不同的存储引擎对索引的支持情况：
我们平时所说的索引，如果没有特别指明，都是指B+树结构组织的索引
2.3.2 二叉树
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
为了解决上面的问题，可以使用红黑树。
但是红黑树在大数据量情况下，层级比较深，检索速度慢
2.3.3 B-Tree(多路平衡查找树)
以一颗最大度数(max-degree)为5(5阶)的b-tree为例(每 个节点最多存储4个key, 5个指针):
知识小贴士:树的度数指的是一个节 点的子节点个数。
2.3.4 B+Tree
以-颗最大度数(max-degree)为4 (4阶)的b+tree为例:
相对于B-Tree的区别：
1.所有数据都会出现在叶子节点
2.叶子节点形成一个单向链表
在mysql中B+Tree的特点：
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础. 上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
2.3.5 哈希索引
哈希索引就是采用一定的hash算法， 将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞) ,可以通过链表来解决。
Hash索引的特点：
- Hash索引只能用于对等比较(=，in)， 不支持范围查询(between, >， <, …
- 无法利用索引完成排序操作
- 查询效率高，
通常只需要一次检索就可以了， 效率通常要高于B+tree索引。（查询一次是仅限于不出现Hash碰撞的情况下）
存储引擎支持：
在MySQL中，支持hash索弓|的是Memory引擎，而InnoDB中 具有自适应hash功能，hash索引是存储弓|擎根据B+Tree索引在指定条件下自动构
建的。
2.3.6 面试题
为什么InnoDB存储引擎选择使用B+tree索引结构?
- 相对于二叉树，层级更少,搜索效率高;
- 对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一
页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的
高度，导致性能降低; - 相对Hash索引，B+tree 支持范围匹配及排序操作;
2.3 索引分类
2.3.1 按照类型分类
2.3.2 安装存储形式分类
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下几种：
2.3.3 聚集索引选取规则
- 如果存在主键，默认选择主键为聚集索引
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引只有一个，每个索引底下存的是这一行的数据。
2.3.4 回表查询
如果不按照主键进行查询，按照其他字段进行查询，就会进行一个回表查询操作，例如下面的查询代码：
select * from user where name=‘Arm’;
首先就会进入二级索引中进行查询，查询到“Arm”，然后根据“Arm”的对应的索引号到聚集索引中进行查询，查到对应的索引，然后查询这个索引下的行数据。这整个操作称为回表查询。
2.3.5 面试题
问题一：
以下SQL语句，那个执行效率高？为什么？
select * from user where id=10；
select * from user where name='Arm';
备注：id为主键，name字段创建的有索引
答：使用id查询效率高，因为id查询直接查询的聚集索引；而根据name查询，先查询二级索引，查找这个name对应的id，然后再进行一个回表查询，查询聚集索引，因此进行了两次查询；
问题二：
InnoDB主键索引的B+tree高度是多少呢？
因为主键使用的是聚集索引，索引是存在页中，页是有大小范围的。
假设：
一行数据大小为1k, 一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint, 占用字节数为8。
高度为2：
n8+(n+ 1)6= 161024 ,算出n约为1170
1171 16= 18736条数据
高度为3：
1171117116=21939856条数据
如果数据量更多的时候，就需要使用分库分表查询。
2.4 索引语法
2.4.1 数据准备
首先在我的linux系统中创建一个数据库和2张表，tb_stu和tb_user，已进行实验；
-- 创建数据库
create database if not exists itcast；
-- 创建表
use itcast;
-- 创建用户表
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 '系统用户表';SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- 创建学生表
DROP TABLE IF EXISTS `tb_stu`;
CREATE TABLE `tb_stu` (`id` int NOT NULL AUTO_INCREMENT,`age` int NOT NULL,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx_t_age`(`age`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4;插入数据
--- 往用户表中插入数据
INSERT INTO itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.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 itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');-- 往学生表中插入数据
INSERT INTO `tb_stu` VALUES (1, 1, 'tom');
INSERT INTO `tb_stu` VALUES (3, 3, 'cat');
INSERT INTO `tb_stu` VALUES (8, 8, 'rose');
INSERT INTO `tb_stu` VALUES (11, 11, 'jetty');
INSERT INTO `tb_stu` VALUES (19, 19, 'lily');
INSERT INTO `tb_stu` VALUES (25, 25, 'luci');SET FOREIGN_KEY_CHECKS = 1;2.4.2 语法
- 创建索引
CREATE[ UNIQUE | FULLTEXT ] INDEX index_ name ON table_ name ( index_ _col_ name... ) ;
- 查看索引
SHOW INDEX FROM table_ name ;
- 删除索引
DROP INDEX index_ name ON table_ name ;
2.4.3 实际案例
完成下列的需求：
- name字段为姓名字段,该字段的值可能会重复,为该字段创建索引。
- phone手机号字段的值, 是非空，且唯一-的，为该字段创建唯一索引。
- 为profession、 age、 status创建联合索引。
- 为email建 立合适的索引来提升查询效率。
- 删除idx_user_email索引。
需求1： name字段为姓名字段,该字段的值可能会重复,为该字段创建索引。
首先查看tb_user表中的索引
show index from tb_user;
从查询结果中可以看出，当前只有id建立了索引，并且索引的结构类型使用的是B+树。
给name字段建立索引：
-- 创建索引
create index idx_user_name on tb_user(name);
-- 查看索引
show index from tb_user;
从查询结果可以看出，索引的名称为idx_user_name,建立索引的字段是name，索引得到结构类型为B+树。
需求2：phone手机号字段的值, 是非空，且唯一-的，为该字段创建唯一索引。
create unique index idx_user_phone on tb_user(phone);
show index from tb_user;
需求3：为profession、 age、 status创建联合索引。
create index idx_user_pro_sta on tb_user(profession,age,status);
show index from tb_user;
注意：索引的顺序是有讲究的，后面会进行讲解
如图所示，同时创建了三个索引，但是三个索引的名字是一样的，则通过seq_in_index来进行区分。
** 需求4：为email建立合适的索引来提升查询效率**
create index idx_user_email on tb_user(email);
show index from tb_us
** 需求5：删除idx_user_email索引：**
drop index idx_user_email on tb_user;
show index from tb_user;
查看表已经不存在了。
2.5 SQL性能分析
2.5.1 SQL执行频率
MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT, UPDATE. DELETE、 SELECT的访问频次:
查询语法：
show global status like 'Com_______'
一共有7个下滑线匹配符。查询结果如下
主要我们看的就是insert、delete、update、select,主要查看当前数据库的更删改查的评率是多少。
2.5.2 慢查询优化
慢查询日志记录了所有执行时间超过指定参数(long_ query time, 单位:秒,默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf) 中配置如下信息:
#开启MySQL慢日志查询开关
slow_ query_ log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_ query_ _time=2
查询是否开启了慢查询：
show variables like 'slow_query_log';
显示没有开启，下面对配置文件进行修改：
在linux系统中输入下面的代码进行文件的修改：
vi /etc/my.cof
输入i进行修改：
按esc然后输入：wq进行保存,然后重启mysql。
systemtcl restart mysqld
再次查询，显示慢查询就开启了：
查看慢查询的日志：
cd /var/lib/mysql
查询这个日志文件：
-- 查询慢查询日志文件
cat localhost-slow.log
-- 实时查看慢查询日志文件
tail -f localhost-slow.log
从这个日志文件中就可以查询，是那个用户查询的结果超过了2秒，就可以对这个一句sql语句进行优化。
2.5.3 profile 详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_ profiling参数,能够看到当前MySQL是否支持profile操作:
查看是否支持profile，在数据库中输入下面语句：
select @@have_progiling;
默认profiling是关闭的，可以通过set语句在session/ global级别开启profiling:
-- 查看是否开启profiling开关
select @@profilling;
-- 开启profiling开关
Set profiling=1;
执行一系列的业务SQL的操作， 然后通过如下指令查看指令的执行耗时:
#查看每一条SQL的耗时基本情况
show profiles;#查看指定query_ id的SQL语句各个阶段的耗时情况
show profile for query query_ id; #查看指定query_ id的SQL语句CPU的使用情况
show profile cpu for query query_ id;
比如下面我进行以下几条sql语句，可以查询一下这些sql语句查询所使用的时间：
select * from tb_user where id=12;
select * from tb_user where name='荆轲';
select * from tb_user where email='jingke123@163.com';
可以查询到根据名字查询的时间最久；
那我们查询一下语句8 在什么地方查询的时间最久：
还可以查询CPU的耗费情况：
2.5.4 explain执行计划
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法:
#直接在select语句之前加，上关键字explain / desc
EXPLAIN SELECT 字段列表FROM 表名WHERE 条件;
explain执行计划各字段含义：
- id：select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下; id不同,值越本,越先执行)。
例如查询下面的多表查询语句，查询id相同的explain执行计划：
explain select s.*,c.*
from student s,course c,student_course sc
where s.id=sc.student_id and c.id=sc.course_id;
例如进行子查询，查询id不同的explain执行计划：
explain select *
from student s
where s.id in (select student_idfrom student_course scwhere sc.course_id =(select id from course c where c.name = 'MYSQL'));
-
select_type：表示SELECT的类型，常见的取值有SIMPLE ( 简单表,即不使用表连接或者子查询)、PRIMARY (主查询,即外层的查询)
UNION (UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等 -
type：表示连接类型，性能由好到差的连接类型为NULL、system、 const、 eq_ _ref. ref. range、index. all 。
-
possible_key：显示可能应用在这张表上的索引，一个或多个。
-
Key：实际使用的索引，如果为NULL,则没有使用索引。
-
Key_ _len：表示索引中使用的字节数，该值为索引字段最大可能长度,并非实际使用长度，在不损失精确性的前提下，长度越短越好。
-
rows：MySQL认为必须要执行查询的行数,在innodb引擎的表中，是一一个估计值， 可能并不总是准确的。
-
filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
注意：主要关注的就是type，表示当前的性能，其次就是possible key 和key，查看索引
2.5.5 面试题
问：在mysql数据库中可以使用那些方法监听mysql：
- global status 查看sql语句执行频率表
- 慢查询查看sql执行时间
- profile查看sql的执行过程
- explain 查看sql执行信息
2.6 索引使用
2.6.1 测试索引的效果
验证索引效率:
在未建立索引之前，执行如下SQL语句,查看SQL的耗时。
我们往数据库中插入200万条数据，验证索引的性能。
SELECT * FROM tb_ sku WHERE sn= '100000003145001;
通过查询可以看出，通过id查询只要0秒，通过sn查询需要2.15秒。
因为表中存在一个聚集索引，所以速度很快，为了验证索引的作用，给sn也加一个索引；
create index idx_sku_sn on tb_sku(sn);
show index from tb_sku;
加了索引之后，查询时间直接为0；
2.6.2 最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。 最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
- 使用三个索引
explain select * from tb_user where profession='软件工程' and age=31 and status='0';
三个索引都使用到了，并且从左至以此使用，索引的长度是54。
- 使用两个索引
-- 使用 professor和age查询
explain select * from tb_user where profession='软件工程' and age=31;
使用了2个索引，索引的长度为49，说明status的索引的长度为5.
-- 使用age和status来查询
explain select * from tb_user where age=31 and status=0;
此时走的是全表扫描，没有使用到索引，因为违背了最左前缀法则。
-- 使用 professor和status查询
explain select * from tb_user where profession='软件工程' and status=0;
使用了索引查询，因为使用了最左索引，但是索引的长度为47，说明只使用了professor索引，因为跳跃了前面的age索引，所以导致部分索引失效。
- 使用一个索引
-- 直接使用professor查询
explain select * from tb_user where profession='软件工程';
使用了索引，因为professor是最左的一个索引，并且professor索引的长度为47。
-- 单独使用age或者status查询
explain select * from tb_user where age=21;
explain select * from tb_user where status='0';
索引使用失效，使用的是全表查找。
注意：最左前缀法则的索引使用位置不影响该法则，只要使用了最左前缀索引即可：
explain select * from tb_user where age=31 and status='0' and profession='软件工程';
这是索引的顺序，我们打乱顺序查询：
查询结果显示，使用了三个索引。
2.6.3 范围查询
联合索弓l中，出现范围查询(>, <),`范围查询右侧的列索引失效``
查看下面的例子：
explain select * from tb_user where profession='软件工程' and age>30 and status='0';
从查询的结果中可以看到，查询的索引长度z只有49，说明只使用了professor索引和status索引。因为范围查询右侧的索引失效。
那么如何规避呢？
在业务允许的情况下，在大于或者小于后面加一个等于，就可以规避这种现象。
explain select * from tb_user where profession='软件工程' and age>=30 and status='0';
2.6.4 索引失效情况
- 在索引上进行运算操作
不要在索引列上进行运算操作，索引将失效。
比如我有下面这个业务，查询手机尾号是XXX的用户信息；
-- 该表的phone字段是创建了索引的
explain select * from tb_user where substring(phone,10,2)='15';
但是查询结果为全表查询，索引索引失效了。
- 字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。
explain select * from tb_user where phone=17799990001;
- 模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
explain select * from tb_user where profession like '软件%';
匹配尾部没有失效，professor索引的长度为47
explain select * from tb_user where profession like '%工程';
模糊匹配前面的索引失效了。
- or 连接条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
explain select * from tb_user where id=10 or age=23;
使用的是全表查询，id索引失效。
-- id和phone有索引
explain select * from tb_user where id=10 or phone='17799990001';
此时的索引没有失效。
解决办法就是针对or中的所有属性都建立索引。
- 数据分布的影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
比如我这张表中的数据绝大多数的电话号码都比17799990000大。
执行下面的查询，其中phone是有索引的。
explain select * from tb_user where phone>='17799990005';
但是进行的是全表扫描，因为使用索引查询，还不如直接使用全表扫描更方便。
但是
explain select * from tb_user where phone>='17799990013';
但是缩小范围了之后，执行同样的语句就使用了索引，这就是因为数据的分布导致的索引失效。
比如使用is函数
explain select * from tb_user where profession is null;
此时走索引，因为表中绝大多数都是非null；
explain select * from tb_user where profession is not null;
此时就不会走索引，因为not null的数据太多了。
2.6.5 SQL提示
SQL提示，是优化数据库的一一个重要手段,简单来说,就是在SQL语句中加入- -些人为的提示来达到优化操作的目的。
一共使用下面三个语句：
-- 使用索引
use index；
-- 忽略索引
ignore index；
-- 强制使用索引
force index；
例：
假如我要根据professor属性进行查询，但是这个属性有多个索引，应该根据那个索引查询呢？
select * from tb_user where profession='软件工程';
查看一下是根据那些索引进行查询的：
explain select * from tb_user where profession='软件工程';
从查询结果可以看出，mysql会根据联合索引idx_user_pro_sta进行查询。于是我就给professor指定索引进行查询；
explain select * from tb_user use index(idx_user_pro_sta) where profession='软件工程';
我可以指定索引，但是mysql接受不接受我的索引还是要mysql自己决定。
也可以选择不按照某个索引查询：
explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
此时就会按照联合索引查询。
explain select * from tb_user force index(idx_user_name) where profession='软件工程';
但是professor不能根据这个索引进行查询，就会自动进行全表扫描。
2.6.6 覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列，在该索引中把返回列全部能够找到)，减少select *。
下面举2个例子感受一下：
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';
此时不要再关注前面的，只要看最后面的一个部分，发现两条sql语句查询的结果是不一样的。那么怎么看呢？
出现了user in性能高，出现了Using index condition 性能低。
【知识小贴士】
- using index condition :查找使用了索引，但是需要回表查询数据
- using where; using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
为什么第一条sql不用回表，但是第二条sql需要回表呢？
因为第一条sql中的四个属性中都用到了索引，通过索引可以直接查询到结果，但是第二条sql使用了二级索引，但是name没有索引，所以就需要回表查询id，通过id查询所有的信息。
【思考】
一张表,有四个字段(id, username, password, status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案:
select id，username，password from tb_user where username='itcast';
首先根据username和password建立一个联合索引，联合索引下面挂的就是id聚集索引，这样建立的索引不用回表，且查询速度快。
2.6.7 前缀索引&回表查询
当字段类型为字符串(varchar, text等) 时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀,建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：
select index idx_xxx on table_name(column(n));
其中n表示截取字符串前面多少个字符作为索引。
前缀长度:
可以根据索引的选择性来决定，而选择性是指不重复的索引值( 基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
【举个例子】
比如我想以Email作为索引进行查询：
首先我们查询这个表中Email数据由多少条：
select count(*) from tb_user;
然后再查询不重复的数据有多少：
select count(distinct email) from tb_user;
最后的查询结果都是24条。
计算一下email字段的选择性：
select count(distinct email)/count(*) from tb_user;
计算出的选择性为1。
那么我应该截取多大长度的字符串呢？
通过截取不同长度的字符串，来计算一下各自的选择性：
select count(distinct substring(email,1,10))/count(*) from tb_user; -- 选择性=1
select count(distinct substring(email,1,8))/count(*) from tb_user;-- 选择性=0.9583
select count(distinct substring(email,1,7))/count(*) from tb_user;-- 选择性=0.9583
select count(distinct substring(email,1,6))/count(*) from tb_user;-- 选择性=0.9583
select count(distinct substring(email,1,5))/count(*) from tb_user;-- 选择性=0.9583
select count(distinct substring(email,1,4))/count(*) from tb_user;-- 选择性=0.9167
通过计算不同的长度的选择性时候发现截取长度为5-8个的时候，选择性都是一样的。如果你需要选择性很高就全部都截取，如果对检索时间想快一点，截取4个长度也是可以的。
下面对email截取前缀索引：
create index idx_email_5 on tb_user(email(5));
查询是否已经创建成功：
show index from tb_user;
测试一下是否使用到了前缀索引：
explain select * from tb_user where email='daqiao666@sina.com';
前缀索引的查询流程：
首先根据Email的前缀截取的字符创建索引，然后再根据索引下面存储的id进行回表查询，查询到这个所以所对应的元组。
2.6.8 单列&联合索引选择
- 单列索引:即一个索引只包含单个列。
- 联合索引:即一个索引包含了多个列。.
在业务场景中，如果存在多个查询条件,考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
【举个例子】
查看一下我的索引信息：
id和name都是单列索引，首先我们使用单列索引进行查询：
explain select id,phone,name from tb_user where phone='17799990010' and name='韩信';
然而实际查询的结果只用了phone索引，并且会进行回表查询。
于是我们将phone和name一起创建一个联合索引，看看会怎么查询：
-- 创建索引
create unique index idx_user_phone_name on tb_user(phone,name);
-- 再次引用索引查询，查看结果
explain select id,phone,name from tb_user where phone='17799990010' and name='韩信';
结果mysql还是选择使用了单列索引，并且还是会进行回表查询。
多条件联合查询时，MySql优化器会评估那个字段的索引效率更高，会选择该索引完成本次查询
如果想要一定使用某个索引，就得使用SQL提示进行查询；
explain select id,phone,name from tb_user use index(idx_user_phone_name) where phone='17799990010' and name='韩信';
此时就会选择使用联合索引进行查询，并且不会回表。
联合索引的结构：
联合索引的节点中存储的是phone和name，首先按照phone进行排序，然后再按照name进行排序，叶子节点中存储的就是id。但是使用联合索引的时候就需要注意最左前缀法则。
2.6.9 面试题
在那些情况下索引会失效：
- 违背最左前缀原则索引会失效
- 范围查询中<或者>后面的索引会失效，可以通过加=来解决
- 在索引上进行运算
- 查询字符串的时候没有加引号
- 模糊查询头部模糊匹配，尾部模糊匹配不会失效
- or 连接条件，前面有索引，后面没有索引
2.7 索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引。如果超过了几千几百万的时候就需要使用索引了。
- 针对于常作为查询条件(where) 、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择
区分度高的列作为索引，尽量建立唯一索引，区分度越高,使用索引的效率越高。 - 如果是
字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 - 尽量使用
联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引,节省存储空间，避免回表,提高查询效率。 - 要控制索引的数量,
索引并不是多多益善,索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 - 如果索引列不能存储
NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
99%的人还看了
相似问题
猜你感兴趣
版权申明
本文"第二章：索引【mysql数据库-进阶篇】"：http://eshow365.cn/6-40459-0.html 内容来自互联网，请自行判断内容的正确性。如有侵权请联系我们，立即删除！
- 上一篇: lambda表达式c++
- 下一篇: Vue 路由缓存 防止路由切换数据丢失 路由的生命周期