Database-related concepts

database transactions: refers to a series of data that is executed as a single logical unit of work Operation , either completely or completely out of the way. 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 actions into a single unit that either succeeds or all fails, you can simplify error recovery and make your application more reliable.

1). Transaction-related properties:

• 1. atomicity
• The transaction must be an atomic unit of work, for its data modification either all of them are executed, or none of them are executed. Typically, the operations associated with a transaction have a common goal and are interdependent, and if the system only performs a subset of these operations, it can break the overall goal of the transaction, and atomicity eliminates the possibility of the system processing a subset of operations;
• 2. Consistency

When a transaction is complete, you must keep all data in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures (such as B-tree indexes or doubly linked lists) must be correct;

3: Isolation

modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state in which the data is located when the transaction is viewing the data, either when another concurrent transaction modifies its state or after another transaction modifies it, and the transaction does not view the data in the middle state. This is called isolation because it is able to reload the starting data and replay a series of transactions so that the state at the end of the data is the same state as the original transaction execution. 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 high isolation limits the number of transactions that can be executed in parallel, some applications reduce the isolation level in exchange for greater throughput;

4. Persistence

After the transaction is complete, its effect on the system is permanent. This modification will persist even in the event of a fatal system failure;

2). Three models of transactions:

1. 1. Implicit transactions mean that each data operation statement automatically becomes a transaction, the beginning of the transaction is implicit, and the end of the transaction is marked explicitly;

2. 2. An explicit transaction is a transaction with an explicit start and end tag, each with an explicit start and end tag;

3). Statements that use transactions:

2. Start things: Begin TRANSACTION

4. Commit a thing: Commit TRANSACTION

6. ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

8. 
   

10. 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 increase the speed at which data is accessed in a database table . Indexes such as the directory of books, if there is no index, to find a particular value in the database needs to traverse the entire database table, but with the index can be found in the index, to facilitate faster access to information;

12. Indexes can be divided into clustered and nonclustered indexes.

Clustered indexes are in the order of the physical locations in which the data is stored, whereas in a nonclustered index, the table data store order 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". Data rows in such a table do not have a specific order, and all new rows are added to the end of the table.

2. The basic information contained in an index record is: Key value (the value of all fields specified when the index is defined) + logical pointer (pointing to a data page or another index page);

4. Depending on the capabilities of your database, you can create three types of indexes in the Database Designer:

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

8. When duplicate key values exist in existing data, most databases do not allow a 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 Employees table, none of the two employees will have a namesake;

10. Primary KEY index: The index requires that each value in the primary key be unique. It also allows quick access to data when a primary key index is used in a query

12. Primary key: A database table often has one or more column combinations whose values uniquely identify each row in the table

14. Clustered index

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

18. Although the purpose of indexing is to speed up the lookup or sorting of records in a table, it pays to set indexes on the table: one is to increase the storage space for the database, and the other is to spend more time inserting and modifying the data (because the index changes as well). A database index is a directory in which values in some fields are created to improve the search efficiency of a table;

20. Advantages: Creating indexes can greatly improve the performance of the system;

22. By creating a unique index, you can guarantee the uniqueness of each row of data in a database table;

24. Can greatly speed up the retrieval of data, which is the main reason for creating indexes;

26. The connection between tables and tables can be accelerated, particularly in terms of the referential integrity of the data;

28. When using grouping and sorting clauses for data retrieval, it is also possible to significantly reduce the time of grouping and sorting in queries;

30. By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

32. Disadvantage: There are many disadvantages to increasing the index;

34. It takes time to create indexes and maintain indexes, and this time increases with the amount of data.

36. The index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies a certain physical space, if to establish the clustered index, then needs the space to be larger;

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

40. Storage Engine:

42. data in MySQL is stored in files (or memory) in a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities, by selecting different technologies, The ability to gain additional speed or functionality to improve the overall functionality of the application. These different technologies and associated functions are called storage engines (also known as table types) in MySQL.

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

46. Trigger: A special type of stored procedure that automatically takes effect when the data in the 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 insert, UPDATE, or delete to the table is automatically activated by the server for the corresponding trigger. Triggers can query other tables. Treats the trigger and the statement that triggered it as a single transaction that can be rolled back within the trigger. If a critical error is detected (for example, insufficient disk space), the entire transaction is automatically rolled back, or revoked.

48. The trigger type is divided into two types (according to the interval size of the triggered action):

50. Row-level triggers (for each row): Trigger actions perform the corresponding times based on the number of rows in a table

52. Statement-level trigger (for each STATEMENT): The trigger action occurs only once, regardless of how many rows the table has

54. When you create a trigger, you must also specify a trigger action: INSERT, UPDATE, delete operation, specify at least one, or specify multiple;

56. To create a trigger:

    Create trigger< trigger name > {Before|after} < Trigger event > on < table name >

58. For each{row|statement}

60. [When < trigger conditions;]

62. < trigger action BODY >
    

Database-related concepts