Detaillierte Erläuterung der Indizes und Trigger in MySQL

1》Die Bedeutung und Eigenschaften des Index:

Was ist ein Index? Der Index entspricht der Katalogsequenz im Wörterbuch. Wenn Sie beispielsweise ein Wort „Stern“ abfragen Wenn wir es gemäß Pinyin nicht finden, müssen wir das gesamte Wörterbuch durchsuchen und abfragen. Um dieses Wort zu finden, müssen Sie, wenn Sie es nach Pinyin suchen, nur ein paar Seiten phonetischer Tabellen nachschlagen. Anhand der Lautfolge können Sie schnell herausfinden, auf welcher Seite des Wörterbuchs sich dieses Wort befindet. In der Datenbank kann der Index die Abfrage der Datenbank erheblich verbessern und auch die Leistung der Datenbank verbessern. Verschiedene Speicher-Engines definieren die maximale Länge des Indexes und die Anzahl der Indizes Unterstützt mindestens 16 Indizes für jede Tabelle und die Indexlänge beträgt mindestens 256 Byte.

Indexvorteile:
Seine Vorteile können die Abrufgeschwindigkeit von Daten für diejenigen mit Abhängigkeiten verbessern Die untergeordnete Tabelle und die übergeordnete Tabelle Die Tabelle kann die Abfragegeschwindigkeit bei gemeinsamen Abfragen verbessern.
  Nachteile von Indizes:
  Die Erstellung und Pflege von Indizes erfordert eine gewisse Menge an physischem Speicherplatz Das Einfügen von Daten erfolgt entsprechend der Indexreihenfolge, wodurch die Geschwindigkeit des Dateneinfügens verringert wird. Löschen Sie beim Einfügen von Daten zunächst den Index der Tabelle und fügen Sie ihn dann ein die Daten und erstellen Sie dann den Index, nachdem die Dateneinfügung abgeschlossen ist.

2》Klassifizierung von Indizes:

Zu den Indextypen von MySQL gehören: gewöhnlicher Index, eindeutiger Index, Volltextindex, einspaltiger Index, mehrspaltiger Index Index und räumlicher Index usw. ; 1 & gt; sexueller Index Verwendung Der eindeutige Parameter kann einen eindeutigen Index festlegen. Beim Erstellen eines eindeutigen Index muss der Wert des Index eindeutig sein. Wenn beispielsweise in der Tabelle „Student“ das Feld „user_name“ als eindeutiger Index festgelegt ist, muss dieser Wert eindeutig sein.
3>Volltextindex
Mithilfe des Parameters „Volltext“ kann ein Volltextindex festgelegt werden. Der Volltextindex kann nur für Felder vom Typ „char varchar“ oder „Text“ erstellt werden. Nur die MyISAM-Speicher-Engine unterstützt diesen Index.
MySQL5.6 InnoDB hat begonnen, den Volltextindex zu unterstützen


4 & gt; Nur einspaltiger Index für ein einzelnes Feld in der Tabelle der Index basierend auf dem Feld. Ein einspaltiger Index kann ein normaler Index, ein eindeutiger Index oder ein Volltextindex sein. Stellen Sie einfach sicher, dass der Index nur einem Feld entspricht.



5>Mehrspaltiger Index Mehrspaltiger Index erstellt einen Index für mehrere Felder der Tabelle, der auf verweist Mehrere Felder, die der Erstellung entsprechen. Sie können über diese Felder Abfragen durchführen. Bei Verwendung eines mehrspaltigen Index wird der Index jedoch nur verwendet, wenn das erste Feld dieser Felder abgefragt wird. Beispiel: Erstellen Sie einen mehrspaltigen Index für die Felder „ID“, „Name“ und „Geschlecht“ in der Tabelle. Dann wird der mehrspaltige Index nur verwendet, wenn die Abfragebedingung das ID-Feld verwendet; 6>Räumlicher Index

Räumliche Parameter verwenden Es kann auf einen räumlichen Index eingestellt werden. Räumliche Indizes können nur für räumliche Datentypen erstellt werden. Derzeit werden räumliche Indizes nur mit der MyISAM-Speicher-Engine unterstützt. Und der Feldwert dieses Index darf nicht leer sein.

                                                                                                                                                                                                   

3》So entwerfen Sie einen Index:
Um die Indexnutzung effizienter zu gestalten, müssen Sie beim Erstellen eines Index berücksichtigen, für welche Felder und welchen Typs ein Index erstellt werden soll des Index zu erstellen;
2> Erstellen Sie Indizes für Felder, die häufig Sortier-, Gruppierungs- und Vereinigungsoperationen erfordern:
Bei Feldern, die häufig eine Sortierung nach Gruppierung nach eindeutigen und Vereinigungsoperationen erfordern, verschwenden Sortieroperationen viel Zeit Für diese Felder können Sortiervorgänge effektiv vermieden werden.
3> Erstellen Sie Indizes für Felder, die häufig als Abfragebedingungen verwendet werden:
3 Das Einrichten von Indizes für solche Felder kann die Abfragegeschwindigkeit der gesamten Tabelle verbessern 4> Begrenzen Sie die Anzahl der Indizes:
Die Anzahl der Indizes ist nicht besser, da jeder Index Speicherplatz benötigt. Je mehr Indizes vorhanden sind, desto mehr Speicherplatz ist erforderlich. Je mehr Indizes aktualisiert werden, desto länger wird die Tabelle benötigt kleine Datenmenge:
Wenn der Indexwert sehr lang ist, wird die Abfragegeschwindigkeit beeinträchtigt. Beispielsweise dauert die Volltextindizierung eines Felds vom Typ Char(100) definitiv länger als die eines Felds vom Typ char(10). ;
6> Indizes löschen, die nicht mehr oder selten verwendet werden:
Wenn die Daten in der Tabelle häufig aktualisiert werden oder die Art und Weise der Datenverwendung geändert wird, können einige der ursprünglichen Indizes nicht mehr verwendet werden mehr benötigt werden, und der DBA sollte diese Indizes regelmäßig finden und löschen, um die Auswirkungen der Indizes auf Aktualisierungsvorgänge zu verringern;




4》

So erstellen Sie einen Index: Syntax:

[unique |fulltext|spatial] index |key                                                                                                                                                            🎜>                                                                                                                               .
Alias ​​​​optionaler Parameter, geben Sie dem erstellten Index einen neuen Namen. Der optionale Längenparameter gibt die Länge des Index an. Zur Angabe der Länge muss es sich um einen Zeichentyp handeln.
ASC aufsteigende Reihenfolge, DESC absteigende Reihenfolge.





        1>Einen normalen Index erstellen
              🎜>

                                                             index, um die ID als normalen Index festzulegen.             Mysql> show create table aatestG;

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

                                                                                                    Verwendung der Unique-Index-Eindeutigkeitsbeschränkung                            > ; 🎜 >

*******Version 5.6 unterstützt Volltextindex

　　　　 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)

 

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der Indizes und Trigger in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!