mysql

mysql

[TOC]

索引

索引结构

  • B+树?
  • hash索引
  • full-text全文索引
  • R树索引
CREATE [UNIQUE] INDEX indexName ON tableName(columnName,c,c) ;

ALTER tableName ADD [UNIQUE] INDEX [indexName] ON (columName(length));

DROP INDEX [indexName] ON tableName;

SHOW INDEX FROM tableName\G
3.png
  • 单值索引:索引中只有一个列,一个表可以有多个单值索引
  • 复合索引

https://www.jianshu.com/p/35588ecf33c1

[图片上传失败...(image-aa78ae-1595646160604)]

  • 索引的值必须唯一,可以为null

B+树实现索引

主键索引查询

1.png

区间查询

找到最小值,然后顺序遍历结点找到最大值

优化

explain

https://www.cnblogs.com/tufujie/p/9413852.html

用于查看mysql实际执行语句的方式,执行后的查询结果含有以下

字段 解释
id 代表执行的顺序,id==大的先==执行,小的后执行
select_type 表示每个查询的类型
table 表名
type 数据操作(访问|读取)方式
possible 可能用到的索引
key 实际用到的索引
key_len 索引字节(个)数
ref 查询使用到的常量或者表中值(用这个值去查询其他表的数据)
rows 每张表有多少行被优化器查询过
Extra

  • id

  • select_type

    (1) SIMPLE(简单SELECT，不使用UNION或子查询等)

    (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

    (3) UNION(UNION中的第二个或后面的SELECT语句)

    (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

    (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

    (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

    (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

    (8) DERIVED(派生表的SELECT, FROM子句的子查询)

    (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

  • table

  • type

9.png
类型 示例 示例
system,const id写死为1,const. <br />表中只有一条id为1,system.
10.png
eq_ref 唯一性索引扫描,索引表中只有一条记录.
15.png
ref 非唯一性索引扫描,索引表中可能有多相同条记录.
14.png
range 范围扫描索引
13.png
index 遍历索引树查找
12.png
all 遍历全表查找
11.png
  • possible
  • key
  • key_len
  • ref
  • rows
  • Extra
名字 解释
using where
using index 使用覆盖索引
using filesort 索引外排序
using temporary 建立了临时表
using join buffer 使用了连接缓存
impossible where
select tables optimized away
distinct

索引失效

  • 全值匹配
  • 最左前缀法则
  • ==不在索引上做任何操作==(计算,函数,类型转换)
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用索引覆盖
  • mysql在似乎用不等于的适合无法是红索引会出现全表扫描
  • is null is not null 无法使用索引
  • like以通配符开头 无法使用索引

若要查询'%aa%',则==只查询覆盖索引加id==字段,再用id查全部诗句(子查询in).

  • 字符串不加单引号
  • or ,会导致连接时索引失效(我的mysql8.0.15显示为range)

order by 优化

key a_b_c(a,b,c)可以使用索引

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC

WHERE a='x' ORDER BY [a,] b,c
WHERE a='a' and b='b' ORDER BY [a,b,] c
WHERE a='a' and b>x ORDER BY [a,b,]|[b,] c

无法使用索引

ORDER BY a ASC,b DESC,c DESC    #排序不一致
WHERE d='x' ORDER BY b ,c       #失去前缀 
WHERE d='x' ORDER BY b,c,d      #包含了非索引字段

WHERE a IN (...) ORDER BY b,c   #对于in来说多个条件也是范围查询
explain select * from city where `name` like 'H%' order by id,`name`;       #也不行,用的id和name不在同一个索引中

慢查询日志

查看

SHOW VARIABLES LIKE '%slow_query_log%'      #开启慢查询日志

SHOW VARIABLES LIKE 'long_query_time%'      #时间阈值

mysqldumpslow

18.png

show proflie

mysql提供可以分析当前会话中语句执行的资源消耗情况

http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

SHOW VARIABLES LIKE 'PROFILING'
SET PROFILING ON
SHOW profiles;
show profile cpu,block, io for query 3;     #(3为id)

[图片上传失败...(image-350ce6-1595646534502)]

案例

aaaa

​
17.png

​ 表名

锁

https://www.cnblogs.com/frankyou/p/9582587.html

https://www.cnblogs.com/volcano-liu/p/9890832.html

锁的分类

LOCK TABLE tableName READ|WRITE, tableName READ|WRITE []

SHOW OPEN TABLES;
UNLOCK TABLES;

SHOW STATUS LIKE 'table%';      #
SHOW STATUS LIKE 'innodb_row_lock%' #
21.png

按粒度

  • 行锁(写)
  • 表锁(读)
  • 页锁(mysql)

按对数据操作类型

  • 读锁(共享锁)
  • 写锁(排他锁)

锁级别

  • readuncommitted 读未提交
  • committed 读已提交
  • repeatable 可重复读(mysql默认)
  • serializer 序列化

读锁

连接1锁住a表,a表不可写

连接1==无法读其他表==,(主从复制,备份数据,上a表锁,会导致事物只能限定在当前表中,避免死锁出现)

连接2写a表会被阻塞

20.png

写锁

VARCHAr 不加''号,导致表锁发生........

连接1锁住a表

连接1==无法读写其他表,(主从复制,备份数据,上a表锁,会导致事物只能限定在当前表中,避免死锁出现)==

连接2读写a表会被阻塞

间隙锁

锁住某范围内的行使得无法插入新列

建议

  • 尽可能让所有数据检索都通过索引完成，避免出现无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件，避免间隙锁
  • 控制事物大小，减少锁定资源量和时间长度
  • 尽可能降低事物隔离级别

事务

SET AUTOCOMMIT=0
{
#锁住某一行
    begin
    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE   #共享锁
    SELECT * FROM tableName WHERE a=1 FOR UPDATE;       #排他锁
    COMMIT
} 
ROLLBACK

ACID

  • 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位

  • 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

  • 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

  • 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

可能发生的问题

https://www.cnblogs.com/wyaokai/p/10921323.html

  • 脏读()
  • 不可重复读(行级,需要行级锁解决)
  • 幻读(表级,需要锁住表)
事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） y y y
不可重复读（read-committed） n y y
可重复读（repeatable-read） n n y
串行化（serializable） n n n

点

  • 数据逻辑删除,不物理删除.减少索引调整次数.每次删除或更新数据,不仅会删除数据本身,还会更新索引.

  • 索引具备查找和排序功能

索引建立的情况(条件)

  • 高并发下倾向创建组合索引

适合

  • 主键自动建立索引
  • 频繁查询的字段
  • 表间关联字段,外键建立索引
  • 查询中排序的字段,排序字段通过索引去访问将提高排序速度
  • 查询中统计或者分组字段

不适合

  • 频繁更新的字段
  • where条件中用不到的字段

查询

23.png

全貌

4.png
5.png

执行流程

6.png

子查询

  • WHERE
  • exists
WHERE

where 型子查询，如果是 where 列 =（内层 sql） 则内层 sql 返回的必须是单行单列，单个值。

where 型子查询，如果是 where 列 in（内层 sql） 则内层 sql 返回的必须是单列，可以多行

select goods_id,goods_name,shop_price from goods where goods_id = (select max(goods_id) from goods);

select goods_id,goods_name,shop_price from goods where goods_id in (select max(goods_id) from goods group by cat_id);
FROM
select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id;
EXISTS

exists 型子查询是指外层 sql 的结果，拿到内层 sql 去测试，如果内层 sql 成立，则该行取出

select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);

select cat_id,cat_name from category where exists (select 1 from goods where goods.cat_id=category.cat_id);
区别

in 是先执行子查询然后得到子查询的结果集，再用子查询的结果去匹配外部表。（子查询结果的长度 * 外表索引的时间）

exists 是先遍历循环外表，然后看外表中的记录有没有和内表中的数据一样的，如果一样就将结果放到结果集中。（外表结果集长度 * 内表索引时间）

结论：外表大，子查询结果小，就用 in ；如果外表小，子查询结果大 ，就用 exists；

not in 和 not exists 的区别

如果查询语句使用了not in ， 那么对内外表都是进行的全表的扫描，没有用到索引；

而 使用 not exists 的子查询依然用到表上的索引，所以无论哪个表大，用 not exists 都比 not in 要快！

连接查询

  • 外连接
  • 内连接
  • 合并 UNION
外连接内连接
7.png
8.png

语句

左(右)外连接

==外连接及简写形式==

SELECT <select_list>
FROM tableA AS A
LEFT OUTER JOIN tableB AS B #完整写法
ON A.key = B.key

SELECT <select_list>
FROM tableA AS A
RIGHT JOIN tableB AS B      #简写,省略OUTER
ON A.key = B.key

交集

  • ==显式内连接==
  • 隐式内连接
SELECT <select_list> 
FROM tableA AS A 
INNER JOIN tableB AS B  #显示
ON A.key = B.key

SELECT <select_list> 
FROM tableA AS A 
JOIN tableB AS B        #隐式     
ON A.key = B.key

差集

(笛卡尔积中把B.key is NULL 的留下)

SELECT <select_list>
FROM tableA AS A
LEFT OUTER JOIN tableB AS B #外连接
ON A.key = B.key
WHERE B.key IS NULL

SELECT <select_list>
FROM tableA AS A
RIGHT JOIN tableB AS B      #外连接简写
ON A.key = B.key
WHERE A.key IS NULL

全外连接(笛卡尔积)

==mysql不支持这个语法==

SELECT <select_list>
FROM tableA AS A
FULL OUTER JOIN tableB AS B #不支持
ON A.key = B.key

SELECT * FROM tableA AS A 
LEFT JOIN tableB AS B ON A.key = B.key

UNION                       #改用union拼接左和右连接,自动去重重复

SELECT * FROM tableA AS A 
RIGHT JOIN tableB AS B ON A.key = B.key

补集连接

SELECT <select_list>
FROM tableA AS A
FULL OUTER JOIN tableB AS B
ON A.key = B.key
WHERE A.key IS NULL
OR B.key IS NULL
合并 UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须==拥有相同数量的列==。列也必须==拥有相似的数据类型==。同时，每个 SELECT 语句中的==列的顺序必须相同==。

  • UNION 去除重复数据
  • UNION 不去除
SELECT <select_list> FROM tableA AS A
UNION       #去重 
SELECT <select_list> FROM tableB AS B

SELECT <select_list> FROM tableA AS A
UNION ALL   #不去重复
SELECT <select_list> FROM tableB AS B

主从复制

从机通过主机的日志文件log-bin来进行数据同步

步骤

  • master将改变记录到二进制日志文件bin-log，这些记录称为二进制日志事件，binary log event
  • slave 将master的binary log event 拷贝到自己的中继日志relay log
  • slave 重做中继日志的事件，将改变应用到自己的数据库中，mysql复制是异步的串行的
22.png

配置

  • mysl版本一致
  • 主从都配置在[mysqlid]结点下，都是小写
  • 修改主机my.ini
server-id =  1          #主服务器唯一id
log-bin=.../mysqlbin    #启用二进制日志
log-err=.../mysqlerr    #启用错误日志
tmpdir=.../             #临时目录,可选
read-only=0             #主机为0,读写都可以,可选
binlog-do-db=test       #需要备份的数据库,可选
binlog-ignore-db=mysql  #不需要备份的数据库,可选
binlog_format=statement #日志格式(默认statement)

binlog_format四种格式

类型 示例
statement 表达式,复制now()函数会随时间变化而变化 set time=now()
row 行模式,记录改变,效率低 123 time->1.5<br />456 time->1.5
mixed 混合,函数使用row
  • 从机my.cnf
log-bin=mysql-bin       #默认开启,复制主机用
#serverl-id=1           #关闭id1
server-id=2             #开启id2

  • 主机从机关闭防火墙?开放端口

  • 在windows主机上建立账户授权slave授权mysql的某个用户的指定ip登陆权限

GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.0.118' IDENTIFIED BY '123456'

FLUSH PRIVILEGES        #刷新权限

SHOW MASTER STATUS
  • 在linux从机上配置需要复制的主机
GRANT MASTER TO 
MASTER_HOST='192.168.0.1',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG='mysqlbin',
MASTER_LOG_POS=123      #每次启动先查询master的position


start slave
show slave status
#Slave_IO_Running:YES
#Slave_SQL_Running:YES  必须是yes

stop slave

分库分表

https://www.sohu.com/a/341544423_728045

垂直分表

根据业务把一个表中的字段（Field）分到不同的表中。这些被分出去的数据通常根据业务需要，例如分出去一些不是经常使用的字段，一些长度较长的字段。

一般被拆分的表的字段数比较多。主要是避免查询的时候出现因为数据量大而造成的“跨页”问题。

一般这种拆分在数据库设计之初就会考虑，尽量在系统上线之前考虑调整。已经上线的项目，做这种操作是要慎重考虑的。

水平分表

Mycat

数据库中间件,所有的读写操作都发给mycat

功能

  • 读写分离
  • 数据分片
  • 多数据源整合
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 221,135评论 6赞 514
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 94,317评论 3赞 397
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 167,596评论 0赞 360
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 59,481评论 1赞 296
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 68,492评论 6赞 397
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 52,153评论 1赞 309
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,737评论 3赞 421
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,657评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 46,193评论 1赞 319
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 38,276评论 3赞 340
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,420评论 1赞 352
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 36,093评论 5赞 349
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,783评论 3赞 333
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,262评论 0赞 23
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,390评论 1赞 271
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,787评论 3赞 376
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,427评论 2赞 359