Shulou(Shulou.com)06/01 Report--

How do I index mysql? This problem may be our daily study or work often see. I hope you learned something from this question. The following is the reference content brought to you by Xiaobian. Let's take a look together!

MySQL index concept

Indexes are special files (indexes on InnoDB tables are part of a table space) that contain pointers to all records in the table. More popularly, database indexes are like tables of contents in front of a book, speeding up database queries. In the above SQL statement, in the case of no index, the database will traverse all 200 pieces of data and select the eligible one; and with the corresponding index, the database will directly look for the eligible option in the index. If we replace the SQL statement with "SELECT * FROM article WHERE id=2000000", do you want the database to read 2 million rows of data in order and give you the result or directly locate it in the index? (Note: General databases generate indexes for primary keys by default).

Clustering index and non-clustering index are divided into two kinds, clustering index is according to the physical location of data storage order, and non-clustering index is different; clustering index can improve the speed of multi-row retrieval, and non-clustering index for single-row retrieval is fast.

MySQL index types

1. general index

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

- Create INDEX index_name ON table (column(length))-How to modify table structure Add index ALTER TABLE table_name ADD INDEX index_name ON (column(length)) â € "Create TABLE `index when creating 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 COLATE utf8_general_ci NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),INDEX index_name (title(length)))-Delete references DROP INDEX index_name ON table

2. unique index

Similar to normal indexes, the difference is that the index column values must be unique, but null values are allowed (note that they are different from primary keys). If it is a composite index, the combination of column values must be unique, and the method of creating it is similar to that of ordinary indexes.

- Create UNIQUE INDEX indexName ON table (column(length))-modify table structure ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))-specify CREATE TABLE `table` directly when creating tables (`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)) Thank you for reading! After reading the above content, do you have a general understanding of the implementation method of mysql index? I hope the content of this article is helpful to everyone. If you want to know more about related articles, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.