Pitfalls encountered in Mysql

1. Erdog, when you create a table, do you always set Null as the default value?

Damn, how do you know, how simple Null is, how convenient it is to set Null without passing a value, and it doesn’t take up much space, and you don’t need to fill in the code, and you don’t need to judge.

Ergou, do you know what bugs will happen if you do whatever you want? Let me give you a good talk about what happens to Null

1.1 The length of Null is not 0

mysql> select length(''), length(null), length(0),length('0');
+------------+--------------+-----------+-------------+
| length('') | length(null) | length(0) | length('0') |
+------------+--------------+-----------+-------------+
|          0 |         NULL |         1 |           1 |
+------------+--------------+-----------+-------------+
1 row in set (0.00 sec)

-- Do not use NULL field
CREATE TABLE `mysql_escape`.`do_not_use_null` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `one` varchar(10) NOT NULL,
    `two` varchar(20) DEFAULT NULL,
    `three` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_one` (`one`),
    KEY `idx_two` (`two`),
    UNIQUE KEY `idx_three` (`three`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- initialize some data
INSERT INTO `mysql_escape`.`do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (1, '', 'a2', 'a3');
INSERT INTO `mysql_escape`.`do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (2, 'b1', NULL, 'b3');
INSERT INTO `mysql_escape`.`do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (3, 'c1', 'c2', NULL);
INSERT INTO `mysql_escape`.`do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (4, 'c3', 'c4', NULL);

1.2 Queries where Null participates

mysql> select * from do_not_use_null where two = null;
Empty set (0.00 sec)

mysql> select * from do_not_use_null where two is null;
+----+-----+------+-------+
| id | one | two  | three |
+----+-----+------+-------+
|  2 | b1  | NULL | b3    |
+----+-----+------+-------+
1 row in set (0.00 sec)

From this, it can be concluded that only the is null or is not null statement can be used to judge whether it is a null value

1.3 The impact of Null on the index

version 5.7 of mysql

mysql> desc select * from do_not_use_null where two != 'xxx'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: do_not_use_null
   partitions: NULL
         type: ALL
possible_keys: idx_two
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from do_not_use_null where two is not null\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: do_not_use_null
   partitions: NULL
         type: range
possible_keys: idx_two
          key: idx_two
      key_len: 63
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

The above two can be seen from the key (idx_two) that the index can only be used if it is null

**8.0 has optimized this ** Only intercept the situation that is not equal

mysql> desc select * from do_not_use_null where two != 'xxx'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: do_not_use_null
   partitions: NULL
         type: range
possible_keys: idx_two
          key: idx_two
      key_len: 63
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

In the scenario where the unique index is invalid, the three field creates a unique index, but you can insert multiple null s, and the index is invalid at this time

1.4 Calculation involving Null

Any computation in which null participates returns null

mysql> select 1+Null;
+--------+
| 1+Null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select concat('abc', Null);
+---------------------+
| concat('abc', Null) |
+---------------------+
| NULL                |
+---------------------+
1 row in set (0.00 sec)

1.5 Aggregation with Null Participation

Aggregating fields that are null will not be counted

mysql> select count(*) from do_not_use_null;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select count(three) from do_not_use_null;
+--------------+
| count(three) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

1.6 Null participates in sorting

The positive order is in the front, and the reverse order is in the back. This is a convention of mysql

mysql> select * from do_not_use_null order by two asc;
+----+-----+------+-------+
| id | one | two  | three |
+----+-----+------+-------+
|  2 | b1  | NULL | b3    |
|  1 |     | a2   | a3    |
|  3 | c1  | c2   | NULL  |
|  4 | c3  | c4   | NULL  |
+----+-----+------+-------+
4 rows in set (0.00 sec)

mysql> select * from do_not_use_null order by two desc;
+----+-----+------+-------+
| id | one | two  | three |
+----+-----+------+-------+
|  4 | c3  | c4   | NULL  |
|  3 | c1  | c2   | NULL  |
|  1 |     | a2   | a3    |
|  2 | b1  | NULL | b3    |
+----+-----+------+-------+
4 rows in set (0.00 sec)

Ergou: It turns out that null has multiple rules and regulations. It seems that we should pay attention to its use in the future.

Attachment mysql5.7 installation

#dokcer pull image name: version number
docker pull mysql:5.7

# docker run -p Externally exposed port: mysql running port --name container name -e MYSQL_ROOT_PASSWORD=mysql connection password -d image name: version number
# -p Port exposed to the outside world: mysql running port		
# --name container name //The container will be created automatically when the image starts for the first time, and the container needs to be named
# -e MYSQL_ROOT_PASSWORD=mysql connection password //The connection password must be set when the mysql image container is created, otherwise the container cannot be created
# -d run in the background
docker run -p 3315:3306 --name mysql5.7 \
-e MYSQL_ROOT_PASSWORD=asd123456 \
-d mysql:5.7


# docker logs --tail=lines container name
docker logs --tail=100 mysql5.7

# first into the container
docker exec -it mysql5.7 bash

mysql -uroot -p

# Authorize extranet access
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'test123' WITH GRANT OPTION;

2. Erdog, did you set the field type at will when you created the table?

There must be this. If you want to connect with a third party, the connection is slow and cannot affect the development. Basically, you should first consider the srting type.

2.1 Select the data type of the column, starting from the primary key

1. mysql does not specify a primary key when creating a table, but must specify a primary key

   If no primary key is specified, it will use whether there is a unique index with non-empty plastic as the primary key. If there is no, InnoDB will automatically add an implicit primary key

2. The primary key does not have any business meaning, it is just a unique self-incrementing integer value

2.2 Choose the right data type and the right range

Four types of data types are defined in mysql, and have different value ranges

1. String char , varchar , [tinytext, text, mediumtext, longtext]
2. date/time date , time , datetime , timestamp
3. Numeric tinyint, int, bigint, float, double, decimal
4. Binary tityblob, blob, mediumblob, longblob

mysql> help char
Name: 'CHAR'
Description:
[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE
collation_name]

A fixed-length string that is always right-padded with spaces to the
specified length when stored. M represents the column length in
characters. The range of M is 0 to 255. If M is omitted, the length is
1.

*Note*:

Trailing spaces are removed when CHAR values are retrieved unless the
PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

URL: https://dev.mysql.com/doc/refman/5.7/en/string-type-syntax.html

Choice of enumeration type

The values ​​of the enumeration type are all selected from the "allowed value list", and this list is defined when the table structure is created

1. Store numbers, the data type is more compact
2. Allows to be defined in advance, mysql can check data correctness

data verification

CREATE TABLE `mysql_escape`.`suitable_data_type` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) NOT NULL,
    `gender` ENUM('male', 'female') NOT NULL,
    `grade` ENUM('0', '1', '2') NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--Insert a correct data(Enumeration values ​​are self-defined)
mysql> INSERT INTO `mysql_escape`.`suitable_data_type`(`id`, `name`, `gender`, `grade`) VALUES (1, 'qinyi', 'male', '1');
Query OK, 1 row affected (0.01 sec)

-- Insert a wrong data, will fail
mysql> INSERT INTO `mysql_escape`.`suitable_data_type`(`id`, `name`, `gender`, `grade`) VALUES (2, 'qinyi', 'male', '9');
ERROR 1265 (01000): Data truncated for column 'grade' at row 1

--View the index corresponding to the enumeration value
mysql> select name, gender + 0, grade from suitable_data_type;
+-------+------------+-------+
| name  | gender + 0 | grade |
+-------+------------+-------+
| qinyi |          1 | 1     |
+-------+------------+-------+

Features/Considerations of Enumerated Types

1. When using aggregate functions such as SUM() or AVG() on Enum columns, because the parameters of these functions must be a number, Mysql will automatically use their quoted values ​​​​as parameters
2. Because the Enum type stores the internal index of the enumeration value, the Enum value is sorted according to the index number
3. If a number is stored into an Enum, that number is treated as potentially worth an index, and the stored value is an enum member with that index

mysql> INSERT INTO `mysql_escape`.`suitable_data_type`(`id`, `name`, `gender`, `grade`) VALUES (3, 'qinyi', 'male', 2);
Query OK, 1 row affected (0.01 sec)

-- At this point, the literal value is translated into an index 
mysql> select id,name,gender,grade from suitable_data_type;
+----+-------+--------+-------+
| id | name  | gender | grade |
+----+-------+--------+-------+
|  1 | qinyi | male   | 1     |
|  3 | qinyi | male   | 1     |
+----+-------+--------+-------+

mysql> INSERT INTO `mysql_escape`.`suitable_data_type`(`id`, `name`, `gender`, `grade`) VALUES (4, 'qinyi', 'male', '2');
\Query OK, 1 row affected (0.00 sec)

-- '2' as a string, in the range of allowed values
mysql> select id,name,gender,grade from suitable_data_type;
+----+-------+--------+-------+
| id | name  | gender | grade |
+----+-------+--------+-------+
|  1 | qinyi | male   | 1     |
|  3 | qinyi | male   | 1     |
|  4 | qinyi | male   | 2     |
+----+-------+--------+-------+

mysql>  INSERT INTO `mysql_escape`.`suitable_data_type`(`id`, `name`, `gender`, `grade`) VALUES (5, 'qinyi', 'male', '3');
Query OK, 1 row affected (0.01 sec)

-- '3' The corresponding index value range is exactly 2
mysql> select id,name,gender,grade from suitable_data_type;
+----+-------+--------+-------+
| id | name  | gender | grade |
+----+-------+--------+-------+
|  1 | qinyi | male   | 1     |
|  3 | qinyi | male   | 1     |
|  4 | qinyi | male   | 2     |
|  5 | qinyi | male   | 2     |
+----+-------+--------+-------+

-- no longer allowed
mysql> INSERT INTO `mysql_escape`.`suitable_data_type`(`id`, `name`, `gender`, `grade`) VALUES (6, 'qinyi', 'male', '4');
ERROR 1265 (01000): Data truncated for column 'grade' at row 1


mysql> select sum(gender) from suitable_data_type;
+-------------+
| sum(gender) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(gender) from suitable_data_type;
+-------------+
| avg(gender) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)



mysql> INSERT INTO `mysql_escape`.`suitable_data_type`(`id`, `name`, `gender`, `grade`) VALUES (7, 'abc', 'female', '0');
Query OK, 1 row affected (0.01 sec)

mysql> select * from suitable_data_type order by gender;
+----+-------+--------+-------+
| id | name  | gender | grade |
+----+-------+--------+-------+
|  1 | qinyi | male   | 1     |
|  3 | qinyi | male   | 1     |
|  4 | qinyi | male   | 2     |
|  5 | qinyi | male   | 2     |
|  7 | abc   | female | 0     |
+----+-------+--------+-------+
5 rows in set (0.00 sec)

-- convert to character
mysql> select * from suitable_data_type order by cast(gender as char);
+----+-------+--------+-------+
| id | name  | gender | grade |
+----+-------+--------+-------+
|  7 | abc   | female | 0     |
|  1 | qinyi | male   | 1     |
|  3 | qinyi | male   | 1     |
|  4 | qinyi | male   | 2     |
|  5 | qinyi | male   | 2     |
+----+-------+--------+-------+
5 rows in set (0.00 sec)

mysql> select * from suitable_data_type order by concat(gender);
+----+-------+--------+-------+
| id | name  | gender | grade |
+----+-------+--------+-------+
|  7 | abc   | female | 0     |
|  1 | qinyi | male   | 1     |
|  3 | qinyi | male   | 1     |
|  4 | qinyi | male   | 2     |
|  5 | qinyi | male   | 2     |
+----+-------+--------+-------+

2.3 Tips and suggestions on the selection and use of data types

1. use the minimum data type required for storage
2. Choose a simple data type
3. To store decimals, choose decimal directly
4. Try to avoid using text and blob

3. Ergou, how do you use the index of the table?

Well, it depends on the situation. The specifics are the frequency of the fields anyway, and the rest is returned to the teacher.

Uh, damn it, this won’t work, let me talk to you about the use of indexes

1. The string type does not use quotation marks in the query and will not use the index
2. The attribute column on the left of the where condition participates in functions or mathematical operations and will not use indexes
3. The leftmost prefix of the joint index does not match, and the index will not be used

3.1 The situation where the index is not added well

verify

-- the first sort
CREATE TABLE `mysql_escape`.`correct_use_index` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) NOT NULL,
    `age` int(11) NOT NULL,
    `phone`  varchar(64) NOT NULL,
    `email`  varchar(128) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_phone` (`phone`),
    KEY `idx_name_phone_email` (`name`, `phone`, `email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- View the index of the table
mysql> show index from correct_use_index \G;
*************************** 1. row ***************************
        Table: correct_use_index
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: correct_use_index
   Non_unique: 1
     Key_name: idx_phone
 Seq_in_index: 1
  Column_name: phone
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: correct_use_index
   Non_unique: 1
     Key_name: idx_name_phone_email
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: correct_use_index
   Non_unique: 1
     Key_name: idx_name_phone_email
 Seq_in_index: 2
  Column_name: phone
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: correct_use_index
   Non_unique: 1
     Key_name: idx_name_phone_email
 Seq_in_index: 3
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 


-- no string no index used
mysql> explain select * from correct_use_index where phone = 17012345678\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: correct_use_index
   partitions: NULL
         type: ALL
possible_keys: idx_phone
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

-- There are strings using the index
mysql> explain select * from correct_use_index where phone = '17012345678'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: correct_use_index
   partitions: NULL
         type: ref
possible_keys: idx_phone
          key: idx_phone
      key_len: 194
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
        
        
-- where The field to the left of the condition participates in a function or mathematical operation
mysql> explain select * from correct_use_index where concat(name, '-qinyi') = 'imooc-qinyi'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: correct_use_index
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

mysql> explain select * from correct_use_index where name = 'imooc'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: correct_use_index
   partitions: NULL
         type: ref
possible_keys: idx_name_phone_email
          key: idx_name_phone_email
      key_len: 98
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
-- Problems with the use of prefixes for joint indexes(Although the optimizer will rearrange where order, but, The query conditions are preferably in the order of the defined joint index, rather than a different order each time, This will also invalidate the query cache, Because the query statement is different)        
mysql> drop index idx_phone ON correct_use_index;

mysql> explain select * from correct_use_index where name = 'qinyi' and phone = '10086' and email = 'qinyi@imooc.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: correct_use_index
   partitions: NULL
         type: ref
possible_keys: idx_name_phone_email
          key: idx_name_phone_email
      key_len: 678
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
        
mysql> explain select * from correct_use_index where phone = '10086' and email = 'qinyi@imooc.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: correct_use_index
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
        

3.2 The index is not added well

1. Indexes that are no longer used are not deleted in time: space waste, insertion, deletion, and update performance are affected, and Mysql also consumes resources to maintain indexes
2. The index selectivity is too low, and the index column is of little significance Index selectivity = unique quoted value/number of table records
3. If the column value is too long, you can choose part of the prefix as the index (in the case of high discrimination) instead of the entire column home index

data preparation

-- second category
CREATE TABLE `mysql_escape`.`correct_use_index_2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `career` varchar(32) NOT NULL,
    `first_name`  varchar(16) NOT NULL,
    `last_name`  varchar(16) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mysql_escape`.`correct_use_index_2`(`id`, `career`, `first_name`, `last_name`) VALUES (1, 'engineer', 'qinyi', 'abcdefg');
INSERT INTO `mysql_escape`.`correct_use_index_2`(`id`, `career`, `first_name`, `last_name`) VALUES (2, 'engineer', 'qinyi', 'abxyzbdf');
INSERT INTO `mysql_escape`.`correct_use_index_2`(`id`, `career`, `first_name`, `last_name`) VALUES (3, 'engineer', 'qinyi', 'aerefgdgfd');
INSERT INTO `mysql_escape`.`correct_use_index_2`(`id`, `career`, `first_name`, `last_name`) VALUES (4, 'engineer', 'qinyi', 'abpoijhyg');
INSERT INTO `mysql_escape`.`correct_use_index_2`(`id`, `career`, `first_name`, `last_name`) VALUES (5, 'engineer', 'qinyi', 'acqasdwqer');

verify

-- index selectivity(There is no need to create indexes for columns with low index selectivity)
mysql> select count(distinct(career))/count(*) from correct_use_index_2;
+----------------------------------+
| count(distinct(career))/count(*) |
+----------------------------------+
|                           0.2000 |
+----------------------------------+

-- want to pass name to query records, may consider creating first_name index, or first_name,last_name joint index --> Take a look at index selectivity
select * from correct_use_index_2 where first_name = '' and last_name = '';

-- The index degree is relatively low, choose the joint index, the selection area last_name  partial character combination
mysql> select count(distinct(first_name))/count(*) from correct_use_index_2;
+--------------------------------------+
| count(distinct(first_name))/count(*) |
+--------------------------------------+
|                               0.2000 |
+--------------------------------------+


-- first_name, last_name The space occupied by creating a joint index will be too large, A balance of length and selectivity needs to be considered
select count(distinct(concat(first_name, last_name)))/count(*) from correct_use_index_2;
select count(distinct(concat(first_name, left(last_name, 1))))/count(*) from correct_use_index_2;
select count(distinct(concat(first_name, left(last_name, 2))))/count(*) from correct_use_index_2;
mysql> select count(distinct(concat(first_name, left(last_name, 3))))/count(*) from correct_use_index_2;
+------------------------------------------------------------------+
| count(distinct(concat(first_name, left(last_name, 3))))/count(*) |
+------------------------------------------------------------------+
|                                                           1.0000 |
+------------------------------------------------------------------+


ALTER TABLE correct_use_index_2 ADD INDEX `idx_first_last_name_3` (first_name, last_name(3));
show index from correct_use_index_2;

Suggest :

1. There are fewer table records, and the full table scan is more efficient
2. In the case of a joint index, it is meaningless to add an index to the prefix part (which has covered a single column or a multi-column index)
3. There are too many indexes created for a table, which should be created according to the analysis of business requirements. Too much space is wasted, which will affect the efficiency of additional queries

4. Ergou, what do mysql locks know?

I know this. There are optimistic locks, right? Optimistic locks come through similar version numbers. Pessimistic locks just wait for execution to complete.

4.1 Classification of database locks

1. According to the strength (level) of lock data analysis

   Row-level locks Table-level locks

2. Distinguish according to the locking method of the data

​ Optimistic lock pessimistic lock (exclusive lock shared lock)

5. Ergou, how to locate the slow query of sql

Ah, this, I use explain to locate

5.1 How to locate slow queries can be located according to the following parameters

1. slow_query_log: A parameter to mark whether the slow query log is enabled, the default is OFF
2. slow_query_log_file : mark the full path of the slow query log file
3. log_query_time : Threshold to control the time of slow queries
4. log_queries_not_using_indexes : Identifies whether to record queries that do not use indexes, the default is off

5.2 Use the tool mysqldumpslow to interpret slow query logs

mysqldumpslow -s -t 10 -g "group by" /tmp/slow_query.log

Parameter Description

-s by which method to sort the number of records c time t query time l the number of returned records r

-t TOP N

-g Regular matching pattern, case insensitive

5.3 explain / desc

mysql> explain select * from correct_use_index where phone = '10086' and email = 'qinyi@imooc.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE                             **query for each select clause type** 
        table: correct_use_index
   partitions: NULL
         type: ALL
possible_keys: NULL                               **Indexes that may be used, the number of bytes used in the index**
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1                                  **Estimate the number of rows to read**
     filtered: 100.00                             **The ratio of the number of records that satisfy the query**
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

5.34 Optimization Suggestions

1. select Too many data rows, data columns
2. No suitable index defined
3. There is no index defined dd amount sequential query
4. Indexes are defined, but mysql has no options
5. complex query