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:

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.

                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;

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

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

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

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

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

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

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

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

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

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

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

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

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

　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)

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 ;

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;查看触发器统计的结果。

　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!