How to use mysql index

The concept of MySQL index

The index is a special file (the index on the InnoDB data table is an integral part of the table space). They contain all the data in the data table. The record's reference pointer. To put it more generally, a database index is like the table of contents at the front of a book, which can speed up database queries. In the above SQL statement, if there is no index, the database will traverse all 200 pieces of data and select the ones that meet the conditions; with the corresponding index, the database will directly search for the options that meet the conditions in the index. If we change the SQL statement to "SELECT * FROM article WHERE id=2000000", do you want the database to give you the results after reading 2 million rows of data in sequence, or to locate it directly in the index? (Note: Generally The database will generate indexes on the primary key by default).

Recommended courses: MySqlTutorial.

Indexes are divided into clustered indexes and non-clustered indexes. Clustered indexes are ordered according to the physical location of data storage. Non-clustered indexes are different; clustered indexes can increase the speed of multi-row retrieval, while non-clustered indexes are very fast for single-row retrievals.

Types of MySQL indexes

1. Ordinary index

This is the most basic index, it has no restrictions. For example, the index created for the title field above is a normal index, and the default BTREE type index in MyIASM is also Indexes we use in most cases.

–直接创建索引
CREATE INDEX index_name ON table(column(length))
–修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
–创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
–删除索引
DROP INDEX index_name ON table

2. Unique index

is similar to a normal index, except that the value of the index column must be unique, but null values ​​are allowed (note that it is different from the primary key) . If it is a combined index, the combination of column values ​​must be unique, and the creation method is similar to that of a normal index.

–创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
–修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
–创建表的时候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))

The above is the detailed content of How to use mysql index. For more information, please follow other related articles on the PHP Chinese website!