The background,

Record the problems encountered in using mysql and some configurations

Mysql > configure mysql

[client]
The default service port number is 3306
port = 3306
socket = /tmp/mysql.sock

[mysqld]
Set the socke file directory
socket = /tmp/mysql.sock
Mysql root installation path
basedir = /usr/local/mysql-5.7.25-macos10.14-x86 _64/
# Data directory
datadir = /usr/local/mysql/data/
# temporary directory
tmpdir = /var/tmp
MySQL > open file descriptor limit, default minimum 1024; When open_files_limit is not configured, compare max_connections*5 with ulimit -n.
# When open_file_limit is configured, compare open_files_limit with max_connections*5.
open_files_limit = 65535
MySQL will read some data into the table_open_cache when it opens a table. If MySQL can't find the data in the cache, it will read it from disk. The default value of 64
# If there are 200 concurrent connections, set this parameter to 200*N(N is the number of file descriptors required per connection);
# When table_open_cache is set to large, if the system cannot handle that many file descriptors, then the client will fail and cannot connect
table_open_cache = 2000

Transaction isolation level, default is repeatable read, mysql default repeatable read level
transaction_isolation = REPEATABLE-READ
The size of the SQL packet to be sent. If there are BLOB objects, change it to 1G
max_allowed_packet = 128M
The MySQL connection will be forcibly closed if it has been idle for more than a certain amount of time
MySQL default wait_timeout is 8 hours. Interactive_timeout must be set at the same time
interactive_timeout = 28800
wait_timeout = 28800

# database default character set, mainstream character set support some special emoticons (special emoticons occupy 4 bytes)
character-set-server = utf8mb4
SQL > select character set from character-set-server
collation-server = utf8mb4_general_ci
Mysql > set character set for client to connect to mysql
init_connect='SET NAMES utf8mb4'
1 indicates that the SQL statement is not case-sensitive. 8.0 needs to be set during initialization
lower_case_table_names = 1
The maximum number of connections is 16384
max_connections = 1000
# stack size for executing requests, default is 50, set to 20% or 30% of the maximum number of links
back_log=300
# Maximum number of incorrect connections
max_connect_errors = 1200
# represents the number of concurrent threads. The default is 0, which means no limit. The recommended value is twice the number of CPU cores.
innodb_thread_concurrency=64
InnoDB cache size is 128 MB by default, 70% of host memory is recommended
innodb_buffer_pool_size=1024M

# Enable slow query logs
slow_query_log = ON
Slow query log location
slow_query_log_file = /usr/local/mysql/data/huandeMacBook-Pro-slow.log
Slow query SQL statement execution time
long_query_time = 3
The number of rows retrieved must reach this value to be counted as a slow query
min_examined_row_limit = 0

Set the path of the relay log
relay_log=/usr/local/var/mysql/relaylog/mysql-relay

# open binlog
# Enable binlog
# binlog log file name, it's important to note that start the mysql user needs on the directory (/ usr/local/var/mysql/binlog) have write permissions
log_bin_basename=/usr/local/var/mysql/binlog/mysql-bin
# configuration serverid
server-id=1
Configure the format of binlog
binlog_format = ROW
Set the path of the relay log
relay_log=/usr/local/var/mysql/relaylog/mysql-relay
If the current server is a secondary server, but needs to act as the primary server of another server, then this parameter needs to be turned on
log_slave_updates = OFF
If this server is a slave server, it is recommended to set it to on and make it read-only
read_only = on
(minimal) (FULL, minimal, minimal)
binlog_row_image = FULL
MySQL flush the data from the log buffer to the log file every time a transaction commits
innodb_flush_log_at_trx_commit=1
# default :0, MySQL does not control the refresh of binlog, the file system itself controls the refresh of its cache, if the server crashes during this period, some data will be lost, the highest performance.
If the value is set to 1, each transaction commit is flushed to disk, which is the safest and has a high performance loss
sync_binlog=1
# configure how long to keep binlog
expire_logs_days=3
Binlog Size of each log file
max_binlog_size = 1G
#binlog Cache size
binlog_cache_size = 4m
You are advised to use the default maximum binlog cache size (18446744073709547520)
# max_binlog_cache_size = 18446744073709547520

When a deadlock is found, a transaction is actively rolled back to allow other transactions to continue. Setting innodb_deadlock_detect to ON indicates that this logic is enabled.
innodb_deadlock_detect = on

In InnoDB, the default value of Innodb_lock_WAIT_timeout is 50s, which means that with the first policy, when a deadlock occurs, the first thread that is locked will timeout out in the 50s before other threads can continue executing. This wait time is often unacceptable for online services.
innodb_lock_wait_timeout=50s

The IO capacity of the disk can be set according to the actual situation. You can use the fiO tool to test it
innodb_io_capacity=1000
Copy the code

3. Slowly query logs

1. The command line takes effect temporarily

After the mysql server is restarted, the command line configuration does not take effect.

# 1And check whether slow log query is enabledshow variables like '%slow_query_log%'; # slow_query_log = slow_query_logONIf yes, it indicates that it is open. #2, enable slow log queryset global slow_query_log=on;

# 3, view the path of slow query logsshow variables like '%slow_query_log_file%';

# 4, view the time of slow query logs. The default value is10S.show variables like '%long_query_time%';

# 5, change the time of slow log query to5S # After the following command is executed, you need to view it on another terminalshow variables like '%long_query_time%'This command will see the modified value.set global long_query_time=5;

# 6, to view the number of slow queriesshow global status like '%slow_queries%';
Copy the code

2. The configuration file modification takes effect permanently

Modify my CNF

MAC for my. CNF file location: mysql — verbose — help | grep. My CNF

/etc/mysql/conf.d/mysql.cnf [mysqld] slow_query_log = ON slow_query_log_file = /var/lib/mysql/instance-1-slow.log # SQL statements that exceed this time are slow query SQL, in seconds long_query_time = 2Copy the code

3. Slowly query log description

Example of slow query logs

# User@Host: root[root] @localhost [] Id: 32 # Query_time: 12.001026 (TOTAL SQL execution time) Lock_time: 0.000000 (wait time for table lock) Rows_sent: 1 (number of rows returned by query) Rows_examined: 0 (query the number of checked rows, the larger the value, the longer the time) SET timestamp=1621950185; select sleep(12); (Specific SQL statement executed)Copy the code

4. Mysqldumpdlow View slow query logs

Sudo mysqlDumpslow-v /usr/local/mysql/data/huandeMacBook-Pro-Slow.log # Sort by total elapsed time sudo mysqlDumpslow-v t /usr/local/mysql/data/huandeMacBook-Pro-Slow.log # Sort by average elapsed time sudo mysqlDumpslow-v at /usr/local/mysql/data/huandeMacBook-Pro-Slow.log # View the average time most time-consuming5Article sudo mysqldumpslow-v at -t 5 /usr/local/mysql/data/huandeMacBook-Pro-Slow.log # returns the most recordsets10asql
sudo mysqldumpslow -s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-Slow.log # gets the most accesses10asql
sudo mysqldumpslow -s c -t 10 /usr/local/mysql/data/huandeMacBook-Pro-Slow.log # gets the front sorted by time10Contains the left-join query statement sudo mysqlDumpslow-s t -t 10 -g "left join"  /usr/local/mysql/data/huandeMacBook-Pro-Slow. Log # Generally, the above statement is executed with and|Sudo mysqlDumpslow sudo mysqlDumpslow-s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log |More # Slow Query log analysis statistics pt-query-digest /usr/local/mysql/data/huandeMacBook-Pro-slow.log
Copy the code

4. Why does the query index not take effect

1. Explain analysis

-- 1. View the execution plan
explain EXTENDED
select * from t_department a LEFT JOIN  t_employees b on a.de_no = b.de_no where a.id = 4019;

2. Use show Warnings
show warnings;
Copy the code

Note: The keyword EXTENDED (EXTENDED information) following explain was deprecated in MySQL5.7 and later, but the syntax is still recognized as backward compatible, so you don’t need to add EXTENDED after Explain in 5.7 and later;

Optimizer_trace analysis

show variables like '%optimizer_trace%';
set optimizer_trace = "enabled=on";
SET optimizer_trace_offset=- 30, optimizer_trace_limit=30;
set optimizer_trace_max_mem_size = 163840; Execute query statements, add statements, etcSELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
set optimizer_trace = "enabled=off";

Copy the code