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

Database transaction: a series of operations performed as a single logical unit of work, either completely or not at all. Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related operations into a unit that either succeeds or fails all, you can simplify error recovery and make the application more reliable.

1)。 Transaction related attributes:

1. Atomic transactions must be atomic units of work; either all or none of their data modifications are performed. Usually, the operations associated with a transaction have a common goal and are interdependent; if the system performs only a subset of these operations, it may undermine the overall goal of the transaction. Atomicity eliminates the possibility that the system will deal with a subset of operations. Consistency

All data must be kept in a consistent state when the transaction is completed. In the relevant database, all rules must be applied to the modification of the transaction to maintain the integrity of all data. At the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) must be correct

3: isolation

Changes made by concurrent transactions must be isolated from changes made by any other concurrent transactions. The state in which the data is in when the transaction views the data, either the state before it is modified by another concurrent transaction or after it is modified by another transaction, and the transaction does not view the data in the intermediate state. This is called isolation because it can reload the starting data and replay a series of transactions so that the state at the end of the data is the same as that in which the original transaction was executed. The highest isolation level is obtained when the transaction is serializable. At this level, the results obtained from a set of transactions that can be executed in parallel are the same as those obtained by running each transaction continuously. Because a high degree of isolation limits the number of transactions that can be executed in parallel, some applications lower the isolation level in exchange for greater throughput

4. Persistence

After the transaction is completed, its impact on the system is permanent. The modification will be maintained even in the event of a fatal system failure.

2)。 Three models of transactions:

1. An implicit transaction means that every data operation statement automatically becomes a transaction, the beginning of the transaction is implicit, and the end of the transaction is clearly marked.

two。 Explicit transactions are transactions with explicit start and end tags, and each transaction has explicit start and end tags

3. Automatic transactions are automatically defaulted by the system, and start and end are not marked.

3)。 Statements that use transactions:

Start things: BEGIN TRANSACTION

Submit things: COMMIT TRANSACTION

Rollback transaction: ROLLBACK TRANSACTION

Index: an index is a data structure that sorts the values of one or more columns in a database table and is a database object used to improve the speed of accessing data in a database table. An index is like a catalog of books. If you don't have an index, you need to traverse the entire database table to find a specific value in the database, but after you have an index, you can find it in the index, which helps to get information more quickly.

Indexes can be divided into clustered indexes and nonclustered indexes.

Clustered index: in a non-clustered index, the storage order of table data is independent of the index order; only one clustered index can be created on a table, because the physical order of real data can only be one. If a table does not have a clustered index, it is called a "heap". The rows in the table are not in a specific order, and all new rows are added to the end of the table.

The basic information contained in an index record is: key values (values of all fields specified when defining the index) + logical pointers (to data pages or another index page)

Depending on the functionality of the database, you can create three indexes in the database designer:

Unique index: an index that does not allow any two rows to have the same index value

When there are duplicate key values in existing data, most databases do not allow the newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the employee table, no two employees can have the same last name

Primary key index: this index requires that each value in the primary key be unique. It also allows quick access to data when primary key indexes are used in queries

Primary key: a database table often has a combination of one or more columns whose values uniquely identify each row in the table

Clustered index

In a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values. A table can contain only one clustered index; if an index is not a clustered index, the physical order of rows in the table does not match the logical order of key values. Clustered indexes usually provide faster data access than nonclustered indexes.

Although the purpose of indexing is to speed up the lookup or sorting of records in a table, there is a price to pay for setting an index for a table: first, it increases the storage space of the database; second, it takes more time to insert and modify data (because the index changes accordingly). A database index is a catalog of values in certain fields to improve the efficiency of table search; each has its own advantages and disadvantages:

Advantages: creating indexes can greatly improve the performance of the system

By creating a uniqueness index, the uniqueness of each row of data in the database table can be guaranteed.

It can greatly speed up the retrieval of data, which is the main reason for creating an index.

The connection between the meter and the table can be accelerated, especially in achieving the reference integrity of the data.

When using grouping and sorting clauses for data retrieval, the time of grouping and sorting in a query can also be significantly reduced.

Fifth, through the use of index, we can use the optimization hidden device in the process of query to improve the performance of the system.

Disadvantages: adding an index also has many disadvantages

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

The index needs to occupy 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.

Storage engine:

Data in MySQL is stored in files (or memory) using a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of functions and capabilities, by selecting different technologies, additional speed or functionality can be achieved, thereby improving the overall functionality of the application. These different technologies and associated functions are called storage engines (also known as table types) in MySQL.

MySQL is configured with many different storage engines by default, which can be pre-set or enabled in the MySQL server. You can choose a storage engine for servers, databases, and tables to provide maximum flexibility when choosing how to store your information, how to retrieve it, and what performance and functionality you need to combine the data with.

Trigger: a special type of stored procedure that automatically takes effect when data in a specified table changes; a trigger is a special transaction unit that can reference columns in other tables to perform special business rules or data logic relationships. Once defined, any user's INSERT, UPDATE, or DELETE for the table is automatically activated by the server. Triggers can query other tables. Treat the trigger and the statement that triggered it as a single transaction that can be rolled back within the trigger. If a serious error is detected (for example, insufficient disk space), the entire transaction is automatically rolled back, that is, undone.

There are two types of triggers (according to the interval size of the triggered action):

Row-level trigger (FOR EACH ROW): triggers the action to execute the corresponding number of times according to the number of rows in a table

Statement-level trigger (FOR EACH STATEMENT): no matter how many rows there are in the table, the trigger action occurs only once

When you create a trigger, you must also specify trigger actions: insert, update, delete operations, at least one, or multiple

Create a trigger:

Create trigger {before | after} ON

For each {row | statement}

[when]

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.