Mysql进阶篇

https://blog.csdn.net/2401_82607598/article/details/159435505?fromshareblogdetailsharetypeblogdetailsharerId159435505sharereferPCsharesource2401_82607598sharefromfrom_link以上是Mysql基础。也可以说是SQL语句基础。SQLDDL数据定义语言用于操作数据库、表、列等有create、alter、drop。。DML数据操作语言用于操作数据库中表的数据有insert、update、delete。。DCL数据控制语言用于操作访问权限和安全级别有grantdeny。。DQL数据查询语言用于查询数据select数据类型整数类型Tinyint、smallint、mediumint、int、bigint浮点数类型和定点数类型float、double、decimal字符串类型varchar、char、text日期和时间类型二进制类型基本操作创建数据库create database 数据库名称;查看数据库基本信息show create database 数据库名称;删除数据库drop database 数据库;查询所有数据库show databases;将数据库的字符集改成gbkalter database 数据库 character set gbk;切换数据库use 数据库;查看当前使用的数据库select database();数据表数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作否则会抛出“No database selected”错误。 语法如下create table 表名( 字段1 字段类型, 字段2 字段类型, … 字段n 字段类型 );如create table student( id int, name varchar(20), gender varchar(10), birthday date );查看数据表查看数据库里有哪些数据表show tables;查看表的字段信息desc student;修改数据表有时希望对表中的某些信息进行修改例如修改表名、修改字段名、修改字段 数据类型…等等。在MySQL中使用alter table修改数据表.修改表名alter table student rename to stu;修改字段名alter table stu change name sname varchar(10);修改字段数据类型alter table stu modify sname int;增加字段alter table stu add address varchar(50);删除字段alter table stu drop address;删除数据表drop table 表名;数据表的约束为防止错误的数据被插入到数据表MySQL中定义了一些维护数据库完整性的规则这些规则常称为表的约束。主键约束主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号并且这个身份证号是唯一的。create table student( id int primary key, name varchar(20) ); create table student01( id int name varchar(20), primary key(id) );s非空约束非空约束即 NOT NULL指的是字段的值不能为空基本的语法格式如下所示create table student02( id int name varchar(20) not null );默认值约束默认值约束即DEFAULT用于给数据表中的字段指定默认值即当在表中插入一条新记录时若未给该字段赋值那么数据库系统会自动为这个字段插入默认值create table student03( id int, name varchar(20), gender varchar(10) default male );s唯一性约束唯一性约束即UNIQUE用于保证数据表中字段的唯一性即表中字段的值不能重复出现其基本的语法格式如下所示create table student04( id int, name varchar(20) unique );s外键约束外键约束即FOREIGN KEY常用于多张表之间的约束。create table student05( id int primary key, name varchar(20) );s数据一致性概念大家知道建立外键是为了保证数据的完整和统一性。但是如果主表中的数据被删除或修改从表中对应的数据该怎么办呢很明显从表中对应的数据也应该被删除否则数据库中会存在很多无意义的垃圾数据。删除外键alter table 从表名 drop foreign key 外键名关于外键约束需要注意的细节1、从表里的外键通常为主表的主键2、从表里外键的数据类型必须与主表中主键的数据类型一致3、主表发生变化时应注意主表与从表的数据一致性问题数据表插入数据在MySQL通过INSERT语句向数据表中插入数据。create table student( id int, name varchar(30), age int, gender varchar(30) );为表中所有字段插入数据insert into student (id,name,age,gender) values (1,bob,16,male);为表中指定字段插入数据INSERT INTO 表名字段名1,字段名2,...) VALUES (值 1,值 2,...);同时插入多条记录insert into student (id,name,age,gender) values (2,lucy,17,female),(3,jack,19,male),(4,tom,18,male);更新数据UPDATE 表名 SET 字段名1值1[,字段名2 值2,…] [WHERE 条件表达式];将name为tom的记录的age设置为20并将其gender设置为female MySQL命令update student set age20,genderfemale where nametom;UPDATE更新全部数据update student set age18;删除数据DELETE FROM 表名 [WHERE 条件表达式];DELETE删除部分数据delete from student where age14;DELETE删除全部数据delete from student;。。。。以上是一些易忘的基础语句。。详细的havingroderby等等的运行顺序及使用记得复习。。以上没说记得补上存储引擎MySQL体系结构存储引擎介绍存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的而不是基于库的所以存储引擎也可被称为表类 型。我们可以在创建表的时候来指定选择的存储引擎如果没有指定将自动选择默认的存储引擎。InnoDB存储引擎特点InnoDB在 MySQL 5.5 之后InnoDB是默认的 MySQL 存储引擎。1. 特点DML操作遵循ACID模型支持事务行级锁提高并发访问性能支持外键FOREIGN KEY约束保证数据的完整性和正确性2. 文件xxx.ibdxxx是表名innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构frm-早期的 、sdi-新版的、数据和索引。show variables like innodb_file_per_table;3. 逻辑存储结构表空间 : InnoDB存储引擎逻辑结构的最高层ibd文件其实就是表空间文件在表空间中可以包含多个Segment段。段 : 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理都是引擎自身完成不需要人为对其控制一个段中包含多个区。区 : 区是表空间的单元结构每个区的大小为1M。 默认情况下 InnoDB存储引擎页大小为16K 即一个区中一共有64个连续的页。页 : 页是组成区的最小单元页也是InnoDB 存储引擎磁盘管理的最小单元即每次读取到内存的时候是读取一页的数据每个页的大小默认为 16KB。为了保证页的连续性InnoDB 存储引擎每次从磁盘申请 4-5 个区。行 : InnoDB 存储引擎是面向行的也就是说数据是按行进行存放的在每一行中除了定义表时所指定的字段以外还包含两个隐藏字段。MyISAMMyISAM是MySQL早期的默认存储引擎。1. 特点不支持事务不支持外键支持表锁不支持行锁访问速度快2. 文件xxx.sdi存储表结构信息xxx.MYD: 存储数据xxx.MYI: 存储索引MemoryMemory引擎的表数据时存储在内存中的由于受到硬件问题、或断电问题的影响只能将这些表作为临时表或缓存使用。1. 特点hash索引默认2. 文件xxx.sdi存储表结构信息面试题 InnoDB引擎与MyISAM引擎的区别 ?①. InnoDB引擎, 支持事务, 而MyISAM不支持。②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。主要是上述三点区别当然也可以从索引结构、存储限制等方面更加深入的回答具体参考如下官方文档 https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html存储引擎选择InnoDB: 是Mysql的默认存储引擎支持事务、外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。MyISAM 如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。MEMORY将所有数据保存在内存中访问速度快通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性。索引索引index是帮助 MySQL 高效获取数据的数据结构。数据库系统除了维护数据之外还维护着索引。索引指向着数据。索引结构MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的索引结构主要包含以下几种不同的存储引擎对于索引结构的支持情况。二叉树如果选择二叉树作为索引结构会存在以下缺点顺序插入时会形成一个链表查询性能大大降低。大数据量情况下层级较深检索速度慢。如果选择红黑树作为索引结构但在大数据量情况下层级较深检索速度慢。B-TreeB树是一个多叉的平衡搜索树。度数节点的子节点个数为5的B树B树的每个节点最多存储4个Key5个指针。特点例如5阶的B树每一个节点最多存储4个key对应5个指针一旦节点存储的key数量到达5就会裂变中间元素向上分裂。在B树中非叶子节点和叶子节点都会存放数据。BTreeBTree是B-Tree的变种我们以一颗最大度数max-degree为44阶的btree为例来看一下其结构示意图绿色框框起来的部分是索引部分仅仅起到索引数据的作用不存储数据。红色框框起来的部分是数据存储部分在其叶子节点中要存储具体的数据。BTree 与 B-Tree相比主要有以下三点区别所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用具体的数据都是在叶子节点存放的MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能利于排序。Hash MySQL中除了支持BTree索引还支持一种索引类型—Hash索引。哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决特点Hash索引只能用于对等比较(in)不支持范围查询between …无法利用索引完成排序操作查询效率高通常(不存在hash冲突的情况)只需要一次检索就可以了效率通常要高于Btree索引在MySQL中支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能hash索引是InnoDB存储引擎根据BTree索引在指定条件下自动构建的。面试题 为什么InnoDB存储引擎选择使用Btree索引结构相对于二叉树层级更少搜索效率高对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低。相对Hash索引Btree支持范围匹配及排序操作索引分类在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引。聚集索引二级索引而在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种聚集索引选取规则:如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。聚集索引和二级索引的具体结构如下聚集索引的叶子节点下挂的是这一行的数据 。二级索引的叶子节点下挂的是该字段值对应的主键值。回表查询 先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取数据的方式就称之为回表查询思考题:1、 以下两条SQL语句那个执行效率高? 为什么A. select * from user where id 10 ; B. select * from user where name Arm; # id为主键name字段创建的有索引答A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引直接返回数据。 而B语句需要先查询name字段的二级索引然后再查询聚集索引也就是需要进行回表查询2、InnoDB主键索引的Btree高度为多高呢美团: Mysql数据库中表的一行数据大小为1k有五千万条记录主键是bigint类型求B树的高是多少答 数据库的指针固定是占6个字节主键是bigint类型的话是占8个字节 一页的大小是16k一页可以存储16个行。假设一页中有n个键值n 1个指针。n * 8 (n 1) * 6 1024 * 16 字节。求出n之后如果树的高度为2存储的总行数 (n 1) * 16行。如果树的高度为3存储的总行数 (n 1)^2 * 16。进而推出公式存储的总行数 (n 1)^树高 - 1 * 16。 然后将五千万带入求出树高。五千万条记录的话B树的高是4索引语法创建索引create [ unique | fulltext ] index index_name on table_name ( index_col_name,... );查看索引show index from table_name;删除索引drop index index_name on table_name;SQL性能分析SQL执行频率MySQL 客户端连接成功后通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT 、UPDATE、DELETE、SELECT的访问频次-- session 是查看当前会话 ;-- global 是查询全局数据 ; SHOW GLOBAL STATUS LIKE Com_______;那么通过查询SQL的执行频次我们就能够知道当前数据库到底是增删改为主还是查询为主。 那假如说是以查询为主我们又该如何定位针对于那些查询语句进行优化呢 次数我们可以借助于慢查询日志。慢查询日志慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。MySQL的慢查询日志默认没有开启我们可以查看一下系统变量slow_query_log。如果要开启慢查询日志需要在MySQL的配置文件C:\ProgramData\MySQL\MySQL Server 8.0的my.ini中配置如下信息# 开启MySQL慢日志查询开关 slow_query_log1 # 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志 long_query_time2配置完毕之后重新启动MySQL服务器。在慢查询日志中只会记录执行时间超多我们预设时间2s的SQL执行较快的SQL是不会记录的。那这样通过慢查询日志就可以定位出执行效率比较低的SQL从而有针对性的进行优化。profile详情show profiles 能够在做SQL优化时帮助我们了解SQL执行时间具体都耗费到哪里去了。explainexplain或者desc命令获取 MySQL 如何执行select语句的信息包括在select语句执行过程中表如何连接和连接的顺序。-- 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;explain 执行计划中各个字段的含义索引使用最左前缀法则如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。注意注意 最左前缀法则中指的最左边的列是指在查询时联合索引的最左边的字段(即是第一个字段) 必须存在与我们编写SQL时条件编写的先后顺序无关。范围查询联合索引中出现范围查询(,)范围查询右侧的列索引失效。在业务允许的情况下尽可能的使用类似于 或 这类的范围查询而避免使用 或 索引失效情况索引列计算不要在索引列上进行运算操作 索引将失效。explain select * from tb_user where substring(phone,10,2) 15; # phone索引失效字符串不加引号字符串类型字段使用时不加引号索引将失效。模糊查询如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。即在like模糊查询中在关键字后面加%索引可以生效。而如果在关键字前面加了%索引将会失效or连接条件用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。当or连接的条件左右两侧字段都有索引时索引才会生效。数据分布影响如果MySQL评估使用索引比全表更慢则不使用索引。SQL提示SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。use index建议MySQL使用哪一个索引完成此次查询仅仅是建议mysql内部还会再次进行评估explain select * from tb_user use index(idx_user_pro) where profession 软件工程;ignore index 忽略指定的索引。explain select * from tb_user ignore index(idx_user_pro) where profession 软件工程;force index 强制使用索引。explain select * from tb_user force index(idx_user_pro) where profession 软件工程;覆盖索引尽量使用覆盖索引减少select *。那么什么是覆盖索引呢 覆盖索引是指查询条件使用了索引并且需要返回的列在该索引中已经全部能够找到。接下来我们来看一组SQL的执行计划看看执行计划的差别然后再来具体做一个解析。explain select id, profession from tb_user where profession 软件工程 and age 31 and status 0 ; explain select id,profession,age, status from tb_user where profession 软件工程 and age 31 and status 0 ; explain select id,profession,age, status, name from tb_user where profession 软件工程 and age 31 and status 0; explain select * from tb_user where profession 软件工程 and age 31 and status 0;思考题 一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案: select id,username,password from tb_user where username ‘itcast’;答案: 针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);这样可以避免上述的SQL语句在查询的过程中出现回表查询。前缀索引当字段类型为字符串varchartextlongtext等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO 影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。语法create index idx_xxxx on table_name(column(n));单列索引与联合索引单列索引即一个索引只包含单个列。联合索引即一个索引包含了多个列。在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引。索引使用原则针对于数据量较大且查询比较频繁的表建立索引。针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。SQL优化锁MySQL中的锁按照锁的粒度分分为以下三类全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。InnoDB引擎