MySQL index types and advantages and disadvantages

1Select the data type of the index

MySQL supports many data types. Choosing the appropriate data type to store data has a great impact on performance. Generally speaking, some guidelines can be followed:

(1) Smaller data types are usually better: Smaller data types usually require less space on disk, memory, and CPU cache. Processing is faster.

(2) Simple data types are better: Integer data has less processing overhead than characters, because the comparison of strings is more complex. In MySQL, you should use the built-in date and time data types instead of strings to store time; and use integer data types to store IP addresses.

(3) Try to avoid NULL: the column should be specified as NOT NULL, unless you want to store NULL. In MySQL, columns containing null values ​​are difficult to optimize queries because they complicate indexes, index statistics, and comparison operations. You should replace null values ​​with 0, a special value, or an empty string.

2
Type of index

Index is implemented in the storage engine, not in the server layer. Therefore, the indexes of each storage engine are not necessarily exactly the same, and not all storage engines support all index types

(1) Ordinary index

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

Create index

CREATE INDEX indexName ON mytable(username(length)); If it is CHAR, VARCHAR type, length can be less than the actual length of the field; if it is For BLOB and TEXT types, length must be specified, the same below.

Modify the table structure

ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );  删除索引的语法：
DROP INDEX [indexName] ON mytable;

(2) Unique index

It is similar to the previous ordinary index, except that the value of the index column must be unique, but empty is allowed value. In the case of a composite index, the combination of column values ​​must be unique. It has the following creation methods:

Create index

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆创建表的时候直接指定
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:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

(4) Composite index

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

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,

Consider creating a combined index. That is to build name, city, age into an index:

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,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. (This is one of the interview questions, and I should have answered it correctly at the time) Not only queries containing these three columns will use this combined index, but the following SQL will use this combined index:

SELECT FROM mytable WHREE username="admin" AND city="郑州"  SELECT FROM mytable WHREE username="admin" 而下面几个则不会用到：
SELECT FROM mytable WHREE age=20 AND city="郑州"  SELECT FROM mytable WHREE city="郑州"

3
Time to create an index

Now we have learned how to create an index, 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:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    
ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

Just mentioned that only LIKE at certain times needs 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:

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

4
The shortcomings of the index

The above mentioned using the index Benefits, but excessive use of indexes can lead to abuse. Therefore, the index will also have its shortcomings:

Although the index greatly improves the query speed, it will also reduce the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file.

Creating index files will occupy disk space. Generally, this problem is not serious, but if you create multiple combined indexes on a large table, the index file will expand quickly.

Indexes are only one factor to improve efficiency. If your MySQL has a large amount of data tables, you need to spend time researching and building the best indexes, or optimizing query statements

5
Notes on using indexes

When using indexes, there are some tips and precautions:

The index will not contain columns with NULL values.

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

使用短索引

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

索引列排序

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

like语句操作

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

不要在列上进行运算

select * from users where YEAR(adddate)<2015; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2015-01-01’;

不使用NOT IN和操作

以上就是MySQL索引类型与优缺点的内容，更多相关内容请关注PHP中文网（www.php.cn）！