MySQL语法
1.drop、delete与truncate的区别
三者都表示删除，但是有一定区别
| delete | truncate | drop | |
|---|---|---|---|
| 类型 | DML | DDL | DDL |
| 回滚 | 可以回滚 | 不能 | 不能 |
| 删除内容 | 表结构还在，删除表中的部分或者全部数据 | 删除表中所有数据 | 从数据库中删除整张表，包括索引，权限 |
| 删除速度 | 删除速度慢，需要逐行删除 | 删除速度快 | 删除速度最快 |
| 所以，在不需要一张表的时候用drop,在删除表部分数据时候用delete,在删除表所有数据又要保留表的结构时，用truncate |
2.SQL 语言包括哪几部分？每部分都有哪些操作关键
SQL语言包括数据定义(DDL)，数据操纵(DML)，数据控制(DCL)和数据查询(DQL),事务控制TCL五个部分。
DQL数据查询语言：主要用于数据的查询 select
DML数据操作语言: 主要用于对数据库中数据进行增加，修改和删除 insert,delete,update
DDL数据定义语言: 主要针对数据库对象的增加，修改和删除操作 create,drop,alter
DCL数据控制语言: 用来授予和回收访问数据库的权限 grant,removke
TCL事务控制语言: 用于数据库的事务管理。 start transaction, commit, rollback, set transaction
3.mysql中UNION与UNION ALL的区别？
UNION会合并重复行，UNION ALL不会合并重复行，UNION ALL效率要高于UNION
4.mysql中char和varchar的区别
(1)char是定字符串，长度不能改变；varchar是可变字符串长度可以改变。
(2)如果插入数据长度小于char的固定长度用空格填充，varchar是插入数据多长就按多长来存储
(3)char因为长度固定，所以存取速度要比varchar快很多，但是也因长度固定，所以会占多余的空间，是空间换时间的做法。
varchar刚好跟char相反，因为长度不固定，不占据多余的存储空间，但是存取慢。
(4)对于char来说最多能存放255个字符数，varchar可以存放65532字符数。
总之char性能更高，varchar更节省磁盘存储空间，怎么选择具体情况具体分析。
5.varchar(50)中50的含义，int(20)中20的含义
varchar(50)中50表示最多存放的字符数，举个例子varchar(50)和varchar(200)存储‘hello’所占的空间是一样的，但是后者在排序时会更占用内存，因为排序采用fixed_length(固定长度)计算字段的长度。
int(20)中的20表示最大的显示宽度为20，而不是int的最大长度的11位。所以无论你设置多大的显示宽度，int存储大小都是4个字节。
只是当int字段类型设置零填充(ZEROFILL)时，如果数值位数未达到设置的显示宽度，数值位数前就会补0，这样易于报表的展示。
6.数据库的三大范式是什么
第一范式: 每个列不可再分(确保每列的原子性)
第二范式: 在第一范式的基础上，非主键列完全依赖于主键，不能是依赖于主键的一部分。(主要是针对联合主键)
第三范式: 在第二范式的基础上，表的非主键列只能依赖于主键，不能依赖其它非主键。（需要生成外键）
参考文章
MySQL存储引擎
7.mysql存储引擎MyISAM和InnoDB区别
(1)MyISAM支持表级锁，InnoDB既支持行级锁又支持表级锁,如果InnoDB没有索引就只能用表级锁
(2)MyISAM不支持事务，InnoDB支持事务
(3)MyISAM不支持外键，InnoDB支持外键
(4)MyISAM支持全文检索，MySql5.6以后Innodb也支持全文检索
(5)MyISAM的索引类型是非聚簇索引，Innodb是聚簇索引
(6)多数情况下MyISAM查询速度要比Innodb快，Innodb更适合大量的增删改操作
MySQL事务
8.事务的四大特性介绍一下
关系性数据库需要遵循ACID原则(原子性，一致性，隔离性，持久性)
原子性:事务是最小的执行单位，不允许再分割，事务的原子性要求动作要么全部完成，要么全部不起作用。
一致性:事务执行的结果必须是从一个一致性状态变到另一个一致性状态，一致性是通过原子性来实现的。
隔离性:并发访问数据库时，一个用户的事务不被其它事务操作数据所干扰，多个并发事务之间要相互独立。
持久性:一个事务被提交后，它对数据库中数据的改变是永久性的，即使数据库发生故障也不应该对其有影响。
9.什么是脏读?不可重复读?幻读？
脏读:某个事务更新了一份数据，另一个事务读取到这个数据，某些原因前一个事务回滚了，那么后一个事务读取的数据就是不正确的。
不可重复读:一个事务两次查询的数据不一致，原因是因为另一事务在两次查询期间，对这个数据进行了更新。
幻读:一个事务两次查询的条数不一致，原因是因为查询期间另一个并发事务插入或者删除了某些数据导致的。
10.mysql的事务的隔离级别是什么？默认的隔离级别是什么？
mysql有4种隔离级别，从低到高依次是Read uncommitted,Read Committed,Repeatable read,Serializable.
READ_UNCOMMITTED: 最低的隔离级别，允许读取事务未提交的数据，可能发生脏读，不可重复读，幻读问题
READ_COMMITTED:只能读取事务提交后的数据，能够解决脏读问题，不能解决不可重复读，幻读问题
REPEATABLE_READ:当一个事务读取一个数据时会对其进行加锁处理，防止其他事务对数据进行修改，如果这个事务不结束，别的事务就不可以更改这个数据，除非这个数据是事务自身修改的。可以解决脏读，不可重复读的问题，不能解决幻读。
Serlalizable:最高的隔离级别，完全服从ACID的隔离级别，所有的事务依次逐个执行，不可能相互干扰。可以解决脏读，不可重复读，幻读问题。
mysql默认的隔离是可重复读Repeatable read,oracle默认采用的是Read Committed
以下内容先了解
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。
MySQL索引
11.MyISAM索引与InnoDB索引的区别？
MyISAM的索引类型是非聚簇索引，索引文件和数据是分开放的，数据存储到MYD文件中，索引存储到MYI文件中。索引文件保存的是行数据的物理地址
而InnoDB的索引和数据都存放到ibd文件中，主键使用的是聚簇索引，聚簇索引的叶子节点存储的直接是行数据，因此通过主键查询数据非常高效。
InnoDB的非主键索引的叶子节点存储的是主键值,所以一般会先从二级索引找到主键值，然后再用该主键值查找主键索引获取数据
12.mysql索引有几种类型
主键索引:数据列不允许重复，不允许为NULL,一个表只能有一个主键
唯一索引:数据列不允许重复，允许为NULL,一个表允许多个列创建唯一索引。
可以通过 alter table table_name add unique (column)创建唯一索引
可以通过 alter table table_name add unique (column1，column2)创建唯一组合索引
普通索引:基本的索引类型，没有唯一性限制，允许为NULL
可以通过 alter table table_name add index index_name (column)创建普通索引
可以通过 alter table table_name add index index_name (column1,column2,column3)创建组合索引
联合索引:多个列组成一个索引,只有在查询条件中使用了创建索引的第一个字段时，索引才会被使用。遵循最左前缀匹配原则
全文索引:是目前搜索引擎使用的一种关键技术
可以通过 alter table table_name add fulltext (column)创建全文索引
13.mysql索引的优缺点
索引的优点:
可以大大加快数据的检索速度，这是创建索引最主要原因
通过使用索引，在查询过程中，使用优化隐藏器，提高系统性能
索引的缺点:
时间方面：创建索引和维护索引都要耗费时间，当数据库表中添加/删除/修改数据时，索引也要动态的维护，会降低增删改的效率。
空间方面: 索引会占用一定的物理空间。
14.数据库为什么使用B+树而不是B树？
B+树的检索效率更高。因为索引占用的物理空间比较大，不可能全部存储在内存中，往往文件的形式存储到磁盘上。这样的话索引查找过程中就要产生磁盘的IO消耗。存储引擎读取数据的时候，是以页(4k)的整数倍读取。B树内部节点存放的是键和值，B+树内部节点只存放键(所以B+树的树的高度更低)，所以相对于B树而言，B+树每次读取到内存页中能获得更多的键，IO读写次数也就降低了，IO读写次数是影响检索效率的最大因素，所以B+树的检索效率更高。
B+树的查询效率更加稳定。B树的搜索可能在非叶子节点结束，查询的记录离根节点越近，查询时间越短。而在B+树中，随机查找时，任何关键字都需要走一条从根节点到叶子节点的路，查找路径长度相同，所以每一个关键字查找效率相当。
B+树解决了元素遍历效率低下的问题。由于数据库中的范围查询非常频繁，B+树的叶子节点使用指针顺序连接在一起，所以只要获取到一个叶子节点就可以实现整棵树的遍历。B树不支持这样的操作，B树需要对树上的每一层进行遍历，因此B树只适合随机检索，而B+树同时支持随机检索和范围检索。
因为B+树的叶子节点以有序的链表结构存储，所以B+树的增删节点的效率更高。
15.非聚簇索引一定会回表吗？
从非聚簇型索引跳转到聚簇型索引查找数据的过程叫做回表
当非聚簇型索引叶子节点中包含了需要查询的所有字段时，不需要回表。(这个过程叫索引覆盖)
select id,name from table where name=20;
参考文章
这个文章非常重要把索引覆盖，最左前缀匹配原则，索引下推解释的非常好。一定要多看几遍。
16.最左前缀匹配原则
最左前缀匹配原则：最左前缀可以是联合索引的最左的N个字段，也可以是字符串索引的最边的M个字符(‘张%‘符合最左匹配,’%张’不符合最左匹配)。也就是说你查询条件的N个字段刚好是某个联合索引的最左的N个字段，顺序也一致。那么你就不需要为这N个字段单独创建一个索引，直接用现有的联合索引就可以，可以起到相同的结果
select * from tuser where name like ‘张%’ and age=10 and ismale=1; 这个语句只能用’张’,不会用age字段当做索引,因为必须对name字段等值查询时，匹配到的数据才是按照age严格有序的。
17.MySQL一条查询语句只能使用一个索引吗？
MySQL5.0之后是有索引合并的，所以可以同时使用多个索引。
接着可能会问：
那为什么存在2个索引的情况却只使用了1个索引呢？
回答：与其说是只使用一个索引，倒不如说一个查询语句同时使用多条独立索引的消耗比只使用一个索引还要慢。
18.联合索引是什么？为什么需要注意联合索引的顺序？
MySQL使用多个字段同时建立一个索引，叫做联合索引。
根据最左前缀匹配原则,对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。
所以联合索引的创建顺序要考虑以下两点:
第一是索引复用能力，根据最左前缀匹配原则，如果有(a,b)联合索引了，就不需要在a上单独创建索引了。第一原则就是：如果通过调整顺序，可以少维护一个索引，那么这个顺序就是往往优先考虑采纳的。
第二个原则考虑的是空间。如果需要创建name,age的联合索引以及age,name单字段索引。建议创建(name,age)联合索引和一个age单字段索引，不要创建(age,name)联合索引和一个name单字段索引，虽然效果是一样的。但是后者存储了2次name字段，更占用空间。
19.什么是索引下推？
索引条件下推优化是MySQL5.6添加的，用一句话总结就是索引下推是数据库检索数据过程中为减少回表次数而做的优化。
如果我们的查询条件包含了联合索引一个以上的字段。如果不使用索引下推优化，会将符合最左匹配原则对应的索引列的条件的数据返回给MySQL服务器，MySQL服务器再根据剩下的条件进行过滤。
有了索引下推优化后，会直接在存储引擎中判断检索出数据是否符合索引中其它字段的条件(不符合最左匹配的索引列的条件)，最后只会把符合条件的数据返回给MySQL服务器。这样就减少了返回给MySQL服务器的记录数，回表次数也就减少了。
转载自:https://blog.csdn.net/nizhongli37/article/details/114976587
20.mysql索引的设计原则(重要)
索引的设计原则
适合建立索引的情况：
- 主键自动建立唯一索引
- 频繁作为查询的条件的字段应该创建索引where
- 查询中与其他表关联的字段，外键关系建立索引join
- 尽量的去扩展索引，少创建单独的索引。
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度order by
- 查询中统计或者分组字段适合建立索引 group by
不适合建立索引的情况:
- 频繁更新的字段不适合创建索引 update
- 不作为查询条件的字段不适合创建索引 ~where
- 数据重复多且分布平均的表字段不适合布创建索引，因为建立索引没有实际太大效果 duplicate
- 表记录太少不适合创建索引 count
- 经常增删改的表不适合创建索引 update/insert/delete
21.索引失效情况
早起版本可能失效的情况更多，本次测试不做声明都是8.0版本情况下测试的
(1)like以通配符开头会导致索引失效全表扫描
--开头有通配符,索引失效
explain select * from emp where ename like "%s%"
解决办法就是使用覆盖索引
select empno,ename from emp where ename like '%s%';
(2)隐式类型转换，索引字段与条件或关联字段的类型不一致
例如我们一个表的主键id是varchar类型，而你查找的时候没有加单引号，就会导致索引失效全表扫描
-- 无法使用索引
EXPLAIN select * from test_idx where id = 1;
-- 可以使用索引
EXPLAIN select * from test_idx where id = '1';
(3)条件中对索引列进行运算或使用函数
这里指的是对索引的列进行运算或者使用函数，而不是对查询条件的值使用了函数或者公式。
-- 无法使用索引
explain select * from emp where substr(empno,1,4)=7369
explain select * from emp where empno-1=7368;
-- 可以使用索引
explain select * from emp where empno = 7370-1;
explain select * from emp where empno = concat(73,69)
(4)使用or可能会引起索引失效
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
explain select * from emp where empno=7369 or comm=888
(5)违反最左前缀法则,会导致右边字段的索引失效
假设有联合索引index(a,b,c)
| Where语句 | 索引是否被使用 |
|---|---|
| 1.where a=3 | 使用了a |
| 2.where a=3 and b=5 | 使用了a,b |
| 3.where a=3 and b=5 and c=4 | 使用了a,b,c |
| 4.where b=3或者where b=3 and c=4或者where c=4 | 索引失效 |
| 5.where a=3 and c=5 | 使用了a,但是c不可以,因为a右边是b |
| 6.where a=3 and b>4 and c=5 | 使用了a,b 由于c在b的范围之后失效 |
| 7.whera a=3 and b like ‘kk%’ and c=4 | a,b,c都使用到 |
| 8.where a=3 and b like ‘%kk%’ and c=4 | 只用到a |
| 9.where a=3 and b like ‘%kk’ and c=4 | 只用到a |
| 10.where a=3 and b like ‘k%kk%’ and c=4 | 用到了a,b,c |
| 我们对7和10补充说明。 | |
b like ‘kk%’不应该是范围查询吗？那么c索引应该失效，为什么c索引会用到？ |
我们先测试下结果
alter table index idx_job_ename_sal(job,ename,sal)
explain select * from emp where job='clerk' and ename like 's%' and sal>1000
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | emp | NULL | range | idx_ename,idx_job_ename_sale | idx_job_ename_sale | 90 | NULL | 1 | 33.33 | Using index condition |
| 发现索引的字节长度是90，经计算length=94+2+104+3+8+1=90。确实使用了sal的索引 | |||||||||||
 | |||||||||||
| 具体解释 |
(6)is NULL,is NOT NULL 有时索引失效
explain select * from emp where job is not null;
explain select * from emp where job is null;
MySQL底层会自动判断，如果全表扫描快，则直接使用全表扫描，不走索引。条件返回值占总数据的比例比较小，就有可能使用索引。
(7)NOT IN索引失效
explain select * from emp where job not in ('clerk','SALESMAN','MANAGER')
MySQL锁
22.按粒度划分mysql数据库的锁有哪些？
在关系型数据库中，可以按照粒度把数据库锁分为行级锁，表级锁，页级锁(BDB引擎)
MyISAM采用表级锁，InnoDB 表级锁和行级锁都支持，默认使用行级锁。
行级锁，表级锁，页级锁对比
行级锁是mysql锁定粒度最小的一种锁，只针对操作行进行加锁。发生锁冲突的概率很低，并发性能最高。但是加锁的开销比较大，加锁慢，有可能出现死锁。
表级锁是mysql锁定粒度最大的一种锁，表示对当前操作的整张表加锁，优点是开销小，加锁快，不会出现死锁。缺点是发生锁冲突概率最高，并发性能最低
页级锁的锁定粒度介于行级锁和表级锁之间,表级锁加锁快，并发性能低，行级锁加锁慢，并发性能高，所以取了折衷的页级，一次锁定相邻的一组记录。并发性能一般。
23.从锁的类别上分MySQL都有哪些锁呢？像上面那样子进行锁定岂不是有点阻碍并发效率了
从锁的类别上分，有共享锁和排他锁。
排他锁也叫独占锁，该锁一次只能被一个线程所持有，如果一个线程对数据加上排他锁后，其它线程便不能给这个数据加任何锁(排他锁跟其它排他锁和共享锁互斥)，获得排他锁的线程既能读数据又能修改数据。JDK中的Synchronized和JUC中的Lock实现类就是互斥锁
共享锁指该锁可以被多个线程所持有。如果一个线程对数据加上共享锁后，其它线程只能对该数据加共享锁，不能加排他锁。获得共享锁的线程只能读数据，不能更新数据。
24.Mysql什么时候用表级锁？什么时候用行级锁？
如果是MyISAM存储引擎只能使用表级锁，如果是InnoDB的存储引擎既支持行级锁又支持表级锁。
对于InnoDB引擎而言，虽然支持行级锁但是我们的SQL语句没有用到索引那还是会全表扫描使用表级锁。
SQL优化
25.Mysql你平常优化是怎么做的？
1.开启慢查询日志，它可以记录MySQL中响应时间超过阈值的语句。使用set global slow_query_log=1开启慢查询日志，set global long_query_time=3设置阈值。
2.使用taif -f命令或者mysqldumpshow日志分析工具对我们慢查询日志结果进行分析。
tail -f d8fa1c4dd675-slow.log
# Time: 2021-09-29T10:51:50.452105Z -->时间
# User@Host: root[root] @ localhost [] Id: 7 -->使用的用户以及用户的id
# Query_time: 4.011471 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 -->检索需要的时间
SET timestamp=1632912710; -->当前时间的时间戳
select sleep(4); -->记录的SQL语句
#查看mysqldumpshow帮助信息
mysqldumpshow --help
-s:是表示按何种方式排序，有以下7种
c:是访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-g:后边搭配一个正则表达式,大小写不敏感的
-t:后面要添加一个数字,表示返回前面多少条数据
3.根据慢查询日志中的sql语句，我们可以使用explain执行计划进行分析。执行计划中我们可以看出查询操作的操作类型，访问类型，可能使用的索引，实际使用的索引，额外信息。
4.优化案例
(1)如果没有使用索引的走全表扫描的，我们可以给查询的字段建立索引，如果是联合索引要注意最左前缀匹配原则。
(2)如果是关联查询的话，我们使用左连接的话会从右边搜索行，左边一定都有，所以我们要给右表的关联字段添加索引。如果是右链接刚好相反，会从左边搜索行，右边一定都有。所以我们一般给左表的关联字段添加索引。如果是内链接的话哪个表关联字段添加索引都可以。SQL语句会自动优化让有索引的表成为驱动表。但是我们一般是给小表建立索引，满足小表驱动大表。
(3)如果执行计划中额外信息extra中出现了Using filesort。说明使用了外部文件进行排序，我们应尽量使用index(索引)排序。使用where子句与order by子句的条件列组合满足索引最左前列匹配原则。
多路排序 先读取磁盘order by的字段 进行排序， 然后再读取磁盘中的其它字段，读取了两次磁盘
单路排序 直接将要查询的字段和排序字段都读取到内存，避免了二次读取磁盘，减少了IO次数，但是比双路排序更占用内存
如果非要使用外部文件排序，尽量多使用单路排序，少使用双路排序。
我们一定要只query需要的字段，只有查询的字段总和小于排序字段的最大长度max_length_for_sort_data才会使用单路排序。
如果单路排序数据超过了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，排序完后再取sort_buffer容量大小，再排序，从而造成多次I/O，所以我们可以尝试提高sort_buffer_size减少IO次数。
也可以尝试提高排序字段的最大长度max_length_for_sort_data，这样可以增加使用单路排序的次数。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
