MySQL基础知识

1.基础知识
decimal能存储比bigint更大的整数;float和double只能做近似计算
经常变更的短字符串用char更好更高效。
text和blob查询会使用临时表,导致严重的性能开销。 单独查询或者单独垂直分表
timestamp y-m-d h:i:s 比datetime空间效率更高。
数据库建模 powerdesigner 重要,不要只写一个数据字典
mysql的瓶颈是磁盘IO;一台双核的mysql压力200个请求。

2.不同表的处理方式:
小表,全表扫描效率更高
500w以内的中大型表,使用索引优化。2亿数据别说使用索引,连SQL语句都执行不动。
大型项目就要 分库分表了。mycat
分区一般扩展性差维护成本高,一般不搞。
分表可以不用中间件,分表查询有五种方式,中间件只是其中一种方式。

3.索引:
ALTER TABLE table_name ADD INDEX index_name (column_list)
Mysql优化器是有规则的,尽可能淘汰更多数据
explain分析单独sql,优化 select type
不是索引越多越好,根据业务需求,对重要的SQL建立响应的组合索引。
索引将随机IO变成顺序IO
索引大大提高了查询速度,降低了写的速度。=》读写分离,通过二进制日志主从同步
失效条件:1.范围查询;2.结果集超过50%=>全表扫描

4.索引类型:强制索引查询 force index(xxx)
普通索引
唯一索引:限制列唯一,可以有多个,可以为空
主键索引:特殊的唯一索引,不能为空
组合索引:多个列组合在一起创建索引(组合唯一索引:两个列不能都一样、组合普通索引)
最左索引匹配原则,有失效条件

5.索引的建立原则:
1.出现在where子句的列或链接子句的列,select后面的列不需要
2.索引列的基数越大效果越好
3.字符串索引,制定前缀长度可以节省大量的索引空间 计算全列选择性
4.根据情况创建复合索引,提高查询效率
5.避免创建过多索引,占用磁盘空间,降低读写效率
6.逐渐选择较短的数据类型

6.索引生效问题:
1.复合索引前缀原则:key(a,b,c) a ab abc生效 ac bc不生效
2.like前面有%不生效;字段 is null可以使用索引
3.or的字段都有索引才能使用
4.列是字符串需要加引号才能使用索引

7.优化方案
MySQL慢查询分析工具pt-query-digest
Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具。
重复数据用redis缓存,复杂查询用es综合查询
关联查询,适当分解,减少锁的竞争;一般sql耗时10ms,复杂sql不超过100ms
on using子句的列要有索引,字段一样用using,不一样用on
ditinct group by要使用索引
关联查询代替子查询;组合查询 union 切分成多个查询,减少单次请求的数据量

8.分库分表带来的问题:
分布式ID寻址
分布式事务问题
mycat
3pc tcc:事务模型
分布式事务中常见的三种解决方案
(一)、基于可靠消息的最终一致性方案
(二)、TCC事务补偿型方案
(三)、最大努力通知型

9.覆盖索引和回表
innodb两大类索引:聚集索引和普通索引。
聚集索引
innodb有且仅有一个聚集索引:
一般主键就是聚集索引
叶子结点存储行记录;查询时可以直接定位到行记录
普通索引:
叶子结点存储主键值
查询时,先查到主键值,再扫描主键的索引树定位到行记录。

回表查询:
查询时,先查到主键值,再扫描主键的索引树定位到行记录。性能相对于值扫描一遍聚集索引的性能要低。
索引覆盖:
索引覆盖是避免回表查询的优化策略。
将要查询的数据作为索引列建立普通索引或联合索引

10.hashmap和tablemap
HashTable继承了被弃用的类
Hashtable是线程安全的,效率比较低
HashMap大道至简的数据类型

1.事务并发问题
脏读:事务A读取了事务B更新的数据,然后事务B回滚了。
不可重复的:事务A多次读取同一数据的值不同。
幻读:事务A多次读取同一段数据的个数不同

2.事务隔离级别
读未提交:脏读
不可重复读:
可重复读:依然有幻读的问题
串行化:性能问题

3.innodb行级锁的条件????
行级锁建立在索引的基础上,索引失效会导致行级锁变表级锁。

4.B+tree索引 hash索引
hash索引:
有局限,只满足 = in <=>安全等于(null=null=>null null<=>null=>1)
无法避免排序,
不能避免表扫描
对于组合索引,hash索引是把组合索引建合并后在一起计算hash值,当只用前一个索引建时hash索引不能被使用。
遇到大量hash值相同时不见得比B+tree性能高
只用heap和memory引擎才支持hash索引
B+tree索引:
等值查询且键值唯一时,hash才有可能比b+tree性能高。
范围查询检索,hash索引没用。
hash没办法利用索引完成排序。
hash索引不支持多列联合索引的最左侧匹配原则。

5.聚集索引和非聚集索引
聚集索引 表记录的排列顺序和索引的排列顺序一直,查询效率快,修改慢。插入式会对数据页重新排序
非聚集索引也适用于分组、大数目的不同值、频繁更新的列中,但这些情况不适合聚集索引。

6.悲观锁乐观锁
悲观锁
通常使用 select for update实现悲观锁。
悲观锁会锁住所有扫描过得行,如果不走索引,走了全表扫描可能会造成全表锁定。

乐观锁
完全是业务逻辑实现。
给表加一个版本号字段是实现乐观锁的一种方式
乐观锁一般用于失败可能性比较小的地方。能提高并发系统

7.主从复制
同步复制、异步复制、半同步复制
主从复制常见七个问题
1.主从复制,从节点不可写
2.多个slave的作用:分担压力、容灾高可用
3.slave的缓存可以放在redis等专用缓存服务器中
4.master满足不了写操作:分库-垂直拆分,分表-水平拆分

8.慢查询解决:
1.slowquerylog打开
2.slowquerylog_file
3.longquerytime

9.什么时候造成死锁:表级锁不会产生死锁,主要是innodb
死锁解决办法:
1.查出线程并杀死
2.设置锁超时时间
3.死锁检测来进行处理死锁

10.mysql高并发环境解决方案
1.分库分表
2.集群方案
3.读写分离
4.热数据给redis等缓存。

11.数据库崩溃时事务恢复
undo log 旧数据的备份,多版本并发控制 为了实现事务的原子性数据操作签的备份。可以用来恢复到事务开始之前的状态
redo log 新数据的备份,将数据恢复到最新状态。

12.腾讯
mysql主从延迟怎么解决
1.半同步复制
2.主库配置syncbinlog=1
3. 5.7的并行复制

某个表千万数据,CRUD比较慢
表拆分
保证查询字段顺序尽量和组合索引的顺序一致
单表进行SQL优化:拆分请求,减少单次请求的数量,缩短锁的锁定。

高并发下,安全的修改一行数据
1.悲观锁 乐观锁
2、队列缓冲
13.阿里
为什么innodb的索引key长度不能太长:
每个页存放key的数目变少,间接导致索引树的页数目变多,索引层次增加,影响整体查询变更的效率。

mysql数据如何恢复到任意时间点
需要定时做全量备份,有备份增量的binlog

14.百度
关系型数据库采用的数据结构
二维表(非线性数据结构)、B+TREE索引(树形数据结构)、hash(索引集合数据结构)
数据结构使用二维表存储

数据库索引的实现
B+TREE索引(树形数据结构)、hash(索引集合数据结构)

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付 9.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值