MySQL常用命令与内部组件及SQL优化详情 - 主机乐

开发当中我们大多数时候用的都是长连接，把连接放在 Pool 内进行管理，但是长连接有时候会导致 MySQL 占用内存飙升，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM)，从现象看就是 MySQL 异常重启了。怎么解决这类问题呢？ 1、定期断开长连接。 使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。 2、如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。 这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

为什么说MySQL查询缓存是否鸡肋？

使用场景极少，表一改动就需要重新维护
innodb，MyISAM 等引擎层有 buffer_pool 会自动缓存查询频繁的数据
可以使用第三方中间件代替
LRU淘汰策略

连接MySQL的过程：

1.完成经典的 TCP 握手建立连接
2.验证用户登录用户名密码
3.验证连接权限，是否运行该Ip连接（User表中的Host字段）
4.开辟专属 session 空间，连接后默认长连接，无操作8小时有效
5.将user表权限加入专属空间
6.每次执行命令在专属空间中查找是否有权限进行操作（权限修改后，如不重新连接，权限仍然不会改变，即使刷新连接也是如此)

MySQL优化器与执行计划

工作过程：

1.词法分析、语法分析、语义检查
2.预处理阶段(查询重写等)
3.查询优化阶段(可详细划分为逻辑优化、物理优化两部分)
4.查询优化器优化依据，来自于代价估算器估算结果(它会调用统计信息作为计算依据)
5.交由执行器执行

SQL执行过程

1.客户端提交一条语句
2.先在查询缓存（相当于一个Map，SQL语句是Key，结果集是Map）查看是否存在对应的缓存数据，如有则直接返回(一般有的可能性极小，因此一般建议关闭查询缓存)。MySQL 8.0开始取消了缓存器，5.0 默认关闭
3.交给解析器处理，解析器会将提交的语句生成一个解析树。
4.预处理器会处理解析树，形成新的解析树。这一阶段存在一些SQL改写的过程。
5.改写后的解析树提交给查询优化器。查询优化器生成执行计划。
6.执行计划交由执行引擎调用存储引擎接口，完成执行过程。这里要注意，MySQL的Server层和Engine层是分离的。
7.最终的结果由执行引擎返回给客户端，如果开启查询缓存的话，则会缓存

词法分析器原理

词法分析器分成6个主要步骤完成对sql语句的分析 1、词法分析 2、语法分析 3、语义分析 4、构造执行树 5、生成执行计划 6、计划的执行

查询优化器

负责生成 SQL 语句的有效执行计划的数据库组件
优化器是数据库的核心价值所在，它是数据库的“大脑”
优化SQL，某种意义上就是理解优化器的行为
优化的依据是执行成本（CBO）
优化器工作的前提是了解数据，工作的目的是解析SQL，生成执行计划
只要有WHERE的地方就会用到查询优化器，并非SELECT独有

举例：

建议：

MySQL 能存储的最小时间粒度为秒。
建议用 DATE 数据类型来保存日期。MySQL 中默认的日期格式是 yyyy-MM-dd。
用 MySQL 的内时间类型 DATE、TIME、DATETIME 来存储时间，而不是使用字符串。
当数据格式为 TIMESTAMP 和 DATETIME 时，可以用 CURRENT_TIMESTAMP 作为默认（MySQL5.6以后）， MySQL 会自动返回记录插入的确切时间。
TIMESTAMP 是 UTC 时间戳，与时区相关。
DATETIME 的存储格式是一个 YYYYMMDD HH:MM:SS 的整数，与时区无关。
除非有特殊需求，一般的公司建议使用 TIMESTAMP，比DATETIME更节约空间，大公司使用DATETIME，因为要用考虑 TIMESTAMP 将来的时间上限（1970-2037）问题。
不要使用 Unix 的时间戳保存为整数值，处理起来极其不方便。

字符串

类型	大小	用途
CHAR	0-255字节	定长字符串，char(n)当插入的字符串实际长度不足n时，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。
VARCHAR	0-65535 字节	变长字符串，varchar(n)中的n代表最大列长度，插入的字符串实际长度不足n时不会补充空格
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215字节	中等长度文本数据
LONGBLOB	0-4 294967295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294967295字节	极大文本数据

建议

字符串的长度相差较大用 VARCHAR；字符串短，且所有值都接近一个长度用 CHAR。
CHAR 和 VARCHAR 适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用 VARCHAR 类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
尽量少用 BLOB 和 TEXT，如果实在要用可以考虑将 BLOB 和 TEXT 字段单独存一张表，用 id 关联。
BLOB 系列存储二进制字符串，与字符集无关。TEXT 系列存储非二进制字符串，与字符集相关。
BLOB 和 TEXT 都不能有默认值。

6.MySQL优化

MySQL优化分类

减少磁盘IO 全表扫描临时表日志、数据块 fsync
减少网络带宽 返回数据过多交互次数过多
降低CPU消耗 排序分组：order by, group by 聚合函数：max,min,count,sum.. 逻辑读

优化方法

创建索引减少扫描量
调整索引减少计算量
索引覆盖（减少不必访问的列，避免回表查询）
SQL改写
干预执行计划

SQL优化原则

减少访问量： 数据存取是数据库系统最核心功能，所以 IO 是数据库系统中最容易出现性能瓶颈，减少 SQL 访问 IO 量是 SQL 优化的第一步；数据块的逻辑读也是产生CPU开销的因素之一。

减少访问量的方法：创建合适的索引、减少不必访问的列、使用索引覆盖、语句改写。

减少计算操作： 计算操作进行优化也是SQL优化的重要方向。SQL 中排序、分组、多表连接操作等计算操作都是十分消耗 CPU 的。

减少 SQL 计算操作的方法：排序列加入索引、适当的列冗余、SQL 拆分、计算功能拆分。

EXPLAIN 查看执行计

type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

1. system：表只有一行记录，const类型的特例，基本不会出现，可以忽略
2. const：通过索引一次就查询出来了，const用于比较primary key或者unique索引。只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引
6. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
7. all：Full Table Scan，遍历全表以找到匹配的行

key列，使用到的索引名。如果没有选择索引，值是NULL。 key_len列，索引长度。 rows列，扫描行数。该值是预估值。 extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

processlist干预执行计划

show [full] processlist
information_schema.processlist copy to tmp table： 出现在某些alter table语句的copy table操作 Copying to tmp table on disk： 由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存 converting HEAP to MyISAM： 线程正在转换内部MEMORY临时表到磁盘MyISAM临时表 Creating sort index： 正在使用内部临时表处理select查询 Sorting index： 磁盘排序操作的一个过程 Sending data ： 正在处理SELECT查询的记录，同时正在把结果发送给客户端 Waiting for table metadata lock: 等待元数据锁

SELECT语句务必指明字段名称

SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）直接使用select字段名称还增加了使用覆盖索引的可能性

如果排序字段没有用到索引，就尽量少排序
分页时要选择合理的方式

不建议使用%前缀模糊查询

使用like '%name%'或者like '%name'会导致索引失效而导致全表扫描。但使用like 'name%'不会。

解决方法：

– 1.使用全文索引
– 2.使用Elasticsearch等搜索工具（不怎么修改的字段才建议使用，实际是倒排索引）

注意： 1.全文索引的存储引擎一定是Myisam,InnoDB没有全文索引 2.全文索引对中文不太友好

//创建全文索引
ALTER TABLE cust ADD FULLTEXT INDEX idx_cust_address (‘cust_address’);
//使用全文索引
select name from cust where match(cust_address) against(‘湖南’);

倒排索引是一种索引数据结构：从文本数据内容中提取出不重复的单词进行分词，每1个单词对应1个ID对单词进行区分，还对应1个该单词在那些文档中出现的列表把这些信息组建成索引。倒排索引还记录了该单词在文档中出现位置、频率（次数/TF）用于快速定位文档和对搜素结果进行排序。