MySQL体系结构--------------MySQL性能优化

mysql性能优化

mysql体系结构 （由8个功能模块组成）：

管理工具： 安装服务软件后，提供的命令 mysqldump、mysqlbinlog、mysqladmin

连接池： 当接收到客户端的连接请求后，检查是否有资源响应客户端的连接请求。

SQL接口： 把sql传递给mysqld线程处理

分析器： 检查执行的sql命令是否有语法错误，和是否有访问权限。

优化器：优化执行的sql命令，已最节省资源的方式执行sql命令

查询缓存： 缓存曾经查找到的记录,缓存空间从物理内存划分出来的。

存储引擎： 是表的处理器，不同的存储引擎有不同的功能和数据存储方式。Myisam innodb

文件系统： 存储表中记录的磁盘

mysql服务处理查询请求过程

数据服务器接收到查询请求后，先从查询缓存里查找记录，若查询缓存里有查找记录，直接从缓存提取数据给客户端，反之到表去查询记录，把查到的记录先存放到查询缓存里在返回给客户端。

**

mysql调优—如何优化mysql数据库服务器

**

A、硬件配置低，导致处理速度慢
B 、网络带宽窄 网络测速软件
C 、提供服务软件的版本低，导致性能低下

1）查看服务运行时的参数配置

 mysql> show variables like "%innodb%";

常用参数：
---------------并发连接数量----------------

mysql> show  variables   like "%conn%";     max_connections//最大并发连接默认 151     

set  global   max_connections  =  数字；      //修改临时修改最大并发

show  global  status  like "Max_used_connections";    //查看有过的最大连接数

Max_used_connections/max_connections=0.85    //生产环境设置公式 (有多的最大连接数 ÷ 设置的并发连接) 
                                             -- 500/x=0.85  * 100%   = 85%

flush status    //刷新状态

-------------- 连接超时时间----------------

show   variables   like   "%timeout%";

connect_timeout  客户端与服务器建立连接时tcp三次握手超时是时间
wait_timeout  户端与服务器建立连接后，等待执行sql命令的超时时间。

----------可以重复使用的线程的数量、线程同时打开表的数量---------

show   variables   like   "%thread%";
--  thread_cache_size = 9    //可以重复使用的线程的数量  事先在内存里运行着的

show   variables   like   "%open%";    //所有线程同时打开表的数量
--  table_open_cache   =  2000

--------------与查询相关参数的设置 (字节)----------

show  variables  like  "key_buffer-size";
--  key_buffer-size   8388608（字节）   //设置索引缓存的大小【key_buffer-size】


转换下 echo $[8388608/1024]    1024字节=1K
81234K
转换下 echo $[8123/1024]       1024K=1M
8M

show  variables  like  "sort_buffer_size";   
//加快处理查询排序      select   *  from   t1  order  by 

show  variables  like  "read_buffer_size";  
//加快顺序读表里的记录的       select   *  from   t1; 

show  variables  like  "read_rnd_buffer_size";   
//加快查询分组的速度      select   *  from   t1  group  by

根据业务需求去调整各种查询优化 --- 提高缓存大小

------------------与查询缓存相关参数的设置---------------------

show   variables   like   "%cache%";            //所有与缓存相关的

show   variables   like   "query_cache%";       //query_cache_mini 默认是4K  会把缓存划分成4K大小的空间  

空间小：划分空间越小越节约空间，但是查询越慢 空间大：划分空间越大越浪费空间资源，但是查询速度越快

query_cache OFF      ---------------------  通常生产环境有独立的缓存服务器（中间件）     

query_cache_wlock_invalidate  OFF  //查询缓存写锁无效 关闭    仅对myisam存储引擎有效

当对myisam存储引擎的表执行查询时，若检查到有对表做写操作,不从查询缓存里查询数据返回给客户端，而是等写操作完成后，重新查询数据返回给客户端。

pc1   select    name  from t1  where name="bob";
cache --->  name=bob

pc2 select    name  from t1  where name="bob";
mysqld->  name= bob;

pc3  update  t1  set  name="jack" wehre  name="bob";          
脏读  -----查询的数据和实际的数据不一样   
幻读 --- 两次查询的结果不一样

-----------------------------查看查询缓存的统计信息------------------------------

show   global   status   like   "qcache%";     //全局状态里查看

Qcache_hits  10     //记录在查询缓存里查询到数据的次数     

Qcache_inserts  100     //记录在查询缓存里查找数据的次数           ----和上面的比较命中率 为什么比例小 因为没存上 查询缓存里空间小 增大空间

Qcache_lowmem_prunes     //清理查询缓存空间的次数，查询缓存里存满了会删除 删除一次 +1

Qcache_not_blocks  //查询不让存的次数就是超过的最大上限值，这个次数越大就要去调整 做大上限值  query_cache_limit 这个的值

mysql数据库服务日志类型： 在主配置文件里更改

错误日志 默认开启 记录服务在启动和运行过程中产生的错误信息log-

error=/var/log/mysqld.log

慢查询日志： 记录超时时间显示查询结果的sql命令 slow-quer-log 生成----主机名-slow.log

slow-quer-log-file=xx    指定存放目录名

long-query-time=xx   指定超时时间     默认10秒

log_queries_not_using_nidexes=1      //查看没有做过索引的查询命令记录

mysqldumpslow 主机名-slow.log > slow.txt //导出给程序员这些是超时的sql命令

网络架构有问题（有数据传输瓶颈）

**----------mysql优化总结--------------

主从结构并发高了
升级硬件
加大网络带宽
调整mysql服务运行参数
调整与查询相关的参数
启动慢查询日志
网络架构不合理