第二章：索引【mysql数据库-进阶篇】

2.索引

2.1 索引概述

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些
数据结构以某种方式引用(指向)数据，这样就 可以在这些数据结构.上实现高级查找算法,这种数据结构就是索引。

如上图所示，没有索引的表需要进行全表扫描，有索引查询数据高效；
索引的优缺点：

优点	缺点
提高数据检索的效率，降低数据库的I0成本	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进耗。行INSERT、UPDATE、DELETE时，效率降低。

2.2 索引结构

2.2.1 概述

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询!
R-tree(空间索引)	空间索引是MyISAM引擎的-一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

不同的存储引擎对索引的支持情况：

我们平时所说的索引，如果没有特别指明，都是指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_id
    from student_course sc
    where 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 索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。如果超过了几千几百万的时候就需要使用索引了。
2. 针对于常作为查询条件(where) 、排序(order by)、分组(group by)操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高,使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引,节省存储空间，避免回表,提高查询效率。
6. 要控制索引的数量,索引并不是多多益善,索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。