企业网站流量预估,旅游网站建设初衷,找网站有中文字目的,Wordpress怎么给图片加来源mysql数据库 数据库基础知识 为什么要使用数据库 数据存内存#xff1b;优点#xff1a;存取速度快。缺点#xff1a;数据不能永久保存 数据存文件。优点#xff1a;数据永久存储。缺点#xff1a;速度比内存操作慢#xff0c;频繁i…mysql数据库 数据库基础知识 为什么要使用数据库 数据存内存优点存取速度快。缺点数据不能永久保存 数据存文件。优点数据永久存储。缺点速度比内存操作慢频繁io查询不方便 存数据库。优点数据永久保存、使用sql语句查询方便效率高、管理数据方便 什么是sql 结构化查询语句是一个数据库查询语言 作用用于存取数据、查询、更新、管理关系型数据库 什么是mysql mysql是一个关系型数据库 数据库三大范式 第一范式原子性每列都不可再拆分 第二范式在第一范式的基础上一个表只说明一个事物 确保表中每列都和主键相关 第三范式在第二范式的基础上吗每列都与主键有直接关系不存在传递依赖(和主键直接相关而不是间接相关 mysql的binlog有几种录入格式分别有什么区别 三种格式如下 statement 每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化减少了binlog日质量节约了io提高了性能。由于sql的执行是有上下文的因此保存的时候要保存相关信息同时还有一些使用了函数之类的语句无法被记录复制 row 不记录sql语句上下文相关信息仅保存哪条记录被修改。记录单元为每一行的改动基本上是全记下来但是由于很多操作会导致大量行的改动alter table因此这种模式的文件保存的信息太多日志量太大 mixed 一种折中的方案普通操作使用statement记录当无法使用statement记录的时候使用row 引擎 mysql存储引擎与MyISAM与InnoDB的区别 常用的存储引擎 innoDB 提供了对数据库的ACID事务的支持。并且还提供了行级锁和外键的约束。他的设计目标就是处理大数据容量的数据库系统。 myisam 不提供事务的支持也不支持行级锁和外键 memory 所有数据都存在内存中数据的处理速度快但是安全性不高 区别 存储结构 myisam每张表都存放在三个文件中frm-表格定义、MYD-数据文件、MYI-索引文件 innoDB所有的表都保存在同一个数据文件中也可能是多个文件或者独立的表空间文件InnoDB表的大小值受限于操作系统文件的大小一般为2gb 存储空间 myisam可被压缩存储空间较小 innoDB需要更多的内存和存储他会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 可移植性、备份及恢复 myisam是以文件的形式存储所以跨平台的数据转移中会很方便在备份和恢复时可以单独对某个表进行操作 innoDB免费的方案可以是拷贝数据文件、备份binlog或者使用mysqldump在数据量大的时候就很痛苦了 文件格式 myisam数据和索引时分别存储的数据时.MYD,索引时MYI innoDB数据和索引时集中存储的.ibd 记录存储顺序 myisam按记录插入顺序保存 innoDB按主键大小有序插入 外键、事务、锁 myisam不支持外键、事务、表级锁定 innoDB支持外键、事务、行级锁、表级锁、锁定粒度小并发能力高 索引的实现 myisamb树索引myisam是堆表不支持哈希索引支持全文索引 innoDBb树索引Innoab是索引组织表支持哈希索引不支持全文索引 MyISAM索引与InnoDB索引的区别 myisam 索引是非聚簇索引 索引的叶子节点存储的是行数据地址需要在寻址一次才能得到数据 innoDB 索引是聚簇索引 主键索引的叶子节点存储这行数据因此主键索引非常高效 非主键索引的叶子节点存储的是主键和其他带索引的列数据因此查询时做到覆盖索引会非常高效 InnoDB引擎的4大特性 插入缓冲insert buffer 二次写double write 自适应哈希索引(ahi) 预读read ahead) 存储引擎选择 如果没有特别的要求使用默认的innoDB即可 myisam以读写插入为主的应用程序比如博客、新闻网站 innoDB更新删除频率高或者要保证数据的完整性并发量高支持事务和外键 索引 什么是索引 索引是一种数据结构相当于目录是数据库系统中的一个排序结构字段以协助快速查询更新数据表中数据。索引的实现通常是b树及其变种b树 索引有哪些优缺点 优点可以大大加快数据的检索速度通过使用索引可以在查询过程中使用优化器提高系统性能 缺点时间方面创建和维护索引需要耗费时间具体对表中数据进行增删改时索引也要动态维护会降低增改删的执行效率空间方法索引需要占用物理内存 索引的使用场景重点 where 字段加索引 order by 字段加索引 join on 字段加索引 索引覆盖如果要查询的字段都建立过索引那么引擎会直接在索引表中查询不会访问原始数据但只要有一个字段没有建立索引就会做全表扫描。因此我们尽可能在Select后只写必要的查询字段以增加索引的覆盖几率 值得注意的是不要想着为每个字段添加索引因为优先使用索引的优势就在于其体积小 索引有哪几种类型 主键索引 数据列不允许重复不允许为null一个表只能有一个主键索引 唯一索引 数据列不允许重复运行为null一个表允许多个列创建唯一索引。【可以单列创建唯一索引和多列创建唯一组合索引】 ALTER TABLE table_name ADD UNIQUE (column); 普通索引 基本的索引类型没有唯一的限制可以为null。【可以创建普通索引和组合索引】 ALTER TABLE table_name ADD INDEX index_name (column); 全文索引 目前搜索引擎使用的一种关键技术 ALTER TABLE table_name ADD FULLTEXT (column); 索引的数据结构b树hash 描述索引的数据结构和具体存储引擎的实现相关在mysqsl中使用较多的有Hash索引B树索引等InnoDB存储引擎默认是索引实现为b树。对于哈希索引来说底层的数据结构就是哈希表因此在绝大多数需求为单条记录查询的时候可以选择哈希索引查询性能最快其他场景建议使用b树索引 b树结构图 b树索引 主键索引区p关联保存的是数据的地址按主键查询 普通索引区关联的id地址然后在到达上面的地址。所以按主键查询速度最快 特性 n棵子树的节点包含n个关键字不用来保存数据而是保存数据的索引 所有的叶子节点中包含了全部的关键字信息及指向含这些关键字记录的指针且叶子节点本身依关键字的大小自小而大顺序链接 所有的非终端节点可以看成是索引部分节点中仅含子树中的最大或最小关键字 b树中数据对象的插入和删除仅在叶节点上进行 b树有两个头指针一个是树的根节点一个是最小关键码的叶节点 哈希索引 类似于数据结构中简单实现的hash表一样当我们在mysql中用哈希索引时主要就是通过hash算法将数据库字段转换成定长的hash值与这条数据的行指针一并存入hash表的对应位置如果发生hash碰撞则以链表的形式存储 哈希结构 索引的基本原理 简单来说就是对无序的数据进行排序变成有序的查询 1.把创建了索引的列的内容进行排序 2.对排序结果生成倒排表 3.在倒排表内容上拼上数据地址链 4.在查询的时候先拿到倒排内容再取出数据地址链从而拿到具体数据 索引的算法有哪些 btree算法 最常用的数据库索引算法也是mysql默认的算法 需要从根节点到枝节点最后才能访问到页节点需要多次io 他不仅可以在被用在,,和between这些操作符上而且还可以like只要他的查询条件不以通配符开头比如like 查询%, 如果以通配符开头索引将会失效。如like %查询 hash算法 只能用于对等比较如操作符。由于是一次定位所以检索效率高。 索引设计的原则 适合索引的列是出现在where子句中的列获取连接子句中指定的列 基数较小的类索引效果较差没有必要在此列建立索引 使用短索引如果对长字符串列进行索引应该指定一个前缀长度这样能够节省大量索引空间 不要过度索引。【索引需要额外的磁盘空间并降低写操作的性能在修改表内容的时候索引会进行更新甚至重构索引列越多这个时间就越长。所以只保持需要的索引有利于查询即可】 创建索引的原则重点 索引虽好但也不能无限制使用最好符合以下几个原则 1.最左前缀匹配原则组合索引非常重要的原则mysql会一直想右匹配知道遇到范围查询、、between、like就会停止匹配。如a1 and b 2 and c 2 and d10如果建立abcd顺序的索引d是用不到索引的。如果建立abdc的索引则可以用到abd的顺序可以任意调整 2.较频繁作为查询条件的字段才去创建索引 3.更新频繁的字段不适合创建索引 4.如是不能有效的区分数据的列不适合做索引列如性别男女未知最多也就三种区分度太低导致需要遍历才能找到影响查询效率 5.尽可能的扩展索引不要新建索引。比如表中已经有a的索引现在要加ab只需要修改原来的索引即可 6.定义有外键的数据列一定要建立索引 7.对于查询中很少涉及的列重复值比较多的不要建立索引 8.对于定义为text、image和bit的数据类型列不要建立索引 创建索引的三种方式删除索引 创建表的时候创建索引 使用alert table user add index indexNamecolumn_name) create index 创建 alert table user drop primary key 索引名 删除主键索引删除主键索引需要先取消自增长再删除 alter table user drop key索引名删除普通索引、唯一索引、全文索引) 创建索引时需要注意什么 非空字段应该指定列为not null除非你想存储null。在mysql中含有空值的列很难进行查询优化因为他们使得索引的统计信息以及运算更为复杂。你应该以0或者一个特殊的值或者空串替代空值 取值离散大的字段变量各个取值至今的差异程度的列放到联合索引的前面可以通过count函数查看字段的差异值返回值越大说明字段的唯一值越多字段的离散程度越高 索引字段越小越好数据库的数据存储以页为单位一页存储的数据越多一次io获取的数据越大效率越高 使用索引查询一定能提高查询的性能吗为什么 通常通过索引查询比全表扫描要快。但是也有代价 索引需要空间来存储也需要定期维护每当有记录在表中增减或者索引列被修改时索引本身也会修改这意味着每条记录的增删改将多付出4、5次的磁盘io。不一定能提高查询性能索引范围查询INDEX RANGE SCAN适用于下面两种情况 基于一个范围的检索一般查询返回结果集小于表中记录数的30% 基于非唯一索引的检索 百万级或以上的数据如何删除 由于索引需要额外的维护成本对数据删除时会产生额外的对所有的操作会消耗额外的io会降低删除效率所以再删除百万级数据时删除数据的速度和创建索引数量是成正比的 1.先删除索引 2.在删除无用数据 3.删除完之后重新创建索引 这比直接删除要快如果万一删除中断一切删除回滚那将更慢 前缀索引 语法index(fieid(10))使用字段值的前10个字符创建索引默认使用字段的全部内容建立索引 前提前缀的标识度高。比如密码就适合建立前缀索引因为密码几乎各不相同 实操的难度在于前缀截取的长度 我们可以利用select count(*)/count(distinct left (password,prefixLen)通过调整prefixLen的值查看不同前缀长度的一个平均匹配度接近1时就可以了表示一个密码的前prefixLen个字符几乎就能确定唯一一条记录 什么是最左前缀原则什么是最左匹配原则 最左优先在创建多列索引时要根据业务需求where子句中使用最频繁的一列放在最左边 最左前缀匹配原则组合索引非常重要的原则mysql会一直想右匹配直到遇到范围查询、、between、like就会停止匹配。如a1 and b 2 and c 2 and d10如果建立abcd顺序的索引d是用不到索引的。如果建立abdc的索引则可以用到abd的顺序可以任意调整 和in可以乱序a2 and b3 and c6建立a,b,c)索引可以任意顺序mysql的查询优化器会帮助我们优化成索引可以识别的形式 b树和b树的区别 在b树中内部节点和叶子节点都可以存放键和值b树中间节点存放键叶子节点可以同时存放键和值 b树的叶子节点有一条链相连而b树叶子节点相互独立 使用b树的好处 b树可以在内部节点同时存储值和键因此把访问频繁的数据放在靠近根节点的地方会大大提高热点数据的查询效率。这种特性使得b树在特定数据重复多次查询的场景中更加高效 使用b树的好处 由于b树内部节点只存放键不存放值因此一次读取可以在内存中获取到更多的键有利于更快的缩小查询范围b树叶子节点有一条链连接因此当需要进行一次全数据遍历时b树需要一次On时间找到最小的一个节点然后通过链进行O(n)的顺序遍历查询即可而b树需要一层一层遍历会需要更多的内存置换次数 Hash索引和b树索引有什么区别或者说优劣 hash索引底层就是哈希表进行查找时调用一次hash函数就可以获取到相应的键值之后进行回表查询获得实际数据 b树底层实现的多路平衡查找树对于每一次查询都是从根节点出发查找到叶子节点方可获得所查键值然后根据查询判断是否需要回表查询数据 hash索引进行等值查询快但是无法范围查询因为哈市索引经过hash函数建立索引之后索引的顺序与原顺序无法保持一致不能支持范围查询而b树的所有节点都遵循左节点小于父节点右节点大于父节点支持范围查询 hash索引不支持索引进行排序、模糊查询以及最左前缀匹配 hash索引任何时候都避免不了回表查询数据而b树在符合某些条件聚簇索引覆盖索引的时候只需要通过索引就可完成查询 因此多数情况下直接选择b树可以获得稳定且较好的查询速度 数据库为什么使用b树而不是b树 b树只适合做随机检索而b树同时支持随机和顺序检索 b树空间利用率高可减少io次数磁盘读写代价低 b树的查询效率更加稳定。b树的搜索有可能会在非叶子节点结束越靠近根节点的记录查找的时间越短。b树随机检索时任何关键字的查找都必须走一条从根节点到叶节点的路所有关键字查询路径的长度相同所有查询效率相当 b树在提高了磁盘io性能的同时并没有解决元素遍历效率低下的问题b树的叶子节点使用指针顺序链接在一起只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围查询是非常频繁的 增删文件节点时效率更高因为b树的叶子节点包含所有关键字并且有序的链表结构存储这样可以很好的提高增删效率 b树在满足聚簇索引和覆盖索引的时候不需要回表查询数据 在b树的索引中叶子节点可能存储了当前的key或者同时存储了值和数据这就是聚簇索引和非聚簇索引在innoDB中只有主键索引时聚簇索引如果没有主键则挑选一个唯一键建立聚簇索引。如果没有唯一键则隐式的生成一个键来建立聚簇索引 当查询使用聚簇索引时在对应的叶子节点可以获取到整行数据因此不用回表 什么是聚簇索引何时使用聚簇索引而非聚簇索引 聚簇索引将数据与索引放到了一块存储找到索引也就找到了数据 非聚簇索引将数据和索引分开结构索引结构的叶子节点指向了数据的对应行myisam通过key buffer把索引先缓存到内存中当需要访问数据时通过索引访问数据在内存中直接搜索索引然后通过索引在磁盘中找到相应数据。这也是为什么索引不在key buffer命中时速度慢的原因 在innoDB中在聚簇索引之上创建的索引称为辅助索引辅助索引访问数据总是需要二次查找非聚簇索引都是辅助索引像复合索引、前缀索引、唯一索引、辅助索引叶子节点存储的不再是行的物理位置而是主键值 非聚簇索引一定会回表查询吗 不一定要看查询语句所要求的字段是否全部命中了索引命中就不用回表查询 联合索引是什么为什么需要注意联合索引中的顺序 使用多个字段同时建立一个索引叫做联合索引 为什么要注意顺序在联合索引中如果想要命中索引需要按照建立索引时的字段顺序挨个使用否则无法命中 事务 什么是数据库事务 一个不可分割的数据库操作序列逻辑上一组操作要么都执行要么都不执行 事务的四大特性ACID 介绍一下 原子性事务是最小的执行单位不允许分割。事务的原子性就是确保动作要么全部完成要么全部不起作用 一致性执行事务前后数据保持一致多个事务对同一个数据读取的结果是相同的 隔离性并发访问数据库一个用户的事务不被其他事务干扰各并发事务之间的数据库是独立的 持久性一个事务被提交后他对数据库中改变的数据时持久的即使数据库发生故障也不应该对其造成任何影响 什么是脏读幻读不可重复读 在多个事务并发操作数据库时如果没有有效的机制避免就会导致出现。 脏读读取到了另一个事务未提交的数据 不可重复读前后两次读取的数据不一致 幻读一个事务在前后读取同一个范围的数据总数不一致幻读和不可重复读有些类似但是幻读强调的是集合的增减而不是单条数据的更新 什么是事务的隔离级别mysql默认的事务隔离级别是什么 为了解决以上问题主流的关系型数据库都会提供四种事务的隔离级别。mysql默认的是可重复读的级别 读未提交read-uncommitted)最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读幻读不可重复读 读已提交read-committed允许读取并发事务已提交的数据可以防止脏读但是幻读或不可重复读还有可能发生 可重复读repeatable-read)对同一字段的多次读取结果都是一致的除非数据是被本事事务自己所修改可以组织脏读和不可重复读但幻读还有可能发生 可串行化serializable最高隔离级别完全服从acid。所有的事务依次逐个执行。可防止脏读不可重复读幻读 事务隔离机制的实现基于锁机制和并发调度其中并发调度使用的是mvvc多版本并发控制通过保存修改的旧版本信息来支持并发一致性读和回滚等特性 锁 对mysql的锁了解吗 当数据库有并发事务时可能会产生数据的不一致这时候需要一些机制来保证访问次序锁机制就是这样一个机制 隔离级别与锁的关系 读未提交级别下读取数据不需要加共享锁这样就不会跟被修改的数据上的排他锁冲突 读已提交的级别下读操作需要加共享锁但是语句执行完以后要释放锁 可重复读级别下读操作需要加共享锁但是事务提交之前并不释放共享锁也就是必须等事务执行完毕才释放共享锁 可串行化级别是限制性最强的因为该级别锁定整个范围的键并一直持有锁知道事务完成 按照锁的粒度分 数据库锁有哪些锁机制与innoDB锁算法 在关系型数据库中可以按照锁的粒度分为行级锁INNODB引擎表级锁MYISAM引擎和页级锁BDB引擎 myisam和innoDB存储引擎使用的锁 myisam使用的表级锁 innodb使用的是行级锁和表级锁默认为行级锁 行级锁、表级锁、页级锁对比 行级锁是mysql中锁定粒度最小的一种锁只针对当前操作的行进行加锁。行级锁大大减少数据库操作的冲突。其加锁粒度最小但是加锁的开销也最大行级锁分为 共享锁和排他锁
表级锁是mysql中锁定粒度最大的一种锁表示对当前操作的表加锁表级分为共享锁和排他锁 页级锁一次锁定相邻的一组数据介于行级锁和表级锁之间的一种锁折中方案。 特点开销大加锁慢会出现死锁所粒度最小发生锁冲突的概率最低并发度也高 特点开销小、加锁快不会出现死锁锁粒度大发生锁冲突的概率最高并发也最低 特点开销和加锁时间介于表锁和行锁之间会出现死锁并发度一般 从锁的类别分mysql都有哪些锁像上面那样子进行锁岂不是有点阻碍并发效率了 从锁的分类上来讲有共享锁和排他锁 共享锁读锁。当用户要进行数据读取时对数据加锁可以同时加多个锁 排他锁写锁。当用户要进行写入数据时对数据上锁排他锁只可以加一个和其他排他锁共享锁相斥 锁的粒度取决于存储引擎innoDB实现了行级锁页级锁表锁 他们的加锁开销从大到小并发能力也是从大到小 mysql中InnoDB引擎的行锁是怎么实现的 innoDB是基于索引来实现的 例如select * from user where id1 for update; for update可以根据条件来完成行锁并且id是有索引键的列如果id不是索引键的列那么innoDB将完成表锁 InnoDB存储引擎的锁的算法有三种 record lock单个行记录上的锁 gap lock间隙锁锁定一个范围不包括记录本身 next-key lockrecordgap锁定一个范围包含记录本身 相关知识点 innoDB对于行的查询使用next-key lock next-key lock为了解决幻读问题 当查询的索引含有唯一属性时将next-key lock降级为record lock 什么是死锁怎么解决 指两个或者多个事务在统一资源上相互占用并请求锁定对方资源从而导致恶性循环的现象 常见的解决死锁方法 1.如果不同程序会并发取多个表尽量约定以相同的顺序访问表 2.在同一个事务中尽可能做到一次锁定所需要的所有资源 3.对于容易产生死锁的业务部分可以尝试使用升级锁定粒度通过表级锁定减少死锁产生的概率 数据库的悲观锁和乐观锁是什么怎么实现的 悲观锁假定会发生冲突屏蔽一切可能违反数据完整性的操作。在查询的时候把事务锁起来知道提交事务。实现方式使用数据库中的锁机制 乐观锁假设不会发生并发冲突只在提交操作时检查是否违反数据完整性。在修改事务时把事务锁起来通过version的方式来进行锁定。实现方式使用版本号或者cas算法实现 视图 为什么要使用视图什么是视图 为了提高复杂sql的复用性和表操作的安全性 视图本质上是一种虚拟表视图并不在数据库中以存储数据的形式存在行和列来自定义视图的查询所引用的基本表并且在具体引用视图时动态生成 视图有哪些缺点 视图的列可以来自不同的表是表得到抽象和逻辑意义上建立的新关系 视图是由基本表实表)产生的表虚表 视图饿建立和删除不影响基本表 对视图内容的更新直接影响基本表 当视图来自多个基本表不允许添加和删除 视图的使用场景有哪些 简化sql提高开发效率兼容老的表结构 重用sql 使用表的组成部分而不是整个表 保护数据 视图的优点 查询简单化视图能简化用户操作 数据安全性 逻辑数据独立性 视图的缺点 性能 修改限制 什么是游标 是系统为用户开设的一个数据缓冲区存放sql的执行结果单个游标区都有一个名字用户可以通过游标逐一获取记录并赋给主变量交由语言进一步处理 存储过程与函数 什么是存储过程有什么优缺点 是一个预编译的sql语句。优点是允许模块化设计只需创建一次以后就可以在程序中多次调用如果其操作要执行多次sql使用存储过程比单纯的sql语句执行要快 缺点 调试麻烦移植性差后期维护麻烦 优点 存储过程是预编译过的执行效率高 直接存储在数据库通过存储过程名直接调用减少网络通信 安全性高执行存储过程需要一定的权限 可以复用 触发器 什么是触发器触发器的使用场景有哪些 触发器是指一段代码当触发某个事件时自动执行这些代码由事件驱动的特殊的存储过程 使用场景 通过数据库中相关表的级联更改 实时监控某个表某个字段修改后需要作出的处理 mysql中都有哪些触发器 有如下六种 before insert after insert before update after update before delete after delete 常用sql语句 sql语句主要分为哪几类 数据定义语言DDLcreate、drop、alter对逻辑结构等有操作其中包括结构视图和索引 数据查询语言DQL select查询操作 数据操作语言DMLinsert、update、delete对数据进行操作 数据控制语言DCLgrant、revoke、commit、rollback;权限控制操作 超键、候选键、主键、外键分别是什么 超键在关系中能唯一标识元祖的属性集称为关系模式的超键一个属性可以作为一个超键多个属性组合在一起也可以作为一个超键。超键包含候选键和主键 候选键是最小超键即没有冗余元素的超键 主键数据库中对存储数据对象予以唯一和完整标识列或属性的组合一个数据列只能有一个主键且不能为空 外键在一个表中存在的另一个表的主键 sql约束有哪几种 not null用于控制字段的内容一定不能为null unique控制字段内容不能重复一个表允许有多个unique约束 primary key主键用于控制字段内容不能重复且表中只允许出现一个 foreign key外键用于预防破坏表之间的连接动作 check用于控制字段的值范围 五种关联查询 交叉连接 cross join 内连接 inner join 外连接 left join 、right join 联合查询 union 、union all 全连接 full join 什么是子查询 条件一条sql语句的查询结果作为另一条查询语句的条件或者查询结果 嵌套多条sql语句嵌套使用内部的sql查询语句为子查询 子查询的三种情况 子查询是单行单列 子查询是多行单列 子查询是多行多列 mysql中的in和exists区别 MySQL中的in语句是把外表和内表做hash连接而exists时候对外表作loop循环在对内表进行查询。 一直大家都觉得exists比in效率要高其实是要看场景的 1.如果查询的两个表大小差不多那么差别不大 2.如果两个表中一个较小一个是大表则子查询表大的用exists子查询表小的用in 3.not in和not exists如果查询语句使用了not in 那么内外表都进行全表扫描没有用到索引而not exists的子查询依然能用到表上的索引。所以无论哪个表大not exists都比not in要快 varchar与char的区别 char的特点 char表示定长字符串长度是固定的 如果插入的数据长度小于char的固定长度时则用空格填空 因为长度固定索引存取速度要比varchar快但也因其长度固定所以会占用多余的空间 最多存放的字符个数为255 varchar的特点 varchar表示可变长字符串长度是可变的 插入的数据是多长就按多长来存储 长度不固定插入比char慢不占用多余的空间 最多存放字符个数为65532 varchahr(50)中的50含义 最多存放50个字符varchar50和200存放hello所占空间是一样的但是后者排序时会消耗更多的内存因为order by col采用fixed length计算col长度 int20中20的含义 指显示字符的长度20表示最大显示宽度为20但仍占4字节存储 float和double区别 float类型数据可以存储至多8位十进制并在内存中占4字节 double类型数据可以存储至多18为十进制并咋子内存中占8字节 drop、delete、truncate区别 三者都是删除但使用上有区别 类型delete 属于dmltruncate属于ddldrop属于ddl 回滚 只有delete可以回滚 删除内容delete表结构还在删除表的全部或者一部分数据truncate表结构还在删除表彰的所有数据drop从数据库中删除表数据、索引、权限也会被删除 删除速度delete删除速度慢需要逐行删除truncate删除速度快drop删除速度最快 union与union all区别 如果使用union all 不会合并重复的记录行 效率union all高于union sql优化 如何定位及优化sql语句的性能问题创建索引有没有被用到 使用explain命令查看语句的执行计划 sql的生命周期 1.应用服务器与数据库建立连接 2.数据库进程拿到sql 3.解析并执行sql 4.读取数据到内存中并进行逻辑处理 5.发送结果到客户端 6.关闭连接释放资源 大表数据查询怎么优化 优化sqlsql语句索引 加缓存redis 主从复制读写分离 垂直拆分根据模块的耦合度将系统分为一个个小系统分布式系统 水平切分针对数据量大的表选择一个合理的sharding key为了更好的查询效率表结构需要改动以一定冗余应用也要改sql中尽量带sharding key将数据定位到限定的表上去查而不是全表扫描 超大分页怎么处理 超大分页一般从两个角度解决 数据库层面减少load的数据 从需求角度减少这种请求 mysql分页 limit 慢查询日志 配置项 show_query_log 可以使用show variables likeshow_query_log查看是否开启如果状态值为off可以使用set global show_query_logon来开启他会在datadir中产生一个xx-show.log文件 设置临界时间 long_query_time show cariables like long_query_time 单位秒 set long_query_log0.5 关心过业务系统里面的sql耗时吗通过统计过慢查询吗对慢查询都怎么优化过 优化分三个方面 首先分析语句是否load了额外的数据可能查询了多余的行并且抛弃掉了或者加载了许多结果中并不需要的列对语句进行分析以及重写 分析语句的执行计划然后获的其使用索引的情况 如果对语句的优化已经无法进行可以考虑表中的数据是否太大如果是的话可以进行横向或者纵向分表 为什么尽量要设定一个主键 主要是数据库却表数据行在整张表唯一性的保障设定了主键之后在后续的删改差的时候可以更加快速以及确保操作数据范围安全 主键使用自增id还是uuid 推荐使用自增长id不要使用uuid 因为在innoDB中主键索引时作为聚簇索引存在的也就是说主键索引的b树叶子节点上存储了主键索引以及全部的数据按照顺序如果主键索引是自增长id那么只需要不断向后排列即可如果是uuid由于到来的id与原来的大小不确定会造成非常多的数据插入数据移动然后导致产生很多的内存碎片进而造成插入性能的下降 字段为什么要定义not null null值会占用更多的字节且会在程序中造成很多与预期不符的情况 如果存储用户的密码散列应该用什么字段进行存储 固定长度的字符串应该使用char这样可以节省空间并且提高检索效率 优化查询过程中的数据访问 查询不需要的数据解决方案使用limit 多表关联查询返回全部列解决方案指定列返回 总是返回全部列解决方案避免使用select * 重复查询相同的数据解决方案使用缓存 是否在扫描额外的记录解决方案使用explain进行分析如果发现查询需要扫描大量的数据但只返回少数的行可以使用这些技巧 使用索引覆盖扫描把所有的列都放到索引中这样存储引擎不需要回表就能返回结果 改变数据库和表的结构 重写sql 优化关联查询 确保on关联的字段是否有索引 确保group by和order by只有一个表中的列这样才能走索引 优化子查询 用关联查询优化 优化limit分页 优化union查询 优化where语句 对查询进行优化应尽量减少全表扫描考虑在where及order by涉及的列加索引 会导致索引失效 尽量避免where中对字段进行null判断 尽量避免使用 或者 尽量避免使用where中使用or in和not in也要慎用 对于连续的值可以用between and where子句中使用参数也会导致索引失效 避免在where子句中对字段进行表达式操作或者算数运算符或者函数 like时不要以通配符开头不然也会导致索引失效 数据库优化 为什么要优化 随着系统的运行数据会越来越多处理的时间就会变慢 数据库结构优化 考虑数据冗余、查询和更新的速度字段的类型是否合理等 将字段很多的表分解成多个表对于字段较多的表如果有些字段使用频率很低可以将这些字段分离出来因为当一个表的数据量很大时会由于使用频率低的字段的存在而变慢 增加中间表对于经常要联合查询的表可以建立中间表提高查询效率通过建立中间表将需要通过联合查询的数据插入到中间表将原来的联合查询改为对中间表的查询 增加冗余字段合理的加入冗余字段可以提高查询效率。冗余字段在一个表中修改了其他表也要更新要不然会导致数据不一致 mysql数据库的cpu飙升到500%的话他怎么处理 首先使用top命令查看是哪个进程占用的cpu最高 如果是mysql使用show processlist看看session情况看看是不是有消耗资源的sql在运行找出消耗高的sql看看执行计划看是否加索引或者数据列太大的问题 大表怎么优化某个表有近千万条数据curd比较慢怎么优化分库分表怎么做分表分库了有什么问题有用到什么中间件吗他们的原理是什么 当mysql单表数据过大时curd性能会明显下降 常见的优化措施 限定数据的范围 读、写分离主库负责写从库负责读 缓存 分库分表 mysql的复制原理以及流程 主从复制将主数据库中的ddl和dml操作通过二进制日志binlog传输到数据库上然后将这些日志重新执行从而使得数据库的数据和主数据库保持一致 作用 主数据库出现问题可以切换到从数据库 可以进行数据库层面的读写分离 可以进行数据库的日常备份 解决的问题 数据分布随意开始或者停止复制并在不同的地理位置分布数据备份 负载均衡降低单个服务器的压力 高可用可故障切换 升级测试可以使用更高版本的mysql作为从库 工作原理 在主库上把数据更改记录到二进制日志binlog 从库将主库的日志复制到自己的中继日志relay log 从库读取中继日志的事件将其放到从库的数据库中
