本文整理自网络,侵删。
目录
- 1. 一些常用的 MySQL 命令
- 2.MySQL的内部组件结构
- MySQL优化器与执行计划
- SQL执行过程
- 词法分析器原理
- 查询优化器
- 4. SQL执行顺序
- 5.MySQL数据类型选择
- 数值类型
- 日期和时间
- 字符串
- 6.MySQL优化
- MySQL优化分类
- 优化方法
- SQL优化原则
- EXPLAIN 查看执行计
- processlist干预执行计划
- SELECT语句务必指明字段名称
- 合理使用in和exits
- 关于not in 和not exists
- order by排序字段和where条件要匹配(关于联合索引)
- 不建议使用%前缀模糊查询
- 关于范围查询
- 避免在where子句中对字段进行null值判断及!=和<>
- 关于OR
- 只需要一条数据的时候,使用limit 1
- 分段查询
- 避免在where子句中对字段进行表达式及函数操作
- 尽量使用 inner join,避免 left join
- IN包含的值不应过多
- 关于索引
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 <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_list> (10) LIMIT <limit_number>
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 字节
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 字节 | [27,27-1] | [0,28-1] | 小整数值 |
SMALLINT | 2 字节 | [215,215-1] | [0,216-1] | 大整数值 |
MEDIUMINT | 3 字节 | [223,223-1] | [0,224-1] | 大整数值 |
INT/INTEGER | 4 字节 | [231,231-1] | [0,232-1] | 大整数值 |
BIGINT | 8 字节 | [263,263-1] | [0,264-1] | 极大整数值 |
FLOAT | 4 字节 | 约-3.40E+38 ~ 3.40E+38 | 约0~3.40E+38 | 单精度浮点数值 |
DOUBLE | 8 字节 | 约1.7E-308~1.7E+308 | 约0~1.7E+308 | 双精度浮点数值 |
DECIMAL | DECIMAL(M,D) | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
建议:
- 如果整型数据没有负数,如ID号,建议指定为UNSIGNED无符号类型,容量可以扩大一倍。
- 建议使用TINYINT代替ENUM、BITENUM、SET。
- 避免使用整数的显示宽度,不要用INT(10)类似的方法指定字段显示宽度,直接用 INT。使用显示宽度后会不足自动填充0,但对查询无影响,查询结果不会自动填充0。
- DECIMAL最适合保存准确度要求高,而且用于计算的数据,比如价格。但是在使用DECIMAL类型的时候,注意长度设置。
- 建议使用整型类型来运算和存储实数。
- 整数通常是最佳的数据类型,因为它速度快,并且能使用AUTO_INCREMENT。
日期和时间
相关阅读 >>
mysql workbench怎么设置为中文?(仅菜单项汉化)
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处:木庄网络博客 » MySQL常用命令与内部组件及SQL优化详情
相关推荐
评论
管理员已关闭评论功能...