[MySQL] Wanted Backend Onboarding Challenge February class content review

After taking the Wanted Backend Onboarding Challenge February class, I briefly summarized the database and MySQL-related information that I wanted to remember. The content at the bottom is a summary of the course content + contents found while studying.

database principles

• Integrity, Safety, and Scalability

Database type

• relational (MySQL, Oracle)
• key-value (Redis, Dynamo DB)
• graph (Neo4j, OrientDB)
• document (MongoDB)

Row-Oriented vs. Column-Oriented

Name	CountryCode	District	Population
Kabul	AFG	Kabol	1780000
Qandahar	AFG	Qandahar	237500

Row-Oriented literally refers to a row-based database that is stored in row order. Performance is good when deleting or adding rows.

Kabul

AFG

Kabol

1780000

Qandahar

AFG

Qandahar

237500

Column-Oriented is a column-based database that stores specific fields in order, which is advantageous when searching for specific field data sets.

Kabul

Qandahar

AFG

AFG

Kabol

Qandahar

1780000

237500

Which database to choose?

Choose a database considering consistency, availability, and distributed processing. (CAP Theorem)

RDBMS vs. NoSQL

The biggest difference between RDBMS and NoSQL is that the data structures stored are different. RDBMS is composed of a table structure that ensures consistency according to the schema. NoSQL is a non-relational database, and although there are various semantic interpretations of NoSQL, it is mainly interpreted as Not Only SQL. It has more diverse data storage methods than RDBMS and provides a specialized mechanism for retrieval.

Transaction Tr ansaction

Transactions are used when you want to prevent data mismatch. Here, data mismatch refers to a case where, when there is a change in data in memory, the data value is different during the process of matching the value in the database.

MySQL storage engine

In MySQL, InnoDB is a default value that is selected without separate settings, and the use of transactions and the method of reading and writing data differ depending on the storage engine.

Command that can be used when you want to see how the table is created

SHOW CREATE TABLE

You can use this command to check the SQL of how this table was created. Here, the city table in the world database is data originally provided by MySQL.

Database Lock vs. Isolation Level

Database locks and transaction isolation levels are both used for concurrency control. Here, concurrency refers to a method of operating multiple threads on a single core that appear to run simultaneously.

Database Lock

First, database locking is a mechanism. It prevents conflicts and maintains data consistency by locking the DBMS so that only one access can use the resource at a time.

MySQL includes global locks, table locks, named locks, metadata locks, and record locks.

A global lock is a lock that affects the entire server and all queries except SELECT are locked, while a table lock only applies to specific tables. A named lock is a lock on a specific string and is not often used unless it is a transaction with complex requirements. A metadata lock is a lock that occurs when storing metadata. Types of metadata include table definitions and index information. At this time, other transactions are prevented from modifying or accessing metadata. A record lock is a lock placed on a row or record of an individual table and is automatically acquired or released depending on the isolation level and as needed by the MySQL server.

Types of locks can be divided into shared locks (Shared Lock / Read Lock) and exclusive locks (Exclusive Lock / Write Lock) depending on how the lock is implemented.

The locking method in InnoDB is separated as follows.

Isolation Level

When there are multiple transactions, this refers to determining whether data changed or viewed by other transactions and resources in use can be viewed.

2023.02.20 - [Database] - [MSSQL] 트랜잭션 격리수준(TRANSACTION ISOLATION LEVEL)

Improving MySQL Efficiency

There are methods such as index utilization, data redundancy improvement, partitioning, cache, and other query optimization.

Which index should I use?

Using indexes helps speed up query searches by finding specific values ​​or defining a range of table values. It is mainly created as an index using pk or a specific column.

When setting up an index, it is best to avoid columns with high update frequency because they can actually slow down the search speed.

And the size of the index key value needs to be adjusted appropriately. A larger size has the advantage of being able to obtain more information that limits search results, but has the disadvantage of requiring more memory and lowering performance.