MySQL

最新推荐文章于 2023-10-27 14:58:14 发布

zheng_chang_wei

最新推荐文章于 2023-10-27 14:58:14 发布

阅读量784

点赞数

分类专栏： mysql 我的笔记文章标签： mysql

本文链接：https://blog.csdn.net/zheng_chang_wei/article/details/115326034

版权

我的笔记同时被 2 个专栏收录

5 篇文章 1 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

1、MySQL优化

1.1 表结构优化

创建合适字段，比如：IP地址可以用无符号int存储，IP转数字函数inet_aton()，数字转IP函数inet_ntoa()

1.1.1 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

1.1.2 日期和时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

1.1.3 字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

text和varchar的区别?

1.2 索引优化

1.2.1 哪些情况下适合建立索引

主键自动建立唯一索引

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

查询中与其他表关联的字段，

查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度

查询中分组字段

1.2.2 哪些情况不要创建索引

表记录太少

经常增删改的表

区分度比较低的字段

1.3 sql优化

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写 *；

不等空值还有 OR，索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

2、分析sql执行慢的原因

1、慢查询的开启并捕获（mysqldumpslow）

2、explain+慢SQL分析

3、show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况

4、SQL数据库服务器的参数调优

2.1 Explain

Type：访问类型，system>const>eq_ref>ref>range>index>ALL

Key: 使用的键（索引）

Extra: Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询；

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

3、Mysql悲观锁和乐观锁

悲观锁加锁：用synchronized，Lock或先执行Select······ · ·for update，再执行update

乐观锁：判断版本号

UPDATE table SET number=number-1 WHERE product_id=#{productId} AND number=#{number}

4、MVCC

多版本控制，在 Read Committed 和 Repeatable Read两个隔离级别下工作

https://blog.csdn.net/Waves___/article/details/105295060

5、innodb和myisam区别

InnoDB支持事务，MyISAM不支持
InnoDB支持外键，而MyISAM不支持。
InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
InnoDB是聚集索引，数据文件是和索引绑在一起的；MyISAM是非聚集索引，索引和数据文件是分离的
Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI； Innodb：frm是表定义文件，ibd是数据文件；Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

6、BTree和B+Tree的区别

有关b树的一些特性，注意与后面的b+树区分：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个节点中；
搜索有可能在非叶子节点结束；
其搜索性能等价于在关键字全集内做一次二分查找；

b+树的特征：

非叶子节点关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
所有的叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接。

b+树相比于b树的查询优势：

b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

7、tinyint(1)和tinyint(4)的区别

都占1个字节，括号内为显示长度

类型	范围（有符号）	范围（无符号）	大小
tinyint	（ -128 ，127）	（0-255）	1

（1）加zerofill
当写入的值没有达到长度时，左边用零填充
（2）当使用整合mybatis使用
使用mybatis查询时查出tinyint(1)的值是Boolean类型
原因： TINYINT(1) 只是在显示的时候作为一个位进行输出
解决：1. 设置成TINYINT(4)则可以正常查询出值。2.jdbcUrl添加参数：tinyInt1isBit=false（默认为true）

8、MySQL中 int(11)和int(10)有没有区别

https://blog.csdn.net/ZBylant/article/details/86572567

9、常用函数

http://c.biancheng.net/mysql/function/

10.数据库三大范式是什么

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

https://zhuanlan.zhihu.com/p/72197799

https://www.cnblogs.com/gongcheng-/p/10901824.html

11.事务的传播机制

propagation_requierd：如果当前没有事务，就新建一个事务，如果已存在一个事务中，加入到这个事务中，这是最常见的选择。
propagation_supports：支持当前事务，如果没有当前事务，就以非事务方法执行。
propagation_mandatory [ˈmændətəri]：使用当前事务，如果没有当前事务，就抛出异常。
propagation_required_new：新建事务，如果当前存在事务，把当前事务挂起。
propagation_not_supported：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
propagation_never：以非事务方式执行操作，如果当前事务存在则抛出异常。
propagation_nested [ˈnestɪd]：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与propagation_required类似的操作，嵌套事务一个非常重要的概念就是内层事务依赖于外层事务。外层事务失败时，会回滚内层事务所做的动作。而内层事务操作失败并不会引起外层事务的回滚。

https://www.cnblogs.com/myseries/p/10800430.html

12.事务失效

（1）数据库不支持事务

（2）没用到代理

（3）没开启事务管理器

（4）设置事务传递性为 not_supported

（5）方法不是public


    @Autowired
    private UserRepository userRepository;
    
    // 不回滚
    @Transactional
    public void test1() throws Exception {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        saveUser(user);
        throw new Exception("test1 error");
    }

    // 不回滚
    @Transactional
    public void test11() throws Exception {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        saveUser(user);
        throw new BusinessException("test11 error");
    }

    // 回滚
    @Transactional(rollbackFor = Exception.class)
    public void test2() throws Exception {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        saveUser(user);
        throw new Exception("test2 error");
    }

    // 回滚
    @Transactional(rollbackFor = Exception.class)
    public void test21() throws Exception {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        saveUser(user);
        throw new BusinessException("test21 error");
    }

    // 回滚
    @Transactional
    public void test3() {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        saveUser(user);
        throw new RuntimeException("test3 runtime error");
    }

    // 不回滚
    @Transactional
    public void test4() throws Exception {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        test41(user);
        throw new Exception("test4 error");
    }

    @Transactional(rollbackFor = Exception.class)
    public void test41(User user) {
        saveUser(user);
    }

    // 不回滚
    public void test5() throws BusinessException {
        test6();
    }

    // 回滚
    @Transactional(rollbackFor = Exception.class)
    public void test6() throws BusinessException {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        saveUser(user);
        throw new BusinessException("test6 error");
    }

    // 回滚
    @Transactional(rollbackFor = Exception.class)
    public void test7() throws BusinessException {
        test8();
    }

    public void test8() throws BusinessException {
        User user = new User(1, "15000000000", "d243ewa", "Comma");
        saveUser(user);
        throw new BusinessException("test8 error");
    }

    public User saveUser(User user) {
        return userRepository.save(user);
    }

zheng_chang_wei

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL

MySQL优化1、慢查询的开启并捕获（mysqldumpslow）2、explain+慢SQL分析3、show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况4、SQL数据库服务器的参数调优ExplainType：访问类型，system>const>eq_ref>ref>range>index>ALLKey: 使用的键（索引）Extra: Using temporary：表示MySQL需要使用临时表来存储结果集，常
复制链接

扫一扫