性能优化之MySQL优化

系统管理员 2022-05-19 13:55 394阅读 0赞

# 性能优化之MySQL优化(一) #

![性能优化之MySQL优化（一）][MySQL]

http://dev.mysql.com/doc/index-other.html

http://dev.mysql.com/doc/sakila/en/sakila-installation.html（使用教程）

下载sakila数据库：http://dev.mysql.com/doc/index-other.html或者是http://downloads.mysql.com/docs/sakila-db.zip

解压sakila-db.zip到Desktop，里面包含三个文件

sakila.mwb

sakila-schema.sql //数据库结构文件

sakila-data.sql //数据文件

select @@version；查看MySQL版本

![性能优化之MySQL优化（一）][MySQL 1]

![性能优化之MySQL优化（一）][MySQL 2]

![性能优化之MySQL优化（一）][MySQL 3]

慢查日志分析软件:mysqldumpslow——mysql官方分析软件

启用mysqldumpslow时首先要在workbench中设置basedir的路径才能避免报错

mysqldumpslow 在windows中是一个perl文件，所以需要你配置perl环境变量,并使用perl运行

查看慢查询日志，命令：mysqldumpslow -t 要查看的条数 日志文件的位置 | more

![性能优化之MySQL优化（一）][MySQL 4]

![性能优化之MySQL优化（一）][MySQL 5]

![性能优化之MySQL优化（一）][MySQL 6]

慢查询分析工具2：pt-query-digest

输出到文件：pt-query-digest show-log > slow\_log.report

输出到数据库表 pt-query-digest show.log -review

h=127.0.0.1,D=test,p=root.P=3306,u=root,t=query\_review

\--create-reviewtable

\--review-history t=hostname\_show

# 性能优化之MySQL优化（二） #

![性能优化之MySQL优化（二）][MySQL 7]

![性能优化之MySQL优化（二）][MySQL 8]

![性能优化之MySQL优化（二）][MySQL 9]

![性能优化之MySQL优化（二）][MySQL 10]

![性能优化之MySQL优化（二）][MySQL 11]

![性能优化之MySQL优化（二）][MySQL 12]

count(\*)和count(release\_year)的区别：

count(\*)统计满足条件的记录数release\_year=null的记录也会被统计，count(release\_year)则会排除为null的结果再统计。

![性能优化之MySQL优化（二）][MySQL 13]

子查询优化时，如果是一对多查询，使用join on时要用distinct去重

如果子查询in中有重复的数据,从查询的结果是看不出来的,因为in(1,1)只会返回一条记录.但是join会显示两条.尽量用join

![性能优化之MySQL优化（二）][MySQL 14]

![性能优化之MySQL优化（二）][MySQL 15]

group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。

可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io

如果涉及到表联接的过程中有group by这样的语句,可以先通过group by做为子查询，统计出结果后，再与其它表进行关联查询。

优化group by 查询后，要在外面加上筛选条件则在子查询中增加

![性能优化之MySQL优化（二）][MySQL 16]

![性能优化之MySQL优化（二）][MySQL 17]

![性能优化之MySQL优化（二）][MySQL 18]

# 性能优化之MySQL优化（三） #

![性能优化之MySQL优化（三）][MySQL 19]

当一个索引包含了查询中的所有列 就成为覆盖索引

离散度：数据唯一值越多，离散度越高。例如，

select count(distinct id),count(distinct real\_name) from user;

id 是唯一值，所以离散度更高。而 real\_name 会出现重复—>会有同名同姓的情况，所以离散度低。

select count(distinct customer\_id), count(distinct staff\_id) from payment;

查看离散度 通过统计不同的列值来实现 count越大 离散程度越高

![性能优化之MySQL优化（三）][MySQL 20]

索引中是字段越小约好，因为是分页进行存储的，字段越小，一页中索引会包含越多。

![性能优化之MySQL优化（三）][MySQL 21]

![性能优化之MySQL优化（三）][MySQL 22]

![性能优化之MySQL优化（三）][MySQL 23]

pt-duplicate-key-checker这个工具可以用来查询冗余索引

![性能优化之MySQL优化（三）][MySQL 24]

pt-duplicate-key-checker

检查多余keypt-index-usage

索引检查pt-query-digest 慢查询分析

![性能优化之MySQL优化（三）][MySQL 25]

数据库优化之使用bigint来存储IP地址。

数据库优化之使用int来存储日期

如果用null，其表结构和not null的不一样，null字段存储会占用比较多，会增加io开销

![性能优化之MySQL优化（三）][MySQL 26]

1、时间转时间戳：unix\_timestamp("2016-08-01 13:14:00");

2、时间戳转时间：fromm\_unixtime(1508076155)

![性能优化之MySQL优化（三）][MySQL 27]

# 性能优化之MySQL优化（四） #

![性能优化之MySQL优化（四）][MySQL 28]

![性能优化之MySQL优化（四）][MySQL 29]

为了查询效率，有些时间需要反范型化

反范式化操作,适当增加复杂性，提高数据库操作效率

反范式化： 增加冗余信息，少关联表，以空间换时间

![性能优化之MySQL优化（四）][MySQL 30]

![性能优化之MySQL优化（四）][MySQL 31]

![性能优化之MySQL优化（四）][MySQL 32]

![性能优化之MySQL优化（四）][MySQL 33]

![性能优化之MySQL优化（四）][MySQL 34]

![性能优化之MySQL优化（四）][MySQL 35]

![性能优化之MySQL优化（四）][MySQL 36]

表的水平拆分。前后台业务分离，前台查询分表的数据，后台要执行报表操作则把数据统一到汇总表再操作

![性能优化之MySQL优化（四）][MySQL 37]

![性能优化之MySQL优化（四）][MySQL 38]

![性能优化之MySQL优化（四）][MySQL 39]

![性能优化之MySQL优化（四）][MySQL 40]

/usr/sbin/mysqld - -verbose - -help | grep -A 1 'Default options '

查看MySQL配置文件路径，如果存在多个配置文件，则后面的会覆盖前面的

\#重要，缓冲池的大小 推荐总内存量的75%，越大越好。innodb\_buffer\_pool\_size

\#默认只有一个缓冲池，如果一个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲

![性能优化之MySQL优化（四）][MySQL 41]

![性能优化之MySQL优化（四）][MySQL 42]

![性能优化之MySQL优化（四）][MySQL 43]

![性能优化之MySQL优化（四）][MySQL 44]

![性能优化之MySQL优化（四）][MySQL 45]

![性能优化之MySQL优化（四）][MySQL 46]

**mysql第三方配置工具:**

https://tools.percona.com/wizard

[MySQL]: /images/20220519/b9fd5653631944d28e81071fdb09efa4.png
[MySQL 1]: /images/20220519/341fe53004b24f1ba96f9d7661c33ac9.png
[MySQL 2]: http://p3.pstatp.com/large/pgc-image/1526985194921817a002b7a
[MySQL 3]: http://p3.pstatp.com/large/pgc-image/1526985216210e19309566c
[MySQL 4]: /images/20220519/ac9cb40b19af49878eb591ee23d1f503.png
[MySQL 5]: http://p3.pstatp.com/large/pgc-image/1526986043034b4b9219077
[MySQL 6]: /images/20220519/2b544271df934b4ca631390c1da5ce4a.png
[MySQL 7]: /images/20220519/0559bb007e1b4fab839d460e422b788e.png
[MySQL 8]: /images/20220519/1f01609c1af948b7ad61d749265b1b72.png
[MySQL 9]: /images/20220519/4b3d581861e84a34961da777ee33fa01.png
[MySQL 10]: http://p3.pstatp.com/large/pgc-image/1527054092600fc2e4bae8a
[MySQL 11]: http://p3.pstatp.com/large/pgc-image/152705572489016297ec647
[MySQL 12]: http://p9.pstatp.com/large/pgc-image/1527055912563a58e4727f3
[MySQL 13]: http://p9.pstatp.com/large/pgc-image/15270573965266568038a09
[MySQL 14]: http://p3.pstatp.com/large/pgc-image/152706148014037f7b26a95
[MySQL 15]: http://p3.pstatp.com/large/pgc-image/1527061572675536eb6b768
[MySQL 16]: /images/20220519/dfbcc5f62f6d41c78b46e3175292e688.png
[MySQL 17]: /images/20220519/81dc9ff97c35494f8b998d29e52f02e3.png
[MySQL 18]: http://p3.pstatp.com/large/pgc-image/15270624068537a0d211381
[MySQL 19]: /images/20220519/88be72e42a7f4fcb91408bd1f756052f.png
[MySQL 20]: http://p3.pstatp.com/large/pgc-image/15270636013220127b28b34
[MySQL 21]: http://p3.pstatp.com/large/pgc-image/152706659092125d44e7449
[MySQL 22]: http://p3.pstatp.com/large/pgc-image/152706670947828ad265706
[MySQL 23]: /images/20220519/caaebceab8104b019b2d28cf1c9465e0.png
[MySQL 24]: http://p3.pstatp.com/large/pgc-image/1527067624072c21d563906
[MySQL 25]: http://p3.pstatp.com/large/pgc-image/152706779077120e3efd9c6
[MySQL 26]: http://p3.pstatp.com/large/pgc-image/15270679960981540654441
[MySQL 27]: http://p3.pstatp.com/large/pgc-image/152706805858588c8863b87
[MySQL 28]: /images/20220519/df45ee0c4a004229b8a10017c32988e9.png
[MySQL 29]: http://p9.pstatp.com/large/pgc-image/1527081911184161bf36eac
[MySQL 30]: /images/20220519/a9928e16c90a48ffa03f80b406db1264.png
[MySQL 31]: /images/20220519/2444c7eebd1a4266ba4f155e93ed03c9.png
[MySQL 32]: /images/20220519/e9a39064471844328673ad90cc4c9288.png
[MySQL 33]: /images/20220519/601c69f9a2114cabb3df83c364281131.png
[MySQL 34]: http://p3.pstatp.com/large/pgc-image/1527131212968b18cb83069
[MySQL 35]: http://p3.pstatp.com/large/pgc-image/1527131413231e420f0de03
[MySQL 36]: http://p9.pstatp.com/large/pgc-image/1527131472150b25293303f
[MySQL 37]: /images/20220519/90baa9bf55cc4a31be45c5dd542c6c42.png
[MySQL 38]: http://p3.pstatp.com/large/pgc-image/1527131984735b235327c0b
[MySQL 39]: http://p3.pstatp.com/large/pgc-image/1527137279796a3a582a220
[MySQL 40]: http://p3.pstatp.com/large/pgc-image/1527137394833c756b77d1b
[MySQL 41]: http://p3.pstatp.com/large/pgc-image/15271376131459a1ba2fbbd
[MySQL 42]: http://p3.pstatp.com/large/pgc-image/15271377065964be3f5ac81
[MySQL 43]: http://p3.pstatp.com/large/pgc-image/15271377646134acfa2bf46
[MySQL 44]: http://p3.pstatp.com/large/pgc-image/15271378111262c87a27090
[MySQL 45]: /images/20220519/1d30f94f87f04ff8a1c46dc06a9da755.png
[MySQL 46]: http://p9.pstatp.com/large/pgc-image/15271378843256763741a80

发表评论取消回复

表情：

评论列表（有 0 条评论，394人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql数据库之性能优化

一、架构设计层面优化。 MySQL是一个磁盘IO访问非常频繁的关系型数据库，在高并发和高性能的场景中，MySQL数据库必然会承受巨大的并发压力。在此时，我们优化的方式主要可以

迷南。/ 2023年10月11日 20:54/ 0 赞/ 48 阅读

相关 mysql性能优化之max,count优化

注：在执行SQL语句前加上explain可以查看MySQL的执行计划数据库：MySQL官方提供的sakila数据库 Max优化：例如：查询最后支付时间

ゝ一世哀愁。/ 2022年07月19日 00:19/ 0 赞/ 219 阅读

相关 Mysql性能优化、Mysql参数优化、Mysql配置优化

Mysql性能优化、Mysql参数优化、Mysql配置优化 Mysql参数优化对于不同的网站，及在线量等，以及机器硬件的配置都有关系，优化不可能一次性完成，需要不断的观察以及

秒速五厘米/ 2022年05月21日 05:00/ 0 赞/ 391 阅读

相关性能优化之MySQL优化

性能优化之MySQL优化(一) ![性能优化之MySQL优化（一）][MySQL] http://dev.mysql.com/doc/index-other.html

系统管理员/ 2022年05月19日 13:55/ 0 赞/ 395 阅读

相关 MySQL(三) —— MySQL性能优化之索引优化

MySQL索引优化最后，关于索引失效问题一、如何选择合适的列建立索引？ 1、在where从句、group by 从句、order by 从句、on 从句中出

分手后的思念是犯贱/ 2022年05月15日 16:35/ 0 赞/ 420 阅读

相关 MySQL(二) —— MySQL性能优化之 SQL语句优化

　　　　　　　　　　SQL语句优化 MySQL优化的目的　　1、避免出现页面访问错误：或由于数据库连接超时 timeout 产生页面5xx错误；或由于慢查询

小鱼儿/ 2022年05月15日 15:21/ 0 赞/ 522 阅读

相关 MySQL之查询性能优化

文章目录 MySQL是否在扫描额外的记录响应时间扫描的行数的返回的行数扫描的行数和访问类型重构查询的方式

╰半橙微兮°/ 2022年05月13日 14:12/ 0 赞/ 379 阅读

相关 MYSQL性能优化(索引优化)

SQL的执行过程： 1. 客户端发送一条查询给服务器； 2. 服务器通过权限检查之后,先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否

港控/mmm°/ 2022年03月21日 03:37/ 0 赞/ 454 阅读

相关 MySQL性能优化

一、参数优化 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0

待我称王封你为后i/ 2022年03月19日 05:58/ 0 赞/ 405 阅读

相关 mysql性能优化

1、为查询缓存优化你的查询大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候

男娘i/ 2021年09月27日 17:06/ 0 赞/ 646 阅读