做的网站怎样打开速度快,广西和城乡建设厅网站,美客多电商平台入驻链接,seo精灵大纲
1.Explain概述
2.Explain详解
3.索引优化数据准备
4.索引优化原则详解
5.慢查询设置与测试
6.慢查询SQL优化思路 1.Explain概述
使用Explain关键字可以模拟查询优化器来执行SQL查询语句#xff0c;从而知道MySQL是如何处理SQL语句的#xff0c;从而分析出查询语句…大纲
1.Explain概述
2.Explain详解
3.索引优化数据准备
4.索引优化原则详解
5.慢查询设置与测试
6.慢查询SQL优化思路 1.Explain概述
使用Explain关键字可以模拟查询优化器来执行SQL查询语句从而知道MySQL是如何处理SQL语句的从而分析出查询语句和表结构的性能瓶颈。 MySQL查询过程 通过Explain可以获得以下信息
一.表的读取顺序
二.数据读取操作的操作类型
三.哪些索引可以被使用
四.哪些索引真正被使用
五.表的直接引用
六.每张表的有多少行被优化器查询了 Explain使用方式Explain SQL语句通过执行Explain可以获得SQL语句执行的相关信息。
EXPLAIN SELECT * FROM L1; 2.Explain详解
(1)数据准备
(2)ID字段说明
(3)select_type和table字段说明
(4)type字段说明
(5)possible_keys与key说明
(6)key_len字段说明
(7)ref字段说明
(8)rows字段说明
(9)filtered字段说明
(10)extra字段说明 (1)数据准备
-- 创建数据库CREATE DATABASE test CHARACTER SET utf8;
-- 创建表CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
-- 每张表插入3条数据INSERT INTO L1(title) VALUES(test001),(test002),(test003);INSERT INTO L2(title) VALUES(test004),(test005),(test006);INSERT INTO L3(title) VALUES(test007),(test008),(test009);INSERT INTO L4(title) VALUES(test010),(test011),(test012);
(2)ID字段说明
ID字段代表SELECT查询的序列号它是一组数字表示的是查询或操作表的顺序。 一.ID相同执行顺序就是由上至下
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.idL2.id AND L2.id L3.id; 二.ID不同如果有子查询ID号会递增
ID值越大优先级越高越先被执行。
EXPLAIN SELECT * FROM L2 WHERE id (SELECT id FROM L1 WHERE id (SELECT L3.id FROM L3 WHERE L3.title test007)
); (3)select_type和table字段说明
select_type表示查询类型主要用于区别普通查询还是子查询等table表示被操作的表。 一.SIMPLE简单的SELECT查询查询中不包含子查询或者UNION
EXPLAIN SELECT * FROM L1 where id 1; 二.PRIMARY在有子查询的情况下最外层被标记为PRIMARY 三.SUBQUERY在SELECT或WHERE列表中包含了子查询
EXPLAIN SELECT * FROM L2 WHERE id (SELECT id FROM L1 WHERE id (SELECT L3.id FROM L3 WHERE L3.title test08)
); 四.UNIONUNION连接的两个SELECT查询
在使用UNION时左边的表的select_type是DERIVED右边的表的select_type是UNION。 五.DERIVED在FROM列表中包含的子查询被标记为DERIVED派生表
MySQL会递归执行这些被标记为DERIVED的子查询然后把结果放到临时表中。 六.UNION RESULTUNION的结果
EXPLAIN SELECT * FROM (SELECT * FROM L3 UNION SELECT * FROM L4) a; (4)type字段说明
type字段表示的是连接类型描述了找到所需数据而使用的扫描方式。 下面给出各种连接类型按照从最好类型到最差类型进行排序system - const - eq_ref - ref - fulltext - ref_or_null - index_merge - unique_subquery - index_subquery - range - index - ALL 简化后可以只关注以下几种 system - const - eq_ref - ref - range - index - ALL 一般来说需要保证查询至少达到range级别最好能达到ref级别否则就要就行SQL的优化调整。 下面介绍type字段不同值表示的含义 一.system
表示表中仅有一行数据这是const连接类型的一个特例很少出现。 二.const
表示命中主键索引(primary key)或唯一索引(unique)通过主键索引或唯一索引一次就找到了数据。因为只匹配一条记录所以被连接的部分是一个常量。如果将主键放在where条件中MySQL就能将该查询转换为一个常量。这种类型非常快例如以下查询
EXPLAIN SELECT * FROM L1 WHERE id 3; -- 为L1表的title字段添加唯一索引ALTER TABLE L1 ADD UNIQUE(title);EXPLAIN SELECT * FROM L1 WHERE title test001; 三.eq_ref
表示的是使用了唯一索引。比如连表查询中对于前一个表中的每一行后表只有一行被扫描。除了system和const类型之外这是最好的连接类型。只有在连表时使用的索引都是主键或唯一索引时才会出现这种类型例如以下查询
EXPLAIN SELECT L1.id,L1.title FROM L1 LEFT JOIN L2 ON L1.id L2.id; 四.ref
表示使用了普通索引即非唯一性索引。比如连表时对于前表的每一行后表可能有多于一行的数据被扫描例如以下查询
-- 为L1表的title字段添加普通索引ALTER TABLE L1 ADD INDEX idx_title (title);EXPLAIN SELECT * FROM L1 INNER JOIN L2 ON L1.title L2.title;-- 如果L1表的title字段没有唯一索引只有普通索引如下查询也是refEXPLAIN SELECT * FROM L1 WHERE title test001; 五.range
表示的是进行了索引上的范围查询检索了给定范围的行比如between、in函数、都是典型的范围查询例如以下查询
EXPLAIN SELECT * FROM L1 WHERE L1.id BETWEEN 1 AND 10; 注意当in函数中的数据很大时可能会导致效率下降最终不走索引。 六.index
当可以使用索引覆盖但需要扫描全部索引记录时则type为index。 当需要执行全表扫描且需要对主键进行排序时则type也为index。 所以如果type的值等于index那么就需要进行优化了。因为出现index表示没有通过索引进行过滤需要扫描索引的全部数据。index会遍历扫描索引树比ALL快一些。如果索引文件过大index的速度还是会很慢的。 总结
当遍历二级索引不需要回表或者主键排序全表扫描时type就为index。 注意
使用索引进行排序分组时可能会出现这种type值为index的情况。比如进行统计操作时会出现type值为index的情况。
EXPLAIN SELECT * FROM L2 GROUP BY id ORDER BY id;-- 该count查询需要通过扫描索引上的全部数据来计数EXPLAIN SELECT count(*) FROM L2; 七.ALL
表示没有使用到任何索引连表查询时对于前表的每一行后表都要被全表扫描。
EXPLAIN SELECT * FROM L3 inner join L4 on L3.title L4.title; 总结各类type类型的特点
system不进行磁盘IO查询系统表仅仅返回一条数据。 const查找主键索引最多返回1条或0条数据属于精确查找。 eq_ref查找唯一性索引返回数据最多一条属于精确查找。 ref查找非唯一性索引返回匹配的多条数据属于精确查找。 range查找索引中给定范围的行属于范围查找(、、in、between)。 index使用了索引但扫描全部了比all快因索引文件比数据文件小。 index比如遍历二级索引不需要回表或者主键排序全表扫描。 all不使用任何索引直接进行全表扫描。 (5)possible_keys与key说明
一.possible_keys
表示可能用于查询的表上的索引。查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。 二.key
表示实际使用的索引。若为null则表示没有使用到索引或索引失效。查询中若使用了覆盖索引则该索引仅出现在key列表中。 情形一理论和实际都用到了索引
EXPLAIN SELECT * FROM L1 WHERE id 1; 情形二理论上没有使用索引但实际上使用了
EXPLAIN SELECT L3.id FROM L3; 情形三理论和实际上都没有使用索引
EXPLAIN SELECT * FROM L3 WHERE title test007; (6)key_len字段说明
表示索引中使用的字节数通过该列可以计算查询中使用索引的长度。key_len字段能够帮我们检查是否充分利用了索引ken_len越长越好说明索引使用的越充分。 一.创建表
CREATE TABLE L5( a INT PRIMARY KEY, b INT NOT NULL, c INT DEFAULT NULL, d CHAR(10) NOT NULL);
二.使用EXPLAIN进行测试
-- 下面的查询只用到了主键a的索引EXPLAIN SELECT * FROM L5 WHERE a 1 AND b 1;
观察key_len的值用到了主键索引是int类型的所以key_len是4字节。 三.为b字段添加索引进行测试
ALTER TABLE L5 ADD INDEX idx_b(b);-- 执行SQL这次将b字段也作为条件EXPLAIN SELECT * FROM L5 WHERE a 1 AND b 1;
ken_len还是4。 四.为c、d字段添加联合索引然后进行测试
ALTER TABLE L5 ADD INDEX idx_c_b(c,d);EXPLAIN SELECT * FROM L5 WHERE c 1 AND d A; c字段是int类型4个字节d字段char(10)代表的是10个字符30个字节。因为数据库的字符集是utf8一个字符3个字节。d字段是char(10)代表的是10个字符相当30个字节。多出的一个字节用来表示是联合索引。 下面这个例子虽然使用了联合索引但没充分利用索引还有优化空间。因为可以根据ken_len的长度推测出该联合索引只使用一部分。
EXPLAIN SELECT * FROM L5 WHERE c 1; (7)ref字段说明
表示的是显示索引的哪一列被使用了如果可能的话最好是一个常数。表示的是哪些列或常量被用于查找索引列上的值。 如下的L1.id1中由于1是常量所以ref const此时的ref const表示着查询过程中使用到了常量。
EXPLAIN SELECT * FROM L1 WHERE L1.id 1; (8)rows字段说明
表示MySQL为了找到所需的记录一共访问了多少行(预估的)。L3中的title没有添加索引所以L3中有3条记录就需要访问3条记录。
EXPLAIN SELECT * FROM L3,L4 WHERE L3.id L4.id AND L3.title LIKE test007; 需要注意的是rows只是一个估算值并不准确。所以rows行数过大的问题并不值得过多考虑主要分析的还是索引是否使用正确了。 (9)filtered字段说明
它指返回结果的行占需要读到的行(rows列的值)的百分比。 (10)extra字段说明
Extra是EXPLAIN输出中另外一个很重要的列该列显示MySQL在查询过程中的一些详细信息。 一.准备数据
CREATE TABLE users ( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(20), age INT(11));INSERT INTO users VALUES(NULL, lisa, 10);INSERT INTO users VALUES(NULL, lisa, 10);INSERT INTO users VALUES(NULL, rose, 11);INSERT INTO users VALUES(NULL, jack, 12);INSERT INTO users VALUES(NULL, sam, 13);
二.Using filesort(需要进行文件排序)
执行结果Extra为Using filesort说明得到所需结果集需要对所有记录进行文件排序。表示执行的SQL语句性能极差需要进行优化。 下面就是在一个没有建立索引的列上进行order by此时会触发filesort。优化方案是在order by的列上添加索引避免每次查询都全量排序。
EXPLAIN SELECT * FROM users ORDER BY age; 三.Using temporary
表示使用了临时表来存储结果集常见于排序和分组查询。
EXPLAIN SELECT COUNT(*),uname FROM users GROUP BY uname; 四.Using where
表示使用了全表扫描或者在查找时使用索引的情况下还有查询条件不在索引字段中需要回表。 注意一返回所有记录的SQL不使用where条件过滤数据大概率不符合预期这类SQL往往需要进行优化。 注意二使用了where条件的SQL并不代表不需要优化往往需要配合explain结果中的type(连接类型)来综合判断。例如下面查询的age未设置索引所以返回的type为ALL仍有优化空间可建立索引优化查询。
EXPLAIN SELECT * FROM users WHERE age 10; 五.Using index
表示直接访问索引就能获取所需数据(覆盖索引)不需要回表。
-- 为uname创建索引ALTER TABLE users ADD INDEX idx_uname(uname);EXPLAIN SELECT uid,uname FROM users WHERE unamelisa; 六.Using join buffer
表示使用了连接缓存还会显示join连接查询时使用的算法。
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE age 1) u2 ON u1.age u2.age; Using join buffer(Block Nested Loop)说明需要进行嵌套循环计算。这里每个表都有五条记录内外表查询的type都为ALL。两个表通过字段age进行关联且age字段未建立索引。 七.Using index condition
表示的是使用了索引但是只使用了索引的一部分。一般发生在使用联合索引时需要回表查询。
EXPLAIN SELECT * FROM L5 WHERE c 10 AND d ; 八.Extra主要指标的含义总结
using index查找时使用了覆盖索引的时候就会出现不需要回表。 using where查找时使用索引的情况下需要回表或全表扫描。 using index condition查找时使用了索引但只用一部分索引需要回表。 Using filesort在一个没有建立索引的列上order by发生文件排序。 Using temporary使用了临时表存储结果集常见于排序和分组查询。 当遍历二级索引不需要回表或者主键排序全表扫描时type就为index。 查找非唯一性索引返回匹配的多条数据type就为ref。 查找唯一性索引返回匹配的数据最多一条type就为eq_ref。 查找索引中给定范围的行type就为range。 3.索引优化数据准备
(1)创建数据库、表插入数据
create database idx_optimize character set utf8;CREATE TABLE users( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(20) NOT NULL COMMENT 姓名, user_age INT NOT NULL DEFAULT 0 COMMENT 年龄, user_level VARCHAR(20) NOT NULL COMMENT 用户等级, reg_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间);INSERT INTO users(user_name,user_age,user_level,reg_time)VALUES(tom,17,A,NOW()),(jack,18,B,NOW()),(lucy,18,C,NOW());
(2)创建联合索引
ALTER TABLE users ADD INDEX idx_nal (user_name,user_age,user_level) USING BTREE; 4.索引优化原则详解
(1)最左侧列匹配和最左前缀匹配法则
(2)不要在索引列上做任何计算
(3)范围之后全失效
(4)避免使用is null、is not null、! 、or
(5)like以%开头会使索引失效
(6)索引优化原则总结 (1)最左侧列匹配和最左前缀匹配法则
如果创建的是联合索引就要遵循该法则。where后面的条件需从索引的最左侧列开始且不能跳过索引中的列。如果where只匹配一个列那么该列在索引最左侧且只匹配前缀字段。 一.最左侧列匹配和最左前缀匹配的场景
场景1按照索引字段顺序使用三个字段都使用了索引没有问题。
EXPLAIN SELECT * FROM users WHERE user_name tom AND user_age 17 AND user_level A; 场景2直接跳过user_name使用索引字段索引无效未使用到索引。
EXPLAIN SELECT * FROM users WHERE user_age 17 AND user_level A; 场景3: 不按照创建联合索引的顺序使用索引。
EXPLAIN SELECT * FROM users WHERE user_age 17 AND user_name tom AND user_level A; where后面查询条件顺序是user_age、user_level、user_name这与创建的索引顺序user_name、user_age、user_level不一致。为什么还是使用了索引原因是MySQL底层优化器对其进行了优化。 场景4只要包含最左侧字段索引就可以生效
但从key_len可知只是用到索引的一部分。
EXPLAIN SELECT * FROM users WHERE user_name tom; 二.最左侧列匹配和最左前缀匹配的原理
InnoDB创建联合索引的规则是
首先会对联合索引最左边的字段进行排序例子中是user_name。在第一个字段的基础之上再对第二个字段进行排序例子中是user_age。所以最佳左前缀原则其实是和B树的结构有关系最左字段肯定是有序的第二个字段则是无序的。 联合索引的排序方式是
先按第一个字段进行排序如果第一个字段相等再根据第二个字段排序。所以如果直接使用第二个字段user_age通常是使用不到索引的。 (2)不要在索引列上做任何计算
不要在索引列上做任何操作否则会导致索引失效从而转向全表扫描。比如计算、使用函数、自动或手动进行类型转换(字符串不加双引号)。 一.插入数据
INSERT INTO users(user_name,user_age,user_level,reg_time) VALUES(11223344,22,D,NOW());
场景1使用系统函数left()函数对user_name进行操作
EXPLAIN SELECT * FROM users WHERE LEFT(user_name, 6) 112233; 场景2字符串不加单引号(隐式类型转换)
对于varchar类型的字段如果查询时不加单引号就会进行隐式转换导致索引失效转向全表扫描。
EXPLAIN SELECT * FROM users WHERE user_name 11223344; (3)范围之后全失效
where条件中如果有范围条件并且范围条件之后还有其他过滤条件那么范围条件之后的列就都将会索引失效。 场景1条件单独使用user_name时typeref、key_len62。
-- 条件只有一个 user_nameEXPLAIN SELECT * FROM users WHERE user_name tom; 场景2条件增加一个user_age(使用常量等值)type ref、key_len 66。
EXPLAIN SELECT * FROM users WHERE user_name tom AND user_age 17; 场景3使用全值匹配type ref、key_len 128索引都利用上了。
EXPLAIN SELECT * FROM users WHERE user_name tom AND user_age 17 AND user_level A; 场景4使用范围条件时avg 17、type range、key_len 66。与场景3比较可发现user_level索引没用上。
EXPLAIN SELECT * FROM users WHERE user_name tom AND user_age 17 AND user_level A; (4)避免使用is null、is not null、! 、or
一.使用is null会使索引失效
EXPLAIN SELECT * FROM users WHERE user_name IS NULL; Impossible Where表示where条件不成立不能返回任何行。 二.使用is not null会使索引失效
EXPLAIN SELECT * FROM users WHERE user_name IS NOT NULL; 三.使用!和or会使索引失效
EXPLAIN SELECT * FROM users WHERE user_name ! tom;EXPLAIN SELECT * FROM users WHERE user_name tom or user_name jack; (5)like以%开头会使索引失效
一.like查询中%出现在左边则索引失效%出现在右边索引未失效
场景1两边都有%或者%在左边索引都会失效
EXPLAIN SELECT * FROM users WHERE user_name LIKE %tom%;EXPLAIN SELECT * FROM users WHERE user_name LIKE %tom; 场景2%在右边索引生效
EXPLAIN SELECT * FROM users WHERE user_name LIKE tom%; 二.解决%出现在左边索引失效的方法——使用覆盖索引
EXPLAIN SELECT user_name FROM users WHERE user_name LIKE %jack%;EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE %jack%; 对比场景1可以知道通过使用覆盖索引type index并且extra Using index从原来的全表扫描变成了全索引扫描也就是索引的全表扫描。 三.like失效的原理
原理一%号在右
由于B树的索引顺序是按照首字母的大小进行排序而%号在右时的匹配又会匹配首字母所以能在B树上进行有序的查找。也就是查找出首字母符合要求的数据所以%号在右可以用到索引。 原理二%号在左是匹配字符串尾部的数据
由于尾部的字母是没有顺序的所以不能按索引顺序查询用不到索引。 原理三两个%%号
这个是查询任意位置的字母满足条件即可。只有首字母是进行索引排序的其他位置的字母都是相对无序的所以查找任意位置的字母是用不上索引的。 (6)索引优化原则总结
一.最左侧列匹配和最左前缀匹配
二.索引列上不计算不转换
三.范围之后全失效
四.最好使用覆盖索引
五.!、is null、is not null、or会索引失效
六.like百分号加右边加左边导致索引失效的解决方法是使用覆盖索引 5.慢查询设置与测试
(1)慢查询介绍
(2)慢查询参数
(3)慢查询配置方式
(4)慢查询测试
(5)慢日志内容 (1)慢查询介绍
MySQL的慢查询全名是慢查询日志是MySQL提供的一种日志记录。慢查询日志会记录在MySQL中响应时间超过阈值的语句。MySQL数据库默认不启动慢查询日志需要手动来设置这个参数。 如果不是调优需要的话一般不建议启动该参数。因为开启慢查询日志会或多或少带来一定的性能影响慢查询日志支持将日志记录写入文件和数据库表。 (2)慢查询参数
执行下面的语句
mysql show variables like %slow_query_log%;---------------------------------------------------| Variable_name | Value |---------------------------------------------------| slow_query_log | ON || slow_query_log_file | /var/lib/mysql/test-slow.log |---------------------------------------------------
mysql show variables like %long_query%;----------------------------| Variable_name | Value |----------------------------| long_query_time | 10.000000 |----------------------------
MySQL慢查询的相关参数解释
一.slow_query_log是否开启慢查询日志。
二.slow-query-log-file慢查询日志存储路径。
三.long_query_time慢查询阈值查询时间多于设定阈值则记录日志。 (3)慢查询配置方式
一.默认情况下slow_query_log的值为OFF表示慢查询日志是禁用的
mysql show variables like %slow_query_log%;---------------------------------------------------| Variable_name | Value |---------------------------------------------------| slow_query_log | ON || slow_query_log_file | /var/lib/mysql/test-slow.log |---------------------------------------------------
二.可以通过设置slow_query_log的值来开启
mysql set global slow_query_log1;
三.set global slow_query_log1开启慢查询日志当前生效重启失效
如果要永久生效就必须修改配置文件my.cnf其它系统变量也是如此。
-- 编辑配置vim /etc/my.cnf
-- 添加如下内容slow_query_log 1slow_query_log_file/var/lib/mysql/ruyuan-slow.log
-- 重启MySQLservice mysqld restart
mysql show variables like %slow_query%;-----------------------------------------------------| Variable_name | Value |-----------------------------------------------------| slow_query_log | ON || slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |-----------------------------------------------------
四. 开启了慢查询日志后什么样的SQL才会记录到慢查询日志里
这个由参数long_query_time控制默认long_query_time的值为10秒。
mysql show variables like long_query_time;----------------------------| Variable_name | Value |----------------------------| long_query_time | 10.000000 |----------------------------
mysql set global long_query_time1;Query OK, 0 rows affected (0.00 sec)
mysql show variables like long_query_time;----------------------------| Variable_name | Value |----------------------------| long_query_time | 10.000000 |----------------------------
五.修改变量long_query_time但查询值还是10
执行命令set global long_query_time1后需要重新连接或者打开新开会话才能看到修改值。
mysql show variables like long_query_time;---------------------------| Variable_name | Value |---------------------------| long_query_time | 1.000000 |---------------------------
六.log_output参数是指定日志的存储方式
log_outputFILE表示将日志存入文件默认值是FILE。log_outputTABLE表示将日志存入数据库这样日志信息就会被写入到mysql.slow_log表中。 MySQL数据库可以同时支持两种日志存储方式配置的时候以逗号隔开即可如log_outputFILE,TABLE。 日志记录到系统的专用日志表中要比记录到文件耗费更多的系统资源。因此如果启用慢查询日志获得更高系统性能则建议优先记录到文件。
mysql SHOW VARIABLES LIKE %log_output%;----------------------| Variable_name | Value |----------------------| log_output | FILE |----------------------
七.开启系统变量让未使用索引的查询也被记录到慢查询日志中
这个系统变量就是log-queries-not-using-indexes所以在进行调优时可以开启这个选项。
mysql show variables like log_queries_not_using_indexes;--------------------------------------| Variable_name | Value |--------------------------------------| log_queries_not_using_indexes | OFF |--------------------------------------
mysql set global log_queries_not_using_indexes1;Query OK, 0 rows affected (0.00 sec)
mysql show variables like log_queries_not_using_indexes;--------------------------------------| Variable_name | Value |--------------------------------------| log_queries_not_using_indexes | ON |--------------------------------------
(4)慢查询测试
一.执行test_index.sql脚本监控慢查询日志内容
[rootlocalhost mysql]# tail -f /var/lib/mysql/test-slow.log
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
二. 执行下面的SQL执行超时(超过1秒)我们去查看慢查询日志
SELECT * FROM test_index WHERE hobby 20009951 OR hobby 10009931 OR hobby 30009931OR dname name4000 OR dname name6600 ;
(5)慢日志内容
我们得到慢查询日志后最重要的一步就是去分析这个日志。先来看慢日志里到底记录了哪些内容如下是慢日志里其中一条记录可以看到有时间戳、用户、查询时长及具体的SQL等信息。
Time Id Command Argument
# Time: 2022-02-23 T03:55:15. 336037Z
# UserHost: root[root] localhost [] Id: 6
# Query_time: 2.375219 Lock_time: 0.000137 Rows_sent: 3 Rows_examined: 5000000
use db4;
SET timestamp1645588515;
SELECT * FROM test_index WHERE hobby 20009961 OR hobby 10009941 OR hobby 30009961 OR dname name4001 OR dname name6601;
Time执行时间
Users用户信息
Query_time查询时长
Lock_time等待锁时长
Rows_sent结果行统计数量
Rows_examined扫描的行数 6.慢查询SQL优化思路
(1)SQL性能下降的原因
(2)慢查询优化思路 (1)SQL性能下降的原因
导致SQL执行性能下降的原因可体现在以下两方面 一.等待时间长
锁表导致查询一直处于等待状态。 二.执行时间长
查询语句没优化、索引失效、关联查询太多join、机器及参数没调优。 (2)慢查询优化思路
一.优先选择优化高并发执行的SQL
因为高并发的SQL出现问题带来后果更严重比如下面两种情况SQL1每小时执行10000次每次20个IO优化后每次18个IO每小时节省2万次IOSQL2每小时10次每次20000个IO每次优化减少2000个IO每小时节省2万次IO。此时SQL2更难优化SQL1更好优化。但是第一种属于高并发SQL更急需优化因为成本更低。 二.定位优化对象的性能瓶颈
在去优化SQL时选择优化分方向有三个
方向1IO数据访问消耗了太多时间查看是否正确使用索引。
方向2CPU数据运算花费了太多时间数据的运算分组、排序是不是有问题。
方向3网络带宽加大网络带宽。 三.明确优化目标
根据数据库当前状态、当前SQL的具体功能来确定最好情况下消耗的资源和最差情况下消耗的资源。因为优化的结果只有一个即给用户一个好的体验。 四.从explain执行计划入手
只有explain能告诉我们当前SQL的执行状态。 五.永远用小的结果集驱动大的结果集
小的数据集驱动大的数据集减少内层表读取次数。
//类似于嵌套循环
for (int i 0; i 5; i) {for (int i 0; i 1000; i) {}
}
六.尽可能在索引中完成排序
排序操作用得比较多所以order by后面的字段尽量使用上索引。因为索引本来就是排好序的所以速度很快。没有索引的话就需要从表中拿数据在内存中进行排序。如果内存空间不够还会发生临时文件落盘操作。 七.只获取自己需要的列
不要使用select *因为select * 很可能不使用索引而且数据量过大。 八.只使用最有效的过滤条件
where后面的条件并非越多越好应该用最短的路径访问到数据。 九.尽可能避免复杂的join和子查询
每条SQL的JOIN操作建议不要超过三张表。将复杂的SQL拆分成多个小的SQL单个表执行然后对获取的结果在程序中进行封装。因为如果join占用的资源比较多会导致其他进程等待时间变长。 十.合理设计并利用索引
也就是合理判断是否需要创建索引以及合理选择合适索引。 (3)如何判定是否需要创建索引
一.频繁作为查询条件的字段应该创建索引
二.唯一性太差的字段不适合单独创建索引即使它频繁作为查询条件
唯一性太差的字段主要是指哪些呢如状态字段、类型字段等。这些字段中的数据可能总共就是那么几个几十个数值重复使用。当一条Query所返回的数据超过了全表的15%时就不应该再使用索引扫描来完成这个Query了。
三.更新非常频繁的字段不适合创建索引
因为索引中的字段被更新时不仅要更新表的数据还要更新索引数据。
四.不会出现在WHERE子句中的字段不该创建索引 (4)如何选择合适索引
一.单键索引尽量选择针对当前Query过滤性更好的索引。
二.联合索引当前查询中过滤性最好的字段在索引字段顺序中排列靠前。
