Database in php I. MySQL optimization policy overview _ MySQL

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 PHP running efficiency, programmers not only need to write code with clear logic and high efficiency, but also read an article about qu a few days ago saying that the PHP bottleneck is not in PHP itself in many cases, but in 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 recommended that keywords in SQL statements be written in uppercase. First, it is easy to distinguish between keywords and Operation objects. second, MySQL converts SQL statements into uppercase statements during execution, manual writing in uppercase can increase the query efficiency (although small ).

2. if we add or delete rows in the database, the data ID will be too large. use alter table tablename AUTO_INCREMENT = N to count the auto-increment IDs from N.

3. add the ZEROFILL attribute to the int type to 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/path/mysql-uusername-ppassword databasename <filename. SQL to 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. when creating a table, you can use the partition command to query a single table partition. MySQL supports the RANGE, LIST, HASH, and KEY partition types, which are most commonly used in RANGE mode:

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. when selecting a database engine, pay attention to the differences 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 generate a primary index, the 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, you do not need to use the first condition and the second condition will not use the union index. 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 should be solved in a timely manner. Generally, the slow query log records the slow query statement, and the EXPLAIN statement is used to analyze the query and index usage, use PROFILE to analyze 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

Type to obtain data types. common types of efficiency from high to low are null> const> eq_ref> ref> range> index> all.

Possible-keys: possible indexes

Index used by key

Key_len index length

The column used by ref and the index are selected from the table.

Rows finds the approximate number of rows of data to be scanned, which shows the advantages and disadvantages of the index.

Extra common

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 qurey Query_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 in the database that are closely related (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, but the data volume in the table is very large, in order to speed up the query, you can use hash and other algorithms to divide a data table into several, put them on different servers, accelerate query. The difference between horizontal splitting and data table partitioning lies in the differences in storage media.

Joint splitting: in more cases, when the data volume in the data table and table is large, joint splitting is required, that is, vertical and horizontal table sharding are performed at the same time, split the database 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.