数据库运维（一）

一、分析命令

1、SQL语句分析：explain

1) id：对应的sql语句，设计union、子查询的SQL会有多个

2) possible_keys：可供优化器选择的索引列

3) key：该条SQL被优化器使用的索引列

4) key_len：连接SQL语句和where条件中的索引

5) explain partitions：对分区表操作的SQL语句提供附加信息

2、索引相关：show indexs

1)cardinalits(基数)：索引列不同数据的数量(相同数据只取一个)，创建索引可以参考索引基数/数据行数。

3、系统参数分析：show status、show variables

1) Show table status like ‘表名’：avg_row_length--平均每行数据大小(字节，以下均同)、data_length--全表共有多少字节、max_data_length--表最大字节容量、index_length--索引占用磁盘大小

2) show [ session | global ] variables：查看MySQL系统变量的当前参数，session级别的只会影响到本session，global是系统级别的。

3) 参数：

1. key_buffer_size：定义myisam索引的全局内存缓冲区。

2. Innodb_buffer_pool_size：定义innodb缓冲池的大小，它是存放数据与索引页的场所。对于Innodb很重要，不正确的分配会导致额外的IO开销。一般情况下，是RAM的80%。

3. query_cache_size：定义查询缓存的大小。它用来缓存经常执行的SELECT语句。

4. show global variables like ‘innodb_buffer%’：用来监控innodb缓冲池。

5. max_heap_table_size：定义一个MEMORY引擎表的最大容量。当某个表容量超过最大值时报错。

*Memory引擎将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。

6. tmp_table_size：定义一个内部基于内存的临时表的最大容量。它与max_heap_table_size参数有密切关联。

7. join_buffer_size：定义每个线程的内存缓冲区，当查询必须连接两个表的数据集并且不能使用索引时，用到这个缓冲区。建议默认值。

8. sort_buffer_size：定义每个线程用于对结果集排序的每个线程的缓冲区。

9. read_buffer_size：连续的表数据扫描会用到这个缓冲区。如果存在大量的连续扫描，推荐加大。

10. read_md_buffer_size：用来存储那些作为排序操作的结果被读取的数据。

11. slow_query_log： 慢查询日志的开关（1/0）。

12. slow_query_log_file：慢查询日志保存文件名及其位置。

13. long_query_time：如果query time超过了该值，则认为是较慢查询，并被记录下来。单位是秒。

14. max_allowed_packet：定义SQL查询结果集的最大值(字节)。

15. wait_timeout：服务器关闭非交互连接（连接空闲）之前等待活动的秒数(默认值28800秒)。建议修改为100。

16. interactive_timeout：服务器关闭交互式连接前等待活动的秒数(默认值28800秒)。建议修改为100。

二、系统优化

1)慢查询

2)连接数：max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）。

3)key_buffer_size：一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引(key_reads)，计算索引命中缓存的概率：key_buffer_read_hits =(1-Key_reads/Key_read_requests)*100%=(1-0.27)% =99.73%

4)进程使用情况：

设置thread_cache_size参数unix数据库，服务器就会有线程池来管理连接。

Thread_cache_hits = (1 - Threads_created / Connections) * 100%Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源。

Thread Cache 命中率>90% 比较合理，需要调整thread_cache_size变量。

5)查询缓存qcache：

1. Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

2. Qcache_total_blocks：缓存中块的数量。

3. Qcache_hits：每次查询在缓存中命中时就增大 。

4. Qcache_inserts：未命中然后插入缓存的次数。即没有命中的次数。

5. Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况） 。

6. Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

7. Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。

1.query_cache_limit：超过此大小的查询将不缓存

2.query_cache_min_res_unit：缓存块的最小大小

3.query_cache_size：查询缓存大小

4.qcache_free_memory：缓存中的空闲内存。

5.query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询

6.query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

7.query_cache_min_res_unit：默认是4KB，设置值大对大数据查询有好处，小数据查询，就容易造成内存碎片和浪费。

系统配置优化：

1.查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit。

2.查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

3. 查询缓存命中率 = Qcache_hits / (Qcache_hits+ Qcache_inserts) * 100%

4. 示例图中查询缓存碎片率 ＝ 13.23％，查询缓存利用率 ＝ 67.82％，查询缓存命中率 ＝ 74.88％

6)打开文件数open_files：比较合适的设置：Open_files / open_files_limit * 100% 5000，最好采用InnoDB引擎。

8)查看操作系统命令

1.top：实时显示process的状态

2.vmstat：vmstat命令是最常见的Linux/Unix监控工具，属于sysstat包。可以展现给定时间间隔的服务器的状态值,包括服务器的CPU使用率，内存使用，虚拟内存交换情况,IO读写情况。这个命令是我查看Linux/Unix最喜爱的命令，一个是Linux/Unix都支持，二是相比top，我可以看到整个机器的CPU,内存,IO的使用情况，而不是单单看到各个进程的CPU使用率和内存使用率(使用场景不一样)。

3.iostat：iostat是I/O statistics（输入/输出统计）的缩写，iostat工具将对系统的磁盘操作活动进行监视。它的特点是汇报磁盘活动统计情况，同时也会汇报出CPU使用情况。iostat也有一个弱点，就是它不能对某个进程进行深入分析，仅对系统的整体情况进行分析。

4.ps：用于显示当前进程的状态,类似于 windows 的任务管理器。

5.df：用于显示目前在 Linux 系统上的文件系统磁盘使用情况统计。

6.lsof：用于查看你进程打开的文件,打开文件的进程,进程打开的端口(TCP、UDP)。

7.netstat：用于显示网络状态。利用netstat 指令可让你得知整个 Linux 系统的网络情况。

（编辑：好传媒网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!