MySQL进阶学习

MySQL基础

数据定义语言DDL(Data Definition Language)

对象： 数据库和表

关键词： create alter drop truncate(删除当前表再新建一个一模一样的表结构)

创建数据库：create database school;

删除数据库：drop database school;

切换数据库：use school;

创建表：create table student
　　　　id int(4) primary key auto_increment
　　　　name varchar(20)
　　　　　　score int(3)

　　);

查看数据库里存在的表：show tables;

数据操纵语言DML(Data Manipulation Language)

1.插入 INSERT

-- 单行插入
INSERT INTO test(id, NAME) 
value(16, 'g');

-- 插入且支持子查询
INSERT INTO test(id, NAME) 
select 16, 'g';

-- 批量插入
INSERT INTO test values(17, 'g'),
(18, 'h');

2.修改 UPDATE

-- 单表修改
UPDATE test SET `NAME` = 'coderblue'
WHERE ID = 1;

-- 多表修改
UPDATE c_ins_project p
INNER JOIN c_ins_project_parnet pa 
SET p.`PROJECTNAME` = 'coderblue' 
WHERE
	p.PARENTID = pa.ID 
	AND p.ID = '202014100130'

3.删除

-- 删除表数据
DELETE FROM test WHERE `NAME` like '%a%';

-- 删除表数据，再插入数据后自增长列的值从1开始。
TRUNCATE TABLE test;

数据查询语言DQL(Data Query Language)

1.子查询

• where后面的列子查询

  -- 等值连接更优
  SELECT p.ID FROM c_ins_project p 
  WHERE p.PARENTID IN (
  SELECT pa.ID FROM c_ins_project_parnet pa
  );

• where后面的行子查询

  -- 查工程名称长度最大的工程id
  SELECT p.ID FROM c_ins_project p 
  WHERE LENGTH(p.PROJECTNAME) = (
  	SELECT MAX(LENGTH(pr.PROJECTNAME)) FROM c_ins_project pr 
  );

• select后面的子查询

  SELECT
  	p.ID,
  	( SELECT count( * ) FROM c_ins_project_parnet pa ) 项目表总数
  FROM
  	c_ins_project p;

• from后面的子查询

  SELECT
  	p.ID,
  	p.PROJECTNAME,
  	pa.ID parentId,
  	p.PARENTID 
  FROM
  	( SELECT pa.ID FROM c_ins_project_parnet pa ) pa
  	INNER JOIN c_ins_project p ON pa.id = p.PARENTID

• exists后面的子查询

  SELECT
  	dep.depid,
  	dep.depname 
  FROM
  	department dep 
  WHERE
  	EXISTS ( SELECT * FROM employee emp WHERE emp.depid = dep.depid )

count函数

1.count(1) 和 count(*) 和 count(字段)区别

*count()**：扫描全表，但不取值，读到的每一行都是1，判断不可能是null，按值累加。（做了专门的优化）

**count(1)*：INNODB存储引擎下，和count()的效率差不多，比count(字段)要高一些

​ count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

**count(字段)**：扫描全表，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加

分组函数

1.Group By函数

-- 全都是函数
select count(*), LENGTH(PROJECTNAME) FROM c_ins_project p 
GROUP BY LENGTH(PROJECTNAME);

-- 使用 any_value() 分组查出id信息，单独查询时写 LENGTH(PROJECTNAME), id，然后又不Group by id 就会报错this is incompatible with sql_mode=only_full_group_by
select count(*), LENGTH(PROJECTNAME), any_value(ID) FROM c_ins_project p 
GROUP BY LENGTH(PROJECTNAME);

-- 根据PROJECTNAME分组
select count(*),any_value(ID),PROJECTNAME FROM c_ins_project p 
GROUP BY PROJECTNAME;

数据控制语言DCL(Data Control Language)

1.事务

查看mysql引擎：默认MyIsam，不支持事务，建议在配置文件里更改成InnoDB，如果还是没用就考虑是不是表的引擎没用及时刷新过来。

-- 查看mysql引擎
show ENGINES;
-- 更改表的引擎(可选)
alter table test engine=InnoDB;
-- 查看当前会话是否自动提交
show VARIABLES like 'autocommit';

演示事务 和 事务回滚

# 关闭当前会话自动提交, set 1 就是自动提交
SET autocommit = 0;
# 开启事务
START TRANSACTION;
# 编写一组事务的语句
UPDATE test SET name = 'coderblue1' WHERE ID = 1;
UPDATE test SET name = 'tom1' WHERE ID = 2;
# 结束事务
COMMIT;
# 或者 事务回滚
ROLLBACK;

回滚到保存点SAVEPOINT

# 关闭自动提交, set 1 就是自动提交
SET autocommit = 0;
# 开启事务
START TRANSACTION;
# 编写一组事务的语句
UPDATE test SET name = 'coderblue' WHERE ID = 1;
# 设置保存点
SAVEPOINT a; 
UPDATE test SET name = 'tom' WHERE ID = 2;
# 回滚到保存点
ROLLBACK TO a;

视图

视图创建语法：可以将经常复用的查询结果集作视图

CREATE VIEW 视图名  
AS  
查询语句;

查看视图

-- 查看视图数据
select * from 视图名;

-- 查看视图结构信息:包括视图名、创建视图语法、视图编码
SHOW CREATE VIEW 视图名;

-- 查看视图的字段信息
DESC 视图名;

视图修改

-- 方式一
CREATE 
OR REPLACE VIEW 视图名  
AS  
查询语句;

-- 方式二
ALTER VIEW 视图名  
AS  
查询语句;

视图删除

DROP VIEW 视图名;

视图和表的区别

	创建语法的关键字	是否实际占用物理空间
视图	create view	没有
表	create table	占用

常用SQL

1. count(1) 和 count(*) 和 count(字段)区别

   *count()**：扫描全表，但不取值，读到的每一行都是1，判断不可能是null，按值累加。（做了专门的优化）

   **count(1)*：INNODB存储引擎下，和count()的效率差不多，比count(字段)要高一些

   ​ count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

   ---

   **count(字段)**：扫描全表，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加

2. 分组函数

   -- 全都是函数
   select count(*), LENGTH(PROJECTNAME) FROM c_ins_project p 
   GROUP BY LENGTH(PROJECTNAME);
   
   -- 使用 any_value() 分组查出id信息，单独查询时写 LENGTH(PROJECTNAME), id，然后又不Group by id 就会报错this is incompatible with sql_mode=only_full_group_by
   select count(*), LENGTH(PROJECTNAME), any_value(ID) FROM c_ins_project p 
   GROUP BY LENGTH(PROJECTNAME);
   
   -- 根据PROJECTNAME分组
   select count(*),any_value(ID),PROJECTNAME FROM c_ins_project p 
   GROUP BY PROJECTNAME;

3. 子查询

• where后面的列子查询

  -- 等值连接更优
  SELECT p.ID FROM c_ins_project p 
  WHERE p.PARENTID IN (
  	SELECT pa.ID FROM c_ins_project_parnet pa
  );

• where后面的行子查询

  -- 查工程名称长度最大的工程id
  SELECT p.ID FROM c_ins_project p 
  WHERE LENGTH(p.PROJECTNAME) = (
  	SELECT MAX(LENGTH(pr.PROJECTNAME)) FROM c_ins_project pr 
  );

• select后面的子查询

  SELECT
  	p.ID,
  	( SELECT count( * ) FROM c_ins_project_parnet pa ) 项目表总数
  FROM
  	c_ins_project p;

• from后面的子查询

  SELECT
  	p.ID,
  	p.PROJECTNAME,
  	pa.ID parentId,
  	p.PARENTID 
  FROM
  	( SELECT pa.ID FROM c_ins_project_parnet pa ) pa
  	INNER JOIN c_ins_project p ON pa.id = p.PARENTID

• exists后面的子查询

  SELECT
  	dep.depid,
  	dep.depname 
  FROM
  	department dep 
  WHERE
  	EXISTS ( SELECT * FROM employee emp WHERE emp.depid = dep.depid )

4. 插入 INSERT

   -- 单行插入
   INSERT INTO test(id, NAME) 
   value(16, 'g');
   
   -- 插入且支持子查询
   INSERT INTO test(id, NAME) 
   select 16, 'g';
   
   -- 批量插入
   INSERT INTO test values(17, 'g'),
   (18, 'h');

5. 修改 UPDATE

   -- 单表修改
   UPDATE test SET `NAME` = 'coderblue'
   WHERE ID = 1;
   
   -- 多表修改
   UPDATE c_ins_project p
   INNER JOIN c_ins_project_parnet pa 
   SET p.`PROJECTNAME` = 'coderblue' 
   WHERE
   	p.PARENTID = pa.ID 
   	AND p.ID = '202014100130'

6. 删除

   -- 删除表数据
   DELETE FROM test WHERE `NAME` like '%a%';
   
   -- 删除表数据，再插入数据后自增长列的值从1开始。
   TRUNCATE TABLE test;

7. 事务

   查看mysql引擎：默认MyIsam，不支持事务，建议在配置文件里更改成InnoDB，如果还是没用就考虑是不是表的引擎没用及时刷新过来。

   -- 查看mysql引擎
   show ENGINES;
   -- 更改表的引擎(可选)
   alter table test engine=InnoDB;
   -- 查看当前会话是否自动提交
   show VARIABLES like 'autocommit';

   演示事务 和 事务回滚

   # 关闭当前会话自动提交, set 1 就是自动提交
   SET autocommit = 0;
   # 开启事务
   START TRANSACTION;
   # 编写一组事务的语句
   UPDATE test SET name = 'coderblue1' WHERE ID = 1;
   UPDATE test SET name = 'tom1' WHERE ID = 2;
   # 结束事务
   COMMIT;
   # 或者 事务回滚
   ROLLBACK;

   回滚到保存点SAVEPOINT

   # 关闭自动提交, set 1 就是自动提交
   SET autocommit = 0;
   # 开启事务
   START TRANSACTION;
   # 编写一组事务的语句
   UPDATE test SET name = 'coderblue' WHERE ID = 1;
   # 设置保存点
   SAVEPOINT a; 
   UPDATE test SET name = 'tom' WHERE ID = 2;
   # 回滚到保存点
   ROLLBACK TO a;

8. 视图

   视图创建语法：可以将经常复用的查询结果集作视图

   CREATE VIEW 视图名  
   AS  
   查询语句;

   查看视图

   -- 查看视图数据
select * from 视图名;
   
   -- 查看视图结构信息:包括视图名、创建视图语法、视图编码
   SHOW CREATE VIEW 视图名;

   -- 查看视图的字段信息
   DESC 视图名;

视图修改

-- 方式一
CREATE 
	OR REPLACE VIEW 视图名  
AS  
	查询语句;

-- 方式二
ALTER VIEW 视图名  
AS  
	查询语句;

视图删除

DROP VIEW 视图名;

视图和表的区别

	创建语法的关键字	是否实际占用物理空间
视图	create view	没有
表	create table	占用

9、mysql 行变列（多行变成一行/多行合并成一行/多行合并成多列/合并行）

使用GROUP_CONCAT函数

select GROUP_CONCAT('"', id, '"') from order
where state = 1 and country not in ('cn','us','jp') and create_time > '2022-03-17 07:00:16';

存储过程

变量

1. 用户变量

   定义和使用的位置可以是会话中的任何地方

   • 声明变量：通过 SET 或 SELECT

   SET @用户变量名=值;
   SET @用户变量名:=值;
   SELECT @用户变量名:=值;

   • 赋值（更新用户变量的值）

   -- 方式一：通过 SET 或 SELECT
   SET @用户变量名=值;
   SET @用户变量名:=值;
   SELECT @用户变量名:=值;
   
   -- 方式二：通过SELECT INTO
   SELECT 字段 INTO 变量名
   FROM 表;

   • 查看用户变量的值

   SELECT @变量名;

2. 局部变量

   作用域：仅仅在定义它的begin end中有效

   • 声明

   DECLARE 局部变量名 类型;
   DECLARE 局部变量名 类型 DEFAULT 值;

   • 赋值

   -- 方式一：通过 SET 或 SELECT
   SET 局部变量名=值;
   SET 局部变量名:=值;
   SELECT @局部变量名:=值;
   
   -- 方式二：通过SELECT INTO
   SELECT 字段 INTO 局部变量名
   FROM 表;

   • 查看局部变量的值

   SELECT 局部变量名;

3. 对比用户变量和局部变量

   	作用域	定义和使用的位置	语法
   用户变量	当前会话	会话中的任何地方	必须加**@符号**，不用限定类型
   局部变量	BEGIN END中	只能在BEGIN END中，且为第一句话。	一般不用加@符号，需要限定类型

函数

• 创建函数

-- 案例1：统计test表的行数
CREATE DEFINER=`root`@`localhost` FUNCTION `countT`() RETURNS int(11)
BEGIN
	DECLARE count INTEGER DEFAULT 0;
	SELECT COUNT(*) INTO count
	FROM test;
	RETURN count;
END

• 查看函数

SHOW CREATE FUNCTION 函数名;

• 删除函数

DROP FUNCTION 函数名;

语法

• 创建语法

CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
	存储过程体(一组合法的SQL语句)
END

注意：

1、参数列表包含三部分：参数模式 参数名 参数类型

例如：IN stuName VARCHAR(20)

---

2、参数模式

IN：该参数可以作为输入，也就是该参数需要调用方传入值

OUT：该参数可以作为输出，也就是该参数可以作为返回值

INOUT：该参数既可以作为输入又可以作为输出

---

3、如果存储过程体仅仅只有一句话，BEGIN END可以省略存储过程体中的每条SQL语句的结尾要求必须加分号。存储过程的结尾可以使用 DELIMITER 重新设置。

语法：DELIMITER 结束标记

举例：DELIMITER $

• 空参的存储过程

CREATE DEFINER=CURRENT_USER PROCEDURE `myp1`()
BEGIN
	-- 单行的sql语句结尾必须加分号;
	INSERT INTO test(`name`) VALUES('name2'), ('name3'), ('name4');
END

-- 调用
CALL myp1();

• 创建带in模式参数的存储过程

-- 案例1：创建存储过程实现根据name，查询对应的信息
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp2`(IN selectId INT)
BEGIN
	SELECT * FROM test WHERE id=selectId;
END

-- 案例二：创建存储过程实现，查看此用户是否存在
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp3`(IN selectId INT, IN selectName VARCHAR(64))
BEGIN
	-- 声明并初始化
	DECLARE result VARCHAR(20) DEFAULT '';
	-- 赋值
	SELECT COUNT(*) INTO result 
	FROM test
	WHERE test.id=selectId AND test.`name`=selectName;
	-- 使用，回显值
	SELECT result;
END

• 创建带out模式参数的存储过程

-- 案例1：根据id，返回对应的name
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp4`(IN selectId INT, out outName VARCHAR(64))
BEGIN
	-- 赋值
	SELECT test.`name` INTO outName 
	FROM test
	WHERE test.id=selectId;
END

-- 调用
CALL myp4(2, @name);
-- 查看值 SELECT @name;

• 创建带inout模式参数的存储过程

-- 案例1：简单赋值
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp6`(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 3;
END

-- 调用
SELECT @a:=1;
SELECT @b:=2;
CALL myp6(@a, @b);

-- 案例2：输入id查询然后返回对应的name
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp5`(INOUT val VARCHAR(64))
BEGIN
	SELECT test.name INTO val 
	FROM test
	-- CONVERT(val,SIGNED)：将入参转成int类型
	WHERE test.id=CONVERT(val,SIGNED);
END

• 删除存储过程

DROP PROCEDURE 存储过程名;

• 查看存储过程

SHOW CREATE PROCEDURE 存储过程名;

• 条件语句

-- case语句：
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin 
      declare var int;  
      set var=parameter+1;  
      case var  
      when 0 then   
      set var=var*4;
      when 1 then   
      set var=var/2;
      else
      set var=var&&3;
      end case;  
     -- 输入var值
	 SELECT var;
     end

-- if-then-else语句：
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin 
      declare var int;  
      set var=parameter+1;   
      if var=0 then   
      set var=var*4;
      else
      SELECT now();
      end if; 
     -- 输入var值
	 SELECT var;
     end

• 循环语句：

-- while ···· end while
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin 
      declare var int;
			SET var := parameter;		
      WHILE var<6 DO   
      set var = var * 2;
      END WHILE;
			SELECT var;
     end

-- repeat···· end repeat：它在执行操作后检查结果，而 while 则是执行前进行检查。
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin 
      DECLARE var int;
			SET var := parameter;		
			REPEAT
      SET var = var * 2;
      UNTIL var > 8
			END REPEAT;
			SELECT var;
     end
     
--  loop ·····endloop：loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin 
      DECLARE var int;
			SET var := parameter;	
            -- LABLES 标号,相当于break，可以结束循环
			loop_lable:LOOP
      SET var = var * 3;
      IF var > 8 then
			LEAVE loop_lable;
			END IF;
			END LOOP;
			SELECT var;
     end
  
-- ITERATE 通过引用复合语句的标号,跳出本次循环:  
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc8`(in v int)
begin 
      LOOP_LABLE:loop  
      if v=3 then   
      set v=v+1;  
      ITERATE LOOP_LABLE;  
      end if;  
      SELECT DATE_FORMAT(NOW(),'%m-%d-%Y');  
      set v=v+1;  
      if v>=5 then 
      -- 相当于continue
      leave LOOP_LABLE; 
			set v=v+100; 
      end if;  
      end loop;        
			SELECT v;
     -- 输入5，返回v=6,和DATE_FORMAT(NOW(),'%m-%d-%Y')值
     end

• 批量插入案例

/**
 * loopCount：循环次数条件
 * inName：拼接的name
 */
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc20`(in loopCount int, in inName VARCHAR(64))
begin 
	DECLARE idVal BIGINT;
	DECLARE param int DEFAULT 0;	
	DECLARE nameVal VARCHAR(64);
      WHILE param < loopCount DO
			-- 当前时间戳 + 秒级别时间戳
			SET idVal = CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'), SUBSTRING(UNIX_TIMESTAMP(), 9, 10), CRC32(CONCAT(NOW(),RAND())) % 100000000);
			SET nameVal = CONCAT(param*param, inName, param);
			INSERT INTO test(ID, `NAME`) VALUES(idVal, nameVal);
			SET param=param+1;
END WHILE;
     end

索引

索引是排好序的快速查找的数据结构

语法

1. 创建索引语法

-- 索引
CREATE INDEX 索引名 ON 表名(字段)

-- 复合索引
CREATE INDEX 索引名 ON 表名(字段1, 字段2)

2. 查看索引

SHOW INDEX FROM 表名;

3. 删除索引

DROP INDEX 索引名 ON 表名;

4. ALTER删除命令

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

5. 创建表同时建立索引

CREATE TABLE `user_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(16) DEFAULT NULL,
  `last_name` varchar(16) DEFAULT NULL,
  `id_card` varchar(18) DEFAULT NULL,
  `information` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_card` (`id_card`),
  KEY `name` (`first_name`,`last_name`),
  FULLTEXT KEY `information` (`information`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

索引的使用

1. 最左前缀法则

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

举例：我在user_index表创建复合索引CREATE INDEX left_index ON user_index(first_name, last_name, id_card)：

-- 主键都会默认创建索引：PRIMARY
EXPLAIN SELECT * from user_index where id = 1;
-- 查复合索引第一个：生效
EXPLAIN SELECT * from user_index where first_name = 'a';
-- 查复合索引第一、第二个：生效
EXPLAIN SELECT * from user_index where first_name = 'a' AND last_name = 'd';
-- 查复合索引的所有：生效
EXPLAIN SELECT * from user_index where first_name = 'a' AND last_name = 'd' AND id_card = 'g';
-- 查复合索引的所有 且 顺序错乱：生效
EXPLAIN SELECT * from user_index where id_card = 'g' AND first_name = 'a' AND last_name = 'd'
-- 查复合索引的首位和末尾：生效
EXPLAIN SELECT * from user_index where first_name = 'a' AND id_card = 'g';

-- 查复合索引的第二位和末尾：不生效
EXPLAIN SELECT * from user_index where last_name = 'd' AND id_card = 'g';

2. 避免索引失效

• varchar类型查询不加单引号

• 不要在索引列进行运算操作，索引将失效

• 范围查询右边的列，不能使用索引

可以看出：在使用范围查询后，name和status是走了索引，但是范围查询status后的address没有走索引

• 尽量使用覆盖索引（已添加索引的字段），避免使用select *

但是select 未添加索引的字段列，就还是需要回表查询数据

TIP：
	using index: 使用覆盖索引的时候就会出现
	using where：在查找使用索引的情况下，需要回表去查询所需的数据
	using index condition：查询使用了索引，但是需要回表查询数据
	using index; using where：查找使用了索引，但是需要的数据都在索引列中能找到索引不需要回表查询数据。

• 用 or 分隔开的条件，如果or前的条件中的列又索引而后面的列中没有索引，那么涉及的索引都不会被用到。

first_name是索引列，information不是索引列，or会进行全盘查询，不走索引

EXPLAIN SELECT * from user_index where first_name = 'a' or information = 'd';

• 以 % 开头的like模糊查询，索引失效

如果解决是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

-- 走索引
EXPLAIN SELECT * from user_index where first_name like 'a';
EXPLAIN SELECT * from user_index where first_name like 'a%';

-- %a%不走索引
EXPLAIN SELECT last_name from user_index where first_name like '%a%';

我们可以使用覆盖索引解决索引失效

-- 以%开头模糊查询，使用select覆盖索引，走索引
EXPLAIN SELECT last_name from user_index where first_name like '%a%';

-- 不走，因为information字段没有添加索引
EXPLAIN SELECT last_name,information from user_index where first_name like '%a%';

• 如果MySQL使用索引比全表更慢，则不适用索引

以：EXPLAIN SELECT * from user_index where first_name = ‘a’;为例：

如果查询时 first_name 为 a的记录值占全表百分比很大，就可能直接进行全表查询，不走索引

但查询时 first_name 为 a的记录值倘若只有少数，就走索引更快。

---

同理也就可以解释 is NULL、is NOT NULL有时索引失效了

• in 走索引，not in 不走索引

3. 单列索引和复合索引

尽量使用符合索引，而少使用单列索引。

如果使用单列索引，一个查询关联多个单列索引时，数据库会选择一个最优索引（辨识度最高

的，比如唯一值），并不会全部使用索引。

SQL优化

基础

1. 查看SQL执行频率

比如update 1000行数据，执行频率加 1，执行次数加 1000

-- 语句执行频率
show status like 'Com_______';

-- 每个语句执行次数
show status LIKE 'Innodb_rows_%';

2. SQL解析顺序

   

   

优化命令

1. EXPLAIN分析执行计划

• explain之ID

• explain值select_type

  

• explain之type

  显示的是访问类型，以下从好到坏依次为：

  system > const > eq_ref > ref > range > index > all

  一般来说，我们需要保证至少 range 级别，最好达到 ref

  

• explain之key

  possible_keys：显示可能应用在这张表的索引，一个或多个。
  
  key：实际使用的索引，如果为NULL，则没有使用索引。
  
  key_len: 表示索引中使用的字节数，该值为索引字段最大可能长度，在不损失精确性的前提下，长度越短越好。

• explain之rows

  扫描行的数量

• explain之extra

  如果出现 using filesort、using temporary就效率有点低，需要被优化。

4. show profile分析SQL

   show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

   通过 have_profiling 参数，能够看到当前MySQL是否支持 profile

   命令如下：SELECT @@have_profiling;

-- 查看所有的执行时间
show PROFILES;

-- 查看单条的执行时间
show PROFILE  FOR QUERY Query_ID;

5. trace分析优化器执行计划

Optimizer Trace 是MySQL 5.6.3里新加的一个特性，可以把MySQL Optimizer的决策和执行过程输出成文本，结果为JSON格式，兼顾了程序分析和阅读的便利。

利用performance_schema库里面的session_status来统计innodb读取行数

利用performance_schema库里面的optimizer_trace来查看语句执行的详细信息

优化方法

1. 优化insert语句

• 如果需要对一张表多行插入数据，应尽量使用多个值表的insert语句。

insert into test values(1, 'name');
insert into test values(2, 'coderblue');

优化后的方案：

insert into test values(1, 'name'),(2, 'coderblue');

• 数据有序插入

• 在事务中进行数据插入

2. 优化order by 语句

• 两种排序方式

第一种：是通过对返回数据进行排序，也就是通常说的 filesort 排序（即不通过索引直接返回结果的排序）

第二种：通过有序索引顺序扫描直接返回有序数据，即为 using index，不需要额外排序，操作效率高。

---

where条件 和 order by 使用相同的索引并且 Order By 的顺序和索引顺序相同，并且 Order By 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。

• 举例

# first_name，last_name，id_card都添加索引

-- 不走索引
EXPLAIN SELECT * from user_index ORDER BY  first_name, last_name;
-- 使用覆盖索引，走索引
EXPLAIN SELECT first_name from user_index ORDER BY  first_name, last_name;

3. 优化group by 语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以 执行order by null 禁止排序。

4. 优化嵌套查询

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL
操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接
（JOIN）替代。

示例 ，查找有角色的所有的用户信息 :

explain select * from t_user where id in (select user_id from user_role );

explain select * from t_user u , user_role ur where u.id = ur.user_id;

连接(Join)查询之所以更有效率一些 ，是因为MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

错误集

mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by

报错信息：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_tl.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：

MySQL版本问题，有的版本默认开启了ONLY_FULL_GROUP_BY，所以导致了一些SQL无法正常执行，其实是因为group by 之后，返回的一些数据是不确定的，所以才会出现这个错误。

解决方法（两种）：

1. 使用聚合函数，例如：**ANY_VALUE(column_name)**。

2. 设置数据库，关闭ONLY_FULL_GROUP_BY。

编辑my.cnf / my.ini 文件，找到sql-mode的位置，去掉ONLY_FULL_GROUP_BY，重启MySQL服务；
如my.cnf中没有sql-mode，需要加入以下：

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启MySQL服务,查询值，已经更改

select @@global.sql_mode

mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by

报错：In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column

Error Code: 1093. You can’t specify target table ‘a’ for update in FROM clause

使用子查询的表进行update操作就报以上错误，因为mysql不支持where里面使用子查询

update student, 
  -- 子查询出来的数据做为 b表 
  (SELECT s.Ssex,s.SId FROM `student` s, `sc` c
where s.SId = c.CId
and c.SId = '01') b
set student.Ssex = '女'
-- 然后利用b的sid = 学生表的sid找到符合b表的所有数据
where student.SId = b.SId

1064错误，delete语句中 表名不能用别名

DELETE FROM tableName  t where t......;

报1064 错误，原因MySQL 中delete 语句不能给表名起别名。
另外。如果记录不存在，delete from 不会报错！