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