[php mysql] Blog paging production ideas, phpmysql paging ideas_PHP tutorial

expire_logs_days has been abandoned in MySQL5.7.6, binlog_expire_logs_seconds should be used instead; this parameter only cleans the binlog and does not affect other logs; it requires double settings of the configuration file SETGLOBAL, and cooperates with regular PURGE or crontab cleanup.

SHOWPROCESSLIST returns the connection snapshot visible to the current user. The key fields include Id (thread ID), User (user name), Host (IP port), and State (current operation). PROCESS permission plus ALL is required to see all connections. Root also needs explicit authorization. It is recommended to use performance_schema.threads instead.

The joint index only recognizes the leftmost prefix, because the B-tree must match continuously from the leftmost column; if the middle column is skipped (such as WHEREa=1ANDc=3 in the index (a,b,c)), the right column will be invalid, causing the key to be NULL or the key_len to be too small.

Under high concurrency conditions, you should avoid using MyISAM and choose InnoDB. MyISAM only supports table-level locks, which can easily lead to write freezes. InnoDB row-level locks are more suitable for concurrency scenarios, but buffer_pool_size, disk flushing strategies, and transaction batch processing need to be properly configured to maximize performance.

To check permissions, you need to first confirm that the user exists and the context is correct. Because MySQL stores permissions according to the 'user'@'host' combination, users with the same name on different hosts have independent permissions. SHOWGRANTS does not display PROXY permissions, so you need to check mysql.proxies_priv. For export, it is recommended to use mysqldump or batch generate GRANT statements and standardize the format. Permission comparison must consider the effective priority, implicit denial and connection context.

MySQL8.0 has completely removed the MyISAM engine, and old tables need to be manually converted to InnoDB or Archive; InnoDB is the default and only safe choice, and buffer_pool_size and log_file_size need to be tuned; Archive is only suitable for pure read-only archiving; Memory is only for temporary calculation and will be lost upon restart.

MySQL does not automatically record the backup time, and the information_schema does not have a backup log table; the reliable methods are: check the backup file modification time, parse the mysqldump log, and query the self-built backup_records table.

Triggers disappear automatically when a table is deleted, and there is no need to manually clean them up; MySQL will clear all triggers in the table when executing DROPTABLE, because they are dependent on the existence of the table and have no independent life cycle.