Turning to Zhihu, you will see:

  • MySQL, SQL, ORM, Sequelize conceptual relationships
  • The use of the Sequelize
  • Sequelize describes the concepts, usage, principles, and differences of a joint table relationship
  • How do I optimize database queries

1 the concept

MySQL

Most people are familiar with MySQL and SQL, after all, it is written in textbooks. MySQL is a typical relational database.

To put it simply, a relational database is a database composed of many two – dimensional column and column tables that can be connected to each other.

There are two points :: : two-dimensional row and row tables: (embodied in individual table data structures), :: interlinked: (embodied in table relationships and library engine characteristics).

Compared with NoSQL like MongoDB, 2d row and column tables have the following advantages:

  • Reliable data structure: The data type and even size of each column is defined by the model, and everything is stable
  • Easy to understand: Every piece of data is “flat” because free nesting reads too far south

SQL

Since the relational database is a unified standard, as long as everyone follows the standard, the rest of the things, such as access to the database, can be unified.

That’s what SQL is all about. It’s just a string that can be interpreted as a command to do anything on a relational database, but thinking about relational design has simplified complex things, and SQL does just a few things:

  • Define libraries, the tables themselves, and the relationships between tables
  • Add, delete, change and check in a list
  • With the help of inter-table relationships, data in multiple tables can be accessed jointly at once
  • Access some basic operation functions

Put it all together, and in theory you can do whatever you want with “just one SQL sentence.” Learn more: At my level of knowledge, I can only read Rookie Tutorial

ORM and Sequlize

But SQL is not enough, because the string itself does not have any constraints, you might want to look up a data, the result is a mistake, delete the library, have to run away. And writing a bunch of strings in code is ugly.

So there’s something called ORM, what is ORM? Object-relational mapping :: is a bit convoluted.

Basically, database tables are mapped to language objects; It then exposes a bunch of methods for library lookup, and the ORM takes care of converting method calls into SQL; Because records in a table are in key-value form, the query result is usually an object, facilitating data use. This improves the convenience and stability of database access.

Method SQL business logic <------> ORM <------> Database data object dataCopy the code

ORM, however, is only one solution. On the right side, any SQL-compliant relational database is supported regardless of database type. On the left, regardless of the language type, each family has a relatively mature implementation scheme, and even adds some language-level optimization support according to the language characteristics.

In nodeJS, “Sequlizejs” is probably the most outstanding ORM implementation. Rooted in NodeJS, Sequlizejs perfectly supports Promise type calls. Further, you can go async/await and bind business code tightly; If you are on TS, type reminders from the model definition make the call easier.

The official documentation is here: Sequelize V5

2 Basic Usage

Table/model definition

As mentioned earlier, the first step in ORM is to set up the mapping of objects to tables. In Sequlize, this is the case. For example, we associate a station table

const Model = sequlize.define('station', { 
    id: { 
        field: 'id', 
        type: Sequelize.INTEGER, 
        allowNull: false, 
        primaryKey: true, 
        autoIncrement: true, 
    }, 
    store_id: Sequelize.STRING(20), 
    name: Sequelize.STRING(20), 
    type: Sequelize.TINYINT, 
    status: Sequelize.TINYINT, 
    ip: Sequelize.STRING(20), 
    plate_no: Sequelize.STRING(20), 
    related_work_order_id: Sequelize.BIGINT, 
});
Copy the code

As you can see during the definition process, the data type is referenced from the Sequelize static property. These types can override the types in the database, but their names do not correspond. For details, see lib/data-types.js

You can also customize the third parameter of define, which is incorporated into the Define field of the Sequlize constructor to define the behavior associated with the model and the table, such as “automatically update update_AT, create_AT in the table.” In the reference Model | Sequelize options

But model to model, is for ORM use, database tables or to build their own. Create table SQL from client or as follows

CREATE TABLE `station` ( 
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, 
`store_id` varchar(20) NOT NULL DEFAULT '', 
`name` varchar(20) NOT NULL DEFAULT '', 
`type` tinyint(4) NOT NULL DEFAULT '0', 
`status` tinyint(4) NOT NULL DEFAULT '0', 
`ip` varchar(20) NOT NULL DEFAULT '', 
`related_work_order_id` bigint(20) NOT NULL DEFAULT '0', 
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`plate_no` varchar(20) NOT NULL DEFAULT '', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='工位表';
Copy the code

The basis of the CURD

Sequlize objects provide rich apis such as:

  • The findOne, the.findall…
  • The create, upsert…
  • Aggregate, Max…

/lib/model.js~ model Here’s a look at what Sequlize does when we call a basic API. It’s helpful to understand the relationship between ORM and SQL.

One example: findAll

When attributes are not given, Sequelize defaults to taking attributes out of the model, which saves database operation costs and transmission bandwidth compared to Select *.

The simplest is when I implement a

Station.findAll()
Copy the code

The Sequlize SQL conversion looks like this

SELECT 
    `id`, 
    `store_id`, 
    `name`, 
    `type`, 
    `status`, 
    `ip`, 
    `plate_no` 
FROM 
    `station` AS `station`;
Copy the code

We can simply add some conditions:

Station.findAll({ 
    attributes: [ 'ip' ], 
    where: { 
        status: 1, 
    }, 
order: [ 
[ 'name', 'ASC' ], 
], 
limit: 10, 
offset: 5, 
});
Copy the code

SQL (still very clear)

SELECT `ip` FROM `station` AS `station` WHERE `station`.`status` = 1 ORDER BY `station`.`name` ASC LIMIT 5, 10;
Copy the code

Second example: findOrCreate

Some advanced apis :: trigger database transactions ::

Things aren’t usually that simple, like when I tune

Station.findOrCreate({ 
    where: { 
        id: 1, 
    }, 
    defaults: { 
        name: 'haha', 
    }, 
});
Copy the code

As you know, you can’t do that in a single SQL sentence, so Sequlize opens a transaction and does “search > Judge > Add”.

START TRANSACTION; 
SELECT `id`, `store_id`, `name`, `type`, `status`, `ip`, `plate_no` FROM `station` AS `station` WHERE `station`.`id` = 2; 
INSERT INTO `station` (`id`,`name`) VALUES (2,`haha`); 
COMMIT;
Copy the code

3 Query the union table

3.1 Why do you want to combine tables

Station id = Station id = Station id = Station id = Station id = Station id = Station id

Find Station, then use where – station_id – in to find the Car, then write logic to go through the Station and insert the Car one by one. But on the one hand, one more query will increase the consumption of database resources, on the other hand, it also has more processing logic.

So we need to use the “table to table relationship” that “relational database” is good at, to complete the above query and data merge.

3.2 Joint table relationship

In Sequlize, syntable relationships need to be marked in the model associate method, usually in this format:

File.belongsTo(User, {... option});Copy the code

Use include when you look it up

File.findOne({
    include: [{ model: User }],
});
Copy the code

Like the model definition itself, this tag does nothing to the database, but merely establishes relationships between models at the ORM layer. This relationship is transformed into a “JOIN” SQL statement when we invoke a JOIN table query.

The lookup operation is always “include”, but whether or not to tag and which tag method to use determines whether or not to use a linked table query in subsequent queries, and how the SQL of the query and the results of the query are organized.

First clarify a few concepts in a marking behavior

  • Two kinds of model

  • Source model: The model that needs to be marked in relation to other models, that is, the model that performs the joint table query (File above)

  • Target model: the model of the tagged relationship that does not itself gain the ability to query the linked table for this tag (User above)

  • Four associative keys

  • ForeignKey: foreignKey, used to associate external models :: a model with a foreignKey is unique to the associated model ::

  • targetKey

  • sourceKey

  • OtherKey: An alternative when a foreignKey is not enough

Relationships between tables typically include: one-to-one, one-to-many, and many-to-many.

3.3 One-to-one Relationship (belongsTo/hasOne)

【Sequelize】 A one-to-one relationship document

【 myth 】 I have a misconception that “one to one” is like a relationship between husband and wife “two-way unique”, in fact, it is not. Our relationship declaration can only be one-way from the source model, that is to say, the one-to-one relationship is also one-way and only guarantees that “the source model record corresponds to a target model record” and vice versa. Like “son.hasone”, there is no guarantee that “Daddy” has only one son.

One-to-one relationships can be marked in belongsTo or hasOne.

1 belongsTo

The File (source model) has a User (target model) whose creator_id marks itself :: to ::. There may not be only one File for a User, but a File can only be associated with one User.

File.belongsto (User, {foreignKey: 'creator_id', // if you do not define this, it will automatically define "target model name + target model primary key name" namely user_id targetKey: 'id', // Target model's associated key, default primary key, usually omitted}); // Here creator_id is on source model FileCopy the code

2 hasOne

The condition is that the File model has a creator_id marker for the User to which it belongs. On the User side, assuming that a User has only one File, we want to fetch File from User:

User.HasOne(File, {foreignKey: 'creator_id', // If this is not defined, it is automatically defined as "source model name + source model primary key name", i.e. User_id sourceKey: 'id', // associate key of source model, default primary key, usually omitted} // Here creator_id is on target model FileCopy the code

HasOne reverses the foreignKey to the source model, so if targetKey and sourceKey use default values (which is usually the case), the foreignKey location can decide whether it is BelongsTo or hasOne convenient.

3 BelongsTo and HasOne

BelongsTo and HasOne can both define “one-to-one” relationships, with three keys that can theoretically be interchangeable.

In fact, the converted SQL is the same ::LEFT JOIN::

# File.BelongsTo(User)
SELECT `FileClass`.`id`, `user`.`id` AS `user.id`
FROM `file` `FileClass`
    LEFT JOIN `user` ON `FileClass`.`creator_id ` = `user`.`id`
# User.HasOne(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM `user` `UserClass`
    LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `
Copy the code

However, at the level of “concept”, “belonging” and “having one” are two different things. Which model is really “uniquely belonging” to another model, then this model should have a foreignKey.

3.4 hasMany

::hasMany can be understood as “multi-choice version of hasOne”, :: Like hasOne, it is required that :: “target model” is uniquely owned by “source model” ::

As in the above scenario, the File model has a creator_id marker for the User it created. Here we take from User all the files he created.

User.hasmany (File, {foreignKey: 'creator_id', // If this is not defined, it will be automatically defined as "source model name + source model primary key name", i.e. User_id sourceKey: 'id', // associate key of source model, default primary key, usually omitted} // Here creator_id is on target model FileCopy the code

Deep differences from hasOne

In fact, under findAll, SQL is the same as “one to one” : (that is, ORM cannot limit the number of LEFT joins.

# User.HasMany(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM `user` `UserClass`
    LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `
Copy the code

::findOne is different :: if you use hasOne, SQL only needs to give a global LIMIT 1, which means “I only need one source model and one target model joined in”.

# findOne: User.HasOne(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM `user` `UserClass`
    LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `
LIMIT 1;
Copy the code

But if you tag hasMany and use findOne to look it up, you’re saying “One source model, but N target models associated with it.” In this case, if LIMIT 1 is given globally, the query result of target model will be mistakenly killed to 1. Therefore, LIMIT query itself obtains “one source model”, and then LEFT JOIN obtains “N target models associated with it”.

# findOne: User.HasMany(File)
SELECT `UserClass`.`id`, `file`.`id` AS `file.id`
FROM (
    SELECT `UserClass`.`id`,
    FROM `user` `UserClass`
    LIMIT 1
) `UserClass`
    LEFT JOIN `file` ON `UserClass`.`id ` = `file`.`creator_id `
Copy the code

3.5 Many-to-many Relationships (belongsToMany)

Sometimes: “target model” and “source model” are not unique ::. A Group (folder) may have multiple users, and a User may have multiple groups, which directly leads to: “it does not make sense to add a foreignKey to any model”. : :

We need the “intermediate model” to be the matchmaker and maintain the relationship between the “target model” and the “source model”. The intermediate model has two Foreign keys (one replaced by otherKey) that are unique to both the “target model” and the “source model.”

User.BelongsToMany(Group, { through: GroupUser, // foreignKey: 'group_id', // if this is not defined, it is automatically defined as' target model name + target model primary key name ', i.e. User_id otherKey: 'user_id',}Copy the code

Select * from INNER JOIN Group; select * from LEFT JOIN Group

# findOne(User): User.BelongsToMany(Group)
SELECT `UserClass`.`id`, `group`.`id` AS `group.id`
FROM (
    SELECT `UserClass`.`id`,
    FROM `user` `UserClass`
    LIMIT 1
) `UserClass`
    LEFT JOIN (`group_user` `group->GroupUser`
        INNER JOIN `group` ON `group`.`id` = `group->GroupUser`.`group_id`)
    ON `UserModel`.`id` = `group->GroupUser`.`user_id`;
Copy the code

3.6 several JOIN

  • Left outer JOIN (equivalent to left join, because the default is OUTER) : merges and returns the rows appearing on the left as the left table behavior; If there is no record associated with the right side, the row is returned with the field added to the right side empty
  • Right join: opposite to left, the right join behaves as the table on the right
  • Inner join: Returns only when the left and right sides match values in join, which is equivalent to taking the intersection
  • Full JOIN: Returns a match on either side, equivalent to taking the union

In the include, if required: true is configured, the SQL changes from the LEFT JOIN to the INNER JOIN, eliminating rows without associated records

4. Optimization of database query

The aforementioned stuff is only meant to “work.” In fact, business query scenarios are likely to be complex, and dbAs will come knocking at your door if you write casually.

4.1 Slow Query, full table scan, and index

In the database world, people often refer to “slow queries,” which are queries that take longer than a specified time. The harm of slow query is that not only the request time of this query becomes longer, but also occupy system resources for a long time, affecting other queries or simply hanging the database.

The most common culprit for “slow queries” is a “full table scan,” in which the database engine searches the entire table until it finds the record. Imagine if you had hundreds of millions of pieces of data, and the data you were looking at happened to be a little bit further down, when would that be? (complexity is O(n)) So how not to “full table scan”?

For example, when you look up a record with a primary key, there is no full table scan.

File.findByPk(123);
Copy the code

MySQL assigns an index to primary key columns by default.

:: What’s the power of index? MySQL creates a btree for this column: (Implementations vary from database to database, but btree is dominant). To find “Station with ID 318”, you just need to go down from the root node, something like this:

3xx --> 31x --> 318
Copy the code

4.2 Index other columns

So what if I look at the normal column? Indexes can also be used to improve query efficiency.

File.findOne({ 
    where: { 
        name: 'station1' 
    } 
})
Copy the code

You can also manually add an index to this column:

create index index_name on file(name);
Copy the code

Instead of looking up the data record directly, the btree is based on the primary key ID. Now the process for looking up a record with the name station1 is similar to this:

Start --> name: sta... --> name: statio --> name: stati1 --> get the id of STATION1:816 --> ID: 8xx --> ID: 81x --> ID: 816 --> get the data of 816Copy the code

If the path is too long, you can also create an “overwrite index” for frequently queried columns, such as File name and author:

create index index_name_and_address on file(name, author);
Copy the code

If I only check author based on name:

File.findOne({ 
    where: { 
        name: 'station1' 
    }, 
    attributes: ['author'] 
})
Copy the code

Since address is already in the index, there is no need to access the source data:

Start --> name: sta... --> name: statio --> name: station1 --> get address: XXX for station1Copy the code

The more indexes, the better?

However, more indexes are not always better. Although indexes improve the efficiency of query, they sacrifice the efficiency of insert and delete. Now you have to change the index. What’s more, the index is a balanced tree, and many scenarios require adjustments to the entire tree. Why are primary keys incremented by default? I guess also to reduce the cost of tree operations when inserting data.)

So we generally consider adding indexes to columns that are commonly used for “where” or “order”.

4.3 Query Statement Optimization

Adding an index to a commonly used column can improve query efficiency by making the query go through “btree” rather than “full table scan”. But instead of using select *, use attributes to pick only the columns you want:

where: { 
    attributes: ['id', 'name'] 
}
Copy the code

However, not all queries go to “btree”. Poor SQL can still trigger full table scan, resulting in slow queries, which should be avoided.

When you place a column, MySQL only uses indexes for the following operators: <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE.

Sequelize:

Sequelize.Op.gt|gte|lt|lte|eq|between|in ...
Copy the code

For example, try not to use “not in” when you can use “in”

/ / bad status: {[Op notIn] : [3, 4, 5, 6],}, / / good status: {[make] in: [1, 2],},Copy the code

Specific online search “avoid full table scan” a lot, not to expand.

5 concludes

  • MySQL operates through SQL, and ORM abstracts operations based on business programming languages, eventually converting to SQL. Sequelize is a type of ORM on node
  • This section describes the Sequelize model establishment and query syntax
  • This paper discusses the concept, usage, principle and difference of these associations through four kinds of tag associations
  • Indexes are important for database optimization, but they should also be avoided in statements