`

MYSQL性能调优与架构设计书读书笔记1

阅读更多
《MYSQL性能调优与架构设计书》,是阿里巴巴著名MYSQL DBA简朝阳的大作,最近在读,
觉得十分好,笔记之:

mysql读书笔记1


1 mysqladmin -u xxxx -p密码  -h localhost ping
  用ping命令检查mysqld状态是否正常

2 获取当前mysql的状态值

   mysqladmin -u xxxx -p密码  -h localhost status
3 获取数据库连接线程信息
   mysqladmin -u xxxx -p密码  -h localhost processlist

4 innodb的物理结构
   一种是共享表空间,也就是所有数据,索引数据,各种数据及UNDO都放在同一个表空间(一个
或者多个数据文件),通过innodb_data_file_path来指定,增加数据文件要停机或重启动;
另外一个是独享表空间,每个表的数据和索引放在一个单独的ibd文件中,

5 备份MYISAM的一种方法,先把表加锁的方法:
   flush tables with read lock;
cp -R test /tmp/backup/test
unlock tables;

6 MYSQL表的权限级别有5个:
   global level:全局权限,这个是最高级别的了,对数据库的所有表和字段都有权力管理,会覆盖其他4个的权限,

典型的如:
   grant select,update,delete on *.* to '111'@'11221";

  database level:即比如指定 database.*的这样的授权了
 
  table level:指定某个表的权限,比如 grant index on test.t1 to ........
  column level:只能操作某些列,比如  grant select(id,value) on test.t2 to ......
  routine level:只有excute和alter routine两种,主要是管理procedure和function.

7 MYSQL的锁:
  有三类,行级(MYISAM),表级(INNODB),页级(BERKEYLEYDB)
8 尽量避免复杂的JOIN语句和子查询,因为越复杂的join语句,锁定的资源也就越多,阻塞其他线程也越多。

9 explain中出现的参数全解析:
   MySQL Explain 功能中给我们展示的各种信息的解释:
◆ ID:Query Optimizer 所选定的执行计划中查询的序列号;
◆ Select_type:所使用的查询类型,主要有以下这几种查询类型
◇ DEPENDENT SUBQUERY:子查询中内层的第一个SELECT,依赖于外部查询的结果集;
◇ DEPENDENT UNION:子查询中的UNION,且为UNION 中从第二个SELECT 开始的后面所有
SELECT,同样依赖于外部查询的结果集;
◇ PRIMARY:子查询中的最外层查询,注意并不是主键查询;
◇ SIMPLE:除子查询或者UNION 之外的其他查询;
◇ SUBQUERY:子查询内层查询的第一个SELECT,结果不依赖于外部查询结果集;
◇ UNCACHEABLE SUBQUERY:结果集无法缓存的子查询;
◇ UNION:UNION 语句中第二个SELECT 开始的后面所有SELECT,第一个SELECT 为PRIMARY
◇ UNION RESULT:UNION 中的合并结果;
◆ Table:显示这一步所访问的数据库中的表的名称;
◆ Type:告诉我们对表所使用的访问方式,主要包含如下集中类型;
◇ all:全表扫描
◇ const:读常量,且最多只会有一条记录匹配,由于是常量,所以实际上只需要读一次;
◇ eq_ref:最多只会有一条匹配结果,一般是通过主键或者唯一键索引来访问;
◇ fulltext:
◇ index:全索引扫描;
◇ index_merge:查询中同时使用两个(或更多)索引,然后对索引结果进行merge 之后再读
取表数据;
◇ index_subquery:子查询中的返回结果字段组合是一个索引(或索引组合),但不是一个
主键或者唯一索引;
◇ rang:索引范围扫描;
◇ ref:Join 语句中被驱动表索引引用查询;
◇ ref_or_null:与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询;
◇ system:系统表,表中只有一行数据;
◇ unique_subquery:子查询中的返回结果字段组合是主键或者唯一约束;

◆ Possible_keys:该查询可以利用的索引. 如果没有任何索引可以使用,就会显示成null,这一
项内容对于优化时候索引的调整非常重要;
◆ Key:MySQL Query Optimizer 从possible_keys 中所选择使用的索引;
◆ Key_len:被选中使用索引的索引键长度;
◆ Ref:列出是通过常量(const),还是某个表的某个字段(如果是join)来过滤(通过key)
的;
◆ Rows:MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数;
◆ Extra:查询中每一步实现的额外细节信息,主要可能会是以下内容:
◇ Distinct:查找distinct 值,所以当mysql 找到了第一条匹配的结果后,将停止该值的查
询而转为后面其他值的查询;
◇ Full scan on NULL key:子查询中的一种优化方式,主要在遇到无法通过索引访问null
值的使用使用;
◇ Impossible WHERE noticed after reading const tables:MySQL Query Optimizer 通过
收集到的统计信息判断出不可能存在结果;
◇ No tables:Query 语句中使用FROM DUAL 或者不包含任何FROM 子句;
◇ Not exists:在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而
使用的优化方法,可以部分减少数据访问次数;
◇ Range checked for each record (index map: N):通过MySQL 官方手册的描述,当
MySQL Query Optimizer 没有发现好的可以使用的索引的时候,如果发现如果来自前面的
表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL 检查是否可以使
用range 或index_merge 访问方法来索取行。
◇ Select tables optimized away:当我们使用某些聚合函数来访问存在索引的某个字段的
时候,MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查
询。当然,前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX()的时
候;
◇ Using filesort:当我们的Query 中包含ORDER BY 操作,而且无法利用索引完成排序操
作的时候,MySQL Query Optimizer 不得不选择相应的排序算法来实现。
◇ Using index:所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据;
◇ Using index for group-by:数据访问和Using index 一样,所需数据只需要读取索引即
可,而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候,如果分组字段也在索引
中,Extra 中的信息就会是Using index for group-by;
◇ Using temporary:当MySQL 在某些操作中必须使用临时表的时候,在Extra 信息中就会
出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。
◇ Using where:如果我们不是读取表的所有数据,或者不是仅仅通过索引就可以获取所有需
要的数据,则会出现Using where 信息;
◇ Using where with pushed condition:这是一个仅仅在NDBCluster 存储引擎中才会出现
的信息,而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数
为engine_condition_pushdown 。
  

10 索引的三个好的实践:
  A 频繁作为查询条件的字段应该做索引
  B 当唯一性太差的字段不应该去做索引
  C 更新频繁的字段不适合做索引
11 MYSQL日志设置优化
   MYSQL中,对性能产生影响大的是binlog二进制日志了。

  show variables like '%binlog%';
   “sync_binlog”:这个参数是对于MySQL 系统来说是至关重要的,他不仅影响到Binlog 对MySQL 所
带来的性能损耗,而且还影响到MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如
下:
● sync_binlog=0,当事务提交之后,MySQL 不做fsync 之类的磁盘同步指令刷新binlog_cache 中
的信息到磁盘,而让Filesystem 自行决定什么时候来做同步,或者cache 满了之后才同步到磁
盘。
● sync_binlog=n,当每进行n 次事务提交之后,MySQL 将进行一次fsync 之类的磁盘同步指令来
将binlog_cache 中的数据强制写入磁盘。
在MySQL 中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性
能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache 中的所有binlog 信息都会被
丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1 的时候,即使系统
Crash,也最多丢失binlog_cache 中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验
和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0 和设置为1 的系统写入性能差
距可能高达5 倍甚至更多。

  MySQL 中Binlog 的产生量是没办法改变的,只要我们的Query 改变了数据库中的数据,那么就必须
将该Query 所对应的Event 记录到Binlog 中。那我们是不是就没有办法优化复制了呢?当然不是,在
MySQL 复制环境中,实际上是是有8 个参数可以让我们控制需要复制或者需要忽略而不进行复制的DB 或
者Table 的,分别为:
● Binlog_Do_DB:设定哪些数据库(Schema)需要记录Binlog;
● Binlog_Ignore_DB:设定哪些数据库(Schema)不要记录Binlog;
● Replicate_Do_DB:设定需要复制的数据库(Schema),多个DB 用逗号(“,”)分隔;
● Replicate_Ignore_DB:设定可以忽略的数据库(Schema);
● Replicate_Do_Table:设定需要复制的Table;
● Replicate_Ignore_Table:设定可以忽略的Table;
● Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以带通配符来进行设置;
● Replicate_Wild_Ignore_Table:功能同Replicate_Ignore_Table,可带通配符设置;

12 慢查询日志的设置:
   查看是否打开;
show variables like 'log_slow%';
查看时间设置:
  show variables like 'long_query%';

13 query cache相关
Query Cache 实现原理实际上并不是特别的复杂,简单的来说就是将客户端请求的Query
语句(当然仅限于SELECT 类型的Query)通过一定的hash 算法进行一个计算而得到一个hash 值,存放
在一个hash 桶中。同时将该Query 的结果集(Result Set)也存放在一个内存Cache 中的。存放Query
hash 值的链表中的每一个hash 值所在的节点中同时还存放了该Query 所对应的Result Set 的Cache 所
在的内存地址,以及该Query 所涉及到的所有Table 的标识等其他一些相关信息。系统接受到任何一个
SELECT 类型的Query 的时候,首先计算出其hash 值,然后通过该hash 值到Query Cache 中去匹配,如
果找到了完全相同的Query,则直接将之前所Cache 的Result Set 返回给客户端而完全不需要进行后面
的任何步骤即可完成这次请求。而后端的任何一个表的任何一条数据发生变化之后,也会通知Query
Cache,需要将所有与该Table 有关的Query 的Cache 全部失效,并释放出之前占用的内存地址,以便后
面其他的Query 能够使用。
   所以说,querycachae对于频繁变化的表和数据其实是作用不大的,而且要耗费hash计算。
   MySQL 中针对Query Cache 有两个专
用的SQL Hint(提示):SQL_NO_CACHE 和SQL_CACHE,分别代表强制不使用Query Cache 和强制使用
Query Cache  

  show variables like '%query_cache%';
  “have_query_cache”:该MySQL 是否支持Query Cache;
● “query_cache_limit”:Query Cache 存放的单条Query 最大Result Set ,默认1M;
● “query_cache_min_res_unit”:Query Cache 每个Result Set 存放的最小内存大小,默认
4k;
● “query_cache_size”:系统中用于Query Cache 内存的大小;
● “query_cache_type”:系统是否打开了Query Cache 功能;
● “query_cache_wlock_invalidate”:针对于MyISAM 存储引擎,设置当有WRITE LOCK 在某个
Table 上面的时候,读请求是要等待WRITE LOCK 释放资源之后再查询还是允许直接从Query
Cache 中读取结果,默认为FALSE(可以直接从Query Cache 中取得结果)。
    如果我们要了解Query Cache 的使用情况,则可以通过Query Cache 相关的状态变量来获取,如通过
如下命令:
mysql> show status like 'Qcache%';
“Qcache_free_blocks”:Query Cache 中目前还有多少剩余的blocks。如果该值显示较大,
则说明Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理()。
● “Qcache_free_memory”:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准
确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是过多了;
● “Qcache_hits”:多少次命中。通过这个参数我们可以查看到Query Cache 的基本效果;
● “Qcache_inserts”:多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两
个参数我们就可以算出Query Cache 的命中率了:
Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts );
● “Qcache_lowmem_prunes”:多少条Query 因为内存不足而被清除出Query Cache。通过
“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系
统中Query Cache 的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query 被换

● “Qcache_not_cached”:因为query_cache_type 的设置或者不能被cache 的Query 的数量;
● “Qcache_queries_in_cache”:当前Query Cache 中cache 的Query 数量;
● “Qcache_total_blocks”:当前Query Cache 中的block 数量;

14 网络设置参数
  max_conecctions:整个MySQL 允许的最大连接数;
这个参数主要影响的是整个MySQL 应用的并发处理能力,当系统中实际需要的连接量大于
max_conecctions 的情况下,由于MySQL 的设置限制,那么应用中必然会产生连接请求的等待,
从而限制了相应的并发量。所以一般来说,只要MySQL 主机性能允许,都是将该参数设置的尽
可能大一点。一般来说500 到800 左右是一个比较合适的参考值
● max_user_connections:每个用户允许的最大连接数;
上面的参数是限制了整个MySQL 的连接数,而max_user_connections 则是针对于单个用户的连
接限制。在一般情况下我们可能都较少使用这个限制,只有在一些专门提供MySQL 数据存储服
务,或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外,其他方面和
max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数,对于普通的应用来
说,完全没有做太多的限制,可以尽量放开一些。
● net_buffer_length:网络包传输中,传输消息之前的net buffer 初始化大小;
这个参数主要可能影响的是网络传输的效率,由于该参数所设置的只是消息缓冲区的初始化大
小,所以造成的影响主要是当我们的每次消息都很大的时候MySQL 总是需要多次申请扩展该缓
冲区大小。系统默认大小为16KB,一般来说可以满足大多数场景,当然如果我们的查询都是非
常小,每次网络传输量都很少,而且系统内存又比较紧缺的情况下,也可以适当将该值降低到
8KB。
● max_allowed_packet:在网络传输中,一次传消息输量的最大值;
这个参数与net_buffer_length 相对应,只不过是net buffer 的最大值。当我们的消息传输量
大于net_buffer_length 的设置时,MySQL 会自动增大net buffer 的大小,直到缓冲区大小达
到max_allowed_packet 所设置的值。系统默认值为1MB,最大值是1GB,必须设定为1024 的倍
数,单位为字节。
     back_log:在MySQL 的连接请求等待队列中允许存放的最大连接请求数。
连接请求等待队列,实际上是指当某一时刻客户端的连接请求数量过大的时候,MySQL 主线程没
办法及时给每一个新的连接请求分配(或者创建)连接线程的时候,还没有分配到连接线程的
所有请求将存放在一个等待队列中,这个队列就是MySQL 的连接请求队列。当我们的系统存在
瞬时的大量连接请求的时候,则应该注意back_log 参数的设置。系统默认值为50,最大可以设
置为65535。当我们增大back_log 的设置的时候,同时还需要主义OS 级别对网络监听队列的限
制,因为如果OS 的网络监听设置小于MySQL 的back_log 设置的时候,我们加大“back_log”设
置是没有意义的。


15 在MySQL 中,为了尽可提高客户端请求创建连接这个过程的性能,实现了一个Thread Cache 池,将
空闲的连接线程存放在其中,而不是完成请求后就销毁。这样,当有新的连接请求的时候,MySQL 首先会
检查Thread Cache 池中是否存在空闲连接线程,如果存在则取出来直接使用,如果没有空闲连接线程,
才创建新的连接线程。在MySQL 中与连接线程相关的系统参数及状态变量说明如下:
  我们现看看连接线程相关的系统变量的设置值:
mysql> show variables like 'thread%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| thread_cache_size | 64 |
| thread_stack | 196608 |
+-------------------+--------+
再来看一下系统被连接的次数以及当前系统中连接线程的状态值:
mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 127 |
+---------------+-------+
mysql> show status like '%thread%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 4 |
| Threads_connected | 7 |
| Threads_created | 11 |
| Threads_running | 1 |
+------------------------+-------+
通过上面的命令,我们可以看出,系统设置了Thread Cache 池最多将缓存32 个连接线程,每个连接
线程创建之初,系统分配192KB 的内存堆栈空给他。系统启动到现在共接收到客户端的连接127 次,共创
建了11 个连接线程,但前有7 个连接线程处于和客户端连接的状态,而7 个连接状态的线程中只有一个
是active 状态,也就是说只有一个正在处理客户端提交的俄请求。而在Thread Cache 池中当共Cache 了
4 个连接线程。
   通过系统设置和当前状态的分析,我们可以发现,thread_cache_size 的设置已经足够了,甚至还远
大于系统的需要。所以我们可以适当减少thread_cache_size 的设置,比如设置为8 或者16。根据
Connections 和Threads_created 这两个系统状态值,我们还可以计算出系统新建连接连接的Thread
Cache 命中率,也就是通过Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率,如
下:
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%
我们可以通过上面的这个运算公式计算一下上面环境中的Thread Cache 命中率:Thread_Cache_Hit
= (127 - 12) / 127 * 100% = 90.55%
一般来说,当系统稳定运行一段时间之后,我们的Thread Cache 命中率应该保持在90%左右甚至更
高的比率才算正常。


16 SORT BUFFER和JOIN BUFFER
   show variables like '%buffer%';
  join_buffer_size :当我们的Join 是ALL , index ,rang 或者index_merge 的时候使用的
Buffer;
实际上这种Join 被称为Full Join。实际上参与Join 的每一个表都需要一个Join Buffer,所以在
Join 出现的时候,至少是两个。Join Buffer 的设置在MySQL 5.1.23 版本之前最大为4GB,但是从
5.1.23 版本开始,在除了Windows 之外的64 位的平台上可以超出4BG 的限制。系统默认是128KB。
● sort_buffer_size:系统中对数据进行排序的时候使用的Buffer;
Sort Buffer 同样是针对单个Thread 的,所以当多个Thread 同时进行排序的时候,系统中就会出现
多个Sort Buffer。一般我们可以通过增大Sort Buffer 的大小来提高ORDER BY 或者是GROUP BY
的处理性能。系统默认大小为2MB,最大限制和Join Buffer 一样,在MySQL 5.1.23 版本之前最大
为4GB,从5.1.23 版本开始,在除了Windows 之外的64 位的平台上可以超出4GB 的限制。
如果应用系统中很少有Join 语句出现,则可以不用太在乎join_buffer_size 参数的大小设置,但是
如果Join 语句不是很少的话,个人建议可以适当增大join_buffer_size 的设置到1MB 左右,如果内存充
足甚至可以设置为2MB。对于sort_buffer_size 参数来说,一般设置为2MB 到4MB 之间可以满足大多数
应用的需求。当然,如果应用系统中的排序都比较大,内存充足且并发量不是特别的大的时候,也可以
继续增大sort_buffer_size 的设置。在这两个Buffer 设置的时候,最需要注意的就是不要忘记是每个
Thread 都会创建自己独立的Buffer,而不是整个系统共享的Buffer,不要因为设置过大而造成系统内存
不足。
10.5 小
3
4
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics