Database in php I. MySQL optimization policy overview, mysql Overview

Database in php I. MySQL optimization policy overview, mysql Overview

The other day I saw an article about the PHP bottleneck. In many cases, it is not the PHP itself, but the database. We all know that in PHP development, adding, deleting, modifying, and querying data is the core. To improve the running efficiency of PHP, programmers not only need to write code with clear logic and high efficiency, but also can optimize query statements. Although we are powerless to read and write data to databases, with the help of some database storage servers such as memcache, mongodb, and redis, PHP can also achieve faster access, so it is also necessary to learn about these extensions. This article first describes the common MySQL optimization strategies.

Several tips on MySQL

• 1. It is best to use keywords in SQL statements.UppercaseFirst, it is easy to distinguish between keywords and operation objects. Second, MySQL converts SQL statements to uppercase during execution. Manual writing can increase the query efficiency (although very small ).
• 2. If we add or delete rows in the database, the data ID is too large.Alter table tablename AUTO_INCREMENT = N,Enable auto-increment IDs to count from N.
• 3. Add int typeZEROFILLAttribute can automatically add 0 to the data
• 4. When importing a large amount of data, it is best to delete the index before inserting the data, and then add the index. Otherwise, mysql will spend a lot of time updating the index.
• 5. When creating a database to write SQL statements, we can create a file with the suffix ". SQL" in IDE, which recognizes SQL syntax and makes it easier to write. More importantly, if your database is lost, you can find this file and use it in the current directory./Path/mysql-uusername-ppassword databasename <filename. SQLTo execute the SQL statement of the entire file (note-u and-p followed by the user name and password without spaces ).

 

Database Design Optimization
1. The database design conforms to the third paradigm, so data redundancy can be ensured for convenient query.
2. Select the data type priority int> date, time> enum, char> varchar> blob. When selecting the data type, consider replacing it. For example, you can use ip2long () for the IP address () the function is converted to the unsign int type for storage.
3. For the char (n) type, try to reduce the n value when the data is complete.
4. Run the partition commandTable PartitioningMySQL supports RANGE, LIST, HASH, and KEY partition types, which are most commonly used in RANGE mode. The partitioning method is as follows:
Create table tablename {
} ENGINE innodb/myisam CHARSET utf8 // select the database ENGINE and encoding
Partition by range/LIST (column), // partition by range and pre-defined LIST
PARTITION partname values less than/IN (n), // name the PARTITION and specify the PARTITION range.
5. Pay attention when selecting a Database EngineDifferences between innodb and myisam.
Storage Structure: MyISAM stores three files on the disk. While all InnoDB tables are stored in the same data file, generally 2 GB
Transaction support: MyISAM does not support transactions. InnoDB provides transaction support.
Table lock difference: MyISAM only supports table-level locks. InnoDB supports transactions and row-level locks.
Full-text index: MyISAM supports full-text indexes of the FULLTEXT type (not applicable to Chinese characters, so use the sphsf-full-Text Index engine ). InnoDB does not.
The specific number of rows in the Table: MyISAM stores the total number of rows in the table, and the Query count (*) is very fast. InnoDB does not store the total number of rows in the table and needs to be recalculated.
Foreign key: not supported by MyISAM. InnoDB support

 

INDEX OPTIMIZATION
1. innodb is a clustered index. A primary key is required for storing indexes. If no primary key is specified, the engine automatically generates a hidden primary key to generatePrimary IndexThe index memory is the physical address of the primary key. data is stored by the primary key. You must first find the primary index and then find the data under the primary index each time you use the index.

The advantage is that primary key lookup is fast, but secondary indexes are slow because secondary indexes must be used first (the primary index is located in the secondary index .) Locate the primary index, and then use the primary index to find data. In addition, if the primary key is irregular, more data blocks need to be moved when a new value is inserted, which will affect the efficiency. Therefore, try to use the regularly increasing int type as the primary key. In addition, because the data is closely followed by the primary key, if there are columns (text/blob) with a particularly large data volume in the data, many data blocks will be skipped during innodb Query, which may lead to a slow speed.

2. The indexes of myisam all point to the addresses of all rows on the disk, and are lightweight pointer data. The disadvantage is that the primary key is not used to create each index. It is faster to search for the primary key without clustering indexes. However, because the address is stored, the comparison changes when the new value is inserted.
3. During Multi-condition query, when you create indexes for multiple conditions separately, MySQL only selects one of the closest indexes for SQL query. Therefore, if you need multi-condition query, to create a joint index, data redundancy may occur.
The method for creating a BTREE for a joint index: Create an index for the first condition, and create an index for the second condition in the BTREE area of the first index, and so on. Therefore, when using an index,The Union index is not used without the first condition and the second condition.. When using indexes, the conditions must be ordered and used in sequence.
4. The index length has a great impact on queries. We should try to create a short index length. We can use the query Column
Select count (distinct left (column)/COUNT (*) FROM tablename is used to test the different lengths used to create an index on a column. What is the index coverage rate, let's choose n lengths close to saturation to create an index.
Alter table tablename add index (column (n); to INDEX the first n characters of a column. If the first n characters are the same, we can even store the string in reverse order and create an index.
5. maintenance methods for index fragmentation caused by frequent changes: alter table tablename ENGINE oldengine; that is, apply the TABLE storage ENGINE again to make it automatically maintained; you can also use the OPTIMIZE tablename command for maintenance.

Data Query Optimization
Perform as few queries as possible for database operations. When there are queries, perform data operations on the database level as far as possible. Instead, return the data to the PHP script for operation, reducing the pressure on the database.
Once a database performance problem is found, it must be solved in a timely manner. GenerallySlow QueryThe log query is very slow.EXPLAINUsed to analyze the query and index usagePROFILEAnalyze the specific resource consumption during statement execution.
Slow query log:
1. Add it under [mysqld] Of my. ini or my. cnf
Slow_query_log_file =/path // you can specify the log storage path.
Long_query_time = n // if the statement execution time reaches n seconds, it will be recorded
2. SET slow_query_log = 'on' in MySQL to enable slow query.
3. After logging, we use mysqldumpslow filename in the/bin/directory to view the log. The common parameters are as follows:
-G pattern: Regular Expression
-T n: return the first n data records.
-S c/t/l/r are sorted by the number of records/time/query time/number of returned records

EXPLAIN statement
Usage: add the EXPLAIN statement before the query statement to be executed.
Explain select * FROM user;
The result is as follows:

The following is an explanation of each item:
Id of the query statement. It is meaningless to perform a simple query. The query execution sequence can be seen in multiple queries.
The type of the query statement executed by select-type, corresponding to multiple queries, such as simple/primary/union.
Data Table queried by the tabel query statement
TypeObtain data types. common types of efficiency from high to low are null> const> eq_ref> ref> range> index> all.
Possible-keys: possible Indexes
KeyUsed Index
Key_len index Length
The column used by ref and the index are selected from the table.
RowsFind the approximate number of rows of data to be scanned, and you can see the advantages and disadvantages of the index.
ExtraCommon
Using filesort sorts the files after the data is queried, which is slow. You need to optimize the index.
Using where reads the entire row of data and then checks whether the where condition is met.
The using index overwrites the index, that is, the target data is already stored in the traction, and the index is directly read, which is very fast.

PROFILE
Use SELECT @ frofiling to check the enabling status of the PROFILE.
If not, use SET profiling = 1.
After it is enabled, run the query statement. MySQL automatically records the profile information.
Run show profiles to view all the SQL information. The result is the Query_ID Duration Query column, which contains the Query ID, the time used, and the SQL statement used.
We can use
Show pfrofile [Type[, Type] [FOR QUREYQuery_ID] [Limit rwo_count [OFFSET offset]
Common types include ALL (ALL) block io (display IO-related overhead) CPU (CPU overhead) MEMORY (MEMORY overhead), etc.

 

Optimization of Large Storage
Master-slave database replication and read/write splitting
1. The master will change the log to the binary log. slave will copy the binary data of the master to its relay log and return the data to its own data, to replicate the data of the master server.

Master-slave replication can be used for database load balancing, database backup, read/write splitting, and other functions.
2. Configure master server
Modify my. ini/my. conf
[Mysqld]
Log-bin = mysql-bin // enable binary log
Server-id = 102 // unique server ID
3. Configure the slave server slave
Log-bin = mysql-bin // enable binary log
Server-id = 226 // unique server ID
4. Authorize the slave server on the master server
Grant replication slave on *. * to 'slavename' @ 'IP' identified by 'root'
5. Use on the slave server
Change master
Master_host = "masterip ",
Master_user = "masteruser ",
Master_password = "masterpasswd ";
6. Run the start slave command to start master-slave replication.
Do not forget to restart the server after each configuration modification. Then, you can use show master/slave status on the master/slave server to view the master/slave status.
Database read/write splitting depends on MySQL middleware, such as mysql_proxy and atlas. By configuring these middleware, you can perform read/write splitting on the Master/Slave server, so that the slave server is responsible for reading the data, thus reducing the burden on the master server.

Database sharding
When the data volume in a data table in a database is very large, the pressure on the database, such as indexing and caching, is very high, so that the database is sharding, so that it is stored in multiple database servers or multiple tables, respectively, to reduce the query pressure.
Methods include vertical splitting, horizontal splitting, and joint splitting.
Vertical Split: When there are many data tables, split the tables that are closely related to the database (such as the same module and frequently connected to the query) and place them on different Master/Slave servers.
Horizontal segmentation: When there are not many tables and the data volume in the table is very large, you can use hash and other algorithms to divide a data table into several tables and put them on different servers to speed up the query. The difference between horizontal splitting and Data Table Partitioning lies in the differences in storage media.
Joint splitting: In more cases, if the data volume in both the data table and the table is large, joint splitting is required, that is, vertical and horizontal table sharding is performed simultaneously, and the database is split into a distributed matrix for storage.
Each of these database optimization methods can be used to write an article, which is profound and profound. After understanding and memorizing these methods, you can perform purposeful selection and optimization when necessary, to achieve database efficiency.

In the next blog, I will summarize the basic usage scenarios and usage of memcache, redis, and mongodb for common PHP database class extensions.

If you think this blog is helpful to you, you can recommend or follow me. If you have any questions, leave a message below to discuss them. Thank you.