MySQL索引介绍
目录
一、MySQL索引介绍
1.1.1 MySQL索引介绍
- 什么是MySQL索引？
索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。我们以简单理解为：快速查找排好序的一种数据结构。Mysql索引主要有两种结构：B+Tree引和Hash索引。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。
- 索引的种类（算法）
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.（聚集索引解决此问题）
- 聚集索引(C)怎么构建B树结构的?
1、前提条件 (1)建表时有主键列（ID）,主键列就会自动被作为聚集索引. (2)如果没有主键,会选择唯一键作为聚集索引. (3)聚集索引必须在建表时才有意义,一般是表的无关列(ID) 2、怎么构建B树结构的? (1)在建表时,设置了主键列(ID) (2) 表中进行数据存储，会按照ID列的顺序，有序的存储一行一行的数据到数据页上(这个动作叫做聚集索引组织表) (3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点,表的数据页被作为聚集索引的叶子节点 (4) 把叶子节点的主键值生成上层枝节点和根节点
- 聚集索引和辅助索引构成区别总结
(1)聚集索引只能有一个,非空唯一,一般时主键 (2)辅助索引,可以有多个,是配合聚集索引使用的 (3)聚集索引叶子节点,就是磁盘的数据行存储的数据页 (4)MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据 (5)辅助索引,只会提取索引键值,进行自动排序生成B树结构
- 辅助索引的细分
(1)单列的辅助索引 (2)联合多列辅助索引(覆盖索引) (3)唯一索引
7.关于索引树的高度受什么影响？
(1)数据行多， 分表 (2)索引列字符长度 ，前缀索引 (3)char varchar ，表设计 (4)enum 优化索引高度，能用则用。
二、索引的管理
2.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)- 创建唯一索引
#检查是否有重复项 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)
- 创建前缀索引
#前缀索引只能用到字符串类型的数据上 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)
- 创建联合索引
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)
- 删除索引
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)
- 执行计划
#作用 上线新的查询语句之前，进行提前预估语句的性能在出现性能问题时，找到合理的解决思路 #获取执行计划 第一种方法： 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)
- 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)
- 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)- 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)
- 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)- 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)
- 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)
- explain(desc)使用场景（面试题）
题目意思: 我们公司业务慢,请你从数据库的角度分析原因 1、mysql出现性能问题,我总结有两种情况: （1）应急性的慢：突然夯住 应急情况:数据库hang(卡了,资源耗尽) 处理过程: 1、show processlist; 获取到导致数据库hang的语句 2、explain 分析SQL的执行计划,有没有走索引,索引的类型情况 3、建索引,改语句 （2）一段时间慢(持续性的): 1. 记录慢日志slowlog,分析slowlog 2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况 3. 建索引,改语句
相关推荐
moyekongling 2020-11-13
liuweiq 2020-09-09
ribavnu 2020-11-16
Ida 2020-09-16
要啥自行车一把梭 2020-11-12
vitasfly 2020-11-12
康慧欣 2020-09-10
CoderToy 2020-11-16
emmm00 2020-11-17
王艺强 2020-11-17
bianruifeng 2020-11-16
wangshuangbao 2020-11-13
苏康申 2020-11-13
vivenwan 2020-11-13
云中舞步 2020-11-12
aydh 2020-11-12
kuwoyinlehe 2020-11-12
minerk 2020-11-12