[MySQL] Column Collection, from Basic Concepts to Tuning

catalogs

catalogs

1. Basic concepts and SQL

2.SQL Tuning

3. Optimize the index

4. Some engineering issues

---

1. Basic concepts and SQL

Article Link: MySQL Basic Concepts and SQL__BugMan’s Blog – Blogs This section will talk about basic MySQL concepts and SQL operations, and will cover the following topics:

1. What is MySQL
2. Relational, non-relational database differences
3. MySQL Architecture
4. Some Basic MySQL Concepts
5. MySQL Data Types
6. SQL for MySQL

1. What is MySQL An open source, lightweight relational database with a wide range of applications in the industry. 2. Relational, non-relational database differences It’s really the difference in the way the data is organized; there are strict formatting constraints on the data in relational databases, but not in non-relational databases. 3. MySQL Architecture The logical architecture of MySQL consists of three tiers; there can be multiple libraries in a MySQL instance, multiple tables inside a library, and multiple fields inside a table. 4. Some Basic Concepts of MySQL The main thing is to figure out, what is a primary key? What is a foreign key? What is an index? 5. MySQL Data Types MySQL has three major types of data types: numeric, string, and date/time. Each of the three major types contains specific subtypes, such as BIGINT, FLOAT, DOUBLE, etc. for numeric values, and CHAR, VARCHAR, etc. for strings. 6.SQL What is important on SQL falls into two main areas:

1. Manipulate tables and libraries, including creating, deleting, restructuring, etc.
2. Manipulate data, including adding, deleting, modifying, and querying data.

2.SQL Tuning

Article Link: MySQL Index Failure_Mysql Compound Index Failure__BugMan’s Blog – Blogs When MySQL in the actual production environment, the amount of data piled up to a certain level after the original query SQL speed will become slower, then you need to the original SQL tuning, so that the speed can be up again. SQL tuning is also a core point of database optimization. the core of the SQL tuning in fact, is to build a reasonable index, with space for time. This section contains:

1. B-tree, B+-tree
2. What happens when indexing fails

1. B-tree, B+-tree B-tree is a common self-balancing tree data structure that is widely used in applications that require efficient insertion, deletion, and lookup operations, such as database systems and file systems. Because MySQL stores data on disk, if there are many levels of lookups, it means that the mechanical actions of seek and head will occur many times, which is undoubtedly time-consuming, so if there are fewer levels of lookups, it will be very efficient. a B-tree is a kind of tree structure that has very few levels, but can store a huge amount of data. The paper will give a detailed description of the data structure of the B-tree, the complete tree building process, the capacity of the stored data, as well as the optimization structure of the B-tree, B+-tree, to give an introduction to the B-tree. 2. Which cases of indexing will be invalid In fact, the root cause of the index will fail is the violation of the data structure of the B-tree, so that accurate matching can not go on. The article will make a detailed description of the following types of common index failure:

• Index not used
• Violation of the left prefix principle
• Range Query Broken Index
• like needs to be contextualized
• More than half of the outcome data

3. Optimize the index

Article Link: Explaining MySQL Covered Indexes, Index Push Down__BugMan’s Blog – Blogs SQL tuning, just built the right index, but sometimes the indexing mechanism itself also needs to be optimized, this part will be around how to optimize the indexing mechanism to talk about, including:

1. Coverage Index
2. index downward thrust

1. Coverage index MySQL inherently comes with the “table lookup” problem, covering indexes, in order to avoid the “table lookup”, so as to reduce the query time-consuming a way to use the index. The article will talk in detail about what is the “table query” and how to use the covering index to solve the table problem. 2. Index push down Index pushdown, a new feature introduced since MySQL 5.6, also aims to reduce the “table query”, thus improving the overall query efficiency. In the article, we will talk about what is the index push down, how to solve the table back problem.

4. Some engineering issues

Article Link: Database how to reasonably generate primary key: UUID, snowflake algorithm_Principal Key Algorithm__BugMan’s Blog – Blogs How to reasonably generate database primary keys in the actual project is a problem. First of all, we seldom use 1,2,3 in real projects …… For example, if I want to synchronize data across databases, or synchronize data across partitions in a distributed system, it is not hard to imagine that 1,2,3 …… It is easy to imagine that this kind of incremental single number is very easy to generate conflicts. The article will introduce some algorithms for generating primary keys, such as UUID, snowflake algorithm and so on.