Detailed explanation of indexes and triggers in MySQL

1》The meaning and characteristics of index:

What is an index? The index is equivalent to the directory sequence in the dictionary. For example, if you query a word "star", if you don't find it according to pinyin, then we need Traverse the entire dictionary and query it. To find this word, if you look for it according to pinyin, you only need to look it up in a few pages of phonetic tables. You can quickly find out which page of the dictionary this word is on through the phonetic sequence. In the database, the index is built on the table. The index can greatly improve the query of the database and also improve the performance of the database. Different storage engines define the maximum length of the index and the number of indexes. All storage engines support at least 16 indexes for each table, and the index length supports at least 256 bytes; The child table and parent table can improve the query speed during joint query.
      Disadvantages of indexes:
            It takes time to create and maintain indexes, and indexes need to occupy physical space. Each index needs to occupy a certain amount of physical space. A large number of indexes will affect the insertion of data, and the database system will sort according to the index. order, which reduces the speed of data insertion;
​ ​
Solution: When inserting data, first temporarily delete the index of the table, then insert the data, and then create the index after the data insertion is completed.


2》Classification of indexes:

The index types of Mysql include: ordinary index, unique index, full-text index, single-column index, multi-column index and spatial index, etc. ;

1 & gt; ordinary index When creating ordinary indexes, do not add any restrictions, such indexes can be created on any data type,
2 & gt The unique parameter can set a unique index. When creating a unique index, the value of the index must be unique. For example, in the student table, if the user_name field is set as a unique index, then this value must be unique.
3>Full-text index
Use the fulltext parameter to set it to a full-text index. Full-text indexes can only be created on fields of char varchar or Text type. Only the MyISAM storage engine supports this index.
MySQL5.6 InnoDB began to support the full -text index


# 4 & GT; single -column index
created index on the single field in the table. Single -column indexed only the index based on the field. A single-column index can be a normal index, a unique index, or a full-text index. Just make sure that the index only corresponds to one field.

## 5>Multi-column index Multi-column index creates an index on multiple fields of the table, which points to Multiple fields corresponding to creation. You can query through these fields. However, using a multi-column index, the index will only be used when querying the first field among these fields. For example: Create a multi-column index on the id, name and sex fields in the table. Then, the multi-column index will be used only when the query condition uses the id field; It can be set to a spatial index. Spatial indexes can only be built on spatial data types. Currently, spatial indexes are only supported using the MyISAM storage engine. And the field value of this index cannot be empty.

          Exercise: Check if there are other types of indexes,

                Hash index Primary key index B-tree index

3》How to design index:
In order to make index usage more efficient, when creating an index, you must consider creating indexes on those fields and what type of index to create;
        1> Unique index setting:
            The value of the unique index is unique, and a certain record can be determined more quickly through the index; If it is a name, there may be a situation with the same name, which will slow down the query speed.
2>Create indexes for fields that often require sorting, grouping, and union operations:
​ ​ ​ ​For fields that often require order by group by distinct and union operations, the sorting operation will waste a lot of time. If you create indexes for these fields, Sorting operations can be effectively avoided;
3>Create indexes for fields that are often used as query conditions:
If a field is commonly used to query conditions, the query speed of this field will affect the speed of the table, so Establishing an index for such fields can improve the inquiries of the entire table;
4 & gt; limit the number of indexes:
index is not as much as possible, and each index needs to occupy disk space. The more indexes, the more disk space is required. When modifying the table, it is troublesome to reconstruct and update the indexes. The more indexes are updated, the more time the table becomes;
5> Try to use indexes with a small amount of data :
If the index value is very long, the query speed will be affected. For example, full-text indexing of a Char(100) type field will definitely take more time than a char(10) type field.
6 & gt; Delete the index that is no longer used and rarely uses: the data in the
table is largely updated, or after the data use is changed, some of the original indexes may not be needed. The DBA should be regularly. Find these indexes and delete them to reduce the impact of the indexes on update operations;

##4》 How to create an index：

## Syntax: [unique |fulltext|spatial] index |key
　　　[Alias] (Attribute name 1 [(length)] [ASC|DESC] )
　　unique optional parameter, representing a unique index             fulltext Optional parameter, representing the full-text index           Spatial optional parameter, representing the spatial index
       
index and key are used to specify the field to choose one of the two for the index.

Alias ​​optional parameter, give the created index a new name. The optional length parameter specifies the length of the index. It must be of character type to specify the length. ​ ​ ASC ascending order, DESC descending order.

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

## Use index to set the id to a normal index.               Mysql> show create table aatest\G; Check the detailed structure of the table             Mysql>explain select * from aatest where id=1 \G; Check whether the index is used.

## This & GT; unique constraint            

create table aatest2(
                             　　　　　　　　　　　　　id int unique,
                             　　　　　　　　　　　　   name varchar(20),
                             　　　　　　　　　　　　　unique index aatest_id(id ASC));

## 　　 3>Create full-text index                  

 create table aatest3(
                                       　　　　　　　　　　　　　id int,
                                       　　　　　　　　　　　　　info varchar(20),
                                       　　　　　　　　　　　　   fulltext index aatest3_info(info));

# ********* 5.6 version has supported the full text 索

##

　　　　 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)

 

The above is the detailed content of Detailed explanation of indexes and triggers in MySQL. For more information, please follow other related articles on the PHP Chinese website!