提示适用于想要学习测开方向的MySQL知识的学习完全够用SQL语句分类DDL数据定义语言用来定义数据库对象库表字段DML数据操作语言增删改DQL数据查询语言查询数据库中表中的记录DCL数据控制语言创建数据库用户控制用户访问权限查询字段去除重复数据selectdistinct字段 from 表名数值函数ceil(x) 向上取整floor(x) 向下取整mod(x,y) 返回x/y的模rand() 生成0-1之间的随机数round(x,y) 求参数四舍五入的值保留y位小数字符串函数字符串截取之substring_index(str,delim,count)str:要处理的字符串delim分隔符count:计数日期函数curdate() 获取当前的年月日curtime() 获取当前的时分秒now() 获取当前的年月日-时分秒year(date) 获取指定date的年份month(date) 获取指定date的月份day(date) 获取指定date的日数date_add(date,interval expr type) 在指定的date上增加一个时间间隔expr后的时间值ytpe单位年月日datediff(date1,date2) 返回两个日期之间相差的天数流程控制函数if(value,t,f) 如果value为真则输出t否则fifnull(value1,value2) 若value1不为空返回value1否则返回value2case when [val] then [1] else [2] end 若val为真则返回1否侧返回2case [expr] when [val1] then [1] else [2]约束非空约束 not null 限制阻断数据不能为null唯一约束 unique 保证字段的数据都唯一不重复主键约束 primary key 配用auto_increment 一行数据的唯一标识非空且唯一外键约束 foreign key 用来让两张表之间建立连接抱持数据的完整性和一致性这里需要注意的是有具有外键的表为子表外键所关联的表为父表添加外键1.create table 表名 字段1 类型 constrant [外键名称] foreign kry (外键字段名) references 主表字段2.alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表关联字段删除外键alter table 表名 drop foreign key 外键名称删除/更新行为alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表关联字段默认约束 default 保存数据时如果未指定字段值的值则用默认值检查约束 check 抱持字段满足某一个条件多表查询一对多多对一例如员工和部门之间的关系解决方案添加外键关联多对多例如学生和选课之间的关系添加中间表对应一对一例如个人基本信息和个人其他信息的关系多表查询分类连接查询内连接查询表A,B的交集部分隐式内连接select 字段列表 from 表1表2 where 条件显示内连接select 字段列表 from 表1 [inner] join 表2 on 条件外连接左外连接select 字段列表 from 表1left[outer] join 表2 on 条件查询的是表1和表2共同的数据以及表1的所有数据右外连接select 字段列表 from 表1right[outer] join 表2 on 条件查询的是表1和表2共同的数据以及表2的所有数据自连接当前表与自身连接查询自连接必须用别名select 字段列表 from 表1 别名1 join 表1 别名2 on 条件;自连接可以是内自连接也可以是外自连接连结查询select 字段列表 from 表Aunion [all]select 字段列表 from 表Bunion all / unionunion all 数据整合不去重union 去重注意连结查询的多张表必须字段列数抱持一致字段类型也抱持一致。子查询标量子查询子查询的结果为的单行单列单个值常见的操作符为 ,, ,,,列子查询子查询的结果为一列可以是多行常见的操作符为in,not in,anysome(子查询返回的列表中有一个满足即可),all(子查询返回的列表中必须所有的满足)行子查询子查询的结果为一行可以是都多列常见的操作符为,in not in表子查询子查询的结果为多行多列常见的操作符为in---------------------------------------------------------------------------------------------------------------------------------这里我们重点讲解一下分组查询group by可能在进行多表连结完之后再进行分组完之后,对于初学者来说可能会犯蒙并且只有select指定的字段时才能查看其图但是我们对它原本分组之后的构造却不清晰这里我们来讲解一下。首先第一点分组之后select的字段的条件1.必须是group by 分组字段2.聚合函数count/avg/distinct这一点很重要查询其他的字段则会报错第二点这里我们举例子讲解用户信息表 user_profiledevice_iduniversity2138北京大学3214复旦大学6543北京大学2315浙江大学5432山东大学2131山东大学4321复旦大学答题情况明细表question_practice_detail其中question_id是题目编号result是答题结果。device_idquestion_id21381113214112321411365431112315115231511623151175432118543211221311145432113要求取出学校答过题的用户平均答题数量情况数据1.首先进行内连接之后再分组select from user_profile as us ,question_practice_detail qpd where us.device_idqpd.device_id group by university;那么上方分组后的逻辑到底是怎样的呢其实分组之后你不取select特定的字段它不算一个表而是四个独立的组它只是一个逻辑分组名称university组内包含的所有原始行共 11 行按学校拆分组内关键统计为后续计算用北京大学行 12138、北京大学、111行 26543、北京大学、111总行数 2去重 device_id 数 2复旦大学行 33214、复旦大学、112行 43214、复旦大学、113总行数 2去重 device_id 数 1浙江大学行 52315、浙江大学、115行 62315、浙江大学、116行 72315、浙江大学、117总行数 3去重 device_id 数 1山东大学行 85432、山东大学、118行 95432、山东大学、112行 102131、山东大学、114行 115432、山东大学、113总行数 4去重 device_id 数 2 核心理解分组后不存在 “新的行”而是把原始行 “归类到不同的组里”每个组的边界是university组内是该学校的所有答题记录数据库执行group by后会 “以组为单位” 处理数据比如用聚合函数统计每组的总行数、去重用户数。事务简介事务是一组操作的集合是一个不可分割的单位。它可以把一系列操作作为一个整体向系统发起提交或者撤回。要么同时成功要么同时失败。注意默认的MySQL是自动提交的也就是说当执行一条DML语句MySQL会隐式提交。事务的操作查看事务的提交方式select autocommit;设置事务的提交方式set autocommit0(手动提交提交是需要commit;)此处需要注意的是一但开启手动提交则当前会话的所有的提交都为手动提交若只想开启一个临时的事务块则start transaction;或者begin ---仅对事务开启后commit/rollback之前的语句有效回滚rollback;事务的四大特性原子性事务是不可分割的最小操作单元要么全部成功要么全部失败一致性事务完成时必须使所有数据保持一致状态隔离性数据库事务的隔离机制保证事务不受外部并发操作的影响持久性一旦提交或者回滚对数据的操作是永久的并发事务的问题脏读一个事务读取到另一个事务还没有提交的数据不可重复读一个事务先后读取同一条数据但是两次的数据不一样幻读一个事务内多次执行同一范围查询但两次查询之间另一个事务提交了插入 / 删除操作导致查询结果集的行数变化核心是 “插入 / 删除” 操作引发事务隔离级别隔离级别脏读不可重复读幻读并发性能适用场景READ UNCOMMITTED读未提交✅✅✅最高几乎不用仅测试READ COMMITTED读已提交❌✅✅较高多数 OLTP 场景Oracle 默认REPEATABLE READ可重复读❌❌✅中等MySQL 默认推荐SERIALIZABLE串行化❌❌❌最低数据一致性要求极高的场景READ UNCOMMITTED会出现脏读问题事务A会读取事务B未commit的数据READ COMMITTED读已提交解决事务的脏读问题。但是会出现不可重复读问题。同样一个SQL在同一个事务中查询不一致。REPEATABLE READ:解决不可重复读问题。即使事务B对数据库做出操作但是事务A两次查询结果都一样,只有事务A最后commit才最终修改。但是解决不了幻读问题。serializable串行化解决幻读问题。强行阻塞一个事务A的运行只有等事务B commit之后才显示SQL结果-- 查看事务的隔离级别select transaction_isolation-- 设置事务的隔离级别set [session/global] transaction isolation level [read uncommited/read commited/repeatable read/serializable]存储引擎MySQL体系结构客户端连接器是什么用户或程序连接MySQL的“桥梁”比如Python/PHP/Java代码、Navicat工具、甚至Excel插件。类比你用“图书馆APP”“实体借书卡”“微信小程序”借书——不同工具对应不同的“连接方式”图中的Native C API、JDBC、ODBC、.NET、PHP、Python等。作用把用户的请求比如“查用户表”转换成MySQL能理解的格式传给下一层。连接层包含认证、线程复用、连接限制、内存检查、缓存认证验证“读者身份”——比如你用MySQL账号root/123456登录连接池会查“用户表”确认密码对不对。线程复用避免“每个请求都开新线程”——比如100个读者同时查书接待处不会开100个新员工而是用“复用线程”比如5个员工轮流处理节省资源。连接限制控制“同时进馆人数”——比如MySQL设置max_connections100超过100人就排队防止服务器崩溃。缓存存“常用信息”——比如“热门查询的结果”“用户权限”不用每次都查数据库。服务层这是MySQL的“大脑”处理所有请求的核心逻辑包含4个模块① SQL接口“接收读者的问题”——处理所有SQL语句比如SELECT/INSERT/UPDATE、存储过程、视图、触发器。② 解析器“翻译权限检查”——把你的SQL翻译成MySQL内部指令比如SELECT * FROM user→“查user表的所有列”还要检查你有没有权限比如“普通用户不能查管理员表”。③ 查询优化器“找最快的找书路线”——比如你要查“年龄18的用户”优化器会选“用年龄索引”还是“全表扫描”就像导航选“地铁”还是“打车”目标是最快返回结果。④ 缓存“常用结果暂存”——如果100个人都查“热门书籍”缓存会把结果存起来第101个人查的时候直接返回不用再查书库。引擎层可插拔存储引擎是MySQL最核心的“数据存储层”包含InnoDB、MyISAM、Memory等引擎是什么不同的“书库管理方式”——比如InnoDB支持事务比如“买东西时扣钱和加库存必须同时成功/失败”、行锁改一行数据时不影响其他行是现在的默认引擎比如淘宝、微信的用户数据都用它。MyISAM不支持事务但查询快比如以前的论坛帖子主要是查不用改但崩溃后容易丢数据。Memory数据存在内存里比如临时统计数据速度极快但重启MySQL就没了。关键特点可插拔——你可以随时把“文学书库”换成“工具书库”比如把表引擎从InnoDB改成MyISAM不用改上面的服务层。存储层包含系统文件和文件日志是数据的“物理存储”系统文件比如NTFS、ext2/3文件系统Windows/Linux的磁盘格式就像图书馆的“书架材质”——用什么方式存文件。文件日志记录所有操作的“历史账本”包括Redo日志重做日志比如你改了一条数据先写Redo日志再写数据文件——万一断电用Redo恢复数据就像图书馆的“操作记录”万一书被烧了能重新买一本。Undo日志回滚日志比如你执行UPDATE没提交想撤销——用Undo恢复到之前的状态就像“撤销键”。Binary日志二进制日志记录所有“修改数据的操作”比如INSERT/UPDATE/DELETE用来做主从复制主库的日志传给从库从库重放操作保持数据一致就像图书馆的“副本账本”。用“查用户数据”的场景串起全流程现在你用Python程序查“id1的用户姓名”整个过程是这样的客户端连接器Python通过JDBC连接MySQL就像用APP连图书馆。连接层连接池验证Python的账号密码认证分配一个线程接待员检查连接数没超限制。服务层SQL接口接收请求SELECT name FROM user WHERE id1。解析器翻译SQL检查你有没有查user表的权限。查询优化器决定用id的索引如果有的话比全表扫描快。缓存检查有没有人之前查过这个id如果有直接返回缓存结果。引擎层如果缓存没有InnoDB引擎去存储层找id1的记录就像去书库找书。存储层从磁盘文件里读出数据返回给引擎层再传给服务层最后给Python程序。存储引擎简介创建表的时候指定存储引擎create table 表名字段1类型【注释】...engineinnodb [注释]查看数据库支持的引擎show engines;InnoDB存储引擎特点支持事务ACID行级锁提高并发访问性能外键抱持数据的完整性一致性文件xxx.ibd(xxx为表名)MySIAM存储引擎MySQL早期默认存储引擎特点不支持事务不支持行级锁支持表锁没有外键访问速度快文件xxx.sdi:存储表结构信息xxx.MYD:存储数据xxx.MYI:存储索引Memory存储引擎表数据存储在内存中受硬件断电影响只能将这些表作为临时表或者缓存使用特点内存存放hash索引文件xxx.sdi:存储表结构信息索引帮助MySQL高效获取数据的数据结构优点提高查询效率排序效率索引结构BTree索引最常见的索引大部分引擎都支持B树索引hash索引底层数据结构使用hash表实现的只有精确匹配索引列的查询有效不支持范围查询between ,,,...R-Tree索引(空间索引):MySIAM引擎支持的特殊索引类型主要用于地理空间数据类型使用叫较少Full-text索引全文索引)是一种建立倒排索引快速匹配文档的方式索引的分类按照【物理存储】分类InnoDB专属聚集索引必须有且只能有一个将数据存储和索引放到一块索引结构的叶子节点保留了行数据二级索引可以存在多个将索引和数据分开存储索引结构的叶子节点关联的对应的主键聚集索引的选取规则如果存在主键则主键索引就是聚集索引如果没有主键则第一个唯一索引unique作为聚集索引如果没有主键也没有合适的唯一索引则INnoDB会自动生成一个rowid作为隐藏的聚集索引最后我们来讲解一下到底什么是索引我们可以想象一下如果我们没有创建索引也就是只有一张表那么我们查找某些数据的时候就是全局扫描查找效率很慢。但是如果我们对一张表创建了索引的话索引就是一种数据结构一种算法来帮助我们快速检索提高查找速率。索引语法创建索引create [unique/fulltext] index 索引名称 on 表名字段1...;删除索引drop index 索引名称 on 表名查看索引show create index from 表名;删除索引drop index 索引名称 on 表名;索引-性能分析SQL执行频率show [global/session] status like com_______(七个下划线);慢查询日志查询慢查询日志是否开启show variables like slow_query_log;开启MySQL慢查询日志show_slow_log1设置慢日志的时间为2秒如果查询时间超过2秒则会被记录long_query_time2profile详情show profile能够帮助我们在做SQL优化的时候时间都消耗在哪里通过have_profile参数能够查看当前MySQL是否支持profile参数select have_profilingselect profiling0表示关闭1表示开启默认的profiling是关闭的我们可以通过set语句在[global/session]级别开启profilingset 【global/session】 profiling1查看每一条SQL语句的耗时情况show profiles查看指定的query_id在各个阶段的耗时情况show profile for query query_id查看指定的query_id 在cpu的使用情况show profile cpu for query query_idexplain执行计划explain/desc可以详细查看select的执行语句包括如何连接和连接顺序直接在select语句前加上explain执行语句索引的使用最左前缀法则如果使用了联合索引就要遵守最左前缀法则即查询从索引的最左侧的字段开始并且不跳过某一列如果跳过了则会部分索引失效后面的字段索引失效注意最左侧的列必须存在如果不存在则全部的索引失效不能在索引列上进行运算操作 否则索引将失效字符串索引使用时不加引号索引失效联合索引中出现范围查询(,)范围查询右侧的列索引失效。所以在业务允许的情况下尽可能的使用类似于或这类的范围查询而避免使用或尾部模糊查询索引不会失效首部模糊查询索引会失效如果用or的连接条件只要有一侧没有索引则整个索引都会失效数据评估影响如果MySQL优化器在评估我们的sql语句时使用索引比全表更慢则不使用索引SQL提示是优化数据库的重要手段通过加入一些人为提示来达到SQL优化的目的user index 建议使用explain select * from 表名 use index(索引名称) where ...ignore index 不使用explain select * from 表名 ignore index(索引名称) where ...force index 强制使用explain select * from 表名 force index(索引名称) where ...覆盖索引在查询的时候尽量使用覆盖索引即查询的字段在索引都都可以找到避免使用*查询前缀索引当字段类型为字符串的时候有时候需要索引很长的字符串会让索引变得很大查询时浪费磁盘I/O资源。此时可以只取字符串的一部分作为前缀节省索引空间提高索引效率语法create index 索引名称 on 表名字段n;索引长度可根据选择性决定。选择性越高索引效率越高。唯一索引的选择性是1性能最好select count(distinct 字段 )/count(*) from 表名 ;select count(distinct substring(字段名起始位置截至位置))/count(*) from 表名;SQL优化插入数据1.批量插入2.手动提交事务3.主键顺序插入如果一次性插入大量数据使用insert语句效率会很低此时可以使用MySQL提供的load指令##客户端连接MySQL服务器时加上参数--local-infileMySQL 默认出于安全考虑禁止客户端读取本地文件并发送给服务器所以必须加这个参数才能用批量导入功能mysql--local-infile -u root -p##设置全局参数为1开启从本地读取数据的开关查看开关是否开启select local_infileset global local_infile1;##执行load指令将准备好的数据加载到表结构中load data local infile 要读取的文件地址 into table 表名 fileds terminated by 字段之间如何分割 lines terminated by 每行之间如何分割主键优化1.满足业务需求的情况下尽量降低主键的长度2.主键顺序插入选择auto_increment自增3.尽量不要使用UUID不重复的无序的随机字符串或者其他作为主键比如身份证号4.业务操作时避免对主键的修改order by优化using filesort:不是利用索引排序的而且在缓冲区sort_buffer中排序然后再返回using index:利用索引本身的有序性1.根据排序字段建立合适的索引多字段排序时也需要遵循最左前缀法则2.尽量使用覆盖索引3.多字段排序一个升序一个降序此时需要注意联合索引在创建的时候的规则4.如果不可避免出现filesort,大量数据排序时可以适当增加缓冲区大小group by优化1.在分组时可以建立索引来提高效率2.分组操作时索引的使用也满足最左前缀法则limit优化如果查询分页的数据特别大时比如limit 2000000,10,此时MySQL会优先对前200000条数据进行扫描丢弃然后返回第20000001到第20000010条记录查询排序的代价非常大优化思路覆盖索引子查询count优化按照效率排序count(字段)count(主键)count(1)count(*)update优化InnoDB引擎针对索引加的是行锁如果更新的字段没有加索引则会升级为表锁把整张表都锁住。一旦升级为表锁则并发性能会降低视图视图是一种虚拟存在的表行和列数据来自于创建视图时所查询的表并且是在使用视图时动态生成的。创建create view 视图名称列名列表 as select语句 【with (cascaded/local) check option】查询视图查看创建视图语句show create view 视图名称;查看试图数据select * from 视图名称;修改视图alter view 视图名称列名列表 as select语句 【with (cascaded/local) check option】;replace view 视图名称列名列表 as select语句 【with (cascaded/local) check option】;删除视图drop view [if exists] 视图名称;视图-检查选项with check option限制通过视图插入 / 更新的数据必须满足视图的WHERE条件防止插入 / 更新后数据 “消失” 在视图中。cascaded级联MySQL 默认当视图基于其他视图创建时会检查本次以及所有底层视图的 WHERE 条件当前视图 所有依赖的视图。无with check option即无 cascaded不做任何条件检查可插入 / 更新不符合视图条件的数据插入后数据不会出现在视图中但会写入基表。关键规则即使某个视图没有with check option但如果它基于的上游视图有check option那么上游视图的检查规则仍然会生效。向视图中插入数据由于视图是不存在的是基于基表创建的所以实际是向基表中插入数据1.cascaded(级联的)2.global不会将这个检查效果传递给其他视图视图更新及作用视图的更新要是视图可以更新必须使视图中的行与基础表中的行存在一对一的关系视图的作用简单简化用户操作。那些经常被使用的操作可以定义为视图从而用户不必为以后的操作指定全部的条件安全通过视图用户只能查看和修改用户权限所能看到的数据数据独立视图可以帮助用户屏蔽基础表的变化锁锁的本质锁是mysql为了解决并发访问数据冲突而设计的机制锁的分类按照锁的粒度分全局锁每次锁定数据库中所有的表表级锁只锁定一张表行级锁只锁定一行数据全局锁全局锁的主要用途是全库逻辑备份比如用mysqldump导出全部数据目的是保证备份过程中数据的一致性。仅允许读阻塞所有写加锁flush tables with read lock备份数据mysqldump -uroot -p密码数据库名 所要导入的文件名.sql解锁unlock tables缺点全局业务锁定不能进行插入更新业务基本停摆代替在innodb引擎中我们可以添加参数single-transaction来完成无锁备份(MVCC(多版本并发控制)一致性快照)mysqldump --single-transaction -uroot -p密码数据库名 所要导入的文件名.sql表级锁发生锁冲突的概率最大并发度最低应用在myisam,innodb中分类:表锁表共享读锁read lock只能读不能写表独占写锁(write lock)只有自己可以读和写其他人不能读写语法加锁lock tables 表名...read/write解锁unlock tables /断开客户端连接元数据锁meta data lock MDL隐藏的表级锁MySQL 5.5 默认自动加无需手动操作用于保护表结构防止读数据时表结构被修改触发场景执行select/insert/update/delete时自动加 MDL 读锁(注意与全局锁的读锁区分)执行altertable/drop table时自动加 MDL 写锁注意与全局锁的写锁区分核心特点MDL 读锁之间不互斥但 MDL 写锁会阻塞所有 MDL 读锁比如改表结构时所有读 / 写操作都会被阻塞。意向锁┌─────────────────────────────────────────────────────────┐ 核心特点 │ ├─────────────────────────────────────────────────────────┤ ✅ 表级锁锁的是整个表但目的是声明行锁意图 ││ ✅ 不与行级锁冲突这是关键 │✅ 自动加锁无需手动干预 ││ ✅ 提高锁冲突判断效率 │ └─────────────────────────────────────────────────────────┘主要解决表锁和行锁的冲突问题意向共享锁(IS):与表级共享锁read兼容与表级排他锁write互斥意向排他锁(IX):与表级共享锁read和排他锁write都互斥行锁每次操作锁住对应行的数据锁粒度最低并发程度最高发生锁冲突概率最低。innodb专属innodb的数据是基于索引所致的行锁是对索引项加锁而不是对记录加锁分类行锁锁定单个行记录防止其他事务进行update和delete在repeatable read和repeatable comment隔离级别支持1.共享锁S允许一个读不允许排他锁2.排他锁X不允许共享锁和排他锁innodb引擎的行锁是对索引加的锁如果没有索引则innodb会升级为表锁间隙锁锁定索引之间的间隙防止其他事务在这个间隙之间insert产生幻读。在repeatable read的隔离级别下支持临键锁行锁和间隙锁的组合。在RR隔离级别下支持InnoDB引擎逻辑存储结构架构缓冲池Buffer Pool是什么缓冲池就是内存里的一块“临时仓库”。它用来存放磁盘上的真实数据这样当你读、改、删数据时不必每次都去慢慢读硬盘。操作顺序先看缓冲池里有没有数据。如果没有就从磁盘读入并缓存。然后再按一定频率把修改后的数据刷回磁盘。好处减少磁盘操作提高处理速度。2. 缓冲池是怎么管理数据的缓冲池按页Page来管理数据每页是固定大小的一块数据。每页可以是三种状态空闲页free page这页没用过可以直接存新数据。干净页clean page这页已经用过但是数据没有改过和磁盘上的数据是一致的。脏页dirty page这页被修改过了内存里的数据和磁盘上的不一致需要以后刷新回磁盘。3. 图中几个部分Buffer Pool缓冲池存放数据库数据的内存区域。Change Buffer变化缓冲区暂存一些修改操作。Log Buffer日记缓冲区记录数据变化保证数据安全。简单说缓冲池就像内存里的小仓库帮你快速操作数据减少慢慢访问硬盘的次数。不同的页状态就是为了知道哪些数据可以直接用哪些需要更新到硬盘。事务原理事务是一组操作的集合是不可分割的单位。这些操作要么同时成功要么同时失败四大特性原子性 隔离性 持久性 一致性redo log:重做日志。记录用户修改数据的记录用来实现事务的持久性该日志文件由两部分组成重做日志缓冲red0 log buffer重做日志文件(redo log file)。前者储存在内存中后者储存在磁盘中undo log:回滚日志用来记录数据修改之前的信息。作用包含两个提供回滚和MVCC多版本并发控制MVCC多版本并发控制
测试开发-MySQL学习
发布时间：2026/7/9 13:40:49
提示适用于想要学习测开方向的MySQL知识的学习完全够用SQL语句分类DDL数据定义语言用来定义数据库对象库表字段DML数据操作语言增删改DQL数据查询语言查询数据库中表中的记录DCL数据控制语言创建数据库用户控制用户访问权限查询字段去除重复数据selectdistinct字段 from 表名数值函数ceil(x) 向上取整floor(x) 向下取整mod(x,y) 返回x/y的模rand() 生成0-1之间的随机数round(x,y) 求参数四舍五入的值保留y位小数字符串函数字符串截取之substring_index(str,delim,count)str:要处理的字符串delim分隔符count:计数日期函数curdate() 获取当前的年月日curtime() 获取当前的时分秒now() 获取当前的年月日-时分秒year(date) 获取指定date的年份month(date) 获取指定date的月份day(date) 获取指定date的日数date_add(date,interval expr type) 在指定的date上增加一个时间间隔expr后的时间值ytpe单位年月日datediff(date1,date2) 返回两个日期之间相差的天数流程控制函数if(value,t,f) 如果value为真则输出t否则fifnull(value1,value2) 若value1不为空返回value1否则返回value2case when [val] then [1] else [2] end 若val为真则返回1否侧返回2case [expr] when [val1] then [1] else [2]约束非空约束 not null 限制阻断数据不能为null唯一约束 unique 保证字段的数据都唯一不重复主键约束 primary key 配用auto_increment 一行数据的唯一标识非空且唯一外键约束 foreign key 用来让两张表之间建立连接抱持数据的完整性和一致性这里需要注意的是有具有外键的表为子表外键所关联的表为父表添加外键1.create table 表名 字段1 类型 constrant [外键名称] foreign kry (外键字段名) references 主表字段2.alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表关联字段删除外键alter table 表名 drop foreign key 外键名称删除/更新行为alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表关联字段默认约束 default 保存数据时如果未指定字段值的值则用默认值检查约束 check 抱持字段满足某一个条件多表查询一对多多对一例如员工和部门之间的关系解决方案添加外键关联多对多例如学生和选课之间的关系添加中间表对应一对一例如个人基本信息和个人其他信息的关系多表查询分类连接查询内连接查询表A,B的交集部分隐式内连接select 字段列表 from 表1表2 where 条件显示内连接select 字段列表 from 表1 [inner] join 表2 on 条件外连接左外连接select 字段列表 from 表1left[outer] join 表2 on 条件查询的是表1和表2共同的数据以及表1的所有数据右外连接select 字段列表 from 表1right[outer] join 表2 on 条件查询的是表1和表2共同的数据以及表2的所有数据自连接当前表与自身连接查询自连接必须用别名select 字段列表 from 表1 别名1 join 表1 别名2 on 条件;自连接可以是内自连接也可以是外自连接连结查询select 字段列表 from 表Aunion [all]select 字段列表 from 表Bunion all / unionunion all 数据整合不去重union 去重注意连结查询的多张表必须字段列数抱持一致字段类型也抱持一致。子查询标量子查询子查询的结果为的单行单列单个值常见的操作符为 ,, ,,,列子查询子查询的结果为一列可以是多行常见的操作符为in,not in,anysome(子查询返回的列表中有一个满足即可),all(子查询返回的列表中必须所有的满足)行子查询子查询的结果为一行可以是都多列常见的操作符为,in not in表子查询子查询的结果为多行多列常见的操作符为in---------------------------------------------------------------------------------------------------------------------------------这里我们重点讲解一下分组查询group by可能在进行多表连结完之后再进行分组完之后,对于初学者来说可能会犯蒙并且只有select指定的字段时才能查看其图但是我们对它原本分组之后的构造却不清晰这里我们来讲解一下。首先第一点分组之后select的字段的条件1.必须是group by 分组字段2.聚合函数count/avg/distinct这一点很重要查询其他的字段则会报错第二点这里我们举例子讲解用户信息表 user_profiledevice_iduniversity2138北京大学3214复旦大学6543北京大学2315浙江大学5432山东大学2131山东大学4321复旦大学答题情况明细表question_practice_detail其中question_id是题目编号result是答题结果。device_idquestion_id21381113214112321411365431112315115231511623151175432118543211221311145432113要求取出学校答过题的用户平均答题数量情况数据1.首先进行内连接之后再分组select from user_profile as us ,question_practice_detail qpd where us.device_idqpd.device_id group by university;那么上方分组后的逻辑到底是怎样的呢其实分组之后你不取select特定的字段它不算一个表而是四个独立的组它只是一个逻辑分组名称university组内包含的所有原始行共 11 行按学校拆分组内关键统计为后续计算用北京大学行 12138、北京大学、111行 26543、北京大学、111总行数 2去重 device_id 数 2复旦大学行 33214、复旦大学、112行 43214、复旦大学、113总行数 2去重 device_id 数 1浙江大学行 52315、浙江大学、115行 62315、浙江大学、116行 72315、浙江大学、117总行数 3去重 device_id 数 1山东大学行 85432、山东大学、118行 95432、山东大学、112行 102131、山东大学、114行 115432、山东大学、113总行数 4去重 device_id 数 2 核心理解分组后不存在 “新的行”而是把原始行 “归类到不同的组里”每个组的边界是university组内是该学校的所有答题记录数据库执行group by后会 “以组为单位” 处理数据比如用聚合函数统计每组的总行数、去重用户数。事务简介事务是一组操作的集合是一个不可分割的单位。它可以把一系列操作作为一个整体向系统发起提交或者撤回。要么同时成功要么同时失败。注意默认的MySQL是自动提交的也就是说当执行一条DML语句MySQL会隐式提交。事务的操作查看事务的提交方式select autocommit;设置事务的提交方式set autocommit0(手动提交提交是需要commit;)此处需要注意的是一但开启手动提交则当前会话的所有的提交都为手动提交若只想开启一个临时的事务块则start transaction;或者begin ---仅对事务开启后commit/rollback之前的语句有效回滚rollback;事务的四大特性原子性事务是不可分割的最小操作单元要么全部成功要么全部失败一致性事务完成时必须使所有数据保持一致状态隔离性数据库事务的隔离机制保证事务不受外部并发操作的影响持久性一旦提交或者回滚对数据的操作是永久的并发事务的问题脏读一个事务读取到另一个事务还没有提交的数据不可重复读一个事务先后读取同一条数据但是两次的数据不一样幻读一个事务内多次执行同一范围查询但两次查询之间另一个事务提交了插入 / 删除操作导致查询结果集的行数变化核心是 “插入 / 删除” 操作引发事务隔离级别隔离级别脏读不可重复读幻读并发性能适用场景READ UNCOMMITTED读未提交✅✅✅最高几乎不用仅测试READ COMMITTED读已提交❌✅✅较高多数 OLTP 场景Oracle 默认REPEATABLE READ可重复读❌❌✅中等MySQL 默认推荐SERIALIZABLE串行化❌❌❌最低数据一致性要求极高的场景READ UNCOMMITTED会出现脏读问题事务A会读取事务B未commit的数据READ COMMITTED读已提交解决事务的脏读问题。但是会出现不可重复读问题。同样一个SQL在同一个事务中查询不一致。REPEATABLE READ:解决不可重复读问题。即使事务B对数据库做出操作但是事务A两次查询结果都一样,只有事务A最后commit才最终修改。但是解决不了幻读问题。serializable串行化解决幻读问题。强行阻塞一个事务A的运行只有等事务B commit之后才显示SQL结果-- 查看事务的隔离级别select transaction_isolation-- 设置事务的隔离级别set [session/global] transaction isolation level [read uncommited/read commited/repeatable read/serializable]存储引擎MySQL体系结构客户端连接器是什么用户或程序连接MySQL的“桥梁”比如Python/PHP/Java代码、Navicat工具、甚至Excel插件。类比你用“图书馆APP”“实体借书卡”“微信小程序”借书——不同工具对应不同的“连接方式”图中的Native C API、JDBC、ODBC、.NET、PHP、Python等。作用把用户的请求比如“查用户表”转换成MySQL能理解的格式传给下一层。连接层包含认证、线程复用、连接限制、内存检查、缓存认证验证“读者身份”——比如你用MySQL账号root/123456登录连接池会查“用户表”确认密码对不对。线程复用避免“每个请求都开新线程”——比如100个读者同时查书接待处不会开100个新员工而是用“复用线程”比如5个员工轮流处理节省资源。连接限制控制“同时进馆人数”——比如MySQL设置max_connections100超过100人就排队防止服务器崩溃。缓存存“常用信息”——比如“热门查询的结果”“用户权限”不用每次都查数据库。服务层这是MySQL的“大脑”处理所有请求的核心逻辑包含4个模块① SQL接口“接收读者的问题”——处理所有SQL语句比如SELECT/INSERT/UPDATE、存储过程、视图、触发器。② 解析器“翻译权限检查”——把你的SQL翻译成MySQL内部指令比如SELECT * FROM user→“查user表的所有列”还要检查你有没有权限比如“普通用户不能查管理员表”。③ 查询优化器“找最快的找书路线”——比如你要查“年龄18的用户”优化器会选“用年龄索引”还是“全表扫描”就像导航选“地铁”还是“打车”目标是最快返回结果。④ 缓存“常用结果暂存”——如果100个人都查“热门书籍”缓存会把结果存起来第101个人查的时候直接返回不用再查书库。引擎层可插拔存储引擎是MySQL最核心的“数据存储层”包含InnoDB、MyISAM、Memory等引擎是什么不同的“书库管理方式”——比如InnoDB支持事务比如“买东西时扣钱和加库存必须同时成功/失败”、行锁改一行数据时不影响其他行是现在的默认引擎比如淘宝、微信的用户数据都用它。MyISAM不支持事务但查询快比如以前的论坛帖子主要是查不用改但崩溃后容易丢数据。Memory数据存在内存里比如临时统计数据速度极快但重启MySQL就没了。关键特点可插拔——你可以随时把“文学书库”换成“工具书库”比如把表引擎从InnoDB改成MyISAM不用改上面的服务层。存储层包含系统文件和文件日志是数据的“物理存储”系统文件比如NTFS、ext2/3文件系统Windows/Linux的磁盘格式就像图书馆的“书架材质”——用什么方式存文件。文件日志记录所有操作的“历史账本”包括Redo日志重做日志比如你改了一条数据先写Redo日志再写数据文件——万一断电用Redo恢复数据就像图书馆的“操作记录”万一书被烧了能重新买一本。Undo日志回滚日志比如你执行UPDATE没提交想撤销——用Undo恢复到之前的状态就像“撤销键”。Binary日志二进制日志记录所有“修改数据的操作”比如INSERT/UPDATE/DELETE用来做主从复制主库的日志传给从库从库重放操作保持数据一致就像图书馆的“副本账本”。用“查用户数据”的场景串起全流程现在你用Python程序查“id1的用户姓名”整个过程是这样的客户端连接器Python通过JDBC连接MySQL就像用APP连图书馆。连接层连接池验证Python的账号密码认证分配一个线程接待员检查连接数没超限制。服务层SQL接口接收请求SELECT name FROM user WHERE id1。解析器翻译SQL检查你有没有查user表的权限。查询优化器决定用id的索引如果有的话比全表扫描快。缓存检查有没有人之前查过这个id如果有直接返回缓存结果。引擎层如果缓存没有InnoDB引擎去存储层找id1的记录就像去书库找书。存储层从磁盘文件里读出数据返回给引擎层再传给服务层最后给Python程序。存储引擎简介创建表的时候指定存储引擎create table 表名字段1类型【注释】...engineinnodb [注释]查看数据库支持的引擎show engines;InnoDB存储引擎特点支持事务ACID行级锁提高并发访问性能外键抱持数据的完整性一致性文件xxx.ibd(xxx为表名)MySIAM存储引擎MySQL早期默认存储引擎特点不支持事务不支持行级锁支持表锁没有外键访问速度快文件xxx.sdi:存储表结构信息xxx.MYD:存储数据xxx.MYI:存储索引Memory存储引擎表数据存储在内存中受硬件断电影响只能将这些表作为临时表或者缓存使用特点内存存放hash索引文件xxx.sdi:存储表结构信息索引帮助MySQL高效获取数据的数据结构优点提高查询效率排序效率索引结构BTree索引最常见的索引大部分引擎都支持B树索引hash索引底层数据结构使用hash表实现的只有精确匹配索引列的查询有效不支持范围查询between ,,,...R-Tree索引(空间索引):MySIAM引擎支持的特殊索引类型主要用于地理空间数据类型使用叫较少Full-text索引全文索引)是一种建立倒排索引快速匹配文档的方式索引的分类按照【物理存储】分类InnoDB专属聚集索引必须有且只能有一个将数据存储和索引放到一块索引结构的叶子节点保留了行数据二级索引可以存在多个将索引和数据分开存储索引结构的叶子节点关联的对应的主键聚集索引的选取规则如果存在主键则主键索引就是聚集索引如果没有主键则第一个唯一索引unique作为聚集索引如果没有主键也没有合适的唯一索引则INnoDB会自动生成一个rowid作为隐藏的聚集索引最后我们来讲解一下到底什么是索引我们可以想象一下如果我们没有创建索引也就是只有一张表那么我们查找某些数据的时候就是全局扫描查找效率很慢。但是如果我们对一张表创建了索引的话索引就是一种数据结构一种算法来帮助我们快速检索提高查找速率。索引语法创建索引create [unique/fulltext] index 索引名称 on 表名字段1...;删除索引drop index 索引名称 on 表名查看索引show create index from 表名;删除索引drop index 索引名称 on 表名;索引-性能分析SQL执行频率show [global/session] status like com_______(七个下划线);慢查询日志查询慢查询日志是否开启show variables like slow_query_log;开启MySQL慢查询日志show_slow_log1设置慢日志的时间为2秒如果查询时间超过2秒则会被记录long_query_time2profile详情show profile能够帮助我们在做SQL优化的时候时间都消耗在哪里通过have_profile参数能够查看当前MySQL是否支持profile参数select have_profilingselect profiling0表示关闭1表示开启默认的profiling是关闭的我们可以通过set语句在[global/session]级别开启profilingset 【global/session】 profiling1查看每一条SQL语句的耗时情况show profiles查看指定的query_id在各个阶段的耗时情况show profile for query query_id查看指定的query_id 在cpu的使用情况show profile cpu for query query_idexplain执行计划explain/desc可以详细查看select的执行语句包括如何连接和连接顺序直接在select语句前加上explain执行语句索引的使用最左前缀法则如果使用了联合索引就要遵守最左前缀法则即查询从索引的最左侧的字段开始并且不跳过某一列如果跳过了则会部分索引失效后面的字段索引失效注意最左侧的列必须存在如果不存在则全部的索引失效不能在索引列上进行运算操作 否则索引将失效字符串索引使用时不加引号索引失效联合索引中出现范围查询(,)范围查询右侧的列索引失效。所以在业务允许的情况下尽可能的使用类似于或这类的范围查询而避免使用或尾部模糊查询索引不会失效首部模糊查询索引会失效如果用or的连接条件只要有一侧没有索引则整个索引都会失效数据评估影响如果MySQL优化器在评估我们的sql语句时使用索引比全表更慢则不使用索引SQL提示是优化数据库的重要手段通过加入一些人为提示来达到SQL优化的目的user index 建议使用explain select * from 表名 use index(索引名称) where ...ignore index 不使用explain select * from 表名 ignore index(索引名称) where ...force index 强制使用explain select * from 表名 force index(索引名称) where ...覆盖索引在查询的时候尽量使用覆盖索引即查询的字段在索引都都可以找到避免使用*查询前缀索引当字段类型为字符串的时候有时候需要索引很长的字符串会让索引变得很大查询时浪费磁盘I/O资源。此时可以只取字符串的一部分作为前缀节省索引空间提高索引效率语法create index 索引名称 on 表名字段n;索引长度可根据选择性决定。选择性越高索引效率越高。唯一索引的选择性是1性能最好select count(distinct 字段 )/count(*) from 表名 ;select count(distinct substring(字段名起始位置截至位置))/count(*) from 表名;SQL优化插入数据1.批量插入2.手动提交事务3.主键顺序插入如果一次性插入大量数据使用insert语句效率会很低此时可以使用MySQL提供的load指令##客户端连接MySQL服务器时加上参数--local-infileMySQL 默认出于安全考虑禁止客户端读取本地文件并发送给服务器所以必须加这个参数才能用批量导入功能mysql--local-infile -u root -p##设置全局参数为1开启从本地读取数据的开关查看开关是否开启select local_infileset global local_infile1;##执行load指令将准备好的数据加载到表结构中load data local infile 要读取的文件地址 into table 表名 fileds terminated by 字段之间如何分割 lines terminated by 每行之间如何分割主键优化1.满足业务需求的情况下尽量降低主键的长度2.主键顺序插入选择auto_increment自增3.尽量不要使用UUID不重复的无序的随机字符串或者其他作为主键比如身份证号4.业务操作时避免对主键的修改order by优化using filesort:不是利用索引排序的而且在缓冲区sort_buffer中排序然后再返回using index:利用索引本身的有序性1.根据排序字段建立合适的索引多字段排序时也需要遵循最左前缀法则2.尽量使用覆盖索引3.多字段排序一个升序一个降序此时需要注意联合索引在创建的时候的规则4.如果不可避免出现filesort,大量数据排序时可以适当增加缓冲区大小group by优化1.在分组时可以建立索引来提高效率2.分组操作时索引的使用也满足最左前缀法则limit优化如果查询分页的数据特别大时比如limit 2000000,10,此时MySQL会优先对前200000条数据进行扫描丢弃然后返回第20000001到第20000010条记录查询排序的代价非常大优化思路覆盖索引子查询count优化按照效率排序count(字段)count(主键)count(1)count(*)update优化InnoDB引擎针对索引加的是行锁如果更新的字段没有加索引则会升级为表锁把整张表都锁住。一旦升级为表锁则并发性能会降低视图视图是一种虚拟存在的表行和列数据来自于创建视图时所查询的表并且是在使用视图时动态生成的。创建create view 视图名称列名列表 as select语句 【with (cascaded/local) check option】查询视图查看创建视图语句show create view 视图名称;查看试图数据select * from 视图名称;修改视图alter view 视图名称列名列表 as select语句 【with (cascaded/local) check option】;replace view 视图名称列名列表 as select语句 【with (cascaded/local) check option】;删除视图drop view [if exists] 视图名称;视图-检查选项with check option限制通过视图插入 / 更新的数据必须满足视图的WHERE条件防止插入 / 更新后数据 “消失” 在视图中。cascaded级联MySQL 默认当视图基于其他视图创建时会检查本次以及所有底层视图的 WHERE 条件当前视图 所有依赖的视图。无with check option即无 cascaded不做任何条件检查可插入 / 更新不符合视图条件的数据插入后数据不会出现在视图中但会写入基表。关键规则即使某个视图没有with check option但如果它基于的上游视图有check option那么上游视图的检查规则仍然会生效。向视图中插入数据由于视图是不存在的是基于基表创建的所以实际是向基表中插入数据1.cascaded(级联的)2.global不会将这个检查效果传递给其他视图视图更新及作用视图的更新要是视图可以更新必须使视图中的行与基础表中的行存在一对一的关系视图的作用简单简化用户操作。那些经常被使用的操作可以定义为视图从而用户不必为以后的操作指定全部的条件安全通过视图用户只能查看和修改用户权限所能看到的数据数据独立视图可以帮助用户屏蔽基础表的变化锁锁的本质锁是mysql为了解决并发访问数据冲突而设计的机制锁的分类按照锁的粒度分全局锁每次锁定数据库中所有的表表级锁只锁定一张表行级锁只锁定一行数据全局锁全局锁的主要用途是全库逻辑备份比如用mysqldump导出全部数据目的是保证备份过程中数据的一致性。仅允许读阻塞所有写加锁flush tables with read lock备份数据mysqldump -uroot -p密码数据库名 所要导入的文件名.sql解锁unlock tables缺点全局业务锁定不能进行插入更新业务基本停摆代替在innodb引擎中我们可以添加参数single-transaction来完成无锁备份(MVCC(多版本并发控制)一致性快照)mysqldump --single-transaction -uroot -p密码数据库名 所要导入的文件名.sql表级锁发生锁冲突的概率最大并发度最低应用在myisam,innodb中分类:表锁表共享读锁read lock只能读不能写表独占写锁(write lock)只有自己可以读和写其他人不能读写语法加锁lock tables 表名...read/write解锁unlock tables /断开客户端连接元数据锁meta data lock MDL隐藏的表级锁MySQL 5.5 默认自动加无需手动操作用于保护表结构防止读数据时表结构被修改触发场景执行select/insert/update/delete时自动加 MDL 读锁(注意与全局锁的读锁区分)执行altertable/drop table时自动加 MDL 写锁注意与全局锁的写锁区分核心特点MDL 读锁之间不互斥但 MDL 写锁会阻塞所有 MDL 读锁比如改表结构时所有读 / 写操作都会被阻塞。意向锁┌─────────────────────────────────────────────────────────┐ 核心特点 │ ├─────────────────────────────────────────────────────────┤ ✅ 表级锁锁的是整个表但目的是声明行锁意图 ││ ✅ 不与行级锁冲突这是关键 │✅ 自动加锁无需手动干预 ││ ✅ 提高锁冲突判断效率 │ └─────────────────────────────────────────────────────────┘主要解决表锁和行锁的冲突问题意向共享锁(IS):与表级共享锁read兼容与表级排他锁write互斥意向排他锁(IX):与表级共享锁read和排他锁write都互斥行锁每次操作锁住对应行的数据锁粒度最低并发程度最高发生锁冲突概率最低。innodb专属innodb的数据是基于索引所致的行锁是对索引项加锁而不是对记录加锁分类行锁锁定单个行记录防止其他事务进行update和delete在repeatable read和repeatable comment隔离级别支持1.共享锁S允许一个读不允许排他锁2.排他锁X不允许共享锁和排他锁innodb引擎的行锁是对索引加的锁如果没有索引则innodb会升级为表锁间隙锁锁定索引之间的间隙防止其他事务在这个间隙之间insert产生幻读。在repeatable read的隔离级别下支持临键锁行锁和间隙锁的组合。在RR隔离级别下支持InnoDB引擎逻辑存储结构架构缓冲池Buffer Pool是什么缓冲池就是内存里的一块“临时仓库”。它用来存放磁盘上的真实数据这样当你读、改、删数据时不必每次都去慢慢读硬盘。操作顺序先看缓冲池里有没有数据。如果没有就从磁盘读入并缓存。然后再按一定频率把修改后的数据刷回磁盘。好处减少磁盘操作提高处理速度。2. 缓冲池是怎么管理数据的缓冲池按页Page来管理数据每页是固定大小的一块数据。每页可以是三种状态空闲页free page这页没用过可以直接存新数据。干净页clean page这页已经用过但是数据没有改过和磁盘上的数据是一致的。脏页dirty page这页被修改过了内存里的数据和磁盘上的不一致需要以后刷新回磁盘。3. 图中几个部分Buffer Pool缓冲池存放数据库数据的内存区域。Change Buffer变化缓冲区暂存一些修改操作。Log Buffer日记缓冲区记录数据变化保证数据安全。简单说缓冲池就像内存里的小仓库帮你快速操作数据减少慢慢访问硬盘的次数。不同的页状态就是为了知道哪些数据可以直接用哪些需要更新到硬盘。事务原理事务是一组操作的集合是不可分割的单位。这些操作要么同时成功要么同时失败四大特性原子性 隔离性 持久性 一致性redo log:重做日志。记录用户修改数据的记录用来实现事务的持久性该日志文件由两部分组成重做日志缓冲red0 log buffer重做日志文件(redo log file)。前者储存在内存中后者储存在磁盘中undo log:回滚日志用来记录数据修改之前的信息。作用包含两个提供回滚和MVCC多版本并发控制MVCC多版本并发控制
