Hello, I am Wukong.
The directory of this article is as follows:
1. Foreword
Recently, I have been sorting out the core knowledge of MySQL, and I just happened to sort out the knowledge related to MySQL indexes. Many of my articles are based on principles + practical methods to help you understand the knowledge points, so this article is also the same.
Of course, there are still many knowledge points about indexing. This article explains the basic knowledge of indexing. It does not involve the underlying principles of indexing or how to view the execution plan. It will be divided into multiple articles to explain, please continue to pay attention~
2. Index VS Library
Imagine a scenario where you are now a librarian and your daily job is to put returned books back into their original places. If someone wants to find a certain book, they can first find the bookshelf location of the book through the book’s category, book number, etc., further narrowing the scope.
If there is no librarian in the library and the books on the bookshelves are arranged irregularly, then if someone wants to find a book, they have to start from scratch. Whether they can find it or not depends entirely on luck.
If you have been to a library, you should know the library’s search system. The library has prepared a search catalog for books, including the book title, book number, and corresponding location information, including which section, which bookshelf, and which floor. We can quickly know the location of the book through the book title or book number, and get the book we need.
The index in MySQL is equivalent to the search catalog of the library. It is a storage structure that helps the MySQL system quickly retrieve data. We can retrieve the value of the index field in the index according to the query conditions, and then quickly locate the location of the data record, so there is no need to traverse the entire data table. Moreover, the more fields and data records in the data table, the more obvious the speed improvement.
3. What is an index
The English name of index is Index, which is a data structure.
Data structures are the way computers store and organize data. A good data structure can bring higher operating or storage efficiency. Data is arranged linearly in memory, but we can use pointers and other props to construct a complex structure similar to a “tree shape”.
Data structures are divided into two categories: linear and nonlinear, and there are eight major types. For example, linear data structures include arrays, linked lists, stacks, and queues.
Nonlinear data structures include trees, heaps, hash tables, graphs, etc.
So which one of the indexes in MySQL is it? It is a tree data structure, and it is a B+ tree, as shown in the figure below, but the tree in the figure is an upside-down tree, with its root at the top.
B + tree
So how does the B+ tree store data?
We can open this website and take a look.
Imagine we randomly insert some numbers into a data table:
2, 5, 8, 100, 20
Similar to how we randomly arrange the books in the library into the bookshelf, let’s see through the animation demonstration how the B + tree stores, searches and deletes these numbers according to its data structure.
**
Wukong Chat Architecture
, like 30
B + tree animation demonstration
4. Advantages and disadvantages of MySQL index
Advantages
Advantage 1: Reduce database I/O costs
This is actually to reduce the time spent reading and writing data in the database.
If you were asked to find a specific book from a pile of messy books, would you have to look at them one by one to see if the title written on the cover is correct?
With the index, you don’t need to look at the cover of each book. You can quickly get to that book, which reduces a lot of ineffective searches.
Advantage 2: Ensure data uniqueness
By creating a unique index, you can ensure the uniqueness of each row of data in the database table. Note that this is a unique index. Use the keyword UNIQUE to create a unique index.
For example, each employee ID in the employee table is unique.
Advantage 3: Improve the efficiency of multi-table joint queries
Whether it is a single table query or a multi-table query, indexes can improve query efficiency.
Everything has two sides. Indexes have advantages and disadvantages. So what are the disadvantages of indexes?
Disadvantages
Disadvantage 1: Creating and maintaining indexes takes time
Just like borrowing and returning books in a library requires maintenance by a librarian. If no one takes care of them for a long time, won’t the books become messy again?
Disadvantage 2: Indexes require disk space
Just like the library needs to store the location information of each book in a piece of data. If it is stored in a computer, it will occupy the computer’s hard drive space. If it is stored in paper documents, it will occupy a room. Space.
Disadvantage 3: Reduces the speed of updating tables
Just like before a library puts a new book on the bookshelf, the administrator needs to check the location of the book before putting it on the bookshelf. This query process will take a certain amount of time.
5. Experience index accelerated query
As mentioned earlier, indexes have many advantages, and the main reason is to improve query speed. Then let’s take a look at the query speed in two scenarios: no indexing and indexing.
First you have to create a table, and then insert a lot of data into the table, right?
Create student table
I have created a student table here:
Field description:
-
id: The id of this record, which is also the primary key id, is unique, which means that each record is unique.
-
stu_no: student number, a self-increasing number when inserting sample data
-
stu_name: student name, a random combination of English letters when inserting sample data
-
age: student age, the age will be randomly distributed when inserting sample data
-
classId: class id. The class id will be randomly distributed when inserting sample data.
Insert 3 million data
Now that the table is created, we need to insert a large amount of data into the table. Here I will directly use the script I wrote to insert 3 million data.
Testing without indexing
So if I want to find the student’s record based on a certain student number stu_no, how should I query it?
The query script is as follows:
Now there is no index added to the student table. Let’s take a look at its query speed.
How to count the time it takes for script execution? Because I am currently using the workbench graphical management tool, I can use this tool to view the execution time:
You can see that it took 0.47s to query this data. You can also see from the query plan that this query scanned the entire table. That is to say, querying stu_no = '555555' records this record from Starting from the first line of the record, scan line by line to see which record has stu_no = '555555'. This query method is very slow, especially from such a large amount of data. Find according to quantity.
Testing indexing
Add index
What will happen if we add the index to the field stu_no we want to query?
Indexing can be done directly through the workbench tool or through a script.
Workbench tool adds index
Script to add index
ALTER TABLE `test`.`student` ADD INDEX `index_stu_no` (`stu_no` ASC) VISIBLE;
Test the query speed after adding the index
After adding the index, the query only takes 0.0013s, as shown in the figure below:
Let’s take a look at its execution plan:
You can see that index search is used to directly locate that row of data through the index.
With the index, MySQL has an additional optimization method when executing SQL statements.
In other words, when querying, you can quickly locate it through the query index, and then find the corresponding data for reading, which greatly improves the query speed.
6. How to create an index
At work, we usually write SQL scripts to create indexes, and then submit the scripts to the code repository. This makes it easier to maintain SQL scripts and indexes.
What does the script to create an index look like? Are there any grammatical requirements?
Syntax for creating an index
There are three ways to create an index:
Create an index when creating a table
grammar:
CREATE TABLE table name(
field data type,
field data type,
...
{ INDEX | KEY } index name (field 1, field 2,...)
)
Example: When creating the member table, create an index uk_idx_id, and the field is id.
CREATE TABLE member (
id INT NOT NULL,
name VARCHAR(30) NOT NULL,
INDEX uk_idx_id (id)
);
Create indexes directly on the data table
grammar:
CREATE INDEX index name ON table name ( Field 1, Field 2, ... )
Example: Create an index index_name with the field name.
CREATE INDEX index_name ON member ( name );
Syntax for adding index to update table
ALTER TABLE table name
ADD { INDEX | KEY } index name (field 1, field 2,...);
Example: Create a joint index index_id_name with fields id and name.
ALTER TABLE member ADD INDEX index_id_name (id, name);
7. Index classification
MySQL’s indexes include ordinary index, unique index, full-text index, single column index, multiple columns Index and Spatial Index etc.
From a functional logic perspective, there are four main types of indexes, namely ordinary index, unique index, primary key index, and full-text index.
According to the physical implementation method, indexes can be divided into two types: Clustered index and Non-clustered index.
Divide according to the number of active fields and divide it into single column index and joint index.
Clustered index (primary key index) features
-
The primary key is used as an index, and the leaf nodes of the B + tree store complete user records.
Non-clustered index (secondary index, auxiliary index) features
Table return query: First locate the primary key value on the ordinary index, and then locate the row record on the clustered index. Its performance is lower than scanning the index tree (under normal circumstances).
Detailed description:
Generally, the indexes we build ourselves, whether they are single-column indexes or joint indexes, are called ordinary indexes, and the corresponding other type is a clustered index. Each ordinary index corresponds to an independent index B + tree. The nodes of the index B + tree only contain the values of several fields in the index and the primary key value.
The required data is found according to the conditions according to the index tree. It is only the values of several fields and primary key values in the index. If you use select *, you need many other fields, and you have to perform a table return operation, and then go to the table according to the primary key. Find it in the clustered index of the primary key. The leaf node of the clustered index is the data page. Only by finding the data page can you extract all the field values of a row of data.
Suppose there is a statement select * from table order by a,b,c (table has 6 abcdef fields), first it must be taken out from the index tree of the joint index in order a, b, c All data, and then each piece of data is searched based on the primary key to the clustered index. In fact, the performance is not high.
Features of joint index (secondary index, combined index)
-
Index multiple columns simultaneously.
8. Creating different indexes will speed up queries
Creating a clustered index will speed up queries
When we created the student table before, we also added a primary key index (clustered index) with id as the index field, so let’s see how fast it is to query using the primary key id. If you have not added a primary key index to this table before, you can add it through this script:
ALTER TABLE `test`.`student` CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`);
You can see in the execution plan that the constant method is used directly, which means that the query directly finds that record, and the speed is very fast.
Then we delete the primary key index and look at the query time.
Delete the primary key index first:
ALTER TABLE `test`.`student` CHANGE COLUMN `id` `id` INT NOT NULL, DROP PRIMARY KEY;
The query took 0.6 seconds.
Moreover, viewing the execution plan requires a full table scan, which is very time-consuming.
Creating a common index will speed up queries
In Section 5 of this article, the query effect has been demonstrated by creating a normal index on the stu_no student number. The index also speeds up the query.
Creating a joint index will speed up queries
Without indexing, it takes 0.46 seconds to query students with age = 15 and class ID = 20.
Create a joint index on the age and classId fields on the student table:
CREATE INDEX index_age_class_id ON test.student ( age, classId )
Check for phrases:
SELECT
*
FROM
test.student
WHERE
age = 15 AND classId = 20
It took 0.014 seconds.
0.46 seconds reduced to 0.014 seconds, a 30x increase in speed.
Summary
This article explains what MySQL indexes are, their advantages and disadvantages, MySQL index classification, and how to create MySQL indexes through scripts. Finally, it demonstrates how different types of indexes speed up queries.
In the next MySQL article, we will talk about MySQL indexes.
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 138777 people are learning the system