MySQL的事务

什么是事务？

事务是一个或多个SQL语句的序列，作为单个工作单元进行处理

事务的基本特性是什么（ACID）？

  1. 原子性（A）：体现在一个事务里面的所有操作都是不可分割的，要么全部执行成功，要么全部不执行。
  2. 一致性（C）：保证数据从一个正确的状态转移到另一个正确的状态
  3. 隔离性（I）：每个事务的执行都互相不干扰，事务之间是独立的
  4. 持久性（D）：事务一旦提交，那么这个变更就是永久的

并发事务可能会带来哪些问题？

事务的隔离级别有哪些？

索引的种类

在MySQL中，索引实在存储引擎层实现的，而不是在服务器层实现的，所以在不同存储引擎中具有不同的索引类型和实现。常见的索引分类如下：

  • 按数据结构分类：B+tree索引、Hash索引、Full-text索引
  • 按物理存储分类：聚集索引、非聚集索引
  • 按字段特性分类：主键索引（PRIMARY KEY）、唯一索引（UNIQUE）、普通索引（INDEX）、全文索引（FULLTEXT）
  • 按字段个数分类：单列索引、复合索引

二叉树

有序的存储数据，将导致一个非常极端的一种数据结构，变成了链表，不会进行分叉，会使树的高度非常高，磁盘I/O次数非常多，进行数据检索的性能是非常低的。

平衡二叉树

通过自旋的方式，能够自动的进行分叉，从而减少树的高度，当数据有序插入时，比二叉树数据检索性能更加，但是如果数据量过大，节点个数也会很多，树的高度也会增高，一样的会增加磁盘I/O次数，影响查询效率。

B树：（平衡多路查找树）

B树的出现可以解决树高度的问题，不再限制一个父节点中只能有两个子节点，且一个节点可以存储多个元素。B树对范围查询的支持不是很好

B+树

更适合做存储索引结构。对B树做了三点优化：
（B树和B+树的区别，MySQL为什么要选择B+树作为默认索引的数据结构？）

  1. 把所有数据都存储在叶子节点上，并且数据是顺序排列的，有利于范围查询，排序查询，分组查询以及去重查询。
  2. 非叶子节点上没有存储数据，仅存储键值。（寻址节点，大部分查询时间，都在进行寻址，通过这个方法，可以快速的定位数据）
  3. 在叶子节点之间加入了个双向链表，方便在数据查询后进行升序或者降序的操作，可以优化B树对范围查询效率低的问题

结构图如下：

Hash索引

Hash索引用的并不多，最主要是因为最常见的存储引擎InnoDB不支持显示地创建Hash索引，只支持自适应Hash索引。虽然可以使用sql语句在InnoDB显示声明Hash索引，但是其实是不生效的虽然可以使用sql语句在InnoDB显示声明哈希索引，但是其实是不生效的。在存储引擎中，Memory引擎支持Hash索引。

二级索引

在MySQL中，创建一张表时会默认为主键创建聚簇索引，B+树将表中所有的数据组织起来，即数据就是索引主键所以在InnoDB里，主键索引也被称为聚簇索引，索引的叶子节点存的是整行数据。而除了聚簇索引以外的所有索引都称为二级索引，二级索引的叶子节点内容是主键的值。

例如创建如下一张表：

1
2
3
4
5
6
CREATE TABLE users(
	id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY(id)
);

新建一个已age字段的二级索引：

1
ALTER TABLE users ADD INDEX index_age(age);

MySQL会分别创建主键id的聚簇索引和age的二级索引。主键索引的叶子节点存的是整行数据，而二级索引叶子节点内容是主键的值，就会涉及到“回表”

覆盖索引

前面说的，执行select * from ‘user’ where age = 35；这条sql的时候，会先从索引页中查出来age=35对应的主键id，之后再回表，到聚簇索引中查询其他字段的值。那如果执行下面这条sql，情况就不一样了：

1
select id from 'user' where age = 35；

这次查询字段从select * 变为select id，查询条件不变，所以也会走age索引，先从索引也中查出age=35，此时已经获取了主键id，压根不需要回表了。而这种需要查询的字段都在索引列中的情况就被称为覆盖索引。使用覆盖索引可以减少回表的次数，查询速度更快，效率更高

索引下推

索引下推是MySQL5.6针对扫描二级索引的一项优化改进，用来在范围查询时减少回表的次数。

例子如下所示：

为一张表创建一个复合索引

1
ALTER TABLE 'user' ADD INDEX ('name','age')

在范围查询内，就会造成多次回表，有多少条数据，就回多少次表，非常的慢。

1
2
#打开索引下推  默认是开启
set optimizer_switch='index_condition_pushdown=on';

当该选项开启时，MySQL优化器会尝试将查询条件下推到索引层级，以减少数据的读取和过滤操作，提高查询性能。简单来说就是一次性回表，大大减少了回表的次数。

复合索引+最左前缀原则

假如为一张表添加了复合索引：

1
ALTER TABLE 'user' ADD INDEX ('name','age','id')

这样子就是先以name排序，name相同的时候再以age排序，以此类推，最后再以id排序

最左前缀原则

以最左边的起点任何连续的索引都能匹配上，就是在编写where条件语句的时候，必须已最左边的索引开始编写条件，比如现在这个例子，就是 where name = 35 and …，一定要有name，这样子复合索引才会生效。

复合索引的优势

  1. 减少开销
    建一个复合索引(a,b,c)，实际相当于建了(a),(a,b),(a,b,c)三个索引.每多一个索引，都会增加写操作的开销和磁盘空间的开销，对于大量数据的表，使用复合索引会大大的减少开销!
  2. 索引覆盖
    对复合索引(a,b,c)，如果有如下sql：select a,b,c from table where a=’xxx’ and b=’xx’；那么mysql可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作，特别是随机io其实DBA主要的优化策略，所以在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
  3. 效率高
    索引列多，通过复合索引筛选出的数据就越少。比如有1000w条数据的表，有如下sql：select col1,col2,col3 from table where col1=1 and col2=2 and col3=3;
    假设：假设每个条件可以筛选出10%的数据
    A：如果只有单列索引，那么通过该索引能筛选出1000w *10%=100w条数据，然后再回表从100w调数据中找到符合col2=2 and col3=3的数据，然后再排序，再分页，以此类推(递归)
    B：如果是(col1,col2,col3)复合索引，通过三列索引筛选出1000w *10% *10% *10%=1w,效率提升可想而知

索引的优缺点

优点：

  1. 提高检索效率
  2. 降低排序成本，索引对应的字段是会有一个自动排序功能的，默认是升序ASC

缺点：

  1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  2. 索引需要占用物理空间，数据量越大，占用空间越大
  3. 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

什么时候需要创建索引？什么时候不需要？

  • 适合：

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

  • 不适合：
    1.字段值的唯一性太差的字段
    2.更新非常频繁的字段
    3.不出现在where条件语句中的字段

MySQL优化

关于SQL优化的方法，包括5点

  1. 创建索引减少扫描量
  2. 调整索引减少计算量
  3. 索引覆盖（减少不必访问的列，避免回表查询）
  4. 干预执行计划
  5. SQL改写

Explain

Explain是SQL分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助理解查询是如何进行的，分析查询执行计划可以帮助发现查询瓶颈，优化查询性能。

列明 含义
id 每个select都有一个对应的id号，并且都是从1开始自增的
select_type 查询语句执行的查询操作类型
table 表名
partitions 表分区情况
type 查询所用的访问类型
possible_keys 可能用到的索引
key 实际查询用到的索引
key_len 索引长度
ref 使用到索引时，与索引进行等值匹配的列或者常亮
rows 预计扫描的行数（索引行数或者表记录行数）
filtered 表示复合查询条件的数据百分比
Extra SQL执行的额外信息

索引优化

  1. 全值匹配，MySQL全值匹配是指在使用复合索引时，查询条件要包含索引的所有列，才能最大程度地利用索引。但是在日常开发中，也很难保证索引的所有列都被用到，但至少要满足最左前缀原则（查询从索引的最左前列开始，并且不能跳过索引中的列）
  2. 索引列上少计算，不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  3. 索引字段不范围查询，范围查询会使得后面的字段无序，造成部分索引失效。解决方法：在where前面加上 FORCE INDEX（possible_keys的值）
  4. 尽量使用覆盖索引，不select *，只访问索引的查询（索引列包含查询列），不使用select * 语句。
  5. 少用不等、空值还有or，mysql在使用不等于（!=或者<>) ，not in , not exists的时候无法使用索引会导致全表扫描<小于、>大于、<=、>=这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
  6. like百分号写最右（后模糊）

问题：如果非要在左边加上%呢，怎么解决索引不被使用的方法

  • 使用覆盖索引，查询字段必须是覆盖索引字段
  • 借助搜索引擎（Elasticsearch）

SQL优化

  1. 避免使用select *，节约cpu、内存资源、减少网络消耗、减少解析器成本、索引扫描提升查询性能
  2. 小表驱动大表，减少数据计算量，提升查询效率
  3. 用连接查询代替子查询，利用数据库索引，提升查询性能
  4. 提升group by效率，给高频分组字段添加索引、减少子查询，用连接查询代替子查询、缩小数据范围
  5. 批量操作，减少与数据库交互次数
  6. 使用limit，提升查询效率，避免过度提取数据、优化分页查询、简化查询结果
  7. 使用union all代替union，更快的查询速度、更精确的数据结果
  8. join的表不宜过多，阿里规定，禁止超过3张表进行join，查询效率下降、系统负载增加、维护难度加大