MySQL database technology (20) [group chart] _ MySQL

MySQL database technology (20) [group chart] the world of relational databases is a world dominated by operations on tables and sets, tables, and sets. A database is a collection of tables, and a table is a collection of rows and columns. When a SELECT query is published to retrieve rows from the table, a set of rows and columns is obtained. These are some abstract concepts, and there is little reference value for the basic representation of the data used by the database system to manipulate the table. Another abstract concept is that all operations on a table are performed simultaneously. query is a conceptual set operation without the concept of time in the set theory. Of course, the real world is quite different. The database management system implements abstract concepts, but is subject to physical constraints within the actual hardware scope. The result is that the query takes a long time. Humans are impatient and don't like waiting, so we leave the abstract world of instantaneous mathematical operations on the set to seek methods to accelerate queries. Fortunately, there are several technologies that accelerate computation, allowing you to index tables to make the database server search faster. You can consider how to make full use of these indexes to write queries. You can write queries that affect the server scheduling mechanism so that queries from multiple clients can collaborate better. We thought about how the basic hardware runs to figure out how to overcome its physical constraints to improve performance.

These are the issues discussed in this Chapter. The goal is to optimize the performance of the database system so that it can process various queries as quickly as possible. MySQL is already quite fast, but even the fastest database can run faster with human design.

4.1 use indexes

We will first discuss the index, because it is the most important tool to speed up the query. There are other technologies for accelerating queries, but the most effective one is to properly use indexes. On the MySQL mail list, people usually ask questions about faster queries. In many cases, because there is no index on the table, the problem can be solved immediately by adding an index. But this is not always effective, because optimization is not always that simple. However, if indexes are not used, in many cases, it is a waste of time to improve performance by other means. You should first consider using indexes to achieve maximum performance improvement, and then seek other technologies that may be helpful.

This section describes what an index is, how it improves query performance, when the index may degrade performance, and how to select an index for a table. In the next section, we will discuss the MySQL Query optimization program. In addition to how to create an index, it is also good to know some optimization procedures, because it can make better use of the created index. Some methods for writing a query will actually impede the indexing effect, so this situation should be avoided. (Though not always. Sometimes you want to ignore the role of the optimization program. We will also introduce these situations .)

4.1.1 Benefits of indexes

Let's start with an index-free table to check how indexes work. A table without indexes is a disordered row set. For example,-1 shows the ad table we first saw in chapter 1st "MySQL and SQL introduction. There is no index on this table. Therefore, if we look for a row in a specific company, we must check each row in the table to see if it matches the required value. This is a full table scan, which is very slow. if there are only a few records in the table that match the search criteria, the efficiency is quite low.