mysql事务庞大,优化一个非常庞大的mysql表（查询或mysql）

I have a mysql DB with 50 GB data and 200M record in a table. I am running following query and it takes 350 second to complete:

SELECT x_date, count(*) as totl

FROM `rec_ex_15`

WHERE x_date > '2014-12-01'

and typx = '2'

group by x_date

order by x_date desc

x_date and typx are indexed.

Here is the explain:

id select_type table type possible_keys key key_len ref rows

1 SIMPLE rec_ex_15 range typx,x_date x_date 3 NULL 15896931 Using where

Are there any way to get the result faster?

解决方案

As noted in the comment... your query is very simple. You would be best to have a covering/compound index on (typx, x_date )

The typx = '2' first, then the index can jump directly to the date criteria and getting the results.

You may (future, consider creating a separate aggregate table, such as counts per type and date, then get aggregates from that if you are dealing with 200m records.

Clarifying the index (typx, x_date)

Since you are looking for a specific "typx", you want that in the first position of the index and secondary is the x_date. Think of the index like a room of boxes. Each box holds only 1 instance of a "typx" value (1, 2, 3, etc)... Within the box for (in your case typx = '2'), they are then sorted by x_date. So, within the box for typx = 2, you can jump directly to the date in question, grab the records and be done.

If the index were based on just x_date (as one you had available), assume each box in a room contains a single date. Yes, you can jump directly to the date starting your list, but then you have to go into the box and look through all entries to pull out any typx = 2 records. Now, close the box for day 1 and go to the next box date and look for any typx = 2 and so forth.

Having an efficient index based on your criteria can significantly help queries.