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

The following mainly brings you a detailed introduction of the index, things and storage engine in Mysql. I hope the detailed introduction of indexes, things and storage engine in Mysql can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

I. Index:

(1) Database index:

1. In a database, an index enables a data program to find the data it needs without scanning the entire table.

2. 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.

(2) the function of the index:

1. The database can greatly accelerate the query rate.

2. Reduce the IO cost of database, and the index can also reduce the sorting cost of database.

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

4. Speed up the connection between tables

5. when grouping and sorting, the time of grouping and sorting can be greatly reduced.

(3) Index classification:

1. General index: the most basic index type, and there are no restrictions such as uniqueness.

2. Unique index: similar to ordinary index, except that all values of index column can only appear once, that is, they must be unique.

3. Primary key: the primary key is a unique index and must be specified as "primary key"

4. Full-text index: mysql supports full-text index and full-text search since version 3.23.23. In mysql, the index type of full-text index is fulltext, and full-text index can be created on columns of type varchar or text.

5. Single-column index and multi-column index: the index can be an index created on a single column or an index created on multiple columns.

(4) the principle of creating an index is based on:

The primary and foreign keys of a table must have an index

Tables with more than 300 rows of data should have indexes

Tables that are often connected to other tables should be indexed on the join field

Fields with poor uniqueness are not suitable for indexing.

Fields that are updated too frequently are not suitable for index creation

Fields that often appear in the where clause, especially those in large tables, should be indexed

The index should be based on fields with high selectivity

The index should be based on small fields and should not be indexed for large text fields or even very long fields.

(5) the method of creating an index:

1. Create a general index:

For example, let's create an index for the age column for the following table:

2. Create a unique index:

3. Create a primary key index:

4. Create a full-text index:

Create fulltext index index name on table name (list of columns)

5. Create a composite index:

Create index index name on table name (list 1 of columns, list 2 of columns.)

6. View the index:

Show index from tablename; show keys from tablename

2. Affairs:

(1) transaction concept:

To put it simply, all commands are submitted or revoked as a whole, either executed together or not executed at all.

(2) the four characteristics of the transaction:

1. Atomicity: all elements must be committed or rolled back as a whole. If any element fails, the whole transaction fails.

For example, we usually download software, etc., if we encounter a failure in the middle of the installation, we will return to the state before the installation of the software.

2. Consistency: before the transaction starts, the data is in a unified and consistent state; after the transaction is completed, it returns to the consistent state again.

3. Isolation: all concurrent transactions are independent, irrelevant and influence each other.

4. Persistence: once the transaction is committed, it is permanently retained in the database.

(3) Operation of the transaction:

By default, mysql transactions are committed automatically, and transactions commit automatically when the sql statement commits

Manual control of transactions (transaction command control or using set command control):

1. Transaction commands control transactions:

Begin: start a transaction

Commit: commit a transaction

Rollback: rolls back a transaction

Next, we start to roll back to which node we want to go back to, just go back to that archive point:

To roll back to the original state, use the rollback command directly:

2. Use the set command to control:

Set autocommit=0: disable autocommit

Set autocommit=1: enable autocommit

3. Storage engine:

(1) Storage engine concept:

The data in mysql is stored in files with 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 engines in mysql.

The 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 (lightweight)

InnoDB

The mysql storage engine is a component of the mysql database cloud server, which is responsible for performing the actual data Istroke 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 of a special application, has less data overhead in the database, and has more efficient and higher database performance.

In mysql system, the storage engine is on top of the file system, and the data is transferred to the storage engine before being saved to the data file, and then stored according to the storage format of each storage engine.

1. MyISAM introduction:

Is the default storage engine prior to version 5.5, formerly known as ISAM

1. Features:

ISAM performs read operations very quickly

Do not take up a lot of memory and storage resources

But transaction processing is not supported

Cannot be fault-tolerant

MyISAM is on disk. Files stored on the

two。 Data storage location:

Table definition file

Table data storage file

Table index file

.frm file storage table definition

The data file has a .MYD (MYData) extension

The extension of the index file is .myi (MYIndex)

Examples of production scenarios applicable to ③ .MyISAM:

The company's business does not need the support of transactions.

Businesses with relatively few data modifications

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

The hardware resources of business cloud servers that do not require very high data business consistency are relatively poor.

2 、 InnoDB

1. Features:

Support for 4 transaction isolation levels

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

Support for partitions, tablespaces, similar to oracle databases

Tables and primary keys are stored in clusters

Row-level locking, but a full table scan will still be table-level locking read-write blocking related to transaction isolation level

It is suitable for situations where the requirement of hardware resources is still high.

two。 Applicable production scenarios:

Business needs transaction support

Scenarios where business data are updated more frequently

High requirements for business data consistency

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

(2) the enterprise chooses storage engine based on:

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

Supported fields and data types

Lock type (table lock, row lock)

(3) Storage engine related commands:

1. View the commands of the storage engine:

Show engines / / View the storage engines supported by the system

2. View the storage engine used by the table:

Show table status from library name where name=' table name'; show create table table name

3. Modify the storage engine:

Method 1: use alter table to modify

Alter table table_name engine= engine

Method 2: modify the my.cnf file, specify the default storage engine and restart the service

Default-storage-engine=InnoDB

Method 3: create table specifies the storage engine when creating the table

Create table Table name (Field) engine= engine

4. Query connected by two tables:

For example, here are two tables, the first is named user; and the second is called hob, because these two tables are associated with some information, how to do it:

Enter the following command to associate the two tables:

1. Direct connection:

Mysql > select * from user inner join hob on user.hobby=hob.id

2. You can also associate according to conditions:

3. If you feel the table name is a little long, you can also give it an alias:

4. Create a view query:

Mysql > create view view_user as select u.name _ name _ from user u inner join hob h on u.hobby=h.id

Note: the view is a virtual thing, the data does not exist in it, but is convenient for users to query, and its function is similar to a shortcut to the window desktop.

For the above detailed introduction of indexes, things and storage engines in Mysql, do you find it very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.