MYSQL学习笔记整理

IccBoY

已于 2022-09-03 23:02:22 修改

阅读量542

点赞数 1

分类专栏：数据库文章标签： mysql

于 2021-02-04 14:07:51 首次发布

本文链接：https://blog.csdn.net/ccboy2009/article/details/113642344

版权

数据库专栏收录该内容

7 篇文章 1 订阅

订阅专栏

衡量指标

常用工具

MySqlSlap 压力测试工具

./mysqlslap -uroot -pxxxxxx --concurrency=1000 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=1000

./mysqlslap -uroot -p2019wsad! --concurrency=1,50,100,200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --create-schema='test11' --debug-info

开启缓存

开启缓存
设置缓存大小

show variables like '%query_cache_type%'
show variables like '%query_cache_size%'

一般不开启数据库的缓存，通过redis等进行缓存

存储引擎

查看mysql提供哪些存储引擎

show engines;

在这里插入图片描述

查看当前的默认引擎

show variables like '%storage_engine%';

在这里插入图片描述

MyISAM

mysql5.5之前的默认的存储引擎
由MYD和MYI组成(.frm 表结构文件)
特征：
- 并发性与锁级别-表级锁
- 支持全文索引
- 支持数据压缩
  - myisampack -b -f testmysam.MYI
适用场景:
- 非事务型应用（数据仓库、报表、日志数据等）
- 只读类应用
- 空间类应用（空间函数，坐标）

Innodb

mysql5.5及以后版本默认存储引擎
innodb_file_per_table
- ON：独立的表空间：tablename.ibd (.frm 表结构文件)
- OFF: 系统表空间： ibdataX
mysql5.6以前默认为系统表空间
系统表空间和独立表空间
- 系统表空间无法兼得的收缩文件大小
- 独立表空间可以通过optimize table 收缩系统文件
- 系统表空间会产生IO瓶颈
- 独立表空间可以同时向多个文件刷新数据
建议：Innodb使用独立表空间
特性：
- Innodb是一种事务性存储引擎
- 完全支持事务的ACID特性
- Redo Log 和 Undo Log
- Innodb支持行级说（并发程度更高）
适用场景

适合于大多数OLTP应用

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	是	是

CSV

组成
- 数据以文本方式存储在文件
  - .csv 文件存储内容
  - .csm 文件存储表的元数据，如表状态和数据量
  - .frm 表结构
特点
- 以csv格式进行数据存储
- 所有列都不能为null
- 不支持索引（不适合大表，不适合在线处理）
- 可以对数据文件直接编辑（保存文本文件内容）

Memory

文件系统存储特点
- 也称HEAP存储引擎，所有数据保存在内存中
支持HASH索引和BTree索引
所有字段都是固定长度varchar(10)=char(10)
不支持Blog和Text等大字段
Memory存储引擎适用表级锁
最大大小由max_heap_table_size参数决定
- show VARIABLES like ‘max_heap_table_size’;

在这里插入图片描述

MySQL临时表与内存表

临时表

MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表在MySQL 3.23版本中添加。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

（1）临时表只在当前连接可见，当这个连接关闭的时候，会自动drop。比如打开mysql 就是一个连接会话。两个不同的连接可以使用相同名字的临时表，两个表之间不存在什么关系，如果临时表的名字和已经存在的磁盘表名字一样，那么临时表会暂时覆盖磁盘表。就是说，你select 查询，只会显示临时表里面的，不会显示磁盘表。

（2）临时表的存储引擎：memor，myisam，merge，innodb，临时表不支持mysql cluster簇。

（3）同一个查询语句，只能用一次临时表，就是说不能将表和自己做连接等。

（4）重命名表，不能用rename 可以用alter table代替。

（5）如果超出了临时表的容量，临时表会转换成磁盘表。

（6）show tables语句不会列出临时表，在information_schema中也不存在临时表信息，show create table可以查看临时表。

内存表

内存表的表结构建立在磁盘里面，数据放在内存里面；
当MySQL断开当前连接后，临时表的表结构和表数据都没了，但内存表的表结构和表数据都存在；
当MySQL服务重启之后，内存表的数据会丢失，但表结构依旧存。

（1）当MySQL服务重启之后，内存表的数据会丢失，表结构依旧存。

（2）多个session，创建表的名字不能一样。

（3）一个session创建会话后，对其他session也是可见的。

（4）data目录下只有tmp_memory.frm，表结构放在磁盘上，数据放在内存中。

（5）可以创建索引，删除索引，支持唯一索引。

（6）不影响主备，主库上插入的数据，备库也可以查到。

（7）show tables 语句可以查看得到表。

（8）内存表不能包含BLOB或者TEXT列。

（9）内存表支持AUTO_INCREMENT列。

在这里插入图片描述

内存表适用场景
- hash索引用于查找或者映射表（邮编和地区的对应表）
- 用于保存数据分析中产生的中间表
- 用于缓存周期性聚合数据的结果表
memory数据易丢失，所以要求数据可以再生

Ferderated

特点
- 提供了访问远程Mysql服务器上表的方法
  - 本地不存储数据，数据全部放到远程服务器上
  - 本地需要保存表结构和远程服务器的连接信息
适用场景
- 偶尔的统计分析及手工查询
如何使用
- 默认禁止，启用需要在启动时增加federated参数

MYSQL锁

表级锁

开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高,并发度最低
行级锁

开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁

开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

仅从锁的角度来说:

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP（On-Line Analytical Processing，联机分析处理）系统
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理OLTP（On-Line Transaction Processing，联机事务处理）系统。
很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适

MyISAM表锁

表计锁有两种模式：

表共享读锁、表独占写锁
在这里插入图片描述

给表加锁
- lock table 表名 read
- lock table 表名 write

查看show status LIKE ‘table_locks_waited’ 表被锁过几次

总结
- 对MylISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
- 对MylSAM表的读操作，不会阻塞当前session对表读，当对表进行修改会报错
- 一个session使用LOCK TABLE命令给表加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误，另外一个session可以查询表中的记录，但更新就会出现锁等待
- 对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作
- 对MyISAM表的写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错

Innodb行锁

行锁：

共享锁又称:读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
排它锁又称:写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

语法：

上共享锁的写法: lock in share mode
例如:select * from表 where条件lock in share mode;
上排它锁的写法: for update
例如: select * from表 where条件for update;

注意:

两个事务不能锁同一个索引。
insert , delete ,update在事务中都会自动默认加上排它锁。
行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

Innodb表锁

和MyISAM差别不大

注意：开启一个新事务的时候会解锁表

问题：

系统运行一段时间，数据量已经很大，这时候系统升级，有张表A需要增加个字段，并发量白天晚上都很大，请问怎么修改表结构？

关键点:
修改表结构会导致表锁，数据量大修改数据很长，导致大量用户阻塞，无法访问！

思路：

首先创建一个和你要执行的alter操作的表一样的空的表结构。
执行我们赋予的表结构的修改，然后copy原表中的数据到新表里面。
在原表上创建一个触发器在数据copy的过程中，将原表的更新数据的操作全部更新到新的表中来。
copy完成之后，用rename table新表代替原表，默认删除原表。

在这里插入图片描述

工具：

pt-online-schema-change

下载安装perl环境http://www.perl.org/get.html
下载percona-toolkit工具集合 https://www.percona.com/doc/percona-toolkit
ppm install DBI依赖

ppm install DBD::mysql安装mysql驱动依赖

perl pt-online-schema-change h=127.0.0.1,u=root,D=mysqldemo,t=product_info --alter "modify product_name varchar(150) not null default '' " --execute

事务

InnoDB支持事务

查看数据库下面是否支持事务(InnoDB支持）?

show engines;
查看mysql当前默认的存储引擎?
show variables like ‘%storage_engine%’;
查看某张表的存储引擎?
show create table表名;
对于表的存储结构的修改?
建立InnoDB表:Create table … engine=InnoDB; Alter table table_name engine=InnoDB;

CREATE TABLE `demo2` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) engine=InnoDB DEFAULT CHARSET=utf8 COMMENT='样例1';


alter table `demo2` engine=MyISAM;

事务的特性

事务应该具有4个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（Atomicity)。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作
一致性(Consistency)。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏
隔离性(Isolation)。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability)。持久性也称永久性（permanence)，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。即使系统崩溃，已经提交的修改数据也不会丢失

隔离性

隔离性要求一个事务对数据库中数据的修改，在未提交完成前对于其他事务是不可见的

mysql默认的事务隔离级别为repeatable-read

select @@tx_isolation;

REPEATABLE-READ

未提交读（READ UNCOMMITED）脏读

已提交读(READ COMMITED）不可重复读

可重复读（REPEATABLE READ)

可串行化（SERIALIZABLE)

set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

事务并发问题

脏读:事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读:事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

在这里插入图片描述

事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行;如果没有索引，更新数据时会锁住整张表
事务隔离级别为串行化时,读写数据都会锁住整张表
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。

事务的语法

show variables like'%autocommit%';

开启事务
- begin
- START TRANSACTION（推荐)
- begin work
事务回滚rollback
事务提交commit
还原点savepoint

begin;
insert into demo1(id, name) values(3, '31');
rollback;


start transaction;
insert into demo1(id, name) values(4, '41');
rollback;

begin work;
insert into demo1(id, name) values(5, '51');
commit;

set autocommit=0;
insert into testdemo values(5,5,5);
savepoint s1;
insert into testdemo values(6,6,6);
savepoint s2;
insert into testdemo values (7,7,7);
savepoint s3;
select * from testdemo;
rollback to savepoint s2;
rollback;

业务设计

逻辑设计

数据库设计的第一大范式
- 数据库表中的所有字段都只具有单一属性
- 单一属性的列是由基本数据类型所构成的
- 设计出来的表都是简单的二维表

id	name-age
1	张三-23

需要该为：

id	name	age
1	张三	23

数据库设计的第二大范式
- 要求表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系
订单表ID（主键）订单时间产品ID
1 2018-12-12 3
1 2018-12-12 4

订单编号和产品ID没有直接关联

产品表ID 产品名称
2 娃娃
3 飞机
4 java入门
改为如下；

订单表ID 订单时间
1 2018-12-12

订单-商品中间表ID 订单ID 产品ID
1 1 3
2 1 4

产品表ID 产品名称
2 娃娃
3 飞机
4 java入门
数据库设计的第三大范式
- 指每一个非非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上相处了非主键对主键的传递依赖
订单表ID（主键）订单时间客户编号客户姓名
1 2018-12-12 1 张三
2 2018-12-12 2 李四

客户编号和订单编号管理关联

客户姓名和订单编号管理关联

客户编号和客户姓名关联

把客户姓名这列删除，只放到客户表中
问题

大量的表关联非常影响查询的性能

完全符合范式化的设计有时并不能得到良好得SQL查询性能
反范式化设计

反范式化是针对范式化而言得，在前面介绍了数据库设计得范式

所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反

允许存在少量得冗余，换句话来说反范式化就是使用空间来换取时间
总结

不能完全按照范式得要求进行设计，需要考虑以后如何使用表

范式化设计优缺点
- 优点：
  - 可以尽量得减少数据冗余
  - 范式化的更新操作比反范式化更快
  - 范式化的表通常比反范式化的表更小
- 缺点：
  - 对于查询需要对多个表进行关联
  - 更难进行索引优化
反范式化设计优缺点
- 优点：
  - 可以减少表的关联
  - 可以更好的进行索引优化

订单表ID（主键）	订单时间	产品ID
1	2018-12-12	3
1	2018-12-12	4

产品表ID	产品名称
2	娃娃
3	飞机
4	java入门

订单表ID	订单时间
1	2018-12-12

订单-商品中间表ID	订单ID	产品ID
1	1	3
2	1	4

产品表ID	产品名称
2	娃娃
3	飞机
4	java入门

订单表ID（主键）	订单时间	客户编号	客户姓名
1	2018-12-12	1	张三
2	2018-12-12	2	李四

缺点：
- 存在数据冗余及数据维护异常
- 对数据的修改需要更多的成本

物理设计

根据所选择的关系型数据库的特点对逻辑模型进行存储结构的设计

定义数据库、表及字段的命名规范
- 数据库、表、字段的命名要遵守可读性原则
  
  使用大小写来格式化的库对象名字以获得良好的可读性例如：使用custAddress而不是custaddress来提高可读性。
- 数据库、表、字段的命名要遵守表意性原则
  
  对象的名字应该能够描述它所表示的对象例如：对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程存储过程应该能够体现存储过程的功能。
- 数据库、表、字段的命名要遵守长名原则
  
  尽可能少使用或者不使用缩写

选择合适的存储引擎

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

为表中的字段选择合适的数据类型
- 当一个列可以选择多种数据类型时
  1. 优先考虑数字类型
  2. 其次是日期、时间类型
  3. 最后是字符类型
  4. 对于相同级别的数据类型，应该优先选择占用空间小的数据类型
- 浮点类型
  
  列类型 存储空间 是否精确类型
  FlOAT 4个字节否
  DOUBLE 8个字节否
  DECIMAL 每4个字节存9个数字，小数点占1个字节是
- 日期类型
  
  timestamp 和时区有关，而datetime无关
  
  datetime类型在5.6中字段长度是5个字节
  
  datetime类型在5.5中字段长度是8个字节
建立数据库结构

列类型	存储空间	是否精确类型
FlOAT	4个字节	否
DOUBLE	8个字节	否
DECIMAL	每4个字节存9个数字，小数点占1个字节	是

慢查询

慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

慢查询配置

slow_query_log 启动停止技术慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

配置了慢查询后，它会记录符合条件的SQL

包括：

查询语句
数据修改语句
已经回滚得SQL

通过下面命令查看下上面的配置：

show VARIABLES like '%slow_query_log%'
show VARIABLES like '%slow_query_log_file%'
show VARIABLES like '%long_query_time%'
show VARIABLES like '%log_queries_not_using_indexes%'
show VARIABLES like 'log_output'
set global long_query_time=0;  ---默认10秒，这里为了演示方便设置为0 
set GLOBAL  slow_query_log = 1; --开启慢查询日志
set global log_output='FILE,TABLE'  --项目开发中日志只能记录在日志文件中，不能记表中

慢查询解读

从慢查询日志里面摘选一条慢查询日志，数据组成如下

在这里插入图片描述

第一行：用户名、用户的IP信息、线程ID号

第二行：执行花费的时间【单位：毫秒】

第三行：执行获得锁的时间

第四行：获得的结果行数

第五行：扫描的数据行数

第六行：这SQL执行的具体时间

第七行：具体的SQL语句

慢查询分析

Mysqldumpslow

mysqldumpslow -s r -t 10 slow-mysql.log
-s order (c,t,l,r,at,al,ar) 
         c:总次数
         t:总时间
         l:锁的时间
         r:总数据行
         at,al,ar  :t,l,r平均数  【例如：at = 总时间/总次数】

  -t  top   指定取前面几天作为结果输出

mysqldumpslow.pl -s t -t 10 xxx-slow.log

pt_query_digest

perl .\pt-query-digest  --explain h=127.0.0.1,u=root,p=root1234% xxx-slow.log

索引与执行计划

BTree、B+Tree、二叉树？

索引

索引（Index）是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构、高效获取数据的数据结构

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引

索引分类

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
非聚簇索引：不是聚簇索引，就是非聚簇索引（show global variables like “%datadir%”;）

基础语法

查看索引

SHOW INDEX FROM table_name

创建索引

CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length));

ALTER TABLE 表名 ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length))

删除索引

DROP INDEX [indexName] ON mytable;

执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

执行计划包含的信息

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | demo1 | index | NULL          | PRIMARY | 4       | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

执行计划-ID

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
ID的数字大小
- id相同：执行顺序由上至下
- id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同：同时存在

执行计划-select_type

Select_type:查询的类型，

要是用于区别:普通查询、联合查询、子查询等的复杂查询

类型	描述
SIMPLE	简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

执行计划-table列

显示这一行的数据是关于哪张表的

执行计划-Type列

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要记忆的 system>const>eq_ref>ref>range>index>ALL

System

表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const

表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> EXPLAIN SELECT *  FROM ( select * from test.demo1 where id=1) t1;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | demo1      | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

mysql> explain select * from test.demo1 d1, test11.t1 t1  where t1.id = d1.id;

+----+-------------+-------+--------+---------------+---------+---------+------------+------+-----------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                 |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-----------------------+
|  1 | SIMPLE      | d1    | ALL    | PRIMARY       | NULL    | NULL    | NULL       |    4 | NULL                  |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 8       | test.d1.id |    1 | Using index condition |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-----------------------+

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

mysql> explain select * from test11.t1 where intcol1 = 41;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | ref  | idx_col1_col2 | idx_col1_col2 | 5       | const |    9 | NULL  |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+

range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

mysql> explain select * from demo1 where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | demo1 | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

index

当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询数据。

mysql> explain select  intcol2, intcol1 from test11.t1;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | idx_col1_col2 | 10      | NULL |  289 | Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+

all

Full Table Scan，将遍历全表以找到匹配的行

mysql> explain select id  from demo1 where title='a';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | demo1 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

执行计划-possible_keys 与key

possible_keys:可能使用的key

key:实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

这里的覆盖索引非常重要，后面会单独的来讲

mysql> explain select intcol1, intcol2 from test11.t1;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | idx_col1_col2 | 10      | NULL |  289 | Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+

其中key和possible_keys都可以出现null的情况

执行计划-key_len

mysql> desc demo1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(10) | YES  | MUL | NULL    |       |
| title  | char(10)    | YES  |     | NULL    |       |
| name1  | varchar(10) | NO   |     | NULL    |       |
| title1 | char(10)    | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> SHOW INDEX FROM demo1;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| demo1 |          0 | PRIMARY    |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| demo1 |          1 | idx_name   |            1 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| demo1 |          1 | idx_name1  |            1 | name1       | A         |           2 | NULL     | NULL   |      | BTREE      |         |               |
| demo1 |          1 | idx_title  |            1 | title       | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| demo1 |          1 | idx_title1 |            1 | title1      | A         |           2 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set

key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

注意：根据底层使用的不同存储引擎，受影响的行数这个指标可能是一个估计值，也可能是一个精确值。即使受影响的行数是一个估计值(例如当使用InnoDB存储引擎管理表存储时),通常情况下这个估计值也足以使优化器做出一个有充分依据的决定。

key_len表示索引使用的字节数
根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。
char和varchar跟字符编码也有密切的联系
latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）

字符类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

在这里插入图片描述

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

以上这个表列出了所有字符类型，但真正建所有的类型常用情况只是CHAR、VARCHAR

字符类型-索引字段为char类型+不可为Null时

demo1 | CREATE TABLE `demo1` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `title` char(10) DEFAULT NULL,
  `name1` varchar(10) NOT NULL,
  `title1` char(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_title` (`name`,`title`) USING BTREE,
  KEY `index_name1_titile1` (`name1`,`title1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='样例1'

mysql> explain select * from demo1 where title1 = 'a';
+----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | demo1 | ref  | idx_title1    | idx_title1 | 30      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------+

title1这一列为char(10),字符集为utf-8占用3个字节key_len=10*3

字符类型-索引字段为char类型+允许为Null时

mysql> explain select * from demo1 where title = 'a';
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | demo1 | ref  | idx_title     | idx_title | 31      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+

title这一列为char(10),字符集为utf-8占用3个字节,外加需要存入一个null值key_len=10*3+1(null) 结果为31

字符类型-索引字段为varchar类型+不可为Null时

mysql> explain select * from demo1 where name1 = 'a';
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | demo1 | ref  | idx_name1     | idx_name1 | 32      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+

Keylen=varchar(n)变长字段+不允许Null=n*(utf8=3,gbk=2,latin1=1)+2

字符类型-索引字段为varchar类型+允许为Null时

mysql> explain select * from demo1 where name = 'a';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | demo1 | ref  | idx_name      | idx_name | 33      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+

Keylen=varchar(n)变长字段+允许Null=n*(utf8=3,gbk=2,latin1=1)+1(NULL)+2

数值类型

在这里插入图片描述

CREATE TABLE `numberKeyLen` (
	`c0` INT (255) NOT NULL,
	`c1` TINYINT (255) NULL DEFAULT NULL,
	`c2` SMALLINT (255) NULL DEFAULT NULL,
	`c3` MEDIUMINT (255) NULL DEFAULT NULL,
	`c4` INT (255) NULL DEFAULT NULL,
	`c5` BIGINT (255) NULL DEFAULT NULL,
	`c6` FLOAT (255, 0) NULL DEFAULT NULL,
	`c7` DOUBLE (255, 0) NULL DEFAULT NULL,
	`c8` INT (255) NOT NULL,
	PRIMARY KEY (`c0`),
	INDEX `index_tinyint` (`c1`) USING BTREE,
	INDEX `index_smallint` (`c2`) USING BTREE,
	INDEX `index_mediumint` (`c3`) USING BTREE,
	INDEX `index_int` (`c4`) USING BTREE,
	INDEX `index_bigint` (`c5`) USING BTREE,
	INDEX `index_float` (`c6`) USING BTREE,
	INDEX `index_double` (`c7`) USING BTREE,
	INDEX `index_intnotnull` (`c8`) USING BTREE
) ENGINE = INNODB DEFAULT CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

mysql> EXPLAIN select * from  numberKeyLen where c1=1;
EXPLAIN select * from  numberKeyLen where c2=1;
EXPLAIN select * from  numberKeyLen where c3=1;
EXPLAIN select * from  numberKeyLen where c4=1;
EXPLAIN select * from  numberKeyLen where c5=1;
EXPLAIN select * from  numberKeyLen where c6=1;
EXPLAIN select * from  numberKeyLen where c7=1;
EXPLAIN select * from  numberKeyLen where c8=1;
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_tinyint | index_tinyint | 2       | const |    1 | NULL  |
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-------+
1 row in set

+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_smallint | index_smallint | 3       | const |    1 | NULL  |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
1 row in set

+----+-------------+--------------+------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_mediumint | index_mediumint | 4       | const |    1 | NULL  |
+----+-------------+--------------+------+-----------------+-----------------+---------+-------+------+-------+
1 row in set

+----+-------------+--------------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_int     | index_int | 5       | const |    1 | NULL  |
+----+-------------+--------------+------+---------------+-----------+---------+-------+------+-------+
1 row in set

+----+-------------+--------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_bigint  | index_bigint | 9       | const |    1 | NULL  |
+----+-------------+--------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set

+----+-------------+--------------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key         | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+-------------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_float   | index_float | 5       | const |    1 | NULL  |
+----+-------------+--------------+------+---------------+-------------+---------+-------+------+-------+
1 row in set

+----+-------------+--------------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key          | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+--------------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_double  | index_double | 9       | const |    1 | NULL  |
+----+-------------+--------------+------+---------------+--------------+---------+-------+------+-------+
1 row in set

+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | numberKeyLen | ref  | index_intnotnull | index_intnotnull | 4       | const |    1 | NULL  |
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
1 row in set

日期和时间

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性,将在后面描述。
在这里插入图片描述

datetime类型在5.6中字段长度是5个字节
datetime类型在5.5中字段长度是8个字节

CREATE TABLE `datatimekeylen` (
	`c1` date NULL DEFAULT NULL,
	`c2` time NULL DEFAULT NULL,
	`c3` YEAR NULL DEFAULT NULL,
	`c4` datetime NULL DEFAULT NULL,
	`c5` TIMESTAMP NULL DEFAULT NULL,
	`c6` TIMESTAMP NOT NULL,
	INDEX `index_date` (`c1`) USING BTREE,
	INDEX `index_time` (`c2`) USING BTREE,
	INDEX `index_year` (`c3`) USING BTREE,
	INDEX `index_datetime` (`c4`) USING BTREE,
	INDEX `index_timestamp` (`c5`) USING BTREE,
	INDEX `index_timestampNotNull` (`c6`) USING BTREE
) ENGINE = INNODB DEFAULT CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

mysql> EXPLAIN SELECT  * from datatimekeylen where c1 = 1;
EXPLAIN SELECT  * from datatimekeylen where c2 = 1;
EXPLAIN SELECT  * from datatimekeylen where c3 = 1;
EXPLAIN SELECT  * from datatimekeylen where c4 = 1;
EXPLAIN SELECT  * from datatimekeylen where c5 = 1;
EXPLAIN SELECT  * from datatimekeylen where c6 = 1;
+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | datatimekeylen | ref  | index_date    | index_date | 4       | const |    0 | Using index condition |
+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
1 row in set

+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | datatimekeylen | ref  | index_time    | index_time | 4       | const |    1 | Using index condition |
+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
1 row in set

+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | datatimekeylen | ref  | index_year    | index_year | 2       | const |    1 | Using index condition |
+----+-------------+----------------+------+---------------+------------+---------+-------+------+-----------------------+
1 row in set

+----+-------------+----------------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+----------------+----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | datatimekeylen | ref  | index_datetime | index_datetime | 6       | const |    0 | Using index condition |
+----+-------------+----------------+------+----------------+----------------+---------+-------+------+-----------------------+
1 row in set

+----+-------------+----------------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys   | key             | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+-----------------+-----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | datatimekeylen | ref  | index_timestamp | index_timestamp | 5       | const |    0 | Using index condition |
+----+-------------+----------------+------+-----------------+-----------------+---------+-------+------+-----------------------+
1 row in set

+----+-------------+----------------+------+------------------------+------------------------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys          | key                    | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+------------------------+------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | datatimekeylen | ref  | index_timestampNotNull | index_timestampNotNull | 4       | const |    0 | Using index condition |
+----+-------------+----------------+------+------------------------+------------------------+---------+-------+------+-----------------------+
1 row in set

总结

字符类型

变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。

而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。

复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

整数/浮点数/时间类型的索引长度

NOT NULL=字段本身的字段长度

NULL=字段本身的字段长度+1(因为需要有是否为空的标记，这个标记需要占用1个字节)

datetime类型在5.6中字段长度是5个字节，datetime类型在5.5中字段长度是8个字节

执行计划-ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

mysql> EXPLAIN select * from demo2 d2, demo3 d3 where d2.name = d3.name and d3.mm = 'a'; 
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref                | rows | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+-----------------------+
|  1 | SIMPLE      | d2    | ALL  | idx_name_mm   | NULL        | NULL    | NULL               |    3 | Using where           |
|  1 | SIMPLE      | d3    | ref  | idx_name_mm   | idx_name_mm | 66      | test.d2.name,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+-----------------------+

执行计划-rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

执行计划-extra

包含不适合在其他列中显示但十分重要的额外信息。

值	描述
Using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary	使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
USING index	是否用了覆盖索引
Using where	表明使用了where过滤
Using join buffer	使用了连接缓存：
Impossible where	where子句的值总是false，不能用来获取任何元组

mysql> explain select * from demo1 where name='a' order by title desc;
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | demo1 | ref  | idx_name      | idx_name | 33      | const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------------+
1 row in set

mysql> explain select name from demo2 d2 where name in ('a','b') group by mm;
 
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                                                     |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | d2    | index | idx_name_mm   | idx_name_mm | 66      | NULL |    3 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------------------------------------------+
1 row in set

mysql> explain select name from demo2 d2 where name in ('a','b') group by name , mm; 
+----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | d2    | index | idx_name_mm   | idx_name_mm | 66      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+--------------------------+

USING index 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找;

mysql> explain select mm from demo2 where name='a';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | demo2 | ref  | idx_name_mm   | idx_name_mm | 33      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+--------------------------+

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

mysql> explain select mm from demo2;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | demo2 | index | NULL          | idx_name_mm | 66      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+

覆盖索引（Covering Index）,一说为索引覆盖。

理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

mysql> explain select * from demo2 d2 inner join demo3 d3 on d2.ot=d3.ot;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | d2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | d3    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

mysql> explain select * from demo2 d2 where d2.name='a' and name='c';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

SQL优化

策略1.尽量全值匹配

CREATE TABLE `staffs` (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR (24) NOT NULL DEFAULT "" COMMENT '姓名',
	age INT NOT NULL DEFAULT 0 COMMENT '年龄',
	pos VARCHAR (20) NOT NULL DEFAULT "" COMMENT '职位',
	add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) charset utf8 COMMENT '员工记录表';

INSERT INTO staffs (NAME, age, pos, add_time) VALUES ('z3', 22, 'manage', now());
INSERT INTO staffs (NAME, age, pos, add_time) VALUES ('july', 23, 'dev', now());
INSERT INTO staffs (NAME, age, pos, add_time) VALUES ('2000', 23, 'dev', now());
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos (NAME, age, pos);

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;

+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE pos = 'dev' and NAME = 'July' and Age = 25;
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+

当建立了索引列后，能在wherel条件中使用索引的尽量所用。

策略2.最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

mysql> EXPLAIN SELECT * FROM staffs WHERE  age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' ;
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

策略3.不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

策略4.范围条件放最后

存储引擎不能使用索引中范围条件右边的列

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' ;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'  and age =22;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'  and age =22 and pos='manager';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
1 row in set

+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+
1 row in set

+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'  and age >22 and pos='manager';
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys         | key                   | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
1 row in set

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and pos='manager' and age > 22;
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys         | key                   | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+

策略5.覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

mysql> EXPLAIN SELECT name,
pos FROM staffs WHERE NAME = 'July'  and age >22 and pos='manager';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using where; Using index |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+

策略6.不等于要甚用

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME != 'July';
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+

如果定要需要使用不等于,请用覆盖索引

mysql> EXPLAIN SELECT name,pos FROM staffs WHERE NAME != 'July';

+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys         | key                   | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+

策略7.Null/Not 有影响

注意：null/not null对索引的可能影响

自定定义为NOT NULL

在这里插入图片描述

在字段为not null的情况下，使用is null 或 is not null 会导致索引失效

解决方式：覆盖索引

EXPLAIN select name,age,pos from staffs where name is not null

自定义为NULL或者不定义

在这里插入图片描述

mysql> EXPLAIN select * from staffs2 where name is null;
+----+-------------+---------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs2 | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 75      | const |    1 | Using index condition |
+----+-------------+---------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

mysql> EXPLAIN select * from staffs2 where name is not null;
+----+-------------+---------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+-----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs2 | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+---------+------+-----------------------+------+---------+------+------+-------------+

Is not null 的情况会导致索引失效

解决方式：覆盖索引

EXPLAIN select name,age,pos from staffs where name is not null

策略8.Like查询要当心

like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

CREATE TABLE `tbl_user` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `NAME` VARCHAR(20) DEFAULT NULL,
 `age` INT(11) DEFAULT NULL,
 email VARCHAR(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
#drop table tbl_user
 
INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');

mysql> EXPLAIN select * from staffs where name ='july';
EXPLAIN select * from staffs where name like '%july%';
EXPLAIN select * from staffs where name like '%july';
EXPLAIN select * from staffs where name like 'july%';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
1 row in set

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set

+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys         | key                   | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+

解决方式：覆盖索引

EXPLAIN select name,age,pos from staffs where name like ‘%july%’

策略9.字符类型加引号

字符串不加单引号索引失效

mysql> EXPLAIN select * from staffs where name = 917;
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+

解决方式：请加引号

mysql> EXPLAIN select * from staffs where name = '917';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

策略10.OR改UNION效率高

mysql> EXPLAIN select * from staffs where name='July' or name = 'z3';
EXPLAIN select * from staffs where name='July' UNION select * from staffs where  name = 'z3';
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
1 row in set

+------+--------------+------------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id   | select_type  | table      | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+------+--------------+------------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|    1 | PRIMARY      | staffs     | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
|    2 | UNION        | staffs     | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL                  | NULL                  | NULL    | NULL  | NULL | Using temporary       |
+------+--------------+------------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

解决方式：覆盖索引

mysql> EXPLAIN  select name,age from staffs where name='July' or name = 'z3';
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys         | key                   | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+

测试

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

葵花宝典

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写*；

不等空值还有OR，索引影响要注意；

VARCHAR引号不可丢， SQL优化有诀窍。

批量导入

insert语句优化

提交前关闭自动提交
尽量使用批量insert语句
可以使用MyISAM存储引擎

LOAD DATA INFLIE

使用LOAD DATA INFLIE ,比一般的insert语句快20倍

select * into OUTFILE 'D:\\product.txt' from product_info;
load data INFILE 'D:\\product.txt' into table product_info;

上面的笔记是通过学习腾讯课程的享学课程整理而来

IccBoY

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
2
评论
MYSQL学习笔记整理

mysql用了很久，但没有系统学习过，去年在网上找了视频学习教程（享学.课堂的）进行了学习，这次把笔记重新整理了下，以方便查阅。
复制链接

扫一扫

专栏目录

MYSQL学习笔记整理

衡量指标

常用工具

开启缓存

存储引擎

MyISAM

Innodb

CSV

Archive

Memory

Ferderated

MYSQL锁

MyISAM表锁

Innodb行锁

Innodb表锁

事务

InnoDB支持事务

事务的特性

隔离性

事务并发问题

事务隔离级别

事务的语法

业务设计

逻辑设计

物理设计

慢查询

慢查询配置

慢查询解读

慢查询分析

索引与执行计划

索引

索引分类

基础语法

执行计划

执行计划-ID

执行计划-select_type

执行计划-table列

执行计划-Type列

System

const

eq_ref

ref

index

all

执行计划-possible_keys 与key

执行计划-key_len

字符类型

字符类型-索引字段为char类型+不可为Null时

字符类型-索引字段为char类型+允许为Null时

字符类型-索引字段为varchar类型+不可为Null时

字符类型-索引字段为varchar类型+允许为Null时

数值类型

日期和时间

总结

执行计划-ref

执行计划-rows

执行计划-extra

SQL优化

策略1.尽量全值匹配

策略2.最佳左前缀法则

策略3.不在索引列上做任何操作

策略4.范围条件放最后

策略5.覆盖索引尽量用

策略6.不等于要甚用

策略7.Null/Not 有影响

自定定义为NOT NULL

自定义为NULL或者不定义

策略8.Like查询要当心

策略9.字符类型加引号

策略10.OR改UNION效率高

测试

葵花宝典

批量导入

insert语句优化

LOAD DATA INFLIE

“相关推荐”对你有帮助么？