Several methods of mysql optimization

Recommended tutorial: MySQL tutorial

## 1. Choose the appropriate storage engine: InnoDB

Unless your data table is used for reading only or full-text retrieval (I believe no one will use MYSQL when it comes to full-text retrieval nowadays) ). You should select InnoDB by default.

When you test it yourself, you may find that MyISAM is faster than InnoDB. This is because: MyISAM only caches indexes, while InnoDB caches data and indexes, and MyISAM does not support transactions. However, assuming you use innodb_flush_log_at_trx_commit = 2 you can get close read performance (a hundred times difference).

1.1 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

1.2 For each Create 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 running mysqlcheck -o –all-databases.

#2. Ensure data is read from memory. Talking about data being stored in memory

##2.1 A large enough innodb_buffer_pool_size

It is recommended to store all data in innodb_buffer_pool_size, that is, planning the capacity of innodb_buffer_pool_size according to the storage amount. This way you can read the data entirely from memory. Minimize disk operations.

2.1.1 How to determine innodb_buffer_pool_size is large enough. Is the data being read from memory instead of hard drive?

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 it is found that Innodb_buffer_pool_pages_free is 0, it means that the buffer pool has been used up. 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.

2.1.2 Whether there is enough memory on the server for planning

Run echo 1 > /proc/ sys/vm/drop_caches Clears the operating system's file cache. Ability to see true memory usage.

2.2 Data Warming

By default, a piece of data will only be cached if it is read once in innodb_buffer_pool. 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 preheating can increase reading 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. Run

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

3. Run 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

2.3 Do not allow data to be saved in SWAP

Assuming it is a dedicated MYSQL server. SWAP can be disabled, assuming it is a shared server, and make sure innodb_buffer_pool_size is large enough. Or use a fixed memory space for caching and use the memlock instruction.

3. Regularly optimize and rebuild the database

mysqlcheck - o –all-databases will make ibdata1 continue to grow. The real optimization only involves rebuilding 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;

4. Reduce disk write operations

4.1 Use a large enough write cache innodb_log_file_size

But you need to pay attention to the assumption that 1G innodb_log_file_size is used. If the server crashes. It takes 10 minutes to recover.

It is recommended to set innodb_log_file_size to 0.25 * innodb_buffer_pool_size

##4.2 innodb_flush_log_at_trx_commit

　　这个选项和写磁盘操作密切相关：

　　innodb_flush_log_at_trx_commit = 1 则每次改动写入磁盘

　　innodb_flush_log_at_trx_commit = 0/2 每秒写入磁盘

　　假设你的应用不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者 事务都非常小，都能够用 0 或者 2 来减少磁盘操作。

4.3 避免双写入缓冲

innodb_flush_method=O_DIRECT

5. 提高磁盘读写速度

　　RAID0 尤其是在使用 EC2 这样的虚拟磁盘 (EBS) 的时候，使用软 RAID0 很重要。

6. 充分使用索引

6.1 查看现有表结构和索引

SHOW CREATE TABLE db1.tb1/G

6.2 加入必要的索引

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

　　索引的加入须要依据查询来确定。比方通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。

ADD UNIQUE INDEX
ADD INDEX

6.2.1 比方，优化用户验证表：

　　加入索引

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

　　每次重新启动server进行数据预热

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

　　加入启动脚本到 my.cnf

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

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

　　比方。Rails 这种框架。会自己主动加入索引。Drupal 这种框架会自己主动拆分表结构。

　　会在你开发的初期指明正确的方向。所以，经验不太丰富的人一開始就追求从 0 開始构建，实际是不好的做法。

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

　　记录全部查询。这在用 ORM 系统或者生成查询语句的系统非常实用。

log=/var/log/mysql.log

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

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

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

8. 激进的方法。使用内存磁盘

　　如今基础设施的可靠性已经非常高了，比方 EC2 差点儿不用操心server硬件当机。并且内存实在是廉价。非常easy买到几十G内存的server，能够用内存磁盘。定期备份到磁盘。

　　将 MYSQL 文件夹迁移到 4G 的内存磁盘

mkdir -p /mnt/ramdisk
sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/
mv /var/lib/mysql /mnt/ramdisk/mysql
ln -s /tmp/ramdisk/mysql /var/lib/mysql
chown mysql:mysql mysql

9. 用 NOSQL 的方式使用 MYSQL

　　B-TREE 仍然是最高效的索引之中的一个，全部 MYSQL 仍然不会过时。

　　用 HandlerSocket 跳过 MYSQL 的 SQL 解析层。MYSQL 就真正变成了 NOSQL。

10. 其它

　　●单条查询最后添加 LIMIT 1，停止全表扫描。

　　●将非”索引”数据分离，比方将大篇文章分离存储，不影响其它自己主动查询。

　　●不用 MYSQL 内置的函数。由于内置函数不会建立查询缓存。

　　●PHP 的建立连接速度很快，全部能够不用连接池。否则可能会造成超过连接数。当然不用连接池 PHP 程序也可能将

　　●连接数占满比方用了 @ignore_user_abort(TRUE);

　　●使用 IP 而不是域名做数据库路径。避免 DNS 解析问题

The above is the detailed content of Several methods of mysql optimization. For more information, please follow other related articles on the PHP Chinese website!