Database MySQL Part4

Storage Engine

What is a storage engine?

The library built in MySQL is a folder, and the built table is a file. There are different types of files, the tables in the database have different types, the table type is different, it will correspond to the different access mechanism of MySQL, and the table type is also called the storage engine.

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, storage and
Manipulate the type of this table)

MySQL-supported storage engine

Show Engines\g;     #查看所有支持的存储引擎show variables like '%storage_engine% ';    #查看正在使用的存储引擎

MySQL Common storage engine

1. MyISAM Storage Engine
Because the storage engine does not support transactions and does not support foreign keys, access is faster. Therefore, it is appropriate to use the storage engine when there is no requirement for transactional integrity and an access-oriented application.

2, InnoDB storage engine (main use is also default)
Because the storage engine has a transactional advantage, which supports transactional features such as commit, rollback, and crash resiliency, it consumes more disk space than the MyISAM storage engine. Therefore, when frequent updates and deletions are required and the integrity of the transaction is high, concurrency control needs to be implemented, and the recommended choice is to do so.

3. MEMORY
The memory storage engine stores data in a location that is RAM and therefore accesses the fastest, but is not guaranteed to be secure. Suitable for fast access or temporary tables. The data in the table is emptied after restarting MySQL or restarting the machine .

4, Blackhole
A black hole storage engine that can be applied to the distribution Master library in primary and standby replication. Inserting any data into a table is equivalent to throwing it into a black hole, and never keeping a record in the table.

Specifying the storage Engine

1, the establishment of the table when the designation

CREATE TABLE innodb_t1 (ID int,name char) engine=innodb;create table innodb_t2 (id int) Engine=innodb;

2. Configuration file designation

[mysqld]default-storage-engine=innodbinnodb_file_per_table=1

3. View

Enter library folder, view file suffix name, different storage engine file suffix different, black hole and memory engine only table structure file, no data file

Federated primary Key

Features of the primary key:

1. Only one primary key per table
2, each table may not have only one non-empty and unique field
3, if there is only one non-empty and unique field in the table, then it is the primary key, if there is not only one non-empty and unique field in the table, then the first non-null-tangent unique field is the primary key

Federated PRIMARY Key:

The Federated primary Key is composed of multiple fields, the following student_id and teacher_id together make up the primary key of the table, so that in the student table and the teacher table in the many-to-many relationship, in this table, there will be no completely duplicate records, resulting in useless duplicate data, It also does not cause important data to be empty, resulting in invalid data. (Note: The example is a many-to-many relational table with no foreign KEY constraints set)

CREATE TABLE student_to_teacher (    ID int not NULL UNIQUE auto_increment,     student_id int,     teacher_id int,     PRIMARY KEY (student_id, teacher_id));D ESC student_to_teacher;+------------+---------+------+-----+---------+ ----------------+| Field      | Type    | Null | Key | Default | Extra          |+------------+---------+------+-----+---------+----------------+| ID         | int (11) | NO   | UNI | NULL    | auto_increment | | student_id | INT (11) | NO   | PRI | NULL    |                | | teacher_id | INT (11) | NO   | PRI | NULL    |                | +------------+---------+------+-----+---------+----------------+3 rows in Set (0.05 sec)

Index

The index, also called a "key" in MySQL, is a data structure used by the storage engine to quickly find records. Indexes are critical to good performance, especially when the amount of data in a table is increasing, and the impact of indexes on performance becomes increasingly important.

Index optimization should be the most effective means of optimizing query performance.
Indexes can easily improve query performance by several orders of magnitude.
The index is equivalent to the dictionary's Sequencer list, if you want to check a word, if you do not use a sequencer, you need to check from page hundreds of.

Index features: Creating and maintaining indexes can consume a lot of time and disk space, but query speed is greatly improved!

When creating an index, a series of algorithms are used, such as hash, binary tree, red-black tree, etc.

Create an index

Index and key are indexed

Grammar:

CREATE Table Table name (             field name 1  data type [integrity constraint ...],             field name 2  data type [integrity constraint ...],             [UNIQUE]   INDEX | KEY             [index name]  (field name [(length)]  [ASC | DESC]));

Example:

--Creating a normal index example: Create    TABLE EMP1 (        ID INT,        name varchar (),        resume VARCHAR (),        index Index_emp_ Name    --key index_dept_name (dept_name)        );--Creating a unique index example: Create    TABLE EMP2 (        ID INT,        name varchar (+),        bank_num CHAR Unique,        resume VARCHAR (+),        unique INDEX index_emp_name (name)        ); --Create a full-text index example: Create    TABLE Emp3 (        ID INT,        name varchar (),        resume VARCHAR (        fulltext), index Index_resume (Resume)        ;--Creating a multicolumn Index example: Create    TABLE emp4 (        ID INT,        name VARCHAR),        resume VARCHAR (+),        INDEX index_name_resume (name,resume)        );

Practice:

--Create TABLE indexdb.t1 (ID int,name varchar (20));--stored procedure delimiter $ $create Procedure AutoInsert () begindeclare i int Default 1;while (i<500000) Doinsert into indexdb.t1 values (i, ' yuan '); set i=i+1;end while; end$ $delimiter;--Invoke function call AutoInsert ();--Take time to compare:--Create INDEX before   select * from indexdb.t1 where id=300000;--0.32s--add index C1/>create index index_id on indexdb.t1 (ID);--After creating an index   select * from Indexdb.t1 where id=300000;--0.00s

Database MySQL Part4