Set the Data Type in MySQL to optimize the running speed of the instance, mysql Data Type

Set the Data Type in MySQL to optimize the running speed of the instance, mysql Data Type

I have read an optimization case today and I think it is quite representative. Here I will record it and make a mark to commemorate the problem of field definition casually.

Recall that in the table design, many people are used to designing the table structure into Varchar (64), Varchar (255), and so on, although in most cases only 5-15 bytes are saved. so let me look at the example below.
Query statement:
 

 SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;

This table (client_id, channel) is a composite index.
Using explain, let's take a look at the execution plan, which looks perfect for index usage.
 

mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+| id | select_type | table  | type | possible_keys  | key    | key_len | ref | rows  | Extra     |+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+| 1 | SIMPLE  | xxx_sources | index | idx_client_channel | idx_client_channel | 1032 | NULL | 20207319 | Using where; Using index |+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+1 row in set (0.00 sec)

Take a look at the actual execution:
 

mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;+---------+----------+| channel | visitors |+---------+----------+| NULL |  0 |+---------+----------+1 row in set (11.69 sec)

The actual execution is very bad. the idea of passthrough is that the execution plan on the index is perfect, as if it had nothing to do with MySQL. after looking at the table design, we can see that the client_id is also designed
Varchar (255). You can use the following method to try it here:

mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel;+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+| id | select_type | table  | type | possible_keys  | key    | key_len | ref | rows | Extra     |+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+| 1 | SIMPLE  | xxx_sources | ref | idx_client_channel | idx_client_channel | 258  | const | 457184 | Using where; Using index |+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+1 row in set (0.00 sec)

From the execution plan point of view, it is almost, but the actual difference is much higher. specifically, the number of key_len lines dropped from 1032 to 258, And the execution plan changed to the const-based equal-to-equal lookup. i/O is understandable.
Saves a lot.
Let's look at the actual execution:

 

mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel;+---------+----------+| channel | visitors |+---------+----------+| NULL |  0 |+---------+----------+1 row in set (0.25 sec)

Wow, it's changed from 11.69 seconds to 0.25 seconds. What is this concept? How many times has it been optimized.

What are you thinking about here? Remember this case. Well, it's good. You can also optimize it with quotation marks in the future. Why don't you ask? Can you optimize it? Why?
Let's take a look at the first question:
Can I optimize it?
The answer is yes. from the perspective of index length, 258 is still a very large data size. For the client_id field, only the data type value is saved, so why does one int unsigned not need to be stored,
The index length will be reduced from 258 to 4 immediately. Does this save a lot?
Next, let's take a look at the second question. Why?
There are two reasons: cost-based optimizer based on one principle. Client_id is defined as a numeric value in the definition of a table. a numeric value is input during the query. A Numerical Conversion is required.
As a result, MySQL selects a complete index for scanning.

In this case, what do we need to pay attention?
It is too important to select a proper data type. This is the starting point for winning the game, do not define a table as downgrading the primary key. All the other tables are Varchar (255 ). Be careful when indexing fields such as double and float in the database.