The establishment of MySQL index is very important for the efficient operation of MySQL, and the index can greatly improve the retrieval speed of MySQL.
For example, if the properly designed MySQL that uses the index is a Lamborghini, then the MySQL without the design and use of the index is a human tricycle.
Taking the catalogue page (index) of the Chinese dictionary as an example, we can quickly find the words we need according to the catalogue (index) sorted by pinyin, strokes, partial radicals, etc.
Indexes are divided into single-column indexes and combined indexes. A single-column index, that is, an index contains only a single column, and a table can have multiple single-column indexes, but this is not a combined index. A composite index, that is, an index contains multiple columns.
When creating an index, you need to make sure that the index is the condition that is applied to the SQL query statement (usually as a condition of the WHERE clause).
In fact, an index is also a table that holds the primary key and index fields and points to the records of the entity table.
All of the above talk about the benefits of using indexes, but excessive use of indexes will lead to abuse. Therefore, the index also has its disadvantages: although the index greatly improves the query speed, it slows down the speed of updating the table, such as INSERT, UPDATE, and DELETE on the table. Because when updating the table, MySQL not only saves the data, but also saves the index file.
Create an index file that takes up disk space. This is the most basic index, and it has no restrictions. It can be created in the following ways: If it is of type CHAR,VARCHAR, the length can be less than the actual length of the field; for types of BLOB and TEXT, length must be specified. It is similar to the previous normal index, except that the value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique. It can be created in the following ways: There are four ways to add an index to a data table: ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a primary key, which means that the index value must be unique and cannot be NULL.General index ¶
Create an index ¶
CREATE INDEX indexName ON table_name (column_name)
Modify table structure (add index) ¶
ALTER table tableName ADD INDEX indexName(columnName)
Specify directly when you create a table ¶
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
Syntax for deleting an index ¶
DROP INDEX [indexName] ON mytable;
Unique index ¶
Create an index ¶
CREATE UNIQUE INDEX indexName ON mytable(username(length))
Modify table structure ¶
ALTER table mytable ADD UNIQUE [indexName] (username(length))
Specify directly when you create a table ¶
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
Use the ALTER command to add and remove indexes ¶