MySQL优化系列文章——SQL优化-索引问题-常用优化技巧，我可以和面试官多聊几句吗？
我可以和面试官多聊几句吗？只是想偷点技能过来。MySQL优化篇（基于MySQL8.0测试验证），上部分：优化SQL语句、数据库对象，MyISAM表锁和InnoDB锁问题。
MyISAM表锁和InnoDB锁问题会在第二篇发布：MySQL优化篇，我可以和面试官多聊几句吗？——MyISAM表锁和InnoDB锁问题（二）
你可以将这片博文，当成过度到MySQL8.0的参考资料。注意，经验是用来参考，不是拿来即用。如果你能看到并分享这篇文章，我很荣幸。如果有误导你的地方，我表示抱歉。
接着上一篇MySQL开发篇存储引擎的选择，上一篇用我现在眼光去看是稀烂的，每隔一段时间回顾自己的文章都感觉稀烂。此次带来的是MySQL优化篇，部分内容针对多版本进行说明。在对MySQL进行举例并使用到数据库表，大多数情况使用MySQL官方提供的sakila（模拟电影出租信息管理系统）和world数据库，类似于Oracle的scott用户。
如果没有进行特别说明，一般是基于MySQL8.0.28进行测试验证。官方文档非常具有参考意义。目前市面上针对MySQL8.0书籍还比较少，部分停留在5.6.x和5.7.x版本，但仍然具有借鉴意义。
文中会给出官方文档可以找到的参考内容，基本上在小标题末尾有提及并说明。辅助你快速定位出处，心里更有底气。如果想应对MySQL面试，我想这篇总结还是有一定的参考意义。需要有耐心看完，个人总结时参考书籍和MySQL8.0官方文档也很乏味，纯英文文档更令人头大。不懂的地方可以使用有道，结合实际测试进行理解。英语差，不应该是借口。
个人理解有限，难免出现错误偏差。所有测试，仅供参考。
如果感觉对你起到作用，有参考意义，想获取原markdown文件。
可以访问我的个人github仓库，定期上传md文件，空余时间会制作目录链接：
目录https://github.com/cnwangk/SQL-study/tree/master/md/SQL/MySQL
- MySQL优化篇（一）
- 正文
- 一、SQL优化
- 01 优化SQL语句流程
- 1 通过show status查询SQL执行频率
- 2 定位执行效率较低的SQL语句
- 3 使用explain分析执行效率低的SQL语句
- 4 show profile分析SQL
- 5 使用trace分析优化器如何选择执行计划
- 6 定位问题后采取相应优化方法
- 02 索引问题
- 1 索引分类
- 2 MySQL如何使用索引
- 3 查看索引使用情况
- 03 简单优化方法
- 3.1 定期分析表和检查表
- 3.2 定期优化表
- 04 常用SQL优化
- 4.1 批量（大量）插入数据
- 4.2 优化 INSERT、ORDER BY、GROUP BY 语句
- 4.3 优化嵌套查询、分页查询
- 4.4 优化 OR 条件
- 4.5 使用 SQL 提示
- 05 常用 SQL 技巧
- 5.1 使用正则表达式
- 5.2 RAND() 提取随机行
- 5.3 GROUP BY 与 WITH ROLLUP 子句
- 5.4 Bit GROUP Functions 做统计
- 5.5 数据库库名、表名大小写问题
- 5.6 使用外键注意事项
- 01 优化SQL语句流程
- 二、优化数据库对象
- 01 优化表数据类型
- 02 拆分表提高访问效率
- 03 逆规范
- 04 中间表提高统计查询效率
- 一、SQL优化
- 莫问收获，但问耕耘
MySQL优化篇（一）
MyISAM表锁和InnoDB锁问题会在第二篇：MySQL优化篇（二）进行发布，篇幅太长，不便一次性全部发完。
给出sakila-db数据库包含三个文件，便于大家获取与使用：
- sakila-schema.sql：数据库表结构；
- sakila-data.sql：数据库示例模拟数据；
- sakila.mwb：数据库物理模型，在MySQL workbench中可以打开查看。
https://downloads.mysql.com/docs/sakila-db.zip
world-db数据库，包含三张表：city、country、countrylanguage。
只是用于用于简单测试学习，建议使用world-db：
https://downloads.mysql.com/docs/world-db.zip
生产前：
应用开发初期数据量比较小，开发人员在编写SQL语句时更加注重功能的实现（优先让程序跑起来，有money赚）。
生产后：
业务体系逐渐扩张，随着生产数据量的急剧增长，部分SQL语句开始漏出疲态，暴露出性能问题（开始优化，赚更多的money）。
引发的思考：
部分有问题的SQL语句成了系统性能的瓶颈，此时需要对SQL语句进行优化。
演示环境：
- 操作系统：Windows10 and Linux for Centos7.5
- 使用工具：MySQL8.0自带字符命令行工具
- 数据库：MySQL8.0.28 and MariaDB10.5.6
正文
注意：在某些情况，你自己测试的结果可能与我演示有所不同，我省略了查询结果的部分参数。
本文侧重点在SQL优化流程以及MySQL锁问题（MyISAM和InnoDB存储引擎）。图片可能会挂，演示时尽量使用SQL查询语句返回结果进行示例。篇幅很长，因此使用markdown语法加了目录。
起初，也只是想看MySQL8.0.28有哪些变化，后面索性结合书籍和官方文档总结了一篇。花了将近两周，基本是每天完善一点，因为个人只有晚上和周末有时间总结并测试验证。如果有错别字，也请多多担待。如果你能看到并分享这篇文章，我很荣幸。如果有误导你的地方，我表示抱歉。
如果你是从MySQL5.6或者5.7版本过渡到MySQL8.0。学习之前，建议线看官方文档这一章节：1.3 What Is New MySQL8.0 。在做对比的时候，文档中带有Note标识是你应该注意的地方。比如下面这张截图：
与我之前一篇《MySQL8.0.28安装教程全程参考官方文档》是一样的想法，希望大家能认识到自学的重要性，以及阅读官方文档自我成长。结合有道和谷歌翻译以及自己的翻译进行理解，感觉翻译很别扭，可以对单个单词进行分析，结合自己的经验调整并符合阅读习惯。
参考文档：refman-8.0-en.pdf
参考书籍：
- 《深入浅出MySQL 第2版 数据库开发、优化与管理维护》，个人参考优化篇部分。
- 《MySQL技术内幕InnoDB存储引擎 第2版》，个人参考索引与锁章节描述。
一、SQL优化
01 优化SQL语句流程
登录到mysql字符命令界面：
mysql -uroot -p
登录时指定端口和主机地址方式：
mysql -h 192.168.245.147 -uroot -p -P 3307
使用？ show帮助命令查询show status用法，截取部分语法如下：
? show
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
1 通过show status查询SQL执行频率
如果不加参数，默认采用session级别，也可以加上global参数进行测试一下。
使用session与global参数区别：
-
session：当前连接统计的结果，默认为session级别；
-
global：上次数据库启动至今统计结果，需要手动那个指定global参数。
下面就列举示例进行说明，分别使用like去查询所有以及匹配CURD操作（select、insert、update、delete）：
查询当前session所有统计记录，如果直接在字符命令界面去查询，共有175条记录，大多数情况会采用工具去执行：
show status LIKE 'com_%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_commit | 0 |
| Com_rollback | 0 |
+-------------------------------------+-------+
...
175 rows in set (0.00 sec)
Com_xx部分参数作用说明：
- Com_xx：代表某某语句执行次数，一般我们关心的是CURD操作（select、insert、update、delete）。
- Com_select：执行select操作次数，每次累加1次。
- Com_insert：执行insert操作次数，对于批量执行插入的insert操作只累加1次。
- Com_update：执行update操作次数。
- Com_delete：执行delete操作次数。
以上这些参数对所有存储引擎表操作均会进行累计。但也有一些参数只针对InnoDB存储引擎，累加算法有些许不同。
查询innodb参数如下，列举部分：
show status LIKE 'innodb_rows%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
+---------------------------------------+--------------------------------------------------+
...
61 rows in set (0.00 sec)
- InnoDB_rows_read：执行select查询返回行数。
- InnoDB_rows_inserted：执行insert插入操作返回行数。
- InnoDB_rows_updated：执行update更新操作返回行数。
- InnoDB_rows_deleted：执行delete删除操作返回行数。
通过上面几个参数，可以轻松了解当前数据库应用是以插入更新为主还是查询操作为主，以及各种SQL大概执行比例是多少。
对于更新操作执行次数计数，无论是提交还是回滚都会进行累加。
对于事务型应用，可以通过Com_commit和Com_rollback了解事务提交与回滚情况。对回滚操作非常频繁的数据库，可能存在应用编写问题。
有几个参数便于用户了解数据库情况：
show status LIKE 'conn%';
show status LIKE 'upti%';
show status LIKE 'slow_q%';
- Connections：试图连接MySQL服务器次数。
- Uptime：服务器工作时间。
- Slow_queries：慢查询次数。
对优化SQL语句流程就介绍这么多，主要对关心的（CURD以及事务）各个参数熟练操作运用。
2 定位执行效率较低的SQL语句
可以通过两种方式定位执行效率较低SQL语句：
- 使用参数：--log-slow-queries [=file_name]，MySQL会将long_query_time的SQL语句日志写入文件；
- 使用参数
show processlist：查询MySQL线程状态、是否锁表。
慢查询日志在查询结束以后才记录，在应用反映执行效率问题时查询慢查询慢查询日志并不能定位问题。可以使用show processlist，查看当前MySQL在进行的线程：线程状态、是否锁表，实时查看SQL执行状态。
3 使用explain分析执行效率低的SQL语句
参考mysql8.0官方文档explain：
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
https://github.com/cnwangk/SQL-study