How to improve the cache hit rate and database query efficiency of PHP and MySQL through indexes?

How to improve the cache hit rate and database query efficiency of PHP and MySQL through indexes?

Introduction:
When developing websites and applications, PHP and MySQL are commonly used combinations. However, in order to optimize performance and improve user experience, we need to focus on the efficiency of database queries and cache hit rates. Among them, indexing is the key to improving query speed and cache efficiency. This article will introduce how to improve the cache hit rate and database query efficiency of PHP and MySQL through indexing, and give specific code examples.

1. Why use indexes:
The index is a data structure in the database table, used to improve the speed of queries. It creates an index on a column or multiple columns so that the database can quickly locate data that meets the query conditions. Using indexes can greatly reduce database query time and improve system response speed.

2. How to create an index:
In MySQL, you can specify the index column when creating the table, or create an index on an existing table. Here are some ways to create an index:

1. Specify the index when creating the table:

   CREATE TABLE `user` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(50) NOT NULL,
     `email` varchar(50) NOT NULL,
     PRIMARY KEY (`id`),
     INDEX `idx_name` (`name`),
     INDEX `idx_email` (`email`)
   ) ENGINE=InnoDB;

   Copy after login

   In the above example, a file named user## is created # table, and indexes are created on the name and email columns.

2. Create an index on an existing table:

   ALTER TABLE `user` ADD INDEX `idx_name` (`name`);

   Copy after login
   The above example creates an index named ## on the existing

   user table Index of #idx_name.

3. Use indexes to optimize queries:

Filter query results:

1. SELECT * FROM `user` WHERE `name` = 'John' AND `email` = 'john@example.com';

   Copy after login

   In the above example,

   name

   and email An index is created on the column to quickly locate data that meets the conditions.

Sort query results:

2. SELECT * FROM `user` ORDER BY `name`;

   Copy after login

   In the above example, an index is created on the

   name

   column, which can speed up the sorting process of query results.

Connection query:

3. SELECT * FROM `user` INNER JOIN `order` ON `user`.`id` = `order`.`user_id`;

   Copy after login

   In the above example, an index is created on the connection field

   user_id

   , which can speed up the connection process of the data table.

4. Use cache to improve hit rate:

In PHP, you can use cache plug-ins (such as Memcached, Redis, etc.) to save query results. When the same query results are needed next time, they can be obtained directly from the cache without accessing the database again.
1. 
   Set cache expiration time:
For some data that does not change frequently, you can set the cache expiration time. When the cache expires, new data is retrieved from the database and the cache is updated.
2. 
   
Code example:

<?php
// 使用缓存插件
$cache = new Memcached();
$cache->addServer('localhost', 11211);

// 查询缓存
$result = $cache->get('user:1');
if (!$result) {
    // 从数据库获取数据
    $result = $db->query("SELECT * FROM `user` WHERE `id` = 1")->fetch();
    // 将查询结果保存到缓存
    $cache->set('user:1', $result, 3600);
}

// 使用缓存的查询结果
echo $result['name'];
?>

The above code example uses the Memcached cache plug-in to save the query results to the cache and set a validity period of 1 hour. When the same data is queried next time, it can be obtained directly from the cache, thereby improving the system's response speed and cache hit rate.

Conclusion:

The above is the detailed content of How to improve the cache hit rate and database query efficiency of PHP and MySQL through indexes?. For more information, please follow other related articles on the PHP Chinese website!