Mysql总结

yangyongdehao30

已于 2025-04-03 10:04:16 修改

阅读量832

点赞数 1

分类专栏： DB sql 文章标签： mysql

于 2018-08-06 17:09:48 首次发布

本文链接：https://blog.youkuaiyun.com/yangyongdehao30/article/details/81457459

版权

DB 同时被 2 个专栏收录

3 篇文章

订阅专栏

sql

2 篇文章

订阅专栏

一个web开发者，对mysql的了解必不可少，以下是个人以程序员角色对mysql的总结

1.常用函数
in 、exists、 not in 、not exists
in是子查询，先遍历内部表，然后外部表来匹配
exist 是函数查询，直接遍历外部表
eg. select A.id from A where A.pid in
   (select B.id from B)
执行计划：   1.select B.id from B >> 1 2 3
2.select A.id from A where A.pid=1
select A.id from A where A.pid=2
select A.id from A where A.pid=3
select A.id from A where exists(select B.id from B where B.id=A.pid)
执行计划：   1.select A.id,A.pid from A >> id1 pid1,id2 pid2,id3 pid3
2.select id1 from B where B.id=pid1
select id2 from B where B.id=pid2
select id3 from B where B.id=pid3

所以用in还是exists，得看内表和外表的数据大小，内表小用in，外表小用exists

sum count min max 等内聚函数，对null值无效
select count(null) from dual >> 0
select count(1) from dual >> 1

substring substr left right replace ascii bin(二进制) concat 不用多说

时间函数 days（返回天数） Month（返回月数）
时间运算 AddDate AddTime
通用时间运算 select now() - Interval 1 Month/Day/Hour/Minute/Second from dual
select DATE_FORMAT(now(),'%y%m%d %H%i%s');

2.字段理解
int（10）和int（2） int（20）
int类型，括号中为显示长度
实际占用长度一致，eg. int(2) 存入1，实际0000000001，显示01，不使用0填充，显示1
int类型最大长度为10，2的32次方大小，故最多到4亿(4个字节，32位)
要获得更大数值，采用bigint（8个字节，64位，最长20长度）
3.sql优化
先说说5.7版本sql执行器优化点：对in的索引支持，自动调节where语句执行顺序，count（*）自动转换为count(id)
所以，在5.7以前的版本里面需要考虑的问题，在5.7里面相对比较少了，主要在于join的主表选择，索引字段选择，以及索引类型，in exists 等
4.文件结构
mysql数据库有几个模块：
   .ibd 表数据文件 .frm表结构文件 ibdata1 共享表空间
   日志：错误日志慢日志查询日志 binlog（一般要开）事务日志（undo_log redo_log）replay_log
5.主从复制
   my.cnf配置中不标记默认为master，标记为slave，则为从库，可以选择master ip 端口库名表名
   订阅主库的binlog，到自己的replaylog中，然后自行replaylog，所以涉及3个线程：binlog线程，copy线程，replaylog执行线程
6.存储机制
外部一条更新语句，是如何到达数据库，并存入磁盘的呢，了解这个过程之后，有很多问题就迎刃而解了
   1.进入优化器：解析sql，先找到where等关键字，形成带所有字段的更新语句
   2.记录undo_log（有事务）
   3.数据提交
   4.记录redo_log(有事务)
   在没有设置持久化事务时，不能保证事务执行成功，就记录了undo_log和redo_log
   设置持久化事务又会严重影响性能，所有当数据库需要还原时，也是有不准确的
   另外：数据提交完毕后并不是立马写入磁盘，而是写入buffer，mysql通常设置buffer为物理内存的75%，而写入磁盘则是根据系统默认设置写入的，所以当出现异常宕机时，不能保证绝对数据完整性

插入一个常用配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 10G
#
# Remove the leading “# “ to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

#skip-grant-tables

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#慢日志 1开启 0关闭
slow_query_log=1
max_connections=20000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=1000
# 服务端使用的字符集默认为UTF8 mysql8需设置为UTF8MB4
character-set-server=UTF8MB4

# 默认使用“mysql_native_password”插件认证 mysql8不再启用此插件，删除即可
default_authentication_plugin=mysql_native_password

# 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘
# 25%~50%
innodb_max_dirty_pages_pct=30

# 后台进程最大IO性能指标
# 默认200，如果SSD，调整为5000~20000
innodb_io_capacity=2000

# 默认2，单位s。慢查询时间。建议0.1~0.5
long_query_time=0.3

# 默认8小时。交互等待时间和非交互等待时间
# 建议300~500s,两参数值必须一致，且同时修改
interactive_timeout=500
wait_timeout=500

#日志大小
innodb_log_file_size=256M
#日志缓存大小
innodb_log_buffer_size=12M

innodb_flush_log_at_trx_commit=2
#innodb_flush_method
#thread_cache=8
#innodb_autoextend_increment=128M

#这里确认是否起用压缩存储功能
innodb_file_per_table=1
#innodb_file_format=barracuda #mysql 8 不支持该功能
#决定压缩程度的参数，如果你设置比较大，那么压缩比较多，耗费的CPU资源也较多；
#相反，如果设置较小的值，那么CPU占用少。默认值6，可以设置0-9#
innodb_compression_level=6
#指定在每个压缩页面可以作为空闲空间的最大比例，
#该参数仅仅应用在设置了innodb_compression_failure_threshold_pct不为零情况下，并且压缩失败率通过了中断点。
#默认值50，可以设置范围是0到75
innodb_compression_pad_pct_max=50


[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3388
default-character-set=utf8