• Mysql 中遇到的坑

    Mysql 中遇到的坑

    文章目录

    1. 二狗，你建表的时候会默认值是不是经常设置Null

    卧槽，你怎么知道的，Null多简单啊，不传值设置Null多方便啊，而且不占用什么空间，代码也不用填写，也不用判断了。

    二狗，你知道你这样的随行所欲会发生哪些Bug么？下面就给你好好的说说Null会产生哪些情况

    1.1 Null的长度并不是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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    -- 不要使用 NULL 字段
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;

-- 初始化一些数据
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
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    1.2 Null参与的查询

    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    由此得出结论 判断是否为null值只能使用is null或is not null语句

    1.3 Null对索引的影响

    5.7 版本的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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    上面两个从key（idx_two）可以看出来 只有是is null 的情况下 才可以使用索引

    **8.0 对此做了优化 ** 只截取不等于的情况

    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    唯一性索引失效的场景，索然three 字段创建了唯一性索引，但是可以插入多条为null的情况，此时索引是失效的

    1.4 Null 参与的计算

    null参与的任何计算返回的结果都是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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    1.5 Null 参与的聚合

    对字段为null 的进行聚合是不会计算在内的

    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    1.6 Null参与排序

    正序的排在前面，逆序的排在后面，这个是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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    二狗 ： 原来null有这个多条条框框啊，看样子以后要注意他的使用了

    附件mysql5.7 安装
    #dokcer pull 镜像名:版本号
docker pull mysql:5.7

# docker run -p 对外暴露的端口:mysql运行端口 --name 容器名称 -e MYSQL_ROOT_PASSWORD=mysql连接密码 -d 镜像名:版本号
# -p 对外暴露的端口:mysql运行端口		
# --name 容器名称	//镜像首次启动时会自动创建容器,需要给容器命名
# -e MYSQL_ROOT_PASSWORD=mysql连接密码		//mysql镜像容器创建时必须设置连接密码,否则无法创建容器
# -d 后台运行
docker run -p 3315:3306 --name mysql5.7 \
-e MYSQL_ROOT_PASSWORD=asd123456 \
-d mysql:5.7


# docker logs --tail=行数 容器名称
docker logs --tail=100 mysql5.7

# 先进入容器
docker exec -it mysql5.7 bash

mysql -uroot -p

# 授权外网访问
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'test123' WITH GRANT OPTION;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    2. 二狗，你创建表的时候字段类型有没有随意设置的

    这个肯定有啊，想对接第三方的，对接慢，又不能影响开发，基本都先考虑srting 类型的

    2.1 选择列的数据类型，从主键开始

    1. mysql创建表的时候不指定主键，但是一定要指定一个主键

      如果没有制定主键的情况下，就会以是否存在非空整形的唯一索引作为主键，如果在没有的情况下，InnoDB会自动添加隐式主键

    2. 主键不具有任何的业务含义，只是一个唯一的自增整数值

    2.2 选择合适的数据类型以及恰当的范围

    mysql中定义了四类数据类型，且有不同的取值范围

    1. 字符串 char 、 varchar 、 【tinytext、text、mediumtext、longtext】
    2. 日期/时间 date 、time 、 datetime 、timestamp
    3. 数值 tinyint、int、bigint、float、double、decimal
    4. 二进制 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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    枚举类型的选择

    枚举类型的值都是从“允许值列表”中选择，且这个列表是在创建表结构时定义好的

    1. 存储数字，数据类型更加紧凑
    2. 允许提前定义，mysql可以检查数据正确性
    枚举值索引
    NULLNULL
    空字符串0
    male1
    female2

    数据验证

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    --插入一条正确的数据(枚举值都是自己定义的)
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)

-- 插入一条错误的数据, 会执行失败
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

--查看枚举值对应的索引
mysql> select name, gender + 0, grade from suitable_data_type;
+-------+------------+-------+
| name  | gender + 0 | grade |
+-------+------------+-------+
| qinyi |          1 | 1     |
+-------+------------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    枚举类型的特性/需要注意的事项

    1. 当在Enum列上使用SUM()或AVG()等聚合函数时，因为这些函数的参数必须是一个数字，所以Mysql会自动使用它们的所引值作为参数
    2. 因为Enum类型存储的是枚举值的内部索引，所以Enum值根据索引号进行排序
    3. 如果将数字存储到Enum中，则将该数字视为可能值得索引，并且存储的值是具有该索引的枚举成员
    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)

-- 此时将字面量的值翻译成了索引 
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' 为字符串，在允许值范围内
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' 对应的索引值范围刚好是 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     |
+----+-------+--------+-------+

-- 不再允许范围内
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)

-- 转换成字符
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     |
+----+-------+--------+-------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100

    2.3 数据类型的选择与使用上的技巧与建议

    1. 使用存储所需要的最小数据类型
    2. 选择简单的数据类型
    3. 存储小数直接选择decimal
    4. 尽量避免使用text和blob

    3. 二狗，表的索引你都怎么使用的

    这个么，看情况啊，具体的反正就是字段出现的频率啥的吧，其他还给老师了。

    额，你丫的，这个不行啊，下面就和你好好说说索引的使用

    1. 字符串类型在查询是没有使用引号，不会使用索引
    2. where 条件左边的属性列参与了函数或者数学运算不会使用索引
    3. 联合索引最左前缀不匹配，不会使用索引

    3.1 索引加的不好的情况

    验证

    -- 第一类
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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    -- 查看表的索引
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: 


-- 没有字符串没有使用索引
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)

-- 有字符串使用索引
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 条件左边的字段参与了函数或者数学运算
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
-- 联合索引的前缀使用问题(虽然优化器会重排 where 顺序, 但是, 查询条件最好还是按照定义的联合索引的顺序, 而不是每次顺序都不一样, 这样也会让查询缓存失效, 因为查询语句不一样了)        
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
        

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171

    3.2索引加的不好

    1. 不再使用的索引没有及时删除： 空间浪费、插入删除更新性能受影响、Mysql维护索引也需要消耗资源
    2. 索引选择性太低，索引列的意义不大 索引选择性 = 不重复的所引值/表记录数
    3. 列值过长，可以选择部分前缀作为索引（区分度高的情况下），而不是整列家索引

    数据准备

    -- 第二类
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');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    验证

    -- 索引选择性(没有必要为索引选择性较低的列创建索引)
mysql> select count(distinct(career))/count(*) from correct_use_index_2;
+----------------------------------+
| count(distinct(career))/count(*) |
+----------------------------------+
|                           0.2000 |
+----------------------------------+

-- 想要通过 name 去查询记录, 可以考虑创建 first_name 索引, 或 first_name、last_name 联合索引 --> 看一看索引选择性
select * from correct_use_index_2 where first_name = '' and last_name = '';

-- 索引度比较低，选择联合索引，选区 last_name  部分字符组合
mysql> select count(distinct(first_name))/count(*) from correct_use_index_2;
+--------------------------------------+
| count(distinct(first_name))/count(*) |
+--------------------------------------+
|                               0.2000 |
+--------------------------------------+


-- first_name, last_name 创建联合索引占据的空间会过大, 需要考虑兼顾长度和选择性
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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    建议 ：

    1. 表记录较少，全表扫描效率更高
    2. 存在联合索引的情况下，在去对前缀部分加索引（已经覆盖了单列或者多列索引）是没有任何意义的
    3. 一张表建立的索引过多，应该根据业务需求分析创建，太多浪费空间，影响额外的查询效率

    4. 二狗，mysql的锁知道哪些

    这个我知道，有乐观锁对不对，乐观锁是通过类似版本号来的，悲观锁就是要等待执行完

    4.1 数据库锁的分类

    1. 按照锁数据的力度（等级） 分析

      行级锁 表级锁

    2. 按照数据的锁定方式区分

    ​ 乐观锁 悲观所 （排他锁 共享锁）

    5. 二狗，sql的慢查询是怎么定位的

    啊，这个，我都是用explain定位的

    5.1 怎样定位慢查询，可以根据以下参数来定位

    1. slow_query_log: 标记慢查询日志是否开启的参数，默认是OFF
    2. slow_query_log_file : 标记存放慢查询日志文件的完整路径
    3. log_query_time : 控制慢查询的时间的阈值
    4. log_queries_not_using_indexes : 标识是否记录未使用索引的查询，默认是关闭的

    5.2 使用工具mysqldumpslow 工具解读慢查询日志

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

    参数说明

    -s 按照何种方式排序 记录次数 c 时间 t 查询时间 l 返回记录数r

    -t TOP N

    -g 正则匹配模式，大小写不敏感

    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                             **查询中每个select子句的类型** 
        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
1 row in set, 1 warning (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    5.34优化建议

    1. select 太多数据行、数据列
    2. 没有定义合适的索引
    3. 没有定义索引dd额顺序查询
    4. 定义了索引，但是mysql没有选择
    5. 复杂查询
  • 相关阅读:
    springboot @Validated验证
    Codeforces Round 928 (Div. 4) (A-E)
    接口请求断言
    QT实战项目1——无边框窗口拖拽和阴影
    基于Python开发的飞机大战小游戏彩色版(源码+可执行程序exe文件+程序配置说明书+程序使用说明书)
    [系统安全] malloc的底层原理—ptmalloc堆概述
    Cybersecurity Requirements for Medical Devices
    椭球面的切平面
    WMTS地图服务每一层级分辨率
    ARM系统控制和管理接口System Control and Management Interface
  • 原文地址：https://blog.csdn.net/hyzsuccess/article/details/127938503