前言

hello，大家好，前面也讲过数据库很多方便，比例锁、表设计、索引、存储过程以及隔离级别，也包含数据库优化，那么今天就给大家单独带来一篇性能方面的优化篇

一、慢查询

如何发现有问题的SQL？

一个sql新能不够改好，那你怎么知道不够好，一般都是通过开启慢查询日志，来定位sql有毒。

1.1、慢查询开启和查询

-- 查看系统变量详解
show variables  
-- 查看慢查询日志
show variables like 'slow_query_log';
-- 慢查询日志存放路径
set global slow_query_log_file = '/home/用户/mysql文件夹/mysql-show.log';
-- 开启
set global log_queries_not_using_indexes=on;
-- 大于1S的查询打印起来
set global long_query_time=1

1.2、如何分析慢查询日志

工具：mysqldumpslow

-- 前三条慢的SQL
mysqldumpslow -t 3 /home/mysql/mysql-slow.log | more

工具：pt-query-digest slow-log > slow_log.report

哪些sql重点关注

  • 查询次数多并且每次查询占用时间久的sql
  • IO大的sql
  • 未命中索引的sql（rows examine 和 rows send对比）

二、SQL优化

2.1、SQL索引优化

  • explain执行计划
table：关于那些表
type：显示连接使用那种类型 const>eq_reg>ref>range>index和All
possible_keys：显示可能应用该表的索引。
key：实际使用索引
key_len：索引长度，越短越好
ref：显示索引某一列被使用
rows：返回查询的行数

2.2、SQL优化

  • count()
  • group by
  • max()
  • limit
select * from testcase_exec_history where id in (
	SELECT * FROM (
		SELECT id FROM testcase_exec_history
		ORDER BY test_strategy_id
		LIMIT 300000, 10
	) t
)
  • 小表驱动大表
  • 关联不要使用三张表以上
  • in、not in 、like防止索引失效，能尽量不要使用范围查询就不要用
  • 查询条件一定和字段类型对应

2.3、索引优化

  • 如何选择建立索引
    • 1、建立在where、group by、order by、on使用的列
    • 2、索引越小越好
    • 3、离散度大的列放在联合索引前面
select * from payment where staff_id = 2 and cus_id = 5;
是index(staff_id, cus_id)好还是index(cus_id, staff_id)

答案：cus_id离散度越大，就应该使用index(cus_id, staff_id)
  • 如何优化
    • 重复以及冗余索引
pt-duplicate-key-checker工具检查冗余索引

pt-duplicate-key-checker\
-uroot \
-p \
-h 127.0.0.1

- 删除无用索引

三、数据结构优化

3.1、选择合适数据类型

  • 尽量使用最小的数据类型、空间大小
  • int比varchar在mysql上处理简单‘’
  • 尽可能使用not null
  • 尽可能避免使用text类型
  • int类型储存日期时间
    • FROM_UNIXTIME()、UNIX_TIMESTAMP()
  • ip使用binint。
    • INET_ATON、INET_NTOA