MySQL索引及执行计划

最新推荐文章于 2021-04-19 12:07:12 发布
最新推荐文章于 2021-04-19 12:07:12 发布
阅读量213 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yengboy/article/details/107446186
版权

  1. 索引应用

    1.1. 准备表

     mysql> source /root/t100w.sql

    1.2.设置用户权限

     mysql> grant all on *.* to root@'10.0.0.%' identified by '123';

    1.3压测

     shell> mysqlslap --defaults-file=/etc/my.cnf --concurrency=50 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=1000 -uroot -p123 -h10.0.0.51  -verbose
 
 参数：	
 --concurrency=50  ：  模拟同时100会话连接
 --create-schema='test' ： 操作的库是谁
 --query="select * from test.t100w where k2='780P'"  ：做了什么操作
 --number-of-queries=1000 ： 一共做了多少次查询

 mysqlslap: [Warning] Using a password on the command line interface can be insecure.
 Benchmark
 		Running for engine rbose
 		Average number of seconds to run all queries: 352.097 seconds
 		Minimum number of seconds to run all queries: 352.097 seconds
 		Maximum number of seconds to run all queries: 352.097 seconds
 		Number of clients running queries: 50
 		Average number of queries per client: 20

  2. 查询表的索引

     desc t100w;  
 -----
 Key 
 -----
 PK     --> 主键（聚簇索引）     
 MUL    --> 辅助索引   
 UK     --> 唯一索引  

 mysql> show index from t100w;

  3. 创建索引
    3.1 单列辅助索引

     select * from test.t100w where k2='780P'

 优化方式： 
 语法：
 alter table 表名 add index 索引名(列名);	
 alter table t100w add index idx_k2(k2);

    3.2 联合索引创建

     mysql> alter table t100w add index idx_k1_num(k1,num);

    3.3 前缀索引创建

     判断前缀长度多少合适：
 select count(distinct(left(name,5)))  from city ;
 select count(distinct name)  from city ;
 创建前缀索引
 mysql> alter table city add index idx_n(name(5));

  4. 删除索引

     alter table city drop index idx_n;

  5. 执行计划查看和分析
    5.1 什么是执行计划?
    优化器优化后的“执行方案”。

    5.2 作用 ？

     a. 语句执行之前，通过执行计划，防患于未然。
 b. 对于有性能问题的语句，进行分析。得出优化方案。

    5.3 获取SQL的执行计划 。

     mysql> desc select * from test.t100w where num=279106  and k2='VWtu';
 	+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
 	| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref         | rows | filtered | Extra |
 	+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
 	|  1 | SIMPLE      | t100w | NULL       | ref  | ix_k2,idx,idx1 | idx1 | 22      | const,const |    1 |   100.00 | NULL  |
 	+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+

    5.4 执行计划介绍

     table          ： 操作的表
 type           ： 查询索引的类型（ALL、index、range、ref 、eq_ref、const(system)）
 possible_keys  :  可能会走的。
 key            ： 最终选择的索引。
 key_len        ： 联合索引覆盖长度。
 rows           ： 此次查询需要扫描的行数(预估值)。
 Extra          ： 额外信息。

    5.5 type 详解
    5.5.1 ALL 全表扫描

    a. 查询条件没有建索引

     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  |     | NULL              |                             |
 	| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 	+-------+-----------+------+-----+-------------------+-----------------------------+
 	5 rows in set (0.00 sec)

 mysql> desc select * from t100w where k2='780P';
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    10.00 | Using where |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	1 row in set, 1 warning (0.00 sec)

    b. 有索引，但查询语句不能走。

     mysql> alter table t100w add index idx(k2);
 mysql> desc select * from t100w where k2 like '%80P';
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    11.11 | Using where |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

 mysql> desc select * from t100w where k2 != '780P';
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	|  1 | SIMPLE      | t100w | NULL       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |


 mysql> desc select * from t100w where k2 not in ('780P');
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
 	|  1 | SIMPLE      | t100w | NULL       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

    5.5.2 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  | 17      | NULL | 986679 |   100.00 | Using index |
 	+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+

    5.5.3 range 索引范围扫描

     mysql> desc select * from city where id<100;
 mysql> desc select  * from city where countrycode like 'CH%';

 mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';
 mysql> desc select  * from world.city where countrycode in ('CHN','USA');

 union all  改写: 

 desc 
 select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA';

 注意： 
 	如果重复值过多的话，可能改写的效果不佳。

 通过压测： 判断改写效果。

 没改写的：
  mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from world.city where countrycode in ('CHN','USA'); " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose
 改写后的：
  mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA'; " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose

    5.5.4 ref : 辅助索引等值查询

     mysql> desc select  * from city where countrycode='CHN';

    5.5.5 eq_ref:

     非驱动表的连接条件是主键或唯一键。是多表连接中性能最好的查询方法。
 where条件：
 mysql> desc select a.name,b.name 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       | range  | CountryCode,idx | idx     | 4       | NULL                |    1 |   100.00 | Using index condition |
 	|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY         | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL                  |
 	+----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+

 左外连接：
 mysql> desc select a.name,b.name from city as a  left join  country as b on a.countrycode=b.code;
 	+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
 	| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra |
 	+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
 	|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                | 4188 |   100.00 | NULL  |
 	|  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)

    5.5.6 const(system) 主键或唯一键等值查询

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

    5.5.7 NULL

     mysql> desc select  * from city where id=1000000;
 	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
 	|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
 	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

    5.6. key_len 说明
    5.6.0 计算方式

     a. 介绍： 
 索引的应用长度 
 b. 作用：
 判断联合索引的覆盖长度。
 idx(a,b,c) 

 c. 如何计算key_len 
 总长度的计算： 
 a+b+c 

 d. 每个索引列占用多长？ 
 每个列key_len,是这个列的《最大》预留长度 。 
 影响因素： 
 1. 数据类型 
 2. not null 
 3. 字符集(字符串类型)

 数字类型： 
    			最大长度         key_len(not null)       key_len(not null)     
 	int           4                 4                        4+1

 	tinyint       1                 1                        1+1 


 字符串类型 (utf8): 
         		最大长度       key_len(not null)     key_len(not null)
 char(10)          30              30                    30+1 
 varchar(10)       30+2            30+2                  30+2+1


 字符串类型 (utf8mb4): 
         		最大长度       key_len(not null)     key_len(not null)
 char(10)          40              40                    40+1 
 varchar(10)       40+2            40+2                  40+2+1
 
 建表：
 create table test (
 id int  not null primary key auto_increment,
 a int not null ,
 b char(10) not null ,
 c char(5) ,
 d varchar(20) not null 
 )engine=innodb charset=utf8mb4;
 
 alter table test add index idx(a,b,c,d);

 4+40+21+82=147

 mysql> desc select * from test where a=1;
 	+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
 	|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | Using index |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
 	1 row in set, 1 warning (0.00 sec)

 mysql> desc select * from test where a=1 and b='aa';
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
 	|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 44      | const,const |    1 |   100.00 | Using index |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
 	1 row in set, 1 warning (0.00 sec)

 mysql> desc select * from test where a=1 and b='aa' and c='aa' ;
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
 	|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 65      | const,const,const |    1 |   100.00 | Using index |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
 	1 row in set, 1 warning (0.00 sec)

 mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' ;
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
 	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                     | rows | filtered | Extra       |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
 	|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 147     | const,const,const,const |    1 |   100.00 | Using index |
 	+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
 	1 row in set, 1 warning (0.00 sec)

    5.6.1 联合索引应用细节

    a. 联合索引全覆盖

     idx(num,k1,k2) 

 mysql> desc select * from t100w  where num=641631  and k1='At'  and k2='rsEF';
 mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2='rsEF';

    b. 部分覆盖

     mysql> desc select * from t100w  where num=641631  and k1='At' ;
 mysql> desc select * from t100w  where  k1='At'  and num=641631 ;
 mysql> desc select * from t100w  where  num=641631  and  k2 like 'rsE%';

    c. 完全不覆盖

     mysql> desc select * from t100w  where k1='At'  and     k2 like 'rsE%';

    d. 在多子句 必须得使用联合索引

     where a   order by  b 
 where a   group by  b   order by   xxx

    5.7 Extra

     using where  :  此次查询中有部分条件是没有走索引的。
 如果出现以上信息，说明where 条件，索引设计问题或者语句有问题。

 using filesort : 出现文件排序，order by 、 group  by 、 distinct ...
响魇
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL索引&&执行计划
qq_63492318的博客
07-06 432
一、什么是索引 二、索引的分类 三、索引的创建使用案例 四、执行计划
t100w.sql进行压力测试100万行数据
11-07
mysql进行压力测试100万行数据，测试数据库的查询能力。
mysql 100万1000万条数据表的生成，t100w.sql导入文件
08-11
【内容】t100w.sql 以及创建的sql语句，自己修改可以生成1000w数据表。 【注意】建表需要先建库，然后在mysql中运行： source /路径/t100w.sql 即可以导入100万条的数据，表结构如下： DROP TABLE IF EXISTS `t100w`; CREATE TABLE `t100w` ( `id` int(11) DEFAULT NULL, `num` int(11) DEFAULT NULL, `k1` char(2) COLLATE utf8mb4_bin DEFAULT NULL, `k2` char(4) COLLATE utf8mb4_bin DEFAULT NULL, `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
MySQL day04 索引及执行计划
果子哥丶的博客
05-17 405
索引的管理命令 建索引原则、 管理命令（ 1.查询表的索引情况 2.建立索引 3.删除索引）、 压力测试体现索引功能、 执行计划type详解
mysql官方world数据库city、country表t100w数据库及表，oldguo老师案例数据库
ty3251778的博客
04-19 1708
mysql官方world数据库city、country表 完整版 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> show tables; +-----------------+ | Tables_in
mysql测试库(100万数据库和世界人口数据库).rar
04-22
内含mysql的world.sql，里面有世界人口统计和t100w 100万条数据数据库。方便大家学习mysql时作为测试和学习的好工具。
mysql 索引与执行计划
10-21
### MySQL 索引与执行计划 #### 一、索引与执行计划 ##### 1.1 索引入门 在深入探讨之前，我们首先需要理解什么是索引以及其重要性。 ###### 1.1.1 索引是什么 索引（Index）在MySQL中是一种帮助数据库高效获取...
mysql索引和执行计划的使用
05-04
MySQL索引的最佳实践和执行计划的使用分析，包括索引的设计原则，SQL语句的优化原则等
MYSQL执行计划及索引最佳实践
10-26
### MySQL执行计划及索引最佳实践 #### 一、MySQL执行计划详解 MySQL执行计划是MySQL处理SQL查询的方式，它提供了关于SQL语句如何被优化和执行的信息。通过理解执行计划，可以有效地优化SQL查询，提高数据库性能。...
mysql 千万数据表 t_order.zip
01-19
千万数据，方便测试，sql调优
mysql 百万级数据测试
07-28
使用命令行数据库将 employees.sql文件导入。 例如：mysql -uroot -proot -Dtest<employees.sql
mysql技术4--索引及执行计划
weixin_44736359的博客
07-28 462
文章目录一.索引的介绍1. 索引的作用2. 索引的分类(算法)3. BTREE索引算法演变4. Btree索引功能上的分类4.1 辅助索引4.2 聚集索引4.3 聚集索引和辅助索引的区别5. 辅助索引细分6. 索引树高度6.1 数据行数较多6.2 字段长度6.3 数据类型7. 索引的命令操作7.1 查询索引7.2 创建索引7.3 删除索引8. 压力测试准备:8.1 未做优化之前测试8.2 索引优化后9.possible_key:可能会用到的索引10.联合索引应用细节11. explain(desc)使用场景
索引覆盖与回表
AF_0228的博客
09-09 5655
懂的越多，不懂的越多 上回书说到：什么情况下，索引下推没办法提高sql查询效率？ 表 info 主键 id 名称 name 值 value 别名 realname 对于info表，我们现在有（name,value）联合索引
Rewrite案列
yengboy的博客
05-12 321
last与break（不能同时使用） last: 匹配成功，表示要停止继续匹配。 请求的是1.HTML，最终的访问结果是a.html 因为：在location{}内部，遇到last，本location{}内后续指令不在执行。 匹配成功后，会重新像Server{}标签发起请求，从头到尾在匹配一遍规则，那个匹配则执行哪个。 break: 匹配成功，表示要停止继续匹配。 请求的是1.HTML，最终的访问结果是2.html 因为：在location {} 内部遇到了break，本location内以及
四层负载均衡
yengboy的博客
05-05 460
优势 1.四层负载均衡仅能转发TCP/IP协议、UDP协议，通常用来转发端口：如 tcp/80 tcp/443 tcp/3306 tcp/22 udp/53 2.四层负载均衡可以解决七层负载均衡高可用性的问题。( 多个七层负载均衡同时提供服务 ) 3.四层负载均衡可以解决七层负载均衡端口数限制问题。（七层负载均衡最多能使用的端口是5w） 4.四层转发效率远比七层代理的效率高的多，但是他只能支持t...
Tomcat
yengboy的博客
05-19 394
jvm的提供（Java需要jvm虚拟机） jre：java运行环境 java runtime environment，包含了jvm。 jdk：java开发环境 会包含java的运行环境 jre。 Tomucat、Nginx的区别 Tomcat是一个web服务，提供动态程序的解析（java），支持静态资源。 企业会nginx+tomcat模型， nginx处理静态资源，tomcat处理动态资源（java），能加速网站的访问和降低访问的延迟。 Tomcat的安装、配置、启动 1.安装jdk [root
MySQL多实例应用
yengboy的博客
07-19 156
1.下载、上传、解压、软连接 （1.）下载 官网地址：https://dev.mysql.com/downloads/ （下载速度很慢） 国内镜像：http://mirrors.sohu.com/mysql/ http://mirrors.163.com/mysql/Downloads/ （2.）上传、解压 cd /data/app/ tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz tar xf mysql-5.7.12-linux-gl
mysql索引及执行计划
最新发布
08-15
MySQL索引是一种数据结构，用于提高数据库查询的速度。它通过创建一个有序的数据结构来存储表中的某一列或多列的值，并通过快速查找和访问来加快查询操作。 执行计划是MySQL查询优化器生成的一个指令序列，用于执行...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值