mysql

附一张桌面壁纸镇楼

mysql基础

1.逻辑分层

• 连接层-提供客户端的连接

• 服务层-拿到连接后的数据，sql优化器优化

• 引擎层-提供各种存储引擎（myisam innodb）

• 存储层-存储数据

2.sql的编写过程和执行过程

2.1 编写过程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit

2.2 解析过程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit

3.sql优化

3.1 优化的原因

原因：性能低、执行时间长、等待时间长、sql语句欠佳、索引失效等

3.2 优化什么

优化sql主要是优化索引

索引相当于目录

index是帮助mysql高效获取数据的数据结构

索引的弊端：

• 索引本身很大，要占一定的空间
• 经常改变的字端
• 很少使用的字端
• 会降低增删改的效率

索引的优势：

• 提高查询效率
• 降低cpu使用率
  • btree索引本身就是排序好的就够了，排序的时候可以直接使用

存储引擎

1.存储引擎

• 通过show engines查看

  mysql> show engines;
  +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
  +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
  | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
  | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
  | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
  | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
  | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
  | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
  | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
  | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
  +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  

• 最常用的还是innodb和myisam

• 区别：

  1. 存储文件：
     • myisam有2个文件
       • 数据结构文件
       • 数据文件
       • 索引文件
     • innodb有2个文件
       • 数据结构文件
       • 数据索引和数据文件
  2. 功能
     • innodb
       • 支持事务
       • 支持外键
       • 支持行锁
       • 支持xa事务
       • 支持savepoints
     • myisam
       • 表锁

2.脏读 幻读 不可重复读

2.1 脏读

在事务进行的过程中，读取到了未提交的数据

2.2 不可重复读

在事务进行的过程中，多次查询结果不一致

2.3 幻读

在一个事务过程中，用同样的操作查询数据，得到的记录数量不一样

2.4 事务并发问题的处理方式

• 加锁
• 事务隔离
• mvcc

2.4.1 加锁

• 脏读：在修改时候加排它锁，知道事务提交才释放。读取的时候加共享锁，读取结束释放锁。
  • 锁会产生性能损失
  • 很多事物并发不能完全避免脏读的问题
• 不可重复读：读数据的时候加共享锁，写数据加排它锁。
• 幻读：加范围锁。

2.4.2 事务隔离

2.4.3 mvcc

• mvcc是多版本并发控制
• mvcc值在提交读和重复读下可以执行，

3.事务特性和隔离级别

事务：表示多个数据操作组成一个完整的事务单元，这个事务内的所有数据操作要么同时成功，要么同时失败。

3.1事务的特性：ACID

1. 原子性：事务是不可分割的，要么全部成功，要么全部失败
2. 一致性：事务无论是完成还是失败，都必须保持事务哪操作的一致性。当失败的时候，都要读i前面的操作进行回滚，不管中途是否成功
3. 隔离性：当多个事务操作一个数据的时候，为防止数据损坏，需要将每个事务进行隔离，互相不干扰
4. 持久性：事务开始就不会终止，不受其他外界因素的影响

3.2事务的隔离级别：默认可重复读

设置隔离级别：

• set transaction level *** 设置下次的事务隔离级别

• set session transaction level ***设置当前会话的隔离级别

• set global transaction level *** 设置全局事务隔离级别

3.3在mysql中五种隔离级别：

• None ：不使用事务
• read uncommitted：未提交读（允许脏读）
• read committed：提交读（防止脏读）
• repeatable：重复读（防止脏读和不可重复读）（mysql默认）
• serializable：串行（防止脏读、幻读、不可重复读）效率最低

五种隔离级别：级别越高安全性越高，并发性能低

实际中最好保证mysql性能将并发控制在程序中

4.mysql中的锁

4.1 从粒度分

• 行锁：加锁粒度小，资源开销大（每一行一个锁，innodb支持）
  • 共享锁：读锁（同时读，不能修改）。多个事务可以同时对同一个数据共享一把锁，持有锁的事务都可以访问数据库但是是只读状态。
    • select xx lock in share mode;
  • 排它锁：写锁。只有一个事务可以获得排它锁，其他事物都不能获取改锁。innodb对写数据库的操作自动增加排它锁
    • select xxx for update;
  • 自增锁：针对自增字段。保证自增的字段值不会重复。如果有事务回滚，但是自增序列不会回滚。
• 表锁：加锁粒度大，加锁资源开销小（锁一张表 myisam和innodb都支持）
  • 表共享读锁
  • 表排它写锁
  • 意向锁：innodb自动添加的锁
• 全局锁
  • 加锁后整个数据库处于只读状态

4.2 常见的锁算法

• 记录锁：锁一条具体的数据
• 间隙锁：重复读的时候会加锁
• next-key：间隙锁+右记录锁

索引

1.索引

1.1 索引的优点和缺点

索引的弊端：

• 索引本身很大，要占一定的空间
• 经常改变的字端
• 很少使用的字端
• 会降低增删改的效率

索引的优势：

• 提高查询效率
• 降低cpu使用率
  • btree索引本身就是排序好的就够了，排序的时候可以直接使用

1.2 索引的分类

• 分类
  • 单值索引：一个表可以多个单值索引
  • 唯一索引：不能重复（id）
  • 复合索引：相当于多个列构成的索引（name，age）

1.3 创建索引

方式1:

-- 创建索引
create 索引类型 索引名称 on 表(字端);
-- 创建单值索引
create index dept_index on tb(dept);
-- 创建唯一索引
create unique index name_index on tb(name);
-- 复合索引
create index dept_name_index on tb(dept_name);

方式2：

-- 创建索引
alter table add 索引类型 索引名称(字端)
-- 创建单值索引 
alter table add index dep_index(dept)
-- 创建唯一索引
alter table tb add unique index  name_index(name);
-- 创建复合索引
alter table tb add index dept_name_index(dept,name);

注意：一个字端是primary_key会自动有索引，主键索引是特殊的唯一索引，主键索引不能为none但是唯一索引可以为none

1.4 删除索引

drop index name_index on tb;

1.5 查询索引

show index from 表名；

2.索引的本质

• 索引是帮助mysql高效查询的数据结构

• 索引的数据结构类型

2.1 二叉树

• 二叉树

  • 二叉树如果顺序从小到大会退化成一个链表
  

2.2 红黑树

• 红黑树（二叉平衡树）

  • 红黑树会在二叉树的基础上如果高度差过大会做一次平衡

    

  • 红黑树的弊端

    • 层级（树的高度）还是很多

2.3 b-tree

• btree

  • 叶子结点具有相同的深度，叶子结节点的指针为空
  • 所有索引元素不重复
  • 节点中的好数据索引从左到右依次递增

  

2.4 b+tree

​

• b+tree

  • b+tree是btree的变种
  • 非叶子节点不会存储数据，非叶子节点只会存储冗余的索引（父节点的首个元素的索引）
  • 叶子节点包含所有的索引字段
  • 叶子节点用指针连接，提高区间访问的性能
  • 非叶子节点的冗余索引还会自动加载到内存（节省了磁盘io）

  

2.5 哈希表

• 哈希表

  • 哈希表采用的 是数组+链表的存储结构
  • 对索引的key进行一次哈希运算根据运算后的值来存储
  • hash算法其实比b+tree要快
  • hash不支持范围查询，所以不用
  • 哈希冲突会采用链表

  

2.6 b-tree 和 b+tree的区别

• b-tree叶子节点没有双向指针不支持范围查询
• b+tree的所有数据都在叶子节点

2.7 索引的缺点

• 创建一个索引就是生成了一个b+tree 本身叶子节点就保存了数据，所以创建索引相当于冗余了数据

3.mysql数据的存储位置

• 默认存储在/mysql/data中
• myisam和innodb是修饰数据表的

3.1 myisam

• myisam索引
  • 非聚集索引：数据和索引分开存储

.frm ---> frame ---> 存储的是数据结构
.myd ---> myisam data ---> 存储的是数据
.myi ---> myisam index ---> 存储的是索引

3.2 innodb

• innodb索引的实现
  • 聚集索引：数据和索引在一起
  • 表数据文件本身就是b+tree的一个索引文件结构
  • 叶子节点包含了完整的数据
  • 叶子节点的双向指针存储的是相邻的磁盘文件地址（方便范围存储）
  • 为什么innodb建议用自增的整型主键？
    • 如果不做主键，mysql会做很多事情
      • innodb默认会使用主键的索引来构建b+tree
      • 如果没有主键它会找到不重复的一列来作为索引
      • 如果没有不重复的列，则会自动生成一个隐藏列来作为索引
    • 使用整形的主键
      • 占用存储空间更小
      • 大小比较更快
    • 自增
      • 非自增的如果插入到中间叶子节点要分裂，还要做平衡
  • 为什么非主键索引结构叶子节点存储的是主键？

.frm ---> frame ---> 存储的是数据结构
.idb ---> index + data ----> 存储的是索引+数据

主键索引

非主键索引

3.3 聚簇索引和非聚簇索引

聚簇索引：

• 数据和索引在一个文件中

• innodb

非聚簇索引：

• 数据和索引在两个文件中
• myisam

4 索引优化

• 索引优化的原因：性能低、执行时间长，等待时间长，sql不好，索引失效

• 联合索引的存储结构

• 联合索引会依照顺序进行比较

• 如图会先比较name，在比较age，在比较position

4.1 explain详解

通过explain关键字来查看sql状态

mysql默认的优化器会干扰我们的优化

id		编号
select_type 	查询类型
table	表
partitions 
type	类型
possible_keys	预测用到的索引
key		实际用到的索引
key_len	长度
ref 表之间引用关系
rows 	通过索引查询到的数据量
filtered
Extra	额外信息

4.1.1 id

• id值相同，程序从上到下顺序执行
  • 数据小的表优先查询:表的执行顺序因为数据量的个数改变而改变：笛卡尔积 程序喜欢中间的数据量越小越好
• id值不同，id越大越先执行(在嵌套子查询的时候 先查内层)
• id值多个，先id越大越先执行 后id相同的数据量大的先执行

4.1.2 select_type

select_type代表查询的类型

• primary：包含子查询的sql中的主查询 （最外层）
• subquery：
• simple：简单查询，不包含子查询和union
• derived：用到了临时表
  • 在from子查询中只有一张表
  • 在from子查询中 有两张表 则左

4.1.3 type

type代表的是索引的优化等级，从高到低依次如下：

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

• system（不用考虑）：只有一条数据的系统表（做不到）；或者衍生表只有一条数据（偶尔做到）；
• const（基本达不到）：仅仅查到一条数据的sql语句，用于primary_key || unique_key；
• eq_ref（唯一性索引，基本也达不到）：对于某个索引键的查询，返回匹配唯一行的数据（每条数据是唯一的【根据索引查到的数据】）
  • 常见于主键索引、常见于唯一索引
  • 查询结果有且只有一个，不能多，不能是0（查询结果小就是0【有6条数据你查到3个】）

注意【以上情况基本达不到】

• ref（）：非唯一性索引，对于每个索引的查询返回匹配的多个结果
• range（）：有索引的范围查询【between and ， in , > , <】其中in偶尔失效转为无索引
• index（）：查询全部索引中的数据
• all（）：查询表中的所有数据

总结：

1. system/const 返回一条数据
2. eq_ref 返回多条数据，但是数据是唯一的
3. ref 返回多条，每条数据可以说或者多条

4.1.4 possible_keys

possible_keys表示可能用得到的索引，是一种预测

4.1.5 key

key 实际用到的索引

4.1.6 key_len

索引的长度

作用：用来判断复合索引是否被完全使用

如果索引字端可以为null那么为null的标识会占一个字节

4.1.7 ref

此处ref和type中的ref不一样

字端中的ref指明当前表参照的字端

select 。。。 where a.c = b.x
其中bx为常用则ref=const

4.1.8 rows

被索引优化查询的数据个数（根据索引查出来的数据）

4.1.9 extra

• using firesort 查询结果需要一次额外的排序 消耗较大
  • 常见于order by 语句
  • 单索引：如果排序和查找不是同一个字端
  • 复合索引：不能垮列（最佳左前）
  • 建议：where 和order by针对相同字端顺序使用
• using temporary 用到了临时表 性能损耗很大
  • 一般出现在group by中
• using index：性能提升 索引覆盖 索引 覆盖
  • 索引覆盖：是要使用的列全部都在索引中就是索引覆盖（索引覆盖了你的所有查询字端）
  • 索引覆盖的时候会对keys 和 possible_keys有影响
    • 有where key和possible-ke y都有
    • 没有where 只有key
  • 此次查询不取原文件（不需要回表）
• using where：
  • 既要在索引查 还需要回表查询

4.2 最佳左前缀原则

• 联合索引的查询过程

• 因为联合索引的排序是从左到右排序的，也就是左边的排序优先级最高，索引不能垮列

• 联合索引是从左到右右顺序的

  • 比较name，在比较age，在比较position
  • 如果不从第一个开始，后面的数据无法保证有顺序

4.3 索引优化实例

实例1: 优化基础

create database myDB charset=utf8;
use myDB;
drop table test01;
create table test01(
	a1 int(4) not null,
	a2 int(4) not null,
	a3 int(4) not null,
	a4 int(4) not null
);
alter table test01 add index idx_a1_a2_a3_a4(a1,a2,a3,a4);
--1.查询1 最优查询
explain select a1,a2,a3,a4 from test01 where a1=1 and a2=2 and a3=3 and a4=4;
explain select a1,a2,a3,a4 from test01 where a4=4 and a3=3 and a2=2 and a1=1;
-- 和上面的结果一样 因为有sql优化器
-- 以上两个sql使用了全部的复合索引

mysql> explain select a1,a2,a3,a4 from test01 where a4=4 and a3=3  and a2=2 and a1=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test01
   partitions: NULL
         type: ref
possible_keys: idx_a1_a2_a3_a4
          key: idx_a1_a2_a3_a4
      key_len: 16
          ref: const,const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

--2.查询2 索引夸列
explain select a1,a2,a3,a4 from test01 where a1=1 and a2=2 and a4=4 order by a3;
-- using index 索引覆盖 a1 a2
-- using where 回表 a4
-- 以上通过key_len验证

mysql> explain select a1,a2,a3,a4 from test01 where a1=1 and a2=2 and  a4=4 order by a3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test01
   partitions: NULL
         type: ref
possible_keys: idx_a1_a2_a3_a4
          key: idx_a1_a2_a3_a4
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

--3.查询3 夸列
-- 注意 看的是where and order by加起来有没有夸列
mysql> explain select a1,a2,a3,a4 from test01 where a1=1 and a4=4 order by a3;
-- using index 索引覆盖 a1
-- using where 回表 a4
-- using firesort order by a3
-- 以上通过key_len验证

--4.查询4 没有多余排序
-- 注意 看的是where and order by加起来有没有夸列
mysql> explain select a1,a2,a3,a4 from test01 where a1=1 and a4=4 order by a2,a3;

mysql> explain select a1,a2,a3,a4 from test01 where a1=1 and a4=4 order by a2,a3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test01
   partitions: NULL
         type: ref
possible_keys: idx_a1_a2_a3_a4
          key: idx_a1_a2_a3_a4
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

总结：

• 如果abcd复合索引和使用的顺序全部一致(且不垮列使用)，则复合索引全部使用，如果部分一致，则部分使用
• where和orderby加起来不要垮列

实例2: 单表优化

create table book(
	bid int(4) primary key,
	name varchar(4) not null,
	authorid int(4) not null,
	publicid int(4) not null,
	typeid int(4) not null
);

insert into book values(1,"java",1,1,2);
insert into book values(2,"tc",2,1,2);
insert into book values(3,"wx",3,2,2);
insert into book values(4,"math",4,2,3);

-- 1.查询authorid=1 typeid为2活着3的bid
explain select bid from book where  typeid in(2,3) and authorid=1 order by typeid desc;

mysql> mysql> explain select bid from book where  typeid in(2,3) and authorid=1 order by type\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

--优化第一步：加索引
alter table book add index idx_bta (bid,typeid,authorid);
--再次查询
explain select bid from book where  typeid in(2,3) and authorid=1 order by typeid desc;

mysql> explain select bid from book where  typeid in(2,3) and authorid=1 order by typeid desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_bta
      key_len: 12
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

再次查询发现type从全表扫描变成了扫描索引

--第二步 分析索引
--先执行的where 在执行的select 索引索引顺序有问题
drop index idx_bta on book;
alter table book add index idx_tab (typeid,authorid,bid);

explain select bid from book where  typeid in(2,3) and authorid=1 order by typeid desc;

mysql> explain select bid from book where  typeid in(2,3) and authorid=1 order by typeid desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: range
possible_keys: idx_tab
          key: idx_tab
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

--第三部 分析sql in会有时候让索引查询失效 将in放在最后面
drop index idx_tab on book;
alter table book add index idx_atb (authorid,typeid,bid);
explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc;

mysql> explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: range
possible_keys: idx_atb
          key: idx_atb
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

总结：

• 最佳左前缀
• 索引需要逐步优化
• 将包括in的查询放在where条件的最后面

实例3: 2表优化

--优化原则
--1.给谁加索引？
--原则 小表驱动大表
--程序设计原则：  虽然大表驱动小表和小表驱动大表最后执行结果一样，但是对于双层循环建议将数据小的放在外面，数据量大的放在里面

--2.on条件中将数据量小的放在左边
--索引建立在经常使用的字段上

--左外连接给左表加索引
--右外连接给右表加索引

--数据准备
create table teacher2(
	tid int(4) primary key,
	cid int(4) not null
);
insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);


create table course2(
	cid  int(4) primary key,
	cname varchar(20)	
);

insert into course2 values(1,"java");
insert into course2 values(2,"python");
insert into course2 values(3,"kotlin");

explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname="java";

--给左连接的左表加索引
alter table teacher2 add index index_teacher2_cid(cid);
--再次查询
explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname="java";

--给查询的cname字端加=索引
alter table course2 add index index_course2_cname(cname);
--再次查询
explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname="java";

实例4：多表查询

--多表优化就是单表和双表优化的结合
--1.小表驱动大表
--2.索引建立在经常查询的字端上

实例5：其他

drop database innodb_test;

create database innodb_test charset=utf8;

use innodb_test;

create table test(
	id int(11) not null,
	a int(11) not null,
	b int(11) not null,
	c int(11) not null,
	d varchar(100) not null,
	primary key(id)
)engine=innodb default charset=utf8;

insert into test values(1,1,1,1,'a');
insert into test values(2,2,2,2,'b');
insert into test values(3,3,3,3,'c');
insert into test values(4,4,4,4,'d');
insert into test values(5,5,5,5,'e');
insert into test values(6,6,6,6,'f');

create index idx_b_c_d on test(b,c,d); -- 为bcd字段创建索引

explain select b from test;
-- 1.全表扫描
-- 2.走联合索引 联合索引的b+tree的叶子节点也有所有的b的数据
-- 最后选择了bcd索引的原因是：联合索引叶子节点数据量更小（不完整的）这样可能叶子节点可能会存的更多
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_b_c_d | 310     | NULL |    6 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

explain select * from test order by b,c,d;
-- 1.索引是排好序的 但是需要回表
-- 2.全表扫描还要排序，但是不需要回表
-- 回表的开销大于排序 所以选择了全表扫描
-- 这个不会走索引，因为这个语句中是select *,而索引b+tree的叶子节点只存了bcd数据和主键的值，select*还要回表
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

explain select b,c,d from test order by b,c,d;
-- 这次查询会走索引，因为索引的b+tree的叶子节点已经存储了所有的bcd数据并且是排好序的
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_b_c_d | 310     | NULL |    6 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

4.4 避免索引失效的原则

• 复合索引-最佳左前缀

  select a b c ... where a b c
  

• 全索引匹配

  • 建立了索引要用上

• 不要索引上操作

  • 计算
  • 函数
  • 类型转换

• 复合索引 != \is null is not null

  • using index永远有效

• like查询不要以百分号开头，以常量开头

  -- select tname from xxx where name like "%李%"；
  select tname from xxx where name like "李%"；
  -- 如果必须使用"%李%"进行模糊查询，可以使用索引覆盖，效率会提高一些
  

• 不要使用类型转换 会让索引失效

  explain select * from teacher where tname = 123;
  --类型转换
  explain select * from teacher where tname = "123";
  

• 不要用or连接

  • or连接会让or之前的索引也失效

4.5 其他优化原则（双路排序和单路排序）

• exsit和in

  如果主查询的数据集大，则使用in
  如果子查询的数据集大，则使用exist
  

• order by

  • using filesort右两种排序

    • 双路排序（4.1之前）-扫描磁盘2次：先扫描排序字段，排序动作在buffer缓冲区中进行，再扫描其他字段。

    • 单路排序（4.1之后）-扫描磁盘1次：io比较消耗性能，所以改为了单路，但是单路排序也有可能是多次io，原因是如果数据量很大的话，无法所有字端全部读完，因此会进行分片读取，单路排序比双路排序会占用更多的buffer，修改buffer的大小可以一定程度缓解单路的问题

      --修改buffer的大小
      set max_length_for_sort_data=1024
      

    • 如果max_length_for_sort_data太低，mysql会自动从单路排序切换为双路排序

  • 提高order by 查询效率的方法

    • 选择单路和双路、跳转buffer大小
    • 避免select *
    • 符合索引不要垮列（避免using filesort）
    • 保证排序的一致性
      • order by a,c desc

4.6 mysql慢查询日志

用来记录响应大于阈值的sql语句

4.6.1 查看和修改阈值

• 查看阈值

show variables like "long_query_time";

• 临时修改阈值

set global long_query_time = 5;--修改后重新登陆就能生效

• 永久修改阈值

/etc/my.cnf中增加配置
vim /etc/my.cnf
[mysqld]
long_query_time = 3

4.6.2 查看慢查询日志是否开启

• 检查是否开启了慢查询日志

show variables like "slow_query_log";

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.03 sec)

• 临时开启

set global slow_query_log = 1; --在内存中开启
exit
service mysql restart

• 永久开启

/etc/my.cnf中增加配置
vim /etc/my.cnf
[mysqld]
slow_global_log = 1
slow_global_log_file = /opt/server/mysql/localhost-slow-mysql.log

4.6.3 通过日志查看慢sql

cat /opt/server/mysql/localhost-slow-mysql.log

4.6.4 通过mysqldimpslow查看慢查询日志

• 获取返回记录最多的三个sql

  mysqldumpslow -s r -t /opt/server/mysql/localhost-slow-mysql.log
  

• 获取访问次数最多的三个sql

  mysqldumpslow -s c -t /opt/server/mysql/localhost-slow-mysql.log
  

• 按照时间排序，前十条包括left join的sql

  mysqldumpslow -s t -t 10 -g "left join" /opt/server/mysql/localhost-slow-mysql.log
  

4.7 海量数据分析sql（开发中使用，生产要关闭）

• profiles

show profiles;--默认关闭
set prifiling=on;--打开
show variables like %profiling%
show profiles;--记录所有的查询会花费的时间
show profiles all for query 2; --第二条sql有句各种执行过程用的时间

• sql诊断
• 全局查询日志

4.8 索引覆盖

如果只需要在一刻索引树上就可以获取sql需要的所有列，这样不需要回表，查询就会很快

实现索引覆盖的方式的最简单的方式，就是建立联合索引

4.9 回表

索引叶子节点查询不到所有数据就会回表查询

5.innodb

• bufferpool

• free链表

• flush链表

• lru链表

• redo.log

• binlog

• undolog

5.1 缓冲区buffer pool

• buffer pool缓冲区：内存中的一块区域，可以理解为一个数组，数组的每一个位置刚开始是空的，默认大小是128M，每一个页的大小是16kb，页也可以理解为b+tree中的一个节点。

• 在执行一条查询语句的时候，首先会看buffer pool中是否有"页"的数据，如果有则会存buffer pool中取，如果没有则会从磁盘中copy一份数据到buffer pool中

• buffer pool中的空闲区域如何管理？

  • free链表：管理空闲区域

  • free链表中存储了基节点，基几点中存储了一些信息，当前有多少个节点，首节点和尾节点信息等

    

• 如何执行update语句

  • 执行update语句的时候，会优先修改buffer pool中的数据，而在此查询也会先取buffer pool中
  • 在mysql后台会启动一个线程定时的去更新脏页的数据

• 如何判断是不是脏页

  • 通过flush链表来存储脏页的位置
  

• buffer pool满了之后怎么办的淘汰机制

  • 通过lru链表来找出最近使用最少的页来进行更新，新来的数据加入到链表的头部，当有一个页被用到，会取出来放在链表的头部，这样链表尾部的就是不常用的。
  • 依据lru来链表的原理可以淘汰lru链表的最后一个数据（不常用的数据）
  

• 全表扫描的情况

  • select * from table1
  • 如果使用了全表扫描，如果数据量很大，会使全表扫描后的结果的所有页淘汰掉原来buffer pool中的热点页?
  • innodb的lru链表分为了两个区域 5/8的热数据区域和3/8的冷数据区域
  • 当磁盘中取出一页，优先淘汰冷数据区域
  • 第一访问页面的时间为t1，第二次访问的时间为t2，如果t2-t1>1s 转移到热数据区域（替换热数据区域数据）

  

• update中如何脏页还没有持久化道硬盘mysql宕机了怎么办

  • 当innodb执行update语句的时候，innodb中的bufferpool中出现了脏页
  • 生成一个redo.log（生成一个日志对象）
    • redo.log可以理解为updae语句的log
  • redo.log持久化
  • 修改成功
  • mysql宕机了后重启了
  • 查询再来的时候磁盘中的数据还没有被修改，然后对比redolog和现在磁盘中的数据去做数据同步。

  注意：页中的每一行数据在磁盘中是连续的，但是在bufferpool中很大概率是不连续的，redolog防止证页持久化道磁盘

  ​ redolog对应的文件是mysql中的ib_logfile0和ib_logfile1，48m大小

  • 为什么redolog有两个文件

    • 如果ib_logfile0满了切换到ib_logfile1，如果ib_logfile1满了，切换到ib_logfile0
    • 循环一次触发一次检查点，会把没有持久化道磁盘的数据持久化道磁盘，在把新的update语句加入到redolog
    • 优化：可以把这两个文件调大，或者文件的个数增加，但如果这样做 ，重启会慢一些

  • 什么时候对redolog进行持久化？

    • 事务commit之后才持久化
    • innodb配置项

    

  • redolog存储在log buffer的内存中

  

5.2 redolog(innodb)

• 事务执行期间生产

• 记录的是哪个物理位置的数据修改了

• 恢复数据更方便

5.3 binlog(mysql)

• 主要用在主从复制

• 事务执行期间生成

• 记录的是sql，update语句

• 恢复数据没有redolog方便

5.4 undolog

• 事务执行期间生成
• 记录的是修改之前的数据
• 事务回滚的时候，bufferpool中已经修改了，此时事务回滚根据undolog去修改bufferpool中已经修改的数据

5.5 doublewrite buffer file（双写缓存）

• innodb的一页是16k（b+tree中一个节点），操作系统的一页是4k，操作系统持久化道磁盘要经历4次

• 假设第二次之后机器宕机了，这时候就会有问题。

  • 方案1
    • 第一步先把数据写在双写缓存中，这时候如果挂了，我们有redolog，可以还原出最新数据
    • 如果第二步写入表中间的时候msyql宕机了，这时候就不影响了，因为从双写 缓存中可以拿到所有的数据
  • 方案2
    • 如果磁盘支持原子性，也可以解决这个问题。可以关掉双鞋缓存。
  

5.6 changebuffer 优化写

6.锁机制详解

解决资源共享造成的并发问题

分类：

• 操作类型
  • 读锁：对同个数据，多个操作互相不干扰
  • 写锁：对同个数据没有释放锁之前，不能读写
• 操作范围
  • 表锁：一次锁定一张表
  • 行锁：一次锁定一行
  • 页锁

mysql主从和读写分离

1.mysql的主从搭建原理（binlog）

mysql通过主节点的binlog同步给从节点实现主丛复制

mysql的主从集群，只会把主节点的binlog同步到从节点，所以写数据的操作在主数据库，读数据的操作可以在主或者从完成

mysql异步复制

mysql的半同步复制

其他高可用组件

• mmm

• mha

• mgr

分库分表

1.什么事分库分表？

当表中数据了很大的时候，查询效率会变低，为了提高效率，进行分库分表

2.分库分表的方式

分库分表包含分库和分表两个部分，而这两个部分可以统称为数据分片，气目的是将数据拆分为不同的单元，从拆分的角度上可以分为水平分片和垂直分片

• 垂直分片：按照业务的数据进行分片，又称为纵向分片。他的核心理念就是专库专用。在拆分之前，一个数据库有多个数据表组成，每个表对应不同的业务。而拆分之后，则是按照业务表进行归类，分布到不同的数据库或者数据表中，从而分散不同的数据库和数据表。
• 从业务结构将不同的表拆分到不同的数据库

• 水平分片：从数据角度将一个表中的数据拆分到不同的数据表中

  
  • 分片的策略：
    • 取余/取模：
    • 按范围分：
    • 按照时间：
    • 安枚举：

3.什么时候分库分表？

阿里手册建议：当单表数据超过500w，或者数据库表文件大小超过2G，就要考虑分库分表

4.分库分表后的执行流程？

5.分库分表的组件？

mycat/shardingsphere

6.分库分表的缺点

跨库查询

跨库排序

分布式事务

公共表

主键避重