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

Concept of index

An index in a database is similar to a catalog in a book

In a book, without reading the whole book, you can quickly find the information you need by using the table of contents.

The table of contents in a book is a list of words with page numbers indicating which words are contained

database index

In a database, the indexing database program does not have to scan the entire table to find the data it needs

An index in a database is a collection of values for a column or columns in a table and a list of logical pointers to the data pages that physically identify those values

Role of index

After setting up proper index, database can speed up query speed greatly by using various fast positioning techniques.

Especially when the table is large, or when the query involves multiple tables, the use of indexes 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 data table data by creating unique index;

You can speed up connections between tables;

When grouping and sorting are used, the grouping and sorting time can be greatly reduced;

Classification of index

general index

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

primary key

A primary key is a unique index, but it must be formulated as "PRIMARY KEY"

full-text index

Full-text indexes are of type FULLTEXT and can be created on VARCHAR or TEXT columns

Single column index versus multiple column index

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

Principles for index creation

Table primary key, foreign key must have index;

Tables with more than 300 rows should have an index;

Tables that are frequently joined to other tables should be indexed on join fields;

Fields that are too unique are not suitable for indexing;

Fields that are updated too frequently are not suitable for indexing;

Fields that appear frequently in where clauses, especially in large tables, should be indexed;

Indexes should be built on highly selective fields;

Indexing should be built on small fields. For large text fields or even super-long fields, do not build indexes.

How to Create an Index

Once you have selected the appropriate index for your enterprise needs, you can create an index using CREATE INDEX

CREATE INDEX plus each index keyword creates 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(salary);

Creating a Unique Index

CREATE UNIQUE INDEX ON tablename (list of columns);

Creating a Unique Index Example

CREATE UNIQUE INDEX salary_unique_index ON IT_salary(last name);

Create Primary Key Index

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

ALTER TABLE tablename ADD PRIMARY KEY (list of columns);

Primary key index example

ALTER TABLE IT_salary ADD PRIMARY KEY (Employee ID);

Index View

SHOW INDEX FROM tablename;

SHOW KEYS FROM tablename;

Concept of business

A transaction is a mechanism, a sequence of operations, containing a set of database operations, and all the commands as a whole together to submit or cancel the operation request to the system, that is, this set of database commands are either executed or not executed;

 A transaction is an indivisible logical unit of work. When concurrent operations are performed on a database system, a transaction is the smallest unit of control.

 Scenarios for database systems operated by multiple users simultaneously, such as banks, insurance companies, and securities trading systems;

 Ensure data consistency through transaction integrity;

ACID characteristics of transactions

Atomicity: A transaction is a complete operation, and its elements are indivisible (atomic).

All elements in a transaction must be committed or rolled back as a unit

If any element in the transaction fails, the entire transaction fails.

Consistency: When a transaction completes, the data must be in a consistent state: the data stored in the database is consistent before the transaction begins; the data may be inconsistent during ongoing transactions; when the transaction completes successfully, the data must return to a known consistent state again Isolation: all concurrent transactions modifying data are isolated from each other, meaning that the transaction must be independent and should not depend on or influence other transactions in any way A transaction modifying data can access the data before another transaction using the same data begins, or after another transaction using the same data ends Persistence: Transaction persistence means that the results of a transaction are permanent regardless of whether the system fails Once the transaction is committed, the effects of the transaction are permanently retained in the database

Transaction processing commands control transactions:

begin: Start a transaction

commit: commit a transaction

rollback: rolling back a transaction

Use the set command to control:

set autocommit=0: Disable autocommit

set autocommit=1: Enable autocommit

Storage Engine Concept Introduction

 Data in MySQL is stored in files using a variety of different technologies, each using different storage mechanisms, indexing techniques, locking levels and ultimately providing different features and capabilities.

 A storage engine is the way MySQL stores data in a file system or storage format.

 There are currently two storage engines used by MySQL:

  MyISQM

  InnoDB

 MySQL Storage Engine is the component in MySQL database server responsible for performing the actual data I/O operations for the database

 One of the main advantages of using a special storage engine is that it provides only the features needed for a particular application, less overhead in the database, and more efficient and higher database performance

 MySQL system, the storage engine is above the file system, before the data is saved to the data file will be transferred to the storage engine, and then according to the storage format of each storage engine

Overview of MyISAM

Features of ISAM:

ISAM performs read operations quickly

And it doesn't take up a lot of memory and storage resources.

He doesn't support transaction processing

Not fault-tolerant

MyISAM features:

1. Unsupported transactions

2. Table-level locking form, in which the data locks the entire table when updated

3. Databases block each other during reading and writing

blocking user data reads during data writes

It also blocks user data writes during data reads

4. Cache index can be set by key_buffer_size to improve access performance and reduce disk IO pressure.

However, caching only caches index files, not data

5, using MyISAM storage engine data to write or read separately, the speed process is fast and takes up relatively few resources

MyISAM storage engine does not support foreign key constraints, only full-text indexing

7, Each MyISAM on disk. The name of each file starts with the name of the table and the extension indicates the file type.

8, MyISAM on disk. files stored on

.frm File Storage Table Definition

Data files have the extension.MYD (MYData)

The index file extension is.MYI (MYIndex)

Production scenarios for MyISAM

Corporate business does not require transactional support

Generally, the business of reading more data unilaterally or writing more data unilaterally

MyISAM storage engine data read and write are frequent scenarios are not suitable

Use read and write concurrency to access relatively low traffic

Services with relatively few data modifications

Services that do not have very high requirements for data service consistency

Server hardware resources are relatively poor

Configure Storage Engine MyISA

Use show engines to view supported storage engines

Method 1:

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

Method 2:

show create table name;

Modify Storage Engine

Method 1: Alter table modification;

alter 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 the storage engine when creating the table

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

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.