What is an index? Why build an index?
Index is used to quickly find a specific value in a column of the line, do not use index, MySQL must start from the first record after reading the entire table, until find out the related line, the larger the table, the time it takes to query data, if the query column in the table there is an index, MySQL can quickly reach a location to search data file, You can save a lot of time by not having to look at all the data.
For example, let’s have a Person table with 2W records for 2W individuals. There is a field of Phone to record the Phone number of each person. Now you want to query the information of the person whose Phone number is XXXX. If there is no index, the first record in the table is iterated down until that information is found. If the index is configured, the Phone field is stored in a certain way, so that the corresponding data can be quickly found when the information in the field is queried without traversing 2W data items. ** There are two storage types of indexes in MySQL: BTREE and **HASH. That is, use a tree or Hash value to store the field. To know how to find the field in detail, you need to know the algorithm. We just need to know what the index does and what its function is.
2, MySQL index advantages and disadvantages and use principles
Advantages:
All MySql column types (column types) can be indexed, that is, any column type can be indexed
2, greatly speed up the data query speed
Disadvantages:
1. Creating and maintaining indexes takes time and increases as the volume of data increases
2. Indexes also take up space. We know that the data in the table will also have a maximum limit
3. When the data in the table is added, deleted, or modified, the index also needs dynamic maintenance, which reduces the data maintenance speed.
Principle of use:
Through the advantages and disadvantages mentioned above, we should know that it is not good to set the index for each field degree, nor is it better to have more indexes, but to use them reasonably.
1. Avoid creating too many indexes for frequently updated tables. Create indexes for frequently used query fields.
2. It is best not to use indexes for small tables, because because of the small amount of data, it may take less time to query all the data than to traverse the index, and the index may not be optimized.
3, do not create an index on a column (field) with a small number of values. For example, in the “gender” field of the student table, there are only two different values for male and female. Conversely, a field with many different values can be indexed.
What is said above is only very one-sided some things, index certainly has many other advantages or disadvantages, as well as the principle of use, first basically understand index, and then wait for the real use of later, will slowly know other functions. Notice, it’s important to know what an index is, what an index does, what it does, why it’s used, etc., if you don’t know, just read the text again and again to make sense of it. It is possible to create multiple indexes in a table, and these indexes are stored in an index file (a special place to store indexes).
Classification of indexes
Note: Indexes are implemented in storage engines, meaning that different storage engines use different indexes
MyISAM and InnoDB storage engine: only supports BTREE index, that is to say, BTREE is used by default, MEMORY/HEAP storage engine cannot be replaced: supports HASH and BTREE index
1, index we are divided into four categories for single-column index (common index, unique index, primary key index), composite index, full-text index, spatial index,
1.1. Single-column Index: An index contains only a single column, but a table can have multiple single-column indexes. Don’t get confused here.
1.1.1. Plain index: The basic index type in MySQL. There are no restrictions on inserting duplicate values and null values in the column where the index is defined.
1.1.2 unique Index: Values in index columns must be unique, but null values are allowed.
1.1.3 primary key index: a special unique index that does not allow null values.
1.2. Composite indexes
An index created on a combination of fields in a table is used only if the left field of those fields is used in a query condition, following the leftmost prefix set. If you don’t understand this, we’ll talk about it later when we give examples
1.3. Full-text index
CHAR,VARCHAR,TEXT, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR, CHAR,VARCHAR Pretty girl…” Through the beautiful boy, may be able to find the record. If you are interested in using it further, there will be a blog post for you to refer to when testing the index.
1.4. Spatial index
Spatial indexes are built on fields of spatial data types. There are four spatial data types in MySQL, namely, GEOMETRY, POINT, LINESTRING, and POLYGON. Use the SPATIAL keyword when creating SPATIAL indexes. If the engine is MyISAM, the column that creates the spatial index must be declared NOT NULL. See below for details
4. Index operation (create and delete)
4.1 create an index
4.1.1 create an index when creating a table
4.1.1.1. Creating a Common index
You can create it either way, but with this example you can compare the format and get a pretty good idea of what the format means.
By printing the result, we will automatically use the field name as the index name if we did not write the index name when creating the index.
Test: to see if an index is used for the query.
EXPLAIN SELECT * FROM book WHERE year_publication = 1990\G;
EXPLAIN: Although there is no data in the table, the EXPLAIN keyword is used to see if the index is being used and output information about the index it is using.
Id :SELECT identifier. This is the query sequence number of the SELECT, which is the number of occurrences in a statement. In the substatement, there is only one SELECT, so it is 1.
Select_type: specifies the SELECT query type used. SIMPLE indicates a SIMPLE SELECT. If UNION or subquery is not used, SIMPLE SELECT is used. That is, indexes are used in this SELECT query. Other values: PRIMARY: the outermost SELECT. When there are subqueries, there are more than two SELECT. UNION: The second or subsequent SELECT statement in the UNION (join two tables) SUBQUERY: In a SUBQUERY, the second select.
Table: indicates the name of the data table. They are listed in the order in which they are read. In this case, only book is displayed because only one table is queried
Type: Specifies the relationship between this table and other tables, and all records in this table that match the retrieved value will be federated with records fetched from the previous table. Ref is used when the linker uses the leftmost prefix of the key or when the key is not a primary key or unique index (in other words, the linker cannot obtain only one record based on the key value). This is a good type of join when only a few matching records are found based on the key value. (Note, personal here is not very understand, Baidu a lot of information, all plain English, and so on later used this kind of information, in the back to supplement, here do not understand the impact on the back is not big.) Possible values are system, const, eq_ref, index, and All
Possible_keys: Possible_keys: There is only one index in the table, YEAR_publication, that MySQL can select when searching data records
Key: indicates the actual selected index
Key_len: displays the length of the index used by mysql. When the key field is null, the length of the index is null. Note that the value of key_len can tell you which indexes mysql actually uses in the federated index. I’m using one index here, so it’s one,
Ref: Gives the name of the data column in the other data table in the association. A const, in this case 1990, is a constant.
Rows: The number of rows that MySQL expects to read from the table when executing the query.
Extra: Provides information about the associated operation, without which nothing is written.
We can see as much as we can about the above list of possible_keys and key. It shows that key is YEAR_publication. Note An index is used.
4.1.1.2. Create a Unique index
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
Explanation: An index is used for the ID field and the index name is UniqIdx.
SHOW CREATE TABLE t1\G;
To view the index used in the query, you must first insert data into the table, and then query the data. Otherwise, you will not use the index to search for an id value that does not exist.
INSERT INTO t1 VALUES(1,’xxx’);
EXPLAIN SELECT * FROM t1 WHERE id = 1\G;
As you can see, when querying by ID, unique indexes are used. I also experimented with querying for an ID value that is not there, so the index is not used. I think the reason is that all ids should be stored in a const tables, so there is no need to look it up.
4.1.1.3 Creating a primary key index
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(10),
PRIMARY KEY(id)
);
INSERT INTO t2 VALUES(1,’QQQ’);
EXPLAIN SELECT * FROM t2 WHERE id = 1\G;
Now, with this primary key index, we should be able to reflect that the primary key constraint that we declared before is actually a primary key index, but we didn’t know that before.
4.1.1.4. Create a single-column index
In fact, I don’t need to say this, the first few are single-column indexes.
4.1.1.5. Creating a composite index
A composite index creates an index on multiple fields, creates a table T3, and creates a composite index on the ID, name, and AGE fields in the table
CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id,name,age)
);
SHOW CREATE t3\G;
Explain the leftmost prefix
Index (id, name, and age) {id, name, and age) {id, name, and age; An index can index the following field combinations (ID, name, age), (ID, name), or (ID). If the field to be queried does not prefix the left-most part of the index, then the index is not used. For example, age or (name, age) combinations are not used
In table T3, query the ID and name fields
EXPLAIN SELECT * FROM t3 WHERE id = 1 AND name = ‘joe’\G;
In table T3, query (age, name) fields so that index queries are not used. Let’s see what happens
EXPLAIN SELECT * FROM t3 WHERE age = 3 AND name = ‘bob’\G;
4.1.1.6 Creating a full-text index
Full-text indexes can be used for full-text searches, but only the MyISAM storage engine supports FULLTEXT indexes and only serves CHAR, VARCHAR, and TEXT columns. Indexes are always performed on the entire column, prefix indexes are not supported,
CREATE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;
SHOW CREATE TABLE t4\G;
Use what is called full text search. That is, in many words, the record can be found by keyword.
INSERT INTO T4 VALUES(8,’AAA’,3,’text is so good, hei, my name is Bob ‘),(9,’BBB’,4,’my name isgorlr’);
SELECT * FROM t4 WHERE MATCH(info) AGAINST(‘gorlr’);
EXPLAIN SELECT * FROM T4 WHERE MATCH(info) AGAINST(‘gorlr’); [if !vml]
Note: When using full-text search, you need to use MATCH function, and there are many restrictions on full-text search, such as only using MyISAM engine, such as only setting full-text index on CHAR,VARCHAR,TEXT. For example, the search keyword must contain at least 4 characters by default. If the search keyword is too short, it will be ignored. Wait a minute, if you were doing the experiment you might not be able to do it. Interested students can take a look at this article, the use of full text search
4.1.1.7 creating a Spatial Index
The spatial index must also use the MyISAM engine, and the fields of the spatial type must be non-empty. I don’t know what this spatial index can do, it may be related to game development, it may be related to other things, and so on encountered nature will know, now only requires to be able to create it.
CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
) ENGINE = MyISAM;
SHOW CREATE TABLE t5\G;
4.1.2 Create an index on an existing table
Format: the ALTER TABLE TABLE name ADD [UNIQUE | FULLTEXT | SPATIAL]
[INDEX | KEY] [INDEX name] (INDEX field name) [ASC | DESC]
With the above foundation, there is no need to state too much here.
Command 1: SHOW INDEX FROM table name \G
View the indexes created in a table
SHOW INDEX FROM book\G;
Pick out the key points, we need to understand the five, marked in red, if you want to know more about it, you can look up the information, I personally think, these will be met in the actual work in the future to do a detailed understanding.
Table: Table on which indexes are created
Non_unique: indicates that the index is not unique. 1 indicates that the index is not unique. 0 indicates that the index is unique
Key_name: indicates the index name
Seq_in_index indicates the position of the field in the index. The value is 1 for a single column index, and the value is 1 for a combined index.
Column_name: indicates the column field that defines the index
Sub_part: indicates the index length
Null: indicates whether the field can be Null
Index_type: indicates the index type
4.1.2.1 Adding an index to a table
Take the book table above. We already have a year_publication, now we are adding a normal index for the table
ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));
If you look at the output, you can see that the index was added successfully.
This is just an example of a normal index, adding any other index is the same. It’s just a copy of the book. I’m not going to explain it here.
4.1.2.2 Use CREATE INDEX to CREATE an INDEX.
Format: CREATE UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] INDEX name ON the name of the table (CREATE indexes of field name/length) [ASC | DESC]
Explanation: in fact, it is the same as before, the format has changed, do the same thing as above, do an example.
Add a normal index to the book table with the field authors.
CREATE INDEX BkBookNameIdx ON book(bookname);
SHOW INDEX FROM book\G; // Check the index in the book table
Explanation: : The first screenshot was not captured, because the image is too large, here as long as we see the index added to prove success. The other indexes are created the same way.
4.2 Drop index
We talked about adding indexes and querying methods to a table.
Two ways to add
1 How to create an index while creating a table
Add index to table (s);
Query mode
SHOW INDEX FROM table_name \G; \G just makes the output format look better
Now let’s talk about two operations to remove an index from a table.
Format 1: ALTER TABLE TABLE name DROP INDEX INDEX name.
Mysql > delete index from book table; delete index from book table
Delete index BkBookNameIdx from book;
ALTER TABLE book DROPINDEX BkBookNameIdx;
SHOW INDEX FROM book\G; Index BkBookNameIdx (BkBookNameIdx); // Index BkBookNameIdx (BkBookNameIdx)
Format 2: DROP INDEX INDEX name ON Table name.
Delete index BkNameIdx from book table
DROP INDEX BkNameIdx ON book;
SHOW INDEX FROM book\G;
Five, the summary
So that’s pretty much the end of MySQL indexes, just to summarize what we should have known so far
1. What is an index for? Why have an index?
This is very important, need to understand yourself, do not understand the top of the explanation
2. Classification of indexes
3. Index operation
Create index in table, add index, drop index, drop index
Buy me a cup of coffee 🙂
