【实操记录】mysql性能优化

最新推荐文章于 2024-06-11 18:40:56 发布
最新推荐文章于 2024-06-11 18:40:56 发布
阅读量185 收藏
点赞数
文章标签： mysql 性能优化 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38360664/article/details/132120960
版权

【实操记录】mysql性能优化

1.mysql整个查询流程

1.与数据库服务器建立连接
2.查询缓存，有缓存直接返回，没有进入下一步。注意：只有在MySQL 8.0之前版本才有查询缓存，8.0之后查询缓存被优化掉了。
3.词法语法分析器
4.优化器，根据查询效率选择最佳执行方案
5.执行器,调用执行引擎
6.返回结果，并缓存查询结果
在这里插入图片描述

2.慢日志分析工具

mysqldumpslow：查看记录查询时间超过阈值(默认为10s)的语句。

2.1 mysqldumpslow

2.1.1 查看mysqldumpslow

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)

说明：
slow_query_log：慢日志开关
slow_query_log_file：慢日志文件存放路径

2.1.2 开启mysqldumpslow

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

or

mysql> set global slow_query_log='on';
Query OK, 0 rows affected (0.00 sec)

2.1.3 关闭mysqldumpslow

mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.00 sec)

or

mysql> set global slow_query_log='off';
Query OK, 0 rows affected (0.00 sec)

2.1.4 查询慢查询定义的时间

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

2.1.5 设置慢查询定义的时间

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

注意
设置完后，需要重新开启一个客户端，才可以看到配置的改变

2.1.6 以上设置是临时配置，以下是永久配置文件

[root@localhost mysql]# vi /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
# 慢查询配置
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time=1

说明
[mysqld] 这个一定要加，不然无效

2.1.7 慢日志查询

[root@localhost mysql]# mysqldumpslow -s at -t 20 /var/lib/mysql/localhost-slow.log
Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
Count: 9  Time=0.30s (2s)  Lock=0.00s (0s)  Rows=143725.8 (1293532), remote[remote]@[192.168.40.2]
  SELECT * from employees_info

说明
-s：按照那种⽅式排序，取值：
 c：访问计数
 l：锁定时间
 r：返回记录
 al：平均锁定时间
 ar：平均访问记录数
 at：平均查询时间
-t：是top n 的意思，返回多少条数据。
-g：可以跟上正则匹配模式，⼤⼩写不敏感。

2.2 pt-query-digest

2.2.1 下载

wget http://www.percona.com/get/pt-query-digest
chmod +x pt-query-digest

2.2.2 使用

[root@localhost Utils]# ./pt-query-digest /var/lib/mysql/localhost-slow.log
# A software update is available:

# 50ms user time, 230ms system time, 40.50M rss, 119.50M vsz
# Current date: Sat Aug  5 20:54:42 2023
# Hostname: localhost.localdomain
# Files: /var/lib/mysql/localhost-slow.log
# Overall: 442 total, 46 unique, 0.03 QPS, 0.07x concurrency _____________
# Time range: 2023-08-05T08:24:58 to 2023-08-05T12:28:07
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           982s     7us    869s      2s     5ms     40s   657us
# Lock time           74ms       0     3ms   168us   541us   256us   103us
# Rows sent          5.10M       0 976.56k  11.80k  463.90 101.80k    0.99
# Rows examine       5.11M       0 976.56k  11.84k  463.90 101.80k    5.75
# Query size        23.73k      11     983   54.97  202.40   84.30   28.75

# Profile
# Rank Query ID                            Response time  Calls R/Call   V
# ==== =================================== ============== ===== ======== =
#    1 0xAA783D4AE6C83F25F8E2F45C8CEE3EED  971.2776 98.9%     4 242.8194 50... CALL `batch_insert_data`
#    2 0x820E5CCD2963A2688B2CB66A8B559743   10.6056  1.1%    13   0.8158  1.09 SELECT employees_info
# MISC 0xMISC                                0.4248  0.0%   425   0.0010   0.0 <44 ITEMS>

# Query 1: 0.00 QPS, 0.33x concurrency, ID 0xAA783D4AE6C83F25F8E2F45C8CEE3EED at byte 108442
# This item is included in the report because it matches --limit.
# Scores: V/M = 500.16
# Time range: 2023-08-05T08:39:18 to 2023-08-05T09:28:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       4
# Exec time     98    971s   373us    869s    243s    833s    348s    468s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0     132      33      33      33      33       0      33
# String:
# Databases    test
# Hosts        192.168.40.2
# Users        remote
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
CALL `batch_insert_data`(@number)\G

# Query 2: 0.00 QPS, 0.00x concurrency, ID 0x820E5CCD2963A2688B2CB66A8B559743 at byte 109308
# This item is included in the report because it matches --outliers.
# Scores: V/M = 1.09
# Time range: 2023-08-05T09:12:12 to 2023-08-05T12:28:07
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      13
# Exec time      1     11s    10ms      2s   816ms      2s   941ms   160ms
# Lock time      2     2ms    78us   312us   131us   236us    64us   110us
# Rows sent     99   5.05M  11.66k 976.56k 397.65k 961.27k 451.34k  97.04k
# Rows examine  98   5.05M  11.66k 976.56k 397.65k 961.27k 451.34k  97.04k
# Query size     1     364      28      28      28      28       0      28
# String:
# Databases    test
# Hosts        192.168.40.2
# Users        remote
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #####################
#    1s  #####################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 'employees_info'\G
#    SHOW CREATE TABLE `test`.`employees_info`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * from employees_info\G

2.2.3 语法与选项

pt-query-digest [OPTIONS] [FILES] [DSN]

说明
–create-review-table 当使用–review参数把分析结果输出到表中时，如果没有表就自动创建。
–create-history-table 当使用–history参数把分析结果输出到表中时，如果没有表就自动创建。
–filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
–limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
–host mysql服务器地址
–user mysql用户名
–password mysql用户密码
–history 将分析结果保存到表中，分析结果比较详细，下次再使用–history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
–review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用–review时，如果存在相同的语句分析，就不会记录到数据表中。
–output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
–since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
–until 截止时间，配合—since可以分析一段时间内的慢查询。

2.2.4 用法示例

1、直接分析慢查询文件:

pt-query-digest /var/lib/mysql/localhost-slow.log > slow_report.log

2、分析最近12小时内的查询：

pt-query-digest --since=12h /var/lib/mysql/localhost-slow.log > slow_report.log

3、分析指定时间范围内的查询：

pt-query-digest --since '2023-08-05 00:00:00' --until '2023-08-06 10:00:00'  /var/lib/mysql/localhost-slow.log > slow_report.log

4、分析只含有select语句的慢查询

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i'  /var/lib/mysql/localhost-slow.log > slow_report.log

5、针对某个用户的慢查询

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i'  /var/lib/mysql/localhost-slow.log > slow_report.log

6、查询所有所有的全表扫描或full join的慢查询

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/localhost-slow.log > slow_report.log

7、把查询保存到test数据库的query_review表,如果没有的话会自动创建;

pt-query-digest --user=root –password=*** --review  h=localhost,D=test,t=query_review --create-review-table /var/lib/mysql/localhost-slow.log > slow_report.log

8、把查询保存到query_history表

pt-query-digest ---user=root –password=*** --review  h=localhost,D=test,t=query_history /var/lib/mysql/localhost-slow.log > slow_report.log

9、通过tcpdump抓取mysql的tcp协议数据，然后再分析

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

10、分析binlog

# 原生方法
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
# pt-query-digest
pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log

11、分析general log

pt-query-digest  --type=genlog  localhost.log > slow_report11.log

3.快速插入批量数据

3.1创建存储过程

#定义分割符号，mysql 默认分割符为分号;  这里定义为 //
#分隔符的作用主要是告诉mysql遇到下一个 // 符号即执行上面这一整段sql语句
DELIMITER //

#创建一个存储过程，并命名为 batch_insert_data
CREATE PROCEDURE batch_insert_data(IN number INT)

#下面这段就是表示循环往表里插入10w条数据
BEGIN
  DECLARE i int;
  SET i=1;
  WHILE(i <= number)DO
    INSERT INTO employees_info (employee_no, name, sex, age, position, birthday, address, telphone,
cred_type, cerd_no, hire_time, create_time, update_time)
VALUES (i, 'LiLei', 'M', 32, 'manager', '1990-09-09', '上海市浦东新区⼴兰路1990弄09号', '13119900909',
'02', '310115199009098715', '2016-06-06', now(), now());
    SET i=i+1;
  END WHILE;
END //  #这里遇到//符号，即执行上面一整段sql语句
#恢复mysql分隔符为;

3.2 调用存储过程

mysql> call batch_insert_data(1000000);

4.explain

4.1 explain 定义

模拟优化器执⾏SQL 语句，分析查询语句或结构的性能瓶颈。
在select语句之前增加explain关键字,执⾏查询会返回执⾏计划的信
息，⽽不是执⾏这条SQL。

4.1.1 explain extended(MySQL5.7 及以前版本)

相⽐explain会多⼀个filtered列字段：是⼀个半分⽐的值，rows * filtered/100可以估算出将要和explain中前⼀个表进⾏连接的⾏数

注意：explain extended语法在MySQL 8.0版本不再⽀持，只需要执⾏explain+SQL 语句即可。

4.1.2 explain partitions(MySQL5.7 及以前版本)

相⽐explain会多⼀个partitions 字段：⽤于场景：基于分区表sql 分析，会显⽰查询将访问的分区。

注意：explain partitions语法在MySQL 8.0版本不再⽀持，只需要执⾏explain+SQL 语句即可。

4.1.3 show warnings

在explain语句后⾯紧跟show warnings 命令，可以得到优化后的查询语句，从⽽看出优化器优化了什么。

mysql> explain SELECT * from employees_info;
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 901674 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`employees_info`.`employee_no` AS `employee_no`,`test`.`employees_info`.`name` AS `name`,`test`.`employees_info`.`sex` AS `sex`,`test`.`employees_info`.`age` AS `age`,`test`.`employees_info`.`position` AS `position`,`test`.`employees_info`.`birthday` AS `birthday`,`test`.`employees_info`.`address` AS `address`,`test`.`employees_info`.`telphone` AS `telphone`,`test`.`employees_info`.`cred_type` AS `cred_type`,`test`.`employees_info`.`cerd_no` AS `cerd_no`,`test`.`employees_info`.`hire_time` AS `hire_time`,`test`.`employees_info`.`create_time` AS `create_time`,`test`.`employees_info`.`update_time` AS `update_time` from `test`.`employees_info` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.2 explain 字段解释

mysql> explain SELECT * from employees_info t where t.employee_no=9999; 
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4.2.1 id

表示执行顺序
数字越大越先执行
数字相同，按从上到下执行
null，最后执行

4.2.2 select_type

SIMPLE 、PRIMARY、DERIVED、SUBQUERY、UNION

mysql> explain SELECT * from employees_info t where t.employee_no=9999; 
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

说明：
SIMPLE：简单查询。查询不包含⼦查询和union

mysql> set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> #set session optimizer_switch='derived_merge=on';
mysql> explain select (select 1 from cust_info where cust_no = 1) from (select * from goods_info where goods_no =
    -> 1) der;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | goods_info | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | cust_info  | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

说明
set session optimizer_switch=‘derived_merge=off’; 关闭mysql5.7/8.0新特性对衍⽣表的合并优化
PRIMARY：复杂查询中最外层的select
SUBQUERY：包含在select 中的⼦查询(不在from⼦句中)
DERIVED：：包含在from ⼦句中的⼦查询。MySQL会将结果存放在⼀个临时表中，也称为派⽣表(derived
的英⽂含义)

mysql> explain select 1 union all select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
|  2 | UNION       | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

说明
UNION ：在union中的第⼆个和随后的select

4.2.3 table

table字段表⽰的是explain分析的当前SQL 正在访问的是哪个表。
当from⼦句中有⼦查询时，table字段是格式，表⽰当前查询依赖id=N的查询结果，于是先执
⾏id=N的查询。
当有union时，UNION RESULT 的table 字段的值为<union1, 2>，1和2表⽰参与union的select⾏id。

mysql> set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> #set session optimizer_switch='derived_merge=on';
mysql> explain select (select 1 from cust_info where cust_no = 1) from (select * from goods_info where goods_no =
    -> 1) der;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | goods_info | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | cust_info  | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * from employees_info where employee_no between 10 and 1000 union SELECT * from employees_info where employee_no between 10000 and 1000000;
+----+--------------+----------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
| id | select_type  | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra           |
+----+--------------+----------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
|  1 | PRIMARY      | employees_info | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    991 |   100.00 | Using where     |
|  2 | UNION        | employees_info | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 450837 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2>     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   NULL |     NULL | Using temporary |
+----+--------------+----------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

4.2.4 partitions

如果查询是基于分区表的话，partitions 字段会显⽰查询将访问的分区。

4.2.5 type(最重要)

从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
⼀般来说，得保证查询达到range级别，最好达到ref
const:使用了主键或者唯一索引全部字段，获得最多一条数据
system:和const基本相同，衍生表使用了主键或者唯一索引全部字段，获得最多一条数据

mysql> set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> explain select * from (select * from goods_info where goods_no = 1) tmp;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | goods_info | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

eq_ref:关联表使用了主键或者唯一索引的全部字段


mysql> explain select * from order_info left join goods_info on order_info. goods_no = goods_info. goods_no;
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                      | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
|  1 | SIMPLE      | order_info | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |    3 |   100.00 | NULL  |
|  1 | SIMPLE      | goods_info | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.order_info.goods_no |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref:使用了非主键索引或者唯一索引的部分字段
range:范围查询
index:全索引查询
all:全表查询

小技巧
表查询顺序：衍生表（跟在from后）–>子查询（跟在select后）–>主表（最外层表，包括关联表）
依据表看字段：
如果字段是主键或者唯一索引全字段的等值比较，const
如果字段是主键或者唯一索引全字段的等值比较，在衍生表中，数据最多获取一条，最外层表，system
在关联查询中，如果字段是主键或者唯一索引全字段的等值比较，关联表，eq_ref
如果字段是非主键或者唯一索引字段的部分，ref
如果字段是在一个范围中，range
如果字段使用索引，但是没有条件限制，index
如果字段没使用索引，all

4.2.6 possible_keys

表⽰可能使⽤哪些索引来查找，但实际可能不⽤。
explain时可能出现possible_keys 有列，⽽key显⽰NULL的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不⼤，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where⼦句看是否可以创造⼀个适当的索引来提⾼查询性能，然后⽤explain查看效果。

4.2.7 key

实际使用的索引。如果为 NULL，则没有使用索引。
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 forceindex、ignore index。

4.2.8 key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。

key_len计算规则如下：
字符串：char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，⽽不是字节数，如果是utf-8，⼀个数字或字⺟占1个字节，⼀个汉字占3个字节
  char(n)：如果存汉字⻓度就是3n字节
  varchar(n)：如果存汉字则⻓度是3n + 2字节，加的2字节⽤来存储字符串⻓度，因为varchar 是变⻓字符串
数值类型
  tinyint：1字节
  smallint：2字节
  int：4字节
  bigint：8字节
时间类型
  date：3字节
  timestamp：4字节
  datetime：8字节
如果字段允许为NULL，需要1字节记录是否为NULL
注意：索引最⼤⻓度是768字节，当字符串过⻓时，mysql会做⼀个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

4.2.9 ref

显示索引的哪一列被使用了，如果可能的话，是一个常量 const。

4.2.10 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。注意这个不是结果集里的行数。

4.2.11 filtered

显示了通过条件过滤出的行数的百分比估计值。

4.2.12 Extra

Using filesort:MySQL 需要额外的一次传递,以找出如何按排序顺序检索行。将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。(使用覆盖索引)
Using temporary:为了解决查询,MySQL 需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户，并且查询的列未被索引覆盖
Impossible WHERE noticed after reading const tables:除了主键或者唯一索引全部字段，还有一个字段，导致查询不到数据，就会出现这个

5.优化技巧

最左前缀法则
尽量不要在索引列上做任何操作(计算、函数、(⾃动or ⼿动)类型转换)，会导致索引失效⽽转向全表扫描
用日期函数转换的可以用范围查询
联合索引中尽量不要有范围查询
尽量使⽤覆盖索引(只访问索引的查询(索引列包含查询列))，减少select *语句
尽量不要使用不等于(!=或者<>)、not in、not exists，is null、is not null，可能会是索引失效
like 尽量不要将% 放前面，可能会使索引失效，解决办法覆盖索引
尽量不要用到数据类型转换，无论是自动的还是手动的，索引失效
少⽤or 或in，可能索引失效
范围查询没走索引的话，可以将数据拆开

6 参考资料

详解 慢查询 之 mysqldumpslow
MySQL—— MySQL的执行计划详解（Explain）

过尽人间三千客
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL优化面试
许诗宇的博客
04-16 1694
注意：本文引用 https://zhenganwen.top/posts/433a3305/ 概要 一、概述 为什么要优化 系统的吞吐量瓶颈往往出现在数据库的访问速度上 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢 数据是存放在磁盘上的，读写速度无法和内存相比 如何优化 设计数据库时：数据库表、字段的设计，存储引擎 利用好MySQL自身提...
MySQL性能优化
01-31
此外，除了数据库索引之外，在LAMP结果如此流行的今天，数据库（尤其是MySQL）性能优化也是海量数据处理的一个热点。下面就结合自己的经验，聊一聊MySQL数据库优化的几个方面。 首先，在数据库设计的时候，要能够...
mysql慢查询日志查看rowexam_【原创】关于MySQL的慢日志分析工具
weixin_42365251的博客
02-01 228
今天我们看看关于MySQL慢日志的阅读。我们知道，如果我们的语句不够优化，那么首先MySQL的慢日志是进一步优化的离线证据，虽然里面有好多“伪慢语句”！先不说怎么优化，如果你的日志有一条语句赌住了，那么会有不计其数的慢语句填充到MySQL的满日志里面。那么首先提炼出这些语句就非常头疼。今天主要介绍两种工具：1,mysqldumpslow。(咱们MySQL自带的简单而又实用的工具)我们先来看下mys...
mysql性能优化_原理_课程大纲
细致-专业-实操
03-05 547
1、MySQL在金融互联网行业的企业级安装部署2、mysql启动关闭原理和实战_及常见错误排查3、MySQL字符集和校对规则4、史上最详细的Mysql用户权原理和实战_生产案例5、InnoDB引擎原理和实战_通俗易懂6、MySQL文件详解_物理结构_逻辑结构_原理和案例7、SQL编程开发与优化事项8、MySQL索引原理和案例9、information_schema和sys中性能查看10、MySQL优化器算法与执行计划11、MySQL查询优化12、事务原理和实战13、锁的原理和应用14、慢查询原理和实战_快速
mysql 优化思路_MySQL 性能优化（思路拓展及实操）
weixin_28856341的博客
02-07 100
# 优化思路优化 MySQL 数据库是数据库管理员或数据库开发人员的必备技能。MySQL优化一方面需要找出系统的瓶颈，提高 MySQL 数据库整体的性能；另一方面，需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能的节省系统资源，以便系统可以提供更大负荷的服务，本节视频将演示优化的基本操作和思路拓展。# 优化措施MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占...
MySQL 索引优化实战
mrathena
03-21 305
博文目录 文章目录常规示例非常规示例索引下推（Index Condition Pushdown，ICP）MySQL针对要执行的SQL计算全表扫描/走某索引的成本cost常见SQL深入优化 示例数据 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '
mysql实操_MySQL实操笔记（1）
weixin_31953847的博客
01-19 101
问题1：MySQL索引类型有哪些区别是什么？MySQL索引类型：normal：表示普通索引、unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复，可以设置为unique。full text：表示全文搜索的索引，用于搜索很长一篇文章的时候，效果特别好，如果是比较短的文本，比如一两行字的普通的normal即可。索引类型有建立索引的字段内容特性来决定，通常normal最常见。问题2：实...
mysql 8.0 主从同步性能优化
emon_lee的专栏
04-19 1383
　　关于mysql 8.0的主从同步，网上有提到一些革命性的改变，但是具体怎么配置，没有简单明了的，净是官网文章的翻译，不具实操性。之前有搭建了mysql一主一从集群，主从同步一直效率不高，看网上说基于写集合的并行复制，关键词是writeset，不同事务修改了不同行的数据，那么可以视为同一组。MySQL 会对这个提交的事务中的一行记录做一个 HASH值，这些 HASH 值称为 writeset。w...
超级全面的MySQL优化面试解析
Java笔记虾
09-02 9713
作者：Anwen juejin.im/post/5c6b9c09f265da2d8a55a855 推荐阅读(点击即可跳转阅读) SpringBoot内容聚合 面试题内容聚合 设计模式内容聚合 Mybatis内容聚合 多线程内容聚合 本文概要 概述 为什么要优化 系统的吞吐量瓶颈往往出现在数据库的访问速度上 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相...
MySQL性能优化详解.docx
03-26
MySQL语句优化是提高数据库性能的关键步骤，它包括调整参数配置、优化SQL语句和分析执行计划等。以下是一些常见的优化策略： 使用EXPLAIN分析查询：通过在查询语句前加上EXPLAIN关键字，可以了解MySQL是如何处理该...
MySQL性能优化方向
12-14
按照从大到小，从主要到次要的形式，分析 mysql 性能优化点，达到终优化的效果。 　利用 mindmanger 整理了思路，形成如下图，每个点在网上都能找到说明，并记录下。形成了优化的思路： 　1 连接 Connections ...
mysql性能优化.pptx
05-21
mysql性能优化
Mysql性能优化方案分享
09-10
主要为大家分享了Mysql性能优化方案，帮助大家进行Mysql性能优化，感兴趣的小伙伴们可以参考一下
MySQL 高级 - 第十一章 | 索引优化与查询优化
xhmico的博客
06-07 1358
上篇：第十章、性能分析工具的使用本文内容主要源于：bilibili-尚硅谷-MySQL高级篇都有哪些纬度可以进行数据库调优？简言之：关于数据库调优的知识点非常分散，不同 ，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。虽然 查询优化的技术很多，但是大体方向上完全可以分为 和 两大块。学员表插 万条， 班级表插 万条步骤1：建表 步骤2：设置参数命令开启：允许创建函数设置 步骤3：创建函数随机产生字符串，保证每条数据都不同 随机产生班级编号 步骤4：创建存储过程创建往 表中插入数据的存储
【MySQL】服务器配置和管理
p_fly的博客
06-09 749
MySQL服务器通常说的是mysqld程序。mysqld 是 MySQL 数据库服务器的核心程序，负责处理客户端的请求、管理数据库和执行数据库操作。管理员可以通过配置文件和各种工具来管理和监控 mysqld 服务器的运行本文将介绍下面三个内容：服务器启动选项：可以在命令行和配置文件中指定，用于配置服务器的行为和特性。服务器系统变量：反映了启动选项的当前状态和值，其中一些变量可以在服务器运行时修改。服务器状态变量：包含了关于运行时操作的计数器和统计信息。
Mysql--基础知识点--86--慢查询
最新发布
Chasing__Dreams的博客
06-11 755
mysql 慢查询
Mysql的 存储过程（procedure）
shark1978的专栏
06-09 733
存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名字和参数（如果有）来执行它。存储过程可以在很多数据库管理系统（DBMS）中找到，如SQL Server、Oracle、MySQL等。
mysql 性能优化
07-28
MySQL性能优化是提升数据库查询和操作效率的关键步骤。以下是一些常见的MySQL性能优化技巧： 1. 使用合适的索引：在经常用于查询的列上创建索引，可以大大提高查询性能。避免创建过多的索引，因为过多的索引可能会导致性能下降。 2. 优化查询语句：编写高效的查询语句是提升性能的关键。避免全表扫描，尽量使用索引来加速查询。使用EXPLAIN命令来分析查询计划，检查是否存在慢查询。 3. 适当分区：对于大型表，可以根据数据的特点将其分成多个分区，以提高查询和维护性能。 4. 调整缓冲区大小：适当调整MySQL的缓冲区大小，如innodb_buffer_pool_size和key_buffer_size，以提高读写性能。 5. 合理配置服务器参数：调整MySQL服务器的参数，如max_connections、innodb_buffer_pool_instances等，以适应实际需求并提高性能。 6. 避免过度规范化：过度规范化可能导致频繁的表连接操作，降低性能。根据实际情况进行适当的冗余设计，以减少表连接操作。 7. 使用批量插入和更新：使用批量插入和更新语句，如INSERT INTO ... VALUES(...), (...), ...，可以减少客户端和服务器之间的通信次数，提高性能。 8. 定期维护和优化表：定期进行表优化、碎片整理、统计信息更新等数据库维护操作，以提高性能。 这些是一些常见的MySQL性能优化技巧，根据具体场景和需求，还可以进行其他优化措施。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值