PHP core technologies and practices-PHP and database basics

PHP core technologies and practices-PHP and database basics

1. PDO (php data object) and PHP support almost all databases on the market, but the abstract access interfaces are not uniform. Therefore, PDO has emerged and provides a common interface to access multiple databases, that is to say, the abstract database model supports connecting to multiple databases. PDO extension is only an abstraction layer that does not support database operations. a specific database PDO driver must be used to access the database, in terms of syntax, PDO is closer to MySQLi. if I have learned PDO before, I will not take this note. I will write only one thing that I have never learned before.
2. PDO efficiency: the CRUD efficiency of PDO is about 5%-15% lower than that of MYSQL direct connection, and the variance is greater than that of MYSQL direct connection. if the project requires strict operation efficiency, it should be MYSQL, MYSQLI, and PDO load, after the persistent connection is enabled, the load is higher than that of MYSQL and is relatively stable. the speed of PDO connection to MYSQL/ORACLE is better than that of direct connection.
3. 10 basic statement optimization principles for database application optimization: 1. avoid column operations as much as possible, which will cause Index failure. select * from t where YEAR (d)> = 2011 optimized to select * from t where d> = '2017-01-01 '2. when using JOIN, the application uses a small result set to drive a large result set. At the same time, complicated JOIN queries are split into multiple queries, because joining multiple tables may cause more locking and blocking. 3. pay attention to the use of LIKE fuzzy queries to avoid % 4. only list the fields to be queried. this will not significantly affect the speed, and the internal savings are mainly considered. 5. use refers to insert statement to save interaction 6. when the limit base is large, between is used, and the between limit is faster than limit. Therefore, we recommend that you replace limit with between or where when accessing massive data, but the defect of between is that if there is a broken line in the middle or some data is not read, the total number of reads will be less than the expected number! When comparing the subsequent data, use the desc method to retrieve the data, so as to reduce the scanning of the previous data, so that the smaller the limit base, the better. 7. do not use the rand function to obtain multiple random records. avoid using NULL9. do not use count (id) instead of count (*) 10. do not perform unnecessary sorting operations and try to complete sorting in the index.
4. Index and performance analysis execution efficiency: set @ profiling = 1; select * from tableName where condition; show profiles; or show profile for query n; obtain the detailed performance report of a query to locate the performance bottleneck. The second query of the same statement is faster than the first query because of the SQL cache results. Basic principles for creating and using MYSQL indexes: design and use indexes rationally on the index of key fields, the index creation speed is nearly 100 times different from the index creation speed, and the index maintenance cost is the same as that without the index effect. the more the index is, the better the index is. each table should have less than five indexes, you can use some of the indexes and the combined indexes to create an index on a column with a single result in the result set, for example, if the gender is only 0 and 1, creating an index on this field does not have many results that help index creation. The result set should be evenly distributed or conform to the regular distribution.
5. Server and Configuration Optimization MYSQL common engine comparison --------------- MyISAM ------- Memory ----------------- InnoDB purpose ---------- fast read -------- Memory data ------------ complete transaction support lock ---------- full table lock ---- full table lock ------------ multiple isolation levels row lock persistence -------- table-based recovery --- no disk IO no durability --- log-based recovery transaction support ------ not supported ------- not supported ------------ supported index type --- B-tree/FullText/R- tree --- Hash/B-tree