MySQL索引介绍

variab 2020-06-14

MySQL索引介绍
目录

一、MySQL索引介绍

1.1.1 MySQL索引介绍

  1. 什么是MySQL索引？

索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。我们以简单理解为：快速查找排好序的一种数据结构。Mysql索引主要有两种结构：B+Tree引和Hash索引。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。
MySQL索引介绍

  1. 索引的种类（算法）
1、B树索引   
 * B-tree 
 * B+tree
 * B*Tree
2、Hash索引
3、R树
4、Full text
5、GIS

3.辅助索引(S索引)怎么构建B树结构的?

(1). 辅助索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,将所有的键值按顺序落到BTree索引的叶子节点上，均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
(6)叶子节点除了存储键值之外，还存储了相邻叶子节点的指针，另外还会保存原表数据的指针
id  name  age  gender
select  *  from  t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.（聚集索引解决此问题）
  1. 聚集索引(C)怎么构建B树结构的?
1、前提条件
(1)建表时有主键列（ID）,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

2、怎么构建B树结构的?
(1)在建表时,设置了主键列(ID)
(2) 表中进行数据存储，会按照ID列的顺序，有序的存储一行一行的数据到数据页上(这个动作叫做聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点,表的数据页被作为聚集索引的叶子节点
(4) 把叶子节点的主键值生成上层枝节点和根节点
  1. 聚集索引和辅助索引构成区别总结
(1)聚集索引只能有一个,非空唯一,一般时主键
(2)辅助索引,可以有多个,是配合聚集索引使用的
(3)聚集索引叶子节点,就是磁盘的数据行存储的数据页
(4)MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
(5)辅助索引,只会提取索引键值,进行自动排序生成B树结构
  1. 辅助索引的细分
(1)单列的辅助索引
(2)联合多列辅助索引(覆盖索引)
(3)唯一索引

7.关于索引树的高度受什么影响？

(1)数据行多， 分表
(2)索引列字符长度 ，前缀索引
(3)char varchar  ，表设计 
(4)enum 优化索引高度，能用则用。

二、索引的管理

2.1.1 索引的管理

  1. 添加索引
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (3.84 sec)
Records: 0  Duplicates: 0  Warnings: 0
#注意建索引的时候会出现短暂的锁表，所以在创建索引的时候要考虑在不忙的时候创建。

#查看索引
mysql> show index from t100w\G;
*************************** 1. row ***************************
        Table: t100w
   Non_unique: 1
     Key_name: idx_k2
 Seq_in_index: 1
  Column_name: k2
    Collation: A
  Cardinality: 1222
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  | MUL | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
  1. 创建唯一索引
#检查是否有重复项
mysql> select count(distinct(k1)) from t100w;
+---------------------+
| count(distinct(k1)) |
+---------------------+
|                1225 |
+---------------------+
1 row in set (1.02 sec)
#有重复项不能创建唯一索引
mysql> select k1,count(k1) from t100w group by k1 having count(k1)>1 limit 10;
+------+-----------+
| k1   | count(k1) |
+------+-----------+
| 00   |       258 |
| 01   |       268 |
| 02   |       243 |
| 03   |       258 |
| 04   |       299 |
| 05   |       257 |
| 06   |       275 |
| 07   |       244 |
| 08   |       280 |
| 0a   |       562 |
+------+-----------+
10 rows in set (0.77 sec)
mysql> alter table t100w add unique index ide_k1(k1);
ERROR 1062 (23000): Duplicate entry ‘XE‘ for key ‘ide_k1‘
#创建报错

#正确创建
mysql> alter table t100w add unique index ide_id(id);
Query OK, 0 rows affected (2.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  | UNI | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  | MUL | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> show index from t100w;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t100w |          0 | ide_id   |            1 | id          | A         |      997335 |     NULL | NULL   | YES  | BTREE      |         |               |
| t100w |          1 | idx_k2   |            1 | k2          | A         |        1222 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
  1. 创建前缀索引
#前缀索引只能用到字符串类型的数据上
mysql> use world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table city add index idx_name(name(5)); #给name列的前5个字符加索引。从左到右（就是前缀索引）
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name    |            1 | Name        | A         |        3554 |        5 | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
  1. 创建联合索引
mysql> alter table city add index idx_co_po(countrycode,population);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name    |            1 | Name        | A         |        3554 |        5 | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_co_po   |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_co_po   |            2 | Population  | A         |        4052 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
  1. 删除索引
mysql> alter table city drop index idx_co_po;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_name    |            1 | Name        | A         |        3554 |        5 | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
  1. 执行计划
#作用
上线新的查询语句之前，进行提前预估语句的性能在出现性能问题时，找到合理的解决思路

#获取执行计划
第一种方法：
mysql> desc  select * from test.t100w where k2=‘EF12‘;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ref  | idx_k2        | idx_k2 | 17      | const |  553 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#第二种方法
mysql> explain  select * from test.t100w where k2=‘EF12‘;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ref  | idx_k2        | idx_k2 | 17      | const |  553 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#说明
table: t100w           表
type: ref              索引的应用级别
possible_keys: idx_k2  可能会使用到的索引  
key: idx_k2			   实际上使用的索引
key_len: 17 		   联合索引覆盖长度
rows: 553		       查询的行数（越少越好）
Extra: NULL			   额外的信息

2.1.2. 执行计划的分析

1.type索引的应用级别 all

1、ALL :   全表扫描，不走索引 ，全表扫描的原因：没建索引，建了索引没走索引
没建索引

全表查询不走索引
mysql> explain select * from test.t100w;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997335 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

没建索引的列查询不走索引
mysql> desc select * from test.t100w where k1=‘aa‘;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997335 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

不等于的也不走索引,范围查询，不确定值的查询也不走索引（针对辅助索引）。
mysql> desc select * from test.t100w where k2 != ‘aaa‘;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | idx_k2        | NULL | NULL    | NULL | 997335 |    79.44 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

两个%%号的模糊查询也不走索引（针对辅助索引）
mysql> desc select * from test.t100w where k2 like ‘%xt%‘;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997335 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

单个%号走了索引
mysql> desc select * from test.t100w where k2 like ‘xt%‘;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t100w | NULL       | range | idx_k2        | idx_k2 | 17      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
  1. Index ：全索引扫描
不加条件的查询，就是是index（全索引扫描），前提你的查询列是有索引的
mysql> desc select k2 from test.t100w;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | index | NULL          | idx_k2 | 17      | NULL | 997335 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. range ：索引范围扫描
辅助索引 : > < >= <= like ,   in   or（in or 尽量避免出现）  
主键： != 

范围查询（针对主键）
mysql> explain select id from city where id>3000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1079 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id from city where id != 3000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 3173 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id from city where id >= 3000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1080 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from world.city where countrycode like ‘C%‘;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  551 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from world.city where countrycode in (‘CHN‘,‘USA‘); #生产中不建议使用
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

建议改成这样
mysql> desc
    -> select * from world.city where countrycode=‘CHN‘
    -> union all 
    -> select * from world.city where countrycode=‘USA‘;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
|  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
  1. ref : 辅助索引等值查询
mysql> explain select countrycode from world.city where countrycode=‘CHN‘;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. eq_ref ：在多表连接查询是on的条件列是唯一索引或主键
mysql> desc select a.name,b.name ,b.surfacearea 
    -> from city as a 
    -> join country as b 
    -> on a.countrycode=b.code 
    -> where a.population <100;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                | 4188 |    33.33 | Using where |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
  1. const和system ： 主键或唯一键等值查询
mysql> explain select id from city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. Extra: NULL 额外的信息
Extra:using filesort 出现这种情况可能是你设计索引的索引有问题或者查询方式有问题

mysql> explain select * from city where countrycode=‘CHN‘ order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

解决方法：添加联合索引
mysql> alter table city add index idx_co_po(countrycode,population);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from city where countrycode=‘CHN‘ order by population;
+----+-------------+-------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys         | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode,idx_co_po | idx_co_po | 3       | const |  363 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
  1. explain(desc)使用场景（面试题）
题目意思:  我们公司业务慢,请你从数据库的角度分析原因
1、mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1、show processlist;  获取到导致数据库hang的语句
2、explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3、建索引,改语句

（2）一段时间慢(持续性的):
1. 记录慢日志slowlog,分析slowlog
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
variab

variab

相关推荐

Golang操作MySql数据库的完整步骤记录

MySQL是业界常用的关系型数据库，在平时开发中会经常与MySql数据库打交道，所以在接下来将介绍怎么使用Go语言操作MySql数据库。Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。我们常用的数

CoderToy / 0评论 2020-11-16

MySQL主从复制原理以及需要注意的地方

最近在写Mycat专题，由于不少小伙伴最近要出去面试，问我能不能简单写下MySQL的主从复制原理和注意事项，因为在之前的面试中被问到了这些问题。 Master 将数据改变记录到二进制日志中，也就是配置文件 log-bin 指定的文件， 这些记录叫做二进制日

emmm00 / 0评论 2020-11-17

Mysql联表update数据的示例详解

在MySQL中，可以在 UPDATE语句 中使用JOIN子句执行跨表更新。employees表将存储在员工编号，姓名，工作表现和工资的数据。employees 和 merits 表之间以是 performance 字段相关联的。对于 employees 表

王艺强 / 0评论 2020-11-17

MySQL数据类型优化原则

MySQL支持的数据类型很多，选择正确的数据类型对于高性能至关重要。下面几个简单的原则都有助于做出更好的选择。应该尽量使用可以正确储存数据的最小数据类型。如果无法确定哪个数据类型时最好的，就选择你认为不会超过范围的最小类型。比如用MySQ内建的类型而不是使

ribavnu / 0评论 2020-11-16

专业级的MySQL开发设计规范及SQL编写规范

在团队开发过程中为了项目的稳定，代码的高效，管理的便捷制定内部种开发设计规范是必不可少的，命名规范的对象是指数据库SCHEMA、表TABLE、索引INDEX、约束CONSTRAINTS等的命名约定。数据库创建时必须添加默认字符集和校对规则子句。设计应至少满

bianruifeng / 0评论 2020-11-16

Mysql 查询JSON结果的相关函数汇总

计算 JSON 深度，计算方式 {} [] 有一个符号即为一层，符号下有数据增加一层，复杂 JSON 算到最深的一次为止，官方文档说 null 值深度为 0，但是实际效果并非如此，列举几个例子。计算 JSON 最外层或者指定 path 的长度，标量的长度为

wangshuangbao / 0评论 2020-11-13

Mysql 实现字段拼接的三个函数

给运营导出数据时，难免需要对字段进行拼接，如果 Mysql 可以完成的话，就可以少些很多代码。不过如果有字段值为 NULL，则结果为 NULL。上面这种方式如果想要使用分隔符分割，就需要每个字段中间插一个字符串，非常麻烦。concat_ws() 可以一次性

苏康申 / 0评论 2020-11-13

浅谈MySQL中的自增主键用完了怎么办

" (然后，你就可以回去等通知了！我们以无符号整型为例，存储范围为0～4294967295，约43亿！我们先说一下，一旦自增id达到最大值，此时数据继续插入是会报一个主键冲突异常如下所示。因此，表中的真实id必然会出现断续的情况。

vivenwan / 0评论 2020-11-13

MySql索引使用策略分析

(建立索引会占用磁盘空间的索引文件。2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率

moyekongling / 0评论 2020-11-13

mysql 如何动态修改复制过滤器

2、这个rds上有一个本地的ECS只读从库，这个只读从库会实时同步线上的rds数据库中的数据，这个只读从库供业务方查询使用

gloryli / 0评论 2020-11-12

MySQL ddl语句的使用

数据定义语言create、drop、alter语句 。定义对数据库记录的增、删、改操作。定义对数据库、表、字段、用户的访问权限和安全级别。这小节主要了解下数据定义语言DDL。我们用它对数据库、表进行一些管理操作，比如：建库、删库、建表、修改表、删除表、对字

云中舞步 / 0评论 2020-11-12

MySQL中使用binlog时格式该如何选择

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然

要啥自行车一把梭 / 0评论 2020-11-12

mysql 8.0.22 安装配置图文教程

解压后的目录并没有的my.ini文件，没关系可以自行创建在安装根目录下添加的my.ini ，写入基本配置：。# 允许连接失败的次数。初始化MySQL，在安装时，避免权限问题出错我们尽量使用管理员身份运行CMD，否则在安装时会报错，会导致安装失败的情况，如下

aydh / 0评论 2020-11-12

解决Navicat Premium 连接 MySQL 8.0 报错\"1251\"的问题分析

人闲太久，努力一下就以为是在拼命。1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client. My

kuwoyinlehe / 0评论 2020-11-12

MySQL数据操作-DML语句的使用

DML数据操作语言，是指对数据库进行增删改的操作指令，主要有INSERT、UPDATE、DELETE三种，代表插入、更新与删除，这是学习MySQL必要掌握的基本知识。方语法中 [] 中内容可以省略。值的顺序和表中字段顺序须保持一致。

minerk / 0评论 2020-11-12

详解 MySQL中count函数的正确使用方法

当搞清楚count函数的运行原理后，相信上面几个问题的答案就会了然于胸。为了解决上述的问题，我创建了一张 user 表，它有两个字段：主键id和name，后者可以为null，建表语句如下。`id` int NOT NULL AUTO_INCREMENT C

vitasfly / 0评论 2020-11-12

MySQL 基于时间点的快速恢复方案

万幸，这份数据是平台上某些商品的价格，基本上是有限个商品，然后价格值也都是固定的，之前有对这个价格表进行备份，于是给他直接重新导入了一份价格表的数据，这个问题也算是解决了。新建一个实例，全库还原，然后应用备份的binlog，一直去追，追到数据被该坏的时间点

jazywoo在路上 / 0评论 2020-11-11

MySQL外键约束的实例讲解

MySQL的外键约束是用来在两个表之间建立链接的，其中一个表发生变化，另外一个表也发生变化。从这个特点来看，它主要是为了保证表数据的一致性和完整性的。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

敏敏张 / 0评论 2020-11-11

MySQL用truncate命令快速清空一个数据库中的所有表

用文本编辑器把每条truncate语句前后的“|”替换为空字符，方便后面一次复制多条执行。truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；dro

世樹 / 0评论 2020-11-11

修改MySQL8.0 默认的数据目录(快捷操作无配置)

使用场景：我们使用的是阿里云，单独购买了数据盘，MySQL 8.0 数据库默认装在系统盘上，为了考虑安全性和空间问题，我们需要将默认的数据库目录更改到其它位置。操作系统：CentOS 7.6 数据库：MySQL 8.0 查看我本人更多原创文章，请点击

zry / 0评论 2020-11-11

MySQL 8.0.15配置MGR单主多从的方法

MySQL Group Replication字面意思是mysql组复制的意思,但其实他是一个高可用的集群架构,暂时只支持mysql5.7和mysql8.0版本.也是mysql官方基于组复制概念并充分参考MariaDB Galera Cluster和Per

暗夜之城 / 0评论 2020-11-11

Mysql中时间戳转为Date的方法示例

日志系统的表中,时间字段存储的是13位时间戳timestamp而不是日期数据,而在业务中,我们需要通过时间和ip来进行分组查询给定日期的数据.当然你可以选择在业务层先将传入的日期转为时间戳,再去进行查询,但是既然mysql既然可以直接进行转换,那么省去在业

九天银河技术 / 0评论 2020-11-11

阿里云服务器安装Mysql数据库的详细教程

由于在学习过程中需要安装zookeeper，我的虚拟机一直有问题，就够买了阿里云服务器。安装完zookeeper后想着把数据库也安装在服务器上，释放一下电脑的压力，在安装数据库的时候遇到了很多问题，通过查看有些大佬的作品终于安装好了数据库。现在就我遇到的问

URML / 0评论 2020-11-11

MySQL查询重写插件的使用

从MySQL 5.7.6开始，MySQL Server支持查询重写插件，可以在服务器执行之前检查并可能修改服务器接收的语句。插件可以将原始树返回到服务器以进行进一步处理，或者构造不同的树并返回该树。","SELECT DBA_no, n

好记忆也需烂 / 0评论 2020-11-11

MySQL5.5 部署的一个问题

目前公司部署MySQL是通过平台化操作的，周五的时候，平台暂时出了点儿问题，手上有个需求比较着急，就直接手动的部署了一下，由于好长时间没有部署环境了，竟然有些手生，这里把部署的步骤以及遇到的问题记录下来，希望对大家有所帮助。

爱雨轩 / 0评论 2020-11-11

MySQL中的undo日志

我们知道，MySQL中的redo日志记录了事务的行为，在服务器宕机的时候，可以通过重做事务来达到恢复数据的目的，然而，有的时候，事务还有回滚的需求，也就是说，我们需要知道某条在变成当前情况之前的样子，这种情况下，undo日志就派上用场了。也就是说，undo

spurity / 0评论 2020-11-10

详解MySQL alter ignore 语法

今天上班的时候，业务方问了我这样一个问题：我有一个表，需要添加一个唯一的字段，但是目前这个字段存在一些重复值，有没有好的解决办法。于是我详细询问了一下他的需求，最终得知，这个过程中重复的数据只需要保存一条就可以了，可以容忍一部分数据丢失，而重复的字段恰好是

SCNUHB / 0评论 2020-11-10

mysql实现从导出数据的sql文件中只导入指定的一个表

昨天系统自动备份了某一个数据库的全部表数据，名dbAll.sql.gz。今天因发现某一个表tableA的一条数据存在问题，需核对两条记录的变化。需从dbAll中找回tableA的数据，以便比较该记录。法3说明：将src_bake_db.sql.gz压缩文件

bleach00 / 0评论 2020-11-10

从零开始用DataGrip的安装及使用教程

在path加上mysql路径。例如，我的安装路径是C:\Users\skywf\mysql-8.0.19-winx64，那么我就要像下图一样配置环境变量。输入mysqld --initialize-insecure --user=mysql，我这里先配置无

Coder技术文摘 / 0评论 2020-09-29

使用 windows bat 脚本命令一键启动MySQL服务的方法

echo UAC.ShellExecute "%~s0", "", "", "runas", 1 >> "%temp%\getadmin.vbs"

killerover天下 / 0评论 2020-10-22

php7连接MySQL实现简易查询程序的方法

假设我们制作的是分班情况查询程序，将使用PHP7的环境以PDO的方式连接MySQL。通过学号和姓名查询自己所在班级。准备就绪，开始吧，现在！echo "<tr><th>学号</th><th>姓名&l

yifangs / 0评论 2020-10-13

node.js如何操作MySQL数据库

MySQL数据库作为最流行的开源数据库。基本上是每个web开发者必须要掌握的数据库程序之一了。node.js上，最受欢迎的mysql包就是mysql模块。这样就拿到了一个连接。node.js对数据库的curd都在query这个方法里面。connection

huacuilaifa / 0评论 2020-10-29

在 Linux 上安装 MariaDB 或 MySQL

开始在 Linux 系统上使用开源的 SQL 数据库吧。MariaDB和MySQL都是使用 SQL 的开源数据库，并且共享相同的初始代码库。因此，本文同时适用于 MariaDB 和 MySQL。在大多数发行版上，MariaDB 分为服务器包和客户端包。客

Gexrior / 0评论 2020-10-22

MySQL管理-授权（14）

查看关于权限的命令mysql> help show grantsmysql> SHOW GRANTS FOR ‘root‘@‘localhost‘;+-----------------------------------------------

lpfvip00 / 0评论 2020-10-07

MySQL 导入数据时　2006-MySQLserver has gone away

max_allowed_packet是mysql允许最大的数据包，也就是你发送的请求；wait_timeout是等待的最长时间，这个值大家可以自定义，但如果时间太短的话，超时后就会现了MySQL server has gone away #2006错误。打

王艺强 / 0评论 2020-10-06

MySQL运维---半同步复制

　　问题：如果在从库返回了ack之后，还没来得及执行第7步写入磁盘，服务器异常宕机，那就会造成数据丢失。　　 解决：先执行第7步，把数据写入磁盘，再向主库返回ack，主库才执行commit，这个解决方案称为半同步复制。

gsmfan / 0评论 2020-10-05

JDBC连接MySQL

String sql = "select * from users where name=?String id = rs.getString;//1代表数据库中表的列数，id在第一列也可以！！！

Andrea0 / 0评论 2020-09-18

Uber为什么放弃Postgres选择迁移到MySQL？

Uber 的早期架构包含了一个用 Python 开发的单体后端应用程序，这个应用程序使用 Postgres 作为数据存储。从那个时候开始，Uber 的架构已经发生了巨大变化，变成了微服务，并采用新的数据平台模型。具体地说，之前使用 Postgres 的地方

Ida / 0评论 2020-09-16

聊聊MySQL中的参数

在前面一些文章中，经常能看到介绍某某参数的作用，可能有些小伙伴仍搞不清楚 MySQL 参数是啥。我们所说的参数在官方文档中称为 系统变量，不同的变量有着不同的作用。MySQL 服务端维护了许多表示其配置的系统变量，所有变量均有默认值。若会话变量未单独设置，

ltd00 / 0评论 2020-09-12

MySQL中的悲观锁与乐观锁

在关系型数据库中，悲观锁与乐观锁是解决资源并发场景的解决方案，接下来将详细讲解一下这两个并发解决方案的实际使用及优缺点。`quantity` int NOT NULL COMMENT '库存',quantity代表着不同商品oid的库存，接下来OCC及PC

Java学习 / 0评论 2020-09-11

JDBC-idea导入mysql连接java的jar包(mac)的方法

与5.0版本相比:导包方式相同,后面代码中的注册驱动方式不同。下载对应版本的jar包。点击上面链接,到官网下载jar包。将jar包在本地放入单独一个文件夹。这样做的好处是,以后再使用其它jar包时,直接放到这个文件夹就好,不需要再一个一个导入。新建Modu

flyToSkyL / 0评论 2020-09-11

MySQL 选择合适的存储引擎

对于数据库这一块询问比较多的就是在 MySQL 中怎么去选择一种何时当前业务需求的存储引擎，而 MySQL 中支持的存储引擎又有很多种，那么 MySQL 中分别又有那些，怎么优雅的使用呢？在文件系统中，MySQL 将每个数据库保存为数据目录下的一个子目录。

云中舞步 / 0评论 2020-09-11

shell脚本定时备份MySQL数据库数据并保留指定时间

公司用到的MySQL数据库，经常有同事通过一顿骚操作把一些关键的配置数据误删，每次恢复都要花上不少时间，于是写了个shell脚本，结合corntab每天凌晨备份数据库，并保留7天。同样的备份脚本也可用于生产环境MySQL数据库定时备份。上述脚本中指定了要备

tufeiax / 0评论 2020-09-03

Mysql读写分离过期常用解决方案

读写分离的主要目标是分摊主库的压力，由客户端选择后端数据库进行查询。还有种架构就是在MYSQL和客户端之间有一个中间代理层proxy，客户端之连接proxy，由proxy根据请求类型和上下文决定请求的分发路由。客户端需要关注后端细节，连接维护、后端信息维护

要啥自行车一把梭 / 0评论 2020-09-10

Mysql误删数据解决方案及kill语句原理

使用flashback工具闪回，把数据恢复回来。原理是修改binlog的内容，拿回原库重放，需要确保binlog_format=row和binlog_row_imsge=Full. 如果是insert，将binlog event类型是write_rows

xjd0 / 0评论 2020-09-10

Mysql临时表及分区表区别详解

内存表，指的是使用Memory引擎的表，建表语法是create table …这种 表的数据都保存在内存，系统重启的时候会被清空，但是表结构还在。除这两个特性看 上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。如果是使用InnoDB引擎或者MyI

康慧欣 / 0评论 2020-09-10

MySQL 如何修改root用户的密码

mysql> set password for 用户名@localhost = password;关闭正在运行的MySQL服务。打开DOS窗口，转到mysql\bin目录。--skip-grant-tables的意思是启动MySQL服务的时候跳过权限

greatboylc / 0评论 2020-09-10

导致MySQL索引失效的一些常见写法总结

最近一直忙着处理原来老项目遗留的一些SQL优化问题，由于当初表的设计以及字段设计的问题，随着业务的增长，出现了大量的慢SQL，导致MySQL的CPU资源飙升，基于此，给大家简单分享下这些比较使用的易于学习和使用的经验。这次的话简单说下如何防止你的索引失效。

liuweiq / 0评论 2020-09-09

通过实例解析MySql CURRENT_TIMESTAMP函数

`text` varchar DEFAULT '' COMMENT '内容',那么如何设置一个具体的默认时间呢？

adsadadaddadasda / 0评论 2020-09-08

mysql主从复制配置过程

配置完毕后，重启数据库： service mysqld restart. 通过phpmyadmin，添加新用户，并授予REPLICATION SLAVE权限。或者通过root用户登录到mysql，赋予权限：。其中repl是用户名，password是该用户的

疯狂老司机 / 0评论 2020-09-08