MySQL MyISAM index, INNODB index, count performance analysis Personal Insights

Tested under MySQL 5.6.

MyISAM Engine:
When creating a data table, MySQL generates 3 files, respectively (table: Test):
TEST.FRM, Test. MYD, Test. Myi file,
TEST.FRM is the table structure, Test.myd is the file that holds the data, Test.myi is the file that holds the index,
The index file Store relationship Key-value,value is a pointer to the test that is stored. A pointer to the data row in the MyD.
Here we can see that the data of the MyISAM engine is stored separately from the index.
When searching for data using an index, the MySQL server will go to test first. Find the location pointer to the data store in the Myi file,
and then to test. Data is removed from the myd.

InnoDB Engine:
The engine creates a data table, generates only two files, one is the table structure file Test.frm, and the other is the TEST.IBD that stores the data and the index.
There are two kinds of index storage modes: Clustered index and second index. When a user creates a table that does not have a primary key set, the table automatically generates a primary key, and the primary key is associated with the data
Stored together, this is the cluster, when the user created a normal index (index, unique, etc.), this is the second index.
When the primary key is used to search for data, the data is fetched directly when using secondary key to search for data, because secondary key index storage mode with MyISAM engine
Like the index store, Key-value,value is a pointer to primary key, so this procedure finds the primary key pointer and finds the data through the clustered index.

Count Performance Analysis:
A lot of people are count (*), COUNT (1), Count (Col) that high, that low, here's my next test:
One:
MyISAM engine, 20,971,520 data.
Table structure:
| T1 | CREATE TABLE ' T1 ' (
' id ' int (ten) is not NULL auto_increment,
' t ' Int (ten) is the DEFAULT NULL,
PRIMARY KEY (' id '),
KEY ' Fuck_index ' (' t ')
) Engine=myisam auto_increment=20971521 DEFAULT charset=latin1
/*!50100 PARTITION by HASH (ID)
Partitions 5 * * |

Mysql> Select COUNT (*) from T1;
+----------+
| COUNT (*) |
+----------+
| 20971520 |
+----------+
1 row in Set (0.00 sec)

Either count (*), COUNT (1), Count (Col) are 0.00 sec, and repeat results are not posted.
Crouching trough, so fast? We look at what the reason is not to do the Divine horse optimization?

Mysql> Explain select count (id) from T1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
| 1 | Simple | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select Tables Optimized Away |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
1 row in Set (0.00 sec)

Here Count (*), COUNT (1), Count (col) results are the same.
From the results we see that the value of the extra column is: Select tables optimized away, which indicates that the optimizer is used here because the MyISAM engine is in the INSERT, UPDATE,
Delete uses statistical statistics to record changes, and when count () is used for unconditional statistics, the statistics are called directly.

Now let's use conditional statistics:
Mysql> Select COUNT (*) from T1 where ID > 0;
+----------+
| COUNT (*) |
+----------+
| 20971520 |
+----------+
1 row in Set (7.73 sec)

Mysql> Select COUNT (1) from T1 where ID > 0;
+----------+
| COUNT (1) |
+----------+
| 20971520 |
+----------+
1 row in Set (7.62 sec)

Mysql> select COUNT (id) from T1 where ID > 0;
+-----------+
| Count (ID) |
+-----------+
| 20971520 |
+-----------+
1 row in Set (7.91 sec)

At this point we see count (*), COUNT (1), Count (COL) It is not the difference between the three, test several times actually can foresee their performance is actually a bird-like.
Let's take a look at the implementation of it:
Mysql> Explain select COUNT (1) from T1 where ID > 0;
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------- -----+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------- -----+
| 1 | Simple | T1 | Index | PRIMARY | PRIMARY | 4 | NULL | 20971520 | Using where; Using Index |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------- -----+
1 row in Set (0.00 sec)

Here we look at the values of the extra and type columns, using index statistics, which the rows column sees as full-table scans.

Two:
InnoDB engine, 10,485,760 data
Table structure:
T1 | CREATE TABLE ' T1 ' (
' id ' int (ten) is not NULL auto_increment,
' t ' Int (ten) is the DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=10485761 DEFAULT charset=latin1
/*!50100 PARTITION by HASH (ID)
Partitions 5 * * |

Mysql> select COUNT (id) from T1;
+-----------+
| Count (ID) |
+-----------+
| 10485760 |
+-----------+
1 row in Set (7.08 sec)

Mysql> Select COUNT (1) from T1;
+----------+
| COUNT (1) |
+----------+
| 10485760 |
+----------+
1 row in Set (7.02 sec)

Mysql> Select COUNT (*) from T1;
+----------+
| count (*) |
+----------+
| 10485760 |
+----------+
1 row in Set (7.01 sec)

Results indicate COUNT (*), COUNT (1), Count (Col) it's the same as three.
Let's see how they perform:

mysql> explain select count (id) from T1;
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
| id | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
| 1 | Simple | T1 | Index | NULL | PRIMARY | 4 | NULL | 15313711 | Using Index |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
1 row in Set (0.00 sec)

Because Count (*), COUNT (1), Count (col) results are the same, they are not pasted out,
Here we look at the type column, the rows column, the key column, followed by a comparison, and the type value indicates that it is a full table scan.

Then we add a second index:
Mysql> ALTER TABLE t1 add index ' Fuck_index ' (t);
Query OK, 0 rows affected (1 min 2.95 sec)
records:0 duplicates:0 warnings:0

Again, repeat the above operation:

Mysql> Select COUNT (1) from T1;
+----------+
| COUNT (1) |
+----------+
| 10485760 |
+----------+
1 row in Set (5.34 sec)

Mysql> Select COUNT (*) from T1;
+----------+
| COUNT (*) |
+----------+
| 10485760 |
+----------+
1 row in Set (5.40 sec)

Mysql> select COUNT (id) from T1;
+-----------+
| Count (ID) |
+-----------+
| 10485760 |
+-----------+
1 row in Set (5.37 sec)

Here Count (*), COUNT (1), Count (col) execution time is still about the same, but compared to the above test no secondary key faster, cause with the above
The InnoDB engine clustered index is related to the second index, and we analyze their execution:

Mysql> Explain select COUNT (1) from T1;
+----+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
| 1 | Simple | T1 | Index | NULL | Fuck_index | 5 | NULL | 15313711 | Using Index |
+----+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+

At this point we see that the Type column is index, full table scan, key is the newly created index, not the primary test above.

The following tests are conditional (primary key ID):

Mysql> Select COUNT (*) from T1 where ID > 0;
+----------+
| COUNT (*) |
+----------+
| 10485760 |
+----------+
1 row in Set (6.60 sec)

Mysql> Select COUNT (1) from T1 where ID > 0;
+----------+
| COUNT (1) |
+----------+
| 10485760 |
+----------+
1 row in Set (6.62 sec)

Mysql> select COUNT (id) from T1 where ID > 0;
+-----------+
| Count (ID) |
+-----------+
| 10485760 |
+-----------+
1 row in Set (6.42 sec)

At this point the Cosan are still the same, let's take a look at the execution process:
Mysql> Explain select COUNT (*) from T1 where ID > 0;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+---------------------- ----+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+---------------------- ----+
| 1 | Simple | T1 | Range | PRIMARY | PRIMARY | 4 | NULL | 7656854 | Using where; Using Index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+---------------------- ----+
1 row in Set (0.00 sec)

The three of their brothers turned out to be the same, not all of them posted.
Here we look at the type column, the key column, the rows column, the value of type is range, the range search (Execution time: all > Index > Range > Null,null is the fastest),
The value of rows is half of the above.

Let's do it again. The test is conditional (Index T):
Mysql> select COUNT (id) from T1 where T > 0;
+-----------+
| Count (ID) |
+-----------+
| 10485760 |
+-----------+
1 row in Set (6.35 sec)

Mysql> Select COUNT (1) from T1 where T > 0;
+----------+
| COUNT (1) |
+----------+
| 10485760 |
+----------+
1 row in Set (6.24 sec)

Mysql> Select COUNT (*) from T1 where T > 0;
+----------+
| COUNT (*) |
+----------+
| 10485760 |
+----------+
1 row in Set (6.06 sec)

Mysql> Explain select COUNT (*) from T1 where T > 0;
+----+-------------+-------+-------+---------------+------------+---------+------+---------+------------------- -------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+---------+------------------- -------+
| 1 | Simple | T1 | Range | Fuck_index | Fuck_index | 5 | NULL | 7656854 | Using where; Using Index |
+----+-------------+-------+-------+---------------+------------+---------+------+---------+------------------- -------+
1 row in Set (0.28 sec)

The result here is similar to the above. I have a limited level of error, please guide ^-^

MySQL MyISAM index, INNODB index, count performance analysis Personal Insights