Mysql（MyISAM和InnoDB）及Btree和索引优化

迈不过友情╰ 2023-08-17 16:55 181阅读 0赞

**一、引擎**

mysql：MySQL是一个**关系型数据库管理系统，**其中有两种引擎最为常见**MyISAM和InnoDB**

**MyISAM（非聚集索引）  　　**MySQL 5.0 之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务

**InnoDB（聚集索引）  　　 **事务型数据库的首选引擎，支持ACID事务，支持行级锁定, MySQL 5.5 起成为默认数据库引擎

**二、MYSQL索引：Btree索引结构。**

B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。

btree索引：通俗点说就是一颗二叉树

![1474945-20190820101651631-2041889193.jpg][]

**通俗得来说**：如取35为节点，比35小的就放左边，比35大的就放右边。如果我们要寻找87叶子节点的位置，87比35大，所以在右边，然后87比39还大，所以还在右边，比65还大，所以还在右边，一共寻找3次。

**Question**

说到Btree索引，一直以来都有个通俗问题：对于hash索引，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，检索效率比Btree高很多，那为什么不用Hash索引却要用Btree索引？

**Answer：**

有以下几点原因：Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

Hash 索引无法被用来避免数据的排序操作。

Hash 索引在任何时候都不能避免表扫描。

Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

（hash 在mylsam数据表存储是范围存放，只按照节点查找，一旦多个hash值索引节点，那么就要在数据表多个位置寻找，比InnoDB中索引和数据在同一节点寻找复杂得多，所以不推荐hash）（括号的是我总结，不一定对。有错可以指出，我会修改）

**三、聚集索引及其碎片维护**

## 　　（InnoDB）聚簇结构的特点： ##

*  根据主键查询条目时,不用回行(数据就在主键节点下)
 *  如果碰到不规则数据插入时,造成频繁的页分裂

## 　　为什么会产生页分裂？ ##

这是因为聚簇索引采用的是平衡二叉树算法，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；**但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据状态。从而导致了页分裂。**

### 　　优化： ###

聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值, (不要用随机字符串或UUID)，否则会造成大量的页分裂与页移动。在使用InnoDB的时候最好定义成：

id int unsigned primary key auto_increment

![1474945-20190820101748296-679317409.jpg][]

## 索引选择性与前缀索引 ##

因为索引虽然加快了查询速度，但索引也是有代价的，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。  
  
**一般两种情况下不建议建索引。**  
1.表记录比较少，超过2000条可以酌情考虑索引。  
2.索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（\#T）的比值：  
Index Selectivity = Cardinality / \#T

显然选择性的取值范围为(0, 1\]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

**使用索引扫描来优化排序条件**  
1.索引的列顺序和Order by子句的顺序完全一致  
2.索引中所有列的方向（升序，降序）和Order by子句完全一致  
3.Order by中的字段全部在关联表中的第一张表中

**四、MYSQL 优化命令查询**

## 1、查版本号 ##

无论做什么都要确认版本号，不同的版本号下会有各种差异。

>Select  version();

![1474945-20190820101838562-155677179.jpg][]

**2、执行状态分析**

显示哪些线程正在运行

>show processlist;

![1474945-20190820101909242-1212259470.jpg][]

## 3、Show profile ##

精确两位数，小数点。

show profile默认的是关闭的，但是会话级别可以开启这个功能，开启它可以让MySQL收集在执行语句的时候所使用的资源。

**分析****SQL****执行带来的开销是优化****SQL****的常用手段，在****MySQL****数据库中，可以通过配置****profiling****参数来启用****SQL****剖析。**  
**它只能在****session****级别来设置，设置后影响当前****session****；当它开启后，后续执行的****SQL****语句都将记录其资源开销，诸如****IO****，上下文，****CPU****，****MEMORY****等。**

**开启profiling,有个警告，这个参数在以后会被删除，用information\_scheam.PROFILING替代。**

** **![1474945-20190820101933236-902820825.jpg][]  **（设置profiling=1，开启profile）**

![1474945-20190820101950499-46922514.jpg][]**（使用命令：show profile 观看执行时间，15行受影响）**

**根据query id查看某个查询得详细时间耗时。**

# Explain的列分析 #

如查询语句：

explain select * from goods order by goods_id asc \G

转载于:https://www.cnblogs.com/sttchengfei/p/11381436.html

[1474945-20190820101651631-2041889193.jpg]: /images/20230809/35fd8597acf5410ca75516fe66ef9275.png
[1474945-20190820101748296-679317409.jpg]: /images/20230809/43a9c17834264d6aaebbe3c1a6cf314f.png
[1474945-20190820101838562-155677179.jpg]: /images/20230809/c30b62fb324140ce939633fdec520b33.png
[1474945-20190820101909242-1212259470.jpg]: /images/20230809/bde0fdcf409745039c801e0a17c8efea.png
[1474945-20190820101933236-902820825.jpg]: /images/20230809/884eef01347e4a71948f70cbb8b110d8.png
[1474945-20190820101950499-46922514.jpg]: /images/20230809/0429b0c4cc984d84ba8fe99771179a5a.png