Explication détaillée des index et des déclencheurs dans MySQL

1》La signification et les caractéristiques de l'index :

Qu'est-ce qu'un index ? L'index est équivalent à la séquence du catalogue dans le dictionnaire Par exemple, si vous interrogez un mot « étoile », si vous ne le faites pas. Si nous ne le trouvons pas selon le pinyin, nous devons parcourir tout le dictionnaire et l'interroger. Pour trouver ce mot, si vous le recherchez selon le pinyin, il vous suffit de le rechercher dans quelques pages de tableaux phonétiques. Vous pouvez rapidement savoir sur quelle page du dictionnaire se trouve ce mot grâce à la séquence phonétique. Dans la base de données, l'index est construit sur la table. L'index peut grandement améliorer la requête de la base de données et également améliorer les performances de la base de données. Différents moteurs de stockage définissent la longueur maximale de l'index et le nombre d'index. prend en charge au moins 16 index pour chaque table et la longueur de l'index est d'au moins 256 octets

Avantages de l'index :
Ses avantages peuvent améliorer la vitesse de récupération des données, pour ceux qui ont des dépendances La table enfant et parent table peut améliorer la vitesse des requêtes lors de l’exécution de requêtes conjointes.
Inconvénients des index :
La création et la maintenance d'index prennent du temps. Les index doivent occuper de l'espace physique. Chaque index doit occuper une certaine quantité d'espace physique. le système de base de données triera selon l'ordre de l'index, ce qui réduit la vitesse d'insertion des données ;
　
Solution : lors de l'insertion de données, supprimez d'abord temporairement l'index de la table, puis insérez les données, puis créez l'index. une fois l’insertion des données terminée.

2》Classification des index : Les types d'index de Mysql incluent : index ordinaire, index unique, index de texte intégral, index à une seule colonne, multi-colonnes index et index spatial, etc. ;
1 & gt; index ordinaire
Lors de la création d'index ordinaires, il n'y a aucune restriction sur n'importe quel type de données,
2 & gt; index sexuel
Utilisation Le paramètre unique peut définir un index unique Lors de la création d'un index unique, la valeur de l'index doit être unique. Par exemple, dans la table student, si le champ user_name est défini comme index unique, alors cette valeur doit être unique.
3>Index de texte intégral
L'utilisation du paramètre fulltext peut être définie sur un index de texte intégral qui ne peut être créé que sur des champs de type char varchar ou Text. Seul le moteur de stockage MyISAM prend en charge cet index.
MySQL5.6 InnoDB a commencé à prendre en charge l'index de texte intégral
4 & gt; index à colonne unique Créez un index sur un seul champ dans la table indexé uniquement. l'index basé sur le champ. Un index à colonne unique peut être un index normal, un index unique ou un index de texte intégral. Assurez-vous simplement que l'index ne correspond qu'à un seul champ.

5>Index multi-colonnes L'index multi-colonnes crée un index sur plusieurs champs de la table, qui pointe vers Plusieurs champs correspondant à la création. Vous pouvez effectuer une requête via ces champs. Cependant, en utilisant un index multi-colonnes, l'index ne sera utilisé que lors de l'interrogation du premier champ parmi ces champs. Par exemple : Créez un index multi-colonnes sur les champs id, name et sex de la table. Ensuite, l'index multi-colonnes ne sera utilisé que lorsque la condition de requête utilise le champ id ; 6>Index spatial Utiliser les paramètres spatiaux Il peut être défini sur un index spatial. Les index spatiaux ne peuvent être construits que sur des types de données spatiales. Actuellement, les index spatiaux ne sont pris en charge qu'à l'aide du moteur de stockage MyISAM. Et la valeur du champ de cet index ne peut pas être vide.
                                                                                                                                                                                                               

3》Comment concevoir un index :
Afin de rendre l'utilisation de l'index plus efficace, lors de la création d'un index, vous devez considérer sur quels champs créer un index et sur quel type d'index à créer ;
                                                                                                                                                                                              S'il s'agit d'un nom, il peut y avoir une situation avec le même nom, ce qui ralentira la vitesse des requêtes.
2> Créez des index pour les champs qui nécessitent souvent des opérations de tri, de regroupement et d'union :
Pour les champs qui nécessitent souvent un tri par groupe par opérations distinctes et d'union, les opérations de tri vous feront perdre beaucoup de temps si vous créez des index. pour ces champs, les opérations de tri peuvent être efficacement évitées ;
3> Créez des index pour les champs qui sont souvent utilisés comme conditions de requête :
3 L'établissement d'index pour de tels champs peut améliorer la vitesse de requête de la table entière ; 4> Limiter le nombre d'index :
Le nombre d'index n'est pas meilleur, car chaque index nécessite de l'espace disque. Plus il y a d'index, plus il faut d'espace disque. Lors de la modification de la table, il est difficile de reconstruire et de mettre à jour les index, plus la table prend du temps ; petite quantité de données :
Si la valeur d'index est très longue, la vitesse de requête sera affectée. Par exemple, l'indexation en texte intégral d'un champ de type Char(100) prendra certainement plus de temps qu'un type char(10). ;
6> Supprimer les index qui ne sont plus utilisés ou rarement utilisés :
Lorsque les données du tableau sont fréquemment mises à jour ou que la façon dont les données sont utilisées est modifiée, certains des index d'origine peuvent ne plus être utilisés. n'est plus nécessaire, et le DBA doit régulièrement rechercher ces index et les supprimer pour réduire l'impact des index sur les opérations de mise à jour ;

4》

Comment créer un index :

Syntaxe :

[unique |fulltext|spatial] index |key                                                                                                                                                                     🎜>                                                                                                                                     .
Paramètre facultatif Alias ​​​​, donne un nouveau nom à l'index créé.
Le paramètre facultatif length spécifie la longueur de l'index. Il doit être de type caractère pour spécifier la longueur. ASC par ordre croissant, DESC par ordre décroissant.






        1>Créer un index normal              🎜>

                                                                             Utiliser l'index pour définir l'identifiant comme index normal.
            Mysql> show create table aatestG; Vérifiez la structure détaillée de la table               Mysql>

　Mysql->create table aatest(
                       　　　　　　　　　　id int,
                       　　　　　　　　　　name varchar(20),
                       　　　　　　　　　　sexboolean,
                       　　　　　　　　　　index(id));

                                                                                                    Utilisation de la contrainte unique d'index unique
                            > 🎜 > *******La version 5.6 prend en charge l'index en texte intégral

　　　　 4>创建单列索引
             　　　　　　

　create table aatest4(
                                        　　　　　　　　　　　　　id int,
                                        　　　　　　　　　　　　  subject varchar(30),
                                        　　　　　　　　　　　　 index aatest4_st(subject(10)));subject(10)指定索引的长度

               5>创建多列索引
             　　　　　　多列索引，是在表上多个字段创建一个索引。
             　　　　　　

　　create table aatest5(
                                         　　　　　　　　　　　　　　id int,
                                         　　　　　　　　　　　　　   name varchar(20),
                                         　　　　　　　　　　　　　　sex char(4),
                                         　　　　　　　　　　　　　　index aatest5_ns(name,sex));

5》在已经有的表上建立索引：
     　　语法：
      　　　　create [unique | fulltext | spatial ] index 索引名
      　　　　on  表名 (属性名 [(长度)] [ ASC | DESC]);

      　　　　alter table 表名 ADD [unique | fulltext | spatial ] index 索引名
      　　　　(属性名 [(长度)] [ ASC | DESC]);

      　　　　1>创建普通索引
          　　　　　　

create index zytest_id  on zytest(id);
           　　　　　　alter table zytest add index zytest_id(id);

      　　　　2>创建唯一索引          　　　　　　

create unique index zytest1_id on zytest1(id);
           　　　　　　alter table zytest1 add unique index zytest1_id(id);

      　　　　3>创建全文索引       　　　　　

　create fulltext index zytest2_id on zytest2(info);
           　　　　　　alter table zytest2 add fulltext zytest_2(info);

              4>创建单列索引        　　　　　　

create index zytest3_addr on zytest3(address(4));
           　　　　　　alter table zytest3 add index zytest3_addr(address(4));

      　　　　5>创建多列索引
          　　　　　　

create index zytest4_na on zytest4(name,address);
           　　　　　　alter table zytest4 add index zytest4_na(name,address);
6》如何删除索引：

     　　如果没有别名，+索引名称
     　　语法：drop index 索引名 ON 表名
      　　　　   drop indexid on zytest;

      　　如果有别名的话。直接+索引别名
      　　语法：drop index 索引别名 ON 表名

================触发器：

1》触发器的含义与作用：

        　　　　触发器(trigger)是由事件来触发某个操作，主要是由insert update delete等事件来触发某种特定的条件，满足触发器的触发条件时，数据库就会执行触     发器定义的程序语句，比如：当学生表当中增加了一个学生记录，学生的总数就必须同时改变。可以在这里创建一个触发器，每次增加一个学生的记录。
   就执行一次计算学生的总数量的操作。这可以保证每次增加学生后的记录统计一直保持最新；触发器触发的执行语句可以只有一个。也可能有多个；

         语法：
        　　　　create trigger 触发器名称  before|after 触发事件
        　　　　on 表名 for each row 执行语句
        　　　　berfore指触发事件之前执行的触发语句。
        　　　　After 表示在触发事件之后执行语句
        　　　　触发事件包括(insert update delete)等
        　　　　on表名在XXX表之上
        　　　　执行语句指的是XXSQL语句和触发事件类型要对应

        　　A  触发器  B存放A总记录,
        　　当A表删除一条数据之后--->触发器将统计的最终结果写入到B表当中，用户每次想要得到A表的结果，只需要去B表当中查询就行了。
         　　select count(*) from A >B表当中。

2》创建触发器

       　　1>创建一个表alvin
            　　　　

create table alvin(
             　　　　　　　　userid int(10),
             　　　　　　　　username varchar(20),
             　　　　　　　　old int(4),
             　　　　　　　　address varchar(30));

       　　2>创建一个表为trigger_time用来存放触发后条件的结果
            　　　　　

create table trigger_time(
             　　　　　　　　　　zhixing_time time);
             　　　　　Query OK, 0 rows affected (0.15 sec)

       　　3>创建只有单个执行语句的触发器
            　　　　　

create trigger alvin1 before insert
             　　　　　　　　　　on alvin for each row
             　　　　　　insert into trigger_time values(now());
             　　　　  Query OK, 0 rows affected (0.07 sec)

        4>创建有多个执行语句的触发器

       　　举例一、
        　　　　　　root@zytest 10:49>delimiter &&#告诉MYSQL该命令段下面的内容在提示结束后再执行分析。默认是以分号(;)执行
　　　　　

　root@zytest 10:53>create trigger alvin3 after delete    　　　　　　　　　
　->on alvin for each row    　　　　　　　　   
　-> begin    　　　　　　　　　
　->insert into trigger_time values('21:01:01');    　　　　　　　　　
　->insert into trigger_time values('22:01:01');    　　　　　　　　  
　->end　　　　    　　　　　　　　 
　->&&　　　　　Query OK, 0 rows affected (0.05 sec)
             root@zytest 10:54>delimiter;#结束退出，注意分号要有空格　　　 
             root@zytest 10:57>select * from alvin;　　　　
             +--------+-------------+------+----------+　　　　
             | userid | username    | old  | address  |　　　　
             +--------+-------------+------+----------+　　　　
             |    110 | zengxiaohua |   28 | tianxing |　　　　
             +--------+-------------+------+----------+　　　　
             1 row in set (0.00 sec)
             root@zytest 11:07>delete from alvin where userid='110';#执行删除动作看看触发器是否成功　　　　
             Query OK, 1 row affected (0.05 sec)　　　　
             root@zytest 11:07>select * from trigger_time;#:查看触发器的执行结果　　　　　　
             +--------------+　　　　　　
             | zhixing_time |　　　　　　
             +--------------+　　　　　　
             | 19:09:41     |　　　　　　
             | 21:01:01     |　　　　　　
             | 22:01:01     |　　　　　　
             +--------------+　　　　
             3 rows in set (0.00 sec)

     举例二、
　　　　alvin1表存放了学生的信息。每次增加(insert)一个学生的信息。就触发一次统计。统计结果存入aac表里面；
　　　　首先创建一个alvin1表结构
　　　　　　

create table alvin1(
　　　　　　　　　　user_id int(10),
　　　　　　　　　　username varchar(20),
　　　　　　　　　　old tinyint(4),
　　　　　　　　　　address varchar(30));
　　　　　　create table aac(
　　　　　　　　　　my_count int);

　　　　然后开始创建一个触发器
　　　　　　

delimiter&&
　　　　　　create trigger alvin123 before insert on
　　　　　　alvin1 for each row begin
　　　　　　declare ycount int(10);#:申明变量类型
　　　　　　set ycount=(select count(*) from alvin1);#:给变量赋值
　　　　　　insert into aac(my_count) values(ycount);#:调用变量
　　　　　　end&&
　　　　　　delimiter ;

  看看before和after的区别
　　　　

create trigger alvin123 after insert on
　　　　　　zyalvin1 for each row
　　　　　　begin
　　　　　　declare ycount int(10);
　　　　　　set ycount=(select count(*) from zyalvin1);
　　　　　　insert into aac(my_count)values(ycount);
　　　　　　end&&
　　　　root@zytest 16:24>insert into alvin1 values('1001','zhangsan','18','China');开始测试
　　　　root@zytest 16:24>select * from aac;查看触发器统计的结果。

3》查看触发器：

    　　1> 查看所有触发器，提前要进入某库
         　　　　#: show triggers \G;

    　　2>在triggers表中查看触发信息
         　　　　root@zytest 11:20>use information_schema;
         　　　　root@zytest 11:19>select * from information_schema.triggers \G;
         　　小技巧：所有触发器的信息都存在information_schema库中的triggers表里面，在使用select 查询单个触发器的时候。可以根据triggers表里面的字段名称
         　　　　　　Trigger_name字段进行查询。
         　　　　root@information_schema 11:24>select * from triggers where trigger_name='alvin1'\G;

  4》删除触发器：

　　语法：
       　　1>删除alvin1触发器
            　　　

　root@(none) 12:18>use zytest;
             　　　　　　Database changed
             　　　　root@zytest 12:18>drop trigger alvin1;
             　　　　Query OK, 0 rows affected (0.03 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!