MySQL Advanced 12 - Index

Indexing is the key to fast searching. The establishment of MySQL index is very important for the efficient operation of MySQL. The following introduces several common MySQL index types

In a database table, indexing fields can greatly improve query speed. Suppose we create a mytable table:

The code is as follows:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );

We randomly insert 10,000 records into it, including one: 5555, admin.

When looking for the record of username="admin"

SELECT * FROM mytable WHERE username='admin';

, if an index has been established on username, MySQL can accurately find the record without any scanning. On the contrary, MySQL will scan all records, that is, 10,000 records will be queried.

Indexes are divided into single column indexes and combined indexes. A single-column index means that an index only contains a single column. A table can have multiple single-column indexes, but this is not a combined index. Combined index, that is, one index contains multiple columns.

MySQL index types include:

1. Ordinary index

This is the most basic index, it has no restrictions. It has the following creation methods:

1. Create index

The code is as follows:

CREATE INDEX indexName ON mytable(username(length));

If it is CHAR, VARCHAR type, length can be less than the actual length of the field; For BLOB and TEXT types, length must be specified, the same below.

2. Modify the table structure

The code is as follows:

ALTER mytable ADD INDEX [indexName] ON (username(length)) CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,   
INDEX [indexName] (username(length)));

Delete index:

DROP INDEX [indexName] ON mytable;

2. Unique index

It is the same as the previous one The ordinary index is similar, except that the value of the index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique. It has the following creation methods:

The code is as follows:

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;

Modify the table structure

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ;

Specify directly when creating the table

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length)));

3. Primary key Index

It is a special unique index that does not allow null values. Generally, the primary key index is created at the same time when creating the table:

The code is as follows:

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID));

Of course, you can also use the ALTER command. Remember: a table can only have one primary key.

4. Combined index

In order to vividly compare single column index and combined index, add multiple fields to the table:

The code is as follows:

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,   age INT NOT NULL);

In order To further extract the efficiency of MySQL, you must consider establishing a combined index. That is to build name, city, age into an index:

The code is as follows:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

When creating the table, the usernname length is 16, and 10 is used here. This is because generally the name length will not exceed 10, which will speed up the index query, reduce the size of the index file, and improve the update speed of INSERT.

If you create single-column indexes on usernname, city, and age respectively, so that the table has three single-column indexes, the query efficiency will be very different from the above combined index, which is far lower than our combined index. . Although there are three indexes at this time, MySQL can only use the single-column index that it thinks is the most efficient.

Establishing such a combined index is actually equivalent to establishing the following three sets of combined indexes:

usernname,city,age usernname,city usernname Why is there no combined index like city and age? ? This is a result of the "leftmost prefix" of the MySQL composite index. The simple understanding is to only start the combination from the leftmost one. Not only queries containing these three columns will use this combined index, the following SQL will use this combined index:

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin";

, while the following few will not use it:

The code is as follows:

SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州";

5. The timing of creating an index

We have learned how to create an index so far, so under what circumstances do we need to create an index? Generally speaking, columns appearing in WHERE and JOIN need to be indexed, but this is not entirely true because MySQL only indexes <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE will use the index. For example:

The code is as follows:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

At this time, it is necessary to index city and age. Since the userame of the mytable table also appears in the JOIN clause, it is also necessary to index it. .

I just mentioned that only certain LIKEs need to be indexed. Because MySQL will not use the index when making queries starting with wildcard characters % and _. For example, the following sentence will use the index:

The code is as follows:

SELECT * FROM mytable WHERE username like'admin%';

, but the next sentence will not use it:

The code is as follows:

SELECT * FROM mytable WHEREt Name like'%admin';

因此，在使用LIKE时应注意以上的区别。

六、索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

七、使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

1.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5.不要在列上进行运算

代码如下:

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

代码如下:

select * from users where adddate<‘2007-01-01&#39;;

6.不使用NOT IN和<>操作

 以上就是MySQL高级十二——索引的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！