MySql理论

逻辑架构

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

存储引擎

查看方式

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

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

存储引擎对比

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

索引的建立

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

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

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

查看索引: show index from 表名

本站声明：
1. iCode9 技术分享网（下文简称本站）提供的所有内容，仅供技术学习、探讨和分享；

2. 关于本站的所有留言、评论、转载及引用，纯属内容发起人的个人观点，与本站观点和立场无关；

3. 关于本站的所有言论和文字，纯属内容发起人的个人观点，与本站观点和立场无关；

4. 本站文章均是网友提供，不完全保证技术分享内容的完整性、准确性、时效性、风险性和版权归属；如您发现该文章侵犯了您的权益，可联系我们第一时间进行删除；

5. 本站为非盈利性的个人网站，所有内容不会用来进行牟利，也不会利用任何形式的广告来间接获益，纯粹是为了广大技术爱好者提供技术内容和技术思想的分享性交流网站。