How to optimize performance of mysql? A practical method to achieve performance optimization (for beginners' reference)

MYSQL should be the most popular WEB back-end database. WEB development languages ​​have been developing rapidly recently. PHP, Ruby, Python, and Java each have their own characteristics. Although NOSQL has been mentioned more and more recently, I believe that most architects will still choose MYSQL for data storage. So how does mysql achieve performance optimization? The following article will introduce some useful methods for MySQL performance optimization. I hope it will be helpful to everyone. .

Refer to php Chinese website mysql tutorial: "Six days to take you through MySQL video tutorial"

Mysql practical method to achieve performance optimization:

Improve disk read and write speed

RAID0 especially when using EC2 When using this kind of virtual disk (EBS), it is very important to use soft RAID0.

Using MYSQL the NOSQL way

B-TREE is still one of the most efficient indexes, and all MYSQL is still future-proof.

Use HandlerSocket to skip the SQL parsing layer of MYSQL, and MYSQL will truly become NOSQL.

Reduce disk write operations

1 Use a large enough write cache innodb_log_file_size

But you need to pay attention to if you use 1G innodb_log_file_size, if the server machine and takes 10 minutes to recover.

It is recommended that innodb_log_file_size be set to 0.25 * innodb_buffer_pool_size

2 innodb_flush_log_at_trx_commit

This option is closely related to the write disk operation:

innodb_flush_log_at_trx_commit = 1, each modification is written Into disk
innodb_flush_log_at_trx_commit = 0/2 Write to disk per second

If your application does not involve high security (financial system), or the infrastructure is secure enough, or the transactions are small, all You can use 0 or 2 to slow down disk operations.

3 Avoid double write buffering

innodb_flush_method=O_DIRECT

Choose the appropriate storage engine: InnoDB

Unless your data table uses For read-only or full-text search (I believe no one will use MYSQL when it comes to full-text search now), you should choose InnoDB by default.

You may find that MyISAM is faster than InnoDB during your own testing. This is because: MyISAM only caches indexes, while InnoDB caches data and indexes. MyISAM does not support transactions. But if you use innodb_flush_log_at_trx_commit = 2 you can get close read performance (a hundred times difference).

How to convert an existing MyISAM database to InnoDB:

mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

Create an InnoDB FILE for each table:

innodb_file_per_table=1

This ensures that the ibdata1 file will not be too large. out of control. Especially when executing mysqlcheck -o –all-databases.

Ensure that data is read from the memory and the data is saved in the memory

A sufficiently large innodb_buffer_pool_size

is recommended The data is completely stored in innodb_buffer_pool_size, that is, the capacity of innodb_buffer_pool_size is planned according to the storage amount. This way you can read data entirely from memory, minimizing disk operations.

How to determine innodb_buffer_pool_size is large enough so that data is read from memory instead of hard disk?

Method 1

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 129037 |
| Innodb_buffer_pool_pages_dirty   | 362    |
| Innodb_buffer_pool_pages_flushed | 9998   |
| Innodb_buffer_pool_pages_free    | 0      |  !!!!!!!!
| Innodb_buffer_pool_pages_misc    | 2035   |
| Innodb_buffer_pool_pages_total   | 131072 |
+----------------------------------+--------+
6 rows in set (0.00 sec)

If you find that Innodb_buffer_pool_pages_free is 0, it means that the buffer pool has been used up and you need to increase innodb_buffer_pool_size

Several other parameters of InnoDB:

innodb_additional_mem_pool_size = 1/200 of buffer_pool
innodb_max_dirty_pages_pct 80%

Method 2

Or use the iostat -d -x -k 1 command to check the operation of the hard disk.

Whether there is enough memory on the server for planning

Execute echo 1 > /proc/sys/vm/drop_caches to clear the operating system's file cache and you can see the real memory usage.

Data warm-up

By default, a piece of data will be cached in innodb_buffer_pool only if it is read once. Therefore, the database has just started and needs to warm up the data and cache all the data on the disk into the memory. Data warm-up can increase read speed.

For InnoDB database, you can use the following method to warm up the data:

1. Save the following script as MakeSelectQueriesToLoad.sql

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

2. Execute

mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

3. Execute every time you restart the database, or when you need to warm up before backing up the entire database:

mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1

Do not allow data to be saved in SWAP

If it is a dedicated MYSQL server, you can disable it SWAP, if it is a shared server, make sure innodb_buffer_pool_size is large enough. Or use a fixed memory space for caching and use the memlock instruction.

Regular optimization and reconstruction of the database

mysqlcheck -o –all-databases will make ibdata1 continue to grow. The real optimization can only rebuild the data table structure ：

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

充分使用索引

查看现有表结构和索引

SHOW CREATE TABLE db1.tb1/G

添加必要的索引

索引是提高查询速度的唯一方法，比如搜索引擎用的倒排索引是一样的原理。

索引的添加需要根据查询来确定，比如通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。

ADD UNIQUE INDEX
ADD INDEX

比如，优化用户验证表：

添加索引

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

每次重启服务器进行数据预热

echo “select username,password from users;” > /var/lib/mysql/upcache.sql

添加启动脚本到 my.cnf

[mysqld]
init-file=/var/lib/mysql/upcache.sql

使用自动加索引的框架或者自动拆分表结构的框架

比如，Rails 这样的框架，会自动添加索引，Drupal 这样的框架会自动拆分表结构。会在你开发的初期指明正确的方向。所以，经验不太丰富的人一开始就追求从 0 开始构建，实际是不好的做法。

分析查询日志和慢查询日志

记录所有查询，这在用 ORM 系统或者生成查询语句的系统很有用。

log=/var/log/mysql.log

注意不要在生产环境用，否则会占满你的磁盘空间。

记录执行时间超过 1 秒的查询：

long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

相关推荐：

1. MySQL最新手册教程

2. MySQL 5.1参考手册