数据库_MySQL_优化

最新推荐文章于 2022-09-27 21:00:08 发布

平揽星尘

最新推荐文章于 2022-09-27 21:00:08 发布

阅读量102

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_15764943/article/details/92803842

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

数据库_MySQL_优化

索引优化
查询优化
其它

索引优化

B-Tree（默认）

B-Tree索引可以被用在像=,>,>=,<,<=和BETWEEN这些比较操作符上。而且还可以用于LIKE操作符，只要它的查询条件是一个不以通配符开头的常量。

Hash

Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

特性：

1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询。 
2. 联合索引中，Hash索引不能利用部分索引键查询。 
3. Hash索引无法避免数据的排序操作 
4. Hash索引任何时候都不能避免表扫描 
5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高，字段出现大量相同值不建议使用Hash

索引添加弊端

在这里插入图片描述

使用策略

1、索引列不用使用表达式或函数，如下out_date有索引，推荐下面语句

select * from table_name where out_date <= in_date(current_date,interval 30 day)

2、前缀索引和索引列的选择性
给指定字段添加前缀索引：指定字段记录的前几个字符进行索引，如下

create index index_name on table(col_name(n))

3、索引扫描优化排序
mysql支持两种方式的排序，filesort和index，index效率高，它指mysql扫描索引本身完成排序，filesort方式效率低。orderby满足两下情况会使用index方式排序，order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列。
在这里插入图片描述
4、索引优化锁
回话一：
begin;
select * from table_name where name = ‘小明’ for update;

回话二：
begin；
select * from table_name where name = ‘小王’ for update; //等待回话一万次，发生排它锁

解决方案，查询条件添加索引：
create index idx_name on table_name(name); //给name字段添加索引

示例

1、设置hash索引

CREATE TABLE `index_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  `num3` decimal(14,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `num1_hash` (`num1`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2、分析sql

explain select * from index_test where num1 < 2;

3、结果
在这里插入图片描述
4、说明
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
6、备注
可以看出在<=2与<3是能命中索引的，在<=3与<4是全表扫描，所以<,>等符号也不是完全不能命中索引

查询优化

获取有性能问题的SQL

1、通过用户反馈获取
2、通过慢查询日志获取（避免大量日志占用磁盘）
3、实时获取

使用慢查询日志

#查询指定配置值
show variable like 'long_query_time';
#设置指定配置值
set global show_query_log=on;

show_query_log 启动停止记录慢查日志
show_query_log_file 指定慢查询日志的存储路径及文件
long_query_time 指定记录慢查日志SQL执行时间的伐值，默认10s ，设置为1
log_queries_not_using_indexes 是否记录未使用索引的SQL

使用工具

1、mysqldumpslow

mysqldumpslow -s r -t 10 show-mysql.log

-s order(c,t,l,r,at,al,ar)
2、pt-query-digest

pt-query-digest --explain h=127.0.0.1,u=root,p=qwe123 slow-mysql.log > tp-query.log

①安装

https://www.percona.com/doc/percona-toolkit/2.2/installation.html
yum install -y perl-CPAN perl-Time-HiRes

方法一：rpm安装
cd /usr/local/src
wget percona.com/get/percona-toolkit.rpm
yum install -y percona-toolkit.rpm
工具安装目录在：/usr/bin

方法二：源码安装
cd /usr/local/src
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd percona-toolkit-2.2.19
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install
工具安装目录在：/usr/local/percona-toolkit/bin

②查询语句选项

--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可以分析一段时间内的慢查询。

③输出格式分析

第一部分：总体统计结果
Overall：总共有多少条查询
Time range：查询执行的时间范围
unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
total：总计   min：最小   max：最大  avg：平均
95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值
median：中位数，把所有值从小到大排列，位置位于中间那个数

第二部分：查询分组统计结果
Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询对象

第三部分：每一种查询的详细统计结果
由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
ID：查询的ID号，和上图的Query ID对应
Databases：数据库名
Users：各个用户执行的次数（占比）
Query_time distribution ：查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables：查询中涉及到的表
Explain：SQL语句

④用法

1.直接分析慢查询文件:
pt-query-digest  slow.log > slow_report.log

2.分析最近12小时内的查询：
pt-query-digest  --since=12h  slow.log > slow_report2.log

3.分析指定时间范围内的查询：
pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log

4.分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

5.针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

6.查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

7.把查询保存到query_review表
pt-query-digest --user=root –password=abc123 --review  h=localhost,D=test,t=query_review--create-review-table  slow.log

8.把查询保存到query_history表
pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  slow.log_0001
pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  slow.log_0002

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  --type=binlog  mysql-bin000093.sql > slow_report10.log

11.分析general log
pt-query-digest  --type=genlog  localhost.log > slow_report11.log

查询缓存

在这里插入图片描述

其它

表结构调整

工具使用pt-online-schema-change

1、安装

下载：
wget percona.com/get/percona-toolkit.tar.gz
安装：
tar -zxvf percona-toolkit.tar.gz
cd percona-toolkit-3.0.4
perl Makefile.PL
(若执行Makefile出错 则需先执行yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker)
make
make test
make install
报错处理：
yum -y install perl-Digest-MD5安装相关组件
提示缺少perl-DBI模块，那么直接 
yum install perl-DBI

2、使用

场景1：增加列
pt-online-schema-change --host=192.168.0.0 -uroot -pyourpassword --alter "add column age int(11) default null" D=test,t='test_tb' --execute --print --statistics --no-check-alter
场景2：删除列
pt-online-schema-change --host=192.168.0.0 -uroot -pyourpassword --alter "drop column age" D=test,t='test_tb' --execute --print --statistics --no-check-alter
场景3：更改列
pt-online-schema-change --host=192.168.0.0 -uroot -pyourpassword --alter "CHANGE id id_num int(20)" D=test,t='test_tb' --execute --print --statistics --no-check-alter
场景4：创建索引
pt-online-schema-change --host=192.168.0.0 -uroot -pyourpassword --alter "add index indx_ukid(address_ukid)" D=test,t='address_tb' --execute --print --statistics --no-check-alter

pt-online-schema-change --alter=“modify c varchar(120) not null default ‘’” --user=root D=database_name,t=table_name – charset=utf-8 --execute

SQL优化示例

1、优化not in和<>查询
在这里插入图片描述
2、使用汇总表优化查询

pt-summary

1、获取帮助
pt-summary –help
2、查看服务器信息
pt-summary
3、查看磁盘开销使用信息
pt-diskstats
4、查看mysql数据库信息
pt-mysql-summary --user=root --password=123456
5、分析慢查询日志
pt-query-digest /data/mysql/data/db-3-12-slow.log
6、查找mysql的从库和同步状态
pt-slave-find --host=localhost --user=root --password=123456
7、查看mysql的死锁信息
pt-deadlock-logger --user=root --password=123456 localhost
8、从慢查询日志中分析索引使用情况
pt-index-usage slow_20131009.log
9、查找数据库表中重复的索引
pt-duplicate-key-checker --host=localhost --user=root --password=123456
10、查看mysql表和文件的当前活动IO开销
pt-ioprofile
11、查看不同mysql配置文件的差异
pt-config-diff /etc/my.cnf /etc/my_master.cnf
12、pt-find查找mysql表和执行命令，示例如下
查找数据库里大于 2G 的表： pt-find --user=root --password=123456 --tablesize +2G
查找 10 天前创建，MyISAM 引擎的表： pt-find --user=root --password=123456 --ctime +10 --engine MyISAM
查看表和索引大小并排序：pt-find --user=root --password=123456 --printf “%T\t%D.%N\n” | sort -rn
13、pt-kill 杀掉符合标准的mysql进程
显示查询时间大于 60 秒的查询：
pt-kill --user=root --password=123456 --busy-time 60 --print kill
掉大于 60 秒的查询：
pt-kill --user=root --password=123456 --busy-time 60 --kill
14、查看mysql授权
pt-show-grants --user=root --password=123456
pt-show-grants --user=root --password=123456 --separate –revoke
15、验证数据库复制的完整性
pt-table-checksum --user=root --password=123456