Index and trigger, index trigger

Index and trigger, index trigger

1. Meanings and features of indexes:

What is an index? The index is equivalent to a directory sequence table in the dictionary. For example, if you want to query a "star" word, if you do not search for it in pinyin, You need to traverse the entire dictionary to one side. This word can be found. If you find it in pinyin, you only need to query it in several audio sequence tables. You can quickly find the word in the Lexicographic Order. In databases, indexes are built on tables, which can be very large.
Improves database query and database performance. Different storage engines define the maximum index length 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;

Index advantages:
Its advantages can improve the data retrieval speed. For dependency-related sub-tables and parent tables, the query speed can be improved during joint queries.
Disadvantages of indexing:
It takes time to create and maintain indexes. Indexes occupy physical space. Each index occupies a certain amount of physical space. A large number of indexes will affect data insertion, the database system will sort the data according to the index, which reduces the data insertion speed;
　　Solution: when data is inserted, the index of the table is deleted temporarily, and then the data is inserted. After the data is inserted, the index is created.

2. Index classification:
Mysql indexes include normal indexes, unique indexes, full-text indexes, single-column indexes, multi-column indexes, and spatial indexes;

1> Common Index
When creating a common index, no restrictions are attached. Such indexes can be created on any data type,
2> unique index
You can use the unique parameter to set a unique index. When creating a unique index, the value of this index must be unique. For example, in the student table, if the user_name field is set to a unique index, the value must be unique.
3> full-text index
You can use the fulltext parameter to set full-Text indexes. Full-Text indexes can only be used to create char varchar or Text fields. Only the storage engine of MyISAM supports this index.MySQL innodb started to support full-text indexing
4> Single Column Index
Create an index on a single field in the table. A single column index is indexed only based on this field. A single-column index can be a common index, a unique index, or a full-text index. Make sure that the index corresponds to only one field.

5> multi-column Index
A multi-column index creates an index on multiple fields in the table. This index points to multiple fields corresponding to the table when it is created. You can query these fields. However, if multiple columns of indexes are used, indexes are used only when the first field in these fields is queried. For example, if you create an index with multiple columns for the id, name, and sex fields in the table, multiple columns of indexes are used only when the id field is used for the query condition;
6> spatial indexes
The spatial parameter can be set to spatial indexes. spatial indexes can only be set to spatial data types. Currently, only MyISAM storage engine supports spatial indexes. The field value of this index cannot be blank.
Exercise: Check whether there are other types of indexes,
Hash index primary key index B-tree Index

3. How to design indexes:
To make indexes more efficient, you must consider the types of indexes and indexes created on those fields when creating indexes;
1> unique index settings:
The value of a unique index is unique and can be used to determine a record more quickly;
For example, the ID card number is unique and you can create a unique index. If it is a name, the same name may occur, thus reducing the query speed.
2> Create an index for fields that frequently require sorting, grouping, and Union operations:
Fields that require operations such as order by group by distinct and union are often sorted, which wastes a lot of time. If you create an index for these fields, you can effectively avoid sorting operations;
3> Create an index for fields that are often used as query conditions:
If a field is frequently used to query conditions, the query speed of this field affects the speed of this table. Therefore, creating an index for this field can increase the query speed of the entire table;
4> limit the number of indexes:
The larger the number of indexes, the better. Each index occupies disk space. The more indexes you need, the more disk space you need. When you modify a table, it is difficult to reconstruct and update the indexes. The more indexes you need to update the table, the more time is wasted;
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, it takes more time to index the full text of a Char (100) field than char (10) type fields require more time;
6> Delete indexes that are no longer in use and rarely used:
After the data in the table is updated in large quantities or the usage of the data is changed, some of the original indexes may not be needed. The DBA should regularly find these indexes and delete them, this reduces the impact of indexes on update operations;

4How to create an index:

Syntax:
[Unique | fulltext | spatial] index | key
　　　　[Alias] (attribute name 1 [(length)] [ASC | DESC])
Unique Index
Fulltext: an optional parameter that represents the full-text index.
Spatial: an optional parameter that represents a spatial index.
　　　　Index and key are used to specify a field as the index.
Alias: an optional parameter. It is used to obtain a new name for the created index.
Length: an optional parameter that specifies the length of the index. The length can be specified only when it is of the character type.
ASC in ascending order and DESC in descending order.

1> create a common index
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 table structure.
Mysql> explain select * from aatest where id = 1 \ G; check whether the index is used.

2> create a unique Cable
Unique indexes use unique for constraints
Create table aatest2 (
Id int unique,
Name varchar (20 ),
Unique index aatest_id (id ASC ));

3> Create a full-text index
Create table aatest3 (
Id int,
Info varchar (20 ),
Fulltext index aatest3_info (info ));
* ****** Full-text indexing is supported in version 5.6.

4> create a single column Index
Create table aatest4 (
Id int,
Subject varchar (30 ),
Index aatest4_st (subject (10); subject (10) Specify the index Length

5> create multi-column Indexes
A multi-column index is used to create an index for multiple fields in a table.
Create table aatest5 (
Id int,
Name varchar (20 ),
Sex char (4 ),
Index aatest5_ns (name, sex ));

5. Create an index on an existing table:
Syntax:
Create [unique | fulltext | spatial] index name
On Table Name (attribute name [(length)] [ASC | DESC]);

Alter table name ADD [unique | fulltext | spatial] index name
(Attribute name [(length)] [ASC | DESC]);

1> create a common index
Create index zytest_id on zytest (id );
Alter table zytest add index zytest_id (id );

2> create a unique index
Create unique index zytest1_id on zytest1 (id );
Alter table zytest1 add unique index zytest1_id (id );

3> Create a full-text index
Create fulltext index zytest2_id on zytest2 (info );
Alter table zytest2 add fulltext zytest_2 (info );

4> create a single column Index
Create index zytest3_addr on zytest3 (address (4 ));
Alter table zytest3 add index zytest3_addr (address (4 ));

5> create multi-column Indexes
Create index zytest4_na on zytest4 (name, address );
Alter table zytest4 add index zytest4_na (name, address );6. How to delete an index:

If no alias exists, + index name
Syntax: drop index name ON table name
Drop indexid on zytest;

If there is an alias. Direct + index alias
Syntax: drop index alias ON table name

================== Trigger:

1Trigger meaning and function:

A trigger is an event that triggers an operation. It is mainly used by insert update delete and other events to trigger certain conditions. When the trigger conditions are met, the database executes the Program Statement defined by the trigger. For example, when a student record is added to the student table, the total number of students must change at the same time. You can create a trigger here to add a student record each time.
Calculate the total number of students at a time. This ensures that the record statistics are kept up-to-date after each student increase; the trigger can trigger only one execution statement. There may also be multiple;

Syntax:
Create trigger name before | after trigger event
On Table name for each row execution statement
Berfore refers to the trigger statement executed before the event is triggered.
After indicates that the statement is executed After the event is triggered.
Trigger events include (insert update delete)
The on table name is on top of the XXX table.
The execution statement indicates that the XXSQL statement corresponds to the trigger event type.

Trigger a B stores A's total records,
After A row of data is deleted in Table A, the trigger writes the final result of the statistics to table B. Each time you want to get the result of Table A, you only need to query it in table B.
Select count (*) from A> B.

2Create a trigger

1> Create a table alvin
Create table alvin (
Userid int (10 ),
Username varchar (20 ),
Old int (4 ),
Address varchar (30 ));

2> Create a table named trigger_time to store the results of the trigger condition.
Create table trigger_time (
Zhixing_time time );
Query OK, 0 rows affected (0.15 sec)

3> Create a trigger with only one statement executed
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> Create a trigger with multiple execution statements

Example 1,
Root @ zytest> delimiter & # Tell MYSQL to execute the analysis after the prompt is complete. The default value is semicolon (;).
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> delimiter; # End and exit. Note that there must be spaces in the semicolon.

Root @ zytest 10: 57> select * from alvin;
+ -------- + ------------- + ------ + ---------- +
| Userid | username | old | address |
+ -------- + ------------- + ------ + ---------- +
| 110 | zengxiaohua | 28 | tianxing |
+ -------- + ------------- + ------ + ---------- +
1 row in set (0.00 sec)

Root @ zytest> delete from alvin where userid = '2013'; # Run the delete action to check whether the trigger is successful.
Query OK, 1 row affected (0.05 sec)

Root @ zytest 11: 07> select * from trigger_time; #: view the trigger execution result
+ -------------- +
| Zhixing_time |
+ -------------- +
| 19:09:41 |
| 21:01:01 |
| 22:01:01 |
+ -------------- +
3 rows in set (0.00 sec)

Example 2,
Alvin1 table stores student information. Add (insert) the information of one student each time. Trigger a statistics. The statistical results are stored in the aac table;
First, create an alvin1 table structure.
Create table alvin1 (
User_id int (10 ),
Username varchar (20 ),
Old tinyint (4 ),
Address varchar (30 ));

Create table aac (
My_count int );
Create a trigger.
Delimiter &&
Create trigger alvin123 before insert on
Alvin1 for each row begin
Declare ycount int (10); #: declare the variable type
Set ycount = (select count (*) from alvin1); #: assign a value to the variable
Insert into aac (my_count) values (ycount); #: Call the variable
End &&
Delimiter;

Let's see the difference between before and 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> insert into alvin1 values ('201312', 'hangsan ', '18', 'China'); start the test
Root @ zytest> select * from aac; view the trigger statistics.

3View triggers:

1> View All triggers. You must enter a database in advance.
#: Show triggers \ G;

2> View trigger information in the triggers table
Root @ zytest 11: 20> use information_schema;
Root @ zytest 11: 19> select * from information_schema.triggers \ G;
Tip: All trigger information is stored in the triggers table in the information_schema database. When you use select to query a single trigger. Based on the field names in the triggers table
Query the Trigger_name field.
Root @ information_schema 11: 24> select * from triggers where trigger_name = 'alvin1' \ G;

4Delete trigger:

Syntax:
1> Delete the alvin1 trigger
Root @ (none) 12:18> use zytest;
Database changed
Root @ zytest 12:18> drop trigger alvin1;
Query OK, 0 rows affected (0.03 sec)