~MySQL Perfect~

MySQL优化

本质就是sql语句的优化 和 索引的优化

1.MySQL历史

image-20210912153904617

   以前是ISAM引擎,2000年升级为默认引擎为MyISAM(同时将MySQL开源,使得其拓展性增强),2003年出现InnoDB存储引擎(MySQL4.0)。
   MySQL5.0发布,成为里程碑的一步(支持更多商业类型的功能,不再局限于表的操作,而是增添了类似 视图、触发器这些对象)
   Sun收购MySQL、Oracle收购Sun,{大企业收购小企业,扫清竞争对手}...
   MySQL5.5将InnoDB封为默认存储引擎 {5年卧薪尝胆,终成C位}

2.MySQL分支

1.Percona Server-XtraDB :基于MySQL引擎开发出的一个新引擎,更好发挥服务器硬件上的性能
2.MariaDB  :Oracle把Sun公司收购后,MySQL创始人害怕自此MySQL闭源,因此开发出类似于InnoDB引擎的高度兼容MySQL的产品
3.InnoSQL  :网易

3.MySQL连接

3.1 通信类型

同步通信(应用操作数据库,线程阻塞,程序不会继续往下运行,等待数据库的return):JDBC连接多使用此种方式
异步通信(异步虽然可以避免阻塞,但是每个sql都会建立连接,看起来避免了数据混乱,实则给服务器带来巨大压力)

3.2 连接方式

短连接
  连接--》数据传输--》断开连接
长连接(将连接放到数据库连接池中,以便下一次连接时使用)
  连接--》数据传输--》保持连接--》数据传输--》保持连接--》...--》关闭连接

3.3 协议

TCP/IP
Unix Socket

3.4 在服务端查看连接信息

  • 在mysql中都是 单进程多线程的

  • 在一个连接来的时候,会创建一个线程,当断开连接后,并不会销毁此线程,而是将此线程缓存起来

    image-20210912163308732

查看mysql版本号:show VERSION()
查看服务端中连接数:show global status like 'Thread%'

4. 优化

4.1 优化Threads_created

image-20210912164842175

出现了Threads_created过大,代表了所创建过的线程过多,其本质就是缓存的线程过小--》Threads_cached问题

解决措施:
  改变thread_cache_size大小,使其变大
  1.先查看其默认值:show variables like 'thread_cache_size'
  2.改变大小,至少需要清楚其设置规则吧:
     短连接:设置大一些(因为本质是需要反复创建和销毁的)
     长连接:设置小一些(因为本质是被缓存的),50-100
  {根据物理内存设置
  		1G--》8
  		2G--》16
  		3G--》32
  		4G--》64
  }
  3.set global thread_cache_size=64  // 重启MySQL后失效

4.2 查看与优化 连接时间

1.交互式:使用客户端工具连接,例如:Navicat、SQLyog
	查看全局:show global variables like 'interactive_timeout'
	查看会话(局部):show session variables like 'interactive_timeout'
2.非交互式:使用JDBC连接、数据库连接池
	查看全局:show global variables like 'wait_timeout'
	查看会话(局部):show session variables like 'wait_timeout'
默认都是8小时(所展示出的单位是秒:28800s = 8h),8h后会将不活跃的线程 清除掉
优化
1.交互式
	全局:set global interactive_timeout = 600
	会话:set session interactive_timeout = 600
2.非交互式{更多的是对于此 优化}
	全局:set global wait_timeout = 600
	会话:set session wait_timeout = 600
	

4.3 查看与优化 最大连接数

show variables like 'max_connections'
命令修改:
set global max_connections=200
配置文件修改:
max_connections=100

4.4 优化SQL语句

常规调优思路:
	-查看和分析slow-log,分析出查询慢的语句
	-按照一定优先级,进行一一排查所有慢语句
	-分析top sql,进行explain调试,查询语句执行时间
	-调整索引和语句本身
MySQL日志:
	错误日志
		show variables like '%log_error%'
		修改错误日志路径:log_error = [路径]/[日志文件名].err
	二进制日志
	通用查询日志
	慢查询日志
SQL语句优化
	未完待续...
索引优化
	未完待续...

5.MySQL架构及内部模块

image-20210912185122550

1 连接器:用于 用户登录
2 查询缓存:查询之前是否有此sql,如有则直接返回结果;(缺点:对于经常更新的数据,缓存有效时间太短)  后来8.0时删掉了此功能
3 分析器: 说白了就是针对查询语句的拆分
	(1)词法分析:select、字段名、表名、查询条件
	(2)语法分析:判断sql是否符合语法,是否正确
	形成分析树,如下图

image-20210912190338311

4.优化器:它认为的最优方式去执行(有时并不是最优方案)
	多个索引的时候如何选用索引,多表查询的时候如何选择关联顺序
5.执行器:执行 执行方案,校验该用户有没有权限,有权限就调用引擎接口,返回接口执行结果

5.1 优化查询缓存

show variables like '%query_cache%'

image-20210912191955674

image-20210912192105065

  优化:
  query_cache_type=2  (On):表示开启缓存,每条语句都建立缓存;除非在sql语句中使用SQL_NO_CACHE禁用查询缓存   select sql_no_cache name from users
  query_cache_size=10M

ps:大小写不一样的sql语句不使用相同的缓存

查看是否sql语句已经被缓存
show status like 'Qcache%'
查询缓存失效情况:
	语句有不确定操作:now(),uuid(),date()
	没有关于表的查询:select 'a'
	表更改
posted @ 2021-09-12 20:32  ~快乐王子~  阅读(58)  评论(0编辑  收藏  举报