MySQL Architecture and Engine
•Download as PPTX, PDF•
1 like•2,334 views
MySQL uses different storage engines to store, retrieve and index data. The major storage engines are MyISAM, InnoDB, MEMORY, and ARCHIVE. MyISAM uses table-level locking and supports full-text searching but not transactions. InnoDB supports transactions, row-level locking and foreign keys but with more overhead than MyISAM. MEMORY stores data in memory for very fast access but data is lost on server restart. ARCHIVE is for read-only tables to improve performance and reduce storage requirements.
MySQL Architecture and Engine
- 1. MySQL Architecture & Engines
- 2. Learning Objectives • Understand MySQL Architecture • How MySQL Uses Disk space and Memory • Storage Engines >MySQL Interaction with Storage Engines >Major Storage engines with details below for each engine ◦ Characteristics / Features ◦ Storage format ◦ Transaction Support ◦ Locking ◦ Special Features
- 5. The brains of the MySQL server Component Feature Parsing Responsible for deconstructing the requested SQL statements Optimizing Responsible for finding the optimal execution plan for the query Executing Responsible for executing the optimized path for the SQL command passed through the parser and optimizer Query Cache The query cache is a fast in-memory store to quickly look up the result set of a particular SELECT statement Storage Engines Enables MySQL to use different implementations for storing, retrieving and indexing data
- 6. How MySQL uses Disk and Memory
- 7. How MySQL Uses Disk Space • Data directory • Table and view format files (.frm) • Server log files and status files • Trigger storage • System database (MySQL)
- 8. How MySQL Memory Two different types memory allocation • per-session (allocated for each connection thread) >Session specific >Dynamically allocated and deallocated >Mostly utilized for handling query results >Buffer sizes usually per session • per-instance (allocated once for the entire server) >Allocated only once (per server instance) >Shared by the server processes and all of its threads
- 9. How MySQL Memory • Server allocates memory for the following • Thread caches • Buffers • MEMORY tables • Internal temporary tables • Client specific buffers
- 10. MySQL Engines
- 11. Storage Engines A storage engine is a software module that a database management system uses to create, read, update data from a database • Client sends requests to the server as SQL • Two-tier processing >Upper tier includes SQL parser and optimizer >Lower tier comprises a set of storage engines • SQL tier not dependent on storage engine >Engine setting does not effect processing >Some Exceptions
- 12. MySQL Interaction with Storage Engines
- 13. What makes Storage Engine different • Storage medium • Transactional capabilities • Locking • Backup and recovery • Optimization • Special features >Full-text search >Referential integrity >Spatial data handling
- 15. Engines • View Available Storage Engines >SHOW ENGINES • Setting the Storage Engine >Specify engine using CREATE TABLE >CREATE TABLE t (i INT) ENGINE = InnoDB; • Uses system default if not set >--default-storage-engine >@@storage_engine • Change storage engine using ALTER TABLE >ALTER TABLE t ENGINE = MEMORY;
- 16. The MyISAM Storage Engine The MyISAM storage engine was the default storage engine from MySQL 3.23 until it was replaced by InnoDB in MariaDB and MySQL 5.5.
- 17. MyISAM Row Storage Formats
- 19. The InnoDB Storage Engine
- 23. The MEMORY Storage Engine(1/2)
- 24. The MEMORY Storage Engine(2/2)
- 26. ARCHIVE Storage
- 27. ARCHIVE Retrieving and Archiving
- 29. Choosing Appropriate Storage Engines
- 30. Choosing Appropriate Storage Engines
- 31. Thank You! Your Questions Please