MySQL optimization-index specific code analysis-Mysql Tutorial-php.cn

MySQL Optimization - Index Specific Code Analysis:

Indexes are implemented in storage engines, so the indexes of each storage engine are not necessarily exactly the same, and Not every storage engine supports all index types.

Defines the maximum number of indexes and maximum index length for each table according to the storage engine. All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes.

Most storage engines have higher limits. There are two storage types of indexes in MYSQL: BTREE and HASH, which are specifically related to the storage engine of the table;

MYISAM and InnoDB storage engines only support BTREE indexes; MEMORY and HEAP storage engines can support HASH and BTREE indexes

Advantages of index:

1. By creating a unique index, the uniqueness of each row of data in the database table is ensured

2. Greatly speed up data query

3. When using grouping and sorting for data query, the time of grouping and sorting in the query can be significantly reduced.

Disadvantages of index:

1. Maintaining the index requires database resources

2. Indexes require disk space, and index files may reach the maximum file size faster than data files.

3. When adding, deleting, or modifying table data, the speed will be affected due to the need to maintain the index. Classification that affects

1. Ordinary index and unique index

The primary key index is a special unique index , NULL values are not allowed

2. Single-column index and composite index

Single-column index only contains a single column

Compound An index refers to an index created on multiple fields. The index will be used only if the first field used when creating the index is used in the query conditions. When using a composite index, follow the leftmost prefix set

3. Full-text index

The full-text index type is FULLTEXT, which is supported on the column that defines the index. Full-text lookup of values, allowing insertion of duplicate and null values in these indexed columns. Full-text indexes can be created on

CHAR, VARCHAR, and TEXT type columns. MYSQL only supports the MYISAM storage engine full-text index

4. Spatial index

The spatial index is an index established for fields of spatial data type. MYSQL There are 4 spatial data types in

They are GEOMETRY, POINT, LINESTRING, and POLYGON.

MYSQL is extended using the SPATIAL keyword, enabling the creation of spatial indexes using the syntax used to create regular index types. Columns used to create spatial indexes must be

declared as NOT NULL. Spatial indexes can only be created in tables with the storage engine MYISAM. Indexes above

are in SQLSERVER are supported in

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

Copy after login

unique|fulltext|spatial is an optional parameter, representing unique index, full-text index and spatial index respectively;

index and key are synonyms, both The two have the same function and are used to specify the index to be created.

col_name is the field column that needs to be created. The column must be selected from multiple columns defined in the data table;

index_name specifies the index. Name is an optional parameter. If not specified, MYSQL defaults col_name as the index value;

length is an optional parameter, indicating the length of the index. Only string type fields can specify the index length;

asc or desc specifies index value storage in ascending or descending order

Normal index

CREATE TABLE book (
  bookid INT NOT NULL,
  bookname VARCHAR (255) NOT NULL,
  AUTHORS VARCHAR (255) NOT NULL,
  info VARCHAR (255) NULL,
  COMMENT VARCHAR (255) NULL,
  year_publication YEAR NOT NULL,
  INDEX (year_publication)
) ;

Copy after login

Use SHOW CREATE TABLE to view the table structure

CREATE TABLE `book` (
  `bookid` INT(11) NOT NULL,
  `bookname` VARCHAR(255) NOT NULL,
  `authors` VARCHAR(255) NOT NULL,
  `info` VARCHAR(255) DEFAULT NULL,
  `comment` VARCHAR(255) DEFAULT NULL,
  `year_publication` YEAR(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1

Copy after login

It can be found that the year_publication field of the book table has successfully created an index and its index name is year_publication

We insert a piece of data into the table, and then use the EXPLAIN statement to check whether the index is in use

NSERT INTO BOOK VALUES(12,&#39;NIHAO&#39;,&#39;NIHAO&#39;,&#39;文学&#39;,&#39;henhao&#39;,1990)
EXPLAIN SELECT * FROM book WHERE year_publication=1990

Copy after login

Because the statement is relatively simple, the system determines that indexing or full-text scanning may be used

Explanation of each line of the EXPLAIN statement output result As follows:

select_type: indicates the type of each select clause in the query (simple OR complex)

type: indicates that MySQL is in the table The way to find the required rows, also known as "access type", common types are as follows: (from top to bottom, the effect becomes better in order)

possible_keys: Indicate which index MySQL can use in Rows are found in the table. If there is an index on the field involved in the query, the index will be listed, but it may not be used by the query.

key: Displays the actual use of MySQL in the query The index, if no index is used, is displayed as NULL

key_len: Indicates the number of bytes used in the index, and the length of the index used in the query can be calculated through this column

ref: Indicates the connection matching conditions of the above table, that is, which columns or constants are used to find the value on the index column

rows: Indicates that MySQL based on the table Statistical information and index selection, estimated number of rows to be read to find the required records

Extra: Contains extra information that is not suitable for display in other columns but is very important, such as using where, using index

唯一索引

唯一索引列的值必须唯一，但允许有空值。如果是复合索引则列值的组合必须唯一

建表

CREATE TABLE t1
(
 id INT NOT NULL,
 NAME CHAR(30) NOT NULL,
 UNIQUE INDEX UniqIdx(id)
）

Copy after login

SHOW CREATE TABLE t1 查看表结构

SHOW CREATE TABLE t1

Copy after login

 CREATE TABLE `t1` (                                                                                                                   
          `id` int(11) NOT NULL,                                                                                                                   
          `name` char(30) NOT NULL,                                                                                                                
          UNIQUE KEY `UniqIdx` (`id`)                                                                                                              
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

Copy after login

可以看到id字段上已经成功建立了一个名为UniqIdx的唯一索引

创建复合索引

CREATE TABLE t3 (
  id INT NOT NULL,
  NAME CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR (255),
  INDEX MultiIdx (id, NAME, age (100))
)

Copy after login

SHOW CREATE TABLE t3

CREATE TABLE `t3` (                                                                                                                                                                                             
          `id` int(11) NOT NULL,                                                                                                                                                                                        
          `NAME` char(30) NOT NULL,                                                                                                                                                                                     
          `age` int(11) NOT NULL,                                                                                                                                                                                       
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
          KEY `MultiIdx` (`id`,`NAME`,`age`)                                                                                                                                                                            
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

Copy after login

由结果可以看到id,name,age字段上已经成功建立了一个名为MultiIdx的复合索引
我们向表插入两条数据

INSERT INTO t3(id ,NAME,age,info) VALUES(1,&#39;小明&#39;,12,&#39;nihao&#39;),(2,&#39;小芳&#39;,16,&#39;nihao&#39;)

Copy after login

使用EXPLAIN语句查看索引使用情况

EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME=&#39;小芳&#39;

Copy after login

可以看到 possible_keys和 key 为MultiIdx证明使用了复合索引

 id  select_type  table   type    possible_keys  key       key_len  ref            rows  Extra      
------  -----------  ------  ------  -------------  --------  -------  -----------  ------  -----------
     1  SIMPLE       t3      ref     MultiIdx       MultiIdx  94       const,const       1  Using where

Copy after login

如果我们只指定name而不指定id

EXPLAIN SELECT * FROM t3 WHERE  NAME=&#39;小芳&#39;

    id  select_type  table   type    possible_keys  key     key_len  ref       rows  Extra      
------  -----------  ------  ------  -------------  ------  -------  ------  ------  -----------
     1  SIMPLE       t3      ALL     (NULL)         (NULL)  (NULL)   (NULL)       2  Using where

Copy after login

结果跟SQLSERVER一样，也是不走索引， possible_keys和key都为NULL

全文索引

FULLTEXT索引可以用于全文搜索。只有MYISAM存储引擎支持FULLTEXT索引，并且只支持CHAR、VARCHAR和TEXT类型

全文索引不支持过滤索引。

CREATE TABLE t4 (
  id INT NOT NULL,
  NAME CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR (255),
  FULLTEXT INDEX FulltxtIdx (info)
) ENGINE = MYISAM

Copy after login

由于MYSQL5.6默认存储引擎为InnoDB，这里创建表的时候要修改表的存储引擎为MYISAM，不然创建索引会出错

SHOW CREATE TABLE t4

Copy after login

Table   Create Table                                                                                                                                                                                                    
------  ------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
t4      CREATE TABLE `t4` (                                                                                                                                                                                             
          `id` int(11) NOT NULL,                                                                                                                                                                                        
          `name` char(30) NOT NULL,                                                                                                                                                                                     
          `age` int(11) NOT NULL,                                                                                                                                                                                       
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                             
          FULLTEXT KEY `FulltxtIdx` (`info`)                                                                                                                                                                            
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

Copy after login

由结果可以看到，info字段上已经成功建立名为FulltxtIdx的FULLTEXT索引。

全文索引非常适合大型数据集合

空间索引

空间索引必须在 MYISAM类型的表中创建，而且空间类型的字段必须为非空

建表t5

CREATE TABLE t5
(g GEOMETRY NOT NULL ,SPATIAL INDEX spatIdx(g))ENGINE=MYISAM

Copy after login

SHOW CREATE TABLE t5

TABLE   CREATE TABLE                                                                                                   
------  ---------------------------------------------------------------------------------------------------------------
t5      CREATE TABLE `t5` (                                                                                            
          `g` GEOMETRY NOT NULL,                                                                                       
          SPATIAL KEY `spatIdx` (`g`)                                                                                  
        ) ENGINE=MYISAM DEFAULT CHARSET=utf8

Copy after login

可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。注意创建时指定空间类型字段值的非空约束

并且表的存储引擎为MYISAM

已经存在的表上创建索引

在已经存在的表中创建索引，可以使用ALTER TABLE或者CREATE INDEX语句

1、使用ALTER TABLE语句创建索引，语法如下

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]
[index_name](col_name[length],...)[ASC|DESC]

Copy after login

与创建表时创建索引的语法不同，在这里使用了ALTER TABLE和ADD关键字，ADD表示向表中添加索引

在t1表中的name字段上建立NameIdx普通索引

ALTER TABLE t1 ADD INDEX NameIdx(NAME)

Copy after login

添加索引之后，使用SHOW INDEX语句查看指定表中创建的索引

SHOW INDEX FROM t1

TABLE   Non_unique  Key_name  Seq_in_index  Column_name  COLLATION  Cardinality  Sub_part  Packed  NULL    Index_type  COMMENT  Index_comment
------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t1               0  UniqIdx              1  id           A                    0    (NULL)  (NULL)          BTREE                             
t1               1  NameIdx              1  NAME         A               (NULL)    (NULL)  (NULL)          BTREE

Copy after login

各个参数的含义

1、TABLE：要创建索引的表

2、Non_unique：索引非唯一，1代表是非唯一索引，0代表唯一索引

3、Key_name：索引的名称

4、Seq_in_index：该字段在索引中的位置，单列索引该值为1，复合索引为每个字段在索引定义中的顺序

5、Column_name：定义索引的列字段

6、Sub_part：索引的长度

7、NULL：该字段是否能为空值

8、Index_type：索引类型

可以看到，t1表已经存在了一个唯一索引
在t3表的age和info字段上创建复合索引

ALTER TABLE t3 ADD INDEX t3AgeAndInfo(age,info)

Copy after login

使用SHOW INDEX查看表中的索引

SHOW INDEX FROM t3

Copy after login

Table   Non_unique  Key_name      Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  ------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t3               1  MultiIdx                 1  id           A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  MultiIdx                 2  NAME         A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  MultiIdx                 3  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  t3AgeAndInfo             1  age          A               (NULL)    (NULL)  (NULL)          BTREE                             
t3               1  t3AgeAndInfo             2  info         A               (NULL)    (NULL)  (NULL)  YES     BTREE

Copy after login

可以看到表中的字段的顺序，第一个位置是age，第二个位置是info，info字段是可空字段

创建表t6，在t6表上创建全文索引

CREATE TABLE t6
(
  id INT NOT NULL,
  info CHAR(255)
)ENGINE= MYISAM;

Copy after login

注意修改ENGINE参数为MYISAM，MYSQL默认引擎InnoDB不支持全文索引

使用ALTER TABLE语句在info字段上创建全文索引

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info)

Copy after login

使用SHOW INDEX查看索引情况

SHOW INDEX FROM t6

Copy after login

Table   Non_unique  Key_name   Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  ---------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t6               1  infoFTIdx             1  info         (NULL)          (NULL)    (NULL)  (NULL)  YES     FULLTEXT

Copy after login

创建表t7，并在空间数据类型字段g上创建名称为spatIdx的空间索引

CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;

Copy after login

使用ALTER TABLE在表t7的g字段建立空间索引

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g)

Copy after login

使用SHOW INDEX查看索引情况

SHOW INDEX FROM t7

Copy after login

Table   Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment
------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  -------------
t7               1  spatIdx              1  g            A               (NULL)        32  (NULL)          SPATIAL

Copy after login

2、使用CREATE INDEX语句创建索引，语法如下

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name

ON table_name(col_name[length],...)  [ASC|DESC]

Copy after login

可以看到CREATE INDEX语句和ALTER INDEX语句的基本语法一样，只是关键字不同。

我们建立一个book表

CREATE TABLE book (
  bookid INT NOT NULL,
  bookname VARCHAR (255) NOT NULL,
  AUTHORS VARCHAR (255) NOT NULL,
  info VARCHAR (255) NULL,
  COMMENT VARCHAR (255) NULL,
  year_publication YEAR NOT NULL
)

Copy after login

建立普通索引

CREATE INDEX BkNameIdx ON book(bookname)

Copy after login

建立唯一索引

CREATE UNIQUE INDEX UniqidIdx ON book(bookId)

Copy after login

建立复合索引

CREATE INDEX BkAuAndInfoIdx ON book(AUTHORS(20),info(50))

Copy after login

建立全文索引，我们drop掉t6表，重新建立t6表

DROP TABLE IF EXISTS t6

CREATE TABLE t6
(
  id INT NOT NULL,
  info CHAR(255)
)ENGINE= MYISAM;

CREATE FULLTEXT INDEX infoFTIdx ON t6(info);

Copy after login

建立空间索引，我们drop掉t7表，重新建立t7表

DROP TABLE IF EXISTS t7

CREATE TABLE t7(g GEOMETRY NOT NULL)ENGINE=MYISAM;

CREATE SPATIAL INDEX spatIdx  ON t7(g)

Copy after login

删除索引

MYSQL中使用ALTER TABLE或者DROP INDEX语句来删除索引，两者实现相同功能

1、使用ALTER TABLE删除索引

语法

ALTER TABLE table_name DROP INDEX index_name

Copy after login

ALTER TABLE book DROP INDEX UniqidIdx

Copy after login

SHOW CREATE TABLE book

Copy after login

Table   Create Table                                                                                                                                                                                                                                                                                                                                                      
------  ----------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                                                                             
          `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                                                                      
          `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                               
          `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                                                                                
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                               
          `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                                                                            
          `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                                                                            
          KEY `BkNameIdx` (`bookname`),                                                                                                                                                                                                                                                                                                                                   
          KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))                                                                                                                                                                                                                                                                                                                 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

Copy after login

可以看到，book表中已经没有名为UniqidIdx的唯一索引，删除索引成功
注意：AUTO_INCREMENT约束字段的唯一索引不能被删除！！
2、使用DROP INDEX 语句删除索引

DROP INDEX index_name ON table_name

Copy after login

DROP INDEX BkAuAndInfoIdx ON book

Copy after login

SHOW CREATE TABLE book;

Table   Create Table                                                                                                                                                                                                                                                                                                   
------  
------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
book    CREATE TABLE `book` (                                                                                                                                                                                                                                                                                          
          `bookid` int(11) NOT NULL,                                                                                                                                                                                                                                                                                   
          `bookname` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                            
          `authors` varchar(255) NOT NULL,                                                                                                                                                                                                                                                                             
          `info` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                            
          `comment` varchar(255) DEFAULT NULL,                                                                                                                                                                                                                                                                         
          `year_publication` year(4) NOT NULL,                                                                                                                                                                                                                                                                         
          KEY `BkNameIdx` (`bookname`)                                                                                                                                                                                                                                                                                 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

Copy after login

可以看到，复合索引BkAuAndInfoIdx已经被删除了

提示：删除表中的某列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。

如果索引中的所有列都被删除，则整个索引将被删除！！

The above is the detailed content of MySQL optimization-index specific code analysis. For more information, please follow other related articles on the PHP Chinese website!