Indexes are the main means to accelerate queries, especially for queries involving multiple tables. This section describes the functions and features of indexes and the syntax for creating and deleting indexes.
13.4.1 optimize queries using IndexesIndexing is a technology for high-speed data locating. First, we use a demo to understand its meaning and functions. For details, refer to Chapter 14th.
1. Index demoIf no indexes are available for the tables created in section 10.3, the data is arranged irregularly, as shown in table 13.3. Table 13.3 students without Indexes
| Sid |
Sname |
Sgender |
Sage |
| 52 |
Zhang |
M |
21 |
| 22 |
Wang |
M |
22 |
| 33 |
Li |
F |
19 |
| 41 |
Zhao |
M |
20 |
| ... |
... |
... |
|
When searching for information about a student, you must view each row in students in sequence to check whether the row matches the required value. This requires scanning the entire table, which is very inefficient. Table 13.4 shows how to add? Students table indexed. Table 13.4 is added to the name column? The index of the Students table is sorted by name. Nowadays, when searching for information about a student, you do not need to search for the full table row by row. You can use the index to perform an ordered search (such as the binary search method) and quickly locate the matched value, to save a lot of search time.
2. IndexingIn addition to the ordered search mentioned above, the database uses a variety of high-speed positioning technologies to greatly improve query efficiency. In particular, when the data volume is large and the query involves multiple tables, using indexes often speeds up the query by thousands of times. For example, there are three unindexed tables T1, T2, and T3, which only contain columns C1, C2, and C3. Each table contains 1000 rows of data, which is 1 ~ The value of 1000 to query the rows with equal values, as shown in the following figure. Select C1, C2, C3 from T1, T2, T3 where c1 = C2 and C1 = C3 this query result should be 1000 rows, each line contains three equal values. To process this query without an index, you must find all the combinations of the three tables to obtain the rows that match the WHERE clause. The number of possible combinations is 1000x1000x1000 (Billions), and the query will obviously be slow. If each table is indexed, the query process can be greatly accelerated. The following is an example of index Query Processing. (1) Select the first row from Table T1 to view the data contained in this row. (2) Use the index on table T2 to directly locate the row that matches the value of T1 in Table T2. Similarly, using the index on table T3, you can directly locate the row in Table T3 that matches the value from t1. (3) scan the next row of table T1 and repeat the previous process until all the rows in Table T1 are traversed. In this case, a full scan is still performed on table T1, but you can perform index search on table T2 and table T3 to directly retrieve the rows in these tables, which is 1 million times faster than when no index is used. Using indexes, MySQL accelerates the WHERE clause's search for rows that meet the condition conditions. In multi-table join queries, MySQL accelerates row matching in other tables during connection execution.
13.4.2 create an indexYou can create an index when running the create table statement, or use the create index or alter table statement to add an index to a table? Index.
1. ALTER TABLEAlter table is used to create a common index, a unique index, or a primary key index. Alter table table_name add index index_name (column_list) alter table table_name add unique (column_list) alter table table_name add primary key (column_list) Where table_name is to be added? The name of the index table. column_list indicates which columns are indexed. Multiple columns are separated by commas. The index name index_name is optional and is time-saving. MySQL assigns a name based on the first index column. In addition, alter table agrees to change multiple tables in a single statement, so Multiple indexes can be created at the same time.
2. Create IndexCan create index be added to a table? Common Index or unique index. Create index index_name on table_name (column_list) create unique index index_name on table_name (column_list) table_name, index_name, and column_list have the same meaning as in the alter table statement, and the index name is not optional. In addition, you cannot use the create index statement to create a primary key index.
3. Index typeWhen creating an index, you can specify whether the index can include repeated values. If not, the index should be created as a primary key or unique index. For a single column uniqueness index, this ensures that the single column does not include repeated values. For multi-column uniqueness indexes, make sure that the combination of multiple values is not repeated. The primary key index is similar to the unique index. In fact, the primary key index is only a unique index with the name primary. This indicates that a table can only contain one primary key, because a table cannot have two indexes with the same name. Which of the following SQL statements add to the SID of the Students table? Primary Key Index. Alter table students add primary key (SID)
13.4.3 delete an indexYou can use the alter table or drop index statement to delete an index. Similar to the create index statement, drop index can be processed as a statement in alter table. The syntax is as follows. Drop index index_name on talbe_namealter table table_name drop index index_namealter table table_name drop primary key, the first two statements are equivalent, delete the index index_name in table_name. The first statement is only used to delete the primary key index. Because a table may only have a primary key index, you do not need to specify the index name. If no primary key index is created, but the table has one or more unique indexes, MySQL deletes the first unique index. If a column is deleted from the table, the index is affected. For an index composed of multiple columns, if a column is deleted, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.
Create and delete Indexes
