Mysql一文到底

一、安装

# 查找mysql镜像
docker search mysql
# 拉取镜像
docker pull mysql:latest
# docker启动mysql
 docker run -d -p 3306:3306 -v C:/Application/docker/mysql/conf:/etc/mysql/conf.d -v C:/Application/docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=Password2023. --name  mysql mysql:latest
 # -d 以后台方式启动
 # -p 端口映射
 # -v 数据卷挂载
 # -e MYSQL_ROOT_PASSWORD= 设置root用户初始化密码
 # --name 容器名

查看字符集

show variables like 'character%'

Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8mb3
character_set_server	utf8mb4
character_set_system	utf8mb3
character_sets_dir	/usr/share/mysql-8.0/charsets/
已生成的库表字符集如何变更

# 修改数据库的字符集
alter database db_name character set 'utf8'
# 修改数据表的字符集
alter table tbl_name convert to character set 'utf8'

但是原有的数据如果是用非’utf8’编码的话，数据本身不会改变

二、配置文件

1. 二进制日志log-bin

   log-bin中存放了所有的操作记录，可用于恢复，默认关闭

2. 错误日志log-error

   默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息

3. 慢查询日志log

   默认关闭，记录查询的sql语句（可自定义慢的概念），如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的

4. 数据文件

   linux: 默认路径 - /var/lib/mysql， 每个目录代表一个同名数据库

   Mysiam:

   • frm文件（framework） – 存储表结构
   • myd文件（data）-- 存放表数据
   • myi文件（index）-- 存放表索引

   Innodb:

   • ibdata1（data） – Innodb引擎将所有表的数据都存在/usr/lib/mysql/ibdata1

   • frm（framework） – 8.0之前存放在库的同名包下，8.0后合并在ibd文件中

     如何证明这件事？
     到存储ibd文件的目录下，执行下面的命令：

     shell ibd2sdi --dump-file=解析后的文件名.txt 要解析的表名.ibd

   • 单独存放 – 设为on后单独以table_name.ibd文件名存储在数据库对应的目录下

    show variables like 'innodb_file_per_table%';
    set innodb_file_per_table = on
   

三、用户管理和权限管理

1. 用户管理

   • 创建用户

     create user lukis identified by 'Password2023.'
     创建用户名为lukis, 密码为Password2023.的用户

   • 用户表

     查看用户
     select * from mysql.user;

     • host

       %表示所有远程通过TCP方式连接

       ip(如120.0.0.1) 表示通过指定ip地址方式进行的TCP方式的连接

       ::1 IPv6的本地地址，等同于IPv4的127.0.0.1

       localhost 本地方式通过命令行方式的连接，比如mysql -u xxx -p xxxx

     • user

       表示用户名，同一用户的通过不同方式连接的权限是不一样的

     • authentication_string

       所有密码通过MYSQLSHA1算法加密后得到的密文字符串，不可逆

     • select_priv,insert_priv等

       表示该用户的权限

   • 修改密码

     • 修改当前用户密码

       set password=password('newpassword')

     • 修改某个用户的密码

       update mysql.user set authentication_string=password('newpassword') where user = 'username'
       # 所有通过user表的修改， 必须使用该命令才能生效
       flush privileges
       

   • 修改用户名

      update mysql.user set user='newusername' where user = 'username'
      # 所有通过user表的修改， 必须使用该命令才能生效
      flush privileges
     

   • 删除用户

     drop use username

2. 权限管理

   • 授予权限

     grant 权限1，权限2，…，权限n on database.table to username@host identified by ‘password’

     eg.

     grant select,insert,update,delete, drop on db_test.* to lukis@localhost

     grant all privileges on . to lukis@‘%’ identified by ‘password’

     即使赋予了all privileges， grant_priv权限也只有root才能拥有

   • 回收权限

     revoke 权限1，权限2，…，权限n on database.table to username@host

     eg.

     revoke select,insert,update,delete, drop on db_test.* to lukis@localhost
     用户必须重新登录才能生效

   • 查看权限

     查看当前用户权限

     show grants;

     查看某用户的全局权限

     select * from mysql.user;

     查看某用户的某库的权限

     select * from mysql.db;

     查看某用户的某表的权限

     select * from mysql.table_priv;

四、杂项设置

1. 大小写问题

   show variables like 'lower_case_table_names'

   Variable_name	Value
   lower_case_table_names	2
   windows系统默认大小写不敏感，但是Linux系统是大小写敏感的

   0 - 大小写敏感

   1 - 大小写不敏感。创建的表和数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和DB进行查找

   2 - 创建的表和数据库都是依据语句上的格式存放，凡是查找都转换成小写进行

   设置变量常采用 set lower_case_table_names = 1; 的方式， 但是该变量只是readonly，所以要在配置文件中（etc/my.cnf）修改， 然后重启服务器。

   在重启数据库实例之前，必须将原来的数据库和表转换为小写，否则重启后会找不到数据库名。

2. sql_mode

   sqL_mode是一个容易被忽视的变量，如果不设置严格模式，是可以允许一些非法操作的，比如一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以在开发和测试环境也同样必须设置。

   show variables like 'sql_mode'

   

   set sql_mode = ONLY_FULL_GROUP_BY

   

   sql_mode的常用值：

   • ONLY_FULL_GROUP_BY

     对于GROUP BY聚合操作，如果在select中的列，没有在group by中出现，那么这个SQL是不合法的，因为列不在group by从句中

   • NO_AUTO_VALUE_ON_ZERO

     该值影响自增长列的插入。默认设置下，插入0或null代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那个这个选项就有用了

   • STRICT_TRANS_TABLES

     如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。

   • NO_ZERO_IN_DATE

     不允许日期和月份为零

   • NO_ZERO_DATE

     不允许插入零日期，插入零日期会抛出错误而不是警告

   • ERROR_FOR_DIVISION_BY_ZERO

     在insert和update过程中，如果数据被零除，则产生错误而不是警告，如果未给出该模式，那么数据被零除时Mysql返回null

   • NO_AUTO_CREATE_USER

     禁止grant创建密码为空的用户

   • NO_ENGINE_SUBSTITUTION

     如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，那默认的存储引擎代替，并抛出一个异常

   • PIPES_AS_CONCAT

     将"||"视为字符串的连接操作符而非或运算符，这个和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

   • ANSI_QUOTES

     启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

   • ORACLE

     设置等同：PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，NO_KEY_OPTIONS， NO_TABLE_OPTIONS，NO_FIELD_OPTIONS，NO_AUTO_CREATE_USER

五、逻辑架构

Mysql的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。

1. 连接层

   最上层的是一些客户端的连接服务，包括本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。只要完成一些类似于连接处理，授权认证及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2. 服务层

   2.1 Management Services & Utilities: 系统管理和控制工具

   2.2 SQL Interface: SQL 接口

   接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

   2.3 Parse：解析器

   SQL命令传递到解析器的时候会被解析器验证和解析

   2.4 Optimizer: 查询优化器

   SQL语句在查询之前会使用查询优化器对查询进行优化。
   eg.

   select uid,name from user where gender=1

   优化器来决定先投影还是先过滤

   2.5 Cache和Buffer：查询缓存

   如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中的数据。这个缓存是由一系列的小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等…

   缓存 -> read; 缓冲 -> write

3. 引擎层

   存储引擎层真正负责了Mysql中数据的存储和提取，服务器通过API与存储引擎进行通信。不通的存储引擎具有功能不同，这样我们可以根据自己的实际需要进行选取

4. 存储层

   数据存储层，主要是将数据存储运行于裸机设备的文件系统之上，并完成与存储引擎的交互。

查询流程:

六、存储引擎

1. 查看命令

   查看已提供的存储引擎

   show engines

   查看默认的存储引擎

   show variables like '%storage_engine%'

2. 各个引擎简介

   • InnoDB

     InnoDB 从 MySQL 5.5 开始成为了 MySQL 默认的事务型存储引擎， InnoDB 支持事务，它具有提交、回滚和崩溃恢复等能力来保护用户数据。其内部采用 MVCC 机制来实现高并发，支持行级锁，实现了 SQL 92 的四个标准隔离级别。为了保证数据的完整性，InnoDB 引擎还支持外键引用来实现完整性约束。

   • MyISAM

     MYISAM 引擎不支持事务和行级锁（改表时会锁住整个表），其设计简单，数据以紧密格式存储，在某些场景如果可以忍受数据丢失（比如日志收集），那么也可以考虑使用 MyISAM 引擎。MyISAM 引擎拥有较高的插入和查询速度。

   • Archive

     这种引擎只支持 insert 和 select 两种操作，而且其在 5.1 版本之前也不支持索引。Archive 引擎会利用 zlib 算法对插入的数据进行压缩，所以正如其名字一样，比较适合于存储归档数据（低访问量，大数据），比如日志和数据采集类应用。

   • Memory

     Memory 引擎所有的数据都保存在内存中，所以其速度是非常快的，不过因为其数据保存在内存中，重启之后数据会全部丢失（表结构不会丢失）。Memory 引擎表可以用于缓存，因为其支持 Hash 索引，所以查找速度非常快，但是 Memory 表也只支持表级锁，因此并发的写入性能比较低。

   • CSV

     CSV 引擎可以将普通的 CSV 文件作为 MySQL 表来存储，不过需要注意的是，其不支持索引。

   • Federated

     Federated 引擎本身并不存储数据，仅仅起到了一个代理作用，它会创建一个远程到 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，该引擎默认是被禁用的。

3. MyISAM 和 InnoDB

   对比项	MyISAM	InnoDB
   主外键	不支持	支持
   事务	不支持	支持
   行表锁	表锁	行锁（适合高并发）
   缓存	只缓存索引	缓存索引和真实数据
   表空间	小	大
   关注点	性能	事务
   索引	B Tree	B+Tree

七、性能下降 慢SQL

执行时间长，等待时间长

1. 查询数据过多

2. 关联太多表 - join太多表

   join原理 - 用A表的每一条数据去扫描B表所有数据。

3. 没有使用到索引

   • 单值索引
   • 复合索引， 一般情况下复合索引优先被使用，但有些情况下就算有索引执行时也不会被使用

4. 服务器参数调优

八、Join查询

select distinct 

  <select_list>

from

  <left_table> <join_type>

join <right_table> on <join_condition>

where

  <where_condition>

group by

  <group_by_list>

having

  <having_condition>

order by

  <order_by_condition>

limit <limit_number>

优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的顺序。

1. from <left_table>
2. on <join_condition>
3. <join_type> join <right_table>
4. where <where_condition>
5. group by <group_by_list>
6. having <having_condition>
7. select
8. distinct <select_list>
9. order by <order_by_condition>
10. limit <limit_number>

建表sql

create table t_dept (
    id int(11) not null auto_increment,
    dept_name varchar(30) default null,
    address varchar(40) default null,
    primary key (id)
)engine=innodb auto_increment=1 default charset=utf8;

create table t_emp (
    id int(11) not null auto_increment,
    name varchar(30) default null,
    age int(3) default null,
    dept_id int(11) default null,
    primary key (id),
    key (dept_id)
)engine=innodb auto_increment=1 default charset=utf8;

insert into t_dept(dept_name, address) value ('华山', '华山');
insert into t_dept(dept_name, address) value ('丐帮', '洛阳');
insert into t_dept(dept_name, address) value ('峨眉', '峨眉山');
insert into t_dept(dept_name, address) value ('武当', '武当山');
insert into t_dept(dept_name, address) value ('明教', '光明的');
insert into t_dept(dept_name, address) value ('少林', '嵩山');

insert into t_emp(name, age, dept_id) value ('岳不群', 50, 1);
insert into t_emp(name, age, dept_id) value ('风清扬', 90, 1);
insert into t_emp(name, age, dept_id) value ('令狐冲', 24, 1);
insert into t_emp(name, age, dept_id) value ('洪七公', 70, 2);
insert into t_emp(name, age, dept_id) value ('乔峰', 35, 2);
insert into t_emp(name, age, dept_id) value ('灭绝师太', 70, 3);
insert into t_emp(name, age, dept_id) value ('周芷若', 20, 3);
insert into t_emp(name, age, dept_id) value ('张三丰', 100, 4);
insert into t_emp(name, age, dept_id) value ('张无忌', 25, 5);
insert into t_emp(name, age, dept_id) value ('韦小宝', 25, null);

常见的7中join

1. A、B共有

select * from t_emp a inner join t_dept b on a.dept_id = b.id;

2. A、B共有 + A独有

select * from t_emp a left join t_dept b on a.dept_id = b.id;

3. A、B共有 + B独有

select * from t_emp a right join t_dept b on a.dept_id = b.id;

4. A的独有

select * from t_emp a left join t_dept b on a.dept_id = b.id where b.id is null;

5. B的独有

select * from t_emp a right join t_dept b on a.dept_id = b.id where a.dept_id is null;

6. AB全有

select * from t_emp a left join t_dept b on a.dept_id = b.id
union
select * from t_emp a right join t_dept b on a.dept_id = b.id;

7. A独有 + B独有

select * from t_emp a left join t_dept b on a.dept_id = b.id where b.id is null
union
select * from t_emp a right join t_dept b on a.dept_id = b.id where a.dept_id is null;

增加掌门字段

alter table t_dept add ceo int(11);
update t_dept set ceo = 2 where id = 1;
update t_dept set ceo = 4 where id = 2;
update t_dept set ceo = 6 where id = 3;
update t_dept set ceo = 8 where id = 4;
update t_dept set ceo = 9 where id = 5;

求各个门派对应掌门人

select * from t_dept a left join t_emp b on a.ceo = b.id;

求所有掌门人平均年龄

select avg(a.age) from t_emp a inner join t_dept b on a.id = b.ceo;

九、索引

1. 什么是索引？

   官方：索引是帮助mysql高效获取数据的数据结构

   简单理解为 "排好序的快速查找数据的数据结构"

   在数据之外，数据库系统还维护着满足特定查找算法的数据结构， 这种数据结构以某种方式引用（指向）数据，这样就可以在这种数据结构实现高级查找算法，这种数据结构就是索引。这是索引可能存在的一种方式：

   

   左边是数据表，共两列七条记录，最左边的是数据记录的物理地址。

   为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内取到相应数据，从而快速检索出符合条件的记录。

   note:
   二叉树 - 两边不平衡
   B树 - 可以两端自动调节，使两端趋于平衡状态（myisam），但插入、修改时会不断调整平衡，消耗性能， 从而说明索引不是越多越好
   B+数 - 与B树相似，但是只在叶子节点存放数据，即查询每一个数据都需要从根节点走到叶子节点

   结论： 除数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

   一般来说，索引文件也很大，往往以索引文件的形式存储在磁盘中。如果没有特别指定，我们常说的索引都是指B树（多路查找树）结构组织的索引，当然还有哈希索引等。

2. 优势

   • 类似大学图书馆建书目索引，提高数据检索效率，降低数据库IO成本。
   • 通过索引列对数据库进行排序，降低数据排序的成本，降低CPU的消耗。

3. 劣势

   • 索引也是一张表，该表保存了索引字段以及指向实体表的记录，所以索引也是需要占用空间的。
   • 虽然索引大大提高了查询效率，同时却会降低更新表的速度， 因为更新表时，mysql不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，都需要调整因为更新所带来的键值变化后的索引信息
   • 索引只是提效率的一个因素，如果表有大量数据，就需要花时间设计表结构，建立最优秀的索引，优化查询语句

4. B树原理

每个磁盘块包括几项，键值，数据和指针，非叶子节点也存放着data，这是和B+树相比一个重要的区别，而每页的存储空间是有限的，如果data比较大，会导致每个节点的key存储的较少，当数据量较大的时候，同样会导致B树很深，从而增加了磁盘 IO 的次数，进而影响查询效率。

5. B+树原理

   

   B+树的data只存在于叶子节点，非叶子节点只作索引使用，在内存有限的情况下，每页可以存放更多的key, 查找效率会更高。

6. B树和B+树的区别

   在内存有限的情况下，B+树永远比B树好，无限内存则后者方便。

   • B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树非叶子节点只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

   • 在B树中，越靠近根节点的记录查找时间越快，只要找到了关键字既可确定记录的存在，而B+树每个记录的的查找时间基本一致，都需要从根节点走到叶子节点，而且叶子节点还要比较关键字。从这角度看B树的性能好像要比B+树好，而实际应用中确实B+树的性能要比B树好些，因为B+树的非也自己欸但不存放实际数据，每个节点可容纳的元素比B树多，树高比B树小，这样带来的好处的减少磁盘IO次数。尽管B+树查找一个记录所需要的比较次数要比B树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便范围查找。

7. 聚簇索引和非聚簇索引

   聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

   “聚簇”表示数据行和相邻的键值聚簇的存储在一起。

   按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量IO时间。

   为了充分利用聚簇索引的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议使用无序的id, 如UUID。

8. 主键索引

   设定主键后数据库会自动创建索引，innodb为聚簇索引。

   create table customer(
   id int(10) auto_increment,
   customer_no varchar(200),
   customer_name varchar(200),
   primary key (id)
   );
   

   使用auto_increment关键字的列必须有索引（只要有索引就行）

   create table customer(
   id int(10),
   customer_no varchar(200),
   customer_name varchar(200),
   primary key (id)
   );
   

   单独建主键索引

   alter table customer add primary key customer(customer_no)

   删除主键索引

   alter table customer drop primary key

9. 单值索引

   除innodb主键默认是聚簇索引外，innodb的索引都采用的B+树， myisam则都采用的B树索引。

   create table customer(
    id int(10) auto_increment,
    customer_no varchar(200),
    customer_name varchar(200),
    primary key (id),
    key (customer_name)
    );
   

   单独建单值索引

create index customer_idx on customer(customer_name);

删除单值索引

drop index customer_idx on customer

10. 唯一索引

    索引的值必须唯一，但允许有空值

    create table customer(
    id int(10) auto_increment,
    customer_no varchar(200),
    customer_name varchar(200),
    primary key (id),
    key (customer_name),
    unique (customer_no)
    );
    

单独建唯一索引

create unique index customer_idx on customer(customer_no);

删除唯一索引

drop index customer_idx on customer

11. 复合索引

    即一个索引包含多列，在数据库操作期间，复合索引比单值索引所需要的开销更小。

    create table customer(
    id int(10) auto_increment,
    customer_no varchar(200),
    customer_name varchar(200),
    primary key (id),
    key (customer_name),
    unique (customer_no),
    key (customer_no, customer_name)
    );
    

    单独建索引

    create index customer_idx on customer(customer_no，customer_name);

    删除索引

    drop index customer_idx on customer

12. 基本语法

    创建
    alter <table> add [unique] index <index name> on <column name>

    删除
    drop index <index name> on <table>

    查看
    show index from <table>

13. 哪种情况需要创建索引

    • 主键自动建立唯一索引

    • 频繁作为查询条件的字段也应该创建索引

    • 查询中于其他表关联的字段，外键关系建立索引

    • 单键/组合索引的选择问题？在高并发下情绪创建组合索引

    • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

    • 查询中统计或分组的字段

14. 哪种情况不需要创建索引

    • 表记录太少

    • 经常增删改的表，跟新表时不仅要保存数据，还要保存一下索引文件

    • where条件里用不到的字段

    • 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，（如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）

十、性能分析

1. 常见瓶颈

   • CPU：SQL中对大量数据进行比较、关联、排序、分组

   • IO：实例内存满足不了缓存数据或排序等需要导致大量物理IO；查询执行效率低，扫描过多数据行

   • 锁：不适宜的锁设置，导致线程阻塞，性能下降；死锁，线程之间交叉调用资源，导致死锁

   • 服务器硬件性能瓶颈： top,free,iostat和vmstat来查看系统的性能状态

2. Explain

   什么是explain? -> 查看执行计划

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

   能干嘛？

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

   建表语句

    create table t1(
      id int(10) auto_increment,
      content varchar(100) null,
      primary key(id)
    );
   
    create table t2(
      id int(10) auto_increment,
      content varchar(100) null,
      primary key(id)
    );
   
    create table t3(
      id int(10) auto_increment,
      content varchar(100) null,
      primary key(id)
    );
   
    create table t4(
      id int(10) auto_increment,
      content varchar(100) null,
      primary key(id)
    );
   
    insert into t1(content) values (concat('t1_', floor(1+rand()*1000)));
    insert into t2(content) values (concat('t2_', floor(1+rand()*1000)));
    insert into t3(content) values (concat('t3_', floor(1+rand()*1000)));
    insert into t4(content) values (concat('t4_', floor(1+rand()*1000)));
   

   各字段解释

   • id

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

     三种情况

     • id相同，执行顺序由上至下

       先执行where后的第一条语句t1.id = t2.id, 通过 ti.id 关联 t2.id。而 t2.id 的结果建立在 t2.id = t3.id的基础之上

       

     • id不同，如果是子查询，id的序号会递增，id值越大，优先级越高

       

     • id相同不同，同时存在

       id相同可以认为是同一组，由上往下顺序执行；

       在所有组中，id值越大，优先级越高，越先执行

   • select_type

     

     主要是用来区别普通查询，联合查询，子查询等的复杂查询

     1. SIMPLE

        简单的select查询，查询中不包含子查询或union

     

     2. PRIMARY

        查询中若包含任何复杂的子部分，最外层查询则标记为Primary

        

     3. DERIVED

        即查询通过子查询查出来的临时表

     4. SUBQUERY

        在select或where列表中包含了子查询

     

     5. DEPENDENT SUBQUERY

        在select或where列表中包含了子查询，子查询基于外层
        DEPENDENT SUBQUERY: 多值
        SUBQUERY： 单值

     6. UNCACHEABLE SUBQUERY

        无法被缓存的子查询

     7. UNION

        若第二个select出现在union之后，则标记为union；

        若union包含在from子句的子查询中，外层select则标记为derived

        

   • table

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

   • type

     访问类型，是一个较为重要的指标，从好到坏依次是：

     system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > all

     一般来说，得保证查询至少达到range级别，最好能达到ref

     1. system

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

     2. const

        表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只匹配一行数据，所以很快，如将主键至于where列表中，mysql就能将该查询转换为一个常量。

     

     3. eq_ref

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

     

     4. ref

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

        

     

     5. range

        只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引

        一般就是在你的where语句中出现了between，<, >, in等的查询

        这种范围扫描索引比全表扫描好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引

        

        

     6. index

        full index scan, index与all的区别为index只遍历索引树，这通常比all快，因为索引文件一般比数据文件小。即都是读全表，但是index是从索引中读取，而all从磁盘中读取

     

     

     7. all

        full table scan, 将遍历全表以找到匹配的行

        

     8. index_merge

        在查询过程中需要多个索引组合使用，通常出现在有or的关键字的sql中

        

     9. ref_or_null

        对于某个字段急需要关联条件，也需要null值的情况下，查询优化器会选择用ref_or_null连接查询

        

     10. index_subquery

         利用索引关联子查询，不再全表扫描

     11. unique_subquery

         类似于index_subquery，子查询中的索引唯一

   • possible_keys

     显示可能能用在这张表中的索引，一个或者多个，查询涉及的字段上若存在索引，则索引会被列出，但不一定会被使用

   • key

     实际上使用的索引。如果为null，则没有使用索引

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

   • key_len

     表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度

     key_len字段能帮你检查是否充分的利用上了索引

   • ref

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

   • rows

     表示mysql认为它执行查询时必须检查的行数 - 越少越好

   • extra

     1. Using filesort

        说明mysql会对数据使用一个外部的索引排序，而不是按照表内是索引顺序进行读取，Mysql中无法利用索引完成的排序操作成为“文件排序”

        
        
        

        查询中排序的字段，排序字段通过索引去访问将会大大提高排序速度。

        分情况：当通过前面的查询语句筛选大部分条件后,只剩下很少的数据，Using filesort性能影响不大。

     2. Using temporary

        使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于order by和分组查询group by

     3. Using index

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

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

        如果没有同事出现using where，表明索引只是用来读取数据而非利用索引执行查找

     4. Using where

        表示使用了where过滤

     5. using join buffer

        使用了连接缓存

     6. impossiable where

        where 子句的值总是false, 不能用来获取任何元组

十一、查询优化

1. 使用索引

   create table staffs(
    id int primary key auto_increment,
    name varchar(24) null default '',
    age int not null default 0,
    pos varchar(20) not null default '',
    add_time timestamp not null  default current_timestamp
   )charset utf8;
   
   insert into staffs(name, age, pos, add_time) values('z3', 22, 'manager', now());
   insert into staffs(name, age, pos, add_time) values('July', 23, 'dev', now());
   insert into staffs(name, age, pos, add_time) values('2000', 23, 'dev', now());
   insert into staffs(name, age, pos, add_time) values(null, 23, 'dev', now());
   
   select * from staffs;
   alter table staffs add index idx_staffs_nameAgePos(name, age, pos);
   

   • 全值匹配

     explain select * from staffs where name = 'July';
     explain select * from staffs where name = 'July' and age = 25;
     explain select * from staffs where name = 'July' and age = 25 and pos = 'dev';
     

     

   • 最佳左前缀法则

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

     and 忽略左右关系，即使没有按顺序编写sql, 由于优化器的存在，也会自动优化

     当使用了覆盖索引的方式时，select name,age,id from staffs where age = 10, 即使没有使用name开头，也会使用 idx_staffs_nameAgePos 索引，即select后的字段有索引，where后的字段也有索引，则无关执行顺序。

     

     explain select * from staffs where age = 10 and pos = 'dev';
     explain select * from staffs where pos = 'dev';
     explain select * from staffs where name = 'July';
     

     

   • 不在索引列做任何操作（计算，函数，类型转换等），会导致索引失效而转向全表扫描

     explain select * from staffs where left(name,4) = 'July';
     

     

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

     范围 - 若有索引测能使用到索引, 范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个索引时才会失效，若是不同索引则不会失效)

     explain select * from staffs where name = 'July';
     explain select * from staffs where name = 'July' and age = 20;
     explain select * from staffs where name = 'July' and age = 20 and pos = 'dev';
     explain select * from staffs where name = 'July' and age > 20 and pos = 'dev';
     

     

   • 尽量使用覆盖索引（只访问索引的的查询（查询列和索引列一致））,减少select *

     explain select name, age, pos from staffs where name = 'July' and age = 20 and pos = 'dev';
     explain select * from staffs where name = 'July' and age = 20 and pos = 'dev';
     

     

   • 使用不等于时（!=, <>）无法使用索引会导致全表扫描

     8.0版本后，会使用索引

     explain select * from staffs where  age != 10 and name = 'July' ;
     

     优化器会优化顺序，仍可使用索引

     

   • is not null 也无法使用索引，但是is null是可以使用索引的

     8.0后is not null也可以使用索引，但是type的range

     

   • like 以通配符开头会导致索引失效而变成全表扫描

     

   • 字符串不加单引号导致索引失效

     

   • 少用or， 用它来连接时会索引失效

     

2. 单表查询优化

   create table if not exists article(
    id int(10) unsigned not null primary key auto_increment,
    author_id int(10) unsigned not null,
    category_id int(10) unsigned not null,
    views int(10) unsigned not null,
    comments int(10) unsigned not null,
    title varbinary(255) not null,
    content text not null
   );
   
   insert into article(author_id, category_id, views, comments, title, content)
   values
   (1, 1, 1, 1, '1', '1'),
   (2, 2, 2, 2, '2', '2'),
   (3, 3, 3, 3, '3', '3');
   
   select * from article;
   

   查询category_id 为1且comments大于1的情况下，views最多的article_id。

   explain select id, author_id from article where category_id = 1 and  comments > 1 order by views desc limit 1;
   

   

   结论：显然type为all,即最坏的情况，而且Extra里还出现了Using filesort，也是最坏的情况。

   开始优化：

   1. 创建索引

      create index idx_article_ccv on article(category_id, comments, views);
      

   2. 第二次 explain

      

      type 变成了 range是可以忍受的，但是Extra里仍使用Using filesort是无法接受的。

   3. 删除第一次的索引，重建索引

      drop index idx_article_ccv on article;
      create index idx_article_ccv on article(category_id, views);
      

   4. 第三次 explain

      

3. 关联查询优化

   create table class(
    id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (id)
   );
   create table book(
    bookid int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key (bookid)
   );
   insert into class(card) values(floor(1+(rand() * 20)));
   insert into book(card) values(floor(1+(rand() * 20)));
   

   explain select * from class left join book on class.card = book.card;

   

   添加索引优化

   create index idx_book on book(card);
   

   删除索引重新创建

   drop index idx_book on book;
   create index idx_class on class(card);
   

   

   • 保证驱动表的join字段已经被索引
   • left join时，选择小表做驱动表，大表做被驱动表
   • inner join时，mysql会自己帮你把小结果集的表选为驱动表
   • 子查询尽量不要放在被驱动表，有可能使用不到索引

4. 子查询优化

   有索引的情况下，用inner join是最好的，其次是in， exists最糟糕
   无索引的情况下
   小表驱动大表，因为join的方式需要distinct, 没有索引 distinct消耗性能比较大
   所以exists性能最佳，in其次
   大表驱动小表，in 和 exists性能接近，都比较糟糕，inner join优先使用了 join buffer，所以快很多，如果left join则最慢

5. order by关键字优化

   order by子句，尽量使用index方式进行排序，避免使用filesort排序，index 排序效率高，它指mysql扫描索引本身完成排序。

   order by满足两情况，会使用index排序

   • order by语句使用索引最左前列
   • 使用where子句和order by子句条件列组合满足索引最左前列
   • where子句中如果出现索引范围查询会导致order by索引失效

   filesort

   双路排序

   mysql 4.1之前使用双路排序，字面意思是两次扫描磁盘，最终得到数据

   读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的指重新从列表中读取对应的数据

   单路排序

   从磁盘中读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效更快一些，避免了二次读取，并且把随机IO变成了顺序IO，但是它会使用更多的空间。

   单例排序可能会导致的问题 - 有可能去除的数据总大小超出了sort_buffer, 导致每次只能取sort_buffer容量大小的数据进行排序…从而导致多次IO

   本来想节省一次IO，没想到导致了大量的IO，得不偿失

   优化策略

   • 增大sort_buffer_size参数的设置
   • 增大max_length_for_sort_data参数的设置
   • 去掉select后不需要的字段

6. 分页查询的优化 - limit

   order by后字段有索引，sql有limit时，

   当select 索引包含字段时，显示using index

   当select后包含order by字段索引不包含的字段时，会显示 using filesort

7. group by 关键字优化

   group by实际是先排序后进行分组，遵照索引建的最佳左前缀

   当无法使用索引列，增大max_length_for_sort_data参数 + 增大sort_buffer_size参数的设置

   where高于having，能写在where限定的条件就不要取having限定

8. 去重优化

   尽量不要使用distinct关键字去重，这非常消耗性能

   可以使用group by完成去重，这能够使用到索引

十二、查询截取分析

1. 慢查询日志

   具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中。

   默认 show variables like '%slow_query_log%'

   开启 set global slow_query_log=1;

   日志分析工具： mysqldumpslow

2. show profiles

   mysql 提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优的测量

   默认情况下该参数处于关闭状态，并保存最近15次的运行结果

3. 全局查询日志

十三、Mysql锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU, RAM, IO等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对于数据库而言显得尤其重要，也更加复杂。

1. 锁的分类

   从数据的操作类型（读/写）

   • 读锁（共享锁），针对同一份数据，多个读操作可以同时进行而不会互相影响。
   • 写锁（排它锁），当前写操作没有完成前，它会阻断其他写锁和读锁。

   从数据的操作粒度

   为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了锁粒度的概念。

   一种提高共享资源并发性的方式是让锁定对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要互相之间不发生冲突即可。

   • 表锁
   • 行锁

2. 表锁（偏读）

   特点：偏向myisam 存储引擎，开销小，加锁快；无死锁；锁定粒度打，发生锁冲突的概率最高，并发度低

   myisam在执行查询语句（select）前，会自动给设计的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

   锁类型	他人可读	他人可写
   读锁	是	否
   写锁	否	否

   • 对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写请求。
   • 对myisam表的写操作（加写锁）， 会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才行执行其他进程的读写操作。

3. 行锁

   特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小最小，发生锁冲突的概率最低，并发度也最高。

   InnoDB与myisam的最大不同有两点：一是支持事务; 二是采取了行级锁

   事务及其ACID属性：

   事务是由其一组sql语句组成的逻辑处理单元，具有以下4个属性。

   • 原子性（Atomicity）: 事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行
   • 一致性（Consistent）： 在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
   • 隔离性（Isolation）: 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
   • 持久性（Durable）: 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

   并发事务处理带来的问题：

   • 更新丢失
   • 脏读
   • 不可重复读
   • 幻读

   事务的隔离级别：

   show variables like 'tx_isolation'

   

   间隙锁

   当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙”， InnoDB也会对这个"间隙"加锁，这个锁机制就是所谓的间隙锁

   危害: 因为query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围的键值之后，即使某些不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入锁定的键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

   行锁分析

   show status like 'innodb_row_lock%'

   查询正在被锁阻塞的sql语句
   select * from information_schema.INNODB_TRX\G

   优化建议：

   • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁强制升级为表锁
   • 尽可能较少检索条件，避免间隙锁
   • 尽量控制事务大小，减少锁定资源量和时间长度
   • 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
   • 涉及相同表的事务，对于调用表的顺序尽量保持一致。
   • 在业务环境允许的情况下，尽可能低级别事务隔离

4. 页锁

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

十四、主从复制

1. 复制的基本原理

   slave会从master读取binlog来进行数据同步

   三步骤

   master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志时间，binary log events

   slave将master的binary log events拷贝到它的中继日志（relay log）

   slave重做中继日志中的事件，将改变应用到自己的数据库中。mysql 复制是异步的且串行化的

2. 复制的基本原则

   每个slave只有一个master

   每个slave只有一个唯一的服务器ID

   每个master可以有多个salve

3. 复制的最大问题

   延迟

4. 一主一从的常见配置

   略