时光
发布于

MySQL性能优化(四):SQL优化

一:基础数据准备

DROP TABLE IF EXISTS `tbl_user`;

CREATE TABLE `tbl_user` (  
`id` int(11) NOT NULL AUTO_INCREMENT, 
 `username` varchar(255) DEFAULT NULL, 
 `email` varchar(20) DEFAULT NULL, 
 `age` tinyint(4) DEFAULT NULL, 
 `type` int(11) DEFAULT NULL,  
`create_time` datetime DEFAULT NULL,  
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; 

INSERT INTO `tbl_user` VALUES
 ('1', 'admin', 'admin@126.com', '18', '1', '2018-07-09 11:08:57'), 
('2', 'mengday', 'mengday@163.com', '31', '2', '2018-07-09 11:09:00'),
 ('3', 'mengdee', 'mengdee@163.com', '20', '2', '2018-07-09 11:09:04'),
 ('4', 'root', 'root@163.com', '31', '1', '2018-07-09 14:36:19'), 
('5', 'zhangsan', 'zhangsan@126.com', '20', '1', '2018-07-09 14:37:28'),
 ('6', 'lisi', 'lisi@gmail.com', '20', '1', '2018-07-09 14:37:31'),
 ('7', 'wangwu', 'wangwu@163.com', '18', '1', '2018-07-09 14:37:34'),
 ('8', 'zhaoliu', 'zhaoliu@163.com', '22', '1', '2018-07-11 18:29:24'),
 ('9', 'fengqi', 'fengqi@163.com', '19', '1', '2018-07-11 18:29:32');  

DROP TABLE IF EXISTS `tbl_userinfo`;

CREATE TABLE `tbl_userinfo` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `address` varchar(255) DEFAULT NULL,  
`user_id` int(11) DEFAULT NULL,  
PRIMARY KEY (`id`),  
UNIQUE KEY `idx_userId` (`user_id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 

INSERT INTO `tbl_userinfo` VALUES
 ('1', '上海市', '1'),
 ('2', '北京市', '2'), 
('3', '杭州', '3'), 
('4', '深圳', '4'), 
('5', '广州', '5'), 
('6', '海南', '6');

二:五百万数据插入

上面插入几条测试数据,在使用索引时还需要插入更多的数据作为测试数据,下面就通过存储过程插入 500W 条数据作为测试数据

-- 修改mysql默认的结束符号,默认是分号;但是在函数和存储过程中会使用到分号导致解析不正确delimiter $$ -- 随机生成一个指定长度的字符串create function rand_string(n int) returns varchar(255) begin  # 定义三个变量 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0;  while i < n do    set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));   set i = i + 1; end while; return return_str;end $$ -- 创建插入的存储过程create procedure insert_user(in start int(10), in max_num int(10))begin    declare i int default 0;     set autocommit = 0;      repeat        set i = i + 1;        insert into tbl_user values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());        until i = max_num    end repeat;   commit;end $$ -- 将命令结束符修改回来delimiter ; -- 调用存储过程,插入500万数据,需要等待一会时间,等待执行完成call insert_user(100001,5000000);-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G内存用了8分钟才执行完 select count(*) from tbl_user;

三:使用索引和不使用索引的比较

没有添加索引前一个简单的查询用了 1.79 秒

这里写图片描述

创建索引,然后再查询可以看到耗时 0.00 秒,这就是索引的威力

这里写图片描述这里写图片描述这里写图片描述


四:explain 命令

这里写图片描述这里写图片描述

这里写图片描述 这里写图片描述

explain 命令用于查看 SQL 执行时是否使用了索引,是优化 SQL 语句的一个非常常用而且非常重要的一个命令, 上面中的 key 字段表示查询使用到的索引即使用了 idx_username 索引

  1. id: SELECT 识别符。这是 SELECT 的查询序列号
  2. select_type: 查询类型
  • simple: 简单表即不适用表连接或者子查询
    
  • primary: 主查询,即外层的查询
    
  • subquery: 子查询内层第一个SELECT,结果不依赖于外部查询
    
  • dependent subquery: 子查询内层第一个
    
  • select: 依赖于外部查询
    
  • union: UNION语句中第二个SELECT开始后面所有SELECT
    
  • union result union 中合并结果
    
  • DERIVED
    
  1. table:查询的表
  2. partitions
  3. type:扫描的方式,all 表示全表扫描
  • all : 全表扫描
  • index: 扫描所有索引
  • range: 索引范围扫描,常见于 < <=、>、>=、between、
  • const: 表最多有一个匹配行, 常见于根据主键或唯一索引进行查询
  • system: 表仅有一行(=系统表)。这是 const 联接类型的一个特例
  • ref
  1. possible_keys: 该查询可以利用的索引,可能同一个查询有多个索引可以使用,如果没有任何索引显示 null
  2. key: 实际使用到的索引,从 Possible_key 中所选择使用索引,当有多个索引时,MySQL 会挑出一个最优的索引来使用
  3. key_len: 被选中使用索引的索引长度
  4. ref:
  • 多表连接时的外键字段
  • const

10)rows: 估算出结果集行数,该 SQL 语句扫描了多少行,可能得到的结果,MySQL 认为它执行查询时必须检查的行数
11)filtered:
12)Extra: 额外重要的信息

  • no tables: Query 语句中使用 FROM DUAL 或不含任何 FROM 子句
  • using filesort : 使用文件排序,最好能避免这种情况
  • Using temporary: 某些操作必须使用临时表,常见 GROUP BY ; ORDER BY
  • Using where: 不用读取表中所有信息,仅通过索引就可以获取所需数据;
  • Using join buffer (Block Nested Loop)
  • Using index condition
  • Using sort_union(索引名)

查看索引的使用情况:

show status like ‘Handler_read%’; Handler_read_key: 越高越好Handler_read_rnd_next:越低越好

这里写图片描述

查询优化器:

  • 重新定义表的关联顺序(优化器会根据统计信息来决定表的关联顺序)
  • 将外连接转化成内连接(当外连接等于内连接)
  • 使用等价变换规则(如去掉 1=1)
  • 优化 count()、min()、max()
  • 子查询优化
  • 提前终止查询
  • in 条件优化

MySQL 可以通过 EXPLAIN EXTENDED 和 SHOW WARNINGS 来查看 MySQL 优化器改写后的 SQL 语句

这里写图片描述


五:走索引的情况和不走索引的情况

1. in 走索引

in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。

这里写图片描述2. 范围查询走索引

这里写图片描述

3. 模糊查询只有左前缀使用索引

这里写图片描述

** 4. 反向条件不走索引 != 、 <> 、 NOT IN、IS NOT NULL**

这里写图片描述

这里写图片描述

# 常见的对not in的优化,使用左连接加上is null的条件过滤SELECT id, username, age FROM tbl_user WHERE id NOT IN (SELECT user_id FROM tbl_order);  SELECT u.id, u.username, u.ageFROM tbl_user uLEFT JOIN tbl_order o ON u.id = o.user_idWHERE o.user_id IS NULL;

5. 对条件计算(使用函数或者算数表达式)不走索引

使用函数计算不走索引,无论是对字段使用了函数还是值使用了函数都不走索引,解决办法通过应用程序计算好,将计算的结果传递给 SQL,而不是让数据库去计算

这里写图片描述

这里写图片描述

id 是主键,id/10 使用了算数表达式不走索引

这里写图片描述

6. 查询时必须使用正确的数据类型

如果索引字段是字符串类型,那么查询条件的值必须使用引号,否则不走索引

这里写图片描述

7. or 使用索引和不使用索引的情况

or 只有两边都有索引才走索引,如果都没有或者只有一个是不走索引的

这里写图片描述

8. 用 union 少用 or

尽量避免使用 or,因为大部分 or 连接的两个条件同时都进行索引的情况几率比较小,应使用 uninon 代替,这样能走索引的走索引,不能走索引的就全表扫描。

这里写图片描述

9. 能用 union all 就不用 union

union all 不去重复,union 去重复,union 使用了临时表,应尽量避免使用临时表

这里写图片描述

10. 复合索引

对于复合索引,如果单独使用右边的索引字段作为条件时不走索引的。即复合索引如果不满足最左原则 leftmost 不会走复合索引

这里写图片描述

11. 覆盖索引

覆盖索引是 select 的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。
当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

它包括在查询里的 Select、Join 和 Where 子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段,也即,索引包含了查询正在查找的数据)

覆盖索引: 根据关键字就能够直接获取查询所需要的所有数据,不必要读取数据行的数据,所有数据是指 where、select 从句、order by、 group by 从句的值这里写图片描述

如果索引字段是字符串那么查询条件必须加引号,但是如果查询的列都在索引中,即使不满足走索引的条件,此时也会使用索引。示例中 order_code=666666,是数字类型,没有加引号,按说是不走索引的,但是 select * 而 test 表只有两个字段,id 和 order_code 而这两个字段都创建了索引,这种情况也会走索引

这里写图片描述

12. order by

MySQL 有两种排序方式:

通过有序索引顺序扫描直接返回有序数据,通过 explain 分析显示 Using Index,不需要额外的排序,操作效率比较高。
通过对返回数据进行排序,也就是 Filesort 排序,所有不是通过索引直接返回排序结果的都叫 Filesort 排序。Filesort 是通过相应的排序算法将取得的数据在 sort_buffer_size 系统变量设置的内存排序中进行排序,如果内存装载不下,就会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集
order by 使用索引的严格要求:

  • 索引的顺序和 order by 子句的顺序完全一致
  • 索引中所有列的方向(升续、降续)和 order by 子句完全一致
  • 当多表连接查询时 order by 中的字段必须在关联表中的第一张表中

如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。

正例:where a=? and b=? order by c; 索引:a_b_c
反例:索引中有范围查找,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序
这里写图片描述

order by 如果根据多个值进行排序,那么排序方式必须保持一致,要么同时升续,要么同时降续,排序方式不一致不走索引

这里写图片描述

这里写图片描述

13. group by

默认情况下,group by column; 有两个作用,第一个就是根据指定的列进行分组,第二作用 group by 不但分组,而且还为分组中的数据按照列来排序,如果分组的字段创建了索引,那么排序也没什么毕竟排序走索引也很快
但是如果 group by 指定的列没有走索引,而我们通常情况下只对分组中的数据进行统计,例如对分组中的数据求和,通常顺序无关紧要,此时就要关闭 group by 的排序功能,使用 Order By NULL;来关闭排序功能,避免排序对性能的影响。这里写图片描述

14. 分页 limit

分页查询一般会全表扫描,优化的目的应尽可能减少扫描;

第一种思路:在索引上完成排序分页的操作,最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的 id,覆盖索引效率高些

第二中思路:limit m,n 转换为 n

之前分页查询是传 pageNo 页码, pageSize 分页数量,
当前页的最后一行对应的 id 即 last_row_id,以及 pageSize,这样先根据条件过滤掉 last_row_id 之前的数据,然后再去 n 挑记录,此种方式只能用于排序字段不重复唯一的列,如果用于重复的列,那么分页数据将不准确

当只一行数据使用 limit 1这里写图片描述

这里写图片描述

多表连接查询连接条件(也就是外键必须创建索引,否则大数据查询直接卡死)

这里写图片描述 如果全表扫描比使用索引快,就不会使用索引,比如 表的数量很少或者满足条件的数据量比较大也不走索引, 查询数据库记录时,查询到的条目数尽量小,当通过索引获取到的数据库记录 > 数据库总记录的 1/3 时,SQL 将有可能直接全表扫描,索引就失去了应有的作用。

这里写图片描述

where 条件将能过滤掉多的条件写在前面,过滤掉少部分的数据写在后面,这样先排除一大部分不满足条件的数据,然后剩下一小部分数据,然后再从中找出满足条件的记录

这里写图片描述

数据类型不匹配是不会走索引,例如对字符串类型创建索引,where 条件值却没有使用引号,就不走索引,join 语句中 join 条件字段类型不一致的时候 MySQL 无法使用索引

15 in 和 exists

查询所有下过订单的用户(tbl_user 500w 条数据,tbl_order 3 条数据), 有两种方式,一种使用 in 另一种使用 exists,但是两者效率相差很大这里写图片描述

in 的伪代码:

/** * tbl_user 有500w条数据 * tbl_order 有3条数据 * 循环users.size次并在内存中比较包含 * in: 总共执行2次SQL * @return */public List<User> in() {    // SELECT * FROM tbl_user WHERE id IN (SELECT user_id FROM tbl_order)    // 首先执行内层子查询,再执行外层查询, 总共执行两个查询    List<Long> userIds = query("SELECT user_id FROM tbl_order");    List<User> users = query("SELECT * FROM tbl_user");     List<User> result = new ArrayList<>();    for(int i = 0; i < users.size(); i++) {        User user = users.get(i);        // 在内存中比较是否包含        for(int j = 0; j < userIds.size(); j++) {            if (userIds[j] == user.getId()) {                result.add(user);            }        }    }     return result;}

SELECT * FROM A WHERE id IN (SELECT a_id FROM B) 当A表中的数据量远大于B表中的数据量时使用in

查询用户 id 大于 10 的用户的订单信息

这里写图片描述

/** * tbl_user 3条数据 * tbl_order 为500W条数据 * * 使用exists会查询1一次tbl_user表并循环users.size次(3次)查询tbl_order表,总共就4次查询 * * @return */public List<User> exists() {    // SELECT * FROM tbl_user u WHERE exists(SELECT 1 FROM tbl_order o WHERE o.user_id = u.id)    List<User> users = query("SELECT * FROM tbl_user");    List<User> result = new ArrayList<>();    for(int i = 0; i < users.size(); i++) {        User user = users.get(i);        // 每循环一次就会操作一次数据库        boolean isExists = query("SELECT 1 FROM tbl_order o WHERE o.user_id =", user.getId());        if (isExists) {            result.add(user);        }    }     return result;}

从以上逻辑可以看到 exists 每次循环一下外表都要查询一下内表,即使外表数量很少,但是每循环一次外表都要去查询一个大表,这样的效率是不高的,除非子查询走索引,最好走覆盖索引。

SELECT * FROM B o WHERE exists(SELECT 1 FROM A WHERE o.user_id > 10) 网上说当 B 表的数据量小 A 表的数据量很大时用 exists。上面那个例子没有测试出来,实际优化时 in 和 exists 具体那个好根据具体执行情况来选择,也不能一味的就说某种情况下用这个就比那个快

16. 强制索引

当查询时不走索引时可以通过 force index 强制 MySQL 使用指定的索引,一般情况下如果 MySQL 不走索引它是认为全表扫描会更快些,可以通过强制走索引看一下查询时间,如果强制索引效果更好,查询速度更快就使用强制索引,如果强制索引没有明显效果就没必要使用了。

MySQL 强制使用索引:force index(索引名或者主键 PRI)

-- 强制使用主键索引select * from table force index(PRI); -- 强制使用索引"idx_xxx"select * from table force index(idx_xxx); -- 强制使用索引"PRI"和"idx_xxx"select * from table force index(PRI,idx_xxx);

MySQL 禁止某个索引:ignore index(索引名或者主键 PRI)

-- 禁止使用主键select * from table ignore index(PRI) -- 禁止使用索引"idx_xxx"select * from table ignore index(idx_xxx); -- 禁止使用索引"PRI","idx_xxx"select * from table ignore index(PRI,idx_xxx);

六:其它优化

  1. 禁止使用 select *,需要什么字段就去取哪些字段这里写图片描述
  2. 超过三个表禁止 join。需要 join 的字段数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。说明:即使双表 join 也要注意表索引、SQL 性能。尽可能避免复杂的 join 和子查询。尽量使用左右连接,少使用内连接。永远小结果集驱动大结果集(这点 MySQL 会自动优化)
  3. 不要使用 count(列名)或 count(常量)来替代 count(),count()是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。 说明:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。
  4. 不得使用外键与级联,一切外键概念必须在应用层解决。
  5. 禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。避免使用存储过程、触发器。
  6. 使用表连接来优化子查询。尽量避免使用子查询,建议将子查询转换成关联查询,子查询的效率并没有连接 join 查询快(并不绝对),连接查询之所以更有效率一些,是因为 MySQL 不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。对于多表连接,如果连接条件创建索引效率更高。
  7. 对于列类型是字符串,值必须使用单引号括住,如果没有引号引住就不走索引, 结论:字符串必须使用‘’引住
  8. 拒绝大 SQL,拆分成小 SQL
  9. 优先优化高并发的 SQL,而不是执行频率低某些“大”SQL

10、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

11、减少 IO 次数
IO 永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过 90% 的时间都是 IO 操作所占用的,减少 IO 次数是 SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段。

12、降低 CPU 计算
除了 IO 瓶颈之外,SQL 优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后,降低 CPU 计算也就成为了我们 SQL 优化的重要目标

13、大表的数据修改最好要分批处理,例如 1000 万行的记录删除更新 100 万行记录,可以一次只删除更新 5000 行记录,暂停几秒,然后再执行剩下的数据。
如何修改大表的表结构:

  • 先在从服务器上修改,然后将从服务器改为主服务器,然后再从主服务器上修改,然后再切换回来,此种方式需要切换主从,有一定的风险。
  • 在主服务器上创建一张新表,将老表的数据迁移到新表,创建一个触发器,将老表的新数据同步到新表中,对老表加一个排它锁,重新命名新表和老表,然后删除掉老表(整个操作过程比较复杂,可以通过工具来实现)
pt-online-schema-change      --alter="modify c varchar(150) not null default '' "      --user=root      --password=123456      D=数据库名,t=表名      --charset=utf8      --execute

这里写图片描述

当进行多表连接查询时, [驱动表] 的定义为:

  1. 指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]
  2. 未指定联接条件时,行数少的表为[驱动表](Important!)MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。

七:SQL 提示(sql hint)

在 SQL 语句中加入一些人为的提示达到绣花操作的目的。

-- SQL_BUFFER_RESULT: 强制生成一个临时结果集,只要临时结果集生成后,所有表上的锁定均被释放。这能再遇到表锁定问题时或者-- 要花很长时间将结果传给客户端时有所帮助,因为可以尽快释放锁资源。SELECT SQL_BUFFER_RESULT * FROM tbl_user; -- mysql参考的索引列表,不在这个配置内mysql就不去使用SELECT * FROM tbl_user USE INDEX (idx_username); -- 让mysql或略掉指定的索引列表SELECT * FROM tbl_user IGNORE INDEX (idx_username); -- 有些情况下mysql会找出最快的查询方式,如果索引没有明显的提速,可能会进行全表扫描,-- 可以强制mysql使用指定的索引,但是之于效率可能没有全表扫描的好SELECT * FROM tbl_user FORCE INDEX (idx_username);

正则表达条件 SELECT * FROM tbl_user WHERE email REGEXP ‘@126.com$’;


八:show profile

这里写图片描述

show profile 可以更清楚的了 SQL 执行的过程

show profile 和 show profiles 语句可以展示当前会话(退出 session 后,profiling 重置为 0) 中执行语句的资源使用情况。 默认情况下 profiling 是关闭的。

show profiles :列表,显示最近发送到服务器上执行的语句的资源使用情况。显示的记录数由变量:profiling_history_size 控制,默认 15 条。
show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status 和 Duration 两列。
show profile 还可根据 show profiles 列表中的 Query_ID ,选择显示某条记录的性能分析信息。

-- 查看profiling是否开启SHOW PROFILES;SHOW VARIABLES LIKE '%profiling%';SELECT @@profiling;SELECT @@have_profiling; -- 开启profilingSET profiling=1; -- 执行SQLSELECT * FROM tbl_user WHERE id > 1000000 ORDER BY id ASC LIMIT 2; SHOW PROFILES; SHOW PROFILE for query 1; SHOW PROFILE cpu, swaps for query 1;

语法结构:

SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]

type:

  • ALL
  • BLOCK IO
  • CONTEXT SWITCHES
  • CPU
  • IPC
  • MEMORY
  • PAGE FAULTS
  • SOURCE
  • SWAPS

这里写图片描述

这里写图片描述


九:找出项目中执行比较慢的 SQL 语句

MySQL 慢查询日志记录下所有执行超过 long_query_time 时间的 SQL 语句,帮你找到执行慢的 SQL(包括查询语句、修改语句、已经回滚的 sql),方便我们对这些 SQL 进行优化。 默认情况下,MySQL 没有启用慢查询日志,如果需要启动需要在 my.cnf 配置文件中配置开启

show [session|global] status like 'xxx'

默认是 session 会话,即取出当前窗口的执行,global 值从 MySQL 启动到现在

-- 查看所有状态show status; -- 数据库启动运行的时间show status like 'uptime'; -- 连接次数show status like 'connections';  -- 执行的查询次数(通过这几个参数就很容易的知道数据库应用是插入更新为主,还是查询为主,以及各种类型的sql执行比例)show status like 'com_select'; show status like 'com_insert';show status like 'com_update';show status like 'com_delete';  -- 查询事务提交回滚情况   show status like 'com_commit';  show status like 'com_rollback';     -- 慢查询相关变量show variables like '%slow%'; -- 慢查询次数(不仅记录查询,而是记录执行时间超过默认时间的操作,增删改都有可能属于慢查询)show status like 'slow_queries';  -- mysql默认10秒才算慢查询show variables like 'long_query_time';-- 修改成1秒set long_query_time = 1;

这里写图片描述

/etc/my.cnf

# Exmple MySQL config file for medium systems.  #  # This is for a system with little memory (32M - 64M) where MySQL plays  # an important part, or systems up to 128M where MySQL is used together with  # other programs (such as a web server)  #  # MySQL programs look for option files in a set of  # locations which depend on the deployment platform.  # You can copy this option file to one of those  # locations. For information about these locations, see:  # http://dev.mysql.com/doc/mysql/en/option-files.html  #  # In this file, you can use all long options that a program supports.  # If you want to know which options a program supports, run the program  # with the "--help" option.  # The following options will be passed to all MySQL clients  [client]default-character-set=utf8#password   = your_password  port        = 3306  socket      = /tmp/mysql.sock   # Here follows entries for some specific programs  # The MySQL server  [mysqld]character-set-server=utf8init_connect='SET NAMES utf8port        = 3306  socket      = /tmp/mysql.sock  skip-external-locking  key_buffer_size = 16M  max_allowed_packet = 1M  table_open_cache = 64  sort_buffer_size = 512K  net_buffer_length = 8K  read_buffer_size = 256K  read_rnd_buffer_size = 512K  myisam_sort_buffer_size = 8M  character-set-server=utf8  init_connect='SET NAMES utf8' # Don't listen on a TCP/IP port at all. This can be a security enhancement,  # if all processes that need to connect to mysqld run on the same host.  # All interaction with mysqld must be made via Unix sockets or named pipes.  # Note that using this option without enabling named pipes on Windows  # (via the "enable-named-pipe" option) will render mysqld useless!  #   #skip-networking  # Replication Master Server (default)  # binary logging is required for replication  log-bin=mysql-bin  # binary logging format - mixed recommended  binlog_format=mixed  # required unique id between 1 and 2^32 - 1  # defaults to 1 if master-host is not set  # but will not function as a master if omitted  server-id   = 1  # Replication Slave (comment out master section to use this)  #  # To configure this host as a replication slave, you can choose between  # two methods :  #  # 1) Use the CHANGE MASTER TO command (fully described in our manual) -  #    the syntax is:  #  #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,  #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;  #  #    where you replace <host>, <user>, <password> by quoted strings and  #    <port> by the master's port number (3306 by default).  #  #    Example:  #  #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,  #    MASTER_USER='joe', MASTER_PASSWORD='secret';  #  # OR  #  # 2) Set the variables below. However, in case you choose this method, then  #    start replication for the first time (even unsuccessfully, for example  #    if you mistyped the password in master-password and the slave fails to  #    connect), the slave will create a master.info file, and any later  #    change in this file to the variables' values below will be ignored and  #    overridden by the content of the master.info file, unless you shutdown  #    the slave server, delete master.info and restart the slaver server.  #    For that reason, you may want to leave the lines below untouched  #    (commented) and instead use CHANGE MASTER TO (see above)  #  # required unique id between 2 and 2^32 - 1  # (and different from the master)  # defaults to 2 if master-host is set  # but will not function as a slave if omitted  #server-id       = 2  #  # The replication master for this slave - required  #master-host     =   <hostname>  #  # The username the slave will use for authentication when connecting  # to the master - required  #master-user     =   <username>  #  # The password the slave will authenticate with when connecting to  # the master - required  #master-password =   <password>  #  # The port the master is listening on.  # optional - defaults to 3306  #master-port     =  <port>  #  # binary logging - not required for slaves, but recommended  #log-bin=mysql-bin  # Uncomment the following if you are using InnoDB tables  #innodb_data_home_dir = /usr/local/mysql/data  #innodb_data_file_path = ibdata1:10M:autoextend  #innodb_log_group_home_dir = /usr/local/mysql/data  # You can set .._buffer_pool_size up to 50 - 80 %  # of RAM but beware of setting memory usage too high  #innodb_buffer_pool_size = 16M  #innodb_additional_mem_pool_size = 2M  # Set .._log_file_size to 25 % of buffer pool size  #innodb_log_file_size = 5M  #innodb_log_buffer_size = 8M  #innodb_flush_log_at_trx_commit = 1  #innodb_lock_wait_timeout = 50basedir=/usr/local/mysqldatadir=/usr/local/mysql/data  slow_query_log=trueslow_query_log_file = /usr/local/mysql/data/slow.loglong_query_time=1 [mysqldump]  quick  max_allowed_packet = 16M   [mysql]  no-auto-rehash  # Remove the next comment character if you are not familiar with SQL  #safe-updates  default-character-set=utf8    [myisamchk]  key_buffer_size = 20M  sort_buffer_size = 20M  read_buffer = 2M  write_buffer = 2M   [mysqlhotcopy]  interactive-timeout

windows 是 my.ini 文件,mac 是/etc/my.cnf,在高版本中没有,需要手动创建,找出慢 SQL 需要在 my.cnf 配置如下参数

  • basedir=/usr/local/mysql
  • datadir=/usr/local/mysql/data
  • slow_query_log=true # 开启慢查询
  • slow_query_log_file = /usr/local/mysql/data/slow.log # 指定慢查询日志的存储路径和文件
  • long_query_time=1 # 指定记录慢查询的时间阀值

配置完成后重启 MySQL 服务器

# 关闭mysqlsudo /usr/local/mysql/support-files/mysql.server stop # 启动mysqlsudo /usr/local/mysql/support-files/mysql.server start# 如果启动报错使用下面命令来解决ERROR! The server quit without updating PID file (/usr/local/mysql/data/MacOSX.local.pid).sudo chown -R mysql:mysql /usr/local/var/mysqlsudo /usr/local/mysql/support-files/mysql.server start

执行一个耗时的 SQL,然后去/usr/local/mysql/data/slow.log 目录查看日志中已经记录了该 SQL
SELECT * FROM tbl_user WHERE email = ‘mengday@163.com’;


十:记录不走索引的 SQL

MySQL 不但能记录慢查询对应的 SQL,也能记录没有使用索引的 SQL

-- 查看是否开启记录没有使用索引的功能show variables like '%not_using_index%'; -- 开启记录没有使用索引的记录set @@global.log_queries_not_using_indexes=on; -- 执行一个不走索引的查询EXPLAIN SELECT id, username FROM tbl_user WHERE username = 'admin' OR email = 'admin@163.com'; SELECT id, username FROM tbl_user WHERE username = 'admin' OR email = 'admin@163.com';

这里写图片描述

不走索引的日志仍然记录在慢查询对应的日志文件,这里配置的是/usr/local/mysql/data/slow.log

这里写图片描述


十一: 分析慢查询 SQL

1. 可以通过 mysqldumpslow 命令来分析慢查询的 SQL

mysqldumpslow -s r -t 10 /usr/local/mysql/data/slow.log

-s 排序:自定按那种排序方式输出结果

  • c: 总次数
  • t : 总时间
  • l:锁的时间
  • r: 总数据行
  • at,al,ar: t,l, r 平均数, 例如 at =总时间/总次数

-t top: 指定取前几条作为输出结果

这里写图片描述

** 2. 使用第三方工具 pt-query-digest**

--explain:会生成每个SQL对应的执行计划pt-query-digest --explain h=127.0.0.1,u=root,p=123456  /usr/local/mysql/data/slow.log;

3. 实时获取有性能的 SQL

-- 实时获取有性能的sqlSELECT  id,  user, host, db, command, time, state, info FROM information_schema.`PROCESSLIST`;
评论