徐州市住房和城乡建设局网站首页,如何建立微信网站,邯郸建设公司网站,搭建是什么意思巩固基础#xff0c;砥砺前行 。 只有不断重复#xff0c;才能做到超越自己。 能坚持把简单的事情做到极致#xff0c;也是不容易的。
mysql怎么优化 : MySQL的优化可以从以下几个方面入手#xff1a;
数据库设计优化#xff1a;合理设计表结构#xff0c;选择合适的数…巩固基础砥砺前行 。 只有不断重复才能做到超越自己。 能坚持把简单的事情做到极致也是不容易的。
mysql怎么优化 : MySQL的优化可以从以下几个方面入手
数据库设计优化合理设计表结构选择合适的数据类型规范化表结构避免冗余数据等。索引优化为经常查询的列添加索引合理选择索引类型避免使用过多的索引。SQL语句优化避免使用SELECT *使用JOIN代替子查询尽量避免使用LIKE和%等模糊查询使用EXPLAIN查看SQL语句执行计划。缓存优化使用缓存来减少对数据库的访问次数如使用Redis缓存热门数据。硬件优化增加内存、优化磁盘I/O等提高服务器的性能。代码优化优化应用程序代码减少不必要的数据库操作避免频繁的连接和关闭数据库。定期维护定期备份数据、优化表、清理日志等保持数据库的稳定性和高效性。
数据库怎么调优 : 数据库调优是指通过优化数据库的性能提高其响应速度和处理能力从而提高系统的整体性能和稳定性。以下是一些常见的数据库调优方法
1.优化查询语句尽量避免使用SELECT *等大量数据的查询语句使用索引查询避免JOIN查询等。 2.优化索引合理设计数据库索引删除不必要的索引使用覆盖索引等。 3.优化表结构合理设计表结构避免使用大量的NULL值避免使用过多的TEXT、BLOB类型的数据。 4.优化缓存增加缓存大小调整缓存策略避免频繁的I/O操作。 5.优化参数设置根据实际情况调整数据库的参数设置如缓冲池大小、连接数、查询缓存等。 6.优化存储引擎选择合适的存储引擎如MyISAM、InnoDB等。 7.分区和分表对于大型数据库可以根据数据的特点进行分区和分表提高查询效率和可维护性。 8.定期维护定期进行数据库的备份、清理和优化避免数据紊乱和性能下降。 总之数据库调优需要根据具体情况进行定制化的优化方案综合考虑数据库的硬件环境、软件配置、应用需求等因素。
Mysql慢查询该如何优化?
1.检查是否走了索引如果没有则优化SQL利用索引 2.检查所利用的索引是否是最优索引 3.检查所查字段是否都是必须的是否查询了过多字段查出了多余数据 4.检查表中数据是否过多是否应该进行分库分表了 5.检查数据库实例所在机器的性能配置是否太低是否可以适当增加资源
Mysql锁有哪些如何理解
按锁粒度分类: 1.行锁锁某行数据锁粒度最小并发度高 2.表锁锁整张表锁粒度最大并发度低 3.间隙锁锁的是一个区间 还可以分为: 1.共享锁也就是读锁一个事务给某行数据加了读锁其他事务也可以读但是不能写 2.排它锁也就是写锁一个事务给某行数据加了写锁其他事务不能读也不能写 还可以分为: 1.乐观锁并不会真正的去锁某行记录而是通过一个版本号来实现的 2.悲观锁上面所的行锁、表锁等都是悲观锁 在事务的隔离级别实现中就需要利用所来解决幻读
锁的类型有哪些
基于锁的属性分类共享锁、排他锁。 基于锁的粒度分类行级锁(INNODB)、表级锁(INNODB、MYISAM)、页级锁(BDB引擎)、记录锁、间隙锁、临键 锁。 基于锁的状态分类意向共享锁、意向排它锁。 共享锁(Share Lock) 共享锁又称读锁简称S锁当一个事务为数据加上读锁之后其他事务只能对该数据加读锁而不能对数据加写锁直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据读取数据的时候不支持修改避免出现重复读的问题。 排他锁exclusive Lock) 排他锁又称写锁简称X锁当一个事务为数据加上写锁时其他请求将不能再为数据加任何锁直到该锁释放之后其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候不允许其他人同时修改也不允许其他人读取。避免了出现脏数据和脏读的问题。 表锁 表锁是指上锁的时候锁住的是整个表当下一个事务访问该表的时候必须等前一个事务释放了锁才能进行对表进行 访问 特点 粒度大加锁简单容易冲突 行锁 行锁是指上锁的时候锁住的是表的某一行或多行记录其他事务访问同一张表时只有被锁住的记录不能访问其他 的记录可正常访问 特点粒度小加锁比表锁麻烦不容易冲突相比表锁支持的并发要高 记录锁(Record Lock) 记录锁也属于行锁中的一种只不过记录锁的范围只是表中的某一条记录记录锁是说事务在加锁后锁住的只是表的 某一条记录。 精准条件命中并且命中的条件字段是唯一索引 加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题也避免了在修改的事务未提交前被其他事务读 取的脏读问题。 页锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。 所以取了折衷的页级一次锁定相邻的一组记录。 特点开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间并发度一般 间隙锁(Gap Lock) 属于行锁中的一种间隙锁是在事务加锁后其锁住的是表记录的某一个区间当表的相邻ID之间出现空隙则会形成一 个区间遵循左开右闭原则。 范围查询并且查询未命中记录查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ重复读的事务级别 中。 触发条件防止幻读问题事务并发的时候如果没有间隙锁就会发生如下图的问题在同一个事务里A事务的两次查询出的结果会不一样。 比如表里面的数据ID为1,4,5,710那么会形成以下几个间隙区间-n-1区间1-4区间7-10区间10-n区 间一n代表负无穷大n代表正无穷大 临建锁(Next-Key Lock) 也属于行锁的一种并且它是INNODB的行锁默认算法总结来说它就是记录锁和间隙锁的组合临键锁会把查询出来 的记录锁住同时也会把该范围查询内的所有间隙空间也会锁住再之它会把相邻的下一个区间也会锁住 触发条件范围查询并命中查询命中了索引。结合记录锁和问隙锁的特性临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后在范围区间内数据不允许被修改和插入。 如果当事务A加锁成功之后就设置一个状态告诉后面的人已经有人对表里的行加了一个排他锁了你们不能对整个表加共享锁或排它锁了那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁避免了对整个索引树的每个节点扫描是否加锁而这个状态就是意向锁。 意向共享锁 当一个事务试图对整个表进行加共享锁之前首先需要获得这个表的意向共享锁。 意向排他锁 当一个事务试图对整个表进行加排它锁之前首先需要获得这个表的意向排它锁。
B树和B树的区别为什么Mysql使用B树
B树的特点 1.节点排序 2.一个节点了可以存多个元素多个元素也排序了 B树的特点1.拥有B树的特点2.叶子节点之间有指针 3.非叶子节点上的元素在叶子节点上都冗余了也就是叶子节点中存储了所有的元素并且排好顺序
B树和B树的区别为什么Mysql使用B树
B树的特点 1.节点排序 2.一个节点了可以存多个元素多个元素也排序了 B树的特点:1.拥有B树的特点2.叶子节点之间有指针 3.非叶子节点上的元素在叶子节点上都冗余了也就是叶子节点中存储了所有的元素并且排好顺序 Mysql索引使用的是B树因为索引是用来加快查询的而B树通过对数据进行排序所以是可以提高查询速度的然后通过一个节点中可以存储多个元素从而可以使得B树的高度不会太高在Mysql中一个Innodb页就是一个B树节点一个Innodb页默认16kb所以一般情况下一颗两层的B树可以存2000万行左右的数据然后通过利用B树叶子节点存储了所有数据并且进行了排序并且叶子节点之间有指针可以很好的支持全表扫描范围查找等SQL语句。
索引数据结构有btree和hash但是我们一般使用的都是btree为啥不用hash呢
hash冲突如果冲突了数组就会扩容 不支持范围查找如果这样查询就会全表扫描。
雪花❄️算法也是递增只不过不是严格的数据递增而是趋势递增。
为什么会有最左元素匹配原则呢
考虑索引的结构。
mysql:可重复读 oracle:读已提交
可重复读不仅针对当前数据那一时刻的所有数据都被定格了
什么是回表
查找到索引文件的地址后还需要再次查找对应的数据文件。这个过程叫回表。 innodb的非主键索引叶子节点存储的是索引下标然后按照索引下标再去主键中查找对应的数据也是回表。
聚集索引和非聚集索引的区别
索引和数据在一起的就是聚集索引。innodb的主键索引就是聚集索引myisam的主键索引就是非聚集索引索引文件和数据文件是分开存储的查找的时候先到索引文件中查到对应的索引地址再到数据文件中查找数据。索引所在行的整行数据
mysql聚簇和非聚簇索引的区别
都是B树的数据结构 聚簇索引将数据存储与索引放到了一块、并且是按照一定的顺序组织的找到索引也就找到了数据数据的物理存放顺序与索引顺序是一致的即只要索引是相邻的那么对应的数据一定也是相邻地存放在磁盘上的非聚簇索引叶子节点不存储数据、存储的是数据行地址也就是说根据索引查找到数据行的位置再取磁盘查找数据这个就有点类似一本树的目录比如我们要找第三章第一节那我们先在这个目录里面找找到对应的页码后再去对应的页码看文章。 优势 1、查询通过聚簇索引可以直接获取数据相比非聚簇索引需要第二次查询非覆盖索引的情况下效率要高 2、聚簇索引对于范围查询的效率很高因为其数据是按照大小排列的 3、聚簇索引适合用在排序的场合非聚簇索引不适合 劣势: 1、维护索引很昂贵特别是插入新行或者主键被更新导至要分页page sp1it)的时候。建议在大量插入新行后选在负载较低的时间段通过OPTIMIZE TABLE优化表因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片 2、表因为使用UUId随机ID作为主键使数据存储稀疏这就会出现聚族索引有可能有比全表扫面更慢所以建 议使用int的auto_increment作为主键 3、如果主键比较大的话那辅助索引将会变的更大因为辅助索引的叶子存储的是主键值过长的主键值会导致非 叶子节点占用占用更多的物理空间 InnoDB中一定有主键主键一定是聚簇索引不手动设置、则会使用unique索引没有unique索引则会使用 数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引辅助索引访问数据总是需要二次查找非聚簇索引都是辅助索引像复合索引、前缀索引、唯一索引辅助索引叶子节点存储的不再是行的物理位置而是主键值 MyISM使用的是非聚簇索引没有聚簇索引非聚簇索引的两棵B树看上去没什么不同节点的结构完全一致只是存储的内容不同而已主键索引B树的节点存储了主键辅助键索引B树存储了辅助键。表数据存储在独立的地方这两颗B树的叶子节点都使用一个地址指向真正的表数据对于表数据来说这两个键没有任何差别。由于索引树是独立的通过辅助键检索无需访问主键的索引树。 如果涉及到大数据量的排序、全表扫描、count之类的操作的话还是MyISAM占优势些因为索引所占空间小 这些操作是需要在内存中完成的。
聚簇索引与非聚集索引的特点是什么 在InnoDB中聚簇索引和非聚簇索引实际上是物理存储方式的一个不同。 聚簇索引 1.聚簇索引将数据存储在索引树的叶子节点上。 2.聚簇索引可以减少一次查询因为查询索引树的同时就能获取到数据。 3.聚簇索引的缺点是对数据进行修改或删除操作时需要更新索引树会增加系统的开销。 4.聚簇索引通常用于数据库系统中主要用于提高查询效率。
非聚簇索引又称二级索引/辅助索引 1.非聚簇索引不将数据存储在索引树的叶子节点上而是存储在数据页中。 2.非聚簇索引在查询数据时需要两次查询一次查询索引树获取数据页的地址再通过数据页的地址查询数据通常情况下来说是的但如果索引覆盖的话实际上是不用回表的 3.非聚簇索引的优点是对数据进行修改或删除操作时不需要更新索引树减少了系统的开销。 4.非聚簇索引通常用于数据库系统中主要用于提高数据更新和删除操作的效率。
什么是Hash索引
哈希索引hash index基于哈希表实现。哈希索引通过Hash算法将数据库的索引列数据转换成定长的哈希码作 为key将这条数据的行的地址作为value一并存入Hash表的对应位置。 在MySQL中只有Memeory引擎显式的支持哈希索引这也是Memory引擎表的默认索引结构Memeory同时也支持B-Tree索引。并且Memory引擎支持非唯一哈希索引如果多个列的哈希值相同或者发生了Hash碰撞索引会在对应Hash键下以链表形式存储多个记录地址。 哈希索引还有如下特点 哈希索引不支持部分索引列的匹配查找因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例 如在数据列AB上建立哈希索引如果查询只有数据列A则无法使用该索引。 哈希索引具有哈希表的特性因此只有精确匹配所有列的查询对于哈希索引才有效比如、、IN(因为 数据的存储是无序的)且无法使用任何范围查询。 因为数据的存储是无序的哈希索引还无法用于排序。 对于精确查询则哈希索引效率很高时间复杂度为O(1)除非有很多哈希冲突不同的索引列有相同的哈希值如果发生哈希冲突则存储引擎必须遍历链表中的所有数据指针逐行比较直到找到所有符合条件的行。哈希冲突越多代价就越大
InnoDB到底支不支持哈希索引
对于InnoDB的哈希索引确切的应该这么说 •InnoDB用户无法手动创建哈希索引这一层上说InnoDB确实不支持哈希索引; InnoDB会自调优(self-tuning)如果判定建立自适应哈希索引(Adaptive HashIndexAHI)能够提升查询 效率InnoDB自己会建立相关哈希索引这一层上说InnoDB又是支持哈希索引的; 那什么是自适应哈希索引(Adaptive Hash IndexAHI)呢 1、自适应即我们不需要自己处理当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点就 会给其建立一个hash索引; 2、hash索引底层的数据结构是散列表Hash表其数据特点就是比较适合在内存中使用自适应Hash索引存 在于InnoDB架构中的缓存中不存在于磁盘架构中
什么是唯一索引?
讲起来非常简单其实和“普通索引类似不同的就是索引列的值必须唯一但允许有空值。可以是单列 唯一索引也可以是联合唯一索引。 最大的所用就是确保写入数据库的数据是唯一值 什么时候应该使用唯一索引呢 我们前面讲了唯一索引最大的好处就是能保证唯一性。看似没什么太大的价值可能就会有同学说我业务层做一个重复检查不就好了。问题就在这个地方“业务是无法确保唯一性的”除非你说你的代码没有BUG。很多时候业务场景需要保证唯一性如果不在数据库加限制的话总有一天会出现脏数据。 那又有同学就说了既然你不想重复你可以使用主键索引。这个回答也很有意思。 我们确实可以通过主键索引来保证唯一但是如果你的数据不能保证有序插入。比如说身份证字段 你如果用身份证字段作为主键的话会导致查询效率降低。 唯一索引还有一个好处就是可以为空真实的业务场景肯定是可以保证身份证为空的如果没有绑定身 份证就不让注册好像也有点说不过去。 聚簇索引的原理就不在这里细讲了会有一个单独的章节来介绍。
唯一索引是否会影响性能呢
我找们通过和普通索引来做一个对比有查询和插入两个场悬 首先第一个数据查询一般情况下来说索引是通过 B树 从根节点开始层序遍历到叶子结点数据页内部通过二分检索 普通索引查到满足条件的第一条记录继续查找下一条记录直到找到不满足条件的记录 唯一索引 查到第一个满足条件的记录就停止搜索。 InnoDB它是以数据页为单位进行读写的我们读一条记录并不是从磁盘加载一条记录而是以页为单 普通索引比唯一索引就多了一次 查找和判断下一条 记录的操作也就是一次指针寻找数据和一次计算。当然还有一种特殊情况读取到的这条数据正好是数据页的最后一条但是这种概率也是非常低几乎可以忽略不计。 整体看下来看上去性能差距并不大对吧. 来看第二个更新的性能我们按照上面图上的例子在 2 和6 之间插入一个 3. 在内存中 普通索引找到2和6之间的位置→插入值→ ·唯一索引找到卫和6之间的位置→当判断有没有冲突→插入值→结束 不在内存中 普通索引 将更新记录在 change buffer→结束 •唯一索引 将数据页读入内存→当判断到没有冲突→插入值→结束 数据读取到内存涉及了随机|O访问这是在数据库里面成本最高的操作之一 而change buffer就可以减少这种随机磁盘访问所以性能提示比较明显。所以在这一块来说如果两者在业务场景下都能满足时可以优先考虑使用普通索引。
什么是联合索引组合索引复合索引?
我们在索引回顾的时候和大家对索引做了一个分类对吧按照字段个数来分的话就分为了单列索引和组合 索引对吧。那么他们之间的特点是什么呢我们来看 单列索引 一个索引只包含了一个列一个表里面可以有多个单加要2但早这不叫组各思己 •组合索引联合索引复合索引一个索引包含多个列。 看上去感觉这组合索引并没有太大作用是吧我一个列已经有一个索引了我还要这组合索引干嘛 真相往往不那么简单首先我们得承认我们的业务千变万化我们的查询语句条件肯定是非常多的。 高效率如果说只有单列索引那就会涉及多次二级索引树查找再加上回表性能相对于联合索引来说 是比较低的。 减少开销 我们要记得创建索引是存在空间开销的对于大数据量的表使用联合索引会降低空间开销。 索引覆盖 如果组合索引索引值已经满足了我们的查询条件那么就不会进行回表直接返回。 但是我们按照我们的查询条件去创建一个联合索引的话就避免了上面的问题
那么联合索引是怎么工作的
呢 这里涉及到了一个重点叫做 最左前缀简单理解就是只会从最左边开始组合组合索引的第一个字段必须出现在查询组句中还不能跳跃只有这样才能让索引生效比如说我查询条件里面有组合索引里面的第二个字段那么也是不会走组合索引的。举个例子 SQL // 假设给username,age创建了组合索引 // 这两种情况是会走索引的 select * from user where username 张三 select * from user where username // 这种是不会走索引的 select * from user where age 18 select * from user where city 北京 and age18 and age 18 复合索引创建时字段顺序不一样使用效果一样吗 SQL // 特殊情况这种也是会走索引的虽然我的age在前面username在后面。 // 刚刚不是手最左前缀匹配吗为什么放到第二位也可以呢 // 虽说顺序不一致但是在SQL执行过程中根据查询条件命中索引//无论我username在不在前面都会按照username去进行索引查找。select * from user where age 18 and username ‘张三
mysql索引的数据结构各自优劣
索引的数据结构和具体存储引擎的实现有关在MySQL中使用较多的索引有Hash索引B树索引等InnoDB存储引擎的默认索引实现为B树索引。对于哈希索引来说底层的数据结构就是哈希表因此在绝大多数需求为单条记录查询的时候可以选择哈希索引查询性能最快其余大部分场景建议选择BTree索引。 B树 B树是一个平衡的多叉树从根节点到每个叶子节点的高度差值不超过1而且同层级的节点间有指针相互链接。在B树上的常规检索从根节点到叶子节点的搜索效率基本相当不会出现大幅波动而且基于索引的顺序扫描时也可以利用双向指针快速左右移动效率非常高。因此B树索引被广泛应用于数据库、文件系统等场景。
哈希索引: 哈希索引就是采用一定的哈希算法把键值换算成新的哈希值检索时不需要类似B树那样从根节点到叶子节点 逐级查找只需一次哈希算法即可立刻定位到相应的位置速度非常快 如果是等值查询那么哈希索引明显有绝对优势因为只需要经过一次算法即可找到相应的键值前提是键值都是 唯一的。如果键值不是唯一的就需要先找到该键所在位置然后再根据链表往后扫描直到找到相应的数据; 如果是范围查询检索这时候哈希索引就毫无用武之地了因为原先是有序的键值经过哈希算法后有可能变成 不连续的了就没办法再利用索引完成范围查询检索; 哈希素引也没办法利用索引完成排序以及likexxx%这样的部分模糊查询这种部分模糊查询其实本质上也是 范围查询; 哈希索引也不支持多列联合索引的最左匹配规则; B树索引的关键字检索效率比较平均不像B树那样波动幅度大在有大量重复键值情况下哈希索引的效率也是 极低的因为存在哈希碰撞问题。 Innodb是如何实现事务的
Innodb通过Buffer PoolLogBuffer,Redo LogUndo Log来实现事务以一个update语句为例:1.Innodb在收到一个update语句后会先根据条件找到数据所在的页并将该页缓存在Buffer Pool中2.执行update语句修改Buffer Pool中的数据也就是内存中的数据 3.针xupdate语句生成一个RedoLog对象并存入LogBuffer中 4.针对update语句生成undolog日志用于事务回滚 5.如果事务提交那么则把RedoLog对象进行持久化后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中 6.如果事务回滚则利用undolog日志进行回滚
mysql left join中on后加条件判断和where中加条件的区别
left join中关于where和on条件的几个知识点 1.多表left join是会生成一张临时表并返回给用户 2.where条件是针对最后生成的这张临时表进行过滤过滤掉不符合where条件的记录是真正的不符合就过滤掉。 3.on条件是对left join的右表进行条件过滤但依然返回左表的所有行右表中没有的补为NULL 4.on条件中如果有对左表的限制条件无论条件真假依然返回左表的所有行,但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容不影响返回行数。 结论 1.where条件中对左表限制不能放到on后面 2.where条件中对右表限制放到on后面会有数据行数差异比原来行数要多
ACID靠什么保证的?
A原子性由undo log日志保证它记录了需要回滚的日志信息事务回滚时撤销已经执行成功的sql C一致性由其他三大特性保证、程序代码要保证业务上的一致性 |隔离性由MVCC来保证 D持久性由内存redo log来保证mysql修改数据同时在内存和redolog记录这次操作宕机的时候可以从redo log恢复 InnoDB redo 1og 写盘InnoDB 事务进入prepare状态。 如果前面 prepare 成功bin1og 写盘再继续将事务日志持久化到bin1og如果持久化成功那么 InnoDB 事务 则进入 commit 状态在 redo 1og 里面写一个 commit 记录 redolog的刷盘会在系统空闲时进行
MySQL的索引结构是什么样的聚簇索引和非聚簇索引又是什么?
二叉树-》 AVL树-》 红黑树-》 B-树-》 B树 二叉树每个节点最多只有两个子节点左边的子节点都比当前节点小右边的子节点都比当前节点大。 AVL树 树中任意节点的两个子树的高度差最大为1 红黑树1、每个节点都是红色或者黑色。2 根节点是黑色。3 每个叶子节点都是黑色的空节点。4 红色节点的父子节点都必 须是褐色。5 从任一节点到其每个叶子节点的所有路径都包含相同的黑色节点。 B-树1、B-树的每个非叶子节点的子节点个数都不会超过D这个D就是B-树的阶2、所有的叶子节点都在同一层。3.所有 节点关键字都是按照递增顺序排列。 B树 1、非叶子节点不存储数据只进行数据索引。2、所有数据都存储在叶子节点当中。3、每个叶子节点都存有相邻叶 子节点的指针。4、叶子节点按照本身关键字从小到大排序。 聚簇索引就是数据和索引是在一起的。 MylSAM使用的是非聚簇索引树的子节点上的data不是数据本身而是数据存放的地址。InnoDB采用的是聚簇索引树的 叶子节点上的data就是数据本身。 聚簇索引的数据物理存放顺序和索引顺序是一致的所以一个表当中只能有一个聚簇索引而非聚簇索引可以有多个。 InnoDB中如果表定义了PK那PK就是聚簇索引 如果没有PK就会找第一个非空的unique列作为聚簇索引。否则, InnoDB会创建一个隐藏的row-i作为聚簇索引。 MySQL的覆盖索引和回表 如果只需要在一颗索引树上就可以获取SQL所需要的所有列就不需要再回表查询这样查询速度就可以更快。 实现索引覆盖最简单的方式就是将要查询的字段全部建立到联合索引当中。 user (PK id, name ,sex) select count(name) from user-在name字段上建立一个索引。 select idname ,sex from user; -将name上的索引升级成为(name,sex)的联合索引。
MySQL有哪几种数据存储引擎有什么区别?
MySQL中通过show ENGINES指令可以看到所有支持的数据库存储引擎。 最为常用的就是MyISAM和InnoDB两种。 MyISAM和InnDB的区别: 1、存储文件。 MylSAM每个表有两个文件。 MYD和MYISAM文件。 MYD是数据文件。 MYI是索引文件。而InnDB每个表 只有一个文件idb。 2、LnnoDB支持事务支持行级锁支持外键。 3、InnoDB支持XA事务 4、InnoDB支持savePoints
MySQL 基础复习 三种存储引擎的特点
1.create database aa;
2.show create database aa\G;
3.drop database aa; 删除数据库mysql是不会有提示的而且不能回复
4.show engines; sopport 表示该数据引擎是否能使用yes表示可以no表示不能default表示该引擎是当前的默认引擎。
5.mysql数据库引擎介绍
5.1 InnoDB:
它是事务型数据库的首选引擎支持事务安全表(提供了提交、回滚、奔溃的恢复能力)支持行锁和外键。
在mysql5.5之后他就是mysql的默认数据库引擎。 InnoDB 锁定在行级 并且 也在select语句中提供了一个类似oracle的非锁定读。
InnoDB能够处理大数据量的数据。
InnoDB存储引擎完全和mysql服务器整合InnoDB为在主内存中缓存数据和索引而维护他自己的缓存池。
InnoDB中的表和缩影存储在一个逻辑空间中表中可以包含多个数据文件或者原始磁盘分区。 这一点和 MyISAM不同MyISAM单个表中存放在分离的文件中。
InnoDB表可设置任何尺寸。
InnoDB支持外键完整性约束。存储表中的数据时按照表中的住建顺序存放如果没有主键他会为每行数据创建一个6B的rowId以此作为行记录的主键。
InnoDB用在需要高性能的大型数据库的地方。
InnoDB不会创建目录MySQL 在她的数据目录下创建一个可以动态扩容的 名为ibdata1的 大小为10M的数据文件创建两个5M的数据库日志文件ib_logfile0和ib_logfile01.
5.2 MyISAM
MyISAM具有较高的查询、插入速度。不支持事务它是mysql5.5之前的版本默认的数据库。
支持大文件在支持大文件的系统上被支持。
当insert update delete 混合使用时动态尺寸的行产生更少的碎片。
单表最大索引数是64可以通过编译改变.每个索引最大的列是16列
最大的键长是100B 可以通过编译改变
blob和text 列可以被索引
null值可以出现在索引中
所有的键值以高字节优先被存储以允许一个更高的索引压缩
表中的自增长列的内部处理insert 和 update 操作自动更新这一列这使得自增长(auto_increment)列更快。在序列顶的值悲伤出后就不再使用了
可以把数据文件和索引文件放在不同的位置
每个字符列可以有不同的字符集
有varchar的表可以固定或者动态记录长度
varchar 和 char 列可以多达64K
使用MyISAM创建数据库产生三个文件文件名以表名开始.frm 文件存储表定义 、.myd 文件存储数据 、.myi存储索引
5.3 memory
它把表中的数据存放在内存中
memory表中可以有32个索引、每个索引可以有16列以及500B的最大键长度
支持存储hash和bree索引
可以在memory表中有非唯一键
不支持blob和text
支持自增长列和对包含null值列的索引
memory表在所有客户端之间共享
MySQL 数据表基本操作
1.在创建表的时候定义主键
create table aa{ id int(10) primary key auto_incremnt, name varchar(10) };
指定索引从哪里开始alter table aa AUTO_INCREMENT123456
2.定义完列之后申明主键
create table aa{ id int(10) , name varchar(10) primary key(id) };
追加
alter table aa add primary key(id); 一堆约束 外键约束 非空约束 唯一性约束 显示表字段以及类型 desc aa 显示建表语句 show create table aa\G
7.修改表名 alter table aa rename bb
修改字段类型 alter table aa modify name varchar(100)
9.修改列名 alter table aa change name newname varchar(10)
10 添加列 alter table aa add sex char(1) [first | after columnlname]
11 添加约束alter table aa add nickname varchar(30) not null
12 删除字段alter table aa drop nickname
13 修改字段的位置alter table aa modify name varchar(10) first | alter name
14 更改数据库引擎alter table aa engine MyISAM
15 删除表的外键alter table aa drop foreign key name
16 drop table if exist aa
17 删除有关联的表首先删除关联关系然后在删除对应的表alter table aa drop foreign key fk_name; drop table aa;
MySQL 数据类型
MySQL 支持多种数据类型主要有 数值类型、日期/时间、字符串等
数值类型整数类型tinyint(1)、smallint(2)、mediumint(3)、int(4)、bigint(8)浮点数据类型有float(4)、double(8) 指定小数类型decimal(M,D) M2个字节。无论是定点还是浮点如果用户指定的精度超出精度范围则会四舍五入进行处理。
时间日期类型year(YYYY)、time(HH:MM:SS)、date(YYYY-MM-DD)、datetime(YYYY-MM-DD HH:MM:SS)、timestamp(YYYY-MM-DD HH:MM:SS)
字符串类型char、varchar、binary、varbinary、blob、text、enum、set。字符串类型分为文本类型和二进制类型。
float(5,1)–float(5.12) 5.1 double(5,1)-double(5.15) 5.2 decimal(5,1)-decimal(5.123) 5.1
InnoDB的索引和MyISAM的索引有什么区别 首先InnoDB和MyISAM都是使用的B树实现的但是InnoDB使用的是聚簇索引而MyISAM使用的是非聚簇索引聚簇索引根据主键创建一颗B树叶子节点则存放的是数据行记录也可以把叶子结点称为数据页。通俗点来说就是把数据和索引存在同一个块找到了索引也就找到了数据。 ·因为叶子结点将索引和数据放在一起就决定了聚簇索引的唯一性一张表里面只能有一个聚簇索引。InnoDB引擎默认将主键设置为聚簇索引但如果没有设置主键那么InnoDB将会选择非空的唯一索引作为代替如果没有这样的索引InnoDB将会定一个隐式主键作为聚簇索引。 因为聚簇索引特殊的物理结构所决定叶子结点将索引和数据存放在一起在获取数据的速度上是比非 聚簇索引快的。 聚簇索引数据的存储是有序的在进行排序查找和范围查找的速度也是非常快的。 A也正因为有序性在数据插入时按照主键的顺序插入是最快的否则就会出现页分裂等问题严重 影响性能。对于InnoDB我们一般采用自增作为主键ID。 第二个问题主键最好不要进行更新修改主键的代价非常大为了保持有序性会导致更新的行移动一 般来说我们通常设置为主键不可更新。 在这部分只介绍InnoDB和MyISAM主键索引的不同辅助索引后面在说 而非聚簇索引是将索引和数据分开存储那么在访问数据的时候就需要2次查找但是和InnoDB的非聚簇部分还是有所区别。InnoDB是需要查找2次树先查找辅助索引树再查找聚簇索引树这个过程也叫回表。而MyISAM的主键索引叶子结点的存储的部分还是有所区别。InnoDB中存储的是索引和聚簇索引ID但是MyISAM中存储的是索引和数据行的地址只要定位就可以获取到 其实看到这个部分会有一个疑惑那就是InnoDB的聚簇索引比MyISAM的主键快那为什么会认为 MyISAM查询效率比InnoDB快呢 第一点对于两者存储引擎的的性能分析不能只看主键索引我们也要看看辅助索引前头我们介绍过InnoDB辅助索引会存在一个回表的过程。而MyISAM的辅助索引和主键索引的原理是一样的并没有什么区别。 (重点)InnoDB对MVCC的支持事物是比较影响性能的就算你没用但是也省不了检查和维护而 MyISAM 这块却没有这方面的影响具体MVCC详解将在后面章节描述。 如果一个表没有主键索引那还会创建B树吗 答案是会的! InnoDB是MySQL中的一种存储引擎它会为每个表创建一个主键索引。如果表没有明确的主键索引, InnoDB 会使用一个隐藏的自动生成的主键来创建索引。 这个隐藏的主键索引使用的就是 B 树结构。
索引的优缺点是什么
•需要占用物理空间建立的索引越多需要的空间越大 创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加 会降低表的增删改的效率因为每次增删改索引需要进行动态维护导致时间变长 使用索引一定能提升效率吗 什么时候适合创建索引什么时候不适 合创建索引) 答案是不一定任何事物我们都应该辩证的看知道其运行逻辑从而利用其优点尽量避开它的缺点。在上 面我们已经和大家介绍了过了索引带来的优缺点那接下来就和大家分享几个建索引的提示。 对于查询中使用的少的字段尽量不要创建索引创建索引是有成本的空间占用、创建和维护成本、增 删改效率降低。 对于数据密度小的列也不建议创建索引因为InnoDB中索引的B树所决定的你能带来的效率提升非常有限。但是也有例外举个例子枚举值123头两个占比百分之1%第三个占比99%并且头两个搜索占比比第三个高很多那么是可以建议加索引的)。InnoDB的辅助索引是存在回表的如果数据密度过小那么性能可能还不如全表扫。像上面这种场景具有特殊性也说明一个道理在大多数场景下建议可能适用但是也有不适用的时候我们不要把这种建议当作铁律。
MySQL 索引
声明写这个玩意仅仅是为了复习看看。仅此而已。
索引老生常谈。就是为了快用空间换时间的。
索引是在存储引擎中实现的每种存储引擎的所应都不一定相同并且每种存储引擎也不一定支持所有的索引类型。mysql中的缩影的存储类型有两种btree和hash。myisam和innodb只支持btree。memory和heap可以支持btree和innodb。每个表都支持至少16个索引每个表索引长度最少256字节。
索引的优点
1、通过创建唯一索引可以保证数据库中的每条记录的唯一性
2、可以加快查询速度
3、在实现数据的参考完整性方面可以加快表之间的连接
4、在使用分组和排序是可以显著减少查询中分组和排序的时间
索引的缺点
1、创建和维护缩影需要耗费时间并且随着数据量的增加所耗费的时间也会增加
2、索引需要占用存储空间物理空间如果有大量的缩影索引文件可能比数据文件更快达到最大尺寸
3、对表进行crud操作时索引需要动态的移动这样减低了数据的维护速度
索引的分类
1、普通索引和唯一索引
2、单列索引和组合索引
3、全文索引
全文索引可以在char、varchar、text类型上创建mysql上只有myisam引擎可以创建全文索引
4、空间索引
mysql上只有myisam引擎可以创建空间索引
索引的设计原则
索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对千获得良好的性能非常重要。设计索引时应该考虑以下准则
索引 并非越多越好 一个表中如有大量的索引 不仅占用磁盘空间 而且会影响 INSERT 、DELETE、UPDATE 等语句的性能因 为当表中的数据更改的同 时索引也 会进行调整和更新。避免对经常更新的表进行过多的索引 并且索引中的列尽可能少。而对经常用 千查询的字段应该创建索引但要避免添加不必要的字段。数据量小的表最好不要使用索引 由千数据较少 查询花费的时间可能比遍历索引的时间还要短索引可能不会产生优化效果。在条件表达式中经常用到的不同值较多的列上建立索引 在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值因此就无须建立 索引。如果建立索引不但不会提高查询效率反而会严重降低数据更新速度。当唯一性是某种数据本身的特征时指 定唯一索引。使用唯一索引需能确保定义的列的数据完整性以提高查询速度。在频繁进行排序或分组即进行group by 或 order by 操作的列上建立索引如 果待排序的列有多个可以在这些列上建立组合索引
mysql建立索引时中的length什么用
如果是CHARVARCHAR类型length可以小于字段实际长度如果是BLOB和TEXT类型必须指定 length. 参考MySQL手册 13.1.4. CREATE INDEX语法
对于CHAR和VARCHAR列只用一列的一部分就可创建索引。创建索引时使用col_name(length)语法对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引但是必须给出前缀长度。
此处展示的语句用于创建一个索引索引使用列名称的前10个字符。 CREATE INDEX part_of_name ON customer (name(10));
因为多数名称的前10个字符通常不同所以此索引不会比使用列的全名创建的索引速度慢很多。另外使用列的一部分创建索引可以使索引文件大大减小从而节省了大量的磁盘空间有可能提高INSERT操作的速度
创建索引
MySQL 支持多种方法在单个或多个列上创建索引
在创建表的定义语句CREATE TABLE中指定索引列使用 ALTER TABLE 语句在存在的表上创建索引使用 CREATE INDEX语句在已存在的表 上添加索引
-- 建表的时候指定索引
CREATE TABLE aa(
ID INT NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR (255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication)
)
--创建唯一索引
UNION INDEX(year_publication)
--创建全文索引
FULLTEXT INDEX FullTxtidx (info)
显示索引
SHOW INDEX FROM TABLE_NAME;
Table: book Non_unique: 1
Key_nam : year_publication
Seq_in_index: 1
Column_name: year_publication
Collation: A
Cardinality : 0
Sub_part: NULL
Packed: NULL Null:
Index_type: BTREE
Comment:
Index comment:其中各个主要参数的含义为
Table 表示创建索引的表。Non_ unique 表示索引非唯一 l 代表是非唯一索引 0 代表唯一索引。Key_ name 表示索引的名称。Seq_in_index 表示该字段在索引中的位置,单列索引该值为 I, 组合索引为每个字段在索引定义中的顺序。Column_name 表示定义索引的列字段。Sub_part 表示索引的长度。Null 表示该字段是否能为空值。Index_type 表示索引类型
在已经存在的表上创建索引
ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME(CLOUMN(INDEX_LENGTH))create index
CREATE INDEX INDEX_NAME(CLOUMN(INDEX_LENGTH))删除索引
有两种方式
1. ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;2. DROP INDEX INDEX_NAME ON TABLE_NAME;MySQL 函数
数字函数、字符串函数、时间日期函数、条件函数、系统函数、加密函数、其他特殊函数
数字函数
abs() 绝对值
2.pi() 圆周率 mod(x,y) 余数、sprt() 获取整数函数ceil() ceiling() floor()
ceil(-3.35) -3 ceilIng(3.35) 4 floor(-3.35) -4 floor(3.35) 3 随机数函数 rand()[0,1] rand(X) 四舍五入round() round(x,y) y表示小数点后保留几位 truncate() 符号函数sign(x)
sign(-21) -1 sign(0) 0 sign21 1 8. 幂运算pow() power() exp()
对数log() log10()
字符串函数 1.char_length() 字符个数
2.length() 字节长度
concat() 如果是有null则为nullconcat_ws() 会忽略null
concat_ws(‘-’,1,1) 1-1 concat_ws(‘-’,1,null) 1-
4.字符串替换: insert(s1,x,len,sr) 索引是从1开始
lower() upper()
6.获取指定长度的字符串left(s,len)、right(s,len) 填充 lpad(s1,len.s2) rpad() 删除空格 trim ltrin rtrim 删除指定字符串 trim(s2 from string) 重复生成字符串repeat(s,n)
11, 空格函数space(n) ;替换replace(s,s1,s2)
12.字符串大小比较strcmp(s1,s2) 返回 -101
13.匹配字符串位置locate(str,s) 返回索引位置索引从0开始positoin(str1 in str);instr(str,str1) reverse() 返回指定位置的字符串函数elt()
elt(1,1,b,c) a elt(3,a,b) null
16 返回指定字符串位置的函数field()
field(s,s1,s2) field(‘hi’,a,b,‘hi’) 3 field(‘hi’,‘hihi’,‘hh’) 0
17 返回字符串位置的函数find_in_set(s1,s2) s1 在s2中出现的索引位置
18 获取字符串函数make_set
时间日期 1.curdate() cur_date() curdate()0
2.curtime() cur_time() curtime()0
3.current_timestamp() localdate() now() sysdate()
nuix 时间戳函数 unix_timestamp()
5.uct_date() 世界标准时间
6.month() monthname()
7.dayname() dayofweek() 注意1表示周日 7表示周6 weekday() 表示工作日索引。0表示周一
8.week(date) 查询日期是一年中的第几周 ,week(datemodle)modle 表示一周的第一天是周几默认是00标识 一周第一天是周日 ;weekofyear(date) 查询日期是一年中的第几周
9.dayofyear(date) 、dayofmonth(date) 时间在一年、一月中的第几天
year() 、quartrt()、minute()、second()
11.extract(type from date) :type可以为 year 、year_month、year_minute
12 时间和秒转换time_to_sec() sec_to_time()
13 计算时间和日期的函数
date_add()、adddate() 时间加法操作date_add(date,interval 数量 单位) 单位可以是 年月日时分秒
date_sub()、subdate()时间减法操作date_add(date,interval 数量 单位) 单位可以是 年月日时分秒
addtime(date,express) :addtime(‘2020-01-01 11:00:00’ ,‘1:00:00’) 加了一个小时 subtime() 同理 addtime(‘2020-01-01 12:00’,‘24:00’)
date_diff(d1d2) : 返回天数
时间日期格式化 date_format() DATE_FORMAT(NOW(),‘%Y-%m-%d %H:%i:%s’)
条件判断函数
ifnull(a,b,c) 三目表达式
2.ifnull(a,b)
case when
系统信息函数
select version
select connection_id()
show processlist
select database() ,schema()
select user(),current_user(),system_user(),session_user()
select charset(‘123’) 获取字符集
select last_insert_id()
加密函数、
password()
md5()
encode(str,pwd_str)
decode(str,pwd_str)
其他函数
cast() convert()
索引
是一个单独的、存储在磁盘上的数据库结构它 们包含着对数据表里所有记录的引用指针
MySQL 中索引的存储类型有两种 BTREE 和 HASH, 具体和表的存储引擎相关 MyISAM 和InnoDB 存储引擎只支持BTREE 索引 MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。
索引的优点主要有以下几条
通过创建唯一索引 可以保证数据库表中每一行数据的唯一性。 可以大大加快数据的查询速度 这也是创建索引的最主要的原因。 在实现数据的参考完整性方面 可以加速表和表之间的连接。 在使用分组和排序子句进行数据查询时 也可以显著减少查询中分组和排序的时间。
增加索引也有许多不利主要表现在如下几个方面 创建索引和维护索引要耗费时间 并且随着数据量的增加所耗费的时间也会增加。 索引需要占磁盘空间 除了数据表占数据空间之外 每一个索引还要占一定的物理空间如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸。 当对表中的数据进行增加、删除和修改的时候 索引也要动态地维护 这样就降低了数据的维护速度。
索引的优缺点是什么
数据是存储在磁盘上的操作系统读取磁盘的最小单位是块如果没有索引会加载所有的数据到内存依次进 行检索加载的总数据会很多磁盘10多。 如果有了索引会以某个列为key创建索引MySQL采用B树结构存储一方面加载的数据只有某个列和主键 ID另一方便采用了多叉平衡树定位到指定某个列的值会很快根据关联的ID可以快速定位到对应行的数据 所以检索的速度会很快因为加载的总数据很少磁盘10少。 可见索引可以大大减少检索数据的范围、减少磁盘1O使查询速度很快因为磁盘10是很慢的是由它的硬件 结构决定的。 优点 索引能够提高数据检索的效率降低数据库的IO成本。 通过创建唯一性索引可以保证数据库表中每一行数据的唯一性创建唯一索引在使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间加速两个表之间的连接一般是在外键上创建索引 ×缺点 需要占用物理空间建立的索引越多需要的空间越大 创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加 会降低表的增删改的效率因为每次增删改索引需要进行动态维护导致时间变长
索引的分类
索引的分类 按「数据结构」分类Btree索引、Hash索引、Full-text索引。 按「物理存储」分类聚簇索引主键索引、二级索引辅助索引)按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。按「字段个数」分类单列索引、联合索引。
1.普通索引和唯—索引
2.单列索引和组合索引
3.全文索引
4.空间索引删除线格式
索引的设计原则 索引并非越多越好 避免对经常更新的表进行过多的索引 数据量小的表最好不要使用索引 在条件表达式中经常用到的不同值较多的列上建立索引 在不同值很少的列上不要建立索引 当唯一性是某种数据本身的特征时指定唯一索引。使用唯一索引需能确保定义的列的数据完整性以提高查询速度 在频繁进行排序或分组即进行group by 或 order by 操作的列上建立索引
create index index_1 on by_his_user(USER_NAME);
EXPLA 语句输出结果的各个行解释如下
(1)select_type 行指定所使用的SELECT查询类型这里值为SIMPLE, 表示简单的SELECT, 不使用UNION 或子查询。其他可能的取值有 PRIMARY、UNION、SUBQUERY 等。 (2)table 行指定数据库读取的数据表的名字 它们按被读取的先后顺序排列。 (3)type 行指定了本数据表与其他数据表之间的 关联关系 可能的取值有 system、const、 eq_ref、 ref、 range、 index 和 All。 (4)possible_keys 行给出了 MySQL 在搜索数据记录时可选用的各个索引。 (5)key 行是 MySQL 实际选用的索引。 (6)key—len 行给出索引 按字节计算的长度 ke y_len 数值越小 表示越快。 (7)ref 行给出了关联关系中另 一个数据表里的数据列的名字。 (8)rows 行是 MySQL 在执行这个查询时预计会从这个数据表里读出的数据行的个数。 extra 行提供了与关联操作有关的信息
性能下降SQL慢,执行时间长,等待时间长: 查询语句写的烂 索引失效??? 关联查询太多join 服务器调优及各个参数设置
join
left join right join inner join left join on where b.XXX is null right join on where a.XXX is null full outer join full outer join where a.XXX is null or b.XXX is null
数据库迁移
数据库迁移就是把数据从一个系统移动到另一个系统上
相同版本的 MySQL 数据库之间的迁移
主版本号 相同的 MySQL 数据库之间进行数据库移动 eg将www.abc.com 主机上的 MySQL 数据库全部迁移到 www.bcd.com 主机上。在 www.abc.com 主机上执行的命令如下 mysqldump -h www.abc.com -uroot -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword MySQLdump 导入的数据直接通过管道符 I 传给 MySQL 命令导入的主机 www.bcd.com 数据库中 dboame 为需要迁移的数据库名称 如果要迁移全部的数据库 可使用参数–all-databases
不同版本的 MySQL 数据库之间的迁移
将旧版本数据库中的数据备份出来然后导入到新版本的数据库中
不同数据库之间的迁移
可以使用naviat Premium工具进行不同数据库之间数据的迁移 数据恢复
可以使用命令进行恢复
直接复制到数据库目录
如果数据库通过复制数据库文件备份可以直接复制备份的文件到 MySQL 数据目录下实现恢复。 通过这种方式恢复时必须保存备份数据的数据库和待恢复的数据库服务器的主版本号相同。而且这种方式只对 MyISAM 引擎的表有效对于 InnoDB 引擎的表不可用。 执行恢复以前关闭MySQL服务将备份的文件或目录覆盖 MySQL 的 data 目录启动MySQL 服务。对于 Linux/Unix 操作系统来说 复制完文件需要将文件的用户和组更改为 MySQL 运行的用户 和组通 常用户是 MySQL, 组也是 MySQ
mysql执行计划怎么看
执行计划就是sql的执行查询的顺序以及如何使用索引查询返回的结果集的行数 EXPLAIN SELECT * from A where X? and Y? id I select_type I table I partitions | type filtered I Extra 1。id是一个有顺序的编号是查询的顺序号有几个select 就显示几行。id的顺序是按 select 出现的顺序增 长的。id列的值越大执行优先级越高越先执行id列的值相同则从上往下执行id列的值为NULL最后执行。 2。selectType 表示查询中每个select子句的类型 SIMPLE表示此查询不包含 UNION查询或子查询 •PRIMARY表示此查询是最外层的查询包含子查询) SUBQUERY子查询中的第一个SELECT UNION表示此查询是UNION的第二或随后的查询 • DEPENDENT UNIONUNION中的第二个或后面的查询语句取决于外面的查询 • UNION RESULT,UNION的结果 DEPENDENT SUBQUERY子查询中的第一个SELECT取决于外面的查询.即子查询依赖于外层查询的结果. DERIVED衍生表示导出表的SELECTFROM子句的子查询) 3.table表示该语句查询的表 4.type优化sql的重要字段也是我们判断sql性能和优化程度重要指标。
mysql执行计划怎么看
执行计划就是sql的执行查询的顺序以及如何使用索引查询返回的结果集的行数 EXPLAIN SELECT * from A where X? and Y? id I select_type I table I partitions I type I possible_keys | key filtered | Extra 1。id是一个有顺序的编号是查询的顺序号有几个select 就显示几行。id的顺序是按 select 出现的顺序增 长的。id列的值越大执行优先级越高越先执行id列的值相同则从上往下执行id列的值为NULL最后执行。 2。selectType 表示查询中每个select子句的类型 SIMPLE表示此查询不包含UNION查询或子查询 •PRIMARY表示此查询是最外层的查询包含子查询) • SUBQUERY子查询中的第一个SELECT UNION表示此查询是 UNION的第二或随后的查询 • DEPENDENT UNIONUNION中的第二个或后面的查询语句取决于外面的查询 • UNION RESULT,UNION的结果 DEPENDENT SUBQUERY子查询中的第一个SELECT取决于外面的查询.即子查询依赖于外层查询的结果. DERIVED衍生表示导出表的SELECTFROM子句的子查询) 3.table表示该语句查询的表 4.type优化sql的重要字段也是我们判断sql性能和优化程度重要指标。他的取值类型范围 const: |通过索引一次命中匹配一行数据 Lsystem:) 表中只有一行记录相当于系统表; eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配 ref:非唯一性索引扫描返回匹配某个值的所有 •range:只检索给定范围的行使用一个索引来选择行一般用于between、 index:只遍历索引树; ALL表示全表扫描这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多执行效率越 慢。 执行效率 ALL index range refeq_refconstsystem。最好是避免ALL和index 5.possible_keys它表示Mysql在执行该sql语句的时候可能用到的索引信息仅仅是可能实际不一定会用 到。 6.key此字段是 mysql在当前查询时所真正使用到的索引。他是possible_keys的子集
MySQL 执行计划 explain
Explain使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句的,能查看哪些索引被使用表的执行顺序。
explain select* from book where year_publicationl990 \Gid: 1
select_type: SIMPLE
table : book
type: ref
possible_keys: year_publication
key: year_publication
key_len: 1
ref: const
rows: l
Extra:explain 语句输出结果的各个行解释如下
select_type 行指定所使用的SELECT查询类型这里值为SIMPLE, 表示简单的SELECT, 不使用UNION 或子查询。其他可能的取值有 PRIMARY、UNION、SUBQUERY 等。table 行指定数据库读取的数据表的名字 它们按被读取的先后顺序排列。type 行指定了本数据表与其他数据表之间的 关联关系 可能的取值有 system、const、eq_ref、 ref、 range、 index 和 All。possible_keys 行给出了 MySQL 在搜索数据记录时可选用的各个索引。key 行是 MySQL 实际选用的索引。key—len 行给出索引 按字节计算的长度 key_len 数值越小,表示越快。ref 行给出了关联关系中另 一个数据表里的数据列的名字。rows 行是 MySQL 在执行这个查询时预计会从这个数据表里读出的数据行的个数。extra 行提供了与关联操作有关的信息
id
三种情况 id相同执行顺序由上至下 id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行 id相同不同同时存在 select_type
表示查询中每个select子句的类型
(1) SIMPLE(简单SELECT不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询查询中若包含任何复杂的子部分最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句取决于外面的查询)
(5) UNION RESULT(UNION的结果union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存必须重新评估外链接的第一行)
table
显示表名称或者别名
type
对表访问方式表示MySQL在表中找到所需行的方式又称“访问类型”。
常用的类型有 ALL、index、range、 ref、eq_ref、const、system、NULL从左到右性能从差到好
ALLFull Table Scan MySQL将遍历全表以找到匹配的行
index: Full Index Scanindex与ALL区别为index类型只遍历索引树
range:只检索给定范围的行使用一个索引来选择行
ref: 表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref区别就在使用的索引是唯一索引对于每个索引键值表中只有一条记录匹配简单来说就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化并转换为一个常量时使用这些类型访问。如将主键置于where列表中MySQL就能将该查询转换为一个常量system是const类型的特例当查询的表只有一行的情况下使用system
NULL: MySQL在优化过程中分解语句执行时甚至不用访问表或索引例如从一个索引列里选取最小值可以通过单独索引查找完成
possible_keys
指出MySQL能使用哪个索引在表中找到记录查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询使用该查询可以利用的索引如果没有任何索引显示 null
Key
key列显示MySQL实际决定使用的键索引必然包含在possible_keys中
如果没有选择索引键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
key_len
表示索引中使用的字节数可通过该列计算查询中使用的索引的长度key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的
ref
列与索引的比较表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值
rows
估算出结果集行数
Extra
该列包含MySQL解决查询的详细信息,有以下几种情况
Using where:不用读取表中所有信息仅通过索引就可以获取所需数据这发生在对表的全部的请求列都是同一个索引的部分的时候表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary表示MySQL需要使用临时表来存储结果集常见于排序和分组查询常见 group by ; order by
Using filesort当Query中包含 order by 操作而且无法利用索引完成的排序操作称为“文件排序”
– 测试Extra的filesort explain select * from emp order by name; Using join buffer改值强调了在获取连接条件时没有使用索引并且需要连接缓冲区来存储中间结果。如果出现了这个值那应该注意根据查询的具体情况可能需要添加索引来改进能。
Impossible where这个值强调了where语句会导致没有符合条件的行通过收集统计信息不可能存在结果。
Select tables optimized away这个值意味着仅通过使用索引优化器可能仅从聚合函数结果中返回一行
No tables usedQuery语句中使用from dual 或不含任何from子句
mysql 备份恢复命令
数据备份 1.备份某个数据库中的一张表
mysqldump -uroot -p 数据库名称 表名 aa.sqleg:mysqldump -uroot -p course student aa.sql2.备份某个数据库
命令mysqldump -uroot -p 数据库名称 aa.sqleg:mysqldump -uroot -p course aa.sql3.备份整个数据库
命令mysqldump -uroot -p aa.sqleg:mysqldump -uroot -p --all-databases aa.sql4.仅导出表结构不导出数据 --no-data
命令mysqldump -uroot -p --no-data [数据库名称] aa.sqleg:mysqldump -uroot -p --no-data [springbootv2][--all-databases] aa.sql5.仅导出表结构不导出数据 --no-create-info
命令mysqldump -uroot -p --no-create-info [数据库名称] aa.sqleg:mysqldump -uroot -p --no-create-info [springbootv2][--all-databases] aa.sql6.导出course库中的存储过程和触发器 --routines --triggers [–测试未通过]
命令mysqldump -uroot -p --routines --triggers [数据库名称] aa.sqleg:mysqldump -uroot -p --routines --triggers [springbootv2][--all-databases] aa.sql数据恢复
有两种恢复方式 登录/不登录
1.不登录
命令mysql -uroot -p 备份文件.sqleg:mysql -uroot -p test.sql2.登录
命令mysql source 备份文件.sqleg: mysql source test.sql数据库 中的事务
事务是由一组SQL语句组成的逻辑处理单元事务具有以下4个属性通常简称为事务的ACID属性。
l 原子性Atomicity事务是一个原子操作单元其对数据的修改要么全都执行要么全都不执行。
l 一致性Consistent在事务开始和完成时数据都必须保持一致状态。 这意味着所有相关的数据规则都必须应用于事务的修改以保持数据的完整性 事务结束时所有的内部数据结构如B树索引或双向链表也都必须是正确的。
l 隔离性Isolation数据库系统提供一定的隔离机制保证事务在不受外部并发操作影响的“独立”环境执行。 这意味着事务处理过程中的中间状态对外部是不可见的反之亦然。
l 持久性Durable事务完成之后它对于数据的修改是永久性的即使出现系统故障也能够保持。
并发事务处理带来的问题:
更新丢失(Lost Update) 当两个或多个事务选择同一行然后基于最初选定的值更新该行时由于每个事务都不知道其他事务的存在 就会发生丢失更新问题最后的更新覆盖了由其他事务所做的更新。 例如两个程序员修改同一java文件。每程序员独立地更改其副本然后保存更改后的副本这样就覆盖了原始文档。 最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。如果在一个程序员完成并提交事务之前 另一个程序员不能访问同一文件则可避免此问题。 脏读(Dirty Reads) 一个事务正在对一条记录做修改在这个事务完成并提交前这条记录的数据就处于不一致状态这时 另一个事务也来读取同一条记录如果不加控制第二个事务读取了这些“脏”数据并据此做进一步的处理 就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。 一句话事务A读取到了事务B已修改但尚未提交的的数据还在这个数据基础上做了操作。此时如果B事务回滚A读取 的数据无效不符合一致性要求。 不可重复读(Non-Repeatable Reads) 一个事务在读取某些数据后的某个时间再次读取以前读过的数据却发现其读出的数据已经发生了改变、或某些记录已经被删除了 这种现象就叫做“不可重复读”。 一句话事务A读取到了事务B已经提交的修改数据不符合隔离性 幻读(Phantom Reads) 一个事务按相同的查询条件重新读取以前检索过的数据却发现其他事务插入了满足其查询条件的新数据这种现象就称为“幻读”。 一句话事务A读取到了事务B体提交的新增数据不符合隔离性。 多说一句幻读和脏读有点类似 脏读是事务B里面修改了数据 幻读是事务B里面新增了数据。
事务隔离级别
脏读”、“不可重复读”和“幻读”其实都是数据库读一致性问题必须由数据库提供一定的事务隔离机制来解决。
数据库的事务隔离越严格并发副作用越小但付出的代价也就越大因为事务隔离实质上就是使事务在 一定程度上 “串行化”进行这显然与“并发”是矛盾的。同时不同的应用对读一致性和事务隔离程度的 要求也是不同的比如许多应用对“不可重复读”和“幻读”并不敏感可能更关心数据并发访问的能力。
mysql主从同步原理
mysql主从同步的过程 Mysql的主从复制中主要有三个线程master(binloa dump thread)、slave thread)Master—条线程和Slave中的两条线程。 ·主节点binlog主从复制的基础是主库记录数据库的所有变更记录到binlog。binlog是数据库服务器启动的 那一刻起保存所有修改数据库结构或内容的一个文件。 主节点log dump线程当binlog有变动时log dump 线程读取其内容并发送给从节点。 从节点1/O线程接收binlog内容并将其写入到relaylog文件中。 ·从节点的SQL线程读取relay log文件内容对数据更新进行重放最终保证主从数据库的一致性。 注主从节点使用binglog文件position偏移量来定位主从同步的位置从节点会保存其已接收到的偏移量 如果从节点发生宕机重启则会自动从 position的位置发起同步。 由于mysql默认的复制方式是异步的主库把日志发送给从库后不关心从库是否已经处理这样会产生一个问题就 是假设主库挂了从库处理失败了这时候从库升为主库后日志就丢失了。由此产生两个概念。 全同步复制 主库写入binlog后强制同步日志到从库所有的从库都执行完成后才返回给客户端但是很显然这个方式的话性能 会受到严重影响。 半同步复制 和全同步不同的是半同步复制的逻辑是这样从库写入日志成功后返回ACK确认给主库主库收到至少一个从库 的确认就认为写操作完成。
Mysql数据库中什么情况下设置了索引但无法使用?
1.没有符合最左前缀原则 2.字段进行了隐私数据类型转化 3.走索引没有全表扫描效率高
存储拆分后如何解决唯一主键问题
UUID简单、性能好没有顺序没有业务含义存在泄漏mac地址的风险 数据库主键实现简单单调递增具有一定的业务可读性强依赖db、存在性能瓶颈存在暴露业务信息的风 险 redis,mongodbzk等中间件增加了系统的复杂度和稳定性 雪花算法
海量数据下如何快速查找一条记录?
1、使用布隆过滤器快速过滤不存在的记录。 使用Redis的bitmap结构来实现布隆过滤器。 2、在Redis中建立数据缓存。-将我们对Redis使用场景的理解尽量表达出来。 以普通字符串的形式来存储(serld-user.json)。以一个hash来存储一条记录(userld key-username field-userAge-)。以一个整的hash来存储所有的数据Userlnfo-field就用userldvalue就用user.json。一个hash最多能支持2^32-1(40多个亿)个键值对。 缓存击穿对不存在的数据也建立key。这些key都是经过布隆过滤器过滤的所以一般不会太多. 缓存过期将热点数据设置成永不过期定期重建缓存。 使用分布式锁重建缓存。 3、查询优化。 按槽位分配数据 自己实现槽位计算找到记录应该分配在哪台机器上然后直接去目标机器上找。
简述MyISAM和InnoDB的区别
MyISAM: 不支持事务 支持表级锁 存储表的总行数 一个MYISAM表有三个文件索引文件、表结构文件、数据文件; 采用非聚集索引索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致但是辅索引不用保证唯 一性。 InnoDb: 支持ACID的事务支持事务的四种隔离级别 支持行级锁及外键约束因此可以支持写并发; 不存储总行数; 一个InnoDb引擎存储在一个文件空间共享表空间表大小不受操作系统控制一个表可能分布在多个文件里也有可能为多个设置为独立表空表大小受操作系统文件大小限制一般为2G受操作系统文件大小的限制; 主键索引采用聚集索引索引的数据域存储数据文件本身辅索引的数据域存储主键的值因此从辅索引查找数据需要先通过辅索引找到主键值再访问辅索引最好使用自增主键防止插入数据时为维持B树结构文件的大调整。
简述mysql中索引类型及对数据库的性能的影响
普通索引允许被索引的数据列包含重复的值。 唯一索引可以保证数据记录的唯一性。 主键是一种特殊的唯一索引在一张表中只能定义一个主键索引主键用于唯一标识一条记录使用关键字 PRIMARYKEY来创建。 联合索引索引可以覆盖多个数据列如像INDEx(olumnA,columnB)引。 全文索引通过建立倒排索引可以极大的提升检索效率解决判断字段是否包含的问题是目前搜索引擎使用的一 种关键技术。可以通过ALTER TABLE table_name ADDFULLTEXT(column)创建全文索引 索引可以极大的提高数据的查询速度。 通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。 但是会降低插入、删除、更新表的速度因为在执行这些写操作时还要操作索引文件 索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那 么需要的空间就会更大如果非聚集索引很多一旦聚集索引改变那么所有非聚集索引都会跟着变。
强平衡二叉树和弱平衡二叉树有什么区别
强平衡二叉树AVL树弱平衡二叉树就是我们说的红黑树。 1.AVL树比红黑树对于平衡的程度更加严格在相同节点的情况下AVL树的高度低于红黑树 2.红黑树中增加了一个节点颜色的概念 3.AVL树的旋转操作比红黑树的旋转操作更耗时 B树和B树的区别为什么Mysql使用B树 B树的特点 1.节点排序 2.一个节点了可以存多个元素多个元素也排序了 B树的特点1.拥有B树的特点2.叶子节点之间有指针 3.非叶子节点上的元素在叶子节点上都冗余了也就是叶子节点中存储了所有的元素并且排好顺序 Mysql索引使用的是B树因为索引是用来加快查询的而B树通过对数据进行排序所以是可以提高查询速度的然后通过一个节点中可以存储多个元素从而可以使得B树的高度不会太高在Mysql中一个Innodb页就是一个B树节点一个Innodb页默认16kb所以一般情况下一颗两层的B树可以存2000万行左右的数据然后通过利用B树叶子节点存储了所有数据并且进行了排序并且叶子节点之间有指针可以很好的支持全表扫描范围查找等SQL语句。
事务的基本特性和隔离级别有哪些?
事务表示多个数据操作组成一个完整的事务单元这个事务内的所有数据操作要么同时成功要么同时失败。 事务的特性ACID 1、原子性事务是不可分割的要么完全成功要么完全失败。 2、一致性事务无论是完成还是失败都必须保持事务内操作的一致性。当失败时都要对前面的操作进行回滚不管中途 是否成功。 3、隔离性当多个事务操作一个数据的时候为防止数据损坏需要将每个事务进行隔离互相不干扰。 4、持久性事务开始就不会终止。他的结果不受其他外在因素的影响。 事务的隔离级别SHOW VARIABLES like’transaction%’ 设置隔离级别set transaction level xxx 设置下次事务的隔离级别。 set session transaction level xxx 设置当前会话的事务隔离级别 set global transaction level xxx 设置全局事务隔离级别 MySQL当中有五种隔离级别 NONE不使用事务。 READ UNCOMMITED: READ COMMITED防止脏读最常用的隔离级别 REPEATABLE READ防止脏读和不可重复读。MYSQL默认 SERIALIZABLE事务串行可以防止脏读、幻读不可重复度
如何实现分库分表
将原本存储于单个数据库上的数据拆分到多个数据库把原来存储在单张数据表的数据拆分到多张数据表中实现 数据切分从而提升数据库操作性能。分库分表的实现可以分为两种方式垂直切分和水平切分。 水平将数据分散到多张表涉及分区键, 。分库每个库结构一样数据不一样没有交集。库多了可以缓解io和cpu压力 ”分表每个表结构一样数据不一样没有交集。表数量减少可以提高sq|执行效率、减轻cpu压力 垂直将字段拆分为多张表需要一定的重构 ·分库每个库结构、数据都不一样所有库的并集为全量数据 、分表每个表结构、数据不一样至少有一列交集用于关联数据所有表的并集为全量数据
什么是MVCC
多版本并发控制读取数据时通过一种类似快照的方式将数据保存下来这样读锁就和写锁不冲突了不同的事务 session会看到自己特定版本的数据版本链 MVCC只在 READ COMMITTED和 REPEATABLE READ两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容因为READ UNCOMMITTED总是读取最新的数据行而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。 聚簇索引记录中有两个必要的隐藏列 trx_id用来存储每次对某条聚簇索引记录进行修改的时候的事务id。 roll_pointer每次对哪条聚簇索引记录有修改的时候都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针它指向这条聚簇索引记录的上一个版本的位置通过它来获得上一个版本的记录信息。注意插入操作的undo日志没有这个属性因为它没有老版本) 已提交读和可重复读的区别就在于它们生成ReadView的策略不同。 开始事务时创建readviewreadView维护当前活动的事务id即未提交的事务id排序生成一个数组 访问数据获取数据中的事务id获取的是事务id最大的记录对比readview: 如果在readview的左边比readview都小可以访问在左边意味着该事务已经提交 如果在readview的右边比readview都大或者就在readview中不可以访问获取roll_pointer取上一版本 重新对比在右边意味着该事务在readview生成之后出现在readview中意味着该事务还未提交 已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的 时候生成一个ReadView之后的读都复用之前的ReadView。 这就是MysqI的MVCC,通过版本链实现多版本可并发读-写写-读。通过ReadView生成策略的不同实现不同的 隔离级别。
什么是脏读、幻读、不可重复读要怎么处理?
这些问题都是MySQL进行事务并发控制时经常遇到的问题。 脏读在事务进行过程中读到了其他事务未提交的数据。 不可重复读在一个事务过程中多次查询的结果不一致。 幻读在一个事务过程中用同样的操作查询数据得到的记录数不相同。 处理的方式有很多种加锁、康务隔离、MVCC 加锁: 1、脏读在修改时加排他锁直到事务提交才释放。读取时加共享锁读完释放锁。 2、不可重复读读数据时加共享锁写数据时加排他锁。 3、幻读加范围锁。
事务的基本特性和隔离级别
事务基本特性ACID分别是: 原子性指的是一个事务中的操作要么全部成功要么全部失败。 一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱假设A只有90块支付之前我们数据库里的数据都是符合约束的但是如果事务执行成功了我们的数据库数据就破坏约束了因此事务不能成功这里我们说事务提供了一致性的保证 隔离性指的是一个事务的修改在最终提交前对其他事务是不可见的。 持久性指的是一旦事务提交所做的修改就会永久保存到数据库中。 隔离性有4个隔离级别分别是: read uncommit 读未提交可能会读到其他事务未提交的数据也叫做脏读。 用户本来应该读取到id1的用户age应该是10结果读取到了其他事务还没有提交的事务结果读取结果 age20这就是脏读。 read commit 读已提交两次读取结果不一致叫做不可重复读。 不可重复读解决了脏读的问题他只会读取已经提交的事务。 用户开启事务读取id1用户查询到age10再次读取发现结果20在同一个事务里同一个查询读取到不同的结果叫做不可重复读。 repeatable read 可重复复读这是mysql的默认级别就是每次读取结果都一样但是有可能产生幻读。 serializable串行一般是不会使用的他会给每一行读取的数据加锁会导致大量超时和锁竞争的问题
索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引一般来说执行查询时遍历整张表。 索引的原理就是把无序的数据变成有序的查询 1.把创建了索引的列的内容进行排序 2.对排序结果生成倒排表 3.在倒排表内容上拼上数据地址链 4.在查询的时候先拿到倒排表内容再取出数据地址链从而拿到具体数据
索引设计的原则
查询更快、占用空间更小 1.适合索引的列是出现在where子句中的列或者连接子句中指定的列 2.基数较小的表索引效果较差没有必要在此列建立索引 3.使用短索引如果对长字符串列进行索引应该指定一个前缀长度这样能够节省大量索引空间如果搜索词 超过索引前缀长度则使用索引排除不匹配的行然后检查其余行是否可能匹配。 4.不要过度索引。索引需要额外的磁盘空间并降低写操作的性能。在修改表内容的时候索引会进行更新甚至 重构索引列越多这个时间就会越长。所以只保持需要的索引有利于查询即可。 5.定义有外键的数据列一定要建立索引。 6.更新频繁字段不适合创建索引 7.若是不能有效区分数据的列不适合做索引列(如性别男女未知最多也就三种区分度实在太低) 8.尽量的扩展索引不要新建索引。比如表中已经有a的索引现在要加(a,b)的索引那么只需要修改原来的索 引即可。 9.对于那些查询中很少涉及的列重复值比较多的列不要建立索引。 10.对于定义为text、image和bit的数据类型的列不要建立索引。
索引覆盖是什么
索引覆盖就是一个SQL在执行时可以利用索引来快速查找并且此SQL所要查询的字段在当前索引对应的字段中都包含了那么 就表示此SQL走完索引后不用回表了所需要的字段都在当前索引的叶子节点上存在可以直接作为结果返回了
最左前缀原则是什么
当一个SQL想要利用索引是就一定要提供该索引所对应的字段中最左边的字段也就是排在最前面的字段比如针对abc三个字段建立了一个联合索引那么在写一个sql时就一定要提供a字段的条件这样才能用到联合索引这是由于在建立abc三个字段的联合索引时底层的B树是按照abc三个字段从左往右去比较大小进行排序的所以如果想要利用B树进行快速查找也得符合这个规则
谈谈如何对MySQL进行分库分表多大数据量需要进行分库分表分库分表的方式和分片策略由哪些分库分表后SQL语句的执行流程是怎样的?
什么是分库分表 就是当表中的数据量过大时整个查询效率就会降低得非常明显。这时为了提升查询效率就要将一个表 中的数据分散到多个数据库的多个表当中。 分库分表最常用的组件: Mycatl ShardingSphere 数据分片的方式有垂直分片和水平分片。垂直分片就是从业务角度将不同的表拆分到不同的库中能够解决数据库数据文件过大的问题但是不能从根本上解决查询问题。水平分片就是从数据角度将一个表中的数据拆分到不同的库或表中这样可以从根本上解决数据量过大造成的查询效率低的问题。 有非常多的分片策略比如 取模、按时间、按枚举值。。。。 阿里提供的开发手册当中建议一个表的数据量超过500W或者数据文件超过2G就要考虑分库分表了。 分库分表后的执行流程 一个user表按照userid进行了分片然后我需要按照sex字段去查这要怎么查强制指定只查一个数据库要怎么做查 询结果按照userid来排序要怎么排 分库分表的问题:跨库查询跨库排序分布式事务公共表主键重复…… 全库表路由对于不带分片键的DQL、DML以及DDL语句会遍历所有的库表逐一执行。例如selectfrom course 或者selectfrom course where ustatus‘1不带分片键) 全库路由对数据库的操作都会遍历所有真实库。例如set autocommit0 全实例路由对于DCL语句每个数据库实例只执行一次例如 CREATE USER customer127.0.0.1 identified BY’123’;
关心过业务系统里面的sql耗时吗统计过慢查询吗对慢查询都
怎么优化过 在业务系统中除了使用主键进行的查询其他的都会在测试库上测试其耗时慢查询的统计主要由运维在做会 定期将业务中的慢查询反馈给我们。 慢查询的优化首先要搞明白慢的原因是什么是查询条件没有命中索引是load了不需要的数据列还是数据量 太大? 所以优化也是针对这三个方向来的 首先分析语句看看是否load了额外的数据可能是查询了多余的行并且抛弃掉了可能是加载了许多结果中 并不需要的列对语句进行分析以及重写。 分析语句的执行计划然后获得其使用索引的情况之后修改语句或者修改索引使得语句可以尽可能的命中 索引。 如果对语句的优化已经无法进行可以考虑表中的数据量是否太大如果是的话可以进行横向或者纵向的分 表。
SQL优化有哪些着手点组合索引的最左前缀原则的含义
首先讲第一个问题 SQL优化既然是优化那么首先得要定位问题才能对症下药开启慢查询日志监控找出系统中比较慢的SQL。这就减少了筛查范围然后逐条进行执行计划分析。没建索引的建索引建了索引的看看索引是不是失效了然后排查为什么索引失效这些问题排查完了之后可能因为表数据量过大那就要考虑是不是要拆表进行分表。| 常用优化建议: 1.优化查询的选择、连接和排序操作。 2.优化查询中使用的索引包括创建新索引、删除无用索引、调整索引的顺序等。 3.优化查询中使用的表连接方式包括内连接、外连接、自连接等。 4.优化查询中使用的子查询包括对子查询进行优化、使用连接代替子查询等。 5.优化查询中使用的聚合函数包括使用索引进行优化、使用分组连接代替聚合函数等。 第二个问题: 组合索引的最左前缀原则指的是在创建组合索引时应该将最常用于筛选数据的列放在索引的最左侧这样可以使索引更有效地帮助查询优 化。 例如如果有一张表中包含三个字段A、B和C并且频繁使用A和B 这两个字段进行筛选数据则应该将 A和B 作为组合索引的最左前 缀而不是C。 这样在使用组合索引进行查询时数据库系统就可以使用索引进行快速筛选而不必扫描整张表。这有助于提高查询的效率。 最左前缀原则对于组合索引的创建非常重要因为它可以帮助数据库系统更有效地使用索引。如果不遵循最左前缀原则则组合索引可能会变 得无用甚至阻碍查询的优化。
分库分表之后id主键如何处理
其实这是分库分表之后你必然要面对的一个问题就是 id昨生成因为要是分成多个表之后每个表都是从 1开始累加那肯定不对啊需要 一个全局唯一的id来支持。所以这都是你实际生产环境中必须考虑的问题。 数据库自增长 ID 这个就是说你的系统里每次得到一个id都是往一个库的一个表里插入一条没什么业务含义的数据然后获取一个数据库自增的一个id。拿到 这个id之后再往对应的分库分表里去写入。 优点非常简单有序递增方便分页和排序。 缺点分库分表后同一数据表的自增ID容易重复无法直接使用可以设置步长但局限性很明显)性能吞吐量整个较低如果设计一个 单独的数据库来实现 分布式应用的数据唯一性即使使用预生成方案也会因为事务锁的问题高并发场景容易出现单点瓶颈。 适用场景单数据库实例的表ID包含主从同步场景部分按天计数的流水号等分库分表场景、全系统唯一性ID场景不适用。 Redis生成ID 通过Redis的INCR/INCRBY自增原子操作命令能保证生成的1D肯定是唯一有序的本质上实现方式与数据库一致。 优点整体吞吐量比数据库要高。 缺点Redis 实例或集群宕机后找回最新的ID值比较麻烦。 适用场景比较适合计数场景如用户访问量订单流水号日期流水号等 UUID、GUID生成ID 优点性能非常高本地生成没有网络消耗; 缺点UUID太长了、占用空间大作为主键性能太差了; 由于UUID不具有有序性会导致B树索引在写的时候有过多的随机写操作 适合的场景如果你是要随机生成个什么文件名、编号之类的你可以用UUID但是作为主键不建议用UUID的。 snowflake雪花算法 snowflake 算法来源于Twitter使用scala语言实现snowflake算法的特性是有序、唯一并且要求高性能低延迟每台机器每秒至少生 成10k条数据并且响应时间在2ms以内要在分布式环境多集群跨机房下使用因此 snowflake算法得到的ID是分段组成的: 与指定日期的时间差毫秒级41位够用69年 集群1D机器ID10位最多支持1024台机器 序列12位每台机器每毫秒内最多产生4096个序列号 雪花算法核心思想是分布式ID固定是一个long型的数字一个long型占8个字节也就是64个bit 1bit符号位固定是0表示全部|D都是正整数 41bit表示的是时间戳单位是毫秒。41 bits可以表示的数字多达241-1也就是可以标识241-1个毫秒值换算成年就是表示 69年的时间。 10bit机器ID有异地部署多集群的也可以配置需要线下规划好各地机房各集群各实例ID的编号 12bit序列ID用来记录同一个毫秒内产生的不同id12 bits 可以代表的最大正整数是2^12-14096也就是说可以用这个12 bits 代表的数字来区分同一个毫秒内的4096个不同的id。 优点 毫秒数在高位自增序列在低位整个ID都是趋势递增的。 不依赖数据库等第三方系统以服务的方式部署稳定性更高生成ID的性能也是非常高的。 可以根据自身业务特性分配bit位非常灵活。 缺点 强依赖机器时钟如果机器上时钟回拨会导致发号重复或者服务会处于不可用状态。
IP地址如何在数据库中存储
在MySQL中当存储IPv4地址时应该使用32位的无符号整数UNSIGNEDINT来存储IP地址而不是使用字符串用UNSIGNED INT 类型存储IP地址是一个4字节长的整数。 如果是字符串存储|P地址在正常格式下最小长度为7个字符(0.0.0.0)最大长度为15个255.255.255.255)因此我们通常会使用varchar(15)来存储。同时为了让数据库准确跟踪列中有多少数据数据库会添加额外的1字节来存储字符串的长度。这使得以字符串表示的IP的实际数据存储成本需要16字节。 这意味着如果将每个IP地址存储为字符串的话每行需要多耗费大约10个字节的额外资源。 如果你说磁盘够使不是事儿那我得告诉你这个不仅会使数据文件消耗更多的磁盘如果该字段加了索引也会同比例扩大索引文件的大小缓存数据需要使用更多内存来缓存数据或索引从而可能将其他更有价值的内容推出缓存区。执行SQL对该字段进行CRUD时也会消耗更多的CPU资源。 MySQL中有内置的函数来对IP和数值进行相互转换。 INET_ATON() 将IP转换成整数。 算法第一位乘256三次方第二位乘256二次方第三位乘256一次方第四位乘256零次方 • INET_NTOA() 将数字反向转换成IP SELECT INET_ATON(‘127.0.0.1’; Java INET_ATON(‘127.0.0.1’| 2130706433 1 row in set (0.00 sec) SELECT INET_NTOA(‘2130706433’); INET_NTOA(‘2130706433’)| 127.0.0.1 1 row in set (0.02 sec) 如果是 IPv6地址的话可以使用函数INET6_ATON()和 INET6_NTOA()来转化: Mysql SELECT HEX(INET6_ATON(‘1030::C9B4:FF12:48AA:1A2B’)) Java | HEX(INET6_ATON(‘1030::C9B4:FF12:48AA:1A2B’)| | 1030000000000000C9B4FF1248AA1A2B 1 row in set mysql SELECT INET6_NTOA(UNHEX(1030000000000000C9B4FF1248AA1A2B | INET6_NTOA(UNHEX(‘1030000000000000C9B4FF1248AA1A2B’)) | 1030::c9b4:ff12:48aa:1a2b 1 row in set 然后将数据库定义为varbinary 类型分配 128bits空间因为ipv6采用的是128bits16个字节) 间。或者定义为char 类型分配 32bits空
如何实现MySQL的读写分离
其实很简单就是基于主从复制架构简单来说就搞一个主库挂多个从库然后我们就单单只是写主库然后主库会自动把数据给同步到从库上去。
MySQL主从复制原理的是啥
主库将变更写入binlog日志然后从库连接到主库之后从库有一个1O线程将主库的binlog日志拷贝到自己本地写入一个relay 中继日志中。接着从库中有一个SQL线程会从中继日志读取binlog然后执行binlog日志中的内容也就是在自己本地再次执行一遍SQL这样就可以保证自己跟主库的数据是一样的。 这里有一个非常重要的一点就是从库同步主库数据的过程是串行化的也就是说主库上并行的操作在从库上会串行执行。所以这就是一个非常重要的点了由于从库从主库拷贝日志以及串行执行SQL的特点在高并发场景下从库的数据一定会比主库慢一些是有延时的。所以经常出现刚写入主库的数据可能是读不到的要过几十毫秒甚至几百毫秒才能读取到。 而且这里还有另外一个问题就是如果主库突然宕机然后恰好数据还没同步到从库那么有些数据可能在从库上是没有的有些数据可能就 丢失了。 所以MySQL实际上在这一块有两个机制一个是半同步复制用来解决主库数据丢失问题一个是并行复制用来解决主从同步延时问题。 这个所谓半同步复制也叫semi-sync复制指的就是主库写入binlog日志之后就会将强制此时立即将数据同步到从库从库将日志写入 自己本地的relay log之后接着会返回一个ack给主库主库接收到至少一个从库的ack之后才会认为写操作完成了。 所谓并行复制指的是从库开启多个线程并行读取relay log中不同库的日志然后并行重放不同库的日志这是库级别的并行。
MySQL主从同步延时问题
以前线上确实处理过因为主从同步延时问题而导致的线上的bug属于小型的生产事故。 是这个么场景。有个同学是这样写代码逻辑的。先插入一条数据再把它查出来然后更新这条数据。在生产环境高峰期写并发达到了2000/s这个时候主从复制延时大概是在小几十毫秒。线上会发现每天总有那么一些数据我们期望更新一些重要的数据状态但在高峰期时候却没更新。用户跟客服反馈而客服就会反馈给我们。 查看 Seconds_Behind_Master可以看到从库复制主库的数据落后了几ms。 一般来说如果主从延迟较为严重有以下解决方案: ·分库将一个主库拆分为多个主库每个主库的写并发就减少了几倍此时主从延迟可以忽略不计。 打开 MySQL支持的并行复制多个库并行复制。如果说某个库的写入并发就是特别高单库写并发达到了2000/s并行复制还是没意义。 重写代码写代码的同学要慎重插入数据时立马查询可能查不到。 如果确实是存在必须先插入立马要求就查询到然后立马就要反过来执行一些操作对这个查询设置直连主库。不推荐这种方法你这么搞导致读写分离的意义就丧失了。
A
1
原子性Atomicity当前事务的操作要么同时成功要么同时失败。原子性由 undo log日志来保证 一致性Consistency使用事务的最终目的由业务代码正确逻辑保证 ·隔离性Isolation在事务并发执行时他们内部的操作不能互相干扰 ·持久性Durability一旦提交了事务它对数据库的改变就应该是永久性的。 持久性由redo log日志来保证
InnoDB引擎中定义了四种隔离级别供我们使用级别越高事务隔离性越好但性 能就越低而隔离性是由MySQL的各种锁以及MVCC机制来实现的 •read uncommit (读未提交)有脏读问题 (read commit(读已提交有不可重复读问题 repeatable read可重复读)有幻读问题 serializable串行上面问题全部解决
set tx_isolation‘read-uncommitted’; set tx_isolation‘read-committed’; set tx isolation‘repeatable-read’ set tx isolationserializable:
串行化在读后面加锁
读锁共享锁、S锁:select… lock in share mode; 读锁是共享的多个事务可以同时读取同一个资源但不允许其他事务修改 写锁排它锁、X锁select… for update; 写锁是排他的会阻塞其他的写锁和读锁update、delete、insert都会加写锁
B树和B树之间的区别是什么 B-Tree结构 叶节点具有相同的深度 叶节点的指针为空 所有索引元素不重复 节点中的数据索引从左到右递增排列 SHOW GLOBAL STATUS like ‘Innodb_page_size’
BTree B-Tree变种 非叶子节点不存储data只存储索引冗余可以放更多的索引 叶子节点包含所有索引字段 叶子节点用指针连接提高区间访问的性能
什么是索引下推
:索引下推INDEX CONDITION PUSHDOWN简称ICP是在MySQL5.6针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎来减少MySQL存储引擎访问基表的次数以及MySQL服务层访问存储引擎的次数。ICP适用于MYISAM和INNODB本篇的内容只基于INNODB。 在讲这个技术之前你得对mysql架构有一个简单的认识见下图 MySQL服务层也就是SERVER层用来解析SQL的语法、语义、生成查询计划、接管从MySQL存储引擎层上推的数据进行二次过滤 等等。 MySQL存储引擎层按照MySQL服务层下发的请求通过索引或者全表扫描等方式把数据上传到MySQL服务层。 MySQL 索引扫描根据指定索引过滤条件遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。 MySQL索引过滤通过索引扫描并且基于索引进行二次条件过滤后再回表。 select * from ‘user’ where nickname like ‘张%’ and age 18: 索引下推的使用条件 •ICP目标是减少全行记录读取从而减少10操作只能用于非聚簇索引。聚簇索引本身包含的表数据也就不存在下推一说。 只能用于range、ref. where 条件中是用 eq_ref、ref_or_null 访问方法and而非or的时候。 ICP 适用于分区表。 ICP 不支持基于虚拟列上建立的索引比如说函数索引 ICP不支持引用子查询作为条件。 ICP不支持存储函数作为条件因为存储引擎无法调用存储函数。 索引下推相关语句 #查看索引下推是否开启 select optimizer_switch #开启索引下推 set optimizer_switch“index_condition_pushdownon”; #关闭索引下推 set optimizer_switch“index_condition_pushdownoff”; 有哪些情况会导致索引失效
这个问题要分版本回答版本不同可能会导致索引失效的场景也不同直接给答案的都是耍流氓! 这里回答基于最新MySQL8版MySQL8失效的以前版本也失效MySQL8不失效的以前可能会失 效。 使用 like 并且是左边带%右边可以带会走索引但是并不绝对详细解释看下面 like专题分析) 隐式类型转换索引字段与条件或关联字段的类型不一致。比如你的字段是int你用字符串方式去查 询会导致索引失效。 在where条件里面对索引列使用运算或者使用函数。 使用 且存在非索引列 在 where 条件中两列做比较会导致索引失效 使用IN可能不会走索引MySQL环境变量eq_range_index_dive_limit 的值对IN语法有很大影 响该参数表示使用索引情况下 IN中参数的最大数量。MySQL 5.7.3以及之前的版本中, 举例 eq_range_index_dive_limit 的默认值为10之后的版本默认值为200。我们拿 MySQL8.0.19 eq_range_index_dive_limit200表示当IN(…)中的值 200个时该查询一定不会走200则可能用到索引。) 索引。 使用非主键范围条件查询时部分情况索引失效。 使用 order by可能会导致索引失效 is null is not null ≠可能会导致索引失效
如果表中有字段为NULL索引是否会失效
为什么LIKE以%开头索引会失效
数据结构和算法动态可视化Ch 首先看看B树是如何查找数据的 查找数据时MySQL会从根节点开始按照从左到右的顺序比较查询条件和节点中的键值。如果查询条件小于节点中的键值则跳到该节点的左子节点继续查找如果查询条件大于节点中的键值则跳到该节点的右子节点继续查找如果查询条件等于节点中的键值则继续查找该节点的下一个节点。 比如说我有下面这条SQL: select from where nickname 如果数据库中存在南冥 北冥 西冥 东冥那么在B树中搜索的效率和全表扫描还有什么区别呢 走的聚簇索引全表扫描还不用回表。 最后在扩展讲一个点其实不一定会导致索引失效。举个例子: 如果表中有字段为NULL索引是否会失效 首先讲答案不一定。即使我们使用 is null 或者 is not null它其实都是会走索引的。那为什么会有这样的言论呢这里首先就得来讲讲NULL值是怎么在记录中存储的又是怎么在B树中存储的呢。那么在InnoDB中分为聚簇索引和非聚簇索引两种聚簇索引本身是不允许记录为空的所以可以不不用考虑那么就剩下非聚簇索引也就是我们的辅助索引。 那既然IS NULL 用全表扫描呢 IS NOT NULL、 这些条件都可能使用到索引那到底什么时候索引什么时候采 首先我们得知道两个东西第一个在InnoDB引擎是如何存储NULL值的第二个问题是索引是如何存储 NUI|值的 这样我们才能从根上理解 NUIL 在什么场景走索引。在什么场景不走索引 在InnoDB引擎是如何存储NULL值的 InnoDB引擎通过使用一个特殊的值来表示null这个值通常被称为null bitmap。null bitmap是一个二进制位序列用来标记表中每一个列是否为null。当null bitmap中对应的位为1时表示对应的列为null当null bitmap中对应的位为0时表示对应的列不为nul。在实际存储时InnoDB引擎会将null bitmap作为行记录的一部分存储在行记录的开头这样可以在读取行记录时快速判断每个列是否为null。 从头开始说理解起来会比较容易理解了独占表空间文件就更容易理解行格式了接着往下看: 当我们创建表的时候默认会创建一个*.idb文件这个文件又称为独占表空间文件它是由段、区、页、 行组成。InnoDB存储引擎独占表空间大致如下图; 表空间Tablespace) Segment表空间是由各个段segment组成的段是由多个区extent组成的。段一般分为数据段、 索引段和回滚段等。 、数据段 存放B树的叶子节点的区的集合 索引段存放B树的非叶子节点的区的集合 回滚段 存放的是回滚数据的区的集合MVCC就是利用了回滚段实现了多版本查询数据 Extent(区在表中数据量大的时候为某个索引分配空间的时候就不再按照页为单位分配了而是按照区(extent为单位分配。每个区的大小为1MB对于16KB的页来说连续的64个页会被划为一个区这样就使得链表中相邻的页的物理位置也相邻就能使用顺序|/O了。 我们知道InnoDB存储引擎是用B树来组织数据的。B树中每一层都是通过双向链表连接起来的如果是以页为单位来分配存储空间那么链表中相邻的两个页之间的物理位置并不是连续的可能离得非常远那么磁盘查询时就会有大量的随机/O随机|/O是非常慢的。解决这个问题也很简单就是让链表中相邻的页的物理位置也相邻这样就可以使用顺序|/0了那么在范围查询扫描叶子节点的时候性能就会很高。) Page(页记录是按照行来存储的但是数据库的读取并不以「行」为单位否则一次读取也就是一次1/0 操作只能处理一行数据效率会非常低。 因此InnoDB的数据是按「页」为单位来读写的也就是说当需要读一条记录的时候并不是将这个行 记录从磁盘读出来而是以页为单位将其整体读入内存。 默认每个页的大小为16KB也就是最多能保证16KB的连续存储空间。 页是InnoDB存储引擎磁盘管理的最小单元意味着数据库每次读写都是以16KB为单位的一次最少从磁盘中读取16K的内容到内存中一次最少把内存中的16K内容刷新到磁盘中。 页的类型有很多常见的有数据页、undo日志页、溢出页等等。数据表中的行记录是用「数据页」来管理 的数据页的结构这里我就不讲细说了总之知道表中的记录存储在「数据页」里面就行。 Row行数据库表中的记录都是按行row进行存放的每行记录根据不同的行格式有不同的存储结 构。 重点来了! InnoDB提供了4种行格式分别是Redundant、Compact、Dynamic和Compressed行格式。 Redundant 是很古老的行格式了MySQL5.0版本之前用的行格式现在基本没人用了那就不展开 详讲了。 MySQL 5.0之后引入了Compact 行记录存储方式由于Redundant不是一种紧凑的行格式而采用更为紧凑的Compact设计的初衷就是为了让一个数据页中可以存放更多的行记录从MySQL5.1版本之后行格式默认设置成Compact。 Dynamic 和Compressed 两个都是紧凑的行格式它们的行格式都和Compact 差不多因为都是基 于Compact改进一点东西。从MySQL5.7版本之后默认使用Dynamic行格式。 那么我们来看看Dynamic里面长什么样先混个脸熟。 这里简单介绍一下Dynamic行格式其他内容后面单独出一个章节介绍。 NULL值列表本问题介绍重点 表中的某些列可能会存储NULL值如果把这些NULL值都放到记录的真实数据中会比较浪费空 间所以Compact行格式把这些值为NULL的列存储到NULL值列表中。如果存在允许 NULL值的列则每个列对应一个二进制位bit二进制位按照列的顺序逆序排列。 二进制位的值为 1时代表该列的值为NULL。二进制位的值为0时代表该列的值不为 NULL。另外NULL值列表必须用整数个字节的位表示1字节8位如果使用的二进制位个数 不足整数个字节则在字节的高位补0。 当然NULL值列表也不是必须的。当数据表的字段都定义成NOTNULL的时候这时候表里的行格 式就不会有NULL值列表了。所以在设计数据库表的时候通常都是建议将字段设置为NOT NULL这样可以节省1字节的空间NULL值列表占用1字节空间。 厂NULL值列表」的空间不是固定1字节的。当一条记录有9个字段值都是NULL那么就会创建2字节空间的「NULL值列表」以此类推。 索引是如何存储NULL值的? 我们知道 InnoDB引擎中按照物理存储的不同分为聚簇索引和非聚簇索引聚簇索引也就是主键索引那么是不允许为空的。那就不再我们本问题的讨论范围我们重点来看看非聚簇索引非聚簇索引是允许值为空的。 在InnoDB中非聚簇索引是通过B树的方式进行存储的 从图中可以看出对于 s1表的二级索引 idx_key1 来说值为 NULL的二级索引记录都被放在了 B 树的最左边这是因为设计 InnoDB 的大叔有这样的规定 We define the SQL null to be the smallest possible value of a field 也就是说他们把 SQL中的NULL值认为是列中最小的值。在通过二级索引idx_key1对应的B树快速定位到叶子节点中符合条件的最左边的那条记录后也就是本例中id值为521的那条记录之后就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了直到某条记录的key1列不为NULL。 我们了解了上面的两个问题之后我们就可以来看看使不使用索引的依据是什么了 实际上来说我们用 is null is not null ≠这些条件都是能走索引的那什么时候走索引什么时候走 全表扫描呢? 总结起来就是两个字成本! 如何去度量成本计算使用某个索引执行查询的成本就非常复杂了展开讲这个话题就停不下来了后面考虑 单独列一个篇幅去讲。 这里总结性讲讲第一个读取二级索引记录的成本第二将二级索引记录执行回表操作也就是到聚簇 索引中找到完整的用户记录操作所付出的成本。 要扫描的二级索引记录条数越多那么需要执行的回表操作的次数也就越多达到了某个比例时使用二级索引执行查询的成本也就超过了全表扫描的成本举一个极端的例子比方说要扫描的全部的二级索引记录那就要对每条记录执行一遍回表操作自然不如直接扫描聚簇索引来的快) 所以MySQL优化器在真正执行查询之前对于每个可能使用到的索引来说都会预先计算一下需要扫描的 二级索引记录的数量比方说对于下边这个查询 SELECT * FROM SI WHERE key1 IS NULL; 优化器会分析出此查询只需要查找key1 值为NULL的记录然后访问一下二级索引 idx_key1看一下值为 NULL的记录有多少如果符合条件的二级索引记录数量较少那么统计结果是精确的如果太多的话会采用一定的手段计算一个模糊的值当然算法也比较麻烦我们就不展开说了这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然对于某些查询比方说WHERE子句中有IN条件并且IN条件中包含许多参数的话比方说这样: SQL SELECT * FROM s1 WHERE key1 IN (‘a’,‘b zzzzzzz’ 这样的话需要统计的key1 值所在的区间就太多了这样就不能采用 index dive 的方式去真正的访问二级索引idx_key1而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条很显然根据统计数据去估算记录条数比 index dive 的方式精确性差了很多。 反正不论采用 index dive 还是依据统计数据估算最终要得到一个需要扫描的二级索引记录条数如果这个条数占整个记录条数的比例特别大那么就趋向于使用全表扫描执行查询否则趋向于使用这个索引执行查询。 理解了这个也就好理解为什么在WHERE子句中出现 IS NULL、 这些条件仍然可以 使用索引本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。 大家可以看到MySQL 中决定使不使用某个索引执行查询的依据很简单就是成本够不够小。而不是是否在WHERE子句中用了 IS NULL、IS NOT NULL、这些条件。大家以后也多多辟谣吧没那么复杂只是一个成本而已。
为什么LIKE以%开头索引会失效
首先看看B树是如何查找数据的 // 特殊情况这种也是会走索引的虽然我的age在前面username在后面。 / 刚刚不是手最左前缀匹配吗为什么放到第二位也可以呢? // 虽说顺序不一致但是在SQL执行过程中根据查询条件命中索引// 无论我username在不在前面都会按照username去进行索引查找。select * from user where age 18 and username ‘张三 使用Order By时能否通过索引排序 我们知道在很多场景下会导致索引失效比如说没有遵循B树的最左匹配原则但是也有一些情况是遵循了最左匹配原则但是还是没有走索引这里我们使用order by进行排序的时候就有不走索引的情况那么带大家来分析一下 SQL drop table if exists drop table if exists create table ‘user’( user user_examp te id int primary key comment’主键ID’ card_id’ int comment’身份证’, nickname’varchar(10)comment‘昵称’,age’ int not null comment’年龄’‘card_id’ (‘card_id’) key ) engineInnoDB default charsetutf8mb4; //这里我们明明对card_id建好了单列索引那为什么不走索引呢 select * from ‘user’ order by card_id 如果索引覆盖是可以走索引的 如果带上索引条件是可以走索引的
通过索引排序内部流程是什么呢
explain select nickname,card_id,age from user order by card_id; 我们在了解mysql底层是怎么排序的之前我们先来了解一下一个概念 sort buffen 首先mysql会为每一个线程都分配一个固定大小的sort buffer 用于排序。它是一个具有逻辑概念的 内存区域我们可以通过sort_buffer_size 参数来控制默认值是 256kb // 输入查看最小可以设置为32K最大可以设置为46。 show variables like ‘sort_buffer_size’; SQL 由于 sort buffer 大小是一个固定的但是我们待排序的数据量它不是所以根据它们之间的一个差值 呢就分为了内部排序和外部排序 当待排序的数据量小于等于sort buffer 时那我们的sort buffer 就能够容纳MySQL就可以直接 在内存里面排序就行了内部排序使用的排序算法是 快排 当待排序的数据量大于 sort buffer时那我们的sort buffer 就不够用了对吧。这个时候MySQL就得要借助外部文件来进行排序了。将待排序数据拆成多个小文件对各个小文件进行排序最后再汇总成一个有序的文件外部排序使用的算法时 归并排序 我们来聊聊row_id排序 和大家说一个这个参数max_length_for_sort_data 在我们MySQL中专门控制用户排序的行数据长 度参数。默认是4096也就是说如果超过了这个长度MySQL就会自动升级成 row_id 算法。 //默认max_length_for_sort_data的大小为4096字节 show variables like ‘max_Length_for_sort_data’ row_id 排序的思想就是把不需要的数据不放到 sort buffe中让sort buffer只存放需要排序的字段。
explain select nickname,card_id,age from user order by card_id; SQL 我们前面说到了sort buffer在 sort buffer 里面进行排序的数据是我们select的全部字段所以当我们查询的字段越多那么 sort buffer 能容纳的数据量也就越小。而通过row_id 排序就只会存放row_id 字段和排序相关的字段。其余的字段等排序完成之后通过主键1D进行回表拿。
group by分组和order by在索引使用上有什么不同吗 没什么太大的差异 group by实际是先进行排序再进行分组。所以遵循order by的索引机制。
索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引一般来说执行查询时遍历整张表。 索引的原理就是把无序的数据变成有序的查询 1.把创建了索引的列的内容进行排序 2.对排序结果生成倒排表 3.在倒排表内容上拼上数据地址链 4.在查询的时候先拿到倒排表内容再取出数据地址链从而拿到具体数据
索引设计的原则?
查询更快、占用空间更小 1.适合索引的列是出现在where子句中的列或者连接子句中指定的列 2.基数较小的类索引效果较差没有必要在此列建立索引 3.使用短索引如果对长字符串列进行索引应该指定一个前缀长度这样能够节省大量索引空间如果搜索词 超过索引前缀长度则使用索引排除不匹配的行然后检查其余行是否可能匹配。 4.不要过度索引。索引需要额外的磁盘空间并降低写操作的性能。在修改表内容的时候索引会进行更新甚至 重构索引列越多这个时间就会越长。所以只保持需要的索引有利于查询即可。 5.定义有外键的数据列一定要建立索引。 6.更新频繁字段不适合创建索引 7.若是不能有效区分数据的列不适合做索引列(如性别男女未知最多也就三种区分度实在太低) 8.尽量的扩展索引不要新建索引。比如表中已经有a的索引现在要加(a,b)的索引那么只需要修改原来的索 引即可。 9.对于那些查询中很少涉及的列重复值比较多的列不要建立索引。 10.对于定义为text、image和bit的数据类型的列不要建立索引。
索引覆盖是什么
索引覆盖就是一个SQL在执行时可以利用索引来快速查找并且此SQL所要查询的字段在当前索引对应的字段中都包含了那么 就表示此SQL走完索引后不用回表了所需要的字段都在当前索引的叶子节点上存在可以直接作为结果返回了 最左前缀原则是什么 当一个SQL想要利用索引是就一定要提供该索引所对应的字段中最左边的字段也就是排在最前面的字段比如针对abc三个字段建立了一个联合索引那么在写一个sql时就一定要提供a字段的条件这样才能用到联合索引这是由于在建立a,b,c三个字段的联合索引时底层的B树是按照abc三个字段从左往右去比较大小进行排序的所以如果想要利用B树进行快速查找也得符合这个规则 Innodb是如何实现事务的 Innodb通过Buffer PoolLogBuffer,Redo LogUndo Log来实现事务以一个update语句为例:1.Innodb在收到一个update语句后会先根据条件找到数据所在的页并将该页缓存在Buffer Pool中2.执行update语句修改Buffer Pool中的数据也就是内存中的数据 3.针对update语句生成一个RedoLog对象并存入LogBuffer中 4.针对update语句生成undolog日志用于事务回滚 5.如果事务提交那么则把RedoLog对象进行持久化后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中 6.如果事务回滚则利用undolog日志进行回滚
MySQL的集群是如何搭建的读写分离是怎么做的? 由于这个发送binlog的过程是异步的。主服务在向客户端反馈执行结果时是不知道binlog是否同步成功了的。这时候如果主服务宕机了而从服务还没有备份到新执行的binlog那就有可能会丢数据。 那怎么解决这个问题呢这就要靠MySQL的半同步复制机制来保证数据安全 半同步复制机制是一种介于异步复制和全同步复制之前的机制。主库在执行完客户端提交的事务后并不是立即返回客户端响应而是等待至少一个从库接收并写到relay log中才会返回给客户端。MySQL在等待确认时默认会等10秒如果超过10秒没有收到ack就会降级成为异步复制。 这种半同步复制相比异步复制能够有效的提高数据的安全性。但是这种安全性也不是绝对的他只保证事务提交后的binlog至少传输到了一个从库并且并不保证从库应用这个事务的binlog是成功的。另一方面半同步复制机制也会造成一定程度的延迟这个延迟时间最少是一个TCP/IP请求往返的时间。整个服务的性能是
mysql高可用方案 1、MMM MMM(Master-Master replication managerfor MysqlMysql主主复制管理器)是一套由Perl语言实现的脚本程序可以对mysql集群进行监控和故障迁移。他需要两个Master同一时间只有一个Master对外提供服务可以说是主备模式。 他是通过一个VIP(虚拟IP)的机制来保证集群的高可用。整个集群中在主节点上会通过一个VIP地址来提供数据读写服务而当出现故障时VIP就会从原来的主节点漂移到其他节点由其他节点提供服务。 2、MHA Master High Availability Manager and Tools for MySQL。是由日本人开发的一个基于Perl脚本写的工具。这个工具专门用于监控主库的状态当发现master节点故障时会提升其中拥有新数据的slave节点成为新的master节点在此期间MHA会通过其他从节点获取额外的信息来避免数据一致性方面的问题。MHA还提供了mater节点的在线切换功能即按需切换master-slave节点。MHA能够在30秒内实现故障切换并能在故障切换过程中最大程度的保证数据一致性。在淘宝内部也有一个相似的TMHA产品。 MHA是需要单独部署的分为Manager节点和Node节点两种节点。其中Manager节点一般是单独部署的一台机器。而Node节点一般是部署在每台MySQL机器上的。Node节点得通过解析各个MySQL的日志来进行一些操作。 Manager节点会通过探测集群里的Node节点去判断各个Node所在机器上的MySQL运行是否正常如果发现某个Master故障了就直接把他的一个Slave提升为Master然后让其他Slave都挂到新的Master上去完全透明。 3、MGR MGR:MySQL Group Replication。是MySQL官方在5.7.17版本正式推出的一种 组复制机制。主要是解决传统异步复制和半同步复制的数据一致性问题。 由若干个节点共同组成一个复制组一个事务提交后必须经过超过半数节点的决议并通过后才可以提交。引入组复制主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。MGR依靠分布式一致性协议(Paxos协议的一个变体)实现了分布式下数据的最终一致性提供了真正的数据高可用方案(方案落地后是否可靠还有待商榷)。 支持多主模式但官方推荐单主模式 。多主模式下客户端可以随机向MySQL节点写入数据 单主模式下MGR集群会选出primary节点负责写请求primary节点与其它节 点都可以进行读请求处理. GTID同步集群 上面我们搭建的集群方式是基于Binlog日志记录点的方式来搭建的这也是最 为传统的MySQL集群搭建方式。而在这个实验中可以看到有一个 Executed_Grid_Set列暂时还没有用上。实际上这就是另外一种搭建主从同步的 方式即GTID搭建方式。这种模式是从MySQL5.6版本引入的。 GTID的本质也是基于Binlog来实现主从同步只是他会基于一个全局的事务ID来标识同步进度。GTID即全局事务ID全局唯一并且趋势递增他可以保证为每一个在主节点上提交的事务在复制集群中可以生成一个唯一的ID。 在基于GTID的复制中首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值然后主库会有把所有没有在从库上执行的事务发送到从库上进行执行并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次这样可以避免由于偏移量的问题造成数据不一致。 他的搭建方式跟我们上面的主从架构整体搭建方式差不多。只是需要在my.cnf中 修改一些配置。 在主节点上
mysql聚簇和非聚簇索引的区别
都是B树的数据结构 聚簇索引将数据存储与索引放到了一块、并且是按照一定的顺序组织的找到索引也就找到了数据数据的I物理存放顺序与索引顺序是一致的即只要索引是相邻的那么对应的数据一定也是相邻地存放在磁盘上的非聚簇索引叶子节点不存储数据、存储的是数据行地址也就是说根据索引查找到数据行的位置再取磁盘查找数据这个就有点类似一本树的目录比如我们要找第三章第一节那我们先在这个目录里面找找到对应的页码后再去对应的页码看文章。 优势 1、查询通过聚簇索引可以直接获取数据相比非聚簇索引需要第二次查询非覆盖索引的情况下效率要高 2、聚簇索引对于范围查询的效率很高因为其数据是按照大小排列的 3、聚簇索引适合用在排序的场合非聚簇索引不适合 劣势: 1、维护索引很昂贵特别是插入新行或者主键被更新导至要分页page sp1it)的时候。建议在大量插入新行后选在负载较低的时间段通过OPTIMIZE TABLE优化表因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片 2、表因为使用UUId随机ID作为主键使数据存储稀疏这就会出现聚族索引有可能有比全表扫面更慢所以建 议使用int的auto_increment作为主键 3、如果主键比较大的话那辅助索引将会变的更大因为辅助索引的叶子存储的是主键值过长的主键值会导致非 叶子节点占用占用更多的物理空间 InnoDB中一定有主键主键一定是聚簇索引不手动设置、则会使用unique索引没有unique索引则会使用 数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引辅助索引访问数据总是需要二次查找非聚簇索引都是辅助索引像复合索引、前缀索引、唯一索引辅助索引叶子节点存储的不再是行的物理位置而是主键值 MyISM使用的是非聚簇索引没有聚簇索引非聚簇索引的两棵B树看上去没什么不同节点的结构完全一致只是存储的内容不同而已主键索引B树的节点存储了主键辅助键索引B树存储了辅助键。表数据存储在独立的地方这两颗B树的叶子节点都使用一个地址指向真正的表数据对于表数据来说这两个键没有任何差别。由于索引树是独立的通过辅助键检索无需访问主键的索引树。 如果涉及到大数据量的排序、全表扫描、count之类的操作的话还是MyISAM占优势些因为索引所占空间小 这些操作是需要在内存中完成的。
Mysql数据库中什么情况下设置了索引但无法使用?
1.没有符合最左前缀原则 2.字段进行了隐私数据类型转化 3.走索引没有全表扫描效率高
Innodb是如何实现事务的
Innodb通过Buffer PoolLogBufferRedo LogUndo Log来实现事务以一个update语句为例:1.Innodb在收到一个update语句后会先根据条件找到数据所在的页并将该页缓存在Buffer Pool中2.执行update语句修改Buffer Pool中的数据也就是内存中的数据 3.针对update语句生成一个RedoLog对象并存入LogBuffer中 4.针对update语句生成undolog日志用于事务回滚 5.如果事务提交那么则把RedoLog对象进行持久化后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中 6.如果事务回滚则利用undolog日志进行回滚
mysql索引的数据结构各自优劣
索引的数据结构和具体存储引擎的实现有关在MySQL中使用较多的索引有Hash索引B树索引等InnoDB存储引擎的默认索引实现为B树索引。对于哈希索引来说底层的数据结构就是哈希表因此在绝大多数需求为单条记录查询的时候可以选择哈希索引查询性能最快其余大部分场景建议选择BTree索引。 B树: B树是一个平衡的多叉树从根节点到每个叶子节点的高度差值不超过1而且同层级的节点间有指针相互链接。在B树上的常规检索从根节点到叶子节点的搜索效率基本相当不会出现大幅波动而且基于索引的顺序扫描时也可以利用双向指针快速左右移动效率非常高。因此B树索引被广泛应用于数据库、文件系统等场景。
哈希索引:
哈希索引就是采用一定的哈希算法把键值换算成新的哈希值检索时不需要类似B树那样从根节点到叶子节点 逐级查找只需一次哈希算法即可立刻定位到相应的位置速度非常快 如果是等值查询那么哈希索引明显有绝对优势因为只需要经过一次算法即可找到相应的键值前提是键值都是 唯一的。如果键值不是唯一的就需要先找到该键所在位置然后再根据链表往后扫描直到找到相应的数据; 如果是范围查询检索这时候哈希索引就毫无用武之地了因为原先是有序的键值经过哈希算法后有可能变成不连续的了就没办法再利用索引完成范围查询检索 哈希素引也没办法利用索引完成排序以及likexxx%这样的部分模糊查询这种部分模糊查询其实本质上也是 范围查询; 哈希索引也不支持多列联合索引的最左匹配规则; B树索引的关键字检索效率比较平均不像B树那样波动幅度大在有大量重复键值情况下哈希索引的效率也是 极低的因为存在哈希碰撞问题。
MySQL有哪几种数据存储引擎有什么区别?
MySQL中通过show ENGINES指令可以看到所有支持的数据库存储引擎。 最为常用的就是MyISAM和nnoDB两种。 MylSAM和LnnDB的区别: 1、存储文件。 MylSAM每个表有两个文件。 MYD和MYISAM文件。 MYD是数据文件。 MYI是索引文件。而InnDB每个表 只有一个文件idb。 2、LnnoDB支持事务支持行级锁支持外键。 3、InnoDB支持XA事务 4、LnnoDB支持savePoints
mysql主从同步原理
mysql主从同步的过程: Mysql的主从复制中主要有三个线程master(binlog dump thread) thread)Master—条线程和Slave中的两条线程。 ·主节点binlog主从复制的基础是主库记录数据库的所有变更记录到binlog。binlog是数据库服务器启动的 那一刻起保存所有修改数据库结构或内容的一个文件。 主节点log dump线程当binlog有变动时log dump 线程读取其内容并发送给从节点。 从节点1/O线程接收binlog内容并将其写入到relaylog文件中。 ·从节点的SQL线程读取 relay log 文件内容对数据更新进行重放最终保证主从数据库的一致性。 注主从节点使用binglog文件position偏移量来定位主从同步的位置从节点会保存其已接收到的偏移量 如果从节点发生宕机重启则会自动从 position的位置发起同步。 由于mysql默认的复制方式是异步的主库把日志发送给从库后不关心从库是否已经处理这样会产生一个问题就 是假设主库挂了从库处理失败了这时候从库升为主库后日志就丢失了。由此产生两个概念。 全同步复制 主库写入binlog后强制同步日志到从库所有的从库都执行完成后才返回给客户端但是很显然这个方式的话性能 会受到严重影响。 半同步复制 和全同步不同的是半同步复制的逻辑是这样从库写入日志成功后返回ACK确认给主库主库收到至少一个从库 的确认就认为写操作完成。
海量数据下如何快速查找一条记录?
1、使用布隆过滤器快速过滤不存在的记录。 使用Redis的bitmap结构来实现布隆过滤器。 2、在Redis中建立数据缓存。-将我们对Redis使用场景的理解尽量表达出来。 以普通字符串的形式来存储(serld-user.json)。以一个hash来存储一条记录(userld key-username field-,userAge-)。以一个整的hash来存储所有的数据Userlnfo-field就用userldvalue就用user.json。一个hash最多能支持2^32-1(40多个亿)个键值对。 缓存击穿对不存在的数据也建立key。这些key都是经过布隆过滤器过滤的所以一般不会太多 缓存过期将热点数据设置成永不过期定期重建缓存。 使用分布式锁重建缓存。 3、查询优化。 按槽位分配数据 自己实现槽位计算找到记录应该分配在哪台机器上然后直接去目标机器上找
事务的基本特性和隔离级别有哪些?
事务 表示多个数据操作组成一个完整的事务单元这个事务内的所有数据操作要么同时成功要么同时失败。 事务的特性ACID 1、原子性事务是不可分割的要么完全成功要么完全失败。 2、一致性事务无论是完成还是失败都必须保持事务内操作的一致性。当失败时都要对前面的操作进行回滚不管中途 是否成功。 3、隔离性当多个事务操作一个数据的时候为防止数据损坏需要将每个事务进行隔离互相不干扰。 4、持久性事务开始就不会终止。他的结果不受其他外在因素的影响。 事务的隔离级别SHOW VARIABLES like’transaction%’ 设置隔离级别set transaction level xxx设置下次事务的隔离级别。 set session transaction level xxx设置当前会话的事务隔离级别 set global transaction level xxx设置全局 set global transaction level xxx设置全局事务隔离级别 MySQL当中有五种隔离级别 NONE不使用事务。 READ UNCOMMITED:I允许脏读 READ COMMITED防止脏读最常用的隔离级别 REPEATABLE READ:防止脏读和不可重复读。MYSQL默认 SERIALIZABLE事务串行可以防止脏读、幻读不可重复度。 五种隔离级别级别越高事务的安全性是更高的但是事务的并性能也就会越低。
什么是MVCC
MVCC(Multi-Version Concurrency Control)多版本并发控制就可以做到读写不阻塞, 且避免了类似脏读这样的问题主要通过undo日志链来实现 read commit(读已提交语句级快照 repeatable read可重复读)事务级快照
多版本并发控制读取数据时通过一种类拟快照的方式将数据保存下来这样读锁就和写锁不冲突了不同的事务 session会看到自己特定版本的数据版本链 MVCC只在 READ COMMITTED 和 REPEATABLE READ两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容因为READ UNCOMMITTED总是读取最新的数据行而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。 聚簇索引记录中有两个必要的隐藏列 trx_id用来存储每次对某条聚簇索引记录进行修改的时候的事务id。 roll_pointer每次对哪条聚簇索引记录有修改的时候都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针它指向这条聚簇索引记录的上一个版本的位置通过它来获得上一个版本的记录信息。注意插入操作的undo日志没有这个属性因为它没有老版本) 已提交读和可重复读的区别就在于它们生成ReadView的策略不同。 开始事务时创建readviewreadView维护当前活动的事务id即未提交的事务id排序生成一个数组 访问数据获取数据中的事务id获取的是事务id最大的记录对比readview: 如果在readvew的左边比readview都小可以访问在左边意味着该事务已经提交 如果在readview的右边比readview都大或者就在readview中不可以访问获取roll_pointer取上一版本 重新对比在右边意味着该事务在readview生成之后出现在readview中意味着该事务还未提交 已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的 时候生成一个ReadView之后的读都复用之前的ReadView。 这就是MysqI的MVCC,通过版本链实现多版本可并发读-写写-读。通过ReadView生成策略的不同实现不同的 隔离级别。
什么是脏读、幻读、不可重复读要怎么处理?
这些问题都是MySQL进行事务并发控制时经常遇到的问题。 脏读在事务进行过程中读到了其他事务未提交的数据。 不可重复读在一个事务过程中多次查询的结果不一致。 幻读在一个事务过程中用同样的操作查询数据得到的记录数不相同。 处理的方式有很多种加锁、事务隔离、MVCC 加锁 1、脏读在修改时加排他锁直到事务提交才释放。读取时加共享锁读完释放锁。 2、不可重复读读数据时加共享锁写数据时加排他锁。 3、幻读加范围锁。
事务的基本特性和隔离级别
事务基本特性ACID分别是: 原子性指的是一个事务中的操作要么全部成功要么全部失败。 一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。 BCf转账给B100块钱假设A只有90块支付之前我们数据库里的数据都是符合约束的但是如果事务执行成功了,我们的数据库数据就破坏约束了因此事务不能成功这里我们说事务提供了一致性的保证 隔离性指的是一个事务的修改在最终提交前对其他事务是不可见的。 持久性指的是一旦事务提交所做的修改就会永久保存到数据库中。 隔离性有4个隔离级别分别是: read uncommit 读未提交可能会读到其他事务未提交的数据也叫做脏读。 用户本来应该读取到id1的用户age应该是10结果读取到了其他事务还没有提交的事务结果读取结果 age20这就是脏读。 read commit 读已提交两次读取结果不一致叫做不可重复读. 不可重复读解决了脏读的问题他只会读取已经提交的事务。 用户开启事务读取id1用户查询到age10再次读取发现结果20在同一个事务里同一个查询读取到不同的结果叫做不可重复读。 repeatable read 可重复复读这是mysgl的默认级别 就是每次读取结果都一样但是有可能产生幻读。 serializable串行一般是不会使用的他会给每一行读取的数据加锁会导致大量超时和锁竞争的问题。 脏读(Drity Read)某个事务已更新一份数据另一个事务在此时读取了同一份数据由于某些原因前一个
Mysql慢查询该如何优化?
1.检查是否走了索引如果没有则优化SQL利用索引 2.检查所利用的索引是否是最优索引 3.检查所查字段是否都是必须的是否查询了过多字段查出了多余数据 4.检查表中数据是否过多是否应该进行分库分表了 5.检查数据库实例所在机器的性能配置是否太低是否可以适当增加资源
什么是Mysql中的降序索引
大家可能对索引比较熟悉而对降序索引比较陌生事实上降序索引是索引的子集。 我们通常使用下面的语句来创建一个索引:
create index idx tl bcd on tl(b,c,d); 上面sql的意思是在t1表中针对bc,d三个字段创建一个联合索引。 但是大家不知道的是上面这个sql实际上和下面的这个sql是等价的: create index idx tl_bcd on tl(b asc,c asc,d asc); asc 表示的是升序使用这种语法创建出来的索引叫做升序索引。也就是我们平时在创建索引的时候创建的都是升序索引。 可能你会想到在创建的索引的时候可以针对字段设置asc那是不是也可以设置desc呢 当然是可以的 文章转载自： http://www.morning.trrrm.cn.gov.cn.trrrm.cn http://www.morning.xqgh.cn.gov.cn.xqgh.cn http://www.morning.mjqms.cn.gov.cn.mjqms.cn http://www.morning.flxqm.cn.gov.cn.flxqm.cn http://www.morning.tpyrn.cn.gov.cn.tpyrn.cn http://www.morning.dnhdp.cn.gov.cn.dnhdp.cn http://www.morning.wjqbr.cn.gov.cn.wjqbr.cn http://www.morning.jbblf.cn.gov.cn.jbblf.cn http://www.morning.rhmt.cn.gov.cn.rhmt.cn http://www.morning.hdwjb.cn.gov.cn.hdwjb.cn http://www.morning.xqcbz.cn.gov.cn.xqcbz.cn http://www.morning.mwwnz.cn.gov.cn.mwwnz.cn http://www.morning.nlbw.cn.gov.cn.nlbw.cn http://www.morning.kpygy.cn.gov.cn.kpygy.cn http://www.morning.lizpw.com.gov.cn.lizpw.com http://www.morning.xpzrx.cn.gov.cn.xpzrx.cn http://www.morning.fjglf.cn.gov.cn.fjglf.cn http://www.morning.hlhqs.cn.gov.cn.hlhqs.cn http://www.morning.kcfnp.cn.gov.cn.kcfnp.cn http://www.morning.hlshn.cn.gov.cn.hlshn.cn http://www.morning.coffeedelsol.com.gov.cn.coffeedelsol.com http://www.morning.zqmdn.cn.gov.cn.zqmdn.cn http://www.morning.ttfh.cn.gov.cn.ttfh.cn http://www.morning.krnzm.cn.gov.cn.krnzm.cn http://www.morning.hbhnh.cn.gov.cn.hbhnh.cn http://www.morning.gcfrt.cn.gov.cn.gcfrt.cn http://www.morning.wnkqt.cn.gov.cn.wnkqt.cn http://www.morning.ndmh.cn.gov.cn.ndmh.cn http://www.morning.qzfjl.cn.gov.cn.qzfjl.cn http://www.morning.dskmq.cn.gov.cn.dskmq.cn http://www.morning.qrwnj.cn.gov.cn.qrwnj.cn http://www.morning.rgxn.cn.gov.cn.rgxn.cn http://www.morning.knmby.cn.gov.cn.knmby.cn http://www.morning.dspqc.cn.gov.cn.dspqc.cn http://www.morning.bsqkt.cn.gov.cn.bsqkt.cn http://www.morning.dnphd.cn.gov.cn.dnphd.cn http://www.morning.wmfr.cn.gov.cn.wmfr.cn http://www.morning.gccrn.cn.gov.cn.gccrn.cn http://www.morning.zfgh.cn.gov.cn.zfgh.cn http://www.morning.rxtxf.cn.gov.cn.rxtxf.cn http://www.morning.njpny.cn.gov.cn.njpny.cn http://www.morning.kjfsd.cn.gov.cn.kjfsd.cn http://www.morning.rbhcx.cn.gov.cn.rbhcx.cn http://www.morning.wlqll.cn.gov.cn.wlqll.cn http://www.morning.hprmg.cn.gov.cn.hprmg.cn http://www.morning.gkpgj.cn.gov.cn.gkpgj.cn http://www.morning.tqsnd.cn.gov.cn.tqsnd.cn http://www.morning.cyyhy.cn.gov.cn.cyyhy.cn http://www.morning.cpqqf.cn.gov.cn.cpqqf.cn http://www.morning.ybhjs.cn.gov.cn.ybhjs.cn http://www.morning.tslfz.cn.gov.cn.tslfz.cn http://www.morning.hlnrj.cn.gov.cn.hlnrj.cn http://www.morning.llxns.cn.gov.cn.llxns.cn http://www.morning.hrkth.cn.gov.cn.hrkth.cn http://www.morning.wcjk.cn.gov.cn.wcjk.cn http://www.morning.rpth.cn.gov.cn.rpth.cn http://www.morning.nyfyq.cn.gov.cn.nyfyq.cn http://www.morning.ybhrb.cn.gov.cn.ybhrb.cn http://www.morning.svrud.cn.gov.cn.svrud.cn http://www.morning.skdrp.cn.gov.cn.skdrp.cn http://www.morning.srxhd.cn.gov.cn.srxhd.cn http://www.morning.c-ae.cn.gov.cn.c-ae.cn http://www.morning.yqkxr.cn.gov.cn.yqkxr.cn http://www.morning.zsleyuan.cn.gov.cn.zsleyuan.cn http://www.morning.qnzld.cn.gov.cn.qnzld.cn http://www.morning.gpcy.cn.gov.cn.gpcy.cn http://www.morning.jjnry.cn.gov.cn.jjnry.cn http://www.morning.gyfhk.cn.gov.cn.gyfhk.cn http://www.morning.yqndr.cn.gov.cn.yqndr.cn http://www.morning.ydwsg.cn.gov.cn.ydwsg.cn http://www.morning.myxps.cn.gov.cn.myxps.cn http://www.morning.lhsdf.cn.gov.cn.lhsdf.cn http://www.morning.dmkhd.cn.gov.cn.dmkhd.cn http://www.morning.nbmyg.cn.gov.cn.nbmyg.cn http://www.morning.nqfxq.cn.gov.cn.nqfxq.cn http://www.morning.pggkr.cn.gov.cn.pggkr.cn http://www.morning.dmkhd.cn.gov.cn.dmkhd.cn http://www.morning.ghssm.cn.gov.cn.ghssm.cn http://www.morning.ztdlp.cn.gov.cn.ztdlp.cn http://www.morning.ftmly.cn.gov.cn.ftmly.cn
