mysql高手进阶优化篇

 

MySql理论

逻辑架构

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

存储引擎

查看方式

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

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

存储引擎对比

	InnoDB	MyISAM
主外键	支持	不支持
事务	支持	不支持
行表锁	行锁 操作是只锁住某一行不对其他行有影响 适合高并发	表锁 即使操作一条数据也会锁住整个表 不适合高并发操作
缓存	不仅缓存索引 还缓存真实数据 对内存要求较高 内存大小对性能有绝对性因素	只缓存索引，不缓存数据
表空间	大	小
关注点	事务	性能

存储引擎	InnoDB	MyISAM
存储文件	.frm表定义文件 .ibd数据文件	.frm表定义文件 .myd数据文件 .myi 索引文件
锁	表锁，行锁	表锁
事务	ACID	不支持
CRUD	读写	读多
count	扫表	专门存储的地方
索引结构	B+Tree	B+Tree

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.索引只是提高效率的一个因素࿰