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
- 单值索引:索引中只有一个列,一个表可以有多个单值索引
- 复合索引
https://www.jianshu.com/p/35588ecf33c1
[图片上传失败...(image-aa78ae-1595646160604)]
- 索引的值必须唯一,可以为null
B+树实现索引
主键索引查询
区间查询
找到最小值,然后顺序遍历结点找到最大值
优化
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
| 类型 | 示例 | 示例 |
|---|---|---|
| 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
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
表名
锁
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%' #
按粒度
- 行锁(写)
- 表锁(读)
- 页锁(mysql)
按对数据操作类型
- 读锁(共享锁)
- 写锁(排他锁)
锁级别
- readuncommitted 读未提交
- committed 读已提交
- repeatable 可重复读(mysql默认)
- serializer 序列化
读锁
连接1锁住a表,a表不可写
连接1==无法读其他表==,(主从复制,备份数据,上a表锁,会导致事物只能限定在当前表中,避免死锁出现)
连接2写a表会被阻塞
写锁
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条件中用不到的字段
查询
全貌
执行流程
子查询
- 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
外连接内连接
语句
左(右)外连接
==外连接及简写形式==
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复制是异步的串行的
配置
- 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
功能
- 读写分离
- 数据分片
- 多数据源整合
