Basic Learning for MySQL

1. mysql main storage engine: Myisam,innodb and memory

(1) MyISAM

It does not support transactions, does not support foreign keys, especially fast access, there is no requirement for transactional integrity, or a SELECT, insert-based application can basically use this engine to create tables.

The only support is full text search index.

Supports static (fixed-length) tables, dynamic tables, and compressed tables in 3 different storage formats.

(2) InnoDB

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.

Auto-Grow column: for InnoDB tables, the autogrow column must be an index. If it is a composite index, it must also be the first column of the combined index, but for the MyISAM table, the autogrow column can be the other column of the combined index, so that when the record is inserted, the autogrow column is incremented by the combined index to the previous column sort.

FOREIGN KEY constraints: MySQL supports foreign key storage engine only InnoDB, when creating foreign keys, the parent table must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.

When you create an index, you can specify the corresponding actions on the child table when the parent table is deleted, updated, including restrict, Cascade, set NULL, and no action. where restrict and no action are the same, it means that the parent table cannot be updated if the child table is associated, Casecade indicates that the parent table updates or deletes the records corresponding to the child table when updating or deleting, and set NULL indicates that the parent table is updated or deleted. The field corresponding to the child table is set NULL.

When a table is created with a foreign key reference by another table, the table's corresponding index or primary key is forbidden from deletion.

You can use set foreign_key_checks=0, temporarily turn off foreign key constraints, set Foreign_key_checks=1, and open constraints.

(3) MEMORY

Memory creates a table using the content that exists in it. Each memory table actually corresponds to a disk file in the format of. frm. The memory type of table access is very fast because it goes to the data in memory, and the hash index is used by default, but once the server shuts down, the data in the table is lost, but the table continues to exist.

By default, the memory data table uses a hash index, which makes the "equality comparison" very fast, but the "range comparison" is much slower. Therefore, the hash index value is suitable for use in the "=" and "<=>" operators, not suitable for use in the "<" or ">" operators, also not applicable in the order by sentence. If you do want to use the < or > or the betwen operator, you can use the Btree index to speed up.

CREATE TABLE Tab_memory engine=memory select Id,name,age,addr from mans order by ID;

CREATE index Mem_hash using hash on tab_memory (city_id);

2. Created on a MySQL database server instance, you can create multiple databases on an instance.

3. Index disadvantage:. Reduce the speed of updating tables, such as INSERT, UPDATE, and delete on tables.

Index files that create indexes that consume disk space.

The index does not exceed three levels, and three layers require four IO.

Index is a+b+c, retrieve BC, skip way, still can use index.

Full-text indexing, only English content is supported.

4. Common constraints

PRIMARY KEY constraint: Unique, non-nullable, one table with only one primary key. When you create a constraint on a primary key, the system establishes the corresponding unique index on the columns column combination.

The unique index is unique, but can be empty, and a table can have more than one.

When a foreign key is created, the index is automatically established.

5. Stored Procedures and functions

stored programs and functions are subroutines created with the CREATE PROCEDURE and creation function statements.

Stored procedures are centralized and cannot be distributed using stored procedures.

6. Trigger

A trigger is a named database object related to a table that is activated when a specific event occurs on the table.

CREATE TRIGGER trigger_name trigger_time trigger_event

On tbl_name for each ROW trigger_stmt

The For each ROW is trigger_stmt and inefficient. Triggers are generally not commonly used.

Basic Learning for MySQL