WordPressのSQLをトレースしてみた

|[../](https://qiita.com/taconana/items/3f5424f9abfc755772c2) |
|---|

WordPressの理解のために、`wp-includes/wp-db.php` の以下の部分に error_log('■') を挟み込んでみた。WordPressがどんなSQLを発行しているかが簡単に分かる。

```php:wp-db.php
	public function db_connect( $allow_bail = true ) {
		...
		$this->dbh = mysqli_init();
		...
		if ( WP_DEBUG ) {
			mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
		} else {
			// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
			@mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags );
		}
		// TODO
		error_log('■■ mysqli_real_connect:: ' . $this->dbhost);
		// TODO
		...
	}
		
	private function _do_query( $query ) {
		...
		if ( ! empty( $this->dbh ) && $this->use_mysqli ) {
			$this->result = mysqli_query( $this->dbh, $query );
		} elseif ( ! empty( $this->dbh ) ) {
			$this->result = mysql_query( $query, $this->dbh );
		}
		// TODO
		error_log('■' . $query . ' --> ' . $this->result->num_rows);
		// TODO
		...
	}
```

## ログイン時のSQL
ログイン時には、以下のようなSQLが発行されていた。

```sql
[11:25:14] ■■ mysqli_real_connect:: localhost

[11:25:14] ■SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' --> 122
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_key' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_salt' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'can_compress_scripts' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-posts' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_entries' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-comments' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_comments' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_crop' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_large_crop' LIMIT 1 --> 0
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'large_crop' LIMIT 1 --> 0
```
[ログイン]を押下すると、MySQLに接続し、まずwp_optionsをチェックしている。認証には関係しないような情報も取得している。
```sql
[11:25:15] ■
			SELECT  t.term_id
			FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
			WHERE tt.taxonomy IN ('wp_theme') AND t.name IN ('twentytwentyone')
			LIMIT 1
		 --> 0
[11:25:15] ■
			SELECT   wp_posts.ID
			FROM wp_posts 
			WHERE 1=1  AND (0 = 1) AND wp_posts.post_type = 'wp_global_styles' AND ((wp_posts.post_status = 'publish'))
			GROUP BY wp_posts.ID
			ORDER BY wp_posts.post_date DESC
			LIMIT 0, 1
		 --> 0
```
用語(カテゴリやタグのこと)や投稿があるかどうかをチェックしている。ここまでは、ページ遷移ごとの定型の動作のようだ。以下からがログインの処理である。
```sql
[11:25:15] ■SELECT * FROM wp_users WHERE user_login = 'taconana' LIMIT 1 --> 1
[11:25:15] ■SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC --> 20
[11:25:15] ■SELECT umeta_id FROM wp_usermeta WHERE meta_key = 'session_tokens' AND user_id = 1 --> 1
```
やっとユーザー情報を取得し、個人のメタ情報を取得している。存在しないユーザーIDだと、ユーザー情報が取れずにエラーになる。パスワードが一致しなければエラーになる。パスワード認証に成功したら、`session_tokens`があるかをチェックしている。メタ情報(`wp_usermeta`)には、以下がある。
```sql
> SELECT umeta_id,meta_key,meta_value FROM wp_usermeta WHERE user_id=1;
+----------+---------------------------------------+
| umeta_id | meta_key                              |
+----------+---------------------------------------+
|        1 | nickname                              |
|        2 | first_name                            |
|        3 | last_name                             |
|        4 | description                           |
|        5 | rich_editing                          |
|        6 | syntax_highlighting                   |
|        7 | comment_shortcuts                     |
|        8 | admin_color                           |
|        9 | use_ssl                               |
|       10 | show_admin_bar_front                  |
|       11 | locale                                |
|       12 | wp_capabilities                       |
|       13 | wp_user_level                         |
|       14 | dismissed_wp_pointers                 |
|       15 | show_welcome_panel                    |
|       17 | wp_dashboard_quick_press_last_post_id |
|       18 | community-events-location             |
|       19 | session_tokens                        |
|       20 | wp_user-settings                      |
|       21 | wp_user-settings-time                 |
+----------+---------------------------------------+
```
ユーザー情報(`wp_users`)は、以下の感じ。
```
> desc wp_users;
+---------------------+---------------------+------+-----+---------------------+----------------+
| Field               | Type                | Null | Key | Default             | Extra          |
+---------------------+---------------------+------+-----+---------------------+----------------+
| ID                  | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| user_login          | varchar(60)         | NO   | MUL |                     |                |
| user_pass           | varchar(255)        | NO   |     |                     |                |
| user_nicename       | varchar(50)         | NO   | MUL |                     |                |
| user_email          | varchar(100)        | NO   | MUL |                     |                |
| user_url            | varchar(100)        | NO   |     |                     |                |
| user_registered     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| user_activation_key | varchar(255)        | NO   |     |                     |                |
| user_status         | int(11)             | NO   |     | 0                   |                |
| display_name        | varchar(250)        | NO   |     |                     |                |
+---------------------+---------------------+------+-----+---------------------+----------------+
```
nicenameってなんだ。nicknameの誤植かと思った。よくわからないが、user_login、user_nicename、display_name、そしてメタ情報のnicknameには同じ値が入っているようだ。
```sql
> select id,user_login,user_nicename,display_name from wp_users;
+----+------------+---------------+--------------+
| id | user_login | user_nicename | display_name |
+----+------------+---------------+--------------+
|  1 | taconana   | taconana      | taconana     |
+----+------------+---------------+--------------+

> select user_id,meta_key,meta_value from wp_usermeta where user_id=1 and meta_key='nickname';
+---------+----------+------------+
| user_id | meta_key | meta_value |
+---------+----------+------------+
|       1 | nickname | taconana   |
+---------+----------+------------+
```
nicenameってなんだ。気になるが話を戻して進める。ログインが成功すると、
```sql
[11:25:15] ■SHOW FULL COLUMNS FROM `wp_usermeta` --> 4
[11:25:15] ■UPDATE `wp_usermeta` SET `meta_value` = '...' WHERE `user_id` = 1 AND `meta_key` = 'session_tokens' --> 
[11:25:15] ■SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC --> 20
```
`session_tokens`(600バイトくらいの文字列)を生成し、メタ情報としてDBに登録し、再度、メタ情報をすべて読み込んでいる。ちょっと無駄な感じ。
```sql
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'auth_key' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'auth_salt' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'logged_in_key' LIMIT 1 --> 1
[11:25:15] ■SELECT option_value FROM wp_options WHERE option_name = 'logged_in_salt' LIMIT 1 --> 1
[11:25:15] 127.0.0.1:64000 [302]: POST /wp/wp-login.php
[11:25:15] 127.0.0.1:64000 Closing
[11:25:15] 127.0.0.1:64004 Accepted
```
また、オプションをチェックし、メモリ上で何かしているのかな。詳細は不明。ちなみに上記4つは暗号化されたデータ。
```sql
> SELECT option_name, option_value FROM wp_options where option_name in ('auth_key', 'auth_salt', 'logged_in_key', 'logged_in_salt');
+----------------+-------------------------+
| option_name    | option_value            |
+----------------+-------------------------+
| auth_key       | tw5,-qLGBHkB%-#2>oQS... |
| auth_salt      | /v>[o%2=XFOT]u[pLZ4e... |
| logged_in_key  | 6p*:g+[=cJEFPO8Y!ae[... |
| logged_in_salt | SDNa4HEh]3_=)Z48?-d<... |
+----------------+-------------------------+
```

## サイト表示時(index.php)のSQL

http://localhost:8488/wp/index.php で何が動作するのか、ちょっとだけ追跡してみた。
```
index.php
	+-- wp-blog-header.php 								// WordPressの環境とテンプレートをロード
	   +-- wp_load.php
	   |   +-- (wp-config.phpが存在する場合) wp-config.php
	   |   |   +-- wp-settings.php
	   |   |       +-- WPINC . /load.php
	   |   |       +-- WPINC . /plugin.php
	   |   |       +-- WPINC . /functions.php 
	   |   |       +-- WPINC . /class-wp.php			// class WP(WordPress環境セットアップ)を定義
	   |   |       +-- WPINC . /class-wp-query.php		// class WP_Query(WordPressクエリ)を定義
	   |   |       +-- load.php::require_wp_db()
	   |   |           +-- WPINC . wp-db.php			// class wpdbを定義
	   |   |           +-- $wpdb = new wpdb()			// DBに接続
	   |   |               +-- $wpdb->db_connect()
	   |   |                   +-- mysqli_real_connect()
	   |   |
	   |   +-- WPINC . /load.php  
	   |   +-- load.php::wp_fix_server_vars()			// $_SERVERへセット
	   |   |   +-- load.php::wp_populate_basic_auth_from_authorization_header()
	   |   +-- WPINC . /functions.php
	   |   +-- load.php::wp_load_translations_early()	// エラー検出のため早期ロードを試行
	   |       +-- WPINC . /plugin.php
	   |
	   +-- functions.php::wp($query_vars)				// WordPressクエリのセット
	   |   +-- WPINC . /option.php
	   |   +-- $wp->main()								// WordPressのメインクエリの実行
	   |       +-- $wp->init()							// ログインユーザーをセット
	   |       +-- $wp->parse_request()
	   |       +-- $wp->send_headers()
	   |       +-- $wp->query_posts()
	   |       +-- $wp->handle_404()
	   |       +-- $wp->register_globals()				// $GLOBALSへセット
	   |       +-- do_action_ref_array()
	   |
	   +-- template-loader.php							// テンプレートをロード
	       +-- plugin.php::do_action('template_redirect')
	       +-- plugin.php::do_action('do_robots')
	       +-- plugin.php::do_action('do_favicon')
	       +-- functions.php::do_feed();
	       +-- wp-trackback.php
```
発行されたSQLは、以下の感じ。      
```sql
[15:48:28] ■■ mysqli_real_connect:: localhost
[15:48:28] ■SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' --> 123
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_key' LIMIT 1 --> 1
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'nonce_salt' LIMIT 1 --> 1
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'can_compress_scripts' LIMIT 1 --> 1
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-posts' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_entries' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent-comments' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'widget_recent_comments' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_crop' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'medium_large_crop' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'large_crop' LIMIT 1 --> 0
[15:48:29] ■
			SELECT  t.term_id
			FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
			WHERE tt.taxonomy IN ('wp_theme') AND t.name IN ('twentytwentyone')
			LIMIT 1
		 --> 0
[15:48:29] ■
			SELECT   wp_posts.ID
			FROM wp_posts 
			WHERE 1=1  AND (0 = 1) AND wp_posts.post_type = 'wp_global_styles' AND ((wp_posts.post_status = 'publish'))
			GROUP BY wp_posts.ID
			ORDER BY wp_posts.post_date DESC
			LIMIT 0, 1
		 --> 0
[15:48:29] ■
			SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
			FROM wp_posts 
			WHERE 1=1  AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')))
			ORDER BY wp_posts.post_date DESC
			LIMIT 0, 10
		 --> 1
[15:48:29] ■SELECT FOUND_ROWS() --> 1
[15:48:29] ■SELECT wp_posts.* FROM wp_posts WHERE ID IN (1) --> 1
[15:48:29] ■
			SELECT DISTINCT t.term_id, tr.object_id
			FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id 
			INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
			WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (1)
			ORDER BY t.name ASC
		 --> 1
[15:48:29] ■SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE t.term_id IN (1) --> 1
[15:48:29] ■SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1) ORDER BY meta_id ASC --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_timeout_global_styles_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_global_styles_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... -->
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... --> 
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = 'site_logo' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_timeout_global_styles_svg_filters_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■SELECT option_value FROM wp_options WHERE option_name = '_transient_global_styles_svg_filters_twentytwentyone' LIMIT 1 --> 0
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... --> 
[15:48:29] ■INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (...) ON DUPLICATE KEY UPDATE ... --> 
[15:48:29] ■SELECT * FROM wp_users WHERE ID = '1' LIMIT 1 --> 1
[15:48:29] ■SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1) ORDER BY umeta_id ASC --> 20
[15:48:29] ■
			SELECT   wp_posts.ID
			FROM wp_posts 
			WHERE 1=1  AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish'))
			ORDER BY wp_posts.post_date DESC
			LIMIT 0, 5
		 --> 1
[15:48:29] ■
			SELECT  wp_comments.comment_ID
			FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID
			WHERE ( comment_approved = '1' ) AND  wp_posts.post_status IN ('publish')
			ORDER BY wp_comments.comment_date_gmt DESC
			LIMIT 0,5
		 --> 1
[15:48:29] ■SELECT wp_comments.* FROM wp_comments WHERE comment_ID IN (1) --> 1
[15:48:29] ■SELECT comment_id, meta_key, meta_value FROM wp_commentmeta WHERE comment_id IN (1) ORDER BY meta_id ASC --> 0
[15:48:29] ■SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts  WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC  --> 1
[15:48:29] ■
			SELECT  t.term_id
			FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
			WHERE tt.taxonomy IN ('category') AND tt.count > 0
			ORDER BY t.name ASC
		 --> 1
[15:48:29] ■SELECT term_id, meta_key, meta_value FROM wp_termmeta WHERE term_id IN (1) ORDER BY meta_id ASC --> 0
[15:48:29] ■SELECT * FROM wp_posts WHERE ID = 3 LIMIT 1 --> 1
[15:48:29] 127.0.0.1:55404 [200]: GET /wp/
[15:48:29] 127.0.0.1:55404 Closing
[15:48:29] 127.0.0.1:55411 Accepted
```

## (おまけ) SQLの説明

参照だけかと思っていたが、テーマは利用するたびに以下のように更新されていた。 
```sql
	INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_global_styles_twentytwentyone', 'xxx', 'no') 
	ON DUPLICATE KEY UPDATE 
		option_name = VALUES(option_name), 
		option_value = VALUES(option_value), 
		autoload = VALUES(autoload);
```
ところで、この`ON DUPLICATE KEY UPDATE`の構文は何だ！初めてみるSQLだ。INSERTで失敗したら、UPDATEに切り替えてくれるみたい。これは便利そう。JavaでINSERTで失敗したら、改めてUPDATEするコードをよく書いていたが、SQL一発で行けたのか。いつからこんな構文が有効になったのだろう。MySQL固有の記述なので、制約はあるかもしれない。

実は、`FOUND_ROWS()`も初めて見る。`SQL_CALC_FOUND_ROWS`を添えておけば、後で`SELECT FOUND_ROWS()`で件数を取れるみたい。LIMITと組み合わせると有効なようだ。例えば、レコードが10件でも LIMIT 0,3 指定だと3件しか返さない。しかし、後で SELECT FOUND_ROWS() で問い合わせると、10が得られるようだ。これも便利そう。ただし、これもMySQL固有の記述のようだ。
```sql
	SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 
	ORDER BY wp_posts.post_date DESC LIMIT 0, 3;
	
	SELECT FOUND_ROWS();
```
|[../](https://qiita.com/taconana/items/3f5424f9abfc755772c2) |
|---|