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

The concept of index

The index in the database is similar to the catalog in the book.

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 page numbers containing each word.

Database index

In the database, the index database program does not need to scan the whole table to find that the index in the desired data database is a collection of one or several column values in a table. and the logical pointer list index of the data page that physically identifies these values. After the appropriate index is set, the database can greatly speed up the query rate by using a variety of fast positioning techniques. Especially when the table is very large, or when the query involves multiple tables, using the index can speed up the query thousands of times; it can reduce the IO cost of the database, and the index can also reduce the sorting cost of the database; ensure the uniqueness of the data of the data table by creating a unique index; speed up the join between tables; when grouping and sorting are used, the grouping and sorting time can be greatly reduced; the classification of the index

General index

This is the most basic index type, and it has no restrictions such as uniqueness.

Uniqueness index

This kind of index 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 defined as "PRIMARY KEY"

Full-text index

The full-text index is of type FULLTEXT and can be created on a column of type VARCHAR or TEXT

Single-column index and multi-column index

An index can be an index created on a single column or an index created on multiple columns. According to the primary key of the 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 be indexed on join fields; fields with poor uniqueness are not suitable for indexing; fields that are updated too frequently are not suitable for creating indexes Fields that often appear in the where clause, especially those in large tables, should be indexed; indexes should be built on fields with high selectivity; indexes should be built on small fields, not on large text fields or even super-long fields; the method of creating 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 can create each type of index.

Create a normal index

CREATE INDEX ON tablename (list of columns)

Create a normal index

CREATE INDEX salary_index ON IT_salary (payroll)

Create a uniqueness index

CREATE UNIQUE INDEX ON tablename (list of columns)

Example of creating a unique index

CREATE UNIQUE INDEX salary_unique_index ON IT_salary (name)

Create a primary key index

CREATE TABLE tablename ([...], PRIMARY KEY (list of columns); ALTER TABLE tablename ADD PRIMARY KEY (list of columns)

Example of primary key index

ALTER TABLE IT_salary ADD PRIMARY KEY (employee ID)

Index view

SHOW INDEX FROM tablename;SHOW KEYS FROM tablename

View the index example

The concept of SHOW INDEX FROM IT_salary;SHOW KEYS FROM IT_salary; transaction

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.

ACID characteristics of transactions

Atomicity:

A 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 entire transaction will fail

Consistency:

When a transaction completes, 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:

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

A transaction that modifies data can access the data before another transaction that uses the same data starts, or after another transaction that uses the same data ends.

Persistence:

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

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

Transaction operation by default the transaction of MySQL commits automatically, when the SQL statement commits, the transaction commits automatically

The method of manually controlling transactions:

Transaction commands control how transactions come out using set

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: introduction to the concept of enabling autocommit storage engine

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, which are called storage engines in MySQL

The   storage engine is the way or format in which MySQL stores data in the file system.

  currently uses two storage engines commonly used in MySQL:

   MyISQM

   InnoDB

The   MySQL storage engine is a component of the MySQL database server, which is responsible for performing the actual data Istroke O operations for the database.

One of the main advantages of   using a special storage engine is that it only needs to provide the features required for special applications, and the system overhead in the database is lower, and it has more efficient and higher database performance.

In   MySQL systems, the storage engine is on top of the file system. Before the data is saved to the data file, the storage engine is transferred and then stored according to the storage format of each storage engine.

Introduction 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 is .MYI (MYIndex) MyISAM applicable production scenario example corporate business does not need transaction support generally unilaterally read more data business Or unilateral write more data business MyISAM storage engine data read and write more frequent scenarios are not suitable to use read and write concurrent access to relatively low business data modification relatively few business requirements for data business consistency is not very high business server hardware resources are relatively poor InnoDB features support transactions: support 4 transaction isolation level row-level locking But full-table scan will still be table-level locking read-write blocking related to transaction isolation level with 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 that 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 scenario analysis business needs transaction support row-level locking has a good adaptability to high concurrency, but you need to make sure that the query is used to complete the scenarios where the business data is updated more frequently, such as forum, Weibo and other business data consistency requirements, for example: the banking business hardware equipment memory is large Make use of Innodb's better cache capacity to improve memory utilization and reduce the pressure on disk I0. Enterprises choose storage engines according to the need to consider which core functions each storage engine provides and the fields and data types supported by the scenario. All engines support common data types, but not all engines support other field types. Such as binary object locking types: different storage engines support different levels of locking table locking, row locking and indexing. Indexing can significantly improve performance when searching and recovering data in the database. Some storage engines provide different indexing techniques that do not support index transactions at all by providing support for transaction processing in tables. Reliability course during new and insert information based on whether the enterprise business supports transaction options, select the storage engine configuration storage engine after selecting the appropriate storage engine in the enterprise You can modify the steps to view the database configurable storage engine view table the storage engine in use configure the storage engine for the selected type use show engines to view the storage engine method supported by the system 1:show table status from library name where name=' table name Method 2:show create table table name; modify storage engine method 1: alter table modification; alter table table_ name engine= engine Method 2: modify my.cnf, specify default storage engine and restart 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 thank you for reading!

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.