Database
Basics
Update Order of Cache
- Redis
- redis，mysql读写一致；
- redis当掉怎么保持数据一致；
Index
Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
- How to speed up: Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.
- index存在哪里:Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees.
- B-tree is a tree data structure that keeps data sorted and allows searches, insertions, and deletions in logarithmic amortized time. Unlike self-balancing binary search trees, it is optimized for systems that read and write large blocks of data. It is most commonly used in database and file systems.
- Type of Index: Primary
- 查看SQL执行计划
- 有十万条数据, 写SQL语句查询其中某字段较大值的几条数据
- 子查询与关联查询的区别
ACID Transaction Properties
- Atomicity: A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
- Consistency: The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
- Isolation: Every transaction should operate as if it is the only transaction in the system.
- Durability: Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.
Database Lock
Database lock tells a transaction, if the data item in questions is currently being used by other transactions.
Shared Lock: When a shared lock is applied on data item, other transactions can only read the item, but can't write into it.
Exclusive Lock: When an exclusive lock is applied on data item, other transactions can't read or write into the data item.
Types of SQL's statements
DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.DML – Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. The Select statement is considered as a limited version of the DML, since it can't change the data in the database. But it can perform operations on data retrieved from the DBMS, before the results are returned to the calling function.DCL – Data Control Language
DCL is used to control the visibility of data like granting database access and set privileges to create tables, etc. Example - Grant, Revoke access permission to the user to access data in the database.
Identity
Identity (or AutoNumber) is a column that automatically generates numeric values.
View
View: The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.
Materialized view: Materialized views are also a view but are disk based. Materialized views get updates on specific duration, base upon the interval specified in the query definition. We can index materialized view.
Pro
- Views don't store data in a physical location.
- The view can be used to hide some of the columns from the table.
- Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.
Con
- When a table is dropped, associated view become irrelevant.
- Since the view is created when a query requesting data from view is triggered, its a bit slow.
- When views are created for large tables, it occupies more memory.