PHP mysql optimization

The content introduced in this article is about PHP mysql optimization, which has certain reference value. Now I share it with you. Friends in need can refer to a few

## MySQL tips

• 1. Keywords in SQL statements are best written in

  uppercase , firstly, it is easy to distinguish keywords and operation objects, and 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 , Make the auto-increment ID start counting 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 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 by using

  /path/mysql -uusername -ppassword databasename in the current directory to execute the sql statement of the entire file (note that -u and -p are followed by the username and password without spaces).

---

##Optimization of database design1 , The database design conforms to the third paradigm, and there can be a certain amount of 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 using the ip2long() function. to store.
3. For char(n) type, try to keep the n value as small as possible when the data is complete.
4. When creating a table, use the partition command to partition a single

table , which can greatly improve query efficiency. MySQL supports RANGE, LIST, HASH, and KEY partition types, of which RANGE is the most popular. Commonly used, the partitioning method is: CREATE TABLE tablename{}ENGINE innodb/myisam CHARSET utf8 //Select database engine and encoding
PARTITION BY RANGE/LIST(column), //By range and predefined list Partitioning
　PARTITION partname VALUES LESS THAN /IN(n),//Name the partition and limit the scope of the partition in detail
5. Pay attention when selecting the database engine

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: MyISAM does not support it. InnoDB supports

---

Index optimization1. InnoDB is a clustered index, and 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, and the data is stored by the primary key. Every time the index is used You need to find the main index first, and then find the data under the main 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. Therefore, if a multi-condition query is required, a joint index must be established, even if Cause data redundancy.
BTREE creation method of 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 the index, Without the first condition, the second condition will not be used, and the joint index will not be used. When using indexes, conditions must be in order and used in sequence.
4. The index length also has a great impact on the query. We should try to build a short index length. We can use the query column
SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename to test the query. Different lengths are selected when creating an index for the column column. How big is the coverage of the index? Let's choose n lengths that are close to saturation to create an index.
ALTER TABLE tablename ADD INDEX (column(n)); To index the front of a certain column n characters to create an index. If the first n characters are the same, we can even reverse the string and store it, and then create an index.
5. How to maintain index fragmentation caused by frequent modifications: ALTER TABLE tablename ENGINE oldengine; that is, apply the table storage engine again to enable automatic maintenance; you can also use the OPTIMIZE tablename command for maintenance.

---

Optimization of data query
Try to keep as few queries as possible in database operations, and try not to do so at the database level when there are queries. Instead of performing data operations on the database, return to the PHP script to operate the data to reduce database pressure.
Once a database performance problem is discovered, it must be solved in time. Generally, slow query is used. Log records query is very "slow" statement, use EXPLAINAnalyze query and index usage, use PROFILE to analyze the specific resource consumption during statement execution.
Slow query log:
1. Add
slow_query_log_file=/ under [mysqld] in my.ini or my.cnf path //Set the log storage path
long_query_time=n //Set if the statement execution time reaches n seconds, it will be recorded
2. Then set SET slow_query_log='ON' in MySQL to enable slow query.
3. After recording the log, we use mysqldumpslow filename in the /bin/ directory to view the log. The commonly used parameters are as follows:
　-g pattern uses regular expressions
　-t nReturn the first n pieces of data
　-s c/t/l/r Sort by the number of records/time/query time/number of returned records

EXPLAIN statement
Usage method, Add EXPLAIN
EXPLAIN SELECT * FROM user;
in front of the query statement to be executed to get the result as shown below:

The following is an explanation of each item:
id is the id of the query statement. A simple query is meaningless. When using multiple queries, you can see the order in which the queries are executed.
select-type is the type of query statement to be executed, corresponding to multiple queries, including simple/primary/union, etc.
tabel The data table queried by the query statement
type The type of data obtained. The common type efficiency from high to low is null>const>eq_ref>ref>range>index> ;all
possible-keys:Possibly used index
key Used index
key_len index length
ref Which column to use with the index Select from table.
rows The approximate number of rows to be scanned to find the data can be used to see the quality of the index
extra Common There are
using filesort to sort the files after querying the data, which is slow and needs to optimize the index
using where to read the entire row of data and then judge and filter whether it meets the where condition
using index index coverage, that is, in The target data is already stored in the traction, and the index is read directly, which is very fast.

PROFILE
Use SELECT @@frofiling to check the opening status of PROFILE.
If it is not enabled, use SET profiling=1 to enable it.
After turning it on, MySQL will automatically record the profile information when executing the query statement.
Apply show profiles to view all sql information. The result is three column results of Query_ID Duration Query, which are query ID, time and 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
Database master-slave replication and read-write separation
1. The master will record the changes in the binary log, and the slave will record the changes of the master. The binary is copied to its relay log and the data is returned to its own data to achieve the purpose of replicating the main server data.

Master-slave replication can be used for: database load balancing, database backup, read-write separation and other functions.
2. Configure the main server master
Modify my.ini/my.conf
[mysqld]
Log-bin=mysql-bin //Enable binary log
Server-id=102 / /Server unique ID
3. Configure slave server slave
Log-bin=mysql-bin //Enable binary log
Server-id=226 //Server unique ID
4. On the master server Authorization from the server
　GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'
5. Use on the slave server
　Change master to
　master_host="masterip" ,
　　master_user="masteruser",
　　master_password="masterpasswd";
6. Then use the start slave command to start 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 in different places. on the master-slave 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 Divide it 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: More often than not, the data table and the amount of data in the table are very large, so joint segmentation is required, that is, vertical and horizontal segmentation are performed at the same time. Table, 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 memorize these methods, you can perform purposeful selection and optimization when necessary to achieve high database efficiency. .

Related recommendations:

Use of php optimization session

PHP learning route and 10 PHP optimization tips

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