MySQL管理与优化(15):优化数据库对象

原创
2014/07/14 14:04
阅读数 128

优化数据库对象

优化表的数据类型:

  • 可通过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. 中间表上可以灵活的添加索引或增加临时用的新字段,从而达到提高统计查询效率和辅助统计查询作用。

不吝指正。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
1 收藏
分享
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部