怎样做商城手机网站,济宁优化网络公司,二手闲置平台网站怎么做,微信小程序开发制作公司mysql体系结构
1.连接层#xff1a;主要就是做客户端的连接。
2.服务层#xff1a;主要就是 缓存#xff0c;分析器#xff0c;优化器#xff0c;执行器。
3.引擎层#xff1a;就是一些存储引擎#xff0c;包括索引的存储结构。
4.存储层#xff1a;数据存储的磁盘…mysql体系结构
1.连接层主要就是做客户端的连接。
2.服务层主要就是 缓存分析器优化器执行器。
3.引擎层就是一些存储引擎包括索引的存储结构。
4.存储层数据存储的磁盘。 存储引擎
Innodb
1.特点支持事务支持行级锁支持外键。
2.存储结构xxx.ibd(存储数据表结构索引)
Myisam
1.特点速度快支持表级锁。
2.存储结构xxx.sdi(存储表结构)xxx.myd(存储数据)xxx.myi(存储索引)
Memory
1.特点内存存储读取速度快。
2.存储结构xxx.sdi(存储表结构)
三者区别 面试题
1.问Innodb和Myisam的区别
Innodb支持事务而Myisam不支持事务。Innodb支持行级锁和表级锁而Myisam只支持表级锁。Innodb支持外键而Myisam不支持。
2.问存储引擎的选择上该怎么做
当要求数据完整性高且并发下需要保证数据的一致性时我们考虑选择Innodb。当数据完整性要求不高只有数据的查询和插入操作时且追求速度此时我们考虑选择Myisam。 索引
性能分析
1.查询数据库增删改查的次数。
show global status like 对应数据库的名字;
2.慢查询(在配置文件 /mysql/etc/my.cnf中进行修改)
#开启慢查询slow_query_log 1#设置最长的查询时间long_query_time 2
3.explain性能查询工具 使用规则索引失效情况
1.未符合最左前缀原则在查询条件中混合索引的最左字段必须存在就会导致索引全部失效如果在查询条件中跳过顺序使用查询字段会导致部分索引失效。
2.使用范围查询会导致部分索引失效会导致范围符右侧的索引失效。解决方案尽可能使用 。
3.使用聚合函数会导致索引失效。
4.当查询条件发生自动类型转换123 - 123会导致索引失效。
5.使用前缀模糊查询%abc后缀模糊则不会失效会导致索引失效。
6.使用or关键字可能会导致索引失效如果有一边查询字段没有索引就会导致全部索引失效。
7.数据分布影响可能会导致索引失效。mysql会对语句进行评估如果查询的数据基本上大于50~60%就会直接不使用索引进行查询。
索引提示
可能存在某个字段既有单独索引又被包含在复合索引中。
1.建议使用某索引
#建议mysql使用某个索引
select * from database use (column)
2.忽略某个索引
#忽略某个索引
select * from database ignore (column)
3.强制使用某个索引
#强制mysql使用某个索引
select * from database force (column)
前缀索引
# n前缀长度
create index index_name on database(column(n)) 面试题
1.问在Inonodb的索引结构选型上为什么使用B树
相比于二叉树B树的层数耕更低减少io操作速度更快。相比于B树在非叶子节点上是不存储数据的因此在非叶子节点上的页就可以存储更多的指针数据对应的层数就会更低速度就更快。相比于hash表hash只支持等值查询而B树支持范围查询且支持排序速度更快。
2.问select id, username, password from database where username123在数据量很大的时候怎么做优化呢 对 username和password做联合索引。虽然它是个二级索引但是在这个二级索引中包含了id的数正好有查询的所有字段就不会做回表操作。 SQL优化
插入数据
当我们需要插入多条数据的时候如果一条条插入的话就会出现事务的频繁开启和关闭效率十分的低下。因此我们可以使用load指令从本地加载数据速度上会快很多。因为是顺序插入的原因所以速度上会比乱序插入快很多。
#在客户端连接的时候开启本地环境
mysql --local-infile -u root -p#全局设置
set global local_infile 1#执行对应的本地文件
local data local infile 对应的文件名 into table tableName fields terminated by , lines terminated by \n;
主键
页分裂主键底层是存到数据页上一遍主键是自增的所以会进行顺序存储。但是当数据不按顺序的时候会数据在找到对应的位置会会将后百分之五十的数据存储到新的页上如何将新的看数据存储到对应的位置上最终在调整页的位置。(图中是插入50后的页分裂) 页合并但主键进行删除的时候会先将要删除的数据进行标记当达到阈值MERGE_THRESHOLD时就会进行删除并判断前后侧的页是否能合并能就进行合并最终出现页合并。
主键设置的原则
1.在满足业务要求的情况下尽量保证主键的长度较短。因为二级索引中的数据就会主键当二级索引多的情况就会占用大量的空间并在数据的读取的时候需要大量的io操作。
2.插入的主键尽量保证有序也就是自增减少页分裂发生的概率。
3.尽量不要使用uuid作为主键。这会导致页分裂的概率大大增加。
4.在编写业务时尽量不要修改主键避免页分裂的发生。
Order By
在使用Order By的时候如果没有使用索引的话就会在缓冲区中按照对应的字段进行排序效率上比较低因此我们可以创建对应的索引结构来减少此排序操作。
Order By 优化
1. 在使用排序语句的时候要创建索引当存在多个字段的时候我们可以使用混合索引达到覆盖索引的效果并且在这个过程中符合最左前缀原则。
2.当排序字段存在已升序一降序的时候我们在创建对应的混合索引的时候也要设置对应的排序方式。默认是升序
3.如果无法避免缓冲区排序的操作那么我们可以设置更大的缓存区。
limit
使用覆盖索引 子查询来优化sql先通过条件查询出符合条件的主键信息已改主键最为一张表查询对应的所有字段信息。
count
count(主键)直接遍历整张表,把每行的id取出来因为主键不可为null。
count(字段)遍历整张表,把每行的字段值取出来但值不为null直接总个数 1.
count(*)遍历整张表但不会取值速度较快。
count(num)遍历整张表并且不会取值对不为null的行放个num进去并进行累加。
update
要确保操作的字段存在索引这样就可以保证在每次进行事务操作的时候只会使用行级锁如果操作的字段不是索引就会导致行级锁升级为表级锁。但是事务并发场景效率低下。 锁
表级锁
锁的类型
1.表级锁。
2.元数据锁。 3.意向锁为了解决加锁冲突在innodb中行锁和表锁加锁的冲突问题。意向锁之间是相互兼容的
意向共享锁IS在执行select...lock in share mode语句的时候就会添加该锁。(其与表锁中的读锁是兼容的和写锁是互斥的)
意向排他锁(IX)在执行 insert,update,select...for update时添加。其与表锁中的读写锁都互斥
行级锁
锁的类型
1.行级锁锁住对应行的数据防止其他事务修改在读已提交可重复读级别下都是支持的。分为共享锁和排他锁。 2.间隙锁锁住索引之间的间隙。防止其他事务做insert操作导致幻读的情况可重复读级别下是支持的。
3.临键锁可以理解成行级锁 间隙锁其会锁住行数据和数据前后的间隙。可重复读级别下是支持的。
如果在做insert操作的时候对应的条件字段没有建立索引的话就会导致锁升级行级锁-表级锁。
行锁的使用
1.如果条件字段是唯一索引并且给不存在的数据加锁的时候就比如需要某个不存在的数据时就会添加间隙锁锁住该数据应该存储的位置的间隙。
2.如果条件字段不是唯一索引相同的数据会相邻存储会使用临键锁锁住当前数据和数据前后的间隙。如果条件时 这种区间的话就会锁住当前行和后续的间隙吗对应的无穷大
3.间隙锁是可以共存的一个事务使用的间隙锁不会影响其他事务使用间隙锁。 InnoDB引擎
内存结构 框架结构 内存区
1.Buffer Pool缓存区减少io操作加快速度。
2.Change Buffer针对非唯一非一二级索引更改缓冲区在执行DML增删改查如果缓冲区中没有该数据的话就会像存储到Change Buffer中最终做同步操作。
3.Log Buffer用于存储日志的缓冲区。
4.在缓冲区中分为三种page
free page未被使用的page。clean page被使用但数据没有被修改。dirty page脏页数据被修改但是数据未同步。
磁盘区主要就是存储change pagerado log undolog等一些表的信息。
后台线程用于做缓冲区和磁盘的同步。
1.Mater Thread核心后台线程负责调度其他线程。
2.IO Thread主要就是负责做IO的操作。 3.Purge Thread主要就是负责回收事务已提交的undo log在事务提交后undo log就需要被回收。
4.Page Cleaner Thread
协助Master Thread 刷新脏数据到磁盘中减少堵塞。
MVCC
当前读读取的就是最新的版本的数据
快照读读取的数据可能是历史版本。
MVCC的三大部分
1.隐藏字段:
DB_TRX_ID(事务id)当前修改的事务。
DB_ROLL_ID(指向上一版本的地址)回滚指针通过undo和回滚指针构成版本链。
DB_ROW_ID(主键id)当不存在主键或唯一索引的时候机会自动生成隐藏的主键id。
2.undo用于表示不同的版本。
3.readview用于选择版本按照规则选择版本。
