php mysql 索引_mysql索引

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