Detailed explanation of MySQL database optimization strategy in PHP

This article briefly describes the MySQL optimization strategy for PHP database programming. Share it with everyone for your reference, the details are as follows:

I saw an article a few days ago that said that in many cases the bottleneck of PHP is not in PHP itself, but in the database. We all know that in PHP development, the addition, deletion, modification and checking of data are the core. In order to improve the operating efficiency of PHP, programmers not only need to write code with clear logic and high efficiency, but also need to be able to optimize query statements. Although there is nothing we can do about the reading and writing speed of the database, with the help of some database class extensions and data storage servers such as memcache, mongodb, and redis, PHP can also achieve faster access speeds, so understand and learn These extensions are also very necessary. This article will first talk about the common optimization strategies of MySQL.

A few MySQL tips

1. Keywords in SQL statements are best written in capital letters. First, it is easier to distinguish keywords. and the operation object. Secondly, when the SQL statement is executed, MySQL will convert it to uppercase. Manually writing uppercase can increase query efficiency (although it is very small).
2. If we add or delete data rows in the database, the data ID will be too large. Use ALTER TABLE tablename AUTO_INCREMENT=N to make the auto-increment ID count from N. .
3. Add the ZEROFILL attribute to the int type to automatically fill in the data. 0
4. When importing a large amount of data, it is best to delete the index first, insert the data, and then add the index. Otherwise, mysql will Spend a lot of time updating the index.
5. When creating a database and writing sql statements, we can create a file with the suffix .sql in the IDE. The IDE will recognize the sql syntax, making it easier to write. More importantly, if your database is lost, you can still find this file and use /path/mysql -uusername -ppassword databasename < filename.sql in the current directory to execute the sql of the entire file statement (note that -u and -p are followed by the username and password without spaces).

Optimization of database design

1. The database design conforms to the third paradigm, and there can be certain data redundancy for the convenience of query.

2. Select the data type priority int > date, time > enum, char > varchar > blob. When selecting the data type, you can consider replacing it. For example, the ip address can be converted to Unsign int type is used for storage.

3. For the char(n) type, try to keep the n value as small as possible if the data is complete.

4. Using the partition command to partition a single table when creating a table can greatly improve query efficiency. MySQL supports RANGE, LIST, HASH, and KEY partition types. Among them, RANGE is the most commonly used, and the partitioning method is:

CREATE TABLE tablename{
}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码
PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区
PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5. When choosing a database engine, you should pay attention to the difference between innodb and myisam.

Storage structure: MyISAM is stored in three files on the disk. All tables in InnoDB are stored in the same data file, generally 2GB
Transaction support: MyISAM does not provide transaction support. InnoDB provides transaction support.
Table lock differences: MyISAM only supports table-level locks. InnoDB supports transactions and row-level locks.
Full-text index: MyISAM supports FULLTEXT type full-text index (not applicable to Chinese, so the sphinx full-text index engine must be used). InnoDB does not support it.
The specific number of rows in the table: MyISAM saves the total number of rows in the table, and querying count(*) is very fast. InnoDB does not save the total number of rows in the table and needs to be recalculated.
Foreign keys: Not supported by MyISAM. InnoDB supports

index optimization

1. Innodb is a clustered index. There must be a primary key when storing the index. If not specified, the engine will Automatically generate a hidden primary key and generate a primary index. The physical address of the primary key is stored in the index. The data is stored based on the primary key. Every time you use the index, you must first find the primary index and then find the data under the primary index.

The advantage is that searching through the primary key is very fast. The disadvantage is that the secondary index will be slower because you need to first find the primary index through the secondary index (the secondary index is the location of the primary index.), and then search through the primary index. data. And if the primary key is irregular, more data blocks need to be moved when inserting new values, which will affect efficiency, so try to use an int type that increases regularly as the primary key. Also, because the data is placed immediately after the primary key, if there are columns (text/blob) with a particularly large amount of data in the data, InnoDB will skip many data blocks during the query, which will also cause slowness.

2. Each index of myisam's index points to the same address of each row on the disk. They are all lightweight pointer data. The disadvantage is that each index is not established through the primary key, and the query is not as fast as searching for the primary key in the clustered index. But because it stores the address, the comparison aspect moves and changes when inserting a new value.

3. When performing a multi-condition query, when creating indexes for multiple conditions separately, when executing a sql query, MySQL will only select the closest index to use, so if a multi-condition query is required, a joint index must be established. , even if it causes data redundancy.

联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。

4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列

SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename

来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引
ALTER TABLE tablename ADD INDEX (column(n)); 来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。

5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。

数据查询方面优化

数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。

一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很"慢"的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。

慢查询日志：

1、在my.ini或my.cnf的[mysqld]下添加

slow_query_log_file=/path //设置日志存储路径
long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来

2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:

-g pattern 使用正则表达式
-t n返回前n条数据
-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

EXPLAIN语句

使用方法，在要执行的查询语句前面加EXPLAIN

EXPLAIN SELECT * FROM user;

得到形如下图的结果：

下面是对每一项的解释：

id 查询语句的id，简单查询无意义，多重查询时可以看出执行查询的顺序
select-type 执行的查询语句的类型，对应多重查询，有simple/primary/union等。
tabel 查询语句查询的数据表
type 获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all
possible-keys:可能使用到的索引
key 使用到的索引
key_len索引长度
ref 使用哪个列与索引一起从表中选择。
rows 查找到数据要扫描的大概行数，可看出索引的优劣
extra 常见的有
using filesort 查询到数据后进行文件排序，较慢，需要优化索引
using where 读取整行数据后进行判断过滤，是否符合where条件
using index 索引覆盖，即在牵引中已经有这存储了目标数据，直接读取索引，很快。

PROFILE

用SELECT @@frofiling来查看PROFILE的开启状态。
如果未开启，用SET profiling=1来开启。
开启之后，再执行查询语句，MySQL会自动记录profile信息。
应用show profiles查看所有的sql信息，结果为 Query_ID Duration Query三列结果，分别是查询ID，用时和所用的sql语句。
我们可以使用

SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

数据库主从复制和读写分离

1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。

主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。

2、配置主服务器master

修改my.ini/my.conf

[mysqld]
log-bin=mysql-bin //启用二进制日志
server-id=102 //服务器唯一ID

3、配置从服务器slave

log-bin=mysql-bin //启用二进制日志
server-id=226 //服务器唯一ID

4、在主服务器上授权从服务器

GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

change master to
master_host="masterip",
master_user="masteruser",
master_password="masterpasswd";

6, then use start The slave command starts master-slave replication.

Don’t forget to restart the server after each configuration modification. Then you can use show master/slave status on the master and slave servers to view the master/slave status.

Achieving the separation of reading and writing in the database relies on MySQL middleware, such as mysql_proxy, atlas, etc. By configuring these middlewares to separate reading and writing between the master and slave servers, the slave server assumes the responsibility of being read, thereby reducing the burden on the master server.

Sharding of the database

When the amount of data in the data table in the database is very large, both indexing and caching are under great pressure. If the database is large, shard the database so that it is stored in multiple database servers or multiple tables to reduce query pressure.

The methods include vertical segmentation, horizontal segmentation and joint segmentation.

Vertical Segmentation: When there are a lot of data tables, split the tables that are closely related in the database (such as the same module, often connected and queried) and put them into different master-slave ones. on the server.

Horizontal Segmentation: When there are not many tables and the amount of data in the table is very large, in order to speed up the query, you can use hashing and other algorithms to divide a data table into several and put them on different servers to speed up queries. The difference between horizontal sharding and data table partitioning lies in the difference in storage media.

Joint Segmentation: In more cases, the data table and the amount of data in the table are very large, so joint segmentation is required, that is, vertical and horizontal table splitting are performed at the same time. The database is divided into a distributed matrix for storage.

Each of these database optimization methods can be used to write an article. It can be said to be profound and profound. If you understand and remember these methods, you can perform purposeful selection and optimization when necessary to achieve the best results for the database. Efficiency of efficiency.

Next we will further summarize the basic usage scenarios and usage methods of commonly used PHP database class extensions memcache, redis and mongodb.

The above is the detailed content of Detailed explanation of MySQL database optimization strategy in PHP. For more information, please follow other related articles on the PHP Chinese website!