mysql索引

mysql優化案例

OA系統mysql索引不合理，慢查詢很多。

之前

<a target="_blank" href="http://blog.51cto.com/attachment/201204/135423769.jpg"></a>

之後

<a target="_blank" href="http://blog.51cto.com/attachment/201204/135446582.jpg"></a>

對系統的影響

<a href="http://blog.51cto.com/attachment/201204/140729160.jpg" target="_blank"></a>

 mysql&gt; 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&gt; 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&gt; 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'

Connection id:    10747

Query OK, 853702 rows affected (11.18 sec)

Records: 853702  Duplicates: 0  Warnings: 0

本文轉自 liang3391 51CTO部落格，原文連結:http://blog.51cto.com/liang3391/844874