"PHP Core technology and practice"-php and Database Foundation (2)

2. MySQL Bottleneck and Countermeasures

   1) Increase the number of buffer and cache in the MySQL configuration, increase the server CPU and memory size, which can deal with the performance bottleneck of MySQL to a great extent, the most significant cost of performance optimization is hardware and server optimization, it is preferred;

   2) use of third-party engines or derivative versions;

   3) migrating to other databases;

   4) The database partition, table operation, reduce the single table volume;

   5) Use of ancillary solutions such as NoSQL, such as Memcached,redis;

   6) Use of middleware for data splitting and distributed deployment;

   Finally, a tool can be used well, the human factor accounted for a large proportion.

4. Database design

   (1) Paradigm and inverse paradigm: for standardizing db design in the course of the development of DB theory, the theory of DB paradigm is gradually formed, so far there are five paradigms. In the third paradigm, which is usually able to meet business requirements, the relationship between tables is relatively clear and easy to maintain.

   (2) Anti-paradigm: The paradigm theory was put forward in the 1970s and in 80 lending basic stereotypes, at that time the characteristics of the system is: The available memory resources are extremely limited, and the network is immature to use the network of people less often only involved in single-machine computing performance, so the paradigm theory emphasizes to reduce dependence/reduce redundancy But now the intrinsic inexpensive storage is not the problem at the same time facing the complex low latency of high concurrency business logic, so should not blindly follow the paradigm design theory, should properly reduce the paradigm, increase redundancy to space for time is worthwhile, the lowest can be reduced to the first paradigm.

   (3) The following principles are typically used when designing a database:

   1) core business usage paradigm.

   2) Weak consistency requirement--inverse paradigm

   3) Space Change time

   4) Avoid unnecessary redundancy

6. Database partitioning

   Partitioning is the distribution of files and indexes of a data table in different physical files, which is supported by MySQL5.1. You can use show VARIABLES like '%partition% ' to confirm that partitioning is supported. The types of partitions supported by MySQL include Range/list/hash/key, where range is most commonly used, examples:

   CREATE TABLE foo (id INT not NULL auto_increment,created datatime,primary KEY (id,created)) ENGINE = INNODB PARTITION by RA NGE (To_days (created)) (PARTITION foo_1 values less THAN (to_days (' 2009-01-01 ')), PARTITION foo_2 values less THAN (to_days (' 2010-01-01 ')))

MySQL saves data to different data files through partitioning, and the indexes are partitioned, and the size of individual data files and index files is significantly reduced and the efficiency is significantly improved when partitioned into partitions. A query/write operation is performed only on the corresponding partition. When you actually apply a partition, the data directory and the index directory options are used to spread the different partitions across different disks to further improve the system's IO throughput. It is best to use explain partitions after partitioning to confirm that the partition is in effect.

A range partition is typically used, but it does not make much sense to use a range query on a master server using the SELECT statement on it, but it is relatively better to use a hash type of partition at this time: Partiton by hash (ID) PARTITION 10, When data is inserted, the data is distributed evenly across partitions based on the ID, and the update operation becomes faster due to the small/high efficiency of the file. It is usually partitioned by Time field, but the specifics are based on demand.

Although partitioning is good, there are many limitations to the current implementation:

The primary key or unique index must contain a partition field, such as primary key (id,created), but for InnoDB, the large primary key does not perform well

There are many times when you use a partition to stop using the primary key or it may affect performance

Can only be partitioned by an INI-type field or an expression that returns an INI type, usually using a function such as year or to_days

Up to 1024 partitions per table, unlimited extended partitions are not possible, and excessive use of partitions consumes large amounts of system memory

The partitioned table does not support foreign keys, and the associated constraint logic must be implemented in the code.

Partitioning may cause indexing to fail and need to verify partition viability

