本文主要介绍 MySQL开发和面试中所必知的
本文较长，分为上下篇（可收藏，勿吃尘）
如有需要，可以参考
如有帮助，不忘 点赞 ❥
MySQL是一个关系型数据库管理系统，由瑞典MYSQL AB公司开发，目前属于Oracle公司。
MySQL 是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库中，这样就增加了速度并提高了灵活性。
Mysql是开源的，是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、JAVA、Perl、php、Eiffel、Ruby和Tcl等。
MySQL支持大型数据库，支持5000条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
[mysqld]
log-bin = mysql-bin
binlog-format = row
复制代码
[mysqld]
log-error=/data/mysql_error.log
复制代码
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log //linux
long_query_time = 1
复制代码
windows：my.ini 配置文件
linux：my.cnf 配置文件
3）MySQL的用户与权限管理
1.创建用户
create user cbuc identified by '123456'
2.关于 user 表
select host,user,select_priv,insert_priv,drop_priv from mysql.user;
host： 表示连接类型
user：表示用户名
select_priv，insert_priv，drop_priv等：该用户所拥有的权限
3.设置密码
--- 修改当前用户的密码
set password = password('123456')
--- 修改某个用户的密码
update mysql.user set password = password('123456') where user = 'cbuc'
4.修改用户
--- 修改用户名：
update mysql.user set user = 'cbuc' where user='c1';
--- 所有通过user表修改后必须用该命令才能生效
flush privileges;
5.删除用户
--- 不要通过delete from user t1 where t1.user='cbuc'进行删除，系统会有残留信息保留
drop user cbuc;
1.授予权限：
--- 如果发现没有该用户，则会直接创建一个用户
grant 权限1，权限2,…,权限n on 数据库名.表名 to 用户名@用户地址 identified by '密码'
--- 给cbuc用户赋予对表增删改查的权限
grant select,insert,delete,update on db_crm.* to cbuc@localhost;
2.收回权限：
--- 如果已赋全库的表，就回收全库全表的所有权限
revoke 权限1，权限2,…,权限n on 数据库名.表名 from 用户名@用户地址
revoke all privileges on mysql.* from cbuc@localhost
3.收回权限：
--- 查看当前用户权限
show grants;
--- 查看某用户的全局权限
select * from mysql.user;
--- 查看某用户的某库的权限
select * from mysql.db;
--- 查看某用户的某个表的权限
select * from mysql.tables_priv;
windows系统默认是大小写不敏感，但是linux系统是大小写敏感的。
0（默认）： 大小写敏感
1： 大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘中，对于sql语句都是转换为小写对表的DB进行查找。
2： 创建的表和DB依据语句上格式存放，凡是查找都是转换为小写进行
SHOW VARIABLES LIKE '%lower_case_table_names%';
设置：
set lower_case_table_names = 1; #此变量是只读权限，需要在配置文件中修改
复制代码
[mysqld]
lower_case_table_names = 1
复制代码
sql_mode 是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的， 比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。
常用设置：
[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
复制代码
show engines;
复制代码
可以看出默认的存储引擎是InooDB
1.InnoDB存储引擎：
InnoDB是MySQL默认的事务型引擎，它被设计用来处理大量的短期（short-lived）事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。具有行级锁，外键，事务等优势，适合高并发情况。
2.MyISAM存储引擎：
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁（MyISAM改表时会将整个表全锁住），缺陷：崩溃后无法安全恢复。
3.Archive引擎：
rchive存储引擎只支持 insert 和 select操作，在MySQL5.1之前不支持索引。Archive表适合日志和数据采集类引用。适合低访问量大数据等情况。
4.Blackhole引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
5.CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。可以作为一种数据交换的机制，非常有用。存储的数据直接可以在操作系统里，用文本编辑器，或者Excel读取。
6.Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启后丢失也没有关系的话，那么使用Memory表是非常有用的。Memory表至少比MyISAM表要快一个数量级。
7.Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
MyISAM和InnoDB比较
InnoDB主键为聚簇索引，基于聚簇索引的增删改查效率非常高
聚簇索引： 实际存储的循序结构与数据存储的物理结构是一致的
非聚簇索引： 记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系
人工读取顺序：
SELECT (DISTINCT) < select_list >FROM < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition>ORDER BY < order_by_condition >LIMIT < limit_number >
引擎执行顺序：
FROM < left_table >ON < join_condition >< join_type > JOIN < right_table >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition >SELECT (DISTINCT) < select_list >ORDER BY < order_by_condition >LIMIT < limit_number >
总结：
1、 t1 表和 t2 表共有 (inner join)
select * from emp t1 inner join dept t2 on t1.deptId = t2.id
2、t1 表和 t2 表共有 + t1 表独有 (left join)
select * from emp t1 left join dept t2 on t1.deptId = t2.id
3、t1 表和 t2 表共有 + t2表独有（right join）
select * from emp t1 right join dept t2 on t1.deptId = t2.id
4、 t1 表的独有（left join…where t2.id is null）
select * from emp t1 left join dept t2 on t1.deptId = t2.id where t2.id is null
5.t2 表的独有（right join…where t1.id is null）
6. t1 表和 t2 表全有（union）
在这里插入图片描述
MySQL中不支持FULL JOIN
UNION： 可去除重复数据
UNION ALL： 不去除重复数据
select * from emp t1 left join dept t2 on t1.deptId = t2.id
union
select * from emp t1 right join dept t2 on t1.deptid = t2.id
7、 t1 表的独有 + t2 表的独有（union）
在这里插入图片描述
select * from emp t1 left join dept t2 on t1.deptId = t2.id where t2.id is null
UNION
select * from emp t1 right join dept t2 on t1.deptId = t2.id
where t1.deptId is null
复制代码
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。 目的在于提高查询效率，可以类比字典。
左边是数据表，一共有两列七条数据，最左边是数据记录的物理地址，为了加快Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
二叉树： 二叉树很可能会发生两边不平衡的情况。
B-Tree： 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态，可以使性能最稳定。但是插入/修改操作过多时，B-TREE会不断调整平衡，消耗性能。
索引优势
索引劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MySQL有大量的表，就需要花时间研究建立最优秀的索引，或优化查询语句
索引结构
1.BTree索引：
真实数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99.
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据29，那么首先会把磁盘块1有磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的的P2指针的磁盘的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找到29，结束查询，总计三次IO
2.B+Tree索引：
B+Tree 第二级的数据并不能直接取出来，只作索引使用。在内存有限的情况下，查询效率高于BTree
BTree第二级可以直接取出来，树形结构比较重，在内存无限大到时候有优势。
【B+Tree 和 BTree 的区别】
1） 内存有限的情况下，B+Tree永远比BTree好，无限内存则反之
2） B树的关键字和记录是放在一起的，叶子节点可以看做外部节点，不包含任何信息；B+树叶子节点中国你只有关键字和指向下一个节点的索引，记录只放在叶子节点中。（一次查询可能进行两次I/O操作）
3） 在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录存在；而B+树每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要在比较关键字。从这个角度看B树的性能好像会比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B数多，树高比B树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数比B树多但是一次磁盘访问时间相当于成百上千次内存比较时间，因此实际中B+树的性能可能还会好写，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等）
4） B+树的磁盘读写代价更低，相对来说IO读写次数也就降低了。
5） B+树的查询效率更加稳定。由于非终结点并不是指向文件内容的节点，而只是叶子节点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所以关键字查询的路径长度相同，导致每一个数据的查询效率相当。
聚簇索引
好处：
按照聚簇索引排序顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。
限制：
3.full-text全文索引
全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
# 传统 LIKE 查询
select * from blink t1 where t1.content like '%菜%'
# 全文检索
select * from blink t1 where MATCH(title,content) AGAINST('菜')
复制代码
4.Hash索引
5.RTree索引
索引分类
1.主键索引
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, NAME varchar(8)
, PRIMARY KEY(ID)
)
# 单独建主键索引
ALTER TABLE emp add PRIMARY KEY emp(id);
# 删除主键索引
ALTER TABLE emp drop PRIMARY KEY; # 修改主键索引前必须删除(drop)原索引，再新建(add)索引
2.单值索引
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, EMP_NO varchar(8)
, NAME varchar(8)
, KEY(EMP_NO)
)
# 单独建单列索引
create index idx_emp_no on emp(EMP_NO)
# 删除单列索引
drop index idx_emp_no
3.唯一索引
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, EMP_NO varchar(8)
, NAME varchar(8)
, UNIQUE(EMP_NO)
)
# 单独建唯一索引
create unique index idx_emp_no on emp(EMP_NO)
# 删除主键索引
drop index idx_emp_no on emp
4.复合索引
在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建索引）;
当表的行数远大于索引列的数目时可以使用复合索引。
# 随表一起创建CREATE TABLE emp ( # 使用AUTO_INCREMENT关键字的列必须要有索引 ID int(10) UNSIGNED AUTO_INCREMENT , EMP_NO varchar(8) , NAME varchar(8) , key(EMP_NO，NAME) )#建立唯一索引是必须保证所有的值是唯一的(除了null)，若有重复数据，会报错# 单独建唯一索引create index idx_no_name on emp(EMP_NO,NAME)# 删除主键索引drop index idx_no_name on emp
【基本语法】
# 创建
alter < table_name > add [unique] index <index_name> on <column_name>
# 删除
drop index <index_name> on <table_name>
#查看
show index from <table_name>
#使用ALTER命令
#方式1：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
alter table <table_name> add primary key <column_name>
#方式2：该语句添加一个唯一索引，值必须是唯一的(null外，null可能会出现很多次)
alter table <table_name> add unique key <column_name>
#方式3：该语句添加普通索引，索引值可以出现很多次
alter table <table_name> add index <index_name>(column_name)
#方式4：该语句指定了索引为FULLTEXT，用户全文索引
alter table <table_name> add FULLTEXT <index_name>(column_name)
哪些情况需要建立索引
查询中统计或者分组字段
哪些情况不需要建立索引
CPU
SQL中对大量数据进行比较、关联、排序、分组（最大的压力在于比较）
IO
实例内存满足不了缓存数据或排序等需要，导致产生大量物理IP。查询执行效率低，扫描过多数据行。
锁
不适宜的锁的设置，导致线程阻塞，性能下降。死锁，线程之间交叉调用资源，导致死锁，程序卡主。
服务器硬件的性能瓶颈：
top，free，IOStat和vmstat来查看系统的性能状态
Explain的使用
使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
explain + SQL语句
包含的信息：
1.【 id】
select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。
三种情况：
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越被先执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
2.【select_type】
SIMPLE
简单的select查询，查询中不包含子查询或者UNION
PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED
在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
SUBQUERY
在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERY
在SELECT或WHERE列表中包含了子查询，子查询基于外层
UNION RESULT
从UNION表获取结果的SELECT
3.【table】
显示这一行的数据是关于哪张表的
4.【type】
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏的依次排序：
system > const > eq_ef > ref > range(尽量保证) > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref
5.【possible_keys】
显示可能应用到这张表中的索引，一个或多个。查询涉及到的字段上若存在的索引，则该索引将被列出，但不一定被查询实际使用
6.【key】
实际使用的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
7.【key_len】
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分利用上了索引。
计算方式：
动态类型包括：varchar，detail text（）截取字符串
本章的表结构如下：
第一组计算结果：
key_len=deptno(int)+null+ename(varchar(20)3+动态=4+1+203+2=67
第二组计算为：
key_len=deptno(int)+null=4+1=5
8.【ref】
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值
9.【row】
rows列显示MySQL认为它执行查询时必须检查的行数（越少越好）
10.【Extra】
包含不适合在其他列中显示但十分重要的额外信息。
2.最佳左前缀原则
如果索引了多列，要遵守最左前缀原则，值得是查询从索引的最左前列开始，并且不跳过索引中的列
and 忽略左右关系，即使没有按顺序，由于优化器的存在，也会自动优化
3.不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描。
4.存储引擎不能使用索引中范围条件右边的列
范围若有索引则能使用到索引，范围条件右边的索引会失效（范围条件右边与范围条件使用的同一个组合索引，右边的才会失效，若是不同索引则不会失效）
5.**尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select ***
6.MySQL在使用不等于（!= 或 <>）的时候无法使用索引，会导致全表扫描。
where age != 10 and name = 'xxx' 这种情况下，mysql会自动优化将 name = 'xxx' 放在 age != 10 之前，name依然能使用索引，只是age的索引失效
7.is not null 也无法使用索引，但是 is null 是可以使用索引
8.like 以通配符开头（'%xxx'）索引失效变成全表扫描
like '%xxx'：type 类型会变成all
like 'xxx%'：type 类型为range，算是范围，可以使用索引
9.字符串不加单引号索引失效
底层进行类型转换时索引失效，使用了函数造成了索引失效
10.少用or，用它连接时索引会失效
【例子小结】
此时复合索引index（a,b,c）
【使用建议】
# 未加索引，type为ALLexplain select * from class left join book on class.card = book.card# 添加索引优化，第二行的type变成了refalter table book add index idx_card_B(card);# 这是由左连接特效决定的，left join条件用于确定如何从右表搜索行，左边一定都有# 继续优化，删除旧索引，新建新索引drop index idx_card_B on book;alter table class add index idx_card_A(card)
例子：talA 表中有索引 （age,birth,name）
explain select sql_no_chache * from emp order by deptno limit 10000,40
#加上索引create index emp on emp(deptno)
# 通过以上结果可以看出加上索引并不能改变# 进一步优化：先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联（查询数据量小了后）explain select sql_no_cache * from emp e inner join (select id from emp e order by deptno limit 10000,40)a on a.id = e.id 
GROUP BY 关键字优化
1、group by实质上是先排序后进行分组，遵照索引建的最佳前缀
2、当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
3、where高于having，能写在where限定的条件就不要去having限定了。
# 使用distinct关键字去重消耗性能select distinct BOOK_NAME from book where id in(1,2,5,4,8)# 使用 group by能够利用到索引select BOOK_NAME from book where id in(1,2,5,4,8) group by BOOK_NAME
本文较长，能看到这里的都是最棒的！成长之路学无止境~
今天的你多努力一点，明天的你就能少说一句求人的话
原文链接：
https://juejin.cn/post/6844904122169180168
京公网安备 11010802035086号