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

The following content mainly brings you MySQL database index, transactions and other detailed explanation, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

The index in the conceptual database of the index is similar to the catalog in the book:

1-in this book, you can quickly find the required information by using the catalogue without reading the whole book.

two。 The catalogue in the book is a list of words with the page number containing each word.

Database index:

1. In the database, the cable bow | enables the database program to find the required data without scanning the entire table.

two。 The bow in the database | is a collection of one-column or several column values in a table, and a list of logical pointers to the data pages that physically identify these values

The role of the index:

1. After setting the appropriate index, the database uses a variety of fast positioning techniques, which can greatly speed up the query rate.

two。 Especially when the table is very large, or when the query involves multiple tables, the use of indexes can make the query dry times faster.

3. It can reduce the IO cost of database, and the index can also reduce the sorting cost of database.

4. Ensuring the uniqueness of data table data by creating a uniqueness index

5. You can speed up the connection between tables

6. When using grouping and sorting, grouping and sorting time can be greatly reduced

Classification of indexes

General Index:

Is the most basic index | type, and it has no restrictions such as uniqueness or sex.

Uniqueness index:

This kind of cable bow | is basically the same as the previous "normal index", but with one difference: all values of the index column can only appear once, that is, they must be unique.

Primary key:

The primary key is a unique index, but it must be specified as "PRIMARY KEY"

Full-text index:

MySQL supports full-text indexing and full-text retrieval since version 3.23.23. In MySQL, the index type of the full-text index is FULLTEXT, and the full-text index can be created on columns of type VARCHAR or TEXT.

Single-column index and multi-column index:

An index can be an index created on a single column or on multiple columns

The principle of creating an index is based on 1. The primary and foreign keys of a table must have an index

two。 Tables with more than 300 rows of data should have indexes

3. Tables that are often joined to other tables are in the join field. Should be indexed on the

4. Fields with poor uniqueness are not suitable for indexing.

5. Fields that are updated too frequently are not suitable for creating index 6. Fields that often appear in the Where clause, especially those in large tables, should be indexed

7. The index should be built on fields with high selectivity

8. The index should be built on small fields, and for large text fields or even super-long fields, do not build the concept of indexing transaction.

1. A transaction is a mechanism, an operation sequence, which contains a set of database operation commands, and all commands are submitted or revoked to the system as a whole, that is, this set of database commands are either executed or not executed.

two。 Transaction is an indivisible working logic unit, and transaction is the smallest control unit when performing concurrent operations on a database system.

3. Suitable for scenarios of database systems operated by multiple users at the same time, such as banks, insurance companies, securities trading systems, etc.

4. Ensure the consistency of data through the integrity of transactions

ACID characteristics of transactions

1. A transaction is a complete operation, and the elements of the transaction are inseparable (atomic).

two。 All elements in the transaction must be committed or rolled back as a whole

3. If any element in the transaction fails, the entire transaction will fail

When the transaction is completed, the data must be in a consistent state: before the transaction starts, the data stored in the database is in a consistent state; in an ongoing transaction, the data may be in an inconsistent state; when the transaction completes successfully, the data must return to the known consistent state again.

1. All concurrent transactions that modify data are isolated from each other, indicating that transactions must be independent and should not depend on or affect other transactions in any way

two。 A transaction that modifies data can access the data before another transaction that uses the same data starts, or after the end of another transaction that uses the same data

1. Transaction persistence means that the result of a transaction is permanent regardless of whether the system fails or not.

two。 Once the transaction is committed, the effect of the transaction is permanently retained in the database

Operation of transaction 1. By default, MySQL transactions commit automatically, and when sql statements commit, transactions commit 2. 5% automatically. The method of manually controlling transactions:

① transaction command control

② uses set to set the transaction mode

3. Transaction commands control transactions:

① begin: start a transaction

② commit: commit a transaction

② rollback: rolls back a transaction

4. Use the set command to control:

① set autocommit=0: disable autocommit

② set autocommit=1: enable autocommit

Introduction to the concept of Storage engine

MyISAM (suitable for reading)

InnoDB (suitable for writing)

Introduction of MyISAM

① ISAM performs read operations very quickly

② it does not support transaction processing

③ and does not take up a lot of memory and storage resources

④ cannot tolerate faults

Table definition file

Table data storage file

Table index file

Examples of production scenarios suitable for MyISAM: 1. The company's business does not need the support of transactions.

two。 Generally, businesses that read more data unilaterally, or write more data unilaterally

3.MyISAM storage engine data are read and written frequently. The scenario is not suitable.

4. Use read-write concurrency to access relatively low services

5. Businesses with relatively few data modifications

6. A business that does not require very high data service consistency.

7. Introduction to the characteristics of InnoDB with relatively poor hardware resources on servers

1. Support for transactions: support for 4 transaction isolation levels

two。 Row-level locking, but full table scanning will still be table-level locking

3. Read-write blocking is related to transaction isolation level

4. It has very efficient caching features: it can cache indexes as well as data.

5. Tables and primary keys are stored in clusters

6. Support for partitions, tablespaces, similar to oracle databases

7. Foreign key constraints are supported. Full-text indexing is not supported before 5.5, but after 5.5.

8. The demand for hardware resources is still relatively high.

InnoDB suitable for production scenario analysis

1. Business needs transaction support

two。 Row-level locking is well adapted to high concurrency, but make sure that the query is done through the index

3. Scenarios where business data are updated frequently, such as forums, Weibo, etc.

4. High consistency of business data is required, for example, banking

5. The memory of the hardware device is large, so the better cache ability of Innodb can improve the memory utilization and reduce the pressure of disk IO.

Configure storage engine 1. After selecting the appropriate storage engine in the enterprise, you can modify 2. 5%. Modify the steps:

① to view the storage engine that the database can configure

② view the storage engine that the table is using

③ configures the storage engine for the selected type

3. Use show engines to view the storage engine supported by the system to view the storage engine used by the table

Method 1: show table status from library name where name=' table name

Method 2: show create table table name

For the above MySQL database indexing, transactions and other detailed explanation, if you need to know more, you can continue to pay attention to the innovation of our industry, if you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.