mysql优化案例

OA系统mysql索引不合理，慢查询很多。

 

之前

 

之后

 

对系统的影响

之后

 

 

 

 mysql> show index from xxxx_pms;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    10543
Current database: ioffice

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| xxxx_pms |          0 | PRIMARY  |            1 | pmid        | A         |      853700 |     NULL | NULL   |      | BTREE      |         | 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.24 sec)

mysql> desc xxxx_pms;
+------------+-----------------------+------+-----+---------+----------------+
| Field      | Type                  | Null | Key | Default | Extra          |
+------------+-----------------------+------+-----+---------+----------------+
| pmid       | int(10) unsigned      | NO   | PRI | NULL    | auto_increment | 
| msgfrom    | varchar(15)           | NO   |     |         |                | 
| msgfromuid | mediumint(8) unsigned | NO   |     | 0       |                | 
| msgto      | varchar(15)           | NO   |     |         |                | 
| msgtouid   | mediumint(8) unsigned | NO   |     | 0       |                | 
| folder     | varchar(15)           | NO   |     |         |                | 
| newpm      | tinyint(1) unsigned   | NO   |     | 1       |                | 
| subject    | varchar(255)          | NO   |     |         |                | 
| dateline   | int(10) unsigned      | NO   |     | 0       |                | 
| content    | text                  | NO   |     | NULL    |                | 
+------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> alter table xxxx_pms add INDEX IX_m_f_n_d(msgtouid,folder,newpm,dateline);
ERROR 1300 (HY000): Invalid utf8 character string: '，folder，newpm，dateline'

mysql> alter table xxxx_pms add INDEX IX_m_f_n_d(msgtouid,folder,newpm,dateline);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    10747
Current database: ioffice

Query OK, 853702 rows affected (11.18 sec)
Records: 853702  Duplicates: 0  Warnings: 0

 

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/844874