02:mysql index type, MySQL storage engine

Day02
One, MySQL index
Second, MySQL storage engine
+++++++++++++++++++++++++++++++++++
One, MySQL index
1.1 Index Description: Equivalent to "book directory"

Page 5000
1~200 directory information
Phonetic sorting
Radical sort
Stroke sort

201~5000 body

1.2 Advantages and disadvantages of the index?
Advantages to speed up queries
The disadvantage takes up physical storage space and slows down the writing speed.

Name Sex class Age
Jim
Jim
Null

1.3 Using a normal index: (Create an index on a field in a table)
Use rules?
View desc table name; key----> MUL
Show index from table name;
table:t23
Key_name:name
Column_name:name
Index_type:btree (B+tree Hash)
Two-fork Tree
1-10
1-5 6-10
1-2.5 2.6-5

To create INDEX indexes:
Specify an index field when creating a table
CREATE TABLE Table name (
List of field names,
Index (field name 1),
Index (field name 2),
);
CREATE TABLE db1.t23 (
Name Char (10),
Sex enum ("Boy", "Girl") The default "boy",
Age tinyint (2) unsigned NOT null default 18,
Index (name),
Index (Sex)
);
Desc db1.t23;
Show index from Db1.t23\g;

Create an index field in an existing table
Create index index name on table name (field);

Delete index index drop index name on table name;

Second, the use of the primary key primary key
2.1 Usage rules?

2.2 views
DESC table name; key----> MUL
Show index from table name;

2.3 Create
2.3.1 created when a table is built
CREATE TABLE T25 (
stu_id Char (9) Primary key,
Name Char (10),
Sex enum ("Boy", "Girl"),
Age tinyint (2) unsigned
);
CREATE TABLE T24 (
stu_id Char (9),
Name Char (10),
Sex enum ("Boy", "Girl"),
Age tinyint (2) unsigned,
Primary KEY (STU_ID)
);
2.3.2 is created in an existing table
ALTER TABLE name Add primary key (field name);
2.4 Delete primary key ALTER TABLE name drop PRIMARY key;

2.5 Use of composite primary keys (multiple fields in a table together with the master key, the values of the composite primary key fields are not allowed to be duplicated at the same time, to be created together)
Pri pri
Name class pay
Jim nsd1710 Yes
Jim nsd1712 No
Create a table when you build it
CREATE TABLE DB1.XFB (
Name Char (10),
Class char (7),
Pay enum ("Yes", "no") default "No",
Primary KEY (Name,class)
);
Verify
INSERT into DB1.XFB values ("Jim", "nsd1710", "yes");
INSERT into DB1.XFB values ("Jim", "nsd1710", "yes");
INSERT into DB1.XFB values ("Jim", "nsd1710", "no");
INSERT into DB1.XFB values ("Bob", "nsd1710", "yes");
INSERT into DB1.XFB values ("Bob", "nsd1703", "no");
Delete Composite PRIMARY key ALTER TABLE name drop PRIMARY key;
Adds a composite primary key to an existing table.
ALTER TABLE name add primary key (List of field names);
++++++++++++++++++++++++++++++++++++++++++
2.6 Primary Key with Auto_increment
The value of the field automatically grows i++ i= $i +1
Numeric type
Primary key

ID Name age Sex
1 Jim Boy
2 Tom Boy
CREATE TABLE Db1.t26 (
ID int (2) Zerofill primary key auto_increment,
Name Char (10),
Age tinyint (2) unsigned,
Sex enum ("Boy", "Girl", "no")
);

Insert into Db1.t26 (name,age,sex) VALUES ("Bob", +, "boy");
Insert into Db1.t26 (name,age,sex) VALUES ("Bob", +, "boy");
Insert into Db1.t26 (name,age,sex) VALUES ("Bob", +, "boy");
SELECT * from Db1.t26;

Unique Indexes Unique

          pri                pri

Name Passport number driver's license number
NULL NULL

Use rules?
View desc table name; key----> UNI

Create
Create a table when you build it
CREATE TABLE Db1.t27 (
Name Char (10),
hz_id char (5),
jsz_id char (5),
Unique (hz_id),
Unique (jsz_id)
);
Desc db1.t27;
INSERT into db1.t27 values ("Jim", "AAA", "BBB");
INSERT into db1.t27 values ("Jim", "AAA", "BBB");
INSERT into db1.t27 values ("Jim", "Aaab", "BBBC");
INSERT into db1.t27 values ("Jim", Null,null);

CREATE TABLE Db1.t28 (
Name Char (10),
hz_id char (5) NOT NULL,
jsz_id char (5),
Unique (hz_id),
Unique (jsz_id)
);
Desc DB1.T28;

Create a unique in an existing table
Create unique index index name on table name (field name);

Delete Drop index index name on table name;
+++++++++++++++++++++++++++++++++++++++
Third, the use of foreign keys
Foreign key role?
What are the rules for using foreign keys?
To create a foreign key:
Foreign key (field name) References table name (field name)
On UPDATE cascade on DELETE CASCADE

JFB Payment Form
School Number
jfb_id name Pay

CREATE TABLE DB1.JFB (
jfb_id Int (2) primary key auto_increment,
Name Char (10),
Pay float (7,2) default 20000
) Engine=innodb;

Insert into DB1.JFB (name) VALUES ("Bob"), ("Tom");

BJB Class Table
FOREIGN key
School Number
bjb_id name Pay

CREATE TABLE DB1.BJB (
bjb_id Int (2),
Name Char (10),
Pay float (7,2) default 20000,
Foreign KEY (bjb_id) references JFB (jfb_id)
On UPDATE cascade on DELETE CASCADE
) Engine=innodb;

ALTER TABLE BJB add primary key (BJB_ID);

Use DB1;
Show CREATE TABLE BJB;

Verify foreign keys?
INSERT into BJB values (1, "Bob", 20000);
INSERT into BJB values (3, "Lucy", 20000);
Insert into JFB (name) VALUES ("Lucy");
INSERT into BJB values (3, "Lucy", 20000);

Update table name SET field name = value where condition;
Update JFB set jfb_id=8 where jfb_id=2;
Select from jfb;
Select from BJB;

Delete from table name where condition;
Delete from JFB where jfb_id=3;
Select from jfb;
Select from BJB;

What should I use to pay attention to?

Delete foreign key ALTER TABLE name drop FOREIGN key foreign key;
Show CREATE TABLE BJB;
ALTER TABLE BJB drop foreign key bjb_ibfk_1;
To create a foreign key in an existing table:
ALTER TABLE BJB add foreign key (bjb_id) references JFB (jfb_id)
On UPDATE cascade on DELETE cascade;

+++++++++++++++++++++++++++++++++++
Four, MySQL storage engine
4.1 MySQL Storage Engine introduction: Is the database service comes with the function program,
Processor for processing tables
Each storage engine has different capabilities and data storage methods
4.2 views
The table name used by the storage engine to show create table;

Storage engines used by data services
Show engines;
InnoDB | DEFAULT

4.3 modifications
The storage engine used by the table?
ALTER TABLE name engine= storage engine name;

Specify the storage engine used by the table when the table is built?
CREATE TABLE Table name (
List of field names
.....
) engine= storage engine name;
The storage engine used by the data service?
Vim/etc/my.cnf
[Mysqld]
Default-storage-engine=myisam
.....
: Wq
#systemctl Restart Mysqld
++++++++++++++++++++++++++++++++
4.4 Common storage engines and features in production environments
MyISAM Features
Support for table-level locks
Foreign key, transaction, transaction rollback not supported
How the data is stored. frm. MYI. MYD
Table Structure Index data
InnoDB Features
Support for row-level locks, foreign keys, transactions, transaction rollback
How data is stored. frm. ibd
Table structure Index + data

The role of locks: resolving conflicting issues with concurrent access
Lock type: Read lock (Shared lock) write lock (exclusive lock)
Lock granularity: Table lock row lock (page lock)

Transactions: A single SQL operation from a connection to a disconnected process is called a transaction. Either all succeeds, any one-step error, and execution fails.

Atm
Insert Card
Transfer: Offset card number 11111
Remittance amount 50000
Transfer in .....

Return card

Transaction rollback: The transaction execution process, any one step execution is unsuccessful, all operations will be resumed.

The InnoDB storage engine table uses transaction files to record the SQL operations that were performed.
cd/var/lib/mysql/db1/t1.*
Ls
Ib_logfile0
Ib_logfile1
Ibdata1

INSERT into T1 VALUES (101), (102);

4.5 How to decide what kind of storage engine the table uses when building a table
Receive access to a number of tables, suitable for the use of MyISAM storage engine, saving system resources.
Receive write-Access tables that are suitable for use with the InnoDB storage engine and have a large number of concurrent accesses.

02:mysql index type, MySQL storage engine