mysql高手进阶优化篇

​MySql理论

逻辑架构

连接层-->服务层-->引擎层-->存储层

存储引擎

查看方式

1.查看mysql现在提供的搜索引擎--->show engines

2.查看mysql当前默认存储引擎show variables like ' storageenginestorage_enginestorageengine'

存储引擎对比

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂

锁机制与InnoDB算法

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。

• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁

• Gap lock：间隙锁，锁定一个范围，不包括记录本身

• Next-key lock：record+gap 锁定一个范围，包含记录本身

性能下降SQL慢的原因

1.查询语句写的不好

2.索引失效

3.关联查询太多join

4.服务器调优及各个参数设置(缓冲，线程数等 )

Mysql索引

定义:是帮助MySql高效获取数据的数据结构(排好序的快速查找的数据结构)

如果没有特别指明，都是B树(多路搜索树 并不一定是二叉树)结构组织索引。

优点

通过索引对数据进行排序。降低数据排序的成本，降低了CPU的消耗

缺点

1.索引也是一张表，该表保存了主键与索引字段，并指向实体表记录，所以索引列也是占用空间的

2.虽然索引提高了查询速度，但是降低更新表的速度(insert update delete) 。因为更新表Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会因为调整更新所带来的减值变化的的索引信息

3.索引只是提高效率的一个因素，如果Mysql有大数据量的表，就需要花时间研究建立最优秀的 索引或优化查询

索引的建立

创建索引:create [unique] index 索引名字 on 表名(列名)

alter 表名 ADD [unique] index 索引名字 on(列名)

删除索引: drop index [索引名字] on 表名

查看索引: show index from 表名

索引结构

B-Tree

树中每个节点最多包含m个孩子 (最多有m个 分支)

出根节点与叶子节点以外，每个节点至少有[ceil(m/2)]个孩子(中间的节点)

若根节点不是叶子节点，则至少有两个孩子（根节点最少有两个分支）

所有叶子节点都在同一层 (没有子节点的都在