php php for循环查询数据库效率低,该如何优化mysql mysql

发布网友 发布时间：2022-04-08 04:34

我来回答

共2个回答

懂视网 时间：2022-04-08 08:55

mysql变量分为global、session两种类型，global针对所有的client(设置后启动的),session针对当前回话的链接

back_log:tcp/ip请求的等待队列长度，主要针对短时间大量请求的实例需要增加数值

To refer to a component of a structured variable instance, you can use a compound name ininstance_name.component_name format. Examples:

hot_cache.key_buffer_size
hot_cache.key_cache_block_size
cold_cache.key_cache_block_size

For each structured system variable, an instance with the name of default is always predefined. If you refer to a component of a structured variable without any instance name, the default instance is used. Thus,default.key_buffer_size and key_buffer_size both refer to the same system variable.

mysql status variables:列出mysql所有的状态变量可以分global、session。

Com_xxx开头的一般是记录xxx语句执行的次数。

key_read_requests:The number of requests to read a key block from the MyISAM key cache.

key_reads:The number of physical reads of a key block from disk into the MyISAM key cache.If Key_reads is large, then yourkey_buffer_size value is probably too small. The cache miss rate can be calculated asKey_reads/Key_read_requests.

opened_tables:The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

Innodb_buffer_pool_reads:The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

Innodb_buffer_pool_wait_free:Normally, writes to the InnoDB buffer pool happen in the background. When InnoDB needs to read or create a pageand no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This counter counts instances of these waits. If innodb_buffer_pool_size has been set properly, this value should be small.

mysql的sql mode:在mysql 5.6.6以前sql_mode的值是空，5.6.6以后是NO_ENGINE_SUBSTITUTION

mysql_install_db启动的时候，在安装目录会生成my.cnf文件，其中包含了sql_mode参数

NO_ENGINE_SUBSTITUTION：在指定的存储引擎不存在时会抛出错误，如果没有这个参数，会采用默认的引擎，并且抛出警告

Combination SQL Modes:包含ANSI、DB2等。

Strict SQL Mode：包含 STRICT_ALL_TABLES、STRICT_TRANS_TABLES等。


在严格模式下：在not null定义下出现null、超出值范围等都会抛出error。

mysql log:1、general query log 2、error log 3、binary log 4、slow log 5、DDL log

binary log:主要用于复制、恢复数据 log_format包含三种格式statment、row、mixed

statment：基于sql语句 row：基于数据库行 mixed：前面两种的混合

slow log:查询语句超出了long_query_time的设置，或者log_queries_not_using_indexes参数打开，并且出现全表扫描的情况即没有使用索引的语句


 min_examined_row_limit:如果打开了log_queries_not_using_indexes参数那么slow log可能会比较多，所以可以设置该参数限制频率

log_slow_admin_statements:打开该参数可以针对一些入alter table create table等语句进行根据。

The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE. 

mysql文档摘要

标签：

热心网友 时间：2022-04-08 06:03