实验5 数据库性能监视与优化实验

1.实验目的

理解数据库性能概念，练习数据库性能监视命令方法，能够对数据库性能进行优化。

2.实验内容

【实验5-1】使用SHOW语句查询设备吞吐量Questions、Com_Select、Com_insert、Com_update、Com_delete几个指标值。

【实验5-2】访问Performance_schema中的events_statements_summary_by_digest表，获取有关的延迟、错误和查询量信息的性能指标。

【实验5-3】使用SHOW语句查询连接检查指标Threads_connected、Threads_running、Connection_errors_internal、Aborted_connects和Connection_errors_max_connections。

【实验5-4】使用SHOW语句查询Innodb缓冲区指标Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads。

【实验5-5】使用SHOW语句获取与查询缓冲相关的指标：Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qcache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、Qcache_queries_in_cache、Qcache_total_blocks。

【实验5-6】使用SHOW语句获取关于临时表的指标Created_tmp_disk_tables、Created_tmp_files、Created_tmp_tables。

【实验5-7】使用SHOW语句获取访问表的数量指标Open_tables和Opened_tables。

【实验5-8】使用SHOW PROCESSLIST命令查询用户正在运行的线程信息协助进行故障诊断。

【实验5-9】调出慢查询日志并利用mysqldumpslow来进行日志分析。

【实验5-10】使用EXPLAIN EXTENDED命令查看带有UNION子句的SELECT的执行计划。

【实验5-11】使用EXPLAIN EXTENDED命令查看如下语句的执行计划：

SELECT * FROM t1 WHERE a1<10 AND (

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2));

【实验5-12】使用EXPLAIN EXTENDED命令查看如下语句的执行计划：

SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10) v_t2

　　WHERE t1.a1<10 AND v_t2.a2<20;

【实验5-13】创建一个表，并在适当字段上创建索引，对比在大数据量情形下使用索引与不使用索引的性能。

【实验5-14】使用PROCEDURE ANALYSE检查表列。

【实验5-15】使用Inet_ATON将IP地址192.128.1.1转换为数字，再将21307033转换为IP地址。

【实验5-16】进行简单的关联查询代替子查询的重写操作，并验证其正确性和执行效率的变化。

【实验5-17】查询SQL的最大连接数并修改其至合适的数值。

3.实验要求

（1）所有操作均在命令行或者MySQL Workbench中完成。

（2）将操作过程以屏幕抓图的方式复制，形成实验文档，并对照本章内容写出分析报告。

（3）将操作所使用的命令对应的参数、参数含义、返回的内容、返回内容的含义整理到分析报告中一同给出。

数据库中的两个重要对象是表和索引，在6.3节的查询性能优化中为了提高查询性能，讲述了很多关于索引的应用。从本质来讲，良好的逻辑设计和物理设计（也就是表的设计）才是高性能的基石，作为数据库中的基础对象，表的设计对性能的影响也很重要，比如反范式设计方法会提升某些查询的速度，但同时也可能使得另一些查询变得很慢，应该根据系统具体执行的任务，以及在应用中承担的角色，对数据库进行整体的设计和优化，这需要权衡各种因素的利弊。本节将讨论关于表的优化。

表需要根据应用来判断使用何种数据类型。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪费存储也浪费内存。我们可以使用PROCEDURE ANALYSE()对当前已有应用的表类型进行判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。PROCEDURE ANALYSE()的语法如下：

SELECT...FROM...WHERE...PROCEDURE ANALYSE([max_elements，[max_memory]])

max_elements（默认值256）为analyze查找每一列不同值时所需关注的最大不同值的数量，analyze还用这个值来检查优化的数据类型是否为ENUM，如果该列的不同值的数量超过了max_elements值，ENUM就不作为建议优化的数据类型。

max_memory（默认值8192）为analyze查找每列所有不同值时可能分配的最大的内存数量。如果没有这样的，输出信息可能很长，ENUM 定义通常很难阅读。在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。

例

SELECT * FROM tbl_name PROCEDURE ANALYSE();

上述语句表明输出的每列信息都会对数据表中的列的数据类型提出优化建议。

SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

该语句告诉PROCEDURE ANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。

下面举例说明如何使用PROCEDURE ANALYS()函数帮助我们优化数据类型：

例

mysql> DESC user_account;

+——————–+————+——+————–+———+—————-+

| Field | Type | Null | Key | Default | Extra |

+——————–+————+——+————–+———+—————-+

| USERID | int(10) unsigned | NO | PRI | NULL | auto_increment |

| USERNAME | varchar(10) | NO | | NULL | |

| PASSSWORD | varchar(30) | NO | | NULL | |

| GROUPNAME | varchar(10) | YES | | NULL | |

+——————–+————+——+————–+———+—————-+

4 rows in set (0.00 sec)

上面是关于user_account表结构的查看，下面通过PROCEDURE ANALYS()函数分析：

mysql> select * from user_account PROCEDURE ANALYSE(1)\\G;

***************** 1. row *****************

Field_name: ibatis.user_account.USERID

Min_value: 1

Max_value: 103

Min_length: 1

Max_length: 3

Empties_or_zeros: 0

Nulls: 0

Avg_value_or_avg_length: 51.7500

Std: 50.2562

Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL

***************** 2. row *****************

Field_name: ibatis.user_account.USERNAME

Min_value: dfsa

Max_value: LMEADORS

从第一行输出我们可以看到analyze分析ibatis.user_account.USERID列最小值为1，最大值为103，最小长度为1，最大长度为3，并给出了该字段的优化建议：将该字段的数据类型改成TINYINT(3) UNSIGNED NOT NULL。

1.数据类型选择的总体原则

2.数据类型的使用建议

3.4.1节中讲述了数据库表支持的数据类型，我们在为列选择数据类型的时候，不仅要考虑存储类型大小，还要考虑MySQL如何对它们进行计算和比较。例如，MySQL在内部把ENUM和SET类型保存为整数，但是在比较的时候把它们转换为字符串。我们要在相关表中使用同样的类型，类型之间要精确匹配，包括诸如UNSIGNED这样的属性。混合不同的数据类型会导致性能问题，即使没有性能问题，隐式的类型转换也能导致难以察觉的错误。选择最小的数据类型要考虑将来留出的增长空间。例如，中国的省份，我们知道不会有成千上万个，因此不必用INT，用TINYINT就足够了，它比INT小3个字节。整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。要尽可能避免将字符串作为列的数据类型，因为它们占用了很多空间，并且通常比整数类型要慢。MyISAM默认情况下为字符串使用了压缩索引，这使得查找更为缓慢。

（1）关于数字类型，非万不得已不要使用DOUBLE，这不仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分TINYINT/INT/BIGINT的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果是数据量较小的数据库，也可以不用严格区分三种整数类型。

（2）关于字符型，非万不得已不要使用TEXT数据类型，其处理方式决定了其性能要低于CHAR类型或者是VARCHAR类型的处理。对于定长字段，建议使用CHAR类型，而不定长字段尽量使用 VARCHAR类型，且仅仅设定适当的最大长度，而不是非常随意地给一个最大长度的限定，因为不同的长度范围，MySQL也会有不一样的存储处理。

（3）关于时间类型，尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为其存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。

（4）对于状态字段，可以尝试使用ENUM来存放，因为可以极大地减小存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢？可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。

（5）关于大对象类型，强烈反对在数据库中存放BLOB类型数据，虽然数据库提供了这样的功能，但这不是其所擅长的。

（6）关联查询时，两个表中关联的字段最好是同一个数据类型。如果没有负数，最好是设置UNDIGNED，这样既避免出现负数的BUG，又使得存储的数据扩大一倍。ENUM和SET类型适合存储固定信息，如有序的状态、产品类型、性别。对于完全随机的字符串【如MD5()、SHA1()、UUID()】，在插入值时会随机写入索引的不同位置，所以插入速度慢，还有可能会导致页和磁盘随机访问，在查询时也会因为逻辑上相邻的行分布在磁盘和同存的不同的位置而变得很慢。随机值会弱化查询语句的缓存作用，因为它使得缓存赖以工作的访问局部性原理失效。在存十六进制的UUID值时，最好移除“-”号。最好的做法是用unhex()函数将其转为16字节的数字，并存在一个binary(16)列中，在检索时可通过hex()函数转为十六进制格式。IP地址时实际是32位的无符号整数，所以存储的最好方式是用无符号整数，而不是字符串类型。Inet_ATON()函数将带点的IP转为数字，而Inet_NTOA()函数可将数字转为IP。

例

SELECT Inet_ATON(‘127.0.0.1’); ——>21307033

SELECT Inet_ATON(‘127.1’); ——>21307033

SELECT Inet_NTOA(3520061480); ——>209.207.224.40

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的，无须修改代码。

对用户来说，分区表是一个的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。

分区最适合的场景是数据的时间序列性比较强，则可以按时间来分区，如下面的例子，查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容易地批量删除。

CREATE TABLE members (

    firstname VARCHAR(25) NOT NULL,

    lastname VARCHAR(25) NOT NULL,

    username VARCHAR(16) NOT NULL,

    email VARCHAR(35),

    joined DATE NOT NULL)

    PARTITION BY RANGE( YEAR(joined) ) 

    (PARTITION p0 VALUES LESS THAN (1970),

    PARTITION p1 VALUES LESS THAN (1980),

    PARTITION p2 VALUES LESS THAN (1990),

    PARTITION p3 VALUES LESS THAN (2000),

    PARTITION p4 VALUES LESS THAN MAXVALUE);

如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问，那么可以将热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，就能够有效使用索引和缓存；分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据，此外，还可以对一个分区进行优化、检查、修复等操作；部分查询能够从查询条件确定只落在少数分区上，速度会很快；可以使用分区表来避免某些特殊瓶颈，如InnoDB单个索引的互斥访问；可以备份和恢复单个分区

MySQL有一种早期的简单的分区实现——合并表（Merge Table），其较多且缺乏优化，不建议使用，应该用新的分区机制来替代。

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在哪些分区上，从而进行SQL优化，下面例子中可以看出，有5条记录落在两个分区上。

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);

+--+-------------+-------+-----------+-------+--------------+--------+---------+----+----+-----------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+---+-------------+-------+-----------+-------+--------------+-------+---------+-----+-----+---------+

|1|SIMPLE|user_partition|p1,p4|range|PRIMARY|PRIMARY|8|NULL|5|Using where; index|

+--+-------------+---------+----------+-------+---------------+------+--------+-----+-----+-----------+

1 row in set (0.00 sec)

（2）垂直拆分，即把主码和一些列放入一个表，然后把主码和另外的列放到另一个表中。垂直拆分只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，每个表中的数据记录数在一般情况下是相同的，只是字段不一样，其使用主键关联，经常访问的字段尽量是定长的，这样可以有效地提高表的查询和更新的效率。