PHP core technologies and practices-PHP and database basics (2)-php Tutorial

PHP core technologies and practices-PHP and database basics (2)

1. MYSQL bottlenecks and countermeasures

   1) increase the buffer and cache values in MYSQL configurations, increase the number of server CPUs and memory size, which can greatly cope with MYSQL performance bottlenecks, in performance optimization, hardware and servers are the most cost-effective;

   2) use a third-party engine or derivative version;

   3) migrate to other databases;

   4) perform database partitioning and table sharding to reduce the volume of a single table;

   5) use NoSQL and other auxiliary solutions, such as Memcached and Redis;

   6) use middleware for data splitting and distributed deployment;

   Finally, whether a tool can be used properly accounts for a large proportion of people.

2. Database Design

   (1) paradigm and anti-paradigm: the theory of DB paradigm has been gradually formed in the development of DB theory to standardize DB. Up to now, there have been five paradigms. By now, the third paradigm can meet business needs, and the relationships between tables are also clear and easy to maintain.

   (2) proposal of the anti-paradigm: the paradigm theory was proposed in 1970s and basically finalized at 80 lending. at that time, the system features were: the available storage resources were extremely limited, in addition, the immature networks usually involve only the computing performance of a single machine. Therefore, the paradigm theory emphasizes reducing dependencies and reducing redundancy; however, currently, low-cost storage is no longer a problem and requires high-concurrency business logic to be complex and low-latency. Therefore, we should not blindly follow the paradigm design theory and appropriately reduce the paradigm, it is worthwhile to increase redundancy and change the time to space. the minimum value can be reduced to the first paradigm.

   (3) the following principles should be followed when designing a database:

   1) core business usage paradigm.

   2) weak consistency requirement-anti-paradigm

   3) space change time

   4) avoid unnecessary redundancy

3. Database partition

   Partitions are used to distribute the files and indexes of a data table in different physical files. MySQL and later versions support partitions. You can use show variables like '% partition %' to check whether partitions are supported. MYSQL supports Range, List, Hash, and Key partitions. Range is the most commonly used partition type. for example:

   Create table foo (id int not null AUTO_INCREMENT, created DATATIME, primary key (id, created) ENGINE = innodb partition by range (TO_DAYS (created )) (PARTITION foo_1 values less than (TO_DAYS ('1970-01-01 '), PARTITION foo_2 values less than (TO_DAYS ('1970-01-01 ')))

MYSQL saves data to different data files through partitions, and indexes are also partitioned. compared with unpartitioned tables, the size of separate data files and index files after partitioning is significantly reduced, the efficiency is significantly improved. A query/write operation is only executed on the corresponding partition. When partitions are used, data directory and index directory are used to distribute different partitions to different disks to further improve the system's IO throughput. After using the partition, it is best to use the explain partitions once to confirm whether the partition takes effect.

Generally, Range partitions are used, but in the master-slave structure, the master server rarely uses the SELECT statement to query the Range. in this case, Hash partitions are better: partiton by hash (id) PARTITION 10. when data is inserted, the data is evenly distributed to each PARTITION based on the ID. because the file is small/efficient, the update operation will become faster. It is usually partitioned by the time field, but the specific situation depends on the demand.

Although the partition is good, there are still many restrictions on the current implementation, such:

PRIMARY keys or unique indexes must contain partition fields, such as primary key (id, created). However, for InnoDB, the performance of large PRIMARY keys is poor.

In many cases, you do not need to use primary keys when using partitions. otherwise, performance may be affected.

Partitions can only be performed by using INI fields or by returning INI expressions. functions such as YEAR or TO_DAYS are generally used.

Each table can have a maximum of 1024 partitions, and partitions cannot be expanded without restriction. excessive use of partitions will consume a large amount of system memory.

Partitioned tables do not support foreign keys. the related constraint logic must be implemented in the code.

The index may be invalid after Partitioning. you need to verify the partition feasibility.