前言
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