Shulou(Shulou.com)05/31 Report--

MySQL database optimization ideas and direction is what, I believe that many people without experience are helpless, for this reason this article summarizes the causes and solutions of the problem, through this article I hope you can solve this problem.

I. Basic optimization

mysql> show status like 'valus'

connections //link parameters

uptime //online time

slow_queries //slow queries

com_select

com_insert

com_update

com_delete

Second, optimize the query:

mysql> explain select * from handb.fruits;

+----+-------------+--------+------+---------------+------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+---------------+------+---------+------+------+-------+

| 1 | SIMPLE | fruits | ALL | NULL | NULL | NULL | NULL | 2 | |

+----+-------------+--------+------+---------------+------+---------+------+------+-------+

Number of times table is queried

possible_keys index

key Current index

key_len index length

ref Columns or constants queried together

rows Required rows

Extra Details

3. The impact of indexes on queries

IV. Optimize database structure

1. Split the old table to form a new table

2. Add middle table

V. Insertion optimization

1. Disable index

mysql> alter table book disable keys;

mysql> alter table book enable keys;

2. Disable unique queries

mysql> set unique_checks=0 |1 ; //0 Close 1 Open

3. Disable foreign key checking

mysql> set foreign_key_checks=0 | 1

4. Disable automatic submission

mysql> set autocommit=0 | 1

VI. Analysis and inspection optimization table

mysql> analyze table fruits;

Type: normal status

info info//duplicate information

note Note//null null

warning//query time too slow exceeded

error error//Error option in table, unavailable

VII. Checklist

mysql> check table fruits fast;

QUICK: No scanning. Do not check for links that are wrong.

FAST: Check only tables that are not closed correctly.

CHANGED: Checks only tables that have changed since the last check and tables that have not been closed correctly.

MEDIUM: Scan rows to verify that deleted links are valid, or calculate keyword checksum for each row and use calculated checksum to verify this.

EXTENDED: Perform a comprehensive keyword lookup on all attachments per line, which ensures 100% consistency, but takes a long time.

Eight, mysql server optimization

Buffers: Data that you want to change resides longer in memory, primarily to keep dirty pages in memory. Do not accept memory management, but accept mysql program management.

Guaranteed buffer size:

[mysqld]

query_cache_size=512M

query_cache_type=1 //0 indicates that buffers are not applicable.

1 means that once turned on, all queries will use buffers. If you do not want to query from the buffer, you need to add the option no_sql_cache after the select statement to indicate that the current query does not apply to buffer queries.

2 indicates that sql_cache will be looked up from the buffer only if the select of the query is increased.

key_buffer_size //index buffer size. All threads share the index buffer. Its size depends on the size of the memory. Too much will lead to frequent page changes, which will also reduce system performance.

table_cache //Number of tables open at the same time. Larger tables mean more tables are open at the same time. Too much impact on system performance.

sort_buffer_size //sort buffer size, the larger, the faster the sort speed. Increase efficiency of order by and group by default 2M

read_buffer_size //The size of the buffer allocated per table. This buffer is only used when threads scan continuously.

read_md_buffer_size //The size of the buffer reserved by each thread, similar to above, but used for a specific order. If continuous scanning and reading occurs, adjust this parameter. set session read_md_buffer_size = n

innodb_buffer_pool_size //Maximum cache of innodb and index, the larger the query, the faster

max_connections //indicates the maximum number of connections in the database. Mainly wasted memory size. (More than the maximum number of links in the system, it will crash)

innodb_flush_log_at_trx_commit //Indicates when buffer data is written to the log and the log is written to disk. This value has three values:

0 Writes data to the log every second and then to the hard disk. Not safe, but fast.

1 Write to the log every time a transaction is committed, and then write to the hard disk. Safe, but slow

2 Write to the log every time a transaction is committed, and write to the hard disk every second. A failure can result in 1- 2 seconds of lost data.

back_log //Indicates how many requests can be placed on the stack. The size of the listening queue for TCP/IP. Put data in cache before accepting new requests. Enable this value only if you want a lot of links in a short period of time. The queue limit of the operating system is larger than mysql and cannot be exceeded, otherwise it is invalid.

interactive_timeout //wait time before closing server links.

thread_buffer_size //The size of the buffer allocated for each thread that needs to be sorted, which can be increased if there are many new threads.

wait_timeout //The time the server waits to close a link, s, default 28800

After reading the above, do you know how to optimize mysql database? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.