Shulou(Shulou.com)05/31 Report--

This article is to share with you about MySQL indexing and transactions, view and storage engines MylSAM and lnnoDB, the editor thinks it is very practical, so I want to share it with you. I hope you can get something after reading this article.

MySQL indexes and transactions, views, storage engines MylSAM and lnnoDB indexes indexes in the conceptual database are similar to catalogs in books:

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

The ACID characteristics of transactions are atomicity (Atomicity):

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

Consistency (Consistency):

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.

Isolation (Isolation):

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

Persistence (Durability):

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 the data in 1.MySQL is stored in files with a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels and finally provides different functions and capabilities. These different technologies and supporting functions are called storage engine 2 in MySQL. Storage engine is the way or format in which MySQL stores data in the file system. Currently, there are two storage engines commonly used in MySQL:

MyISAM (suitable for reading)

InnoDB (suitable for writing)

The 4.MySQL storage engine is a component of the MySQL database server, which is responsible for performing the actual data Ihandle O operation for the database. One of the main advantages of using a special storage engine is that it only needs to provide the characteristics needed for special applications, the system overhead in the database is smaller, and it has more efficient and higher database performance in 6.MySQL systems, and the storage engine is located in the file system. Before the data is saved to the data file, it is transferred to the storage engine, and then it is stored according to the storage format of each storage engine. 1.MyISAM storage engine is the default storage engine before version 5.5 of MySQ relational database system. The predecessor is ISAM2.ISAM, which is a well-defined and time-tested data table management method. At the time of design, it is considered that the number of times the database is queried is much greater than the number of updates 3.SAM:

① 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

Three files:

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

These are the MySQL index and transaction, view and storage engines MylSAM and lnnoDB. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.