MySQL优化之路

一、Mysql的存储原理

索引相关

本质

​ 索引是帮助MySQL高效获取数据的排好序的数据结构

​ 建索引，提高数据检索的效率，降低数据库的IO成本； 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU的消耗。

索引分类

• 主键索引：主键自带索引效果，性能很好
• 普通索引：为普通列创建的索引

-- 格式
create index 索引名称 on 表名(列名);
-- 示例
create index idx_name on user(name);

• 唯一索引 ： 索引列的值必须唯一，但允许有空值。比普通索引的性能要好

-- 格式
create unique index 索引名称 on 表名(列名);
-- 示例
create unique index uniq_name on user(name);

• 联合索引 ( 开发常用 ) ：一次性的为表中的多个列创建索引 (建议一个联合索引不超过5个字段)

  ​ (最左前缀法则：如何命中联合索引中的索引列)

-- 格式
create index 索引名称 on 表名(列名1,列名2);
-- 示例
create index idx_name_age_password on user(name,age,password);

• 全文索引：进行查询时，数据源可能来自不同的字段或者表

  ​ MyISAM存储引擎支持全文索引，在实际开发中并不会去使用而是使用搜索引擎中间件

总结

-- 创建索引
create [unique] index 索引名称 on 表名(列名)
-- 删除索引
drop index [索引名称] on 表名
-- 查看索引
show index from 表名\\G

为数据表添加索引

索引的数据结构

• 二叉树 （链表情况）
• 红黑树 (层次太多)
• Hash表
  • 对索引的key进行一次hash计算就可以定位出数据存储的位置
  • 很多时候Hash索引要比B+树索引更高效
  • 仅能满足“=”,“IN”，不支持范围查询
  • hash冲突问题
• B Tree
  • 叶节点具有相同的深度，叶节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排列
• B+Tree （底层）
  • 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接，提高区间访问的性能

​ 推荐一个外国的数据结构在线演示网站: Data Structure Visualization

INNODB与 MYISAM类型的区别

1、INNODB引擎----聚集索引
把索引和数据存放在一个文件中，通过找到索引后就能直接在索引树上的叶子结点中获得完整的数据。可以实现行锁/表锁

2、MYISAM----非聚集索引
把索引和数据存放在两个文件中，查找到索引后还要去另一个文件中找数据，性能会慢一些。除此之外，MylSAM天然支持表锁，而且支持全文索引。

创建索引的情况

• 需要创建索引的情况：

1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该创建索引
3、查询中与其他表关联的字段，外键关系建立索引
4、单键/组合索引的选择问题 ?( 在高并发下倾向创建组合索引 )
5、查询中排序的字段，排序字段通过索引去访问将大大提高排序速度
6、查询中统计或分组字段

• 不需要创建索引的情况：

1、频繁更新的字段不适合创建索引
2、经常增删改的表
3、where条件里用不到的字段不创建索引
4、表记录太少
5、如果某个数据列包含许多重复的内容，为他建立索引没有太大的效果

​ Tip： 一个索引的选择性越接近1，这个索引的效率就越高！

联合索引

使用一个索引来实现多个表中字段的索引效果。

存储方式：

索引最左前缀原理

最左前缀法则是表示一条sql语句在联合索引中有没有走索引(命中索引/不会全表扫描)

联合索引的底层存储结构长什么样?

慢sql原因

• 查询语句写的不好
• 索引失效
• 关联过多的 join （设计缺陷或不得已的需求）
• 服务器调优以及参数设置

二、Mysql性能查询

SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多的途径:

• 工程优化如何实现︰数据库标准、表的结构标准、字段的标准、创建索引 阿里：MySQL数据库规范
• SQL语句的优化:当前SQL语句有没有命中索引。

explain

简介

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

详解

MySQL Explain详解 explain

作用

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

使用格式：explain + sql语句

信息

详解

​ 关闭对mysql对衍生表的合并优化（演示）

set session optimizer_switch = \'derived_merge=off\';

null > system > const > eq_ref > range > index > all

-- null
性能最好的，一般在使用了聚合函数操作索引列，结果直接从索引树获取即可

-- system
很少见 直接和一条记录进行匹配

-- const
使用主键索引或者唯一索引和常量进行比较，性能也很好

-- eq_ref
在进行连接查询时，连接查询的条件中使用了本表的主键进行关联

-- ref
-- 简单查询
使用普通列作为查询条件
-- 复杂查询
在进行连接查询时，连接查询的条件中使用了本表的普通索引列

-- range
在索引列上使用了范围查找，性能是ok的

-- index
在查询表中的所有的记录，但是所有的记录可以直接从索引树上获取，(表中字段均加索引)

-- ALL
全表扫描。就是要从头到尾对表中的数据扫描一遍。这种查询性能是一定要做优化的。

-- unsing index
使用了覆盖索引 （指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要进行查表。使用覆盖索引进行性能优化这种手段是之后sql优化经常要用到的。）

-- using where
where的条件没有使用索引列。这种性能是不ok的，我们如果条件允许可以给列设置索引，也同样尽可能的使用覆盖索引。

-- using index condition
查询的列没有完全被索引覆盖，并且where条件中使用普通索引

-- using temporary
会创建临时表来执行，比如在没有索引的列上执行去重操作，就需要临时表来实现。（这种情况可以通过给列加索引进行优化。）

-- using filesort
MySQL对数据进行排序，都会使用磁盘骧完成，可能会借助内存，涉及到两个概念︰单路排序、双路排序

-- Select tables optimized away
当直接在索引列上使用聚合函数，意味着不需要操作表

三、mysql优化细节

索引优化建议

命中索引建议：

• 对于SQL优化来说，要尽量保证type列的值是属于range及以上级别。

• 不能在索引列上做计算、函数、类型转换，会导致索引失效

• 对于日期时间的处理 转换成范围查找

• 尽量使用覆盖索引

• 使用不等于(!=或者<>)会导致全表扫描

• 使用is null、 is not null会导致全表扫描

• 使用like以通配符开头(\'%xxx...\")会导致全表扫描 (使用覆盖索引或者搜索引擎中间件)

• 字符串不加单引号会导致全表扫描

• 少用or或in，MySQL内部优化器可能不使用索引 （使用多线程或者搜索引擎中间件）

• 范围查询优化 （范围大的拆分查找）

Trace工具

在执行计划中我们发现有的sql会走索引，有的sql即使明确使用了索引也不会走索引。mysql依据Trace工具的结论

-- 开启trace 设置格式为JSON，设置trace的缓存大小，避免因为容量大小而不能显示完整的跟踪过程。
set optimier_trace=\"enabled=on\",end_markers_in_JSON=on;
-- 执行sql语句
-- 获得trace分析结果
select *from information_schema.optimizer_trace \\G

Order by优化

在Order by中，如果排序会造成文件排序（在磁盘中完成排序，这样的性能会比较差)，那么就说明sql没有命中索引，怎么解决? 可以使用最左前缀法则，让排序遵循最左前缀法则，避免文件排序。

优化手段：

• 如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。
• 如果文件排序没办法避免，那么尽量想办法使用覆盖索引。all->index

对于Group by而言 ：本质上是先排序后分组，所以排序优化参考order by优化。

分页查询优化

-- 原始
Explain select * from employees limit 10000,10

-- 对于主键连续的情况下进行优化:(少见)
Explain select * from employees where id>10000 limit 10

-- 通过先进行覆盖索引的查找，然后在使用join做连接查询获取所有数据。这样比全表扫描要快
EXPLAIN select * from employees a inner join (select id from employees order by name limit 1000000,10) b on a.id = b.id;

join查询优化

in、exstis优化

在sql中如果A表是大表，B表是小表，那么使用in会更加合适。反之应该使用exists。

count优化

对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户访问，所以对于访问频率过高的数据建议维护在缓存中。

四、mysql的锁机制

锁的定义与分类

定义

锁是用来解决多个任务（线程、进程)在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。

对于数据管理软件MySQL来说，必然会到任务的并发访问。那么MySQL是怎么样在数据安全和性能上做权衡的呢?——MVCC设计思想。

分类

# 表锁
-- 对表上读锁或者写锁格式
lock table 表名 read/write;
-- 释放当前锁
unlock tables
-- 查看表的上锁情况
show open tables

# 行锁
# MySQL 是默认开启事务自动提交的
SET autocommit = 0; # 关闭
SET autocommit = 1; # 开启 默认的
-- 开启事务
begin;
-- 上行锁 对id = 8 的这行数据上锁
update `user` set name=\'前度\' where id = 8;
-- 方式2
select * from `user` where id = 8 for update;
-- 释放锁
commint;

MVCC设计思想

​ MVCC，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

事务的特性

• 原子性：一个事务是一个最小的操作单位，要么都成功，要么都失败
• 隔离性：数据库为每个用户开启的事务，不能被其他事务影响
• 一致性：事务提交之前与回滚之后的数据一致
• 持久性：事务一旦提交不可逆 被持久化到数据库中

事务的隔离级别

• read uncommitted(读未提交) : 一个事务读取了另一个事务还没有提交的数据 会出现脏读的情况
• read committed(读已提交) : 已经解决了脏读问题，在一个事务中只会读取另一个事务已提交的数据，出现不可重复读情况
• repeatable read (可重复读): 默认级别 在一个事务中每次读取的数据都是一致的．不会出现脏读和不可重复读的问题。但会与幻读情况
• Serializable: 串行化的隔离界别直接不允许事务的并发发生，不存在任何的并发性。相当于锁表，性能非常差，一般都不考虑 通过上行锁来解决幻读问题

-- 设置隔离级别
set session transaction isolation level 隔离级别;

隔离导致的一些问题
脏读： 一个事务读取了另外一个事务未提交的数据
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
虚读，幻读：是指在一个事务内读取了别的事务插入的数据，导致前后读取不一致（一般是行影响，多了一行）

MySQL在读和写的操作中，对读的性能做了并发性的保障，让所有的读都是快照读，对于写的时候，进行版本控制，如果真实数据的版本比快照版本要新，那么写之前就要进行版本（快照〉更新，这样就可以既能够提高读的并发性，又能够保证写的数据安全。

死锁与间隙锁

死锁

​ 所谓的死锁，就是开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态

间隙锁

​ 行锁只能对某一行上锁，如果相对某一个范围上锁，就可以使用间隙锁。间隙锁给的条件where id>13 and id<19，会 对13和19所处的间隙进行上锁。

五、部分面试题

为什么非主键索引的叶子节点存放的数据是主键值?

​ 如果普通索引中不存放主键，而存放完整数据，那么就会造成:

为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?

​ mysql 为什么建议 innodb 表要建一个主键？

• 如果有一个主键，可以直接使用主键建索引

• 如果没有主键，会从第一列开始选择一列所有值都不相同的，作为索引列

• 如果没有选到唯一值的索引列，mysql 会建立一个隐藏列，维护一个唯一id，以此来组织索引

  为什么推荐使用整形作为主键?

• 在索引中查找数据时，减少比较的性能。

• 使用整形作为主键相比字符型可以节省数据页的空间。

• 构建索引 b+ 树时，为了保证索引的有序性，使用整形可以避免页分裂。

  主键为什么要自增?

• 索引结构 b+ 树，具有有序的特性。
  -如果主键不是自增的，在进行增删数据的时候，会判断数据应该存放的位置，进行插入和删除，为了保持平衡，会对数据页进行分裂等操作移动数据，严重影响性能，所以主键需要是自增的，插入时，插入在索引数据页最后。

The End~~

来源：https://www.cnblogs.com/qd666/p/15351071.html
图文来源于网络，如有侵权请联系删除。