如何优化MySQL表？

Question

我在找一种方法来提高桌子的性能。下面是MySQL表。

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| receiver | varchar(15) | NO   |     | NULL    |                |
| depCode  | varchar(12) | NO   |     | NULL    |                |
| sms      | text        | YES  |     | NULL    |                |
| billable | varchar(5)  | YES  |     | NULL    |                |
| smsc     | varchar(10) | YES  |     | NULL    |                |
| sendDate | date        | YES  |     | NULL    |                |
| sendTime | time        | YES  |     | NULL    |                |
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
+----------+-------------+------+-----+---------+----------------+

它有10,000多万份记录。

下面是我用来获取记录的SQL查询。${variable}是变量的语法。

SELECT count(*) as totalCount FROM `tbl_incoming` 
WHERE `depCode`=${depCode} AND `smsc`=${smsc_value} AND receivedDate BETWEEN  ${fromDate} AND ${toDate} 
ORDER BY receivedDate, receivedTime;

请参阅下面的解释。

EXPLAIN SELECT * FROM `tbl_incoming`  
WHERE `depCode`="Slrd" AND `smsc`="dgsm" AND receivedDate  BETWEEN  "2009-11-26" AND "2014-11-26"  
ORDER BY receivedDate, receivedTime;

+----+-------------+--------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | tbl_incoming | ALL  | NULL          | NULL | NULL    | NULL | 542527 | Using where; Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-----------------------------+

根据我的知识，索引可以帮助提高表的性能。

PS :除了索引之外，我还在寻找其他选项。

我怎样才能提高桌子的性能？

Answer 1

索引应该对您有帮助。

在您的查询中，您对列depCode、smsc和receivedDate有一个where条件。你一定对数据有更多的了解。第一，标识:在上述3列中具有最多不同值的列。选择它作为索引列，并在include中添加其他2。

Eg:
Create Index IX_Custome On tbl_incoming (depCode) Include (smsc,receivedDate)

这只是一种方法，如果没有用，就忽略它。