Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@taconana(たこなな)

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

Last updated at Posted at 2022-06-30
../

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

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が発行されていた。

[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をチェックしている。認証には関係しないような情報も取得している。

[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

用語(カテゴリやタグのこと)や投稿があるかどうかをチェックしている。ここまでは、ページ遷移ごとの定型の動作のようだ。以下からがログインの処理である。

[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)には、以下がある。

> 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には同じ値が入っているようだ。

> 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ってなんだ。気になるが話を戻して進める。ログインが成功すると、

[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に登録し、再度、メタ情報をすべて読み込んでいる。ちょっと無駄な感じ。

[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つは暗号化されたデータ。

> 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は、以下の感じ。

[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の説明

参照だけかと思っていたが、テーマは利用するたびに以下のように更新されていた。

	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固有の記述のようだ。

	SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 
	ORDER BY wp_posts.post_date DESC LIMIT 0, 3;
	
	SELECT FOUND_ROWS();
../
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?