数据库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语句的。分析你的查询语句或是表结构的性能瓶颈
详解
作用
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询