大佬教程收集整理的这篇文章主要介绍了【MySQL我可以讲一个小时】,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
@H_664_8@mySQL四大属性:A(原子性)c;要么全部完成c;要么完全不起作用。底层实现是通过undo log日志去实现的c;当这个事务对数据库进行修改的时候c;innodb 生成对应undo logc;undolog有多个版本c;并且存放的是与上一个版本相反的操作c;他会记录这个SQL执行的相关信息c;如果SQL执行失败发生回滚c;innodb 根据这个undo log内容去做相反的工作c;比如说我执行了一个insert 操作c;那么回滚的时候c;就会执行一个相反的操作c;就是delete,对应updatec;回滚的时候也是执行相反的update。这就是原子性的底层实现。C(一致性)c;一旦事务完成c;不管成功还是失败c;数据处于一致的状态c;而不会是部分完成c;部分失败。事务执行前后c;数据库的完整约束没有遭受破坏c;事务执行前后都是合法的一个数据状态。I(隔离性)c;多事务会同时处理相同的数据c;因此每个事务都应该与其他事务隔离开来c;防止数据损坏。底层实现原理:写-写操作:通过加锁c;原理和 java 里面的锁机制是一样的。写-读操作:MVCC多版本并发控制c;对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性c;避免了频繁加锁互斥。一行数据被多个事务依次修改过后c;在每个事务修改完后c;R_774_11845@ysql会保留修改前的数据undo回滚日志c;并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。在可重复读隔离级别c;事务开启的时候c;执行任何查询sql会生成当前事务的一致性视图read-viewc;也就是第一次SELEct生成一个版本c;read-view视图在事务结束之前都不会变化。如果是读已提交隔离级别c;在每次执行查询sql时都会重新生成视图read-viewc;也就是每次SELEct生成一个版本。执行查询时c;从对应版本链里的最新数据开始逐条跟read-view做比c;会拿着当前事务的id和readview视图数组里面的已创建的最小事务id和已创建的最大事务id进行比较c;这里面分为三种情况c;第一种c;当前事务的id小于数组里面最小的idc;说明这个版本是已提交的事务生成的c;表示这个数据可见。第二种c;当前事务比已创建的最大事务id还要大c;说明这个版本还没开启事务c;表示不可见。第三种c;如果刚好在这个区间c;被访问的事务id在最小事务id与最大事务id之间c;又有二种情况c;第一种c;这个版本是由还没提交的事务生成的c;不可见c;第二种c;表示这个版本是已经提交了的事务生成的c;可见。做比对c;得到最终的快照结果c;通过这种机制保证了隔离性。D(持久性)c;一旦事务完成c;无论发生什么系统错误c;它的结果都不会受到影响c;事务的结果被写到持久化存储器中。底层实现原理是:redo log机制去实现的c;R_774_11845@ysql 的数据是存放在这个磁盘上的c;但是每次去读数据都需要通过这个磁盘ioc;效率就很低c;使用 innodb 提供了一个缓存 bufferc;这个 buffer 中包含了磁盘部分数据页的一个映射c;作为访问数据库的一个缓冲c;从数据库读取一个数据c;就会先从这个 buffer 中获取c;如果 buffer 中没有c;就从这个磁盘中获取c;读取完再放到这个 buffer 缓冲中c;当数据库写入数据的时候c;也会首先向这个 buffer 中写入数据c;定期将 buffer 中的数据刷新到磁盘中c;进行持久化的一个操作。如果 buffer 中的数据还没来得及同步到这个磁盘上c;这个时候 MySQL 宕机了c;buffer 里面的数据就会丢失c;造成数据丢失的情况c;持久性就无法保证了。使用 redolog 解决这个问题c;当数据库的数据要进行新增或者是修改的时候c;除了修改这个 buffer 中的数据c;还会把这次的操作写入到这个 redolog 中c;如果 msyql 宕机了c;就可以通过 redolog 去恢复数据c;redolog 是预写式日志c;会先将所有的修改写入到日志里面c;然后再更新到 buffer 里面c;保证了这个数据不会丢失c;保证了数据的持久性c;redolog 属于记录修改的操作c;主要为了提交@R_963_6754@使用!讲完事务的四大特性c;再来说下事务的隔离性c;当多个线程都开启事务操作数据库中的数据时c;数据库系统要能进行隔离操作c;以保证各个线程获取数据的准确性c;在介绍数据库提供的各种隔离级别之前c;来说一下如果不考虑事务的隔离性c;会发生的几种问题:第一个问题是脏读c;在一个事务处理过程里读取了另一个未提交的事务中的数据。举个例子c;公司发工资了c;领导把四万块钱打到我的账号上c;但是该事务并未提交c;而我正好去查看账户c;发现工资已经到账c;是四万c;非常高兴。可是不幸的是c;领导发现发给我的工资金额不对c;是三万五元c;于是迅速修改金额c;将事务提交c;最后我实际的工资只有三万五元c;我就白高兴一场。第二个问题是不可重复读c;某个数据在一个事务范围内多次查询却返回了不同的结果c;用大白话讲就是事务T1读取数据c;事务T2立马修改了这个数据并且提交事务给数据库c;事务T1再次读取这个数据就得到了不同的结果c;发生了不可重复读。举个例子c;我拿着工资卡去消费c;系统读取到卡里确实有一百块钱c;这个时候我的女朋友刚好用我的工资卡在网上转账c;把我工资卡的一百块钱转到另一账户c;并在我之前提交了事务c;当我扣款时c;系统检查到我的工资卡已经没有钱c;扣款失败c;廖志伟十分纳闷c;明明卡里有钱的。第三个问题是幻读c;事务T1对一个表的数据做了从“1”修改成“2”的操作c;这时事务T2又对这个表插入了一条数据c;而这个数据的值还是为“1”并且提交给数据库c;操作事务T1的用户再查看刚刚修改的数据c;会发现还有一行没有修改。举个例子c;当我拿着工资卡去消费时c;一旦系统开始读取工资卡信息c;这个时候事务开始c;我的女朋友就不可能对该记录进行修改c;也就是我的女朋友不能在这个时候转账。这就避免了不可重复读。假设我的女朋友在银行部门工作c;她时常通过银行内部系统查看我的工资卡消费记录。有一天c;她正在查询到我当月信用卡的总消费金额(SELEct sum(amount) from transaction where month = 本月)为80元c;而我此时正好在外面胡吃海喝后在收银台买单c;消费1000元c;即新增了一条1000元的消费记录(insert transaction … )c;并提交了事务c;随后我的女朋友把我当月工资卡消费的明细打印到A4纸上c;却发现消费总额为1080元c;我女朋友很诧异c;以为出现了幻觉c;幻读就这样产生了。
@H_103_5@mysql调优第一步c;先连接到这个数据库上c;这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。用户名密码认证通过c;连接器会到权限表里面查出你拥有的权限。一个用户成功建立连接后c;即使你用管理员账号对这个用户的权限做了修改c;也不会影响已经存在连接的权限。修改完成后c;只有再新建的连接才会使用新的权限设置。连接完成后c;如果你没有后续的动作c;这个连接就处于空闲状态c;你可以在 show processlist 命令中看到它。客户端如果长时间不发送command到Server端c;连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的c;默认值是 8 小时。第二步:查询缓存。MySQL 拿到一个查询请求后c;会先到查询缓存看看c;之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value对的形式c;被直接缓存在内存中。key 是查询的语句c;value 是查询的结果。如果你的查询能够直接在这个缓存中找到 keyc;那么这个value就会被直接返回给客户端。如果语句不在查询缓存中c;就会继续后面的执行阶段。执行完成后c;执行结果会被存入查询缓存中。你可以看到c;如果查询命中缓存c;R_774_11845@ySQL不需要执行后面的复杂操作c;就可以直接返回结果c;这个效率会很高。大多数情况查询缓存就是个鸡肋c;为什么呢?因为查询缓存往往弊大于利。查询缓存的失效非常频繁c;只要有对一个表的更新c;这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来c;还没使用呢c;就被一个更新全清空了。对于更新压力大的数据库来说c;查询缓存的命中率会非常低。这个鸡肋也有地方可以去使用它c;比如说不会改变的表数据c;极少更新的表c;像一些系统配置表、字典表c;全国的省份之类的表c;这些表上的查询适合使用查询缓存。MySQL提供了这种“按需使用”的方式c;可以将my.cnf参数query_cache_type 设置成2c;query_cache_type有3个值:0代表关闭查询缓存c;1代表开启c;2代表当sql语句中有SQL_CACHE关键词时才缓存。确定要使用查询缓存的语句c;用 SQL_CACHE显式指定c;比如c;SELEct SQL_CACHE * from user where ID=5;第三步c;如果没有命中查询缓存c;就要开始真正执行语句了。MySQL 需要知道你要做什么c;需要对 SQL 语句做解析。
分析器先会做“词法分析”c;你输入的一条 SQL 语句c;R_774_11845@ySQL需要识别出里面的字符串分别是什么c;代表什么。MySQL从你输入的"select"这个关键字识别出来c;这是一个查询语句。它也要把字符串“user”识别成“表名 user”c;把字符串“ID”识别成“列 ID”。做完了这些识别以后c;就要做“语法分析”。根据词法分析的结果c;语法分析器会根据语法规则c;判断你输入的这个 sql语句是否满足Mysql语法。如果你的语句不对c;就会收到“您的sql语法有错误”的错误提醒。语句正确之后c;会丢到分析机里面执行分析c;语法分析由Bison生成c;经过bison语法分析之后c;会生成一个语法树。比如c;你的操作是SELEct还是insertc;你需要对那些字段进行操作c;作用在哪张表上面c;条件是什么。
经过了分析器c;R_774_11845@ySQL就知道这些字符串代表什么c;要做什么了。在开始执行之前c;还要先经过优化器的处理。第四步c;优化器c;在表里面有多个索引的时候c;决定使用哪个索引;或者在一个语句有多表关联的时候c;优化器可以决定各个表的连接顺序c;同一条多表查询的sqlc;执行的方案会有多种c;比如c;
SELEct * from user1 join user2 on user1.id = user2.id where user1.name=liaozhiwei and user2.name=haoshuai;
既可以先从表user1 里面取出 name=liaozhiwei的 ID 值c;再根据 ID 值关联到表user2c;再判断user2 里面 name的值是否等于liaozhiwei。也可以先从表user2 里面取出 name=haoshuai的 ID 值c;再根据 ID 值关联到user1c;再判断user1 里面 name 的值是否等于haoshuai。这两种执行方法的逻辑结果是一样的c;但是执行的效率会有不同c;而优化器的作用就是决定选择使用哪一个方案。执行方案就确定下来了c;然后进入执行器阶段。第五步c;开始执行的时候c;要先判断一下你对这个表有没有执行查询的权限c;如果没有c;就会返回没有权限的错误。如果有权限c;就打开表继续执行。打开表的时候c;执行器就会根据表的引擎定义c;去使用这个引擎提供的接口。比如c;我有一条sql:SELEct * from user where id=10;
执行器调用 InnoDB 引擎接口取这个表的第一行c;判断 ID 值是不是10c;如果不是则跳过c; 调用引擎接口取“下一行”c;重复相同的判断逻辑c;直到取到这个表的最后一行c;如果是将这行保存在结果集中。执行器将遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。到这一步c;这个语句就执行完成了。
第一步c;从初期的一个需求规划c;也就是对表的设计就开始了c;先从底层开始说吧c;R_774_11845@ysql底层的页的大小是16kbc;假如c;我有一张表对单行数据量就有16kbc;那么这张表就只能存储1条数据了c;这是非常恐怖的。mysql是从磁盘读取数据到内存的c;是以磁盘块为基本单位的c;位于同一磁盘块中的数据会被一次性读取出来c;不是按需读取。以InnoDB存储引擎来说c;它使用页作为数据读取单位c;页是其磁盘管理的最小单位c;默认大小是16kb。系统的一个磁盘块的存储空间往往没有这么大c;所以InnoDB每次申请磁盘空间时都会是多个地址连续磁盘块来达到页的大小16KB。在查询数据时一个页中的每条数据都能定位数据记录的位置c;这会减少磁盘 I/O 的次数c;提高查询效率。InnoDB存储引擎在设计时是将根节点常驻内存的c;力求达到树的深度不超过 3c;也就是说I/O不超过3次。树形结构的数据可以让系统高效的找到数据所在的磁盘块c;这里就可以说一下这个b树和b+树了c;B树的结构是每个节点中有key也有valuec;而每一个页的存储空间是16kbc;如果数据较大时将会导致一页能存储数据量的数量很小。B+Tree的结构是将所有数据记录节点按照键值大小顺序存放在同一层的叶子节点上c;而非叶子节点上只存储 key 值信息c;这样可以大大加大每个节点存储的key 值数量c;降低B+Tree的高度。所以通过mysql的底层存储的原理和数据结构c;我们在设计表的时候c;尽量减少单行数据的大小c;字段的宽度设得尽可能小c;尽可能不用text、Blob、Clob等类型c;它会增加存储空间的占用c;读取速度较慢。能用数字型字段就不要设计为字符型c;因为字符型锁占的存储空间更大c;比如c;性别这个字段不用男女进行存储c;改为0/1的方式c;这样不仅可以控制数据量的大小c;增加了同一高度下的B+树容纳的数据量c;还能提高检索速度。尽量使用varchar/nvarchar代替char/ncharc;因为变长字段空间小c;可以节省存储空间。不在数据库中存储图片、文件等大数据c;可以通过第三方云存储c;存放图片或者文件地址。金额用decimalc;注意长度和精度。如果存储的数据范围超过decimal的范围c;建议将数据拆成整数和小树分开存储。尽可能不要给数据库留null值c;尤其是时间、整数等类型c;可以在建表的时候就给非空设置。第二步c;就是建索引c;先说一下mysql的三种索引c;聚簇索引c;辅助索引c;覆盖索引。聚集索引的叶子节点称为数据页c;每个数据页通过一个双向链表来进行链接c;而且数据页按照主键的顺序进行排列。每个数据页上存放的是完整的行记录c;而在非数据页的索引页中c;存放的仅仅是键值及指向数据页的偏移量c;而不是一个完整的行记录。如果定义了主键c;InnoDB会自动使用主键来创建聚集索引。如果没有定义主键c;InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引c;InnoDB会隐式定义一个主键来作为聚集索引。辅助索引它叶子节点中没有行记录的全部数据c;叶子节点除了包含键值以外c;每个叶子节点的索引行还包含了一个书签c;该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。覆盖索引先遍历辅助索引c;再遍历聚集索引c;而如果要查询的字段值在辅助索引上就有c;就不用再查聚集索引了c;这显然会减少IO操作。除了这三种索引c;还有一种联合索引c;它是对表上的多个列进行索引c;键值都是排序的c;通过叶子节点可以顺序的读出所有数据c;联合索引的好处在于能起到"一个顶三个"的作用。比如建了一个(a,b,C)的复合索引c;那么实际等于建了(a),(a,b),(a,b,C)三个索引c;每多一个索引c;都会增加写操作的开销和磁盘空间的开销c;对于大数据的表c;这是不小的开销。另外它还可以避免filesort排序c;因为filesort的过程c;一行数据会被读两次c;第一次是where条件过滤时c;第二个是排完序后还得用行指针去读一次。filesort的过程是这样的:第一步先根据表的索引或者全表扫描c;读取所有满足条件的记录。第二步c;存储每一行排序列c;就是order by用到的列值c;还有行记录指针c;就是指向该行数据的行指针c;把这二个存储到缓冲区。第三步c;当缓冲区满后c;运行一个快速排序来将缓冲区中数据排序c;将排序完的数据存储到一个临时文件c;保存一个存储块的指针c;当然如果缓冲区不满c;则不会重建临时文件了。直到将所有行读完c;建立相应有序的临时文件。第四步c;对块级进行排序c;这个类似归并排序算法c;只通过两个临时文件的指针来不断交换数据c;最终达到两个文件c;都是有序的c;直到所有的数据都排序完毕。第五步c;采取顺序读的方式c;将每行数据读入内存c;取出数据传到客户端。为什么要说这个filesort呢?举二个场景c;第一个c;如果order by的条件不在索引列上会产生filesortc;第二个c;排序的字段不在where的条件中c;没有办法走索引排序Indexc;而是走的文件排序filesort 。这种概率其实还是挺高的。这个时候就需要看文件排序用的是单路排序还是双路排序c;单路排序会把所有需要查询的字段都放到 sort buffer 中c;而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序c;然后再通过主键回到原表查询需要的字段。mysql优化器使用双路排序还是单路排序是有自己的算法判断的c;如果查询的列字段大于max_length_for_sort_data变量c;则会使用双路排序c;反之则会使用单路排序c;单路排序速度是更快的c;不过比较占据内存c;如果在内存空间允许的情况下想要使用单路排序的话c;可以增加max_length_for_sort_data变量的大小c;R_774_11845@ax_length_for_sort_data变量默认为1024字节。第三步c;针对SQL进行调整c;在写SQL的时候遵循最左前缀原则c;向右匹配直到遇到范围查询(>、<、between、like)就停止匹配c;范围列可以用到索引c;但是范围列后面的列无法用到索引。like以通配符%开头索引失效会变成全表扫描的操作。如果查询条件中含有函数或表达式c;将导致索引失效而进行全表扫描。只要列中包含有 NULL 值都将不会被包含在索引中c;复合索引中只要有一列含有 NULL 值c;那么这一列对于此复合索引就是无效的。不要使用SELEct *c;改用SELEct加字段名称c;因为SELEct *走的聚集索引c;会进行全表扫描c;如果一定要使用SELEct *的话c;R_774_11845@ysql至少使用5.6版本c;这个版本有一个离散读的优化c;离散读的优化是将离散度大的列放到联合索引的前面c;举个例子c;
SELEct * from user where staff_id = 2 and customer_id = 584
c;这个时候索引优化会将customer_id放到前面c;因为它的离散度更高c;可以通过SELEct count(disTinct customer_id),count(disTinct staff_id) from user
查看列的离散度。有一个ICP的优化c;以往c;根据索引查找记录c;再根据WHERE条件来过滤记录。使用ICP优化后c;会在取出索引的同时c;直接根据WHERE条件过滤c;将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取c;从而提高性能。还有一个MRR优化c;是批量处理对键值的查询操作ICP优化c;减少缓冲池中页被替换的次数c;使数据访问变得较为顺序。辅助索引查询得到书签后c;先对主键进行排序c;再按序进行查找。另外在写sql的时候c;尽量使用它的一个执行计划c;去看我们的索引是不是失效了。索引失效有这么几种情况c;上面也提到过几个c;如果条件中有orc;即使其中有部分条件带索引也不会使用。对于复合索引c;如果不使用前列c;后续列也将无法使用。like以%开头。列类型是字符串c;那一定要在条件中将数据使用引号引用起来c;否则不使用索引。where中索引列有运算c;有函数的c;不使用索引。如果mysql觉得全表扫描更快的时候c;数据少的情况下c;不使用索引。第四步c;随着数据量的增涨之后c;会考虑一个数据的分区c;Partition分区c;表分区必须在表建立的时候创建规则c;而已经存在的没有创建过表分区规则的表需要重新做导入处理。如果想修改有规则的表分区c;只能新增c;不要随意删除c;删除表分区会造成该表分区内部数据也一起被删除掉。除此之外c;分区也需要结合实际场景进行分区c;而且Partition分区也是有一个局限性c;因为单台的mysql服务器支持1024个分区c;一旦达到这个分区上限c;考虑垂直拆分和水平拆分了。垂直分区c;它是将单表变多表c;这样也是有些优点的c;一条数据存储的数据量越小c;三层b+所容纳的数据量是更多的c;这样一个分区就可以承载多个数据c;多个分区它承载的数据就会更多c;这是一个累加的效果c;不过也有缺点c;就是进行表的垂直划分的时候c;还需要考虑它的一个关联性c;在进行sql查询的情况下c;需要反复测试c;考虑它的一个性能问题c;最好的结果就是拆分出来的表还是能够支持铁定的业务线。比如一个包含了大text和BLOB列的表c;这些text和BLOB列又不经常被访问c;这时候就要把这些不经常使用的text和BLOB了划分到另一个分区c;在保证它们数据相关性的同时还能提高访问速度。随着数据量持续的增涨c;这个时候就需要考虑水平分区了c;水平分区有多种模式c;Range(范围)模式:允许DBA将数据划分不同范围。比如DBA可以将一个表通过年份划分成三个分区c;80年代的数据c;90年代的数据以及任何在2000年之后的数据。Hash(哈希)模式允许DBA通过对表的一个或多个列的Hash Key进行计算c;最后通过这个Hash码不同数值对应的数据区域进行分区c;比如DBA可以建立一个c;对表的主键进行分区的表。List(预定义列表)模式:允许系统通过DBA定义列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表c;分别根据2004年c;2005年c;2006年值对应数据。Composite(复合模式)c;就是多模式的组合使用c;在初始化已经进行了Range范围分区的表上c;我们可以对其中一个分区再进行hash哈希分区。第五步c;就是冷热备份c;对于一些无用的数据c;这个时候根据实际的需求c;对数据进行一个实时的备份c;保证MySQL的数据保持在一个比较稳定的情况。
整个图片c;歇歇眼c;文章大多不换行c;排版基本都是一块的c;接近九千字c;口速快的话c;一个小时差不多可以讲完c;这篇博文主要是针对面试口述的c;备战面试。啊c;又要面试啦c;太难了。
以上是大佬教程为你收集整理的【MySQL我可以讲一个小时】全部内容,希望文章能够帮你解决【MySQL我可以讲一个小时】所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。