数据库MySQL优化之路

数据库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

作用

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