MySQL Indexes 、 Transaction and storage engine

preparation ： install MySQL database

MySQL Database first experience

mysql -u root -p
create database CLASS;
use CLASS;
create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
desc member;
insert into member values (1,'zhangsan','123','111111','nanjing','this is vip');
insert into member values (4,'lisi','1234','444444','nanjing','this is normal');
insert into member values (2,'wangwu','12345','222222','benjing','this is normal');
insert into member values (5,'zhaoliu','123456','555555','nanjing','this is vip');
insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip');
select * from member;

 Create a table ：member
Field one ：id integer Maximum display length 10 position
Field 2 ：name varchar（10） most 10 character
Field 2 ：cardid varchar（18） most 18 character
Field four ：phone varchar（11） most 11 character
Field five ：address varchar（50） most 50 character
Field 6 ：remark text： The text type Can store about 64kb

Is a table member Insert new data content

Indexes

One 、 The concept of index

• An index is a sorted list , In this list are stored the value of the index and the physical address of the row containing the data of this value （ Be similar to C The linked list of language points to the memory address of data record through pointer ）.
• After using the index, you can locate the data of a row without scanning the whole table , Instead, we first find the corresponding physical address of the row data through the index table, and then access the corresponding data , Therefore, it can speed up the query speed of the database .
• An index is like a catalogue of a book , You can quickly find the required content according to the page number in the table of contents .
• Index is a way to sort the values of one or more columns in a table .
• The purpose of indexing is to speed up the search or sorting of records in a table .

---

Two 、 The function of index

• After setting the appropriate index , The database uses a variety of fast positioning technologies , Can greatly speed up the query speed , This is the main reason for creating all the .
• When the table is large or the query involves multiple tables , Using indexes can improve query speed thousands of times .
• It can reduce the cost of the database IO cost , And the index can also reduce the sorting cost of the database .
• By creating a unique index , It can ensure the uniqueness of each row of data in the data table .
• You can speed up the connection between tables .
• When using grouping and sorting , Can greatly reduce the grouping and sorting time .

---

Side effects of indexing

• Index takes up extra disk space .
• about MyISAM In terms of engines , Index files and data files are separate , The address where the index file is used to hold the data record .
  and InnoDB The table data file of the engine itself is the index file .
• It takes more time to insert and modify data , Because the index has to change with it .

---

3、 ... and 、 The principle of index creation is based on

 Indexing can speed up database queries , But it's not always appropriate to create an index . Because the index itself consumes system resources , With an index , The database will perform index query first , Then navigate to the specific data row , If the index is not used properly , On the contrary, it will increase the burden of the database .

• Primary Key 、 Foreign key must have index . Because the primary key is unique , The foreign key is associated with the primary key of the child table , You can quickly locate when querying .
• Number of records exceeds 300 The table of rows should have an index . If there is no index , You need to traverse the table , Will seriously affect the performance of the database .
• A table that is often connected to other tables , Index should be established on the connection field .
• Fields with poor uniqueness are not suitable for indexing .
• Fields that are updated too frequently are not suitable for creating indexes .
• It often appears in where Fields in clause , In particular, the fields of large tables , It should be indexed .
• Indexes should be built on fields with high selectivity .
• Indexes should be built on small fields , For large text fields or even extra long fields , Don't index .

Four 、 Classification and creation of index

1. General index

 The most basic index type , There's no such thing as uniqueness .

Create index directly

CREATE INDEX Index name ON Table name ( Name [(length)]);

#( Name (length))：length Optional. . If you ignore length Value , Then use the value of the entire column as the index . If specified, use the... Before the column length Characters to create the index , This helps to reduce the size of the index file .
# The index name is suggested to be “_index” ending .

 example ：create index phone_index on member (phone);
select phone from member;
show create table member;

Modify tables to create

ALTER TABLE Table name ADD INDEX Index name ( Name );
example ：alter table member add index id_index (id);
select id from member;
select id,name from member;

Specify index when creating table

CREATE TABLE Table name ( Field 1 data type , Field 2 data type [,...],INDEX Index name ( Name ));
example ：create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));
show create table test;

2. unique index

• Similar to a normal index , But the difference is that each value of a unique index column is unique .
• Null values are allowed for unique indexes （ Note that it's different from the primary key ）. If it's a composite index , The combination of column values must be unique .
• Adding a unique key automatically creates a unique index .

Create a unique index directly

CREATE UNIQUE INDEX Index name ON Table name ( Name );
example ：select * from member;
create unique index address_index on member (address);
create unique index name_index on member (name);
show create table member;

Modify tables to create

ALTER TABLE Table name ADD UNIQUE Index name ( Name );
example ：alter table member add unique cardid_index (cardid);
Modify table member increase unique unique index Index name ：cardid——index（ Field name ）

When creating a table, specify

CREATE TABLE Table name ( Field 1 data type , Field 2 data type [,...],UNIQUE Index name ( Name ));
example :create table amd2 (id int,name varchar(20),unique id_index (id));
show creat table amd2;

3. primary key

• Is a special unique index , Must be specified as “PRIMARY KEY”.
• A table can only have one primary key , No null values are allowed . Adding a primary key will automatically create a primary key index .

When creating a table, specify

CREATE TABLE Table name ([...],PRIMARY KEY ( Name ));
example ：create table test1 (id int primary key,name varchar(20));
create table test2 (id int,name varchar(20),primary key (id));
show create table test1;
show create table test2;

 primary key
Specify... When creating a table , There are two ways
One is to directly specify... In the field
The second is to specify... After the end of the field

Modify tables to create

4. Composite index （ Single column index and multi column index ）

 It can be an index created on a single column , It can also be indexes created on multiple columns .

CREATE TABLE Table name ( Name 1 data type , Name 2 data type , Name 3 data type ,INDEX Index name ( Name 1, Name 2, Name 3));
select * from Table name where Name 1='...' AND Name 2='...' AND Name 3='...';
example :create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table amd1;
insert into amd1 values(1,'zhangsan','123123');
select * from amd1 where name='zhangsan' and id=1;

5. Full-text index （FULLTEXT）

 It is suitable for fuzzy query , Can be used to retrieve text information in an article .
stay MySQL5.6 Version before FULLTEXT The index can only be used for MyISAM engine , stay 5.6 After the version innodb The engine also supports FULLTEXT Indexes . Full text indexing can be done in CHAR、VARCHAR perhaps TEXT Create on column of type . Only one full-text index per table is allowed .

Create index directly

CREATE FULLTEXT INDEX Index name ON Table name ( Name );
example ：select * from member;
create fulltext index remark_index on member (remark);

Modify tables to create

ALTER TABLE Table name ADD FULLTEXT Index name ( Name );

Specify index when creating table

CREATE TABLE Table name ( Field 1 data type [,...],FULLTEXT Index name ( Name ));

# The data type can be CHAR、VARCHAR perhaps TEXT

Use full text index queries

SELECT * FROM Table name WHERE MATCH( Name ) AGAINST(' Query content ');
example ：select * from member where match(remark) against('this is vip');

5、 ... and 、 Look at the index

show index from Table name ;
show index from Table name \G; Display table index information vertically
show keys from Table name ;
show keys from Table name \G;
example ：show index from member;

The meaning of each field is as follows ：

Table	The name of the table
Non_unique	If the index can't include repetition , Then for 0; If possible , Then for 1.
Key_name	Name of index .
Seq_in_index	Column ordinal in index , from 1 Start .
Column_name	Column name .
Collation	How columns are stored in indexes . stay MySQL in , Valuable ‘A’（ Ascending ） or NULL（ No classification ）.
Cardinality	An estimate of the number of unique values in an index .
Sub_part	If the column is only partially indexed , Is the number of characters indexed . If the entire column is indexed , Then for NULL.
Packed	Indicates how keywords are compressed . If it's not compressed , Then for NULL.
Null	If the column contains NULL, It contains YES. without , The column contains NO.
Index_type	Used indexing methods （BTREE, FULLTEXT, HASH, RTREE）.
Comment	remarks .

6、 ... and 、 Delete index

Delete index directly

DROP INDEX Index name ON Table name ;
example ：drop index name_index on member;

Modify the table to delete the index

ALTER TABLE Table name DROP INDEX Index name ;

 example ：alter table member drop index id_index;
show index from member;

Delete primary key index

ALTER TABLE Table name DROP PRIMARY KEY;

MySQL Business

One 、MySQL Concept of transactions

• A transaction is a mechanism 、 A sequence of operations , Contains a set of database operation commands , And all the commands as a whole to submit or revoke the operation request to the system , That is, this set of database commands is either executed , Either not .
• A transaction is an indivisible unit of work logic , When performing concurrent operations on a database system , Transactions are the smallest unit of control .
• Transaction is suitable for the scenario of multi-user operating database system at the same time , Like a bank 、 Insurance companies and securities trading systems, etc .
• Transaction is to ensure data consistency through transaction integrity .

To put it bluntly , Transaction , It's a sequence of operations , These operations are either performed , Either not , It is an indivisible unit of work .

Two 、 The transaction ACID characteristic

ACID, In a reliable database management system （DBMS） in , Business (transaction) It should have four characteristics ： Atomicity （Atomicity）、 Uniformity （Consistency）、 Isolation, （Isolation）、 persistence （Durability）. These are the characteristics of a reliable database .

Atomicity ： A transaction is an indivisible unit of work , Either the operations in the transaction occur , Or none at all .

 A transaction is a complete operation , The elements of a transaction are indivisible .
All elements in a transaction must be committed or rolled back as a whole .
If any element in the transaction fails , Then the whole transaction will fail .

Uniformity ： Before and after the transaction , The database integrity constraint is not broken .

 When the transaction is complete , 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, ： In a concurrent environment , When different transactions manipulate the same data at the same time , Each transaction has its own full data space .

 All concurrent transactions that modify data are isolated from each other , Indicates that the transaction must be independent , It should not in any way depend on or affect other transactions .
A transaction that modifies data can access that data before another transaction that uses the same data starts , Or access the data after another transaction using the same data ends .

persistence ： After the transaction is completed , Changes made by the firm to the database are persisted in the database , It will not be rolled back .

 No matter whether the system fails or not , The result of the transaction is permanent .
Once the transaction is committed , The effect of the transaction is permanently preserved in the database .

3、 ... and 、 The interaction between transactions

There are several kinds of interactions between transactions , Respectively ：

1、 Dirty reading ： One transaction reads uncommitted data from another transaction , And it's possible to roll back this data .

2、 It can't be read repeatedly ： Two identical queries in a transaction return different data . This is caused by the commit of other transaction modifications in the system during query .

3、 Fantasy reading ： A transaction modifies the data in a table , This modification involves all data rows in the table . meanwhile , Another transaction also modifies the data in this table , This modification is to insert a new row of data into the table . that , The user operating the previous transaction will find that there are no modified data rows in the table , It's like an illusion .

4、 Lost update ： Two transactions read the same record at the same time ,A Change the record first ,B Also modify the record （B I do not know! A A modified ）,B After submitting the data B The result of the modification covers A Modification results of .

---

Four 、MySQL And transaction isolation level

1. read uncommitted : Read uncommitted data ： Don't solve dirty reading
2. read committed： Read submitted data ： Can solve dirty reading
3. repeatable read： Reread read ： Can solve dirty reading and It can't be read repeatedly —mysql default
4. serializable： Serialization ： Can solve Dirty reading It can't be read repeatedly and Virtual reading — It's like a lock table

mysql The default transaction level is repeatable read , and Oracle and SQL Server yes read committed .

1. Query global transaction isolation level

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

Both methods can view the global transaction isolation level

You can see mysql Default transaction level REPEATABLE-READ

2. Query session transaction isolation level

show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

3. Set the global transaction isolation level

SELECT @@global.tx_isolation;
set global transaction isolation level read committed;
SELECT @@global.tx_isolation;

4. Set session transaction isolation level

set session transaction isolation level read committed;

5、 ... and 、 Transaction control statement

BEGIN or START TRANSACTION： Open a transaction explicitly .
COMMIT or COMMIT WORK： Commit transaction , And make all changes that have been made to the database permanent .
ROLLBACK or ROLLBACK WORK： Rolling back ends the user's transaction , And undo all pending changes .
SAVEPOINT S1： Use SAVEPOINT Allows you to create a rollback point in a transaction , There can be more than one... In a transaction SAVEPOINT;“S1” Represents the name of the rollback point .
ROLLBACK TO [SAVEPOINT] S1： Roll back the transaction to the marked point .

Case study

create database SCHOOL;
use SCHOOL;
create table CLASS(
id int(10) primary key not null,
name varchar(40),
money double
);
insert into CLASS values(1,'A',1000);
insert into CLASS values(2,'B',1000);
select * from CLASS;

1. Test commit transactions

begin;
update CLASS set money= money - 100 where name='A';
select * from CLASS;
commit;
quit
mysql -u root -p
use SCHOOL;
select * from CLASS;

2. Test rollback transactions

begin;
update CLASS set money= money + 100 where name='A';
select * from CLASS;
rollback;
quit
mysql -u root -p
use SCHOOL;
select * from CLASS;

3. Test peer-to-peer rollback

begin;
update CLASS set money= money + 100 where name='A';
select * from CLASS;
SAVEPOINT S1;
update CLASS set money= money + 100 where name='B';
select * from CLASS;
SAVEPOINT S2;
insert into CLASS values(3,'C',1000);
select * from CLASS;
ROLLBACK TO S1;
select * from CLASS;

4. Use set Set control transaction

SET AUTOCOMMIT=0; # Disable auto submit
SET AUTOCOMMIT=1; # Turn on auto submit ,Mysql The default is 1
SHOW VARIABLES LIKE 'AUTOCOMMIT'; # see Mysql Medium AUTOCOMMIT value

• If Auto submit is not turned on , The name of the current session connection mysql All of the operations are treated as a transaction until you type rollback|commit; The current transaction is closed . New before the end of the current transaction mysql Unable to read the operation result of any current session while connecting .
• If Auto submit is on ,mysql Will put each sql Statement as a transaction , And then automatically commit.
• Of course, whether it's on or off ,begin; commit|rollback; It's all independent business .

use SCHOOL;
select * from CLASS;
SET AUTOCOMMIT=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';
update CLASS set money= money + 100 where name='B';
select * from CLASS;
quit
mysql -u root -p
use SCHOOL;
select * from CLASS;

MySQL Storage engine

One 、 Introduction to the concept of storage engine

• MySQL The data in is stored in files with various technologies , Each technology uses a different storage mechanism 、 Indexing techniques 、 Lock levels and ultimately provide different functions and capabilities , These different technologies and supporting functions are in MySQL It's called storage engine in
• The storage engine is MySQL Storage mode or storage format of data stored in file system

1.MySQL Common storage engines

**1、MyISAM
**

2、InnoDB

• MySQL Components in the database , Responsible for executing the actual data I/O operation
• MySQL In the system , The storage engine is on top of the file system , The data is transferred to the storage engine before it is saved to the data file , Then, it is stored according to the storage format of each storage engine

MyISAM

1.MyISAM Characteristics

MyISAM Unsupported transaction , Foreign key constraints are not supported either , Full text indexing only , Data files and index files are kept separately

Fast access , There is no requirement for transaction integrity
MyISAM Suitable for inquiry 、 Insertion based applications

MyISAM Store three files on disk , The file name and table name are the same , But the extensions are ：
.frm Definition of file storage table structure
The extension of the data file is .MYD (MYData)
The extension of the index file is .MYI (MYIndex)

Table level locking form , Lock the entire table when the data is updated
Databases block each other in the process of reading and writing :
It will block the reading of user data in the process of data writing
It will also block the user's data writing in the process of data reading
Data is written or read separately , The process is fast and takes up less resources

2、MyISAM Table support 3 Different storage formats ：

1. static state ( Fixed length ) surface
   Static tables are the default storage format . Fields in static tables are immutable , So every record is a fixed length , The advantage of this storage method is that the storage is very fast , Easy to cache , It's easy to recover in case of failure ; The disadvantage is that it usually takes up more space than a dynamic table .
2. Dynamic table
   Dynamic tables contain variable fields , Records are not fixed length , The advantage of this storage is that it takes up less space , But frequent updates 、 Deleting records will cause fragmentation , It needs to be carried out on a regular basis OPTIMIZE TABLE Sentence or myisamchk -r Command to improve performance , And it's relatively difficult to recover in case of failure .
3. Compression meter
   The compressed table consists of myisamchk Tool creation , Take up a very small space , Because each record is compressed individually , So there's only a very small cost of access .

3、MyISAM Applicable production scenarios

Business doesn't need the support of business
Read or write data unilaterally
MyISAM The storage engine reads and writes data frequently, which is not suitable for the scenario
Using read-write concurrency to access relatively low business
Business with relatively little data modification
Services that do not require very high consistency of data services
The server hardware resources are relatively poor

lnnoDB

One .lnnoDB characteristic

Support transactions , Support 4 Transaction isolation levels
MySQL from 5.5.5 Version start , The default storage engine is InnoDB

Read and write blocking is related to transaction isolation level
It can cache index and data very efficiently
Tables and primary keys are stored in clusters
Support partition 、 Table space , similar oracle database
Support for foreign key constraints ,5.5 Full text indexing is not supported before ,5.5 Full text index is supported after

The requirement for hardware resources is relatively high
Row level locking , But all Table scanning will still be table level locking , Such as
update table set a=1 where user like ‘%lic%’;

InnoDB The number of rows in the table is not saved , Such as select count() from table; when ,InnoDB You need to scan the entire table to calculate how many rows there are , however MyISAM Simply read out the number of saved lines . It should be noted that , When count() The statement contains where When the conditions MyISAM You also need to scan the entire table
For self growing fields ,InnoDB Must contain only the index of this field , But in MyISAM You can create a composite index with other fields in the table

When you empty the entire table ,InnoDB It's line by line deletion , Efficiency is very slow .MyISAM The table is rebuilt

Two 、 Check out the storage engines supported by the system

show engines;

3、 ... and 、 Look at the storage engine that the table uses

Method 1

show table status from Library name where name=' Table name '\G;
example ：show table status from SCHOOL where name='CLASS'\G;

Method 2

use Library name ;
show create table Table name ;
example ：use SCHOOL;
show create table CLASS;

Four 、 Modify the storage engine

Method 1 ：

adopt alter table modify

use Library name ;
alter table Table name engine=MyISAM;
example ：use SCHOOL;
alter table CLASS engine=myisam;
show create table CLASS;

Method 2 ：

By modifying the /etc/my.cnf The configuration file , Specify the default storage engine and restart the service

quit
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB
systemctl restart mysqld.service
# Be careful ： This method only changes the configuration file and restarts mysql The newly created table is valid after service , The existing table will not be changed .
systemctl restart mysqld.service
Remember to restart after modification mysql service

notes ： This method only changes the configuration file and restarts mysql The newly created table is valid after service , The existing table will not be changed .

Method 3 ：

adopt create table Specify the storage engine when creating the table

use Library name ;
create table Table name ( Field 1 data type ,...) engine=MyISAM;
example ：mysql -u root -p
use SCHOOL;
create table hellolic (name varchar(10),age char(4))engine=myisam;