Sql总结
sql语句中加单引号的地方
sql中涉及varchar值得时候
like后面的内容必须加单引号，因为是字符串匹配
sql巩固
count/sum函数
count(值)，如果这个值不为null则计1，值为null则计0
sum(值),会对这个值进行累加，值为null，结果为null。因此对字段先做IFNULL(字段，代替值)的判断
条件控制语句
case when 条件 then 值 else 值 end，在操作完某个字段后，形成的结果可以对其再进行操作， when .. then.. 可以有多个，如分组聚合，count函数等
select if(表达式，结果一，结果二)
日期
-
当前时间
当前日期函数：now()
当前日期：current_date
当前时间：current_time
日期格式化函数：date_format(列名/值，格式)
-
查询日期之前，之后函数
DATE_SUB（值，INTERVAL 3 MINUTE）
DATE_ADD（值，INTERVAL 3 DAY）
INTERVAL表示时间间隔
字符串
字符串的拼接：concat(str1,str2..)
去除空格：trim(字段)
替换：replace（字段，要替换的值，替换之后的值）
计算字符串的长度length(str),单位字节
将字符串转换成date对象:STR_To_DATE(str,格式)
-
字符串截取
SUBSTR（字符串，截取的开始位，截取的结束位）
LEFT（字符串，位数）
RIGHT（字符串，位数）
将查询的结果直接导入某个表
- insert into 表名 查询的结果
将两个结果集整合成一个结果集
要求：每个结果集的字段一致
使用：将两个查询的sql直接用UNION ALL（不会去重）或UNION（会去重）连接即可，sql语句用括号括起来
联合查询
-
外连接
不管字段有没有值，是以左表/右表为主，查出来的结果可以为null
左外：select 字段名称 from 表1 left join 表2 on 条件 where 关联
-
内连接
隐式内连接
-
显示内连接
- Inner join
显示内连接相当于省略了Inner join， where换成了on
相比于外连接，参与查询的字段都有值才可显示
联合索引
按左匹配
跟where后面的条件顺序无关，但不能少了加了索引的字段（如果说第一索引在，后面还可以加任何索引，但是只走第一个索引）
sql中的不等于
数据库、表结构优化
主从同步，读写分离
主数据库可读可写，从数据库只读不写
存在问题：mysql默认采用异步复制，在数据库压力大的情况下，数据同步可能存在延迟
通过binlog 日志实现数据同步
缓存
-
mysql缓存
-
查询缓存的相关配置:SHOW VARIABLES LIKE '%query_cache%';
- OFF（0）：关闭 ，不使用查询缓存
-
ON（1）：总是打开 ，始终使用查询缓存
-
DEMAND（2）：按需使用查询缓存，只有明确写了SQL_CACHE的查询才会写入缓存
- 查询缓存在数据库变化频繁的情况下会降低性能
-
分布式缓存
- redis缓存
-
服务本地缓存
- java服务本地缓存
分库分表
按业务拆分
按数据拆分
-
分库分表采用的中间件
-
Sharding-jdbc
- 优点：不用部署，运维成本低，不需要代理层的二次转发请求，性能高， 缺点：耦合 Sharding-jdbc的依赖，遇到升级问题需要各个版本重新升级再进行版本发布
-
Mycat
- 缺点：需要部署，运维成本高 优点：如遇升级问题，只要修改自己的中间件即可
-
-
数据迁移：从未分库分表-分库分表：
a、停机迁移
-
b、双写迁移方案
- 所有写库，增删改操作除了对老库进行修改，还要对新库进行修改，然后项目部署后，再利用导数工具读老库写新库
nosql非关系型数据库
redis、mongoDB
K:V存储
海量数据的存储
newSql
- 不仅保持了nosql对海量数据的存储能力，还保持了传统数据可的ACID和sql的特性
存储引擎优化
-
InnoDB
事务型数据库首选引擎
支持事务，支持行锁，支持外键
B+树
-
MyISAM
较高的插入、查询速度，不支持事务
支持表锁
-
hash索引
hash索引不支持范围查询
查询数据全表扫描
-
Memory
存放临时数据，数据量不大
数据存放在内存中，表结构存储在磁盘中
基于表
数据归档
- 定时清理不需要的数据，从实时数据库中清除
数据中台、数仓
- 都不在查询实时业务生产数据库，搜索业务从实时业务生产数据库中剥离，采用canal、日志、定时任务进行同步
分表
-
水平拆分
- 可以对id取模进行拆分
-
垂直拆分
- 对经常查询的字段进行分一个表
sql优化
分页查询优化
适合主键自增，而且主键不能断层
-
在索引上完成排序分页，根据主键回原表查询所需要的的内容
image.png
建立索引，条件要命中索引字段，没有命中索引的sql不执行
字段能小则小
普通索引不建立在大字段上
索引建立在离散度大的字段上
批量插入
-
load data
- 文件有固定的格式
索引字段避免使用函数
在分组之后，禁止排序也可以提升效率
- 加上order by null 禁止默认排序
相同字段进行比较，否则可能索引失效
连接查询代替子查询
尽可能使用覆盖索引（查询的字段尽可能是建了索引的字段）
涉及到排序，尽可能结合覆盖索引使用using index排序方式
-
两种排序方式
using filesort
-
using index
- 效率高
可以使用union代替or
- union相当于求并集
手动指定索引名 use index（索引名）
排查慢查询
慢查询的表现
数据库数据同步慢
mysql的cpu、内存狂飙
查询sql的执行情况
-
show [full] processlist
image.png- 适合实时查询（查询正在执行sql的耗时）
终止慢查询
查看该sql是否是慢查询
-
EXPLAIN查看执行计划
image.png- key_len表示索引的长度，在不损失精度的情况下，越小越好
-
表现
image.png
show profile分析sql
-
SELECT @@have_profiling
- 查看profiles是否已经开启
-
查看最近sql的执行时间
- show profiles
-
show profile for query query_Id
-
查看某条sql的具体耗时
image.png
-
开启慢查询日志
回表索引
先定位主键，再定位行记录，扫描两遍索引树，效率更低
叶子节点存的是主键
索引使用B+树
原因：索引是按页存储的，相同数据量二叉树的高度比B+树高，高度越高，磁盘的IO次数越多，效率也越低，采用B+树减少IO 的次数
聚簇索引和非聚簇索引
非聚簇索引（辅助索引）
- 叶子节点存放的是主键值，访问数据需要两次查找
聚簇索引（主键索引）
- 按照每张表的主键构成一个B+树，叶子节点存放的表的数据
索引
索引介绍
索引其实也是一张表，保存了主键与索引字段，存储在磁盘，占用的空间比较大，不能放在内存
索引影响了增删改的效率，因为需要在增删改的时候维护索引
索引在mysql的存储引擎层实现
B树跟B+树的区别
B树
多路平衡搜索树，m叉类型的树
树中最多包含m个孩子
除根结点和叶子节点外，每个节点至少有ceil[m/2]个孩子
若根结点不是叶子节点，至少有两个孩子
所有叶子节点都在同一层
每个非叶子节点由n个key和n+1个指针组成，其ceil(m/2)-1<=n<=m-1
ps:根据ceil(m/2)-1<=n<=m-1 ，当n超出范围后，中间节点分裂成父节点，两边节点分裂
B+树
n叉B+树最多含有n个key，而B树最对含有n-1个key
所有的数据存在叶子节点中，而非叶子节点看做是key的索引部分
叶子节点中含有所有key的信息，依key大小顺序排列，非叶子节点只为了提供查询
ps:mySql的B+树中，相邻叶子节点间增加了链表指针，提供了区间访问
B树相对于二叉树
- 原因：索引是按页存储的，相同数据量二叉树的高度比B+树高，高度越高，磁盘的IO次数越多，效率也越低，采用B+树减少IO 的次数
B+树相比于B树的优点
- 数据都在叶子节点，每次都要从root节点走到子节点，所以B+Tree查询更加稳定
ES相比于数据库的查询更快
倒排索引
-
结构
image.png
-
term dictionary
image.png
* 而我们的数据库只到这一层，因此查询速度没有ES快，ES有term index存在于内存中
-
term index
image.png -
文章链接
索引失效
1、联合索引按左匹配
- 跟where后面的条件顺序无关，但不能少了加了索引的字段
2、范围查询右边的列，不能使用索引
3、不要在索引列上进行运算
4、字符串不加单引号，索引失效
5、用or分割条件，or前面的字段有索引，or后面的字段没索引，会造成所有的索引失效
ps：and不会
6、以%开头的like模糊查询，索引失效
- 可以用覆盖索引解决此类问题，只查询有索引的字段
7、如果Mysql评估使用索引比全表扫描慢，则不使用索引（如根据某个字段查询，然而这个字段的某个值占用的比例特别大，就不会走索引）
8、in走索引， not in不走索引
9、is not null不管什么情况下都不会走索引,is null在字段允许为空时会使用索引
mysql服务端的优化
查询缓存开启优化
查看是否开启：SHOW VARIABLES LIKE 'query_cache_type'
默认关闭 需要在配置文件中配置开启
默认大小是1M
ps:可以在sql语句的字段前面指定SQL_NO_CACHE 查询sql语句不走缓存
Mysql锁
按对数据操作的类型分
-
读锁（共享锁）
- 针对同一份数据，多个读操作同时进行，互不影响
-
写锁（排它锁）
- 当前操作没有完成之前，会阻断其他读锁和写锁
PS：读锁会阻塞写，不会阻塞读，写锁会阻塞读、写
按对数据操作的粒度分
-
表锁
-
MyiSAM的默认存储引擎
- 不会出现死锁，发生锁冲突概率高，并发度低
-
-
行锁
-
InnoDB的默认存储引擎
- 会出现死锁，发生锁冲突的概率低，并发度高
-
PS：索引失效，行锁升级成表锁
Hash索引相比于B树索引
hash表
-
适合等值查询
通过找到一定的hash算法找到即可
不能使用范围查询
-
始终是全表扫描
- 因为有可能hash值一样，使用全表扫描所有对应的hash值，然后再找出对应的值
