Analyse de code spécifique à l'index d'optimisation MySQL

Optimisation MySQL - Analyse du code spécifique à l'index :

Les index sont implémentés dans les moteurs de stockage, de sorte que les index de chaque moteur de stockage ne sont pas nécessairement exactement les mêmes, et pas tous Le moteur de stockage prend en charge tous les types d'index.

Définit le nombre maximum d'index et la longueur maximale de l'index pour chaque table en fonction du moteur de stockage. Tous les moteurs de stockage prennent en charge au moins 16 index par table, avec une longueur totale d'index d'au moins 256 octets.

La plupart des moteurs de stockage ont des limites plus élevées. Il existe deux types d'index de stockage dans MYSQL : BTREE et HASH, qui sont spécifiquement liés au moteur de stockage de la table ;

Les moteurs de stockage MYISAM et InnoDB ne prennent en charge que les index BTREE et HEAP ; et index BTREE

Avantages de l'index :

1. Garantissez l'unicité de chaque ligne de données dans la table de la base de données en créant un index unique

2. requête

3. Lors de l'utilisation du regroupement et du tri pour la requête de données, le temps de regroupement et de tri dans la requête peut être considérablement réduit

Inconvénients de l'index :

1. l'index consomme des ressources de base de données

2. Les index nécessitent de l'espace disque et les fichiers d'index peuvent atteindre la taille maximale plus rapidement que les fichiers de données

3. la vitesse sera affectée en raison de la nécessité de maintenir l'index

index

1. L'index de clé primaire est un index unique spécial, aucune valeur nulle n'est autorisée

Index à colonne unique et index composite

L'index à une seule colonne ne contient qu'une seule colonne

Composé Un index fait référence à un index créé sur plusieurs champs. L'index ne sera utilisé que si le premier champ lors de la création de l'index est utilisé dans les conditions de requête. . Lorsque vous utilisez un index composite, suivez le jeu de préfixes le plus à gauche

3 Index de texte intégral

Le type d'index de texte intégral est FULLTEXT, qui est pris en charge sur la colonne qui définit l'index Recherche en texte intégral des valeurs, permettant l'insertion de valeurs en double et nulles dans ces colonnes indexées. Des index de texte intégral peuvent être créés sur des colonnes de type

CHAR, VARCHAR et TEXT. MYSQL ne prend en charge que l'index de texte intégral du moteur de stockage MYISAM

4. Index spatial

L'index spatial est un index établi pour les champs de données spatiales. type. MYSQL Il existe 4 types de données spatiales dans

qui sont GEOMETRY, POINT, LINESTRING et POLYGON.

MYSQL est étendu avec le mot-clé SPATIAL, permettant la création d'index spatiaux en utilisant la syntaxe utilisée pour créer des types d'index réguliers. Les colonnes utilisées pour créer des index spatiaux doivent être

déclarées comme NON NULL. Les index spatiaux ne peuvent être créés que dans des tables dont le moteur de stockage est

Les index ci-dessus sont dans

. SQLSERVER

prend en charge unique|fulltext|spatial comme paramètres facultatifs, qui représentent respectivement l'index unique, l'index de texte intégral et l'index spatial

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

l'index et la clé sont ; synonymes , les deux ont la même fonction et sont utilisés pour spécifier l'index à créer.

col_name est la colonne de champ pour laquelle l'index doit être créé. Cette colonne doit être sélectionnée parmi plusieurs colonnes définies dans le fichier. table de données ;

index_name spécifie Le nom de l'index est un paramètre facultatif, s'il n'est pas spécifié, MYSQL utilise par défaut col_name comme valeur d'index

length est un paramètre facultatif, indiquant la longueur de ; l'index. Seuls les champs de type chaîne peuvent spécifier la longueur de l'index ;

asc ou desc spécifie le stockage de la valeur d'index par ordre croissant ou décroissant

---

Index normal<🎜. >

Utilisez SHOW CREATE TABLE pour examiner la structure de la table

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)
) ;

et on peut constater que le champ year_publication de la table book est indexé avec succès et son index le nom est year_publication

Nous insérons une donnée dans la table, puis utilisons l'instruction EXPLAIN pour vérifier si l'index utilisez-vous

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

Parce que l'instruction est relativement simple, la Le système détermine que l'indexation ou l'analyse de texte intégral peuvent être utilisées

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

EXPLAIN L'explication de chaque ligne du résultat de la sortie de l'instruction est la suivante :

select_type :

représente le type de chaque clause select dans la requête (simple ou complexe)

type : représente la façon dont MySQL trouve les lignes requises dans le tableau, également appelé "type d'accès". Les types courants sont les suivants : (de haut en bas, l'effet s'améliore dans l'ordre)

possible_keys : Indique quel index MySQL peut utiliser. pour rechercher des lignes dans la table. S'il y a un index sur le champ impliqué dans la requête, l'index sera listé, mais il ne pourra pas être utilisé par la requête

clé : Affiche l'index réellement utilisé par MySQL dans la requête. Si aucun index n'est utilisé, il est affiché comme NULL

key_len : Indique le nombre d'octets utilisés dans l'index, qui peut être calculé via cette colonne. La longueur de l'index

ref : Indique les conditions de correspondance de connexion du tableau ci-dessus, c'est-à-dire quelles colonnes ou constantes sont utilisées pour trouver la valeur sur la colonne d'index

lignes  : indique que MySQL estime le nombre de lignes qui doivent être lues pour trouver les enregistrements requis en fonction des statistiques de la table et de la sélection d'index

Extra<. 🎜> : Contient ne convient pas aux autres colonnes Affichage mais des informations supplémentaires très importantes telles que l'utilisation de où, l'utilisation de l'index

---

唯一索引

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

建表

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

SHOW CREATE TABLE t1 查看表结构

SHOW CREATE TABLE t1

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

可以看到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))
)

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

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

INSERT INTO t3(id ,NAME,age,info) VALUES(1,'小明',12,'nihao'),(2,'小芳',16,'nihao')

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

EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME='小芳'

可以看到 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

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

EXPLAIN SELECT * FROM t3 WHERE  NAME='小芳'

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

结果跟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

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

SHOW CREATE TABLE t4

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

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

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

---

空间索引

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

建表t5

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

SHOW CREATE TABLE t5

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

可以看到，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]

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

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

ALTER TABLE t1 ADD INDEX NameIdx(NAME)

添加索引之后，使用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

各个参数的含义

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)

使用SHOW INDEX查看表中的索引

SHOW INDEX FROM t3

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

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

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

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

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

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

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info)

使用SHOW INDEX查看索引情况

SHOW INDEX FROM t6

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

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

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

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

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g)

使用SHOW INDEX查看索引情况

SHOW INDEX FROM t7

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

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

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name

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

可以看到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
)

建立普通索引

CREATE INDEX BkNameIdx ON book(bookname)

建立唯一索引

CREATE UNIQUE INDEX UniqidIdx ON book(bookId)

建立复合索引

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

建立全文索引，我们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);

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

DROP TABLE IF EXISTS t7

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

CREATE SPATIAL INDEX spatIdx  ON t7(g)

删除索引

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

1、使用ALTER TABLE删除索引

语法

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE book DROP INDEX UniqidIdx

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`),                                                                                                                                                                                                                                                                                                                                   
          KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))                                                                                                                                                                                                                                                                                                                 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

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

DROP INDEX index_name ON table_name

DROP INDEX BkAuAndInfoIdx ON book

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

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

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

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!