MySQL: What is Store Engine

1. Introduction

    1) Take a movie for example. We have different format of it including mp4, avi, rmvb, flv. And different format occupy different space in hard disk, and resolution differs.

    2) The data in a table is also stored in hard disk. But they also have different method of storing.

        From the prospective of user, the data fetched from DB is the same regardless of the store engine of the DB.

    3) Take a live example: At the exit of subway, we have two bicycle administrators called A and B.

        For A, as long as you hand in 5 cent, then you can save your car there and won't do any validation when you come and fetch your car/bicycle back.

        For B, he will record every feature of you and your car. When you come and fetch your car, he will take a careful validation to ensure you have the right to fetch the car and car is just yours and not others'.

        For A and B, whose bussiness efficiency is better? Obviously A.

        For A and B, whose administration is safer? Obviously B.

    4) Database has different way of saving  and administration. In MySQL, it is called Store Engine.

create table t(
id int
) engine myisam charset utf8;

 

2. What kind of store engine does MySQL offers?

    1) InnoDB ---> Is just like B who is very careful.

    2) Myisam ---> Is just like A who is not careful.

 

3. Comparation between InnoDB and MyISAM (We only focus on the differences whose background painted as gray) But in MySQL 5.5 and above, full-text searching is also supported by InnoDB. 

    Comparison:

    1) InnoDB has transactions while MyISAM does not.

    2) InnoDB has foreign keys and relationship contraints while MyISAM does not.

    3) Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.