大家好,我是小菜,一个渴望在互联网行业做到蔡不菜的小菜。可柔可刚,点赞则柔,白嫖则刚! 死鬼~看完记得给我来个三连哦!
“ 本文主要介绍
Mysql开发中所需的知识和面试中所必知的
如有需要,可以参考 如有帮助,不忘 点赞 ❥
Oracle
公司。GPL
协议,你可以修改源码来开发自己的MySQL系统。binlog(二进制日志)
用于主从复制及备份恢复:binlog中存放了所有操作记录,可用于恢复。相当于Redis中的AOF,my.ini中binlog配置(默认是关闭的)
开启:
[mysqld]
log-bin = mysql-bin
binlog-format = row
Error log(错误日志)
默认是关闭的,通常用于记录数据库服务端启动、重启、主从复制时,记录错误,将日志详情保留在文件中,方便DBA、运维开发人员阅读。
开启:
[mysqld]
log-error=/data/mysql_error.log
慢查询日志log
默认是关闭的。记录查询的sql语句,如果开启会减低mysql的整体性能,因为记录日志也是需要消耗系统资源的。
开启:
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log //linux
long_query_time = 1
windows:
..\mysql-8.0.19-winx64\data
目录下存储数据库文件
linux:
默认路径/var/lib/mysql
(可在配置文件中更改/usr/share/mysql/
下的my-huge.cnf
)每个目录代表一个同名的库。
Myisam存放方式:
frm文件(framework)
:存放表结构myd文件(data)
:存放表数据myi文件(index)
:存放表索引innodb存放方式:
ibdata1
:Innodb引擎将所有表的数据都存放在这里面/usr/share/mysql/ibdata1而frm文件存放在库同名的包下frm文件
:存放表结构my.ini
配置文件my.cnf
配置文件MysSQL用户管理
create user cbuc identified by '123456'
select host,user,select_priv,insert_priv,drop_priv from mysql.user;
host:
表示连接类型
user:
表示用户名
select_priv,insert_priv,drop_priv等:
该用户所拥有的权限
设置密码 修改当前用户的密码:
修改某个用户的密码
修改用户 修改用户名:
删除用户
权限管理
grant 权限1,权限2,...,权限n on 数据库名.表名 to 用户名@用户地址 identified by '密码'
#如果发现没有该用户,则会直接创建一个用户
grant select,insert,delete,update on db_crm.* to cbuc@localhost;
#给cbuc用户赋予对表增删改查的权限
revoke 权限1,权限2,...,权限n on 数据库名.表名 from 用户名@用户地址
revoke all privileges on mysql.* from cbuc@localhost
#如果已赋全库的表,就回收全库全表的所有权限
#查看当前用户权限
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;
#此变量是只读权限,需要在配置文件中修改
在my.ini
/ my.cnf
中添加
[mysqld]
lower_case_table_names = 1
重启服务器(重启前要将原来的数据库和表转换为小写,否则更改后将找不到数据库名)
sql_mode
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
各引擎简介:
InnoDB存储引擎
:
InnoDB是MySQL默认的事务型引擎,它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。具有行级锁,外键,事务等优势,适合高并发情况。MyISAM存储引擎
:
MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务和行级锁(MyISAM改表时会将整个表全锁住),缺陷:崩溃后无法安全恢复。Archive引擎
:
rchive存储引擎只支持 insert 和 select操作,在MySQL5.1之前不支持索引。Archive表适合日志和数据采集类引用。适合低访问量大数据等情况。Blackhole引擎
:
Blackhole引擎没实现任何存储机制,它会丢弃所有插入的数据,不任何保存。但服务器会记录Blackhole表的日志,所以可以用于复制数据到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。CSV引擎
:
CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引。可以作为一种数据交换的机制,非常有用。存储的数据直接可以在操作系统里,用文本编辑器,或者excel读取。Memory引擎
:
如果需要快速地访问数据,并且这些数据不会被修改,重启后丢失也没有关系的话,那么使用Memory表是非常有用的。Memory表至少比MyISAM表要快一个数量级。Federated引擎
:
Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的。MyISAM和InnoDB比较:
外键 | 不支持 | 支持 |
---|---|---|
索引 | B Tree | B+ Tree |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其他行有影响,适合高并发操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求比较高,而且内存大小对性能有决定性的影响 |
表空间 | 小 | 大 |
关注点 | 性能 | 事务 |
默认安装 | 是 | 是 |
用户表默认使用 | 否 | 是 |
自带系统表使用 | 是 | 否 |
InnoDB主键为聚簇索引,基于聚簇索引的增删改查效率非常高
聚簇索引
:实际存储的循序结构与数据存储的物理机构是一致的
非聚簇索引
:记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系
列
建索引,但并不可能每一列都建索引
索引并非越多越好。当数据更新了,索引会进行调整,也会很消耗性能。
并且MySQL并不会把所有索引都用上,只会根据其算法挑一个索引用。所以建的准很重要SQL执行顺序
人工读取顺序:
引擎执行顺序:
总结:
共有/独有
有两个表,员工表Employee和部门表Dept,员工表里面有Dept字段与部门表的主键ID相对应。
共有:满足employee.deptId = dept.id 的数据 称为两表共有
独有:employee.deptId <> dept.id 的数据 称为员工表独有
七种JOIN
有两个表,t1 表是员工表 emp,t1 表是部门表 dept
2、t1 表和 t2 表共有 + t1 表独有 (left join
)
select * from emp t1 left 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
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
“ MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:==索引是数据结构。== 目的在于提高查询效率,可以类比字典。
左边是数据表,一共有两列七条数据,最左边是数据记录的物理地址,为了加快Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。
二叉树
:二叉树很可能会发生两边不平衡的情况。
B-Tree
:会自动根据两边的情况自动调节,使两端无限趋近于平衡状态,可以使性能最稳定。但是插入/修改
操作过多时,B-TREE会不断调整平衡,消耗性能。
B树
(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树这种类型的索引之外,还有哈希索引(hash index)等。索引优势:
索引劣势:
索引结构
BTree索引
:
真实数据存在于叶子节点,即3、5、9、10、13、15、28、29、36、60、75、79、90、99.
【查找过程】 如果要查找数据29,那么首先会把磁盘块1有磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的的P2指针的磁盘的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找到29,结束查询,总计三次IO
B+Tree索引
:
非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如5、10并不真实存在于数据表中。
B+Tree 第二级的数据并不能直接取出来,只作索引使用。在内存有限的情况下,查询效率高于BTree BTree第二级可以直接取出来,树形结构比较重,在内存无限大到时候有优势。
【B+Tree 和 BTree 的区别】
聚簇索引
好处:
按照聚簇索引排序顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的IO操作。
限制:
full-text全文索引
“ 全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用
分词技术
等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
查询:
# 传统 LIKE 查询
select * from blink t1 where t1.content like '%菜%'
# 全文检索
select * from blink t1 where MATCH(title,content) AGAINST('菜')
限制:
MySQL5.6.4之前只用MyISAM 支持,5.6.4以后 InnoDB才支持,但是官方版不支持中文分词,需要第三方分词插件。Hash索引
RTree索引
R-Tree在MySQL很少使用,仅支持geometry 数据结构,支持该类型的存储引擎只有MyISAM、bdb、InnoDB、ndb、archive几种。相对于B-Tree,R-Tree的优势在于查找
。
语法:
# 随表一起创建
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)索引
语法:
# 随表一起创建
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
# 随表一起创建
CREATE TABLE emp (
# 使用AUTO_INCREMENT关键字的列必须要有索引
ID int(10) UNSIGNED AUTO_INCREMENT
, EMP_NO varchar(8)
, NAME varchar(8)
, UNIQUE(EMP_NO)
)
#建立唯一索引是必须保证所有的值是唯一的(除了null),若有重复数据,会报错
# 单独建唯一索引
create unique index idx_emp_no on emp(EMP_NO)
# 删除主键索引
drop index idx_emp_no on emp
# 随表一起创建
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)
哪些情况需要建立索引
查询条件
的字段应该创建索引(where后面的语句)查询中排序的字段
,排序字段若通过索引去访问将大大提高排序速度查询中统计或者分组字段
哪些情况不需要建立索引
MySQL常见瓶颈
CPU
SQL中对大量数据进行比较、关联、排序、分组(最大的压力在于比较)IO
实例内存满足不了缓存数据或排序等需要,导致产生大量物理IP。查询执行效率低,扫描过多数据行。锁
不适宜的锁的设置,导致线程阻塞,性能下降。死锁,线程之间交叉调用资源,导致死锁,程序卡主。服务器硬件的性能瓶颈:
top
,free
,iostat
和vmstat
来查看系统的性能状态
Explain的使用
使用Explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
可以查看的内容:
怎么用:
explain + SQL语句
包含的信息:
各字段解释
1.【 id】
select查询的序列号,包含一组数字,表示查询中执行select字句或操作表的顺序。三种情况:
id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行。
2.【select_type】
【dependent subquery 和 subquery 的区别】
依赖子查询:子查询结果为多值
子查询:查询结果为单值
@@表示查的是环境参数,没办法缓存
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+20*3+2=67
第二组计算为:
key_len=deptno(int)+null=4+1=5
8.【ref】
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或者常量被用于查找索引列上的值
9.【row】
rows列显示MySQL认为它执行查询时必须检查的行数(越少越好)
10.【Extra】
包含不适合在其他列中显示但十分重要的额外信息。
覆盖索引:
一个索引包含了(或覆盖了)【select子句】与查询条件【where 子句】中所有需要的字段就叫做覆盖索引。
==注意:== 只取出需要的列,不可select *,不可将所有字段一起做索引索引的使用
(select *)
like '%xxx'
:type 类型会变成all
like 'xxx%'
:type 类型为range,算是范围,可以使用索引
【例子小节】
此时复合索引index(a,b,c)
是,使用到 a
【使用建议】
关联查询优化
1、保证被驱动表的join字段已经被索引(join后的表为驱动表) 2、left join时,选择小表作为驱动表,大表作为被驱动表(left join一定是左边是驱动表,右边是被驱动表) 3、inner join时,MySQL会自己帮你把小结果集选为驱动表。因为驱动表无论如何都会被全表扫描,所以扫描次数越少越好。 4、子查询尽量不要放在被驱动表,有可能使用不到索引。
# 未加索引,type为ALL
explain select * from class left join book on class.card = book.card
# 添加索引优化,第二行的type变成了ref
alter 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)
子查询优化
1、 有索引的情况下用 inner join 是最好的,其次是 in,exists最糟糕 2、 无索引的情况下用小表驱动大表,因为 join 方式需要 distinct ,没有索引distinct 消耗性能比较大,所以 exists 性能最佳,其次 in 其次,join性能最差 3、 无索引的情况下大表驱动小表,in和exists的性能应该是接近的,都比较糟糕,exists稍微好一点,但是超不过5%
order by关键字优化
尽量使用Index方式排序,避免使用FileSort 方式排序。MySQL中支持两种方式的排序,FileSort
和Index
,其中index效率高,它指Mysql扫描索引本身完成排序,FileSort方式效率比较低。满足三种情况会使用Index排序。
例子:talA 表中有索引 (age,birth,name)
分页查询的优化--limit
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关键字去重
例子:
# 使用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
“ 本文较长,能看到这里的都是好样的,成长之路学无止境 今天的你多努力一点,明天的你就能少说一句求人的话!