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

Below I will briefly tell you about Mysql index, storage engine, what is the specific difference, you know about similar topics before? Interested words to take a look at this article, I believe that after reading Mysql index, storage engine what is the specific difference to everyone how much help it.

Common index: index

View index:

desc library name. Table name;

show index from Library Name. Table name;

Index type used by default:

BTREE hash B+Tree

Create index:

--> Create table when creating:

create table name (

Field name type (width) constraint,

Field name type (width) constraint,

index (field 1), index (field 2)

）;

create table t2 (

name char(10) not null,

index(name)

);

--》Existing table Add index:

create index name on table name (field name);

create index id on t1(id);

//Note: Our index name and field name can be different, but for the convenience of identification, we generally set the index name and field name the same.

Delete index:

drop index name on table name;

##########################################################################################3

Primary Key: Primary Key

Rules of Use:

--> Set the field value of the primary key. Cannot be duplicate and null.

A table can only have one primary key.

If multiple fields are used as primary keys, called composite primary keys, they must be created together.

--> The key flag of the primary key field is pri

--"is usually used with auto_increment.

Create Primary Key:

--> Create table when creating:

create table name (

Field Name 1 Type (Width) Constraint,

Field Name 2 Type (Width) Constraint,

primary key (field name 1, field name 2)

）;

create table t4(

id int auto_increase, //Set automatic increase (if no value is assigned, it will automatically increase by 1 according to the previous record)

name char(10),

primary key(id,name)

);

--> already created table:

alter table name primary key (field name);

alter table t1 primary key（id）;

Delete Primary Key:

alter table name drop primary key;

##########################################################################################

Third, unique index: unique

Rules of Use:

--》field value can be null, but cannot be repeated

--》When the field is modified to null, the restriction is the same as the primary key,

There can be multiple unique fields in a table.

Create a unique index:

--> Create table when creating:

create table name (

Field Name 1 Type (Width) Constraint,

Field Name 2 Type (Width) Constraint,

unique(field name 1),

unique (field name 2)

）;

create table t5(

id int,

name char(10),

unique(id),

unique(name)

);

--> already created table:

create unique index name on table name (field name);

Delete Unique Index:

drop index name on table name;

#############################################################################################

Foreign key: Foreign key

Foreign key:

Lets the value of a field in the current table be selected within a range of field values in another table.

Rules of Use:

The storage engine for the table must be innodb.

--> field types should be consistent

The referenced field must be one of the index types.

Create foreign key:

Command: foreign key(field name) references table name (field name)

on update cascade on delete cascade

create table jfb(

id int auto_increment,

name char(10),

pay float(7,2),

primary key(id),

);

create table xsb(

num int,

name char(10),

class char(10),

foreign key(num) references key(id) on update cascade on delete cascade

);

//Create jfb table, create xsb table. num of xsb needs to refer to id field in jfb. That is, num is set to the foreign key and id in jfb is set to the reference key. And set up automatic updates.

insert into jfb values(1,"bob",20000),(2,"lucy",19000);

insert into xsb values(3,"jim","1709"); //Cannot insert because num is not in the id range of jfb.

insert into xsb values (1,"jim","1709"; //insert succeeded

update jfb set id=6 where name="bob"

select * from xsb; //update id field data in jfb, view records in xsb, and update at the same time

delete from jfb where name="bob";

selece * from xsb; //Delete the record in the jfb table, and the record in xsb will be deleted accordingly.

View foreign key:

show create table name; //You can view foreign key information, including foreign key names

Delete foreign key:

alter table name drop foreign key name;

##############################################################################################

mysql storage engine:

I. Introduction:

mysql database service software comes with procedures, different storage engines have different functions and data storage methods, is the table processor.

mysql service architecture:

connection pool

SQL interface

analyzer

Optimizer

query cache

storage engine

file system

management tools

########################################################################################

II. Basic settings,

To view the storage engines used by database services:

show engines;

View the storage engine used by the table:

show create table name;

Set up the storage engine used by the database service:

vim /etc/my.cnf

[mysql]

default-storage-engine= engine type

Set up the table's storage engine:

create table name (...); //Use default engine

create table name (...) engine=innodb; //Set the storage engine to innodb

Modify the storage engine used by the table:

alter table name engine= storage engine type;

################################################################################3

Third, the characteristics of common engines:

Myisam Features:

Table.MYI Index Information

Table.MYD data

Table.frm Table structure

Support table-level lock (lock a table)

Transaction rollback is not supported

innodb Features:

Table.frm Table structure

Table.ibd index info + data

Support row level locking (lock only the currently accessed row)

Support transaction rollback

The role of locks: resolving concurrent access conflicts.

Lock type: read lock and write lock

Lock granularity: row-level lock table-level lock

Transaction: The process from start to finish of a data access is called a transaction

Transaction rollback: A data access fails at any step and all operations are resumed.

Characteristics of transactions: consistency atomicity isolation

Transaction log file: records operations performed on innodb storage engine tables.

How to decide which storage engine to use for tables:

Tables that receive a lot of write operations are suitable for use with the innodb storage engine.

A table that receives a lot of read operations is suitable for use with the myisam storage engine

What is the difference between Mysql index and storage engine? How about this article? If you want to know more about it, you can continue to pay attention to our industry information section.

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.