Summary of MySql storage engine and index related knowledge

Storage Engine

What is a database storage engine?

The database engine is the underlying software component of the database. Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions. Using different database engines can obtain specific functions.

How to view the engine?

--How to view the engines supported by the database show engines;
​
--View the current data engine:
show create table table name\G
​
--View the engines of all tables in the current library:
show table status_G

Specify the engine when creating a table

create table yingqin (id int,name varchar(20)) engine='InnoDB';

Engine for modifying tables

alter table table name engine = 'engine name';

Modify the default engine

1. vi /etc/my.cnf (configuration file address depends on the installation situation)
2. [mysqld] below
3. default-storage-engine=MyIsAM
4. Remember to restart the service after saving
   

Differences between MyISAM and InnoDB

MyISAM: supports full-text indexing (full text); does not support transactions; table-level locks; saves the specific number of rows in the table; crash recovery is poor.

Innodb: supports transactions; previous versions did not support full-text indexing, but versions after 5.6 began to support this feature; row-level locks (not absolute, when the range cannot be determined when executing a SQL statement, the entire table will be locked, for example: update table set id=3 where name like 'a%';); does not save the specific number of rows in the table; good crash recovery.

When to choose which engine is better

MyISAM:

• When no transaction is needed
• Do a lot of count calculations
  

InnoDB:

• High reliability requirements or support services
• When you want to use foreign key constraints (foreign keys created by MyISAM are invalid)
  

InnoDB is recommended

index

What is an index?

Indexes are separate database structures stored on disk that contain reference pointers to all the records in a table. Using indexes, you can quickly find rows that have specific values ​​in one or more columns.

Advantages of indexing:

• By creating a unique index, the uniqueness of each row of data in the database table is guaranteed.
• It can speed up the retrieval of data.
• Can ensure the integrity and accuracy of table data
  

Disadvantages of indexing:

• Indexes require physical space.
• When the data in the table is modified, the index also needs to be maintained dynamically, which reduces the speed of data maintenance.

Common types of indexes:

• index: normal index
• unique: unique index
• primary key: primary key index
• foreign key: foreign key index
• fulltext: full text index
• Composite Index
  

Normal index and unique index

What is a normal index?

As the name suggests, a common index is the most common index among all types of indexes. Its main task is to improve query speed. Its characteristics are that the same index content is allowed and null values ​​are allowed.

What is a unique index?

Unique index: As the name implies, the same index content cannot appear, but it can be empty (null) value

How to create a normal index or a unique index?

--Create table test (
  id int(7) zerofill auto_increment not null,
  username varchar(20),
  servnumber varchar(30),
  password varchar(20),
  createtime datetime,
  unique (id)
)DEFAULT CHARSET=utf8;

--Add an index to the table directly--Syntax: alter table table name add index index name (field name);
--Note: If the index name is not specified, the default field name will be used as the index name alter table test add unique unique_username (username);
​
--Create an index directly--Syntax: create index index on table name (field name);
create index index_createtime on test (createtime);

View Index

--Syntax: show index from table name\G
show index from test_G

How to delete an index

--Syntax: drop index index name on table name;
drop index unique_username on test;
--Syntax: alter table table name drop index index name;
alter table test drop index createtime;

Primary key index

What is a primary key index?

Adding an index to the primary key is a primary key index, which is a special unique index that does not allow null values, while a unique index (unique) allows null values. Specified as "PRIMARY KEY"

Primary key: The primary key is a column in a table, and the value of this column is used to mark each row of data in the table. Note: Each table can only have one primary key.

Create a primary key:

--1) Create when creating a table --2) Add a primary key index directly to the table --Syntax: alter table table name add primary key (field name);
alter table test add primary key (id);

To delete a primary key:

--Syntax: alter table table name drop primary key;
alter table test drop primary key;

Note: If there is an auto-increment, you must delete the auto-increment before you can delete the primary key.

​--Delete auto-increment:
alter table test change id id int(7) unsigned zerofill not null;

Full-text index

What is full-text indexing?

A full-text index is an index that searches for any content information such as articles or sentences stored in a database, and the unit of the index is words. Full-text indexing is also a key technology currently used by search engines. Specify fulltext

--Create the SQL for the practice table:
create table command (
  id int(5) unsigned primary key auto_increment,
  name varchar(10),
  instruction varchar(60)
)engine=MyISAM;

--Insert data sql:
insert into command values('1','ls','list directory contents');
insert into command values('2','wc','print newline, word, and byte counts for each file');
insert into command values('3','cut','remove sections from each line of files');
insert into command values('4','sort','sort lines of text files');
insert into command values('5','find','search for files in a directory hierarchy');
insert into command values('6','cp','Copy files or folders');
insert into command values('7','top','display Linux processes');
insert into command values('8','mv','Modify file name, move');
insert into command values('9','Stop words','is,not,me,yes,no ...');

Add a full-text index:

--1) Create a full-text index when creating a table --2) Add through alter alter table command add fulltext(instruction);

Use full-text indexing:

--Syntax: select * from table name where match (field name) against ('search content');
select * from command where match(instruction) against ('sections');

Check the matching degree:

select * from command where match(instruction) against ('directory');

Stop words:

Words that appear very frequently will invalidate the full-text index.

in boolean mode:

in boolean mode: means to specify the full-text search mode as Boolean full-text search (which can be simply understood as the search method)

--Syntax: select * from table name where match (field name) against ('search content' in boolean mode);
select * from command where match(instruction) against ('direct*' in boolean mode);

Note: When using the wildcard *, it can only be placed after the word, not before.

To delete a full-text index:

alter table command drop index instruction;

Summary of points to note:

1. Generally, the field data types for creating full-text indexes are char, varchar, and text. Other field types are not allowed.
2. Full-text indexing does not index very frequent words. For example, is, no, not, you, me, yes, we call them stop words
3. Ignore uppercase and lowercase letters when searching in English

Foreign key constraints

What is a foreign key?

A foreign key is one or more columns that act as a link between the data in two tables to ensure the integrity and accuracy of the data between the tables.

Add a foreign key constraint:

--Syntax: foreign key (field name) references associated table name (field name of associated table)
--​Note: The field types of the primary key and the foreign key must be the same--create table method:
CREATE TABLE `employee` (
 `empno` int(11) NOT NULL COMMENT 'Employee number',
 `ename` varchar(50) DEFAULT NULL COMMENT 'Employee name',
 `job` varchar(30) DEFAULT NULL,
 `mgr` int(11) DEFAULT NULL COMMENT 'Employee supervisor number',
 `hiredate` date DEFAULT NULL COMMENT 'hired date',
 `sal` decimal(7,2) DEFAULT NULL COMMENT 'Salary',
 `deptnu` int(11) DEFAULT NULL COMMENT 'Department number',
 PRIMARY KEY (`empno`),
 foreign key (deptnu) references dept(deptnu)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

--Alter table method:
alter table employee add foreign key (deptnu) references dept(deptnu);

To drop a foreign key constraint:

Note: Before you can delete a foreign key index, you must first delete the foreign key constraint.

mysql> alter table employee drop index deptnu;
ERROR 1553 (HY000): Cannot drop index 'deptnu': needed in a foreign key constraint
mysql> 
mysql> alter table employee drop foreign key employee_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> 
mysql> alter table employee drop index deptnu;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Summary of points to note:

1. The field types of the primary key and foreign key of the two tables must be the same
2. The engine for using foreign key constraints must be InnoDB. MyISAM does not work.
3. Before you can delete a foreign key index, you must first delete the foreign key constraint.

Joint Index

What is a joint index?

A joint index, also known as a combined index or a composite index, is an index based on two or more columns.

Creating a Joint Index

--Syntax: alter table table name add index (field 1, field 2, field 3);
alter table test add index(username,servnumber,password);

Deleting a joint index

--Syntax: alter table test drop index index name;
alter table test drop index username;

Why use a joint index instead of multiple single-column indexes?

The efficiency of a joint index is much higher than that of a single-column index. If three single-column indexes are created and the query conditions also contain these three columns, MySQL will only select the best column index and will not use all three indexes.

Leftmost Principle of Joint Index

Taking the above index as an example, the query condition must contain username to use this index, otherwise it will not be used.

Summary of points to note:

• The more indexes, the better. Too many indexes will increase the speed of data maintenance and waste disk space.
• When the amount of data in the table is large, you can consider creating an index.
• For fields in the table where data is frequently queried, you can consider creating indexes.
• To ensure the uniqueness of data in a table, you can consider creating a unique index.
• If you want to ensure the integrity and accuracy of the data in the two tables, you can consider establishing a foreign key constraint.
• When you frequently query multiple columns of data, you can consider creating a joint index.

The above is the detailed content of the summary of the knowledge related to MySql storage engine and index. For more information about MySql storage engine and index, please pay attention to other related articles on 123WORDPRESS.COM!