InnoDB
innodb is a storage engine database management system (DBMS) MySQL and MariaDB
InnoDB release on MySQL 5.5.5 in 2010
its provide ACID-compliant transaction features
what is InnoDB in MySQL
InnoDB has evolved from being a storage subsystem to a general-purpose storage engine for MySQL.
Its combination of high performance and high reliability,
It was made the default storage engine from Version 5.6 onwards.
MySQL can handle large volumes of reads and writes
A DBMS is a tool that allows you to store, index, and retrieve data in a table
ACID:
Atomicity, consistency, isolation, durability is a set of properties of database transaction data validity despite errors, power failures and more in this sequence of database operation that satisfies the ACID
in1983 Andreas Reuther German computer science professor and Theo Harder coined the acronym ACID ,on earlier work by Jim Gary named atomicity, consistency, durability ,but not isolation. Transaction access data using read and write operation
1.atomicity
2.consistency
3.Isolation
4.Durability
ATOMICITY
Atomicity is the one of the ACID Transaction properities For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account
Abort = if a transaction abort, changes made to database but not visible.
commit = if a transaction abort, changes made are visible.
CONSISTENCY
Data is in a consistent state when a transaction starts and when it ends. This means that integrity constraints must be maintained so that the database is consistent before and after the transaction a database tracking a checking account may only allow unique check numbers to exist for each transaction
Auto commit setting
COMMIT statement
ROLLBACK statement
ISOLATION
The isolation aspect of the ACID model mainly involves InnoDB transaction. The intermediate state of a transaction is invisible to other transactions.
for example A teller looking up a balance must be isolated from a concurrent transaction involving a withdrawal from the same account. Only when the withdrawal transaction commits successfully and the teller looks at the balance again will the new balance be reported.
DURABILITY
A system crash or any other failure must not be allowed to lose the results of a transaction or the contents of the database. Durability is often achieved through separate transaction logs that can "re-create" all transactions from some picked point in time (like a backup).
Why InnoDB use
Each InnoDB table arranges the data on the disk in order to optimize queries with a primary key.
InnoDB make access a little slower but also much secure
Data selection is fast but inserting updating take longer
InnoDB is best suited large database
If some of the data is deleted, InnoDB will automatically delete all of the referenced data too.
The InnoDB table structure is saved in FRM files, user data, and indexes in a tablespace that is linked to the database.
Advantage of InnoDB
1. Its DML operation follow the ACID model and transaction have commit, rollback, crash-recovery features to protect user data
2. Faster in write (update, inserts)
3. you can compress tables and associated indexes.
4. InnoDB has been designed for CPU efficiency and maximum performance when processing large data volume.
5. You can monitor the performance detail of storage engine by querying performance Schema table.
6. You can monitor the internal working of storage engine by querying
Information Schema table.
Disadvantages of InnoDB
1. innoDB access little slow
2. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
Comments
Post a Comment