SQL性能分析工具有助于我们能够快速定位哪些SQL语句执行效率低下，从而有针对性的进行优化，这里我们优化的主要语句是SQL的DQL语句也就是查询语句。而在优化select查询语句的时候呢，索引的优化占据相当高的比重。

首先我们得知道MySQL服务器中SQL语句的执行频率如何，可通过show [sessison|global] status 命令查服务器的状态，然后通过show global status like 'Com_______'; 查看当前数据库的insert、update、delete、select的访问频次。

show global status like 'Com_______'; #匹配后面的7个字符串

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |      # 删除
| Com_insert | 0 |       # 插入
| Com_repair | 0 |
| Com_revoke | 0 |     
| Com_select | 4 |       # 查询
| Com_signal | 0 |
| Com_update | 0 |     # 更新
| Com_xa_end | 0 |
+---------------+-------+
10 rows in set (0.00 sec)

#  当有增删改查的操作时 对应value会改变
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_insert    | 1     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 3023  |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.01 sec)

通过这条语句可以判断出当前数据库是以查询为主还是以写入为主，如果一个数据库它查询占据了绝大部分，那么此时我们就要针对这类的数据库当中的SQL来进行优化了。好了，这里是我们介绍的第一种SQL性能分析工具，通过这条指令来查看SQL的执行频率，为我们的SQL优化提供支撑。

第二种、慢查询日志

由于第一种情况只能知道当前数据库的select执行频率，并不知道具体要对哪些select语句进行优化，此时我们就要通过MySQL数据库的慢查询日志来定位哪些SQL语句执行效率比较低，从而对这类SQL语句进行优化。

慢查询日志记录了所以执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志，MySQL的慢查询日志默认是没有开启的，需要在my.cnf配置文件中添加如下信息:

#开启MySQL慢日志查询开关

slow_query_log=1

#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录到文件里

long_query_time=2

配置完毕之后，通过以下命令重启MySQL进行测试，查看慢日志文件中记录的信息。

或者set global slow_query_log=ON; set global long_query_time=2;无需重启。

mysql> show global variables like 'long_query_%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.02 sec)

mysql> show global variables like 'slow_query_%';
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /data/mydata/mdata/logs/slow.log |
+---------------------+----------------------------------+
2 rows in set (0.01 sec)

tail -n 20 /data/mydata/mdata/logs/slow.log
select * from king_user;
# Time: 2022-04-10T10:02:40.254528Z
# User@Host: root[root] @ localhost []  Id:  1002
# Query_time: 0.000440  Lock_time: 0.000140 Rows_sent: 10  Rows_examined: 10
SET timestamp=1649584960;
select * from king_user;
/data/soft/mysql/bin/mysqld, Version: 5.7.32-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2022-04-11T06:55:03.673298Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 0.058483  Lock_time: 0.000569 Rows_sent: 458  Rows_examined: 458
use xjqx_game_s410;
SET timestamp=1649660103;
select * from bag;
# Time: 2022-04-11T06:57:15.320498Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 0.010096  Lock_time: 0.000155 Rows_sent: 458  Rows_examined: 458
SET timestamp=1649660235;
select * from yy_vip;

第三种，profile详情

有些查询日志非常接近我们设置的慢日志的时间，这一类日志的执行效率也是很低的，但是没有被记录下来，这一类日志也是要优化的。如何定位这一类日志呢?我们可以借助profile详情，show profiles指令能够在做SQL优化的时候帮助我们了解时间都耗费到拿了，通过have_profiling参数,能够看到当前MySQL是否支持profile操作。

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.02 sec)

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling

mysql> show variables like 'profi%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.02 sec)

set profiling=1;

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'profi%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.01 sec)

我先执行一些select语句

select * from xjqx_game_s410.account

select * from king_user where name = '石秀';

select * from king_user where id = 10;

通过 show profiles指令可以看到查询语句所耗费的时间，同时也看出根据id查询的效率比根据name查询的效率高出很多。为什么，不了解的可以回顾上一节的内容。

show profiles # 是查看每一条SQL指令的耗时时间。

show profile for query Query_ID # 查看指定id的SQL语句各个阶段的耗时情况

mysql> show profile for query 4;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000175 |
| checking permissions | 0.000022 |
| Opening tables | 0.000108 |
| init | 0.000057 |
| System lock | 0.000023 |
| optimizing | 0.000010 |
| statistics | 0.000024 |
| preparing | 0.000022 |
| executing | 0.000006 |
| Sending data | 0.054713 |    # 发送数据这一块比较耗时
| end | 0.000033 |
| query end | 0.000018 |
| closing tables | 0.000022 |
| freeing items | 0.000072 |
| logging slow query | 0.000153 |
| cleaning up | 0.000036 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
mysql>

show profile cpu for query Query_ID #查看指定ID 的SQL语句的CPU使用情况

第四种、explain执行计划

以上三种都是通过时间的评判一条SQL语句的性能，执行时间短说明SQL语句的性能高反之低，这种判定只能算是粗略的,并不能真正的评判一条SQL语句的性能。我们要想看一条SQL语句的性能还需要借助explain来查看SQL的执行计划。explain或者desc命令可以获取mysql如何执行select语句的信息，包括在select语句执行过程中表是如何连接和连接的顺序。语法如下:

explain/desc select 字段列表 from 表名 where 条件;

desc select * from king_user where name='糜夫人';

explain select * from bag where id=4100000027;

explain执行计划个列含义

id： select查询的序号，表示查询中执行select子句或者是操作的顺序，id相同的情况下从执 行顺序从上到下,值越大越先执行.

select * from student as s,caurse c,student_caurse sc where sc.id=c.id and c.id=sc.id;

explain select * from student as s where s.id in (select stu_id from student_caurse as sc where caur_id=(select id from caurse as c where c.id=3));

select_type:表示select的类型，simple简单表(即不使用表连接和子查询),primary（主查询，即外层查询）,union(union中的第二个或者后面的查询语句)，subquery(select/where之后包含了子查询)等。

type: 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。当我们根据主键或者唯一索引查询时 type为const

非唯一性索引为:ref

不走索引的情况下是all 全表扫描

possible_key 表示可能应用到这张表上的索引，一个或者多个。

key 显示实际用到的索引,没有则NULL

key_len 表示索引用到的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不 损失精度的前提下，长度越短越好。

rows MySQL认为必须要执行的行数，在innodb引擎中，是一个估计值，可能并不总是标准 的。

filtered 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

Extra 额外的信息

索引使用规则

索引的最左前缀法则，如果索引关联了多列（联合索引）,要遵守最左前缀法则,最左前缀法则指的是查询从索引的最左边列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引将失效)。

索引的最左边字段必须存在，和字段的顺序无关。

desc select * from king_user where profession='如雷三叉戟' and age=23 and status=1;

desc select * from king_user where profession='如雷三叉戟' and age=23;

desc select * from king_user where profession='如雷三叉戟';

desc select * from king_user where age=23 and status=1; #不走索引

跳过age字段,后面的字段status索引不生效

2、当使用了范围查询时(> <)，右边的列索引失效,尽量使用>= <= =。

3、不要在索引列上进行运算操作，索引将失效。

select * from king_user where substring(phone,10,2)='16'; #截取后两位

4、字符串类型的字段，如果不加引号,索引失效。

5、模糊查询，如果仅仅是尾部模糊匹配，索引是不会失效的，如果是头部模糊匹配索引将失效。 如 '%log'，切记大数量时不能这么玩，会全表扫描。

6、or连接的条件，如果用or分割开的条件，or前的条件中的列有索引，后面的列没有索 引，那么涉及到的所有都不会用到。

desc select * from king_user where id=9 or age=25;

desc select * from king_user where age=25 or phone='13056980266';

由于age没有索引，所以即使id、phone有索引，索引也会失效，要对age建立索引才行。

create index idx_usr_age king_user(age)

7、数据分布影响，如果MySQL评估使用索引比全表扫描更慢，则不使用索引。

is not null/is null 同样根据数据分布进行评估。

8、使用SQL提示，规定使用哪个索引

use index:

desc select * from king_user use index(idx_user_pro) where profession='张飞';

ignore index:

desc select * from king_user ignore index(idx_user_pro) where profession='张飞';

force index:

desc select * from king_user force index(idx_user_pro) where profession='张飞';

。。。。。

9、覆盖索引，就是尽量使用索引列，减少使用select *.

desc select id,age,profession from king_user where profession='张飞' and age=31 and status=1;

如果执行计划的Extra是:

a、Extra: Using where; Using index 表示查找使用了索引，但是需要的数据都在索引 列中能找到，所以不需要回表查询，效率高。

select id,name from king_user where name='Arm';

b、Extra: Using index condition 查找使用了索引,但是需要回表查询数据。

select id,name,gender from king_user where name='Arm'; # gender 没有找到，根据id 值回表查询，找到这行数据再提起gender值返回。所以说使用select * 很容易就出现回 表查询，除非你创建索引字段的联合索引，否则改回哪回哪查，导致性能降低。

面试题: 有以下语句，请问如何创建索引，使查询最优？

select id,username,password from king_user where username='张三';

根据二级索引的特点，叶子节点存放的是id值，可以考虑给username和password建立 联合索引,查询列都是索引列，所以不会回表查询。

前缀索引

当字段类型为字符串varchar、text时，有时候需要索引很长的字符串,这样会使索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间,从而提高效率。

语法: create index idx_xxxx on 表名(column(n));

如何确定前缀的长度，可以根据索引的选择性来决定，而选择性是指不重复的索引值和数据表的记录总数的比值，索引的选择性越高查询效率越高。

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好。

select count(distinct email)/count(*) from king_user;

select count(distinct substring(email,1,5))/count(*) from king_user;

如下 分别截取3,2,1个字符作为前缀的话不是最好的

此处我们截取5个字符作为前缀索引

create index idx_email_5 on king_user(email(5));

查看执行计划也是用到索引的

实际生产环境中如果使用了长字符串或者大文本字段，我们就可以使用前缀索引来缩小索引的体积。（前缀索引需要回表查询）

单列索引与联合索引的选择

单列索引，即一个索引只包含单个字段。（如果查询中包含多个单列查询，则MySQL会自己判断哪个索引最优然后选择它，剩下的列即使是有索引的也无效。导致回表查询影响性能。）

提示: 在多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该字段完成本次查询。

联合索引，一个索引包含多个字段。

在实际业务场景中，如果存在多个查询条件，考虑针对于查询字段建立的索引时，建议建立联合索引，而非单列索引。

索引的设计原则

1、针对那些数据量比较大,且查询比较频繁的表建立索引。

2、针对于经常作为查询条件where、排序order by、分组 group by操作的字段建立索引。

3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度也高，使用索引的效率就 越高。

4、如果是字符串类型的字段，字段的长度较长，可以针对字段的特点建立前缀索引。

5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存 储空间避免回表查询，提高查询效率。

6、要控制索引的数量，索引并不是越多越好，索引过多维护索引的结构的代价会越来越 大，会影响增删改的效率。

7、如果索引列不能存储NULL值,请在创建索引时用not null约束它，当优化器知道每列是否 包含null时，它可以更好的确定哪个索引最有效地用于查询。