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

This article mainly introduces what is the index, transaction and storage engine of MySQL. I hope I can add and update some knowledge to you. If you have any other questions to know, you can continue to follow my updated article in the industry information.

I. Index

Concept:

In a book, you can quickly find the information you need without reading the whole book.

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

Database index:

In a database, the index database program can find the data it needs without scanning the entire table.

An index in a database is a collection of one or more 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 up the appropriate index, the database uses a variety of fast positioning technologies, which can greatly speed up the query rate.

2. Especially when the table is very large, or when the query involves multiple tables, using indexes can make the query thousands of times faster

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

4. Ensure the uniqueness of the data in the data table by creating a unique index.

5. it can speed up the connection between tables.

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

Classification of indexes

General index: this is the most basic index type, and it has no restrictions such as uniqueness: this index is basically the same as the previous "ordinary index", but there is one difference: all values of the index column can only appear once, that is, the primary key index must be unique: the primary key is a unique index, but it must be defined as a "PRIMARY KEY" full-text index: the type of full-text index is FULLTEXT. You can create single-column and multi-column indexes on columns of type VARCHAR or TEXT: indexes can be indexes created on a single column or on multiple columns

The principle of creating an index

The primary key of a table, the foreign key must have an index; tables with more than 300 rows of data should have indexes; tables that are often joined with other tables should establish indexes on join fields; fields with poor uniqueness are not suitable for indexing; fields that are updated too frequently are not suitable for indexing; fields that often appear in the where clause, especially fields with large tables, should be indexed; indexes should be built on fields with high selectivity The index should be built on small fields. For large text fields or even super-long fields, do not build indexes.

How to create an index

After selecting the appropriate index according to the needs of the enterprise, you can use CREATE INDEX to create the index CREATE INDEX plus each index keyword to create various types of index to create a normal index CREATE INDEX ON tablename (column list); CREATE INDEX salary_index ON IT_salary (payroll); create unique index CREATE UNIQUE INDEX ON tablename (column list); CREATE UNIQUE INDEX salary_unique_index ON IT_salary (name) Create primary key index CREATE TABLE tablename ([...], PRIMARY KEY (column list)); ALTER TABLE tablename ADD PRIMARY KEY (column list); example: ALTER TABLE IT_salary ADD PRIMARY KEY (employee ID); Index View SHOW INDEX FROM tablename;SHOW KEYS FROM tablename; example: SHOW INDEX FROM IT_salary; SHOW KEYS FROM IT_salary; II, transaction

Concept

A   transaction is a mechanism, an operation sequence, that contains a set of database operation commands, and submits or revokes operation requests to the system as a whole, that is, this set of database commands are either executed or not executed.

  transaction is an indivisible working logic unit. When performing concurrent operations on a database system, the transaction is the smallest control unit.

  is used in scenarios where multiple users operate database systems at the same time, such as banks, insurance companies, securities trading systems, etc.

  ensures data consistency through the integrity of transactions.

Characteristics

1. Atomicity: the transaction is a complete operation, and the elements of the transaction are inseparable. (atomic) all elements in the transaction must be committed or rolled back as a whole. If any element in the transaction fails, the whole transaction will fail. 2. 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 3. Isolation: all concurrent transactions that modify the data are isolated from each other, indicating that the transaction must be independent. He should not in any way rely on or affect other transactions that modify data can access the data before another transaction that uses the same data starts Or access the data after another transaction that uses the same data 4. Persistence: transaction persistence means that the result of the transaction is permanent once the transaction is committed, regardless of whether the system fails or not. the effect of the transaction is permanently retained in the database.

Operation of a transaction

MySQL transactions commit automatically by default, and transactions commit automatically when the SQL statement commits

The method of manually controlling transactions:

1. Transaction command control

2. Use set to set the transaction exit mode

Transaction commands control transactions:

Begin: start a transaction commit: commit a transaction rollback: roll back a transaction

Use the set command to control:

Set autocommit=0: disable autocommit set autocommit=1: enable autocommit III. Storage engine

Concept introduction

Data in MySQL is stored in files using a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides different functions and capabilities These different technologies and supporting functions are called storage engine in MySQL.   storage engine is the way or format in which MySQL stores data in the file system.   currently has two storage engines commonly used in MySQL:    MyISQM    InnoDB   MySQL storage engine is a component of MySQL database cloud server. One of the main advantages of using a special storage engine responsible for performing the actual data operation for the database   is that it only needs to provide the features required by the special application, the system overhead in the database is less, and has more efficient and higher database performance in   MySQL systems, the storage engine is on top of the file system and the storage engine is transferred before the data is saved to the data file. Then it is stored according to the storage format of each storage engine.

Overview of MyISQM

Characteristics of ISAM:

ISAM performs read operations quickly and does not take up a lot of memory and storage resources. He does not support transactions and cannot tolerate faults.

Characteristics of MyISAM:

1. Do not support transaction 2, table-level locking form, lock the entire table 3 when data is updated, ● blocks each other in the process of reading and writing, ● will block the reading of user data in the process of data writing, ● will also block the user's data writing in the process of data reading. 4. Cached indexes can be set through key_buffer_size to improve access performance and reduce the pressure on disk IO, but caching will only cache index files. Will not cache data 5, using MyISAM storage engine data to write or read separately, the speed of the process is fast and relatively less resources 6, MyISAM storage engine it does not support foreign key constraints, only support full-text index 7, each MyISAM on disk. The name of each file starts with the name of the table, and the extension indicates that the file type 8 and MyISAM are on disk. File ● .frm file storage table definition ● data file extension .MYD (MYData) ● index file extension .MYI (MYIndex)

Production scenarios suitable for MyISAM

The company's business does not need the support of transactions, generally businesses that read more data unilaterally, or businesses that write more data unilaterally. MyISAM storage engine data read and write are more frequent scenarios are not suitable for using read and write concurrent access relatively low business data modification relatively less business data consistency requirements are not very high business server hardware resources are relatively poor

Introduction to the characteristics of InnoDB

Support for transactions: 4 transaction isolation level row-level locking is supported, but full-table scanning will still be table-level locking read-write blocking related to transaction isolation level has very efficient caching features: it can cache indexes, data tables and primary keys can be stored in clusters to support partitions and tablespaces, similar to oracle databases support foreign key constraints, full-text indexing is not supported before 5.5 Support for full-text indexing after version 5.5 still requires high hardware resources.

InnoDB is suitable for production scenarios.

Business needs transaction support row-level locking has a good adaptability to high concurrency, but you need to ensure that queries are completed through indexes to complete scenarios where business data is updated frequently, such as forums, Weibo and other business data consistency requirements. For example, banking hardware devices have large memory, use Innodb's better cache capacity to improve memory utilization and reduce the pressure on disk I0. Enterprises choose storage engine based on

You need to consider what different core functions and scenarios each storage engine provides.

Supported fields and data types all engines support common data types, but not all engines support other field types, such as binary objects

Locking types: different storage engines support different levels of locking

Table lock row locking

Supported indexes

Indexing can significantly improve performance when searching and recovering data in a database. Different storage engines provide different indexing techniques. Some storage engines do not support indexing at all.

Supported transactions

The transaction function configures the storage engine MyISA by providing reliability lessons during updating and inserting information into the table, depending on whether the enterprise business supports transaction options.

After selecting the appropriate storage engine in the enterprise, you can modify it.

Modify step

View the storage engine that the database can be configured to view the table the storage engine is using to configure the storage engine for the selected type

Use show engines to view the storage engines supported by the system

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

Method 2: show create table table name

Modify the storage engine

Method 1: alter table modification; alter table table_ name engine= engine; method 2: modify my.cnf, specify the default storage engine and restart the service default-storage-engine=InnDB method 3: create table specify storage engine create table table name (field) engine= engine method 4: Mysql_ convert_ table_ format conversion storage engine Mysql_ convert_ table_ format-user=root-password= password

-sock=/tmp/mysql.sock-engine = engine library name table name

Read the above about what is the index, transaction and storage engine of MySQL, and hope to bring some help to you in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.