MySQL数据库进阶

MySQL

一、InnoDB和MyISAM存储引擎区别

InnoDB：支持事务处理，支持外键，支持行锁和表锁，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比

较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的

更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择

MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6EgkOVwH-1625896924841)(G:\Data\新技术学习\数据库\MySql\MySQL.assets\1619683675809.png)]

二、日志文件

binlog

binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描

述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到 binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。 生产中开启 数据备份、恢复、主从

慢查询日志（slow query log）

SQL调优 定位慢的 select

2.1查看日志开启情况

show variables like ‘log_%’;

存储位置

SHOW VARIABLES LIKE ‘%datadir%’;

三、MySQL索引

优点：可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。 – 检索

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。 --排序

缺点：

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率**。比如每次对表进行增删改操作，

MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

四、B树和B+树

B和B+的区别

B树和B+树的最大区别在于非叶子节点是否存储数据的问题。

B树是非叶子节点和叶子节点都会存储数据。

B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向 的，也就是有顺序的。

五、索引使用

哪些情况需要创建索引

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引 where

3、多表关联查询中，关联字段应该创建索引 on 两边都要创建索引

4、查询中排序的字段，应该创建索引 B + tree 有顺序

5、覆盖索引 好处是？ 不需要回表 组合索引

6、统计或者分组字段，应该创建索引

哪些情况不需要创建索引

1、表记录太少 索引是要有存储的开销

2、频繁更新 索引要维护

3、查询字段使用频率不高

索引失效分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vulenhH5-1625896924843)(G:\Data\新技术学习\数据库\MySql\MySQL.assets\1619686443439.png)]

六、MySQL锁

七、MySQL优化

1、打开mysql慢查询

slow_query_log=ON

long_query_time=3

slow_query_log_file=/var/lib/mysql/slow-log.log #存储路径

2、查看慢查询文件

3、执行explain分析语句

4、服务器层面优化

扩大buffffer pool 理论上内存的3/4或4/5

修改 my.cnf

innodb_buffffer_pool_size = 750M

5、sql层面设计优化

5.1设计中间表**，一般针对于**统计分析功能，或者实时性不高的需求（OLTP、OLAP）

5.2为减少关联查询，创建合理的冗余字段（考虑数据库的三范式和查询性能的取舍，创建冗余字段还

需要注意数据一致性问题）

5.3对于字段太多的大表，考虑拆表（比如一个表有100多个字段） 人和身份证

5.4对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介 绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）

5.5每张表建议都要有一个主键（主键索引），而且主键类型最好是int****类型，建议自增主键（不考虑

分布式系统的情况下）。

八、集群搭建读写分离

字段单独拆解到另一个表中，使用商品ID关联）

5.5每张表建议都要有一个主键（主键索引），而且主键类型最好是int****类型，建议自增主键（不考虑

分布式系统的情况下）。

八、集群搭建读写分离