MySQL database optimization (1)—MySQL engine

1. MySQL engine types
(1), InnoDB
1. Introduction to InnoDB
InnoDB is my default engine, supports transactions, and the data in is stored in the tablespace (tablespace). Prevent phantom reads through gap locking (next-key locking).
2. InnoDB Features
(1) In has done a lot of optimizations internally. In builds tables based on clustered indexes and has high performance for primary key queries.
(2) When reading data, a hash index can be created in the memory to speed up the insertion into the buffer, and read-ahead is used when reading data.
(3) Support hot backup, my other engines do not support this feature.
(2), MyISAM
1, Introduction to MyISAM
MyISAM is the default engine for versions prior to my5.1. It supports full-text indexing, compression, and spatial functions, but does not support transactions and row-level locks. It is suitable for applications that only support full-text indexing, compression, and spatial functions. Application scenarios where data is read and tables are small and can tolerate repair operations.
2. MyISAM features
(1) MyISAM storage stores tables in two files: data files and index files.
(2) MyISAM supports locking the entire table, but does not support row locking.
(3) MyISAM compressed table. For some table data that will no longer be modified, use MyISAM to compress the table to reduce disk I/O operations and improve query efficiency.
(3), CSV
The CSV engine can process ordinary CSV files as Mysql tables, and copy files in and out when the database is running. In addition, it can also dump excel and other format files into CSV, and then use Mysql The CSV engine processes it into mysql table type. As a data exchange mechanism, it is very practical.
(4), Memory
The Memory engine saves all data into the memory without disk I/O. The data it stores is mainly to save and search for intermediate data during data processing, and restart the data. Just clear it. If a temporary table is needed to save intermediate results during Mysql execution, the temporary table used internally is the Memory table.
(5) NDB Cluster
The NDB cluster index was developed by mysql after acquiring the NDB database. It supports distributed, disaster-tolerant, and high-availability database indexes.
2. Engine related operations
1. View the current version of mysql engine
Use the command show enginses; view the mysql engine
Mysql engine in version 5.1:

5.7 version The mysql engine in (can be viewed by executing the command on the mysql client):

2. Specify the mysql engine
Method 1: Modify the my.cnf configuration file

(1) If you are not sure where the my.cnf configuration file of mysql is stored, you can use sudo find /-name my.cnf to find the file path.

(2) is modified as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

default-storage-engine=MyISAM

( 3) Use the service mysqld start command to restart the mysql service; check the engine type

2. Specify the engine when creating the library/table
CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB
ALTER TABLE mytable ENGINE = MyISAM

The above is the content of MySQL database optimization (1) - MySQL engine. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!