13.SQL性能分析_profiles (工具三)
(1).profile介绍
show profiles 能够在做SQL优化时帮助我们了解事件都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持。
(1).查看当前数据库是否支持profile?
select @@have_profiling; #
(2).查看@@profiling是否打开
select @@profiling;
我们发现默认是关闭的，我们需要进行手动打开~~~
(3).开启我们的profils功能
set profiling =1;
(4).profiles 示列应用
1.查看全部语句的具体秒数
show profiles
- 查看指定Query_id语句各个阶段的耗时情况
show profile [cpu] for query 查询编号
未加上cpu的操作
加上cpu的消耗
14.SQL性能分析_explain
(1).explain执行计划
explain 或者 desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
(2).explain执行计划语法
[explain|desc] select 字段列表 from 表名 where 条件;
测试:
explain select *from tb_user;
(3).explain执行计划各个字段含义:
| 字段 | 含义 |
| id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(如果id相同，执行是顺序从上到下; 假如id不同，值越大，越先执行)。 |
| 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 的值越大越好。 |
(4).explain执行计划演示
- id标签 (并非是我们主键的id)
多对多表查询需要使用到中间表 student、course、student_course:
a.验证id相同的时候，顺序执行
-- 1.验证id相同的时候顺序执行。(查询所有学生的选课信息) explain select s.*,c.* from student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid;
b.验证id不同时候，id越大越先执行。 (子查询实现id不同)
-- 2.验证id不同，id越大越先执行。(查询选修了MySQL课程的学生_子查询) explain select s.* from student s,student_course sc where sc.courseid=(select course.id from course where course.`name`='MySQL') and s.id=sc.studentid;
结论: 子查询是由内向外的。
15.索引使用_验证索引效率
(1).正常查询一个数据_未使用索引
select *from tb_user; #有1千万条数据，耗时11.03秒 ⭐
(2).使用索引单独查询一条数据
select *frm tb_user where id=1; #耗时0.0秒 ⭐
(3).未使用索引单独查询一条数据
select *frm tb_user where name=''小米; #耗时20.0秒 ⭐
(4).给非索引字段创建一个索引
create index idx_user_name on tb_user(name); #先创建索引(创建的过程耗时很高) ⭐
先给这个字段创建索引再根据这个字段查询的时候，耗时降低很多
select *frm tb_user where name=''小米; #耗时0.0秒 ⭐⭐
结论: 给非主键字段创建好索引之后，再通过这个索引进行查询之后，我们的查询效率会显著提高。
16.索引使用_使用规则_最左前缀法 ⭐
(1).最左前缀法介绍
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列。索引将部分失效(后面的字段索引失效)。通俗的说就是:“我们创建联合索引的第一个字段，必须要存在于搜素字段中,where不在意顺序,order by,group by在意顺序。否则就会出现索引失效”
(2).最左前缀索引示列
我们这里对 age、profession、status这三个字段进行联合索引设置。
# 给字段常见联合索引 create index idx_user_pro_age_status on tb_user(profession,age,status);
1.查看执行计划
explain select *from tb_user where profession='软件工程' and age=31 and status='0';
(3).最左前缀法则索引失效场景
索引全部失效场景: 没有第一个索引字段。索引部分失效，跳过了中间列。
-- 索引全部失效: 因为没有第一个索引的字段 explain select *from tb_user where status='0'; -- 索引全部失效: 因为没有第一个索引的字段 select *from tb_user where status='0' and age=31; -- 索引部分失效: 因为没有第三个索引的字段(所以只有俩索引) explain select *from tb_user where profession='软件工程' and age=19; -- 索引部分失效：因为跳过了中间列(所以只有一个索引) explain select *from tb_user where profession='软件工程' and status='0'; -- 索引部分失效：因为最左索引在最后一个跳过了中间列 (所以只有一个索引) explain select *from tb_user where status='0' and profession='软件工程'; -- 索引全部生效：因为每一个联合索引字段都存在(不存在排序) explain select *from tb_user where age=31 and profession='软件工程' and status='0'; -- 索引： 假如key_len为null的话，那么就代表没有索引 explain select *from tb_user;
验证索引是否存在即 key_len的长度。
17.索引使用_索引失效
(1).范围查询
联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效。
#出现索引失效： 长度由原来的54变成49 explain select *from tb_user where profession='软件工程' and age>30 and status='0'; #恢复索引：长度回复成54，只需要加一个等于 explain select *from tb_user where profession='软件工程' and age>=30 and status='0';
(2).索引列运算
不要再索引列上进行运算操作，索引将失效。
#不使用运算操作 explain select *from tb_user where phone='17799990015'; # 使用运算操作 explain select *from tb_user where substring(phone,10,2)='15';
(3).字符串不加引号
字符串类型字段使用时，不加引号， 会进行自动转换。索引将失效。
# 字符串加引号会走索引 explain select *from tb_user where phone='17799990015'; # 字符串不加引号不会走索引 explain select *from tb_user where phone=17799990015;
(4).头部模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
# 尾部进行模糊匹配，索引不失效 explain select *from tb_user where profession like '软件%'; # 头部进行模糊匹配，索引失效 explain select *from tb_user where profession like '%工程';
(5).or连接条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都失效。
-- 前面有索引，后面没索引 失效 explain select *from tb_user where id=10 or age =23; -- 前面没索引后面有索引 失效 explain select *from tb_user where age=23 or phone ='17799990017'; -- 前面和后面都有索引 不失效 explain select *from tb_user where id=18 or phone ='17799990017';
(6).数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。(通俗的讲就是假如查询的数据占整张表的小部分，才会使用索引。大部分不会使用)
# 全表24条数据，查询小部分使用索引 explain select *from tb_user where phone >='17799990020'; # 全表24条数据，查询整表效率没有顺序高，所以不走索引 explain select *from tb_user where phone >='17799990000'; # 全表24条数据，查询大部分效率没有顺序高，所以不走索引 explain select *from tb_user where phone >='17799990010'; # 全表24条数据，profession都不为null，所以占小数部分走索引 explain select *from tb_user where profession is null; # 全表24条数据，profession都不为null，所以占大数部分不走索引 explain select *from tb_user where profession is not null;
18.索引使用_SQL提示 ⭐(索引推荐)
(1).什么是SQL提示?
# 1.查看profession是否已经是联合索引 explain select *from tb_user where profession='软件工程'; # 2再设置单列索引 create index idx_user_pro on tb_user(profession); # 3.再次查看索引: 我们发现仍然使用的是单列索引 explain select *from tb_user where profession='软件工程';
(2).SQL提示
SQL提示: 是优化数据库的一个重要等后端，简单来说，就是在SQL语句中假如一些认为的提示来达到优化操作的目的。
(3).SQL提示的三种分类
1. use index: 告诉数据库推荐用指定的索引，推荐被介绍与否另说
explain select * from 表名 use index(索引) where 字段 = '软件工程';
2.ignore index: 告诉数据库不使用指定索引
explain select * from 表名 ignore index(索引) where 字段= '软件工程';
3. force index: 告诉数据库必须走这个索引
explain select * from 表名 ignore index(索引) where 字段= '软件工程';
(4).SQL提示测试
-- 推荐使用 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 ignore index(idx_user_pro) where profession = '软件工程';
19.索引使用_覆盖索引&回表查询
(1).覆盖索引
尽量使用覆盖索引(查询条件使用了索引，并且需要返回的字段中，在该索引中全部能够找到)。减少select *。
- 知识小贴士:执行计划中最右侧的Extra。
- using index condition 或 NULL: 查找使用了索引，但是需要回表查询数据。
- using where; using index : 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询。
(2).覆盖索引示列
show index from tb_user; +---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | | tb_user | 0 | idx_user_phone | 1 | phone | A | 24 | NULL | NULL | | BTREE | | | | tb_user | 1 | idx_user_name | 1 | name | A | 24 | NULL | NULL | | BTREE | | | | tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | | tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | | tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | +---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 注意: 1.where后面的条件是聚集索引的话，那么就不会触发回表。 2.where后面的条件是二级索引或辅助索引，那么要看返回的字段是什么。 -- 1. 利用*号 会回表，因为查询的字段有包含非索引字段 explain select *from tb_user where profession='软件工程' and age=31 and status='0'; -- 2. 查询id 和 profession 不会回表因为返回的字段都是索引，索引二级索引下就能获取到 explain select id,profession from tb_user where profession='软件工程' and age=31 and status='0'; --3. 查询id 和 profession 和 status 不会回表因为返回的字段都是索引，索引二级索引下就能获取到 explain select id,profession,status from tb_user where profession='软件工程' and age=31 and status='0'; --4. 查询id 和 profession 和 status 和name 会回表因为name不是索引 explain select id,profession,status,name from tb_user where profession='软件工程' and age=31 and status='0';
注意:
- where后面的条件是聚集索引的话，那么就不会触发回表，直接高效查询。
- where后面的条件是二级索引或辅助索引，那么要看返回的字段是索引字段还是非索引字段，假如包含非索引字段的话，那么就会触发回表，否则直接二级查询返回了。
20.索引使用_前缀索引
(1).前缀索引
当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引边得很大，查询时，浪费大量得磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
(2).前缀索引语法
create index idx_xxxx on table_name(column(n))
(3).前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
(4).前缀索引示列
# 查看不重复邮箱的个数 select count(distinct email) from tb_user; # 查看 选择性=不重复索引数/总数 select count(distinct email)/count(*) from tb_user; +--------------------------------+ | count(distinct email)/count(*) | +--------------------------------+ | 1.0000 | +--------------------------------+ # 截取前5个字符 分辨率已经是0.95了，已经有很大的分辨度了 select count(distinct substring(email,1,5))/count(*) from tb_user; +--------------------------------+ | count(distinct email)/count(*) | +--------------------------------+ | 0.9500 | +--------------------------------+ # 创建索引 idx_email_5 并截取前缀索引的长度为5 create index idx_email_5 on tb_user(email(5)); # 查看执行计划，会经过回表查询的 explain select *from tb_user where email='jingke123@163.com';
21.索引的设计原则
(1).索引地七大原则
- 针对数据量较大，且
查询比较频繁的表建立索引。 - 针对于常作为
查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。 - 尽量选择
区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 - 如果是
字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表操作，提高查询效率。- 要
控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 - 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它，当优化器直到每列是否包含NULL值时，它可以更好的确定哪个索引最有效地用于查询。
