Explication détaillée de l'index MySQL + explication

tutoriel vidéo mysql la chronique d'aujourd'hui se concentre sur l'indexation + expliquer pour préparer les entretiens.

Recommandation gratuite : tutoriel vidéo mysql

1. Introduction à l'index

1. Dans MySQL, l'index est la structure de données, qui a été triée en fonction de l'index dans le fichier.
2. L'utilisation de l'index peut accélérer notre vitesse de requête, mais l'efficacité de l'ajout, de la suppression et de la modification de nos données sera réduite
3. Comme la majeure partie d'un site Web est une requête, nous optimisons principalement l'instruction select

<.> 2. Index dans la catégorie MySQL

• Index commun clé
• Index unique clé unique alias de clé unique l'alias peut être ignoré l'alias peut être ignoré
• Index de clé primaire clé primaire (champ)
• Index de texte intégralprise en charge du moteur myisam (indexation en anglais uniquement, version mysql 5.6 également supports), sphinx (recherche en chinois)
• Index hybride Un index composé de plusieurs champs Tels que key key_index(title,email)

Basique. opérations d'index

1. Ajouter un index à la table

create table t_index(
    id int not null auto_increment,
    title varchar(30) not null default '',
    email varchar(30) not null default '',
    primary key(id),
    unique key uni_email(email) ,
    key key_title(title)
)engine=innodb charset=utf8;

Voir la table

desc tablename

mysql> desc t_index;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(30) | NO   | MUL |         |                |
| email | varchar(30) | NO   | UNI |         |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Afficher l'instruction de création de la table

show create table tbalename/G

mysql> show create table t_index/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show create table t_index\G;
*************************** 1. row ***************************
       Table: t_index
Create Table: CREATE TABLE `t_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL DEFAULT '',
  `email` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_email` (`email`),
  KEY `key_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

2 Supprimer l'index

1. Supprimer l'index de clé primaire

alter table table_name drop primary key;Remarque :

mysql> alter table t_index drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

La clé primaire n'est pas nécessairement auto-croissante, mais l'auto-augmentation doit être la clé primaire.

Avant de supprimer l'index, vous devez d'abord supprimer l'auto-incrémentation de l'index de clé primaire.

mysql> alter table t_index modify  id int not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Supprimer à nouveau la clé primaire

mysql> alter table t_index drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Supprimer les index ordinaires et uniques

alter table table_name drop key ‘索引的别名’ Fonctionnement réel

mysql> alter table t_index drop key uni_email;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

rrree

3. Ajouter un index

mysql> alter table t_index drop key key_title;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

4 Comparaison avec ou sans index

alter table t_index add key key_title(title);
alter table t_index add key uni_email(email);
alter table t_index add primary key(id);

Insérer des données

create table article(
id int not null auto_increment,
no_index int,
title varchar(30) not null default '',
add_time datetime,
primary key(id)
);

Comparaison avec ou sans requête d'index. data

mysql> insert into article(id,title,add_time) values(null,'ddsd1212123d',now());

mysql> insert into article(title,add_time) select title,now() from article;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> update article set no_index=id;

mysql> select * from article where no_index=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.28 sec)

Structure de la table

mysql> select * from article where id=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.01 sec)

4

expliquer l'analyseUtilisez expliquer pour analyser si l'instruction SQL utilise une requête d'index, afin de mieux comprendre. Optimisez-le.

Il suffit d'ajouter une explication ou une description devant l'instruction select.

Syntaxe

explain|desc select * from tablename G;2. . Analyse

Comparez les deux index tout à l'heure avec et sans index

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1572824 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> mysql> explain select * from article where no_index=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE//单表查询
        table: article//查询的表名
   partitions: NULL
         type: ALL//索引的类型，从好到坏的情况是：system>const>range>index>All
possible_keys: NULL//可能使用到的索引
          key: NULL//实际使用到的索引
      key_len: NULL//索引的长度
          ref: NULL
         rows: 1307580//可能进行扫描表的行数
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

3.Analyse de l'élément de type de

expliquer

Les éléments de type sont. trié du meilleur au pire :

system
•  : Généralement, il apparaîtra lorsque la table système n'a qu'une seule ligne d'enregistrements
const<.> : Lors de l'attente de la valeur de la clé primaire. Il apparaîtra lors de l'exécution d'une requête de valeur, telle que Where id=666666
• range
 : Il apparaîtra lors de l'exécution d'une requête de plage sur l'index. valeur, telle que Where id<100000
• index
 : lorsque le champ que nous interrogeons correspond à la valeur de notre fichier d'index, il y aura
• Tous
 : la pire situation, qu'il faut éviter.
• Test réel

mysql> explain select * from article where id=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const//当对主键索引进行等值查询的时候出现const
possible_keys: PRIMARY
          key: PRIMARY//实际使用到的所有primary索引
      key_len: 4//索引的长度4 = int占4个字节
          ref: const
         rows: 1//所扫描的行数只有一行
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified<pre class="brush:php;toolbar:false">mysql> use mysql;
mysql> explain select * from user\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> use test;
mysql> explain select * from article where id=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

mysql> explain select * from article where id>666666\G;
mysql> explain select * from article where id<666666\G;

Si le champ interrogé existe dans le fichier d'index, il sera interrogé directement depuis l'index Nous appelons cette requête une requête de couverture d’index.

apparaît tout, ce que nous devons éviter en raison d'une analyse complète.

Pour toutes les occurrences, vous pouvez ajouter une requête d'index commune au champ

mysql> explain select id  from article \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

4 Scénarios d'utilisation des index

1 Nous avons besoin de champs qui apparaissent souvent après où Ajouter. un index dessus

2. Optimisation de l'instruction order by en utilisant l'index

mysql> alter table article add key key_no_index(no_index);
Query OK, 0 rows affected (1.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

type为ref，应该是关联，但是ref是const
mysql> explain select * from article where no_index=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: key_no_index
          key: key_no_index
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

速度飞跃
mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.00 sec)

On peut voir que même si un index est utilisé, il s'agit presque toujours d'une analyse de table complète .

Si vous ajoutez où, la moitié sera perdue

3. Optimisation de l'index de requête floue pour comme

. où titre comme '%keyword%' ====>Analyse complète du tableau

où titre comme 'keyword%' ===> Requête d'index

Ajouter un index au titre

mysql> explain select * from article order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from article where id >0  order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 653790
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

Parce que % n'apparaît pas à l'extrême gauche de la requête de mot-clé similaire, vous pouvez utiliser la requête d'index

Tant que c'est % qui apparaît sur le côté gauche de like, qui est la requête de table complète

mysql> alter table article  add key key_index(title);
Query OK, 0 rows affected (2.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4 Optimisation de l'utilisation de l'index de l'instruction de limite Pour. l'optimisation de l'instruction de limite, nous pouvons ajouter order by devant le champ d'index

Si le champ order by est un index, il recherchera d'abord le nombre spécifié de lignes de données dans le fichier d'index

mysql> explain select * from article where title like 'a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range//范围查询
possible_keys: key_index
          key: key_index
      key_len: 92//
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where title like '%a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表查询
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Une autre méthode d'optimisation pour la limite :

Couverture de l'index + association retardéePrincipe : Utiliser principalement la requête de couverture d'index et associer l'identifiant renvoyé par la couverture requête d'index avec l'identifiant de l'enregistrement que nous voulons interroger,

mysql> explain select sql_no_cache  * from article limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

mysql> explain select sql_no_cache  * from article order by id  limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY//使用到了索引
      key_len: 4
          ref: NULL
         rows: 90010
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

5. Le principe le plus à gauche de l'index composé (multi-colonnes) (souvent demandé dans les entretiens)

Tant que le plus à gauche Le champ de l'index composé apparaît lors de la requête, la requête d'index sera utilisée

Créer un index composite sur no_index et titre de la table article :

//给no_index和title创建一个复合索引
mysql> alter table article add key index_no_index_title(no_index,title);
Query OK, 0 rows affected (1.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

//查看创建后的结构
mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//删除no_index和title的索引
mysql> alter table article drop key key_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table article drop key key_no_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//复合索引使用情况
mysql> explain select * from article where title='ddsd1123d' and no_index=77777\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 97
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where  no_index=77777\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

五、慢查询日志

1、介绍

我们可以定义(程序员)一个sql语句执行的最大执行时间,如果发现某条sql语句的执行时间超过我们所规定的时间界限,那么这条sql就会被记录下来.

2、慢查询具体操作

1. 先开启慢日志查询

   查看慢日志配置

   mysql> show variables like '%slow_query%';
   +---------------------+--------------------------------------------------+
   | Variable_name       | Value                                            |
   +---------------------+--------------------------------------------------+
   | slow_query_log      | OFF                                              |
   | slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
   +---------------------+--------------------------------------------------+
   2 rows in set (0.00 sec)

   开启慢日志查询

   mysql> set global slow_query_log=on;
   Query OK, 0 rows affected (0.00 sec)

   再次检查慢日志配置

   mysql> show variables like '%slow_query%';
   +---------------------+--------------------------------------------------+
   | Variable_name       | Value                                            |
   +---------------------+--------------------------------------------------+
   | slow_query_log      | ON                                               |
   | slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
   +---------------------+--------------------------------------------------+
   2 rows in set (0.00 sec)

2. 去mysql配置文件my.ini中指定sql语句的界限时间和慢日志文件的路径

   慢日志的名称，默认保存在mysql目录下面的data目录下面

   log-slow-queries = 'man.txt'

   设置一个界限时间

   long-query-time=5

   重启

六、profile工具

1、介绍

通过profile工具分析一条sql语句的时间消耗在哪里

2、具体操作

1. 开启profile

2. 执行一条SQL，(开启之后执行的所有SQL语句都会被记录下来

   ，以查看某条sql语句的具体执行时间耗费哪里)

3. 根据query_id查找到具体的SQL

实例：

//查看profile设置
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |//未开启状态
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

//开启操作
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

//查看是否开启成功
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |//开启成功
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

具体查询

mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00150700 | show variables like '%profil%'              |
|        2 | 0.01481100 | select * from article where no_index=666666 |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000291 |
| checking permissions | 0.000007 |
| Opening tables       | 0.012663 |//打开表
| init                 | 0.000050 |
| System lock          | 0.000009 |
| optimizing           | 0.000053 |
| statistics           | 0.001566 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 0.000091 |//磁盘上的发送数据
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000006 |
| freeing items        | 0.000037 |
| cleaning up          | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

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!