以下代码演示均在linux的mysql服务中运行，我用的是CentOS9
当然依旧使用datagrip或者idea也是可以的
create database if not exists bilibili; #创建数据库bilibili use bilibili; #选择bilibili数据库，即可进行下面的代码演示 drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删 show databases; #查看是否删除完成
准备数据
create table tb_user( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', phone char(11) comment '手机号', email varchar(40) comment '邮箱', profession varchar(20) comment '专业', age int comment '年龄', gender int comment '性别', status int comment '状态', createtime date comment '创建时间') comment '用户信息表';insert into tb_user(id,name,phone,email,profession,age,gender,status,createtime) values (null,'吕布',17799990000,'1vbu666@163.com','软件工程',23,1,6,'2001-02-02 00:00:00'),#id字段不用指定，我们设置了自增 (null,'曹操',17799990001,'caocao666@qq.com','通讯工程',33,1,0,'2001-03-05 00:00:00'), (null,'赵云',17799990002,'17799990@139.com','英语',34,1,2,'2001-03-02 00:00:00'), (null,'孙悟空',17799990003,'17799990@sina.com','工程造价',54,1,0,'2001-07-02 00:00:00'), (null,'花木兰',17799990004,'19980729@sina.com','软件工程',23,2,1,'2001-04-22 00:00:00'), (null,'大乔',17799990005,'daqiao666@sina.com','舞蹈',22,2,0,'2001-02-07 00:00:00'), (null,'露娜',17799990006,'luna_love@sina.com','应用数学',24,2,0,'2001-08-02 00:00:00'), (null,'程咬金',17799990007,'chengyaojin@163.com','化工',38,1,5,'2001-05-23 00:00:00'), (null,'项羽',17799990008,'xiaoyu666@qq.com','金属材料',43,1,0,'2001-9-18 00:00:00'), (null,'白起',17799990009,'baiqi666@sina.com','机械工程及其自动化',27,1,2,'2001-08-16 00:00:00'), (null,'韩信',17799990010,'hanxin520@163.com','无机非金属材料工程',27,1,0,'2001-06-12 00:00:00'), (null,'荆轲',17799990011,'jingke123@163.com','会计',29,1,0,'2001-05-11 00:00:00'), (null,'兰陵王',17799990012,'lanlinwang666@126.com','工程造价',44,1,1,'2001-04-09 00:00:00'), (null,'狂铁',17799990013,'kuangtie@sina.com','应用数学',43,1,2,'2001-04-10 00:00:00'), (null,'貂蝉',17799990014,'84958948374@qq.com','软件工程',40,2,3,'2001-02-12 00:00:00'), (null,'妲己',17799990015,'2783238293@qq.com','软件工程',31,2,0,'2001-01-30 00:00:00'), (null,'芈月',17799990016,'xiaomin2001@sina.com','工业经济',35,2,0,'2000-05-03 00:00:00'), (null,'嬴政',17799990017,'8839434342@qq.com','化工',38,1,1,'2001-08-08 00:00:00'), (null,'狄仁杰',17799990018,'jujiamlm0166@163.com','国际贸易',30,1,0,'2007-03-12 00:00:00'), (null,'安琪拉',17799990019,'jdodmlh@126.com','城市规划',51,2,0,'2001-08-15 00:00:00'), (null,'典韦',17799990020,'ycaunanjian@163.com','城市规划',52,1,2,'2000-04-12 00:00:00'), (null,'廉颇',17799990021,'lianpo321@126.com','土木工程',19,1,3,'2002-07-18 00:00:00'), (null,'后裔',17799990022,'altycj2000@139.com','城市园林',20,1,0,'2002-03-10 00:00:00'), (null,'姜子牙',17799990023,'37483844@qq.com','工程造价',29,1,4,'2003-05-26 00:00:00');
查询当前tb_user表有哪些索引
xxxxxxxxxx#目前只有一个索引，即主键索引primary为id。数据结构index_type是BTREE，即B树，但实际是B+树show index from tb_user;#这个命令展示的内容跟上一行一样，优点是展示的信息会对齐且简略。注意这个命令只能在命令行用。末尾不要加;号show index from tb_user\G
-- 1. name字段为姓名字段，该字段的值可以会重复，为该字段创建索引。(常规索引)
xxxxxxxxxx#idx_user_name是自定义的索引名称，一般的规范为idx_表名_字段名，idx就是index的缩写create index idx_user_name on tb_user(name);-- 验证。查看索引
xxxxxxxxxx#多了一个名为idx_user_name的索引，且索引结构为Btree(实际是B+树)，但我们创建该索引时并没有指定索引结构show index from tb_user;#对于InnoDB引擎，如果创建索引没有指定索引结构的话，该索引默认的索引结构就是B+树
-- 2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。(唯一索引)
create unique index idx_user_phone on tb_user(phone);-- 验证。查看索引
show index from tb_user;
-- 3. 为profession、age、status创建联合索引。(联合索引，关联这三个字段)
xxxxxxxxxx#在创建联合索引时，括号里面的字段填写的顺序是有讲究的，后面会学create index idx_user_pro_age_sta on tb_user(profession,age,status);-- 验证。查看索引
xxxxxxxxxx#查询显示的seq_in_index列是上面那个联合索引的各个索引在这个联合索引中的序号。所以说上面括号里面的字段填写的顺序是有讲究的show index from tb_user;
-- 4. 为email建立合适的索引来提升查询效率。(常规索引)
create index idx_user_email on tb_user(email);-- 验证。查看索引
show index from tb_user;
SQL性能分析
SQL执行频率，可以判定出当前数据库丹到底是以插入、更新、删除、查询为主。即查看更删改查在当前数据库的执行频率哪个比较大 当数据库是以增删改为主，那么优化的比重不大。当数据库是以查询为主，那么就要注重性能优化
MySQL客户端连接成功后，通过 show [session | global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert、 update、delete、select的访问频次:
xxxxxxxxxx#查看当前会话的全局状态信息，like是模糊匹配，com后面的是下划线，一个下划线代表一个字符*/show global status like 'com___';#六个下划线show global status like 'com_______';
查询到的结果中，Com_insert、Com_select、Com_update、Com_commit分别表示的是当前数据库的插入、查询、更新、删除的次数
谁的次数多，就可判定为当前数据库以谁为主
注意这节课是必须在linux的操作系统中 ！！！ 并且开两个会话窗口，一个是mysql>，另一个是[root@localhost ~]#，这两个会话都是同一个linux的MySQL数据库
当我们根据上一节课的知识查到当前数据库是以查询select为主，那么我们就要对当前数据库进行优化，我们到底是优化哪些查询SQL，即哪些select语句呢？ 我们就可以使用慢查询日志定位出哪些SQL语句的执行效率低，从而对这类SQL语句进行优化
慢查询日志 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志 show variables like 'slow_query_log'; #查看慢查询日志是否打开，OFF关闭，ON打开 MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下2条信息:
1、开启MySQL慢日志查询开关
xxxxxxxxxxslow_query_log=12、设置慢查询日志的时间为2秒，SQL语句执行时间超过两秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
在[root@localhost ~]#视图下输入如下1行:
vi /etc/my.cnf
按键盘上大写的G切换到文本最后一行 按键盘上的a表示插入模式
在弹出来的文本里输入如下3行:
#慢查询日志slow_query_log=1long_query_time=2
按键盘上的esc，然后输入:x，表示保存退出
在[root@localhost ~]#视图下输入如下1行，即可找到慢日志文件localhost-slow.log
cd /var/lib/mysql && ll
在[root@localhost ~]#视图下输入如下1行，即可查看慢日志文件localhost-slow.log的内容 内容有: 慢日志文件当刚开启时只记录了当前数据库版本、当前数据库端口。后续我们执行select时，就会记录所有执行时间超过指定参数的SQL语句
cat localhost-slow.log
然后在[root@localhost ~]#视图下重启MySQL服务器
systemctl restart mysqld
重新登录MySQL
mysql -u root -p查看慢查询日志是否打开，OFF关闭，ON打开
show variables like 'slow_query_log';
在[root@localhost mysql]#视图下输入如下1行，表示查看慢日志文件尾部实时输出的内容
tail -f localhost-slow.log
接着在mysql>视图下输入如下2行，表示执行一条查询语句
use bilibili;select * from tb_user;
回到[root@localhost mysql]#视图下，发现并没有新记录出现，原因是我们上面那条查询语句的执行时间在指定参数内，即这上面那条查询语句不是慢语句 当某一条查询语句的执行时间超过我们设置的2秒，这条查询语句就会被记录到慢日志文件里面
慢日志文件记录的内容: 哪个用户，哪个主机，耗时，锁了多少行，返回多少条记录，用的哪个数据库，当前操作的时间，执行的哪个SQL语句
我们就能够通过慢查询日志定位查询效率低的SQL
随便执行一些语句
select * from tb_user;select * from tb_user where id = 1;select * from tb_user where name = '白起';select count(*) from tb_user;
温馨提示:这节课用linux终端或xshell，是在不行就用windows的cmd命令行。否则会报红线执行不了
通过上节课的知识我们可以定位出例如哪条查询语句的执行时间超过2秒，即认定该查询语句是慢查询。此时有一个问题，当某条查询语句是1.9秒，
它可以是慢查询语句，但是它不超过2秒就不会被慢查询日志记录，我们就定位不到该1.9秒的查询语句，怎么办呢，就需要使用到这节课的profile操作
show profiles能够在做SQL优化时，帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作:
select @@have_profiling; #YES表示支持
虽然当前数据库支持profile操作，但是profile默认是关闭的，可以通过set语句在session/global级别开启profiling:
set profiling = 1;
然后就可以进行profile操作了。profile能够在我们执行SQL时，通过如下指令查看SQL的执行耗时(在命令行操作显示的才正常，可用linux终端或xshell):
1、查看每一条SQL的耗时基本情况。可以定位出哪条语句耗时多
show profiles;
2、查看指定query_id的SQL语句各个阶段的耗时情况。可以查出某条(查哪条就输入哪条的query_id)耗时多的语句耗时在哪些地方
show profile for query query_id;#每条语句都有自己的query_id，输入该语句的query_id值(数字)即可
3、查看指定query_id的SQL语句CPU的使用情况.可以查出某条(查哪条就输入哪条的query_id)耗时多的语句耗时在哪些地方，以及CPU耗时
show profile cpu for query query_id;#每条语句都有自己的query_id，输入该语句的query_id值(数字)即可
如果在dategrip上执行show profiles指令，会多出来很多莫名其妙的数据，建议用linux终端或xshell，是在不行就用windows的cmd命令行
上面第2点的查询某条语句耗时的具体地方，也就是某条语句从开始到结束执行的全过程耗时，过程包括如下
温馨提示:这节课用linux终端或xshell，实在不行就用windows的cmd命令行。否则有几个命令会报红线执行不了
explain执行计划
我们前面学的'执行频次'和'慢查询日志'和'profiles'是以执行时间来判定某条查询语句是否是慢查询语句，实际上这种判定是粗略的判定，并不能真正的
评判SQL语句的性能，我们需要使用explain查看SQL语句的执行计划，才能真正的评判SQL语句的性能。在后面学使用索引的时候，会大量使用explain
explain可以查看某条语句是否用到了索引、表的连接情况、表的连接顺序
explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序，语法如下
1、直接在select语句之前加上关键字explain / desc
explain select 字段列表 from 表名 where 条件;
select * from tb_user where id = 1;#正常查询explain select * from tb_user where id = 1;#使用explain关键字查询desc select * from tb_user where id = 1;#使用explain关键字查询。使用explain和desc的效果是一样的，比较常用的是explain
我们需要准备之前的3张表，其中学生表和课程表是多对多的关系，另一张是中间表用于维护学生表和课程表之间的多对多关系
create table student( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号') comment '学生表';insert into student values (null,'黛绮丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104');create table course( id int auto_increment primary key comment '主键ID', name varchar(10) comment '课程名称') comment '课程表';insert into course values (null,'Java'),(null,'PHP'),(null,'MySQL'),(null,'Hadoop');create table student_course( #学生表和课程表的中间表 id int auto_increment comment '主键' primary key, studentid int not null comment '学生ID', courseid int not null comment '课程ID', constraint fk_courseid foreign key (courseid) references course (id), constraint fk_studentid foreign key (studentid) references student (id)) comment '学生课程中间表';insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);
-- 分别查看上面的3张表数据
select * from course;select * from student;select * from student_course;
使用explain和desc的效果是一样的，比较常用的是explain，输出的是一个表格(里面记录了查询计划)，其中列头及意思如下:
id(需要用到上面新加的3张表，用这3张表通过SQL语句的查询计划来演示id。id演示比较麻烦，下面那一段都是演示id的，其实id就分两种情况，值相同和值不相同时有不同的执行顺序) select查询的序列号，表示查询中执行select字句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。如下两个需求:
(1)使用刚创建的3张表，查询学生的选课情况。多表联查，这里是3张表联查，需要两个条件才能消除无效的笛卡尔积
xxxxxxxxxxselect s.*,c.* from student as s,course as c,student_course sc where s.id = sc.studentid and c.id = sc.courseid;查询上面那条语句的执行计划。注意能显示出3条数据，重点是id字段，3条数据的id字段都显示1。如果id相同就表示表的执行顺序是从上往下，即3张表的执行顺序是s、sc、c
xxxxxxxxxxexplain select s.*,c.* from student as s,course as c,student_course sc where s.id = sc.studentid and c.id = sc.courseid;总结:在id值相同时，即如上多表查询的时候，执行的表的顺序是:表1、表1和表2的中间表、表2
(2)使用刚创建的3张表，查询选修了MySQL课程的学生。要求使用子查询
select id from course as c where c.name = 'MySQL';#查询MySQL的课程id，为3查询选修了id为3的课程的学生，需要去中间表查(中间表没有学生的名字，索引不可能查出学生的名字，只能查出学生的id)
xxxxxxxxxxselect studentid from student_course as sc where sc.courseid = 3;#结果是id为2、3的学生选修了MySQL课程然后根据学生id查出对应的是哪些学生
select * from student as s where s.id in (1,2);#in表示多选，注意不是范围选，而是多选
最后把3条散的语句组合起来成为1条
select * from student as s where s.id in (select studentid from student_course as sc where sc.courseid = (select id from course as c where c.name = 'MySQL'));
查询上面那条语句的执行计划(注意下面那行要在命令行执行，在datagrip执行的话，id分别为1、1、3，是错误的，在命令行执行id是1、1、2、3)才是正确的
explain select * from student as s where s.id in (select studentid from student_course as sc where sc.courseid = (select id from course as c where c.name = 'MySQL'));
总结:在id值不同时，id值越大就越先执行;id相同就表示表的执行顺序是从上往下。所以上面那行的语句的表按顺序执行分别是如下:
c、sc、
这个临时表就是上面的(select studentid from student_course as sc where sc.courseid = (select id from course as 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(最差)*/
(1)当查询的时候不访问任何表，性能才会为null。所以在业务系统中一般不太可能优化为null。null的例子如下
explain select 'A';#查询A就返回A，A不是我们表中的数据，即不需要查任何表
(2)当访问系统表的时候，性能才会为system
(3)根据主键或唯一索引进行访问，性能才会为const。const的例子如下
explain select * from tb_user where id = 1;#id是tb_user表的主键explain select * from tb_user where phone = '17799990014';#我们在上面的67行为tb_user表phone字段创建了唯一索引
(4)使用非唯一性索引进行访问，性能才会为ref。ref的例子如下
explain select * from tb_user where name = '貂蝉';#name是我们在上面的60行创建的常规索引
(5)连表查询时使用了唯一索引，性能才会为eq_ref
(6)当条件列的索引为primary和unique，性能才会为range
(6)使用索引进行访问，性能才会为index。index表示扫描遍历整个索引树，虽然比all快，但是性能也低
(7)我们在优化type的时候，尽量把type往前优化，尽量不要出现all。all表示全表扫描，性能低
possible_keys:可能用到的索引 显示可能应用在这张表上的索引，一个或多个
key:实际用到的索引 实际使用的索引，如果为null，则没有使用索引
key_len:索引的长度 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows:扫描的记录数 MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered:符合某条件的记录数百分比。根据主键或唯一索引查的时候，就是100%，根据常规索引查也可能出现100% 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好，最高是100%。共查询了多少行÷共返回多少行×100%=filtered
Extra:额外的信息 在前面8个关键字的字段没展示的值，就会在Extra字段里展示
准备数据。为下面导入200万条数据做准备
create table `tb_sku` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id', `sn` varchar(100) NOT NULL COMMENT '商品条码', `name` varchar(200) NOT NULL COMMENT 'SKU名称', `price` int(20) NOT NULL COMMENT '价格（分）', `num` int(10) NOT NULL COMMENT '库存数量', `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量', `image` varchar(200) DEFAULT NULL COMMENT '商品图片', `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表', `weight` int(11) DEFAULT NULL COMMENT '重量（克）', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称', `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称', `spec` varchar(200) DEFAULT NULL COMMENT '规格', `sale_num` int(11) DEFAULT '0' COMMENT '销量', `comment_num` int(11) DEFAULT '0' COMMENT '评论数', `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除', PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
下面导入的是200万条数据，相关sql也就是tb_sku.sql文件我会放出来，我的存放路径在linux的'/root/200wdata/tb_sku.sql'。导入的命令如下:
mysql -u root -p --local-infile=1;
load data local infile '/root/200wdata/tb_sku.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
查询tb_sku表的数据量有多少条
select count(*) from tb_sku;
查询tb_sku表的索引
show index from tb_user;
验证索引效率，在未建立索引之前，执行如下SQL语句，查看SQL的耗时
xxxxxxxxxxselect * from tb_sku where id = '1';#耗时短也就是查询效率很快，因为id是主键,即这里是根据主键id进行查询
select * from tb_sku where id = '1'\G #\G表示简化显示，不撑破一行。只能在命令行使用，在datagrip会报红线。末尾不要加;号
select * from tb_sku where sn = '100000003145001';#耗时长，原因:sn字段没有设置索引
为查询慢的语句的sn字段创建索引
create index idx_sku_sn on tb_sku(sn);#这行命令耗时会比较长，原因:sn字段正在创建B+树数据结构(innoDB引擎默认的数据结构)
然后再次执行相同的SQL语句，再次查看SQL的耗时
select * from tb_sku where sn = '100000003145001';#耗时变短
查看优化后的语句的执行计划
explain select * from tb_sku where sn = '100000003145001';#看返回的执行计划的key列，表示这条语句实际用到的索引
这节课就可以理解为，为一个普通字段创建索引后，当数据量庞大时，能高效的提升查询该字段的效率，总结就是空间换时间
这节课使用的是tb_user表，这里只学索引使用的第一个原则: 最左前缀原则，主要用于联合索引
如果索引了多列，也就是联合索引，那么就要遵守最左前缀法则。最左前缀法则指的是查询从索引最左列开始，并且不跳过索引中的列。如果跳跃了 某一列，索引将部分失效(后面的字段索引失效)
查询tb_user表的索引。可查到创建索引时的索引顺序，顺序优先级越上面位置越高。下面讲到的左右，指的是创建索引时的索引顺序，左表示上面位置的索引，右表示下面位置的索引
show index from tb_user;
上面那行返回的Column_name字段的profession、age、status就是一组联合索引对应的表字段名。根据最左前缀法则，在进行联合索引查询时，如果要想用到
这3个表字段名的联合索引idx_user_pro_age_sta、idx_user_pro_age_sta、idx_user_pro_age_sta，那么就意味着profession字段一定要有值，不能是null，
并且不能跳过age字段，不然status还是会失效。演示如下
xxxxxxxxxx#profession、age、status就是一组联合索引select * from tb_user where profession = '软件工程' and age = 31 and status = '0';#查看这条语句的执行计划，看返回的key列,索引总长度是93explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
上面两条语句的演示，是正常的，查询的时候把3个联合索引idx_user_pro_age_sta都执行了，符合最左前缀法则
xxxxxxxxxxselect * from tb_user where profession = '软件工程' and age = 31;#查看这条语句的执行计划，看返回的key列，索引总长度是88，也就是status字段的索引长度是93-88=5explain select * from tb_user where profession = '软件工程' and age = 31;
上面两条语句的演示，是正常的，查询的时候把3个联合索引idx_user_pro_age_sta都执行了，符合最左前缀法则
select * from tb_user where profession = '软件工程'; explain select * from tb_user where profession = '软件工程';#查看这条语句的执行计划，看返回的key列，索引长度总是83，也就是profession字段的索引长度是83
上面两条语句的演示，是正常的，查询的时候把3个联合索引idx_user_pro_age_sta都执行了，符合最左前缀法则
xxxxxxxxxxselect * from tb_user where status = '0';#查看这条语句的执行计划，看返回的key列,返回的key列是null，表示该语句已经导致索引失效，该条语句的执行没有走索引explain select * from tb_user where status = '0';
在使用索引进行查询时，并且是联合索引查询时，查询的条件一定要包括这组联合索引的第一个表字段名，对于上面来说就是不能少了profession这个表字段名
xxxxxxxxxxselect * from tb_user where profession = '软件工程' and status = '0';#profession、age、status就是一组联合索引#按理来说这两个索引长度之和应该是83+5=88，但是结果却是83，说明并没有走status索引，只走了profession索引explain select * from tb_user where profession = '软件工程' and status = '0';
在使用索引进行查询时，并且是联合索引查询时，只要最左边的第一个索引存在，就一定会走索引，但是还跳过了某个索引的话，那么这个被跳过索引的后面索引索引就会失效
题目。查询tb_user用户表的数据，条件如下，文下面那条语句在查询时走不走索引
xxxxxxxxxxselect * from tb_user where age = 31 and status = '0' and profession = '软件工程';答案，通过explain查看该语句的执行计划，发现返回的key列为idx_user_pro_age_sta，所以上面那条查询走了索引。第一个表字段名，不一定要写最前面，只要写了就行
xxxxxxxxxxexplain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';#索引之和是93，即走了完整的3个索引总结: 查询语句走索引的前提是遵循'最左前缀法则'，即查询的条件一定要包括这组联合索引的第一个表字段名，不一定要写最前面，只要写了查询条件为
这组联合索引的第一个表字段名就行。如果跳过了某个这组联合索引的表字段名，那么从这个被跳过的表字段名(包含)开始，后面的表字段名的索引都会失效
走索引的好处: 查询效率非常高效
注意: 即使索引失效，我们查询的数据也会正确的显示出来，只不过需要的执行时间会多，在查非常庞大的数据时，是很低效率的，所以好好学，别让索引失效
索引的长度根据个人而定，你的跟我的如果不同也很正常，我们可以根据返回的索引总长度推算出具体某个索引的长度，从而就知道哪些索引没走，哪些索引走了
这节课使用的是tb_user表，这里只学索引使用的第二个原则: 范围查询，主要用于复合索引(也就是联合索引，叫法不同)的范围查询
查询tb_user表的索引。可查到创建索引时的索引顺序，顺序优先级越上面位置越高。下面讲到的左右，指的是创建索引时的索引顺序，左表示上面位置的索引，右表示下面位置的索引
show index from tb_user;
xxxxxxxxxxselect * from tb_user where profession = '软件工程' and age > 30 and status = '0';explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';#explain查看执行计划根据上面那行的执行计划，我们可以知道返回的索引长度总和是88，即走了profession、age索引，没走status索引
原因:age > 30 是范围查询，在范围查询右边的索引都会失效，注意age > 30这个范围查询是正常的，失效的是右边的那些字段条件
解决:常用的规避方案就是在业务允许的情况下，使用>=、<=，不要使用>、< 、!=。解决方案的演示如下
xxxxxxxxxxexplain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';#explain查看执行计划根据explain执行计划，上面那行返回的索引总长度是93，即完整的走了3个索引
查看tb_user表的数据
select * from tb_user;
查看tb_user表的索引
show index from tb_user;#我们在前面的课给phone字段设置了单列索引
-- 第1种情况: 索引列进行运算
不要在索引列上进行运算操作(通配符的模糊查询是例外。'%15'、'%01%'不会触发索引。'01%'会走索引，满足最左前缀法则)，索引将失效
根据phone字段进行查询的时候，是会走phone索引的
select * from tb_user where phone = '17799990015';
根据explain执行计划，验证上面那条语句是否会走phone索引
xxxxxxxxxx#答案是走了phone索引，索引总长度是45,也就是phone索引的长度是45explain select * from tb_user where phone = '17799990015';
查询手机号最后两位是15的用户，观察phone索引是否会失效。字符串截取，从第10位(包含)开始截取，截取两位
select * from tb_user where substring(phone,10,2) = '15';
根据explain执行计划，验证上面那条语句是否会走phone索引
xxxxxxxxxx#结构是返回的key列为null，也就是没走phone索引，即phone索引失效explain select * from tb_user where substring(phone,10,2) = '15';
在phone索引使用通配符:'%15'、'%01%'本身是不会触发索引的，属于模糊查询; '01%'本身会走索引，满足最左前缀法则
explain select * from tb_user where phone like '%15';explain select * from tb_user where phone like '%01%';explain select * from tb_user where phone like '01%';
-- 第2种情况: 字符串不加引号
-- 字符串类型字段使用时，不加引号，索引将失效
x
#索引有效explain select * from tb_user where phone = '17799990015';#phone字段的值是字符串类型，使用时没有加引号，导致索引失效explain select * from tb_user where phone = 17799990015;
-- 第3种情况: 模糊查询
-- 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
xxxxxxxxxxexplain select * from tb_user where phone like '%15';#失效explain select * from tb_user where phone like '%01%';#失效explain select * from tb_user where phone like '01%';#不失效注意当最左索引是整型时，like '11%'照样会失效，varchar类型不会
-- 第4种情况: or连接的条件
-- 用or分隔开的条件，如果or前面的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
开始下面的演示之前，先讲一下我们的age字段，age字段在前几节课设置了复合索引(也就是联合索引，叫法不同)，当我们想用age的话，就一定
要用profession字段索引，因为profession是这个联合索引中的最左索引，如果单单只用age索引的话，那么跟没用索引的效果是一样的，我们
就认为age此时不是索引字段
explain select * from tb_user where id = 10 or age = 23;#左索引，右不是索引，结果是两个索引都没有走，即两个索引都失效explain select * from tb_user where age = 23 or name = '吕布';#左不是，右是索引，结果是两个索引都没有走，即两个索引都失效explain select * from tb_user where id = 10 or name = '吕布';#左是索引，右是索引，结果是两个索引都走了，即两个索引都生效
解决:为age创建一个常规索引
xxxxxxxxxxcreate index idx_user_age on tb_user(age);此时再去执行上面那3条演示语句，索引就都生效啦
-- 第5种情况: 数据分布影响
-- 如果MySQL评估使用索引比全表慢，则不使用索引。简单说就是全表扫描如果都比你使用索引快的话，那么就不让你使用索引
开始下面的演示之前，先讲一下我们tb_user表的数据有24条。并且这24条数据都是非空的，也就是not null
explain select * from tb_user where phone >= '17799990020';#走索引explain select * from tb_user where phone >= '17799990000';#不走索引，即索引失效explain select * from tb_user where phone >= '17799990011';#不走索引，即索引失效explain select * from tb_user where phone >= '17799990012';#走索引
当索引字段的条件要求MySQL查询大于或等于该表一半的数据时，MySQL就认为跟我走全表扫描没区别，即使你用索引我也不会用(ps:好傲娇的MySQL)
需要注意的情况，如下
xxxxxxxxxx#走索引，因为你只要求MySQL查一条数据，当这个字段有索引时，MySQL就会走索引explain select * from tb_user where phone is null;#走索引，原因同上explain select * from tb_user where phone = '17799990012';#不走索引，原因是在tb_user表中，绝大多数都是not null数据，查询大于或等于该表一半的数据时就不走索引explain select * from tb_user where phone is not null;总结: 当MySQL认为它走索引比较快时，才会走索引，不然不走
SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的
1、告诉数据库，我们要使用哪个索引，只是建议，MySQL可能不会采用
use index例如: explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
2、告诉数据库，不可以使用哪个索引
ignore index例如: explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
3、告诉数据库，必须使用哪个索引，强制性
force index例如: explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';*/
开始下面的演示之前，先讲一下我们的profession字段，profession字段在前几节课设置了复合索引(也就是联合索引，叫法不同)，且正好是最左索引
xxxxxxxxxxselect * from tb_user where profession = '软件工程';#走索引。原因:profession是这个联合索引中的最左索引，满足最左前缀法则explain select * from tb_user where profession = '软件工程';
#我们给profession字段再创建一个单列索引
create index idx_user_pro on tb_user(profession);
#再执行一次查询，观察MySQL会走profession的哪个索引
xxxxxxxxxx#答案是走的联合索引，没有走单列索引explain select * from tb_user where profession = '软件工程';
#我们如果不想MySQL走联合索引，指定它走单列索引，怎么做呢。也就是如果当该字段有多个索引，MySQL需要按照我们指定的索引来进行查询。如下
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
#如何让MySQL在查询的时候，不使用我们设置的单列索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
#如何让MySQL在查询的时候，强制使用单列索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
在进行下面的演示之前，由于tb_user在前面的演示中，字段被添加了很多索引，我们先清除一下后续演示不需要的索引
show index from tb_user;
drop index idx_user_email on tb_user;
drop index idx_user_cre on tb_user;
drop index idx_user_age on tb_user;
drop index idx_user_pro on tb_user;
覆盖索引
什么是 '查询需要返回的字段' : 简单理解就是select后面跟的那个词
覆盖索引指的是一条select查询语句在查询的过程中，使用到了索引，并且查询需要返回的字段，在该索引中已经全部找到了对应的字段值，这类的操作 就称为覆盖查询。前面学的是where之后的条件怎么去规避索引失效。这节课我们学的是查询需要返回的字段怎么去规避索引失效。 在实际业务开发中，尽量使用覆盖索引，较少select *
例如下面的4个演示，注意我们主要关注的是select之后的词，from后面的我们不需要过多纠结*/
xxxxxxxxxxexplain 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';通过上面的四条演示，可发现前2条的执行计划返回的结果，中的最后一列Extra，前3条语句都是为Using where、Using index。我查的是为null，跟老师对不上
后2条语句的执行计划的Extra列为Using index condition。我查的是为null，跟老师对不上
知识小贴士:
1、using index condition: 查找使用了索引，但是需要回表查询数据。也就是出现这个，就说明这条语句回表查询了，回表查询的后果就是查询效率低
2、using where: 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
3、using index: 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
4、联合索引本质是二级索引，只不过单列存的是一个值加主键，多列是多个值加主键
主键索引称为聚集索引，也就是id也叫聚集索引。聚集索引的B+树的底部叶子节点的数据是某个id值的一整行数据
除主键索引，其他的索引就是二级索引(或叫辅助索引)，二级索引的B+树的底部叶子节点的数据是该索引对应的id值
为什么上面查询这id, profession, age ,status四个字段不用回表呢。原因: 是我们在前面几节课把profession, age ,status字段设置了联合索引，联合索引
是属于二级索引(或叫辅助索引)，二级索引的B+树的底部叶子节点上的值是id，所以我们在二级索引这里就找到了id, profession, age ,status这四个字段，
把要找的都找完了，就没必要再拿着id主键到一级索引里面查(这步就叫回表查询)，也就没有经历回表查询。如果还要求再查一个name字段，由于name字
段不是主键索引，也不是联合索引中的一个，所以要把name字段的值查出来，就必须得经历回表查询
如果没有经历回表查询，也就是只在二级索引的B+树就找到了要找的所有数据，所用到的索引就称为覆盖索引
什么是回表查询
先走二级索引去查，把查询到的结果，拿去聚集索引中查
总结如下:
不回表
select 主键索引, 联合索引1, 联合索引2, 二级索引... from
select 联合索引1, 联合索引2, 二级索引... from
select * from 表名 where 主键索引的条件
回表
select 主键索引, 非索引 from
select 非索引 from
select * from 表名 where 非主键索引的条件
讲下面的知识的时候，需要知道例如表的varchar字段存储了很长的值，那我们给这个varchar字段建立索引，就会让索引变得很大 或者说如果需要我们通过文章内容进行查询，这个内容是text类型的字段，存储了上万个字符，我们对这么多字符建立索引，会导致索引变得庞大
前缀索引 当字段类型为字符串(varchar, text等)时，有时候需要索引很长的字符串，这会让索引变得很大(索引长度大)，查询时，浪费大量的磁盘IO，影响查询效率。 此时可以只将字符串的一部分前缀截取出来，建立前缀，这样可以大大节约索引空间，从而提高索引效率。语法如下
xxxxxxxxxxcreate index idx_自命名 on 表名(字段(n)); #n表示要截取该字段前面的几个字符来构建索引，n可以叫前缀长度前缀长度 可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性 是1，这是最好的索引选择性，性能也是最好的
如何获取tb_user表的email字段的选择性
select count(distinct email) / count(*) from tb_user;select count(distinct substring(email,1,5)) / count(*) from tb_user;
演示如下
查看tb_user表的数据
select * from tb_user;
查看tb_user表的总记录数，也就是共有多少行
select count(*) from tb_user;#结果是24
查看tb_user表的email字段中不为空的值有多少个
select count(email) from tb_user;#结果是24
查看tb_user表的email字段中不重复的值有多少个，也就是对email字段的值进行去重
select count(distinct email) from tb_user;#结果是24
当截取前10个，查看此时tb_user表的email字段的选择性
select count(distinct substring(email,1,10)) / count(*) from tb_user;#结果是1.0000
当截取前9个，查看此时tb_user表的email字段的选择性
select count(distinct substring(email,1,9)) / count(*) from tb_user;#结果是0.9583，表示此时就出现重复
当截取前6个，查看此时tb_user表的email字段的选择性
select count(distinct substring(email,1,6)) / count(*) from tb_user;#结果是0.9583，表示此时就出现重复
当截取前5个，查看此时tb_user表的email字段的选择性
select count(distinct substring(email,1,5)) / count(*) from tb_user;#结果是0.9583，表示此时就出现重复
当截取前4个，查看此时tb_user表的email字段的选择性
select count(distinct substring(email,1,4)) / count(*) from tb_user;#结果是0.9167，表示此时就出现重复
总结:尽量截取最少的字符作为前缀索引，让选择性尽量接近或等于1，这样查询效率越高，也可降低索引存储的空间
例如上面那里的演示，如果业务要求选择性要尽量高，那么截取前10个是最优的
例如上面那里的演示，如果业务要求平衡选择性和索引体积，也就是让选择性高且索引体积小，那么截取前5个是最优的
截取的越少，索引的体积就越小，不过不能截取的太少，要注意这个'最优'
'此时就出现重复'的后果: 可能会查多个匹配的值，即可能会查出跟我们要查的很相似，但不是我们要查的值，也就是查出来多余的值
当确定好截取5个之后，如何为email字段创建前缀索引(我们命名这个email的前缀索引叫idx_email_5)
show index from tb_user;#查看tb_user表有那些索引create index idx_email_5 on tb_user(email(5));
验证前缀索引
select * from tb_user where email = 'xiaomin2001@sina.com';
查看上面那条语句的explain执行计划，在key_len列的值是23，即我们的前缀索引(idx_email_5)的索引长度为23
explain select * from tb_user where email = 'xiaomin2001@sina.com';
前缀索引的查询流程如下
1、把表中的id列创建主键索引(也叫聚集索引), 聚集索引在B+树查的是某个id值对应的一整行的数据
2、把email列的每个值的前5个字符截取出来，作为5个前缀索引，前缀索引就是二级索引(也叫辅助索引)，二级索引在B+树查的是某个二级索引对应的id值
3、前缀索引必然回表
单列索引与联合索引
单列索引:即一个索引值包含单个列 联合索引:即一个索引包含了多个列
#查看tb_user表的索引
show index from tb_user;
#查看下面那条语句的执行计划。注意，我们的name和phone字段在前面的学习中设置了单列索引,并且在前面的学习中我们还把phone字段设置成了唯一索引
explain select id,phone,name from tb_user where phone = '17799990010' and name = '韩信';#上面那行返回的结果的key列只有idx_user_phone，即只有phone索引生效了，也就是查询name的时候，name没有使用索引
#为phone和name创建联合索引。由于phone本身是唯一索引，所以创建的phone和name的联合索引idx_user_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 = '韩信';#还是只使用了idx_user_phone索引。执行计划的Extra列为null，表示需要回表查询 #多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询
#我们使用前几节课学的SQL提示，告诉MySQL应该使用idx_user_phone_name，然后再次查看执行计划，查看索引的使用情况
explain select id,phone,name from tb_user use index(idx_user_phone_name) where phone = '17799990010' and name = '韩信';#此时使用的就是phone和name的联合索引idx_user_phone_name。执行计划的Extra列为Using index，表示此时用的是覆盖索引，不需要回表查询，效率更高
注意:多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询
创建联合索引的时候，位置谁在前面谁就是最左索引。使用联合索引的时候，位置随便，只要用到了最左索引就行
为了提高SQL语句的查询效率，应该对什么样的表、什么样的字段建立什么样的索引，就涉及到这节课的设计原则
索引设计原则
1、对于数据量较大(超过100w)，且查询比较频繁的表建立索引
2、对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字符建立索引。也就是例如在where后面出现的字段建立索引
3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4、如果是字符串类型的字段，字段的长度比较长的时候，可以对于字段的特点，建立前缀索引(要考虑前缀的区分度)
5、尽量使用联合索引(要遵循最左前缀法则)，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7、如果索引列不能存储null值，请在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询