Shulou(Shulou.com)06/01 Report--

An index is a structure that sorts the values of one or more columns in a database table (for example, the name (name) column of the employee table). The database index is like the catalogue in front of a book, which can speed up the retrieval of the database.

The implementation of the index is usually B-tree and its variant B + tree.

Benefits of indexing:

Speed up the search or sort of records in the table

It can greatly improve the performance of the system.

Disadvantages of the index:

It takes time to create and maintain an index, which increases as the amount of data increases

The index needs to take up the physical space, in addition to the data table occupies the data space, each index also takes up a certain amount of physical space, if you want to establish a clustered index, then the space required will be more.

When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

Trigger:

Trigger is a special type of stored procedure that is triggered by events and executed automatically. A trigger is when an operation is performed on a table. When operating such as update, insert, delete, the system will automatically call and execute the corresponding trigger on the table. It cannot be called explicitly, but is automatically activated when a record is inserted, updated, or deleted into the table.

Classification:

DML (data manipulation language Data Manipulation Language) trigger: means that the trigger will be enabled when a DML event occurs in the database. DML events are insert, update, and delete statements that modify data in a table or view.

DDL (data definition language Data Definition Language) trigger: it is enabled when a DDL event occurs in the server or database. DDL events refer to create, alter, and drop statements in a table or index.

Login trigger: it is triggered when the user logs in to the SQL SERVER instance to establish a session (SQL Server 2000 is not supported).

Things:

A thing is a series of operations that are performed as a single logical unit of work, either completely or not at all.

Transaction characteristics:

Atomic: the operations contained in a transaction are treated as a logical unit in which either all the operations succeed or all fail.

Consistency: only legitimate data can be written to the database, otherwise the transaction should roll it back to its original state.

Isolation: transactions allow multiple users to access the same data concurrently without compromising the correctness and integrity of the data. At the same time, the modification of parallel transactions must be independent of the modifications of other parallel transactions.

Durability: after the transaction ends, the results of the transaction must be able to be solidified.

MySql has an important feature called Pluggable Storage Engine Architecture (replaceable storage engine architecture). There are two important storage engines: MyISAM and InnoDB. MyISAM does not support transaction processing, but it is fast. InnoDB supports row locking and transaction processing, which is slightly slower than MyISAM. A new storage engine, Falcon, also supports transaction processing.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.