Database in php I. MySQL optimization policy overview

Database in php I. MySQL optimization policy 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. Tips for MySQL: 1. It is best to write keywords in SQL statements in uppercase. First, it is easy to differentiate keywords and operation objects. Second, MySQL converts SQL statements into uppercase when executing SQL statements, 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. Adding the ZEROFILL attribute to the int type 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 spends a lot of time updating indexes. 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 that data redundancy can be ensured for ease of 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 predefined list PARTITION partname values less than/IN (n ), // name the partition and specify the partition range in detail. 5. When selecting a database engine, pay attention to the differences between innodb and myisam. Storage Structure: MyISAM stores three files on the disk. All InnoDB tables are stored in the same data file, which is generally 2 GB transaction support: MyISAM does not provide transaction support. 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 supports 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 the query. We should try to establish a short index length. We can use the query column select count (distinct left (column)/COUNT (*) FROM tablename is used to test the index coverage of a column with different lengths, we will select n lengths close to saturation to create an index alter table tablename add index (column (n); to create an INDEX for 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. Optimize database operations for data query as few queries as possible. When querying data, try not to perform data operations at the database level. Instead, return the data to the PHP script to reduce the database pressure. 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. In my. ini or my. add slow_query_log_file =/path // under [mysqld] of cnf to set the log storage path long_query_time = n // if the statement execution time reaches n seconds, 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 use the regular expression-t n to return the first n data records-s c/t/l/r to sort the number of records/time/query time/number of returned records. EXPLAIN statement is used method, add explain select * FROM user before the query statement to be executed to obtain the result shown in the following figure: the id of the query statement is meaningless, when performing multiple queries, you can see the type of the query statement executed by the select-type query sequence, corresponding to multiple queries, such as simple/primary/union. The type of the data table queried by the tabel query statement obtains the data type. The common type efficiency ranges from high to low. The value is null.> const> eq_ref> ref> range> index> all possible-keys: the index key_len index length that may be used by the index key ref which is used together with the index is selected from the table. Rows finds the approximate number of rows of data to be scanned. It can be seen that the index is good or bad. extra common examples include using filesort, which sorts files after querying data, which is slow, you need to optimize the index using where to read the entire row of data and then judge and filter whether the where condition using index is satisfied. That is, the target data is stored in the pull and the index is directly read, soon. Use SELECT @ frofiling to view the Enabled state 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] type (ALL) block io (display IO overhead) CPU (CPU overhead) MEMORY (MEMORY overhead) and other large storage aspects optimize database master/Slave replication and read/write separation 1. The master will change the records to the binary log, slave copies the binary data of the master to its relay log and returns 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 the master server to modify my. ini/my. conf [mysqld] log-bin = mysql-bin // enable the binary log server-id = 102 // unique server ID 3. Configure slave server slave log-bin = mysql-bin/ /enable the binary log server-id = 226 // unique server ID 4. Authorize the SLAVE server grant replication slave on * ON the master server *. * to 'slavename' @ 'IP' identified by 'root' 5. Use change master to master_host = "masterip", master_user = "masteruser" on the slave server ", 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. When the data volume of database sharding data tables in the database is very large, the pressure on indexes, caches, and so on is high, and the database is sharding, store the data in multiple database servers or tables 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.