MySQL快速进阶

MySQL快速进阶

1. 基础知识回顾

• MySQL介绍、安装与启动
• DDL、DML、DQL、DCL（略）
• 五种约束
• 表设计与关系
• 单表查询、多表查询（重点）
• 备份与恢复
• 事务处理与事务隔离机制

2. 练习用的SQL

-- 创建雇员表
CREATE TABLE emp(
    empno INT PRIMARY KEY, -- 编号需要自己指定，因此这里也没有用auto_increment
    ename VARCHAR(50) NOT NULL,
    job VARCHAR(50) NOT NULL,
    mgr INT,
    hiredate DATE NOT NULL,
    sal DECIMAL(7,2) NOT NULL,
    comm decimal(7,2),
    deptno INT NOT NULL
) ;
-- 添加数据
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 创建部门表
CREATE TABLE dept(
    deptno INT, -- 编号需要自己指定，因此这里也没有用auto_increment
    dname varchar(14) NOT NULL,
    loc varchar(13) NOT NULL
);
-- 添加数据
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

2. 视图

2.1. 视图介绍

视图（View）是一个基于一个表或多个表的逻辑表，视图本身不包含任何数据。通俗来说，可以把视图看成是虚拟的表，只是一个查询语句的结果，它的数据最终是从表中获取的，这些表通常称为源表或基表。当基表的数据发生变化时，视图里的数据同样发生变化。它的产生主要出于以下几个原因：

• 简化查询

  把复杂的语句做成视图，后期可直接调用视图，从而简化查询

• 增加安全性

  可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等

2.2. 创建或修改视图

• 格式

  CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)]
  AS 
  	select_statement
  	[WITH [CASCADED | LOCAL] CHECK OPTION]
  

  CREATE OR REPLACE表示如果视图存在就修改，视图不存在就创建。

• 示例

  CREATE OR REPLACE VIEW emp_view
  AS
  	SELECT e.ename, e.job, e.hiredate, d.dname
  	FROM emp e, dept d
  	WHERE e.deptno=d.deptno
  

  创建的视图可以使用像表一样查询

  SELECT ename, job, hiredate, dname FROM emp_view;
  

2.4. 删除视图

• 格式

  DROP VIEW view_name [, view_name] ...
  

  可以一次删除一个或者多个视图。

• 示例

  DROP VIEW emp_view;
  

3. 存储过程

3.1. 存储过程介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。它的产生主要出于以下几个原因：

• 简化复杂的操作

  一人编写多人调用。

• 增加数据独立性

  与视图的效果类似，利用存储过程可以把数据库基础数据和程序（或用户）隔离开来，当基础数据的结构发生变化时，可以修改存储过程，这样对程序来说基础数据的变化是不可见的，也就不需要修改程序代码了。

• 提高性能

  完成一项复杂的功能可能需要多条SQL，同时SQL每次都需要编译，而存储过程可以包含多条SQL语句，而且创建完成后只需要编译一次，以后就可以直接调用，从这方面来看存储过程可以提高性能。如果程序语言要实现某项比较复杂的功能，它会多次连接数据库，在使用存储过程的情况下，程序只需连接一次就能达到目的。

3.2. 存储过程格式

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

3.3. 简单的存储过程

1. 声明：

   DELIMITER $$
   
   CREATE PROCEDURE pro1()
   BEGIN
   	INSERT INTO dept VALUES(50, 'TEST', 'TEST');
   	SELECT * FROM dept ORDER BY deptno DESC LIMIT 3;
   END $$
   
   DELIMITER ;
   

2. 调用：

   CALL pro1();
   

3.4. 带参数的存储过程

1. 声明：

   DELIMITER $$
   
   CREATE PROCEDURE pro2(IN deptno INT, IN dname VARCHAR(14), IN loc VARCHAR(13), OUT dcount INT)
   BEGIN
   	INSERT INTO dept VALUES(deptno, dname, loc);
   	SELECT COUNT(*) INTO dcount FROM dept;
   END $$
   
   DELIMITER ;
   

2. 调用：

   CALL pro2(60, 'TEST2', 'TEST2', @dept_count);
   SELECT @dept_count;
   

3.5. 带流程控制的存储过程

1. 声明：

   DELIMITER $$
   
   CREATE PROCEDURE pro3(IN insertCount INT)
   BEGIN
   	# 变量定义要放在存储过程逻辑的开始位置
   	DECLARE i INT DEFAULT 1;
   	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
   	DECLARE startIndex INT DEFAULT 1;
   	DECLARE len INT DEFAULT 1;
   
   	### 逻辑1：创建一张测试表 ###
   	CREATE TABLE stringcontent(
   		id INT AUTO_INCREMENT PRIMARY KEY,
   		content VARCHAR(20)
   	);
   
   	### 逻辑2：根据传入参数决定向测试表写多少表数据 ###
   	WHILE i <= insertCount DO
   		# 将来字符串截取时要用到的结束位置
   		SET len = FLOOR(RAND() * (20 - startIndex + 1) + 1);
   		# 将来字符串截取时要用到的开始位置
   		SET startIndex = FLOOR(RAND() * 26 + 1);
   		INSERT INTO stringcontent(content) VALUES(SUBSTR(str, startIndex, len));
   		SET i = i + 1;
   	END WHILE;
   END $$
   
   DELIMITER ;
   

   以上演示的是while结构，除此之外，肯定还有if结构。

2. 调用：

   CALL pro3(3);
   

   存储过程调用成功后，可查看stringcontent表，数据是否已写入。

3.6. 查询存储过程

• 查看存储过程状态

  SHOW PROCEDURE STATUS;
  

• 查看存储过程定义

  SHOW CREATE PROCEDURE pro1;
  

3.7. 删除存储过程

• 格式

  DROP PROCEDURE procedure_name;
  

  一次只能删除一个存储过程。

• 示例

  DROP PROCEDURE pro1;
  

3.8. JDBC调用存储过程

CallableStatement主要是调用数据库中的存储过程，CallableStatement也是Statement接口的子接口。

3.9. 存储过程与函数的区别

MySQL中还有一个知识点叫函数，它与存储过程类似（都是事先经过编译并存储在数据库中的一段SQL语句的集合，方便外部调用），但还是有以下区别：

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程。

3.10. 存储过程的实际应用

对于开发者来讲最好禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

DBA存储过程还是挺管用的。

４. 触发器

4.1. 触发器介绍

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

4.2. 触发器格式

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

4.3. 触发器测试

测试的数据表（添加部门时，会产生一条日志）：

CREATE TABLE log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255)
);

创建触发器：

DELIMITER $$

CREATE TRIGGER dept_log AFTER INSERT ON dept FOR EACH ROW
BEGIN
    DECLARE result VARCHAR(40);
    DECLARE suffix VARCHAR(20);
    SET suffix = " is created";
    SET result = CONCAT(NEW.dname, suffix);
    INSERT INTO log(content) values(result);
END $$

DELIMITER ;

NEW和OLD的使用：

根据以上的表格，可以使用一下格式来使用相应的数据：

NEW.columnname：新增行的某列数据

OLD.columnname：删除行的某列数据

触发器测试：

INSERT INTO dept VALUES(70, 'TEST3', 'TEST3');

4.4. 查看触发器

SHOW TRIGGERS;

4.5. 删除触发器

DROP TRIGGER trgger_name;

5. 索引

5.1. 索引介绍

• 索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。

• 所有MySQL列都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径，MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引（且不能更换），MEMORY和HEAP存储引擎支持HASH和BTREE索引。

• 通常数据库中指的BTREE结构，大部分就是在说B+TREE结构哦！

更直观的图

对B+TREE的理解：

1. 根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中。
2. 叶子节点中的数据按从小到大的顺序链接。
3. 叶子节点中还有指向下一个叶子节点的next指针，所以叶子节点形成了一个有序的链表，方便遍历。
4. 在B+TREE结构上对某一值查询时，最多只需要3次磁盘I/O操作。

扩展：

1. BTREE,B-TREE,B+TREE,B*TREE都是什么？

   https://blog.csdn.net/andyzhaojianhui/article/details/76988560

2. B+TREE的优势（与BTREE对比）？

   https://www.cnblogs.com/dongguacai/p/7241860.html

• 索引分类

  • 单列索引（普通索引、唯一索引、主键索引）

  • 组合索引

  • 全文索引（不常用）

    全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，基本都不用这种索引方式，如果有类似的需求也只会采用一些全文检索框架，如Solr、ElasticSearch等。

  • 空间索引（不常用）

    空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。并要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

5.2. 创建索引

5.2.1. 创建普通索引

• 直接创建索引

  • 格式

    CREATE INDEX index_name ON table_name(column_name(length));
    

  • 示例

    CREATE INDEX emp_ename ON emp(ename(50));
    

• 修改表结构的方式添加索引

  • 格式

    ALTER TABLE table_name ADD INDEX index_name(column_name(length))
    

  • 示例

    ALTER TABLE emp ADD INDEX emp_ename(ename(50))
    

• 创建表的时候同时创建索引

  CREATE TABLE table_name(  
    id INT AUTO_INCREMENT PRIMARY KEY,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (column_name(length))  
  );
  

5.2.2. 创建唯一索引

与创建普通索引类似，不同的是：索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

• 直接创建索引

  • 格式

    CREATE UNIQUE INDEX index_name ON table_name(column_name(length));
    

  • 示例

    CREATE UNIQUE INDEX emp_dname ON emp(ename(50));
    

• 修改表结构的方式添加索引

  • 格式

    ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name(length))
    

  • 示例

    ALTER TABLE emp ADD UNIQUE INDEX emp_ename(ename(50))
    

• 创建表的时候同时创建索引

  CREATE TABLE table_name(  
    id INT AUTO_INCREMENT PRIMARY KEY,   
    username VARCHAR(16) NOT NULL,  
    UNIQUE INDEX [indexName] (column_name(length))  
  );
  

5.2.3. 创建主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候就会同时创建主键索引：

CREATE TABLE table_name(  
  id INT AUTO_INCREMENT PRIMARY KEY,   
  username VARCHAR(16) NOT NULL,  
  UNIQUE INDEX [indexName] (column_name(length))  
);

5.2.4. 创建组合索引

组合索引就是在多个字段上创建一个索引，这里演示在创建表的时候创建组合索引：

CREATE TABLE table_name(  
  id INT AUTO_INCREMENT PRIMARY KEY,   
  username VARCHAR(16) NOT NULL,  
  age INT NOT NULL,
  [UNIQUE] INDEX [indexName] (column_name(length),column_name(length),column_name(length)...) 
);

使用组合索引时遵循最左前缀原则：

组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，不明白没关系，举几个例子就明白了，例如，假如有一张名为t3的表，在这张表里有id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询

• 在t3表中，查询id和name字段（会使用索引）

  EXPLAIN SELECT * FROM t3 WHERE id = 1 AND name = 'joe'\G;
  

• 在t3表中，查询(age，name)字段，这样就不会使用索引查询。

  EXPLAIN SELECT * FROM t3 WHERE age = 3 AND name = 'bob'\G;
  

5.3. 查看索引

SHOW INDEX FROM table_name;
# 或
SHOW KEYS FROM table_name;

5.4. 删除索引

DROP INDEX index_name ON table_name;

5.5. 索引的优缺点和使用原则

5.5.1. 优缺点

• 优点

  大大提高查询效率

• 缺点

  • 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
  • 索引也需要占空间

5.5.2. 使用原则

通过以上提及的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。 这里提几点索引的使用原则，，创建索引的时候请尽量符合这些原则：

• 不要过度使用索引，严格要求只有被频繁查询的字段才设置索引 。
  • 每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。
  • 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所化的时间越长。
  • 对于那些查询中很少涉及的列、重复值比较多的列、对于经常存取的列避免建立索引 。
• 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。都知道索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
• 经常用在WHERE子句中的数据列。
• 经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
• 索引的列的基数越大，效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只有”M”和”F”，则对此列进行索引没有多大用处。
• 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用，因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
• 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就按照唯一索引的顺序保存。既没有主键又没有唯一索引，表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或内部列进行访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键。InnoDB表的普通索引会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

注：在创建表时，预估那些被经常查询的字段，给其创建索引，不好预估就不要乱加，等SQL优化时再加！

6. SQL优化

• 今天，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构，对操作数据库时（尤其是查表时的SQL语句），我们都需要注意数据操作的性能。
• 另外，以下的测试是基于MySQL 5.5的，在不同版本中，具体效果以测试为准！

6.1. 查看MySQL服务器状态信息

• 通过SHOW [SESSION | GLOBAL] STATUS命令可以提供服务器状态信息，共300多条。

  SESSION为当前连接的统计结果，GLOBAL为数据库启动至今的统计结果，如果不写，默认为SESSION

• 比较关心的几个统计参数

  • Com_select：执行SELECT操作的次数，一次查询只累加1

    注：感觉使用SHOW STATUS LIKE 'Com_select'测试不太准，执行一次就累加了2？原因未知！

  • Com_insert：执行INSERT操作的次数，对于批量插入的INSERT操作，只累加1

  • Com_update：执行UPDATE操作的次数

  • Com_delete：执行DELETE操作的次数

  • Connections：试图连接MySQL服务器的次数

  • Uptime：服务器工作时间

  • Slow_queries：慢查询次数

    • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

    • 慢查询日志：https://www.cnblogs.com/saneri/p/6656161.html

      慢查询设置：

      long_query_time = 2 
      log_slow_queries = /data/mysql/slowlog.log
      

6.2. 查询语句分析

EXPLAIN命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，EXPLAIN可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

• id：这是SELECT的查询序列号

• select_type：SIMPLE表示简单表（不使用连接或子查询），PRIMARY表示子查询的外部查询，这里特别要注意SUBQUERY（子查询比较消耗，一般优化方式都是用连接查询代替）和DEPENDENT SUBQUERY（子查询依赖会严重消耗性能）。

  子查询依赖的解释：

  子查询的查询方式依赖于外面的查询结果。用这个例子就是，先进行子查询外部的查询，得到一个结果集，然后这个结果的每一行在跟SELECT子查询的结果集进行匹配，也就是说，外部结果集的每一行都要关联内部结果集一次，如：

  

• table：输出结果集的表

• type：表示 MySQL在表中找到所需行的方式

  从左至右，性能由最差到最好：ALL < index < range < ref < eq_ref < const

  SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

  • type=ALL，全表扫描

    
    

  • type=index，全索引扫描

    
    

  • type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符

    
    

  • type=ref，索引查询（索引既不是UNIQUE也不是PRIMARY KEY）的情况

    
    
    或

    
    

    本次测试前，在ename上添加了一个普通索引。

  • type=const，单表中最多有一个匹配行，查询起来非常迅速，通常查询条件为主键索引或唯一索引

    
    

• possible_keys：表示查询时可能使用的索引

• key：表示实际使用的索引

• key_len：使用到索引字段的长度

• rows：扫描行的数量

• Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

  该列包含MySQL解决查询的详细信息

  • Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
  • Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
  • range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。
  • Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。
  • Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  • Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。
  • Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  • Using sort_union(…), Using union(…), Using intersect(…)：这些函数说明如何为index_merge联接类型合并索引扫描。
  • Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查 询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。

6.3. 使用索引查询需要注意

索引可以提供查询的速度，但并不是使用了带有索引的字段查询都会有明显提升，使用索引时需要注意以下情况：

• 合理添加索引，不要滥加索引

• 如果MySQL估计使用全表扫描要比使用索引快，则不使用索引

• 使用组合索引时遵循最左前缀原则，不是使用的第一部分，则不会使用索引

• 使用LIKE关键字，开头不能用%，否则索引失效

  失效情况：

  
  

• 使用OR关键字，要保证OR前后的两个条件中的列都是索引列，索引才会生效，否则索引失效

  在MySQL 5.5中测试结果：

  即使两边的条件都是索引列，索引也没有生效，具体是不是低版本情况，可以到高版本上先测试一下。但现有一个解决办法就是使用UNION代替OR。

  EXPLAIN SELECT * FROM emp WHERE ename='ALLEN' UNION SELECT * FROM emp WHERE job='PRESIDENT'
  

  补充：以上两边的条件都是索引列，索引也没有生效的问题可以到切换到MyISAM引擎上试试。

• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

• IS NULL或者IS NOT NULL也会导致无法使用索引

  在MySQL 5.5中测试结果：

  IS NULL会用到索引，IS NOT NULL无法使用索引！

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

• 索引字段上使用（!= 或者 < >）判断时，可能会导致索引失效而转向全表扫描

• IN和 NOT IN 也要慎用，否则会导致全表扫描

  在MySQL 5.5中测试结果：

  IN会用到索引，NOT IN无法使用索引！

• …

6.4. 其它SQL优化总结

• SQL语句中的关键字尽量都大写字母出现

• 任何地方都不要使用 SELECT * FROM t，用具体的字段列表代替“*”，不要返回用不到的任何字段。

• 当只要一行数据时使用LIMIT 1

• 子查询优化

  子查询虽然很灵活，但是执行效率并不高。执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

• 编写MySQL语句时，插入多条数据时，优先选用INSERT INTO table_name VALUES(), (), ()…

• …

7. 表结构优化

• 创建合理的表结构

  字段类型、字段大小等

• 创建合理的索引

• 合理拆分表

  对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

• 尽量满足数据库的三范式

• 合理增加冗余字段

  设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

  注：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

• 选择合理的存储引擎

  主要就是MyISAM和InnoDB，MySQL早期默认的是MyISAM，后来默认为InnoDB。

  使用InnoDB存储引擎的相关优化

  • 禁用外键

    插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

    禁用：SET foreign_key_checks = 0;
    开启：SET foreign_key_checks = 1;

    注：正是由于有插入数据会执行对外键检查，因此现在很多数据库设计时都不建议使用外键了！

  • 禁用自动提交

    特别是插入多条语句时，需要禁止事务的自动提交，即保证了事务安全又提高了插入速度

  • 禁用唯一性检查

    唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

    禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0;

    开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

• …

8. 服务器优化

• 硬件方面

  • 服务器系统优先选用Linux。
  • 配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。
  • 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。
  • 配置高速磁盘，比如SSD。

• 服务器参数介绍

  有了基本的硬件条件，还需要通过优化MySQL服务器参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。MySQL的配置都在my.conf或my.ini文件的[mysqld]组中，常用的参数如下：

  https://www.cnblogs.com/angryprogrammer/p/6667741.html

9. MySQL主从复制

9.1. 主从复制介绍

• MySQL配置主从复制，最终想实现的就是读取分离。

  就好比Nginx反向代理，最终想实现的就是负载均衡。

• 有一种场景：电子商务网站上的商品，一般都是一次上传，无数次浏览的，说专业点也就是”多读少写”。

  
  

  如图所示，我们将一台MySQL服务器作主库（Matser），其他三台作为从库（Slave），主库只负责写数据，每次有数据更新都将同步到它所有的从库，而从库只负责读数据。这样一来，就有了两个好处：

  • 读写分离，不仅可以提高服务器负载能力，还可以根据读请求的规模自由增加或者减少从库的数量；
  • 数据被复制成了好几份，就算有一台机器出现故障，也可以使用其他机器的数据快速恢复。

  需要注意的是：

  • 在MySQL主从模式中，一台主库可以拥有多个从库，但是一个从库只能隶属于一个主库；
  • 主从复制不会阻塞Master，在同步数据时，Master可以继续处理client请求。
  • 主从复制存在的问题： 由于所有的写操作都是先在Master上操作，然后同步更新到Slave上，所以从Master同步到Slave机器有一定的延迟，当系统很繁忙的时候，延迟问题会更加严重，Slave机器数量的增加也会使这个问题更加严重。

9.2. MySQL主从复制原理

第1步，主库的更新事件(update、insert、delete)被写到binlog

第2步，从库发起连接，连接到主库

第3步，此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库

第4步，从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.

第5步，还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到从库.

9.3. 主从复制配置

配置前注意：

• 以下的测试是使用一台服务器进行模拟MySQL的主从复制，一般来说，要将MySQL主从复制运用于工程项目中，只使用一台MySQL服务器是万万不能的
• 以下配置的是一主一从。

• 第1步，使用Docker下载MySQL 5.5.55的镜像

  docker pull mysql:5.5.55
  

• 第2步，创建/data/mysql1目录存放主库信息

  再进到/data/mysql1目录中，再创建名为data的目录用于存储MySQL的数据信息

  mkdir data
  

  接着创建名为my.cnf的文件用于存储MySQL的配置信息，内容如下：

  [mysqld]
  
  # 打开binlog
  log-bin=mysql-bin
  # 服务器id标识
  server-id=1
  
  # 字符集
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci
  

• 第3步，创建/data/mysql2目录存放从库信息

  再进到/data/mysql2目录中，再创建名为data的目录用于存储MySQL的数据信息

  mkdir data
  

  接着创建名为my.cnf的文件用于存储MySQL的配置信息，内容如下：

  [mysqld]
  
  log-bin=mysql-bin
  server-id=2
  
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci
  

• 第4步，创建并运行主库容器

  docker run -d --privileged=true --name mysql1 -v /data/mysql1/data:/var/lib/mysql -v /data/mysql1:/etc/mysql/conf.d -p 33061:3306 -e MYSQL_ROOT_PASSWORD=root mysql:5.5.55
  

• 第5步，创建并运行从库容器

  docker run -d --privileged=true --name mysql2 -v /data/mysql2/data:/var/lib/mysql -v /data/mysql2:/etc/mysql/conf.d -p 33062:3306 -e MYSQL_ROOT_PASSWORD=root mysql:5.5.55
  

• 第6步，登录进主库，给从库访问授权

  GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
  FLUSH PRIVILEGES;
  

• 第7步，给从库访问授权完成后，接着查询一下主库状态

  SHOW MASTER STATUS;
  

  
  

  特别记住File和Positiong两个值，File用于从库从哪个文件读，Position用于从库从哪个位置读！

• 第8步，登录进从库，启动从库

  # 建议启动之前先停止一下从库，为了避免之前有已启动的从库而报错
  STOP SLAVE;
  
  # 设置与主库相关的配置参数（注意与主库状态一致）
  CHANGE MASTER TO master_host = '192.168.31.31',
   master_port = 33061,
   master_user = 'backup',
   master_password = '123456',
   master_log_file = 'mysql-bin.000008',
   master_log_pos = 107;
   
  # 启动从库
  START SLAVE;
  
  # 查询从库状态
  SHOW SLAVE STATUS;
  

  
  
  如上图，如果Slave_IO_State显示

  • “Waiting for master to send event” 就是成功了
  • “Connecting to master” 多半是连接不通，有可能是网络不通、密码不对、log_pos不对等问题

• 第9步，测试在主库修改数据，检查从库是否会同步

  测试成功后，根据需要自己还可以自由增长从库节点。

附录

1. 阿里巴巴Java开发规约—MySQL数据库

在线阅读地址：https://files-cdn.cnblogs.com/files/han-1034683568/阿里巴巴Java开发手册终极版v1.3.0.pdf

2. …