mysql database-index

An index is a special file that contains pointers used by all records. Just like the table of contents of a book, it speeds up book retrieval. A database is almost unusable without indexes. Someone once jokingly said that if MySQL using index is a Lamborghini, if it does not use index, it is at best a human-powered three-wheeler.

Indexes are divided into clustered indexes and non-clustered indexes. Clustered indexes improve the retrieval speed of multiple rows, and non-clustered indexes improve the retrieval speed of single rows of data. In the database functions, three types of indexes can be created: unique index, primary key index and clustered index.

Ordinary index is an index without any restrictions. Its creation process is: CREATE INDEX index_name ON table(column(col_name)); It is the default BTREE type index of the MYIASM database engine. You often see fields like this when using navicat to export mysql database scripts.

<strong> Unique index</strong> is based on the ordinary index to ensure that the value of the index column must be unique, and there can be null values ​​except the primary key. Creation process:

CREATE UNIQUE INDEX indexName ON table(column(length))

Pros and cons of indexing:

Indexes greatly improve query and sorting speeds, but reduce data storage and update efficiency. Indexes need to generate index files. When combined indexes are used extensively, the index files will expand rapidly. In response to these problems, the following optimization methods are proposed:

1. When to use clustered indexes Or non-clustered index?

Action description	Use clustered index	Using a non-clustered index
Columns are often grouped and sorted	Using	Using
Return data within a certain range	Used	Not used
One or very few different values	Not used	Not used
Small number of different values ​​	Used	Not used
Large Number of distinct values	Not used	Used
Frequently updated columns	Not used	Used
Foreign key column	Use	Use
Primary key column	Use	Use
Frequently modify index columns	Not use	Use

2. 使用短索引列。

<strong> </strong>索引列如果使用varchar（255）的话会让索引文件变大，不利于检索，这255个字符中前10或20个字符能够保证索引唯一的话，就使用这些字段作为索引列即可。

3. like语句

在数据库操作中不建议使用like语句，但费用不可时，like"%aaa%"不会使用索引而like“aaa%”则可以使用索引。

4. 不要再索引列执行运算，这样会导致索引失效。

5. 使用越小越简单的数据类型越好；尽量避免null；

6. 组合索引仅能对索引最左边的索引进行有效查询。如：

索引列为c1，c2，以下查询语句有效：

select * form table where c1=1 and c2=2;
 
   select * from table where c1=1;

但对于一下查询语句是无效的：

select * from table where c2=2;

 以上就是mysql数据库-索引的内容，更多相关内容请关注PHP中文网（www.php.cn）！<br>

<br>

<br>