MySQL統計情報

MySQLの実行プランのベースとなるデータ確認
-found_records: total amount of read records.
-read_time: assumption of total disk access times.

■ テーブルの情報

mysql> show table status like 'language'\G
*************************** 1. row ***************************
           Name: language
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3560363   (found_recordsの値)
 Avg_row_length: 51
    Data_length: 183156736 (rad_time=Data_Length/16KB)
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 3950305
    Create_time: 2014-08-04 17:59:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql>

■フルスキャンのコスト
found_recordsの確認(Rows),InnoDBのテーブル統計はSamplingの為,
正確にcount(*)の値と同じになってないようです。
Data_lengthを16KBで割るとread_timeが出るそうです。
read_time = (183,156,736/(16*1024))=11,179という事でしょうか。

コスト=read_time+found_records*ROW_EVALUATE_COST
found_records: 3,560,363
read_time: 183,156,736/(16*1024)
ROW_EVALUATE_COST: 0.20

mysql> select (183156736/(16*1024))+3560363*0.2;
+-----------------------------------+
| (183156736/(16*1024))+3560363*0.2 |
+-----------------------------------+
|                       723251.6000 |
+-----------------------------------+
1 row in set (0.00 sec)

実行プランとコストの確認

mysql> select SQL_NO_CACHE count(*) from language;
+----------+
| count(*) |
+----------+
|  3950304 |
+----------+
1 row in set (0.81 sec)

mysql> explain select SQL_NO_CACHE count(*) from language;
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | language | index | NULL          | PRIMARY | 4       | NULL | 3560363 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> show local status like 'Last_query_cost';
+-----------------+---------------+
| Variable_name   | Value         |
+-----------------+---------------+
| Last_query_cost | 723251.599000 |
+-----------------+---------------+
1 row in set (0.00 sec)

mysql> select (183156736/(16*1024))+3560363*0.2;
+-----------------------------------+
| (183156736/(16*1024))+3560363*0.2 |
+-----------------------------------+
|                       723251.6000 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>

found_records: 1
read_time: 1
統計情報ではなく、固定値として1が入るとの事。
主キー検索の場合は統計情報による判断が入らないので速い。


mysql> select SQL_NO_CACHE count(*) from language where language_id = 1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> explain select SQL_NO_CACHE count(*) from language where language_id = 1;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | language | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> show local status like 'Last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> explain select SQL_NO_CACHE count(*) from language where language_id between 1 and 100;
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | language | range | PRIMARY       | PRIMARY | 4       | NULL |  100 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> show local status like 'Last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 41.091530 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql>

Reference:
http://dbstudy.info/files/20120310/mysql_costcalc.pdf

Post Navigation