就业培训学习记录-day007

课堂任务

索引

索引是帮助MySQL高效获取数据的排好序的数据结构，在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高效的查找算法，这种数据结构就是索引。

索引的优势：

1. 类似于数据目录的索引，提高数据检索的效率，降低数据库的IO成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

索引的劣势：

1. 实际上索引也是一张表，该表中保存了主键与索引的字段，并指向实体类的记录，所以索引也是要占用空间的
2. 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如果对表进行insert、update、delete操作的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会因为更改所带来的键值来更新索引信息

索引分类：

1. 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
2. 联合索引，又叫复合索引，即一个索引包含多个列
3. 唯一索引，即索引列的值必须唯一，但允许有空值

查看索引

show index from table_name;

添加索引

索引在创建表的时候，可以同时创建，也可以在创建表后增加新的索引

-- 创建表时创建索引
CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,  
  sex VARCHAR(2) NOT NULL,
  INDEX [indexName] (username(length))  
);
-- 创建表后，添加新的单值索引
CREATE INDEX indexName ON table_name (column_name)
-- 创建表后，添加新的联合索引
CREATE INDEX indexName ON table_name (column_name1,column_name2,...)

删除索引

drop index index_name on tabl_name;

存储引擎

MySQL中有一个存储引擎的概念，针对不同的存储需要可以选择最优的存储引擎。
存储引擎就是存储数据、建立索引、更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库，所以存储引擎也可以称为表类型。
InnoDB是常用的存储引擎之一，也是MySQL数据库的默认存储引擎，它的主要特点有：

1. 可以通过自动增长列，方法是auto_increment。
2. 支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
3. 使用的锁为行级锁，可以支持更高的并发。
4. 支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
5. 配合一些热备工具可以支持在线热备份。
6. 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。
7. 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上。

优化SQL

步骤：

1. 查看SQL执行频率
2. 查看查询日志
3. 定位低效率的SQL查询语句

查看正在进行的查询

该命令是显示当前正在执行的查询，通常用来确认数据库的查询状态

show processlist;

explain分析执行计划

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

explain 语句;
explain select * from employees;
explain select employee_id from employees;

结果描述：

1. id
   是SQL执行的顺序的标识，SQL从大到小的执行。
   id相同时，执行顺序由上至下。
   如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
   id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

2. select_type

值	说明
SIMPLE	简单查询，不包含子查询或Union查询。
PRIMARY	查询中若包含子查询，最外层查询则被标记为主查询。
SUBQUERY	子查询。
UNION	若第二个select出现在union之后，则被标记为UNION。
UNION RESULT	从UNION表获取结果的select。

3. table
   数据来自哪张表。

4. partitions
   匹配的分区。

5. type
   表示表的连接性能，性能的由好到坏。

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
-- 掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

值	说明
NULL	MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
system	表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。
const	表示通过索引一次就找到了，const用于比较primary key或unique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
ref	非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体。
ref_or_null	类似ref，但是可以搜索值为NULL的行。
index_merge	表示使用了索引合并的优化方法。
range	只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询。
index	Full index scan，Index与All区别为，index只遍历索引树，通常比All快。因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
ALL	Full table scan，将遍历全表以找到匹配行。

6. possible_keys
   显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。

7. key
   实际使用到的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

8. key_len
   表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确度的情况下，长度越短越好。key_len显示的值为索引字段最大的可能长度，并非实际使用长度。即key_len是根据定义计算而得，不是通过表内检索得出的。

9. ref
   列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

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

11. filtered
    查询的表行占表的百分比。

12. Extra
    包含不适合在其它列中显示，但十分重要的额外信息。

值	说明
Using filesort	说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using temporary	使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by和分组查询group by。
Using index	表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where	使用了where条件。
Using join buffer	使用了连接缓存。
Impossible where	where子句的值总是false，不能用来获取任何元组。
Distinct	一旦mysql找到了与行相联合匹配的行，就不再搜索了。
Select tables optimized away	SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）。

分析SQL

-- 查看上一条SQL的开销信息
show profile;
-- 查看之前所有SQL的开销信息
show profiles;
-- 获取指定查询SQL的开销（这里举例查询第二条的开销明细） 
show profile for query 2;  

能够在做SQL优化的时候，帮助我们了解时间都耗费到哪里去了。
查看MySQL是否支持（5.0.37版本以上的才会支持）。

select version();
select @@have_profiling;

profile默认是关闭的（0关闭，1开启），查看方式如下

select @@profiling;

开启profile

set profiling=1;

避免索引失效

索引是数据库优化最常用的也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。创建索引可以提高查询效率，但是创建了索引就不一定就可以提高查询效率。

-- 创建一个联合索引来举例子
create index idx_seller_name_status_addr on tb_seller(name,status,address);

1. 全值匹配
   查询时，对应索引的列要指定具体值

select * from tb_seller where name='小米科技' and status='1' and address='西安市';

2. 最左前缀原则
   如果索引中有多列，要遵守最左前缀原则，指的是查询从索引的最左列（联合索引中的第一列）开始，并且不跳过索引中的列。
   下面的例子不符合最左原则，索引失效

explain select * from tb_seller where status='1';
explain select * from tb_seller where address='西安市';
explain select * from tb_seller where status='1' and address='西安市';

3. 范围查询右边的索引列将失效

explain select * from tb_seller where name='小米科技' and status='1';
explain select * from tb_seller where name='小米科技' and status='1' and address='西安市';
explain select * from tb_seller where name='小米科技' and status>'1' and address='西安市';

第三个例子只走了name和status的索引，address的索引没走，也就是范围条件之后的筛选条件不会使用索引。

4. 不要在索引列上进行运算操作，会导致索引失效

explain select * from tb_seller where SUBSTR(name,2,2)='小米科技';

5. 索引列不加单引号，索引会失效

explain select * from tb_seller where name='小米科技' and status='1';
explain select * from tb_seller where name='小米科技' and status=1;
explain select * from tb_seller where name='小米科技';

6. 尽量使用覆盖索引，避免使用select *
   这点并不会导致索引失效，但是使用覆盖索引能提高查询效率。见Using index

explain select * from tb_seller where name='小米科技';
explain select name,status,address from tb_seller where name='小米科技';

7. 用or分隔的条件，如果or前的条件有索引列，而后面没有索引列，那么所涉及的索引都不会被使用

explain select * from tb_seller where name='小米科技' and nickname='小米官方旗舰店';
explain select * from tb_seller where name='小米科技' or nickname='小米官方旗舰店';

8. 以%开头的模糊查询，索引失效
   下面的例子中，第二和第四个索引失效。

explain select * from tb_seller where name like '科技';
explain select * from tb_seller where name like '%科技';
explain select * from tb_seller where name like '科技%';
explain select * from tb_seller where name like '%科技%';

9. 如果MySQL评估使用索引比全表扫描慢，则不会使用索引

-- 先给address字段添加索引，以便举例
create index idx_seller_address on tb_seller(address);
explain select * from tb_seller where address='北京市'; #没有使用索引
explain select * from tb_seller where address='西安市'; #使用了索引

MySQL在查找的时候会评估一下，觉得用全表扫描要比索引快，就不会用索引。

10. in走索引，not in索引失效

explain select * from tb_seller where name in ('小米科技','千度科技');
explain select * from tb_seller where name not in ('小米科技','千度科技');

11. 尽量使用联合索引
    创建联合索引，相当于创建三个索引。

create index indx_name_sta_addr on tb_seller(name,status,address);
-- 相当于
create index indx_name_sta_addr on tb_seller(name);
create index indx_name_sta_addr on tb_seller(name,status);
create index indx_name_sta_addr on tb_seller(name,status,address);

优化order by

多字段排序，要么全升序，要么全部降序。

优化嵌套查询

可以使用select语句来创建一个单例的查询结果，然后把这个结果作为筛选条件用在另一个查询中。使用子查询可以一次性的完成逻辑上需要多个步骤才能完成的SQL操作，有些情况下可以被更高效的连接join代替。

优化分页查询

只适用于主键自增的表，可以把limit查询转换成某个位置的范围查询

select employee_id from employees order by employee_id limit 90,10;
-- 下面的写法效率更高
select employee_id from employees where employee_id>=90 order by employee_id limit 10;

使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index

在查询语句中表名的后面，添加use index来希望MySQL去使用人为建议的索引列表，就可以让MySQL不再考虑其他可用的索引。但是不一定会使用建议的索引。

drop index idx_seller_name on tb_seller; #删除索引
drop index idx_seller_name_status_addr on tb_seller; #删除索引

show index from tb_seller; #查看索引
create index idx_seller_name on tb_seller(name); #创建单列索引
create index idx_seller_name_status_addr on tb_seller(name,status,address); #创建联合索引

explain select * from tb_seller where name='小米科技'; #使用了单列索引
explain select * from tb_seller use index(idx_seller_name_status_addr) where name='小米科技'; #建议使用联合索引

ignore index

如果用户只是单纯的想让MySQL忽略一个或多个索引，可以使用ignore index提示。

force indx

强制MySQL使用一个特定的索引。

应用上优化

1. 使用数据库连接池
2. 增加缓存层，MyBatis自带一级缓存和二级缓存
3. 负载均衡
   负载均衡是一种非常普遍的优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来降低单台服务器的负载，达到优化的效果。

查询缓存（MySQL8已经废弃这个功能）

查看当前MySQL数据库是否支持查询缓存

show variables like 'have_query_cache';

查看当前MySQL是否开启了查询缓存

show variables like 'query_cache_type';

如果显示OFF，可以修改配置文件以开启查询缓存
找到MySQL配置文件my.ini，在[mysqld]中添加下面的内容，然后重启MySQL服务即可。

query_cache_size = 20M
query_cache_type = ON

查看缓存使用情况，其中Qcache_hits代表查询缓存命中次数

show status like 'Qcache%';  

查询缓存失效的情况

1. SQL语句不一致的情况下，要想命中查询缓存，查询的sql语句必须一致
2. 当查询语句中有不确定具体值的时候，则不会查询。比如now()、uuid()、rand()
3. 不使用任何表的查询语句
4. 在存储的函数、触发器或事件的主体内执行的查询

存储过程和存储函数

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

存储过程

创建语法

create procedure procedure_name()
begin
  -- PL/SQL
end;

创建存储过程

create procedure pro_hello()
begin
	select 'hello mysql';
end;

调用存储过程

call pro_hello();

查看数据库026有哪些存储过程

select * from mysql.proc where db='026';

删除存储过程

drop procedure pro_hello;

存储过程是可以编程的，可以使用变量、表达式以及控制结构。
通过declare可以定义一个局部变量，该变量的作用范围在begin...and块中。

直接赋值使用set，可以赋常量或者表达式。

通过select...into，还可以对查询到的内容赋值给变量。

if条件判断

传递参数

create procedure procedure_name(in/out/inout 参数名 参数类型)
begin
  -- PL/SQL
end;

in：该参数可以作为输入，也就是需要调用方传入值
out：该参数作为输出，也就是该参数可以作为返回值
inout：既可以作为输入参数，也可以作为输出参数

根据定义的身高变量.判断当前身高所属的身材类型

根据传入的身高变量，获取所属的身材类型（@是局部变量，@@是系统变量）

while循环，当满足条件就继续循环，计算1到n的值

repeat，当条件就退出循环

loop循环

游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括：声明游标、打开游标（open）、取游标（fetch）和关闭游标（close）。
查询emp表中的数据，并逐行显示出来

存储函数

定义一个存储函数，获取满足某种条件的emp记录总数

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的PL/SQL语句。
创建语法

create trigger trigger_name
before/after insert/update/delete
on table_name
for each row --行级触发器
begin
  -- PL/SQL
end;

关键字new、old

insert：new表示新增的数据
update：old表示修改前的数据，new表示修改后的数据
delete：old表示删除的数据

创建一个日志表，通过触发器记录emp表中的数据变更日志，包括数据的插入和修改

插入的时候记录插入的值

修改的时候记录修改的值

查看触发器

show triggers;

课后任务

实现MySQL数据库的主从复制。

参考链接

mysql主从复制（超简单）
MySQL主从复制的实现过程
mysql主从复制之异常解决--- Slave_IO_Running: NO

实际操作

我是Windows 10物理机为主服务器（MySQL 5.7.21-log），CentOS Linux release 7.5.1804 (Core)虚拟机为从服务器（MySQL 8.0.23），主IP10.27.76.69，从IP192.168.86.145

1. 修改主服务器（master）的配置文件
   在[mysqld]后添加如下内容

#服务器ID，默认是1，不能和从服务器ID一致，一般取IP最后一段
server-id=1
#要生成的二进制日记文件名称
log-bin=mysql-log-bin

2. 修改从服务器（slave）的配置文件

server-id=2
log-bin=mysql-log-bin

3. 重启主服务器和从服务器的MySQL

4. 主服务器上建立帐户并授权给slave

mysql>-- 一般不用root帐号，"%"表示所有客户端都可能连，只要帐号密码正确，此处的"%"可用具体客户端IP代替，如10.27.76.69，加强安全。
mysql>grant replication slave on *.* to 'mysync'@'%' identified by 'root'; 

5. 登录主服务器的MySQL，查询master的状态

mysql>show master status;

结果如下

+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000001 |     1357 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

6. 配置从服务器slave

mysql>change master to master_host='10.27.76.69',master_user='mysync',master_password='root',
    ->master_log_file='mysql-log-bin.000001',master_log_pos=1357;
mysql>start slave; -- 启动从服务器复制功能

7. 检查从服务器复制功能状态

mysql> show slave statusG;

结果如下

*************************** 1. row ***************************

         Slave_IO_State: Waiting for master to send event
            Master_Host: 10.27.76.69  //主服务器地址
            Master_User: mysync  //授权帐户名，尽量避免使用root
            Master_Port: 3306    //数据库端口，部分版本没有此行
          Connect_Retry: 60
        Master_Log_File: mysql-log-bin.000001
    Read_Master_Log_Pos: 1357  //同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
         Relay_Log_File: number026-relay-bin.000003
          Relay_Log_Pos: 1244
  Relay_Master_Log_File: mysql-log-bin.000001
       Slave_IO_Running: Yes    //此状态必须YES
      Slave_SQL_Running: Yes   //此状态必须YES
                      ...

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态（其中一个NO均属错误)。

8. 至此，主从服务器配置完成

9. 主服务器测试

create database test_sync;
show databases;
use test_sync;
create table hi_tb(id int,name varchar(10));
insert into hi_tb values(001,'admin');
select * from hi_tb;

10. 从服务器查看结果

show databases;
select * from hi_tb;