day06（索引）

作者: 五月_w | 来源:发表于2019-06-20 21:40 被阅读0次

day06（索引）
自律给我自由—Day006
day06
day06-01-进制装换-基本概念
晨起阅读 Day06：《人生十二法则》01章
Day06：《细节：如何轻松影响他人》
微信小程序开发 day06、day07 - event.cu
《细节：如何轻松影响他人》
读书写故事系列（06）
Day06 - 面向对象

1. 索引

创建索引列
use oldboy;
alter table t100w add index idx_k2(k2);
查索引
desc t100w;
show index from t100w\G
删除索引
alter table city drop index idx_co_po;


MUL 辅助索引
PRI   主键，聚集索引
UNI   唯一索引

mysql> alter table t100w add unique index idx_k1(k1);
ERROR 1062 (23000): Duplicate entry 'E2' for key 'idx_k1'
说明：不能对有重复值的列添加唯一索引



创建前缀索引


mysql> alter table city add index idx_name(name(5));
Query OK, 0 rows affected (0.03 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       |                |
+-------------+----------+------+-----+---------+----------------+


创建联合索引


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> 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      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---

2. 执行计划

2.1、作用


(1)上线新的查询语句之前，进行提前预估语句的性能
(2)在出现性能问题时能找到合理的解决思路

2.2、获取执行计划(两条作用一样）

mysql> desc select * from oldboy.t100w where k2='pdef'\G
mysql> explain select * from oldboy.t100w where k2='pdef'\G
*************************** 1. row ***************************
           id: 1                     ------->语句序号
  select_type: SIMPLE
        table: t100w                 ------->表名
   partitions: NULL
         type: ref                   ------->索引应用的级别
possible_keys: idx_k2                ------->可能会使用到的索引
          key: idx_k2                ------->实际上使用的索引
      key_len: 17                    ------->联合索引的覆盖长度
          ref: const
         rows: 1                     ------->查询的行数（越少越好）
     filtered: 100.00
        Extra: NULL                  ------->额外的信息
1 row in set, 1 warning (0.00 sec)

2.3、执行计划的分析

2.3.1、type 索引的应用级别

ALL ：全表扫描，不走索引

两种情况导致：没建索引
             建了索引不走
例子：
1）mysql> desc select * from 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 | 907758 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

下面几个是针对辅助索引的
2）mysql> desc select * from 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 | 907758 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


3）mysql> desc select * from t100w where k2 !='aaaa';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 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 | 907758 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


4）mysql> desc select * from t100w where k2 like '%ef%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 907758 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Index ：全索引扫描

mysql> desc select k2 from 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 | 907758 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

range : 索引范围扫描

辅助索引： >  <  >=  <=  like  in  or
主键：!=  

（1）mysql> desc select * from t100w where k2 like 'ef%';
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
| 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 | 31404 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+------------------

（2）mysql> desc select * from world.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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+


（3）mysql> desc select * from world.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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

（4）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 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

生产中不建议使用In，用union all 替代
mysql> desc select * from city where countrycode='CHN'  union all select * from 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  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

ref ：辅助索引等值查询

mysql> desc select * 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 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

eq_ref ：在多表连接查询时on的条件列是唯一索引或主键

mysql> 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        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+

const,system ：主键或者唯一键等值查询

mysql> desc select * from world.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 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

extra : NULL 额外的信息

错误：type应用级别是  ref ， 但是出现  using filesort
mysql> desc select * from city where countrycode='CHN' order by population limit 10;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+


解决方法：联合索引
mysql> desc select * from city where countrycode='CHN' order by population limit 10;
+----+-------------+-------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+
| 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 |
+----+-------------+-------+------------+------+-----------------------+-----------+---------+-------+------+----------+-----------------------+

2.4、explain(desc)使用场景（面试题）

题目：我们公司业务慢，请你从数据库的角度分析原因
mysql出现性能问题，我分析有两种原因
1、应急性的慢：突然hang住
      应急情况：数据库hang（卡住，资源耗尽）
      1）处理过程：show processlist；获取到导致数据库hang住的语句
2）explain,分析SQL的执行计划，有没有走索引，

3、索引应用规范

业务
1.产品的功能
2.用户的行为
“热”查询语句--------->较慢-------->slowlog
“热数据”

3.1、建立索引的原则（DBA运维规范）*****

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期

3.2、关于联合索引*****

(1) where a group b order by c 
abc创建索引时必须按顺序index idx(a,b,c)
(2) where a b c
      (2.1)  都是等值，在5.5以后无关索引顺序，把控一个原则唯一值多的列放在最左边
     （2.2）如果有不等值的查询
            select   where a=   and   b>   and  c=      
               索引顺序是acb,语句改写为acb

day06（索引）
1. 索引 2. 执行计划 2.1、作用 2.2、获取执行计划(两条作用一样） 2.3、执行计划的分析 2.3.1...
自律给我自由—Day006
【叶子姑娘的自律100天挑战 Day06】 2019.01.19 Day06/100 【早起】周末也不要赖床。早起...
day06
day06 轮播图布局 java代码适配器 Activity代码
day06-01-进制装换-基本概念
``` //main.c //day06 // //Created by且听风吟on 2017/5/14. //C...
晨起阅读 Day06：《人生十二法则》01章
阅读日记-Day06：2021.01.17 周日阅读时间：05:50-06:29阅读内容：《人生十二法则》01章...
Day06：《细节：如何轻松影响他人》
0922（Day06）书名：《细节：如何轻松影响他人》字数：938字 1、化敌为友几乎每个球迷都有对抗的体会...
微信小程序开发 day06、day07 - event.cu
一、day06 event.currentTarget、data-*、自定义数据属性、组件间通信 1、浏览器事件...
《细节：如何轻松影响他人》
0922（day06）书名：《细节：如何轻松影响他人》正文字数：751字 ✍⒈《不可忽视的环境暗示》凯泽和他...
读书写故事系列（06）
0922（Day06）书名：《细节：如何轻松影响他人》正文字数：711字 1.《从众心理》英国税务海关...
Day06 - 面向对象
Day06的课程要点记录详细教程地址：Day6 - 面向对象学习上节补遗 - Subprocess模块 os.s...

day06（索引）

1. 索引

2. 执行计划

2.1、作用

2.2、获取执行计划(两条作用一样）

2.3、执行计划的分析

2.3.1、type 索引的应用级别

ALL ：全表扫描，不走索引

Index ：全索引扫描

range : 索引范围扫描

ref ：辅助索引等值查询

eq_ref ：在多表连接查询时on的条件列是唯一索引或主键

const,system ：主键或者唯一键等值查询

extra : NULL 额外的信息

2.4、explain(desc)使用场景（面试题）

3、索引应用规范

3.1、建立索引的原则（DBA运维规范）*****

3.2、关于联合索引*****

相关文章

day06（索引）

自律给我自由—Day006

day06

day06-01-进制装换-基本概念

晨起阅读 Day06：《人生十二法则》01章

Day06：《细节：如何轻松影响他人》

微信小程序开发 day06、day07 - event.cu

《细节：如何轻松影响他人》

读书写故事系列（06）

Day06 - 面向对象

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

day06（索引）

1. 索引

2. 执行计划

2.1、作用

2.2、获取执行计划(两条作用一样）

2.3、执行计划的分析

2.3.1、type 索引的应用级别

ALL ：全表扫描，不走索引

Index ：全索引扫描

range : 索引范围扫描

ref ： 辅助索引等值查询

eq_ref ：在多表连接查询时on的条件列是唯一索引或主键

const,system ：主键或者唯一键等值查询

extra : NULL 额外的信息

2.4、explain(desc)使用场景（面试题）

3、索引应用规范

3.1、建立索引的原则（DBA运维规范）*****

3.2、关于联合索引*****

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

ref ：辅助索引等值查询