mysql数据库对象管理_MySQL管理与优化(15)：优化数据库对象

优化数据库对象

优化表的数据类型：

可通过PROCEDURE_ANALYZE()对当前应用的表进行分析，语法：

SELECT * FROM tb_name PROCEDURE ANALYSE()

范例：

-- 建表

mysql> CREATE TABLE DUCK_CUST(

-> cust_num MEDIUMINT AUTO_INCREMENT,

-> cust_title TINYINT,

-> cust_last CHAR(20) NOT NULL,

-> cust_first CHAR(15) NOT NULL,

-> cust_suffix ENUM('Jr.', 'II', 'III', 'IV', 'V', 'M.D.', 'PhD'),

-> cust_add1 CHAR(30) NOT NULL,

-> cust_add2 CHAR(10),

-> cust_city CHAR(18) NOT NULL,

-> cust_state CHAR(2) NOT NULL,

-> cust_zip1 CHAR(5) NOT NULL,

-> cust_zip2 CHAR(4),

-> cust_duckname CHAR(25) NOT NULL,

-> cust_duckday DATE,

-> PRIMARY KEY (cust_num)

)ENGINE=MyISAM;

#插入数据

...

#查看表优化

mysql> SELECT * FROM DUCK_CUST PROCEDURE ANALYSE()\G

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

Field_name: test.DUCK_CUST.cust_num

Min_value: 1

Max_value: 1

Min_length: 1

Max_length: 1

Empties_or_zeros: 0

Nulls: 0

Avg_value_or_avg_length: 1.0000

Std: 0.0000

Optimal_fieldtype: ENUM('1') NOT NULL

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

Field_name: test.DUCK_CUST.cust_title

Min_value: 1

Max_value: 1

Min_length: 1

Max_length: 1

...

可见由于cust_num数据Max_length, Min_length, Avg_value_or_avg_length, 长度均为1，所以建议用MEDIUMINT(2)

通过拆分提高表的访问效率：

这里的拆分指数据库表进行拆分，对于MyISAM表主要有两种拆分方法：

1. 第一种方法是垂直拆分，即把主键和一些列放到一个表，然后把主键和其他一些列放到另一个表中。这样可以把常用列和非常用列分开，一定程度上减少I/O次数，但需要管理冗余列，查询所有数据需要JOIN操作。

2. 第二种方法是水平拆分，根据一列或多列的值，把数据行放到两个独立的表中。表很大，分割后可以降低在查询时所需要读的数据和索引的页数；表中数据本来就有独立性，比如有些数据常用，有些不常用。需要把数据存放在多个介质中。

逆规范化：

大学老师都喜欢讲究三范式，道理不错，要放在数据量大，查询频繁的互联网应用中，是完全行不通的，我们随时需要逆规范化。

常用的逆规范化技术有：

1. 增加冗余列：指多个表中具有相同的列，避免连接查询。

2. 增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成，以减少连接查询和集函数的使用。

3. 重新组表：指如果许多用户需要查询两个表的数据，则把他们组合成一张表，以减少连接查询。

4. 分割表：把常用列与非常用列等可以分开保存，减少查询的I/O。

逆规范也会带来一定的问题，比如数据完整性等，你需要保证各表的各种冗余字段的同步，个人建议冗余那些不会或不经常变动的列，可以通过其他一些方法保证数据完整性：

1. 定时批处理更新。

2. 在逻辑实现中实现同步。

3. 触发器同步执行。

使用中间表提高统计查询速度：

对数据量较大的表，我们可以采用中间表来进行查询，以提高查询性能。

范例：

# 建表

mysql> CREATE TABLE session(

-> cust_id VARCHAR(10),

-> cust_amount DECIMAL(16,2),

-> cust_date DATE,

-> cust_ip VARCHAR(20));

Query OK, 0 rows affected (0.36 sec)

# 插入大量数据后

...

# 查询特定用户的消费总金额

mysql> SELECT sum(cust_amount) FROM session WHERE cust_id='123';

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

| sum(cust_amount) |

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

| 25585254.40 |

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

1 row in set (5.81 sec)

# 若先将记录转移到中间表

mysql> CREATE TABLE tmp_session(

-> cust_id VARCHAR(10),

-> cust_amount DECIMAL(16, 2),

-> cust_date DATE,

-> cust_ip VARCHAR(20));

Query OK, 0 rows affected (0.72 sec)

#将数据先放到中间表

mysql> INSERT INTO tmp_session SELECT * FROM session WHERE cust_id='123';

Query OK, 2097152 rows affected (21.19 sec)

Records: 2097152 Duplicates: 0 Warnings: 0

mysql> SELECT sum(cust_amount) FROM tmp_session;

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

| sum(cust_amount) |

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

| 25585254.40 |

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

1 row in set (1.76 sec)

中间表在统计查询中经常会用到，主要优点有：

1. 中间表复制源表部分数据，并且与源表相隔离，在中间表上做统计查询不会对在线应用产生负面影响。

2. 中间表上可以灵活的添加索引或增加临时用的新字段，从而达到提高统计查询效率和辅助统计查询作用。

不吝指正。