Shulou(Shulou.com)06/02 Report--

16 Performance optimization

===============

index

Rational use of indexes can improve data access speed

Whether the index is properly used can be checked by using the show status or mysqladmin extension

mysql> show status like 'handler_read%';

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 456 |

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

6 rows in set (0.00 sec)

Of which:

Handler_read_key This value indicates the number of times a row has been read by the index. A high value indicates high index usage.

Handler_read_rnd_next This value indicates the number of requests to read the next row in sequence. A high value indicates that the query efficiency is low and the index of the resultant force needs to be created.

After the index in the table is created, parsing the table reduces fragmentation and improves access efficiency

mysql> analyze table a,b;

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

| Table | Op | Msg_type | Msg_text |

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

| an.a | analyze | status | OK |

| an.b | analyze | status | OK |

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

2 rows in set (0.03 sec)

--------------

query cache

mysql> show variables like '%query_cache%';

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

| Variable_name | Value |

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

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 9437184 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

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

6 rows in set (0.00 sec)

have_query_cache Is the query cache set

query_cache_size indicates the size of the allocated query cache, 0 indicates that query tell cache is turned off

query_cache_type values range from 0 to 2, where 0 or off indicates that the query tell cache is off, 1 indicates that the query tell cache is on, except for select statements that use the sql_no_cache option, 2 or demand provides the query tell cache as needed by running select statements with the sql_cache option

The use or non-use of query tell cache can be controlled by the keywords sql_cache,sql_no_cache in queries

mysql> select sql_cache * from a;

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

| id | name |

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

| 1 | ab |

| 2 | abc |

| 3 | abcd |

| 4 | ann |

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

4 rows in set (0.00 sec)

mysql> select sql_no_cache * from a;

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

| id | name |

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

| 1 | ab |

| 2 | abc |

| 3 | abcd |

| 4 | ann |

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

4 rows in set (0.00 sec)

----------------

analytic query

Oracle-like view execution plans

mysql> explain select * from a;

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

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

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

| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4 | |

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

1 row in set (0.00 sec)

mysql> explain select * from a where id=1 union select * from a;

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

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

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

| 1 | PRIMARY | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |

| 2 | UNION | a | ALL | NULL | NULL | NULL | NULL | 4 | |

|NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |

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

3 rows in set (0.02 sec)

Meaning of each column:

id query in color traditional location

Table name of table query

select_type Query type, including whether there is a subquery, union, external query, subquery in external query, etc.

type Execution of the connection, const: Best type of connection, all: scan all data before getting results

possible_keys Increase query speed Available indexes

keys actually used, including key lengths shown in the key_len column

rows The length of the query data

extra Other information, such as how mysql processes query results, etc.

---------------

Optimize multi-table queries

Joins in mysql work better than subqueries

Avoid nesting

Reduce query hierarchy using intermediate variables

----------------

use the temporary table

Using temporary tables to store intermediate information can improve performance

-----------------

optimization table design

Reasonable field selection, field length selection

optimize table

------------------

Adjust server settings

The first thing that comes to mind about mysql optimization is adjusting key_buffer_size and table_cache.

key_buffer_size mysql index buffer can use the amount of memory, it is generally recommended to use 25% to 30% of the memory force

table_cache The amount of memory used by the table cache, which is also related to max_connections

mysql recommends table_cache=max_connections*n, where n is the number of tables in a standard connection

mysql> select @@table_cache;

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

| @@table_cache |

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

| 700 |

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

1 row in set (0.00 sec)

mysql> show variables like '%table_cache%';

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

| Variable_name | Value |

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

| table_cache | 700 |

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

1 row in set (0.00 sec)

mysql> set global table_cache=1000;

Query OK, 0 rows affected (0.00 sec)

mysql> select @@table_cache;

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

| @@table_cache |

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

| 1000 |

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

1 row in set (0.00 sec)

This setting will be restored after restarting the database

Other optimization methods:

Adding sort_buffer can speed up queries for order by and group by statements

Adding the read_rnd_buffer_size variable increases the speed of sorting rows

Increasing read_buffer_size can improve read cache and thus select efficiency

Increase binlog_cache_size Increase binary log cache area to improve log processing speed

Increasing bulk_insert_buffer_size speeds up bulk inserts, but only in myisam tables

Increasing thread_cache_size controls the amount of memory allocated per process, and provides better control if there are many connections

--------------

benchmark technology

mysql benchmark suite

Requires ability to run perl, including perl dbi suite and mysql database driver (dbd)

perl -e "use DBI"

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.