Let's talk about the difference between MyISAM and InnoDB

The main differences are as follows:

1. MySQL uses MyISAM by default.

2. MyISAM does not support transactions, but InnoDB does. InnoDB's AUTOCOMMIT is turned on by default, that is, each SQL statement will be encapsulated into a transaction by default and automatically committed. This will affect the speed, so it is best to put multiple SQL statements between begin and commit to form a transaction for submission.

3. InnoDB supports data row locking, while MyISAM does not support row locking and only supports locking the entire table. That is, the read lock and write lock on the same table of MyISAM are mutually exclusive. When MyISAM reads and writes concurrently, if there are both read requests and write requests in the waiting queue, the default write request has a higher priority, even if the read request arrives first. Therefore, MyISAM is not suitable for situations where a large number of queries and modifications coexist, as the query process will be blocked for a long time. Because MyISAM locks the table, a time-consuming read operation may starve other write processes.

4. InnoDB supports foreign keys, but MyISAM does not.

5. InnoDB's primary key range is larger, up to twice that of MyISAM.

6. InnoDB does not support full-text indexing, but MyISAM does. Full-text indexing means creating an inverted index for each word (except stop words) in char, varchar, and text. MyISAM's full-text index is actually useless because it does not support Chinese word segmentation. The user must add spaces after word segmentation and then write it to the data table. In addition, words with less than 4 Chinese characters will be ignored like stop words.

7. MyISAM supports GIS data, but InnoDB does not. That is, MyISAM supports the following spatial data objects: Point, Line, Polygon, Surface, etc.

8. Count(*) without where is much faster using MyISAM than InnoDB. Because MyISAM has a built-in counter, it reads directly from the counter when count(*), while InnoDB must scan the entire table.

Therefore, when executing count(*) on InnoDB, it is usually accompanied by a where clause, and the where clause must include index columns other than the primary key.

Why is “outside the primary key” emphasized here? Because in InnoDB, the primary index is stored together with the raw data, while the secondary index is stored separately, and there is a pointer pointing to the primary key.

Therefore, if you only count(*), it is faster to use the secondary index to scan, while the primary key is mainly useful when scanning the index and returning raw data at the same time.

Supplementary knowledge: What does ENGINE=InnoDB and AUTO_INCREMENT mean in MySQL?

As shown below:

CREATE TABLE `hui_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user table id',
 `username` varchar(50) NOT NULL COMMENT 'Username',
 `password` varchar(50) NOT NULL COMMENT 'User password, MD5 encrypted',
 `email` varchar(50) DEFAULT NULL,
 `phone` varchar(20) DEFAULT NULL,
 `question` varchar(100) DEFAULT NULL COMMENT 'Password recovery question',
 `answer` varchar(100) DEFAULT NULL COMMENT 'Retrieve password answer',
 `role` int(4) NOT NULL COMMENT 'Role 0-Administrator, 1-Ordinary User',
 `create_time` datetime NOT NULL COMMENT 'Creation time',
 `update_time` datetime NOT NULL COMMENT 'Last update time',
 PRIMARY KEY (`id`),
 UNIQUE KEY `user_name_unique` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

In this sql statement, there is such a

ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

But I don't quite understand what the last three items are for?

1. Isn't ENGINE=InnoDB the default engine?

2. AUTO_INCREMENT=22, isn’t it auto-incrementing? Why set numbers?

3. Isn't utf8 already set in my.ini?

Isn't ENGINE=InnoDB the default engine?

——Yes, if you don't write it, it will be ok, and the default will be used. I write it here because you can clearly see what is used in this table creation statement, and it is also a good habit to write it when creating a table.

AUTO_INCREMENT=22, isn't it auto-incrementing? Why set numbers?

——This is auto-incremental. Setting a number here means that you want this statement to auto-increment from 22 as it grows.

Isn't utf8 already set in my.ini?

——Although this has been set in my.ini, it sets the language encoding of MySQL. If it is not set when creating here, garbled characters will appear. The scopes of the two are different. When creating a form, this charset will be applied to this table. It represents the character set of MySQL resume database data table set to utf-8

The above article about the difference between MyISAM and InnoDB is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.