Mysql索引、命令重点介绍

最新推荐文章于 2022-04-27 20:23:09 发布

啥都会一点

最新推荐文章于 2022-04-27 20:23:09 发布

阅读量128

点赞数

分类专栏： java后端系列

本文链接：https://blog.csdn.net/weixin_42075258/article/details/117394347

版权

java后端系列专栏收录该内容

8 篇文章 0 订阅

订阅专栏

Mysql 索引整理

1.索引概念介绍：

# 1.什么是索引  （index）
-官方定义：一种帮助mysql提高查询效率的数据结构
-索引的优点
		大大加快查询速度。
-索引的缺点
		1.维护索引需要耗费数据库资源
		2.索引需要占用磁盘资源
		3.当对表进行增删改时候，因为要维护索引，速度会收到影响
# 2.索引的分类
-InnoDB
-a.主键索引
    设为主键后数据库会自动建立索引，innodb为聚簇索引。主键索引列不能为空。
-b.单值索引
		即一个索引只包含一个单列，一个表可以有多个单列索引。（单列索引，普通索引）
-c.唯一索引
		索引列的值必须唯一，但允许有空值，但是只能存在一个。（一个null也是唯一）
-d.复合索引
    即一个索引包含多个列。主要用于多列字段复合查询。
-MyISAM     
-e. Full Text 全文索引 （mysql5.7 版本之前只能由MYISAM引擎）
		全文索引类型为FULL TEXT，在定义索引列上支持全文查找，允许这些索引列中插入重复值和空值，全文索引可以在CHAR 、VARCHAR、TEXT 类型列上创建，mysql中只有MYISAM 存储引擎支持全文索引。（全文索引会对全文进行分词）

2.索引基本操作

# 3.索引的基本操作

1. 主键索引 自动创建
--建表
create table t_user(
										id varchar(20) primary key ,
										name varchar(20)
);
-- 查看索引
show index from t_user;

2. 单列索引（普通索引｜单列索引）
-- 建表时创建
create table t_user(
		id varchar(20) primary key,
		name varchar(20), key (name)		
);
-- 建表后创建
create index nameindex on t_user(name);

-- 删除索引
drop index 索引名 on 表名;

3. 唯一索引
-- 建表时创建
create table t_user (
			id varchar(20) primary key,
			name varchar(20),
			unique(name)
);

-- 建表后创建
create unique index nameindex on t_user(name);

4. 复合索引
-- 建表时创建
create table t_user (
			id varchar(20) primary key,
			name varchar(20),
			age int,
			key(name,age)
);
-- 建表后创建
create index nameindex on t_user(name,age);

#### 复合索引查询：（经典面试题）
1.最左前缀原则
2.mysql引擎在查询为了更好的利用索引 在查询过程中会动态调整查询字段顺序以便利用索引。
key （name age bir )
name bir age  可以使用索引，会使用优化器调整（1，2都复合）
name age bir  完全使用索引
age bir   -   不会使用索引，因为不符合1.

3.索引的底层原理

1. 思考
--- 建表
create table t_emp(id int primary key ,name varchar(20),age int );
--- 1.插入数据无序数据
insert into t_temp values(5,'d',22);
insert into t_temp values(6,'d',22);
insert into t_temp values(7,'e',21);
insert into t_temp values(1,'a',23);
insert into t_temp values(2,'b',26);
insert into t_temp values(3,'c',27);
insert into t_temp values(4,'a',32);
insert into t_temp values(8,'f',53);
insert into t_temp values(9,'v',13);
--- 2.注意⚠️：数据插入会自动按照主键索引排序。
--- 3.主键为什么会进行排序？ 为了快速查询。

数据行（根据数据而定）–>页（16kb）–>页目录（16kb）—>页目录（16kb满了上还有页目录的页目录）

⚠️B+树一般2-4层。
三层结构中：数据页–页目录–页目录的页目录。（10亿数据左右条记录，只存储id、指针）

聚簇索引 b+树：

非叶子结点只会存储主键（页首id）、指针（主键所在行指针）。
首层页目录常驻内存。
我们一般使用到的只是2层结构。（数据量不是特别大）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3TIVfv8m-1622336034073)(/Users/fanyuanxiang/Library/Application Support/typora-user-images/截屏2021-02-08 13.29.19.png)]

数据大小：视数据定义而定，
指针大小：4—8 byte （指针：指向下一个地址的数据）
页大小：默认16 kb
页的页大小：默认16 kb

三层b+树
顶层结构 : 常驻内存 ，不动用磁盘io，
第二层 : 从顶层查到第二层会动用一次磁盘io
第三层 : 从第二层查到第三次会动用一次磁盘io

4.聚簇索引、非聚簇索引

聚簇索引：将数据存储与索引放到一块，索引结构的叶子结点保存了行数据（row记录）。一个表中只能有一个聚簇索引。（主键索引默认是聚簇，如果不指定主键索引时候，innodb会隐式第一一个主键（类似oracle RowId）来作为聚簇索引。）
非聚簇索引：将数据与索引分开储存，索引结构的叶子结点指向了数据对应的位置。（索引储存的是数据对应的row记录地址）

InnoDB

注意⚠️：
在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、唯一索引、单值索引。
辅助索引 叶子结点存储的不再是行的物理位置，而是索引值、主键值；非叶子节点存储索引值、指针（下一个节点位置）。辅助索引访问数据都要经过二次查找。



- 聚簇索引 具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

- 表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行 按照一定的顺序排列，并且自动维护这个顺序；

- 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

在这里插入图片描述

MyISAM

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向（指针）真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6hfswTkX-1622336034094)(/Users/fanyuanxiang/Library/Application Support/typora-user-images/截屏2021-02-08 15.55.09.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5b1CumVJ-1622336034096)(/Users/fanyuanxiang/Library/Application Support/typora-user-images/截屏2021-02-08 16.02.09.png)]

Innodb 和MyISAM b+树比较总结

innodb和myisam物理存储的数据分布对比：

myisam：

是按照数据插入的顺序存储在磁盘上的，myisam中的主键索引和二级索引在结构上并没有什么不同，主键索引就是一个名为primary的唯一非空索引。

innodb：

因为innodb支持聚集索引，所以使用非常不同的方式存储同样的数据，innodb聚集索引包含了整个表的数据，而不是只有索引，因为在Innodb中，聚集索引就是表，所以不像myisam那样需要独立的行存储。聚集索引的每一个叶子节点都包含了主键值，事务ID，用于事务和MVCC的回滚指针以及所有剩余列的值，如果主键是一个列前缀索引，innodb也会包含完整的主键列和剩下的列的值。

还有一点和myisam不同的是，innodb的二级索引和聚集索引很不同，innodb二级索引（辅助索引）的叶子节点中存储的不是行指针，而是主键值，并以此作为指向行的指针，这样的策略减少了当出现行移动或者数据页的分裂时二级索引的维护工作，使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，innodb在移动行时无须更新二级索引中的这个指针。

非聚簇索引存储叶子节点存储的是数据地址，当数据row发生了增删改其他则可能影响数据大小，即其他数据物理地址也会发生改变，这要会发生b+树结构的变换调整。而聚簇索引辅助索引叶子节点存储的是id值，如果id发生改变只要改变对应的id值即可以。

聚簇索引使用主键自增：
保证数据存放顺序和物理存放顺序是一致的，即索引是相邻的。减少操作数据时发生调整物理地址、分页。磁盘碎片少，效率高。

5.什么情况下无法利用索引

- 1.查询中使用LIKE关键字
		在查询过程中使用LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%” ，索引不会被使用，如果 “%” 不是第一个位置，索引就会被使用。
- 2.查询语句中使用多列索引不符合最左匹配原则
		多列索引也称复合索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用到，
- 3.查询语句中使用OR关键字
		查询语句只有OR关键字时，如果OR前后的两个条件列都是索引，那么查询汇总将使用到索引，如果OR前后有一个条件不是索引，那么查询中将不会使用索引。

附录：

常用sql

数据库
# 查看所有的数据库
SHOW DATABASES ;
# 创建一个数据库
CREATE DATABASE k;
# 删除一个数据库
DROP DATABASE k;
# 使用这个数据库
USE k;
表
# 查看所有的表
SHOW TABLES ;
# 创建一个表
CREATE TABLE n(id INT, name VARCHAR(10));
CREATE TABLE m(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name));
CREATE TABLE m(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的表
CREATE TABLE n SELECT * FROM m;
# 新创建的表与一个存在的表的数据结构类似
CREATE TABLE m LIKE n;
# 创建一个临时表
# 临时表将在你连接MySQL期间存在。当断开连接时，MySQL将自动删除表并释放所用的空间。也可手动删除。
CREATE TEMPORARY TABLE l(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的临时表
CREATE TEMPORARY TABLE tt SELECT * FROM n;
# 删除一个存在表
DROP TABLE IF EXISTS m;
# 更改存在表的名称
ALTER TABLE n RENAME m;
RENAME TABLE n TO m;
# 查看表的结构(以下五条语句效果相同）
DESC n;   # 因为简单，所以建议使用
DESCRIBE n;
SHOW COLUMNS IN n;
SHOW COLUMNS FROM n;
EXPLAIN n;
# 查看表的创建语句
SHOW CREATE TABLE n;
表的结构
# 添加字段
ALTER TABLE n ADD age VARCHAR(2) ;
# 删除字段
ALTER TABLE n DROP age;
# 更改字段属性和属性
ALTER TABLE n CHANGE age a INT;
# 只更改字段属性
ALTER TABLE n MODIFY age VARCHAR(7) ;
表的数据
# 增加数据
INSERT INTO n VALUES (1, 'tom', '23'), (2, 'john', '22');
INSERT INTO n SELECT * FROM n;  # 把数据复制一遍重新插入
# 删除数据
DELETE FROM n WHERE id = 2;
# 更改数据
UPDATE n SET name = 'tom' WHERE id = 2;
# 数据查找
SELECT * FROM n WHERE name LIKE '%h%';
# 数据排序(反序)
SELECT * FROM n ORDER BY name, id DESC ;
键
# 添加主键
ALTER TABLE n ADD PRIMARY KEY (id);
ALTER TABLE n ADD CONSTRAINT pk_n PRIMARY KEY (id);   # 主键只有一个，所以定义键名似乎也没有什么用
# 删除主键
ALTER TABLE n DROP PRIMARY KEY ;
# 添加外键
ALTER TABLE m ADD FOREIGN KEY (id) REFERENCES n(id);    # 自动生成键名m_ibfk_1
ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id);   # 使用定义的键名fk_id
# 删除外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`;
# 修改外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n(id);    # 删除之后从新建
# 添加唯一键
ALTER TABLE n ADD UNIQUE (name);
ALTER TABLE n ADD UNIQUE u_name (name);
ALTER TABLE n ADD UNIQUE INDEX u_name (name);
ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name);
CREATE UNIQUE INDEX u_name ON n(name);
# 添加索引
ALTER TABLE n ADD INDEX (age);
ALTER TABLE n ADD INDEX i_age (age);
CREATE INDEX i_age ON n(age);
# 删除索引或唯一键
DROP INDEX u_name ON n;
DROP INDEX i_age ON n;
视图
# 创建视图
CREATE VIEW v AS SELECT id, name FROM n;
CREATE VIEW v(id, name) AS SELECT id, name FROM n;
# 查看视图(与表操作类似)
SELECT * FROM v;
DESC v;
# 查看创建视图语句
SHOW CREATE VIEW v;
# 更改视图
CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
ALTER VIEW v AS SELECT name FROM n ;
# 删除视图
DROP VIEW IF EXISTS v;
联接
# 内联接
SELECT * FROM m INNER JOIN n ON m.id = n.id;
# 左外联接
SELECT * FROM m LEFT JOIN n ON m.id = n.id;
# 右外联接
SELECT * FROM m RIGHT JOIN n ON m.id = n.id;
# 交叉联接
SELECT * FROM m CROSS JOIN n;   # 标准写法
SELECT * FROM m, n;
# 类似全连接full join的联接用法
SELECT id,name FROM m
UNION
SELECT id,name FROM n;
函数
# 聚合函数
SELECT count(id) AS total FROM n;   # 总数
SELECT sum(age) AS all_age FROM n;   # 总和
SELECT avg(age) AS all_age FROM n;   # 平均值
SELECT max(age) AS all_age FROM n;   # 最大值
SELECT min(age) AS all_age FROM n;   # 最小值
# 数学函数
SELECT abs(-5);   # 绝对值
SELECT bin(15), oct(15), hex(15);   # 二进制，八进制，十六进制
SELECT pi();   # 圆周率3.141593
SELECT ceil(5.5);   # 大于x的最小整数值6
SELECT floor(5.5);   # 小于x的最大整数值5
SELECT greatest(3,1,4,1,5,9,2,6);   # 返回集合中最大的值9
SELECT least(3,1,4,1,5,9,2,6);    # 返回集合中最小的值1
SELECT mod(5,3);    # 余数2
SELECT rand();    # 返回０到１内的随机值，每次不一样
SELECT rand(5);   # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
SELECT round(1415.1415);   # 四舍五入1415
SELECT round(1415.1415, 3);   # 四舍五入三位数1415.142
SELECT round(1415.1415, -1);    # 四舍五入整数位数1420
SELECT truncate(1415.1415, 3);    # 截短为3位小数1415.141
SELECT truncate(1415.1415, -1);   # 截短为-1位小数1410
SELECT sign(-5);    # 符号的值负数-1
SELECT sign(5);    # 符号的值正数1
SELECT sqrt(9);   # 平方根3
SELECT sqrt(9);   # 平方根3
# 字符串函数
SELECT concat('a', 'p', 'p', 'le');   # 连接字符串-apple
SELECT concat_ws(',', 'a', 'p', 'p', 'le');   # 连接用','分割字符串-a,p,p,le
SELECT insert('chinese', 3, 2, 'IN');    # 将字符串'chinese'从3位置开始的2个字符替换为'IN'-chINese
SELECT left('chinese', 4);   # 返回字符串'chinese'左边的4个字符-chin
SELECT right('chinese', 3);   # 返回字符串'chinese'右边的3个字符-ese
SELECT substring('chinese', 3);   # 返回字符串'chinese'第三个字符之后的子字符串-inese
SELECT substring('chinese', -3);   # 返回字符串'chinese'倒数第三个字符之后的子字符串-ese
SELECT substring('chinese', 3, 2);   # 返回字符串'chinese'第三个字符之后的两个字符-in
SELECT trim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese'
SELECT ltrim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese '
SELECT rtrim(' chinese ');    # 切割字符串' chinese '两边的空字符-' chinese'
SELECT repeat('boy', 3);    # 重复字符'boy'三次-'boyboyboy'
SELECT reverse('chinese');    # 反向排序-'esenihc'
SELECT length('chinese');   # 返回字符串的长度-7
SELECT upper('chINese'), lower('chINese');    # 大写小写 CHINESE    chinese
SELECT ucase('chINese'), lcase('chINese');    # 大写小写 CHINESE    chinese
SELECT position('i' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-3
SELECT position('e' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-5
SELECT strcmp('abc', 'abd');    # 比较字符串，第一个参数小于第二个返回负数- -1
SELECT strcmp('abc', 'abb');    # 比较字符串，第一个参数大于第二个返回正数- 1
# 时间函数
SELECT current_date, current_time, now();    # 2018-01-13   12:33:43    2018-01-13 12:33:43
SELECT hour(current_time), minute(current_time), second(current_time);    # 12  31   34
SELECT year(current_date), month(current_date), week(current_date);   # 2018    1   1
SELECT quarter(current_date);   # 1
SELECT monthname(current_date), dayname(current_date);   # January  Saturday
SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);    # 7   13  13
# 控制流函数
SELECT if(3>2, 't', 'f'), if(3<2, 't', 'f');    # t f
SELECT ifnull(NULL, 't'), ifnull(2, 't');    # t 2
SELECT isnull(1), isnull(1/0);    # 0 1 是null返回1，不是null返回0
SELECT nullif('a', 'a'), nullif('a', 'b');    # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
SELECT CASE 2
       WHEN 1 THEN 'first'
       WHEN 2 THEN 'second'
       WHEN 3 THEN 'third'
       ELSE 'other'
       END ;     # second
# 系统信息函数
SELECT database();    # 当前数据库名-test
SELECT connection_id();   # 当前用户id-306
SELECT user();    # 当前用户-root@localhost
SELECT version();   # 当前mysql版本
SELECT found_rows();    # 返回上次查询的检索行数
用户
# 增加用户
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test'));    # 在用户表中插入用户信息，直接操作User表不推荐
# 删除用户
DROP USER 'test'@'localhost';
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 用户授权
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
# 授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
FLUSH PRIVILEGES ;   # 刷新系统权限表,使授予权限生效
# 撤销用户授权
REVOKE DELETE ON *.* FROM 'test'@'localhost';   # 取消该用户的删除权限
存储过程
# 创建存储过程
DELIMITER //    # 无参数
CREATE PROCEDURE getDates()
  BEGIN
    SELECT * FROM test ;
  END //
CREATE PROCEDURE getDates_2(IN id INT)    # in参数
  BEGIN
    SELECT * FROM test WHERE a = id;
  END //
CREATE PROCEDURE getDates_3(OUT sum INT)    # out参数
  BEGIN
    SET sum = (SELECT count(*) FROM test);
  END //
CREATE PROCEDURE getDates_4(INOUT i INT)    # inout参数
  BEGIN
    SET i = i + 1;
  END //
DELIMITER ;
# 删除存储过程
DROP PROCEDURE IF EXISTS getDates;
# 修改存储过程的特性
ALTER PROCEDURE getDates MODIFIES SQL DATA ;
# 修改存储过程语句（删除再重建）略
# 查看存储过程
SHOW PROCEDURE STATUS LIKE 'getDates';    # 状态
SHOW CREATE PROCEDURE getDates_3;   # 语句
# 调用存储过程
CALL getDates();
CALL getDates_2(1);
CALL getDates_3(@s);
SELECT @s;
SET @i = 1;
CALL getDates_4(@i);
SELECT @i;    # @i = 2
其他语句
# 查看所有的表信息（包括视图）
SHOW TABLE STATUS;
其他
# 数据库备份
mysqldump -u root -p db_name > file.sql
mysqldump -u root -p db_name table_name > file.sql
# 数据库还原
mysql -u root -p < C:\file.sql

crud相关sql

关键字顺序

1、使用

查询中用到的关键词主要包含六个，并且他们的使用顺序依次为

select --> from --> where --> group by --> having --> order by

其中select和from是必须的，其他关键词是可选的

2、执行

六个关键词的执行顺序是：

from --> where --> group by --> having --> select --> order by

from: 需要从哪个数据表检索数据。

where: 过滤表中数据的条件。

group by: 如何将上面过滤出的数据分组。

having: 对上面已经分组的数据进行过滤的条件。

select: 查看结果集中的哪个列，或列的计算结果。

order by: 按照什么样的顺序来查看返回的数据。

表库操作集合

连接数据库：

mysql -h10.20.66.32 -uroot -p123456

-h后面是mysqlServer所在地址，-u后面是用户名，-p后面是密码。

查看数据库

show databases;

show databases

使用数据库

use test;

查看表

show tables;

tables

查看表结构

desc winton

desc

建表

create table t1(
    id int not null primary key, name char(20) not null );

语法 create table 表名称( 字段名字段名类型字段描述符，字段名字段类型字段描述符);

删除表

drop table test;

语法：drop table 表名称；

修改表

添加字段

alter table t1 add(score int not null);

语法：alter table 表明称 add(字段名类型描述符)；

移除字段

alter table t1 drop column score;

语法：alter table 表名 drop colunm 字段名，drop colunm 字段名；

变更字段

alter table t1 change name score int not null;

语法：alter table 表名 change 旧字段名新字段名新字段描述符

插入

全字段插入

insert into winton values(001,'zww'),(002,'rs');

语法：insert into 表名 values(字段1值,字段2值,……),(字段1值,字段2值，……);

个别字段插入

insert into winton(id) values(004);

insert
查看插如后的结果，如上图所示。

语法：insert inton 表名(字段名) values(值一),(值二);

普通查询

单表全字段查询

select * from t1;

语法：select * from 表名；

单表个别字段查询

select id from t1;

语法：select 字段一,字段二 from 表名;

多表查询

select t1.id,t1.score,winton.name from t1,winton;

多表查询

语法：select 表一字段,表二字段,表三字段,…… from 表一,表二,表三,……

条件查询

单表条件查询

select * from t1 where socre>90;

语法：select 字段1,字段2 from 表名 where 条件；

多表条件查询

select t1.id,t1.score,winton.name from t1,winton where t1.id=winton.id;

winton

语法：select 表一字段，表二字段 from 表一，表二 where 条件；

嵌套查询

select name from winton where id=(select id from t1 where score=90);

这里写图片描述

语法：select 字段一,字段二…… from 表名 where 条件（查询）;

并查询

(select id from t1 )union(select id from winton);

并查询

交查询

select id from t1 where id in (select id from winton);

这里写图片描述

删除

delete from winton where id=4;

语法：delete from 表名 where 条件；

更新

update t1 set score=69 where id=2;

语法：update 表名 set 更改的字段名=值 where 条件；

常用函数

求和

select sum(score) from t1;

注：sum(字段) 对字符串和时间无效

求平均值

select avg(score) from t1;

注：avg(字段)对字符串和时间无效

计数

select count(*) from t1;

注：count(字段名)不包含NULL；

这里写图片描述

求最大值

select max(name) from winton;

注：max(colunm)返回字母序最大的，返回数值最大的

求最小值

select min(name) from winton;

注：min(colunm)返回字母序最小值，返回数值最小值

常用的修饰符

distinct 字段中值唯一

select distinct name from winton;

limit查询结果数限制

select * from winton limit 2;

order by 排序

select * from winton order by name;

注：默认是升序

desc 降序

slelect * from winton order by name desc;

asc 升序

select * from winton order by name asc;

group by 分组

select name from winton group by name;

索引

创建普通索引

create index wintonIndex on winton (name);

语法：create index 索引名称 on 表名 (字段一，字段二，……);

创建唯一索引

create unique index wintonIndex on winton (id);

语法：create unique index 索引名 on 表名 (字段一,字段二,……)；
ps:unique index 要求列中数据唯一，不能出现重复。

移除索引

drop index wintonIndex on winton;

语法: drop index 索引名 on 表名；

mysql相关DDL以及相关示例

# mysql相关常用DDL
 DDL 语句
1、 创建数据库
 CREATE DATABASE dbname;
　　查看已经存在的数据库
 SHOW DATABASES;
　　选择数据库
 USE dbname;
　　查看数据库中的表
 SHOW TABLES;

2、删除数据库
 DROP DATABASE dbname;
 
3、创建表
 CREATE TABLE tablename(column_name_1 column_type_1 constraints,
 column_name_2 column_type_2 constraints,
 ......
 column_name_n column_type_n constraints);
　　查看表
 DESC tablename；
　　查看创建表的SQL语句
 SHOW CREATE TABLE tablename \G
 
4、删除表
  DROP TABLE tablename；
  
5、修改表
✅ ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];
　　示例
复制代码
mysql> CREATE TABLE emp(ename VARCHAR(10),
     hiredate DATE,
     sal DECIMAL(10,2),
     deptno INT(2));
     
Query OK, 0 rows affected (0.02 sec)

mysql> DESC emp;

+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

✅ mysql> ALTER TABLE emp MODIFY ename VARCHAR(20);

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
复制代码

　　/*增加表字段*/
　　
✅ ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];
　　示例
复制代码
mysql> ALTER TABLE emp ADD COLUMN age INT(3);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
复制代码

　　/*删除表字段*/
　　
✅ ALTER TABLE tablename DROP [COLUMN] col_name;
　　字段改名
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name];
　　示例
复制代码
mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

✅ mysql> ALTER TABLE emp CHANGE COLUMN age age1 INT(4);

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
复制代码
⚠️ 注意：change和modify都可以修改表的定义，

　　不同的是change后面需要写两次列名，不方便。

　　但是change的优点是可以修改列名称，modify则不能。
　　
说明： 
　　修改字段排列顺序
　　上面字段增加和修改语法（ADD、CHANGE、MODIFY）中，都有可选项 FIRST | AFTER column_name，默认ADD增加的新字段是加在表的最后位置，而CHANGE/MODIFY默认都不会改变字段的位置。
　　
　　FIRST :代表将字段位置添加或修改到第一列。
　　AFTER column_name : 代表将字段位置添加或者修改到column_name字段的后面。

 

注意： CHANGE/FIRST/AFTER COLUMN这些关键字都属于MySQL在标准SQL上的扩展，在其他数据库上不一定适用。

 

　　修改表名称
ALTER TABLE tablename RENAME [TO] new_tablename;

EXPLAIN 命令详解

在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN去完成。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。需要注意的是，生成的QEP并不确定，它可能会根据很多因素发生改变。MySQL不会将一个QEP和某个给定查询绑定，QEP将由SQL语句每次执行时的实际情况确定，即便使用存储过程也是如此。尽管在存储过程中SQL语句都是预先解析过的，但QEP仍然会在每次调用存储过程的时候才被确定。

通过执行计划可以知道什么？

(root@yayun-mysql-server) [test]>explain select d1.age, t2.id from (select age,name from t1 where id in (1,2))d1, t2 where d1.age=t2.age group by d1.age, t2.id order by t2.id;
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref    | rows | Extra                           |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL   |    2 | Using temporary; Using filesort |
|  1 | PRIMARY     | t2         | ref   | age           | age     | 5       | d1.age |    1 | Using where; Using index        |
|  2 | DERIVED     | t1         | range | PRIMARY       | PRIMARY | 4       | NULL   |    2 | Using where                     |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
3 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

**MySQL执行计划调用方式
**1.EXPLAIN SELECT ……
变体：
2.EXPLAIN EXTENDED SELECT ……
将执行计划"反编译"成SELECT语句，运行SHOW WARNINGS
可得到被MySQL优化器优化后的查询语句
3.EXPLAIN PARTITIONS SELECT ……
用于分区表的EXPLAIN生成QEP的信息

执行计划包含的信息

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

1. id：

包含一组数字，表示查询中执行select子句或操作表的顺序

Example（id相同，执行顺序由上至下）

(root@yayun-mysql-server) [test]>explain select t2.* from t1, t2, t3 where t1.id=t2.id and t1.id=t3.id and t1.name='';
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref    | PRIMARY,name  | name    | 63      | const      |    1 | Using where; Using index |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |    1 |                          |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |    1 | Using index              |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
3 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

Example （如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行）

(root@yayun-mysql-server) [test]>explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name=''));
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | no matching row in const table                      |
|  3 | SUBQUERY    | t3    | ref  | name          | name | 63      |      |    1 | Using where; Using index                            |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
3 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

Example（id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行）

(root@yayun-mysql-server) [test]>explain select t2.* from (select t3.id from t3 where t3.name='')s1, t2 where s1.id=t2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 |                          |
|  1 | PRIMARY     | t2         | const  | PRIMARY       | PRIMARY | 4       | const |    1 |                          |
|  2 | DERIVED     | t3         | ref    | name          | name    | 63      |       |    1 | Using where; Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+--------------------------+
3 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

2. select_type

示查询中每个select子句的类型（简单OR复杂）

a. SIMPLE：查询中不包含子查询或者UNION
b. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
e. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f. 从UNION表获取结果的SELECT被标记为：UNION RESULT

SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
DEPENDENT意味着select依赖于外层查询中发现的数据。
UNCACHEABLE意味着select中的某些特性阻止结果被缓存于一个item_cache中。

Example

复制代码

(root@yayun-mysql-server) [test]>explain select d1.name, ( select id from t3) d2 from (select id,name from t1 where name='')d1 union (select name,id from t2);
+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+
| id | select_type  | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+
|  1 | PRIMARY      | <derived3> | system | NULL          | NULL | NULL    | NULL |    0 | const row not found      |
|  3 | DERIVED      | t1         | ref    | name          | name | 63      |      |    1 | Using where; Using index |
|  2 | SUBQUERY     | t3         | index  | NULL          | age  | 5       | NULL |    6 | Using index              |
|  4 | UNION        | t2         | index  | NULL          | name | 63      | NULL |    4 | Using index              |
| NULL | UNION RESULT | <union1,4> | ALL    | NULL          | NULL | NULL    | NULL | NULL |                          |
+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+
5 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

第一行：id列为1，表示第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。
第二行：id为3，表示该查询的执行次序为2（ 4 => 3），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。
第三行：select列表中的子查询，select_type为subquery，为整个查询中的第二个select。
第四行：select_type为union，说明第四个select是union里的第二个select，最先执行。
第五行：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

3. type

表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下:

ALL, index, range, ref, eq_ref, const, system, NULL

从左到右，性能从最差到最好

*Example*

a. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

复制代码

(root@yayun-mysql-server) [test]>explain select * from t1 where email='';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

b. index：Full Index Scan，index与ALL区别为index类型只遍历索引树

复制代码

(root@yayun-mysql-server) [test]>explain select id from t1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | age  | 5       | NULL |    4 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

c. range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

复制代码

(root@yayun-mysql-server) [test]>explain select * from t1 where id in (1,4);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>explain select * from t1 where id between 1 and 4;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>explain select * from t1 where id=1 or id=4;       
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

(root@yayun-mysql-server) [test]>explain select * from t1 where id > 1;      
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

d. ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行

复制代码

(root@yayun-mysql-server) [test]>explain select * from t1 where name='yayun';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | name          | name | 63      | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

e. eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

复制代码

(root@yayun-mysql-server) [test]>explain select t1.name from t1, t2 where t1.id=t2.id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t1    | index  | PRIMARY       | name    | 63      | NULL       |    4 | Using index |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.id |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

f. const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

复制代码

(root@yayun-mysql-server) [test]>explain select * from ( select * from t1 where id=1)b1;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t1         | const  | PRIMARY       | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
2 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

注：system是const类型的特例，当查询的表只有一行的情况下，使用system

g. NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

复制代码

(root@yayun-mysql-server) [test]>explain select * from t1 where id = (select min(id) from t2);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                        |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 |                              |
|  2 | SUBQUERY    | NULL  | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
2 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

4. possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

5. key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select id,age from t1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | age  | 5       | NULL |    4 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

6. key_len

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

**7. ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值**

**8. rows

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

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select * from t1 , t2 where t1.id=t2.id and t2.name='atlas';
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 63      | const      |    1 | Using where |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.id |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

9. Extra

包含不适合在其他列中显示但十分重要的额外信息
a. Using index
该值表示相应的select操作中使用了覆盖索引（Covering Index）

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select id from t1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | age  | 5       | NULL |    4 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

覆盖索引（Covering Index）
MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件
包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）
注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

b. Using where
表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select id,name from t1 where id<4;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY       | name | 63      | NULL |    4 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

c. Using temporary
表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：
1)内部临时表占用的空间超过min(tmp_table_size，max_heap_table_size)系统变量的限制
2)使用了TEXT/BLOB 列

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select id from t1 where id in (1,2) group by age,name;
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

d. Using filesort
MySQL中无法利用索引完成的排序操作称为“文件排序”

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select id,age from t1 order by name; 
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>explain select id,age from t1 order by age; 
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | age  | 5       | NULL |    4 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

e. Using join buffer
改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进****能。

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select t1.name from t1 inner join t2 on t1.name=t2.name;
+----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref          | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | name          | name | 63      | NULL         |    4 | Using index              |
|  1 | SIMPLE      | t2    | ref   | name          | name | 63      | test.t1.name |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------+
2 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>alter table t1 drop key name;                                   
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@yayun-mysql-server) [test]>alter table t2 drop key name; 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@yayun-mysql-server) [test]>explain select t1.name from t1 inner join t2 on t1.name=t2.name;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |                                |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

f. Impossible where
这个值强调了where语句会导致没有符合条件的行。

*Example*

复制代码

(root@yayun-mysql-server) [test]>EXPLAIN SELECT * FROM t1 WHERE 1=2;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

复制代码

h. Select tables optimized away
这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行.

*Example*

复制代码

(root@yayun-mysql-server) [test]>explain select max(id) from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

(root@yayun-mysql-server) [test]>

I. Index merges
当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。
Using sort_union(…)
Using union(…)
Using intersect(…)

总结：**
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。**

啥都会一点

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql索引、命令重点介绍

目录Mysql 索引整理1.索引概念介绍：2.索引基本操作3.索引的底层原理4.聚簇索引、非聚簇索引5.什么情况下无法利用索引附录：常用sqlcrud相关sql关键字顺序1、使用2、执行表库操作集合连接数据库：**查看数据库****使用数据库**查看表**查看表结构****建表****删除表****修改表****移除字段****变更字段****插入****个别字段插入****普通查询****多表查询****多表条件查询****嵌套查询****并查询**求最小值常用的修饰符distinct 字段中值唯一lim
复制链接

扫一扫