Full stack data base (understanding)

Time:2020-10-31

preface

This paper begins to standardize the use of markup syntax “markdown grammar writing”.
In the case of no practical experience, the basic data types should also be kept in mind so that they can be applied quickly.

1. Redis data type

Redis data type
Redis supports five data types:

a. One dimension

  • String — set / get
  • Hash — hmset / hget

Time complexity O (1): one-time completion; hash can store objects (array): hset key_ NAME FIELD_ NAME FIELD_ VALUE;

b. Two dimensions

  • List — lpush / [lpop | lrange |…] order
  • Set — Sadd / [srandmember | members |…] disorder
  • Zset (ordered set) — zadd / zscore | zrange [zrank] ordered, double score

2. Golang derived data type

Go language data type

a. The difference between array and map

The array has the sameA sequence of numbered and fixed length data items of unique type.
Definition, make initialization type, assignment
var variable_ name [SIZE] variable_ Type; auto set size var balance = […] float32 {1000.0, 2.0, 3.4, 7.0, 50.0}
balance[4] = 50.0

  • Definition and assignment of map set

Define and make initialization type
var map_ Variable map [string] string; using the make function: Map_ variable := make(map[key_ data_ type]value_ data_ type)
assignment
map_ Variable [“France”] = Paris “

Array is equivalent to PHP’s index array, while the sequence is complete, and map is equivalent to PHP’s associative array and unordered

b. The difference between arrays and slices

Go language slice is the abstraction of array. The powerful built-in type slicing (“dynamic array”), compared with the array, the length of the slice is not fixed. Elements can be appended, and the capacity of the slice may be increased when appending.The same type is fixed.

  • Slice slice creation, initialization and assignment

The length of VaR type [] identifier is not required. Or use the make() function to create slices: s: = make ([] type, len)
s :=[] int {1,2,3 }

3、MySQL optimization related

Understanding function and stored procedure again in MySQL programmingFor the code, refer to “re learning MySQL database”.
Initial experiment tableCreate a temporary memory table, character utf8mb4

DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_memory` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

–Create a common table to use as a test case to simulate big data; change to the same type

DROP TABLE IF EXISTS `vote_record`;
CREATE TABLE `vote_record` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_ User 'default',
    `vote_ Num ` int (10) unsigned not null default '0' comment 'votes',
    `group_ ID ` int (10) unsigned not null default '0' comment 'user group ID 0 - inactive user 1 - normal user 2 - VIP user 3 - administrator user',
    `Status ` tinyint (2) unsigned not null default '1' comment 'status 1 - normal 2 - deleted',
    `create_ Time ` int (10) unsigned not null default '0000-00-00 00 00:00:00' comment 'creation time',
    PRIMARY KEY (`id`),
    KEY `index_ user_ id` (`user_ ID ') using hash comment' user ID hash index '
)Engine = InnoDB default charset = utf8 comment ='voting record table ';
--alter table vote_ record CHANGE create_ time create_ Time datetime not null default '2000-01-01 00:00:00' comment 'creation time'

a.Create tables - indexes, functions, stored procedurescarry

  • Create a function that generates a random string of length n; check
Delete // -- modify MySQL qualifier: '//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8mb4 //
CREATE FUNCTION `rand_ String '(n int) returns varchar (255) no SQL --! Note here!
BEGIN 
    DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
        SET i = i+1;
    END WHILE;
    RETURN return_str;
END //
--Check select Rand_ string(22)//
  • Creating stored procedures for inserting data
DROP PROCEDURE IF EXISTS `add_vote_record_memory` //
CREATE PROCEDURE `add_vote_record_memory`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE vote_num INT DEFAULT 0;
    DECLARE group_id INT DEFAULT 0;
    DECLARE status TINYINT DEFAULT 1;
    WHILE i < n DO
        SET vote_num = FLOOR(1 + RAND() * 10000);
        SET group_id = FLOOR(0 + RAND()*3);
        SET status = FLOOR(1 + RAND()*2);
        INSERT INTO `vote_record_memory` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW());
        SET i = i + 1;
    END WHILE;
END //

CREATE PROCEDURE `copy_data_from_tmp`(IN n INT)
BEGIN
    DECLARE lastid INT DEFAULT 0;
    SELECT IFNULL(MAX(id), 0) INTO lastid FROM `vote_record`;
    INSERT INTO `vote_record` SELECT * FROM `vote_record_memory` where id > lastid LIMIT n;
END //
Delimiter; -- change back to the default MySQL qualifier: ';'
  • call

–Call stored procedure to generate 10W data (2 min 3.69 sec, second run, 80W 16 min 41.58 sec)
CALL add_vote_record_memory(100000);
–Copy data (0.87 sec second run, 80W 9.49 sec)
CALL copy_data_from_tmp(100000);

Modify the parameters and adjust the data in Table 2 to 100W.

b. Explain usage

The command explain is used to view the execution plan of these SQL statements to see if the SQL statement has used the index and whether the full table scan has been performed.
Explain acts on select, delete, insert, replace, and update statements (MySQL version 8.0.12 +, old only select). Official output format: explain output format:

Column JSON Name Meaning
id select_id The select identifier; if it is a subquery, the sequence number of ID will be incremented, that is, the higher the ID value, the higher the priority, and the earlier it is executed
select_type No, The select type is mainly used to distinguish complex queries such as ordinary query, union query and subquery
table table_name Output row table; may be derived from table name, derived + ID
partitions partitions Matching partition, mull for non partitioned table
type access_type line type
possible_keys possible_keys Available indexes
key key Actual selected index
key_len key_length The length of the selected key
ref ref Columns are compared to indexes
rows rows Estimate the rows to check, which is an estimate for the InnoDB table and may not always be accurate
filtered filtered The estimated percentage of table rows that will be filtered by table criteria.
Extra No, Additional information

The document “syntax of creating procedure and function”, and the italicized remarks refer to official and [MySQL advanced] (I) explain usage and result analysis “.

c. Official document: optimizing select statement

Mysql8.0.12 (installed through LNMP) performance test:

select * from vote_record;
-- 1000000 rows in set (0.51 sec)
select * from vote_record where vote_num > 1000;
-- 899936 rows in set (0.50 sec)

There are many optimization contents, which are listed separately in the next part.

d. Transaction atomicity and lock

Four characteristics of database transaction and isolation level of transaction
Deep understanding of distributed transactions

Recommended Today

Elasticsearch database | Elasticsearch-7.5.0 application construction practice

Elasticsearch is a distributed and restful search and data analysis engine. ——Elastic stack official website Building elasticsearch’s “things”“ One day, on the deck of the golden Melly, Weibo told Saab that it was necessary to use elasticsearch database in the following project development process, which was mainly used for real-time query of data collected by […]