MySQL常用命令与内部组件及SQL优化详情_Mysql_

1. 一些常用的 MySQL 命令

#连接MySQL mysql -h 127.0.0.1 -u UserName -p pwd -P 3306 #创建新用户 CREATE USER 'username'@'host' IDENTIFIED BY 'password'; #赋权限,%表示所有(host)： grant all privileges on *.* to 'username'@'%'; #修改密码 update user set password=password("123456") where user='root'; #查看当前用户的权限 show grants for root@"%"; #显示所有数据库 show databases; #打开数据库 use dbname； #查看库中有哪些表 show tables #显示表mysql数据库中user表的列信息） describe user #查看连接（包括用户、正在执行的操作、状态等） show processlist #刷新连接 flush privileges #关闭某连接 kill id #查询库中所有的表 select * from information_schema.tables where table_schema='zhebase'; #查询表信息（字段，字段类型，是否为空，编码，备注等） select * from information_schema.columns where table_schema='zhebase' and table_name='student_inndb'; #查看MySQL权限 Host列表示那个Ip可以连接，User表示用户，后面的字段是权限 select * from mysql.user; #查看全局服务器关闭非交互连接之前等待活动的秒数 show global variables like "wait_timeout"; #设置全局服务器关闭非交互连接之前等待活动的秒数（默认8小时不发送命令自动断连） set global wait_timeout=28800; 

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

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

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

#my.cnf配置文件中，一般将my.cnf参数 query_cache_type 设置成 DEMAND query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE 关键词时才缓存

2.MySQL的内部组件结构

 连接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独有

举例：

Select EMPLOYEE.Name , WELFARE.Bonus From EMPLOYEE , WELFARE  Where EMPLOYEE.Seniority > 5  And EMPLOYEE.Seniority = WELFARE.Seniority ; Select EMPLOYEE.Name , WELFARE.Bonus From EMPLOYEE , WELFARE  Where EMPLOYEE.Seniority > 5  And EMPLOYEE.Seniority = WELFARE.Seniority   And EMPLOYEE.Seniority > 5;

查询重写： 因为第一条将EMPLOYEE中Seniority > 5 的行与 WELFARE 中的所有行作外连接再来找 Seniority 相等的行，而第二条则是将 EMPLOYEE 中 Seniority > 5 的行和 WELFARE 中 Seniority > 5 的行作外连接再来找 Seniority 相等的行，第二条语句只有更少的行参与外连接，效率更高。写 SQL 时查询优化器自动重写。

4. SQL执行顺序

(7) SELECT (8) DISTINCT  (1) FROM  (3)  JOIN  (2) ON  (4) WHERE  (5) GROUP  (6) HAVING  (9) ORDER BY  (10) LIMIT 

5.MySQL数据类型选择

在设计表时，选择数据类型时一般先确定大的类型（数字，字符串，时间，二进制），然后再根据有无符号、取值范围、是否定长等确定具体的数据类型。在设计时，尽量使用更小的数据类型以达到更优的性能。并且在定义时尽量使用 NOT NULL，避免 NULL 值。

数值类型

首先了解：

• 1 byte = 8 bit (1字节等于8位，当需要符号时，符号占用1位)
• float 的指数位有8位，尾数位有23位，符号位 1 位，float 的指数范围，为 -127~+128，按补码的形式来划分。有效位数 7 位
• double 有效位数 15 位
• 对DECIMAL(M,D) ，如果 M>D，为 M+2 否则为 D+2 字节

建议：

• 如果整型数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
• 建议使用TINYINT代替ENUM、BITENUM、SET。
• 避免使用整数的显示宽度，不要用INT(10)类似的方法指定字段显示宽度，直接用 INT。使用显示宽度后会不足自动填充0，但对查询无影响，查询结果不会自动填充0。
• DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
• 建议使用整型类型来运算和存储实数。
• 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

日期和时间

 建议：

• 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 的时间戳保存为整数值，处理起来极其不方便。

字符串

建议

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

6.MySQL优化

MySQL优化分类

• 减少磁盘IO 全表扫描 临时表 日志、数据块

  提示： 本文由神整理自网络，如有侵权请联系本站删除！
  本站声明：
  1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
  2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！

  上一篇：Mysql中count(*)、count(1)、count(主键id)与count(字段)的区别_Mysql_

  下一篇：Mysql中使用count加条件统计的实现示例_Mysql_