mysql数据库笔记

数据库–
查找磁盘物理文件 IO操作
CPU–缓存(L1-L2-L3)–内存–SSD–网络–硬盘

数据库IO

响应时间
带宽吞吐量60GB/s

数据库操作空间
CPU,cache,内存:
缓存数据访问,比较,排序,事物检测,SQL解析,函数,逻辑运算

SSD,机械硬盘:
数据访问,数据写入,日志记录,大量数据排序,大表连接

网络:
结果数据传输,SQL请求,远程数据访问

数据库访问原则
1减少数据访问(减少磁盘访问)
2返回更少数据(分页)
3减少客户端与数据库交互次数(减少网络传输)
4减少服务器CPU开销
5数据库集群,增加资源

数据库存储引擎:
写入磁盘的方式

InnoDB
MyISAM
Memory
Blackhole
…
查看数据库引擎

show engines ;

查看数据表引擎

show table status from db0905;
show create table student;

my.ini文件

[Client]
port = 3306
 
[mysqld]
#设置3306端口
port = 3306
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
 
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

修改引擎

alter table student engine =MyISAM;

InonoDB引擎
事物机制支持提交,回滚,崩溃恢复
行级锁,减少锁定空间,增加并发性能
支持外键
数据和索引存储在一起,快速查询,减少IO访问

InnoDB每一条SQL语句都默认封装为一个事物,单独执行自动提交,
为提高效率,一般把多条SQL语句放在start transcation 和commit之间

#执行前
set autocommit =false
#执行后
set autocommit =false

行级锁

select *from student where sid=1 for update ;

锁定索引为1这行,可以查但不可以修改,不影响其他行

行锁失效:
锁定范围不确定时开启表锁

InnoDB

支持行锁
使用sphnix插件支持全文索引
频繁并发更新的表
支持事物
bin-log日志恢复
支持外键
主键查询性能高

innoDB使用了事物机制,多版本并发控制,在同一时间进行查询得到的结果不一定相同,因此count无法直接保存在表里

MyISAM引擎

支持数据直接拷贝复制
支持fulltext全文索引
数据跨平台使用
表锁
读取不占用大量存储资源
不支持事物
磁盘单独存储了表的count行数数据,统计行数不用遍历数据

count存在表里

对比	InnoDB引擎 -------------- MyISAM引擎
事务支持	支持-------------------不支持
锁定级别	行锁------------------表锁
外键约束	支持----------------不支持
索引数据存储	存储在一起-----------分开存储
崩溃恢复	支持----------------不支持

BLACKHOLE引擎**

• 任何写入到黑洞引擎的数据都会被丢弃,select语句的内容为空
• 服务器会记录BLACKHOLE表日志,用于复制数据到备份数据库

#改变引擎
alter table score engine =blackhole;
#查看引擎
show create table score;
#select查询语句为空
select *from score;

Memory引擎

• 数据放在内存中,无法持久化,默认使用hash索引,memory类型的表访问非常快
• 服务关闭后表中的数据会丢失
• 不支持事务
• 适合做临时表
• create temporary table tmp(a int) engine=memory;
  临时表只对当前连接生效

适用场景
1内容变化不频繁的代码表,统计操作的中间结果表
2目标数据比较小,频繁进行访问,在内存中存放太大的数据会造成内存溢出
3数据是临时的,而且必须立即可用到
存在memory表中的数据如果突然丢失也没太大关系

TokuDB

分形树存储结构
支持事务
行锁
压缩效率较高
适合大批量的insert场景
tokuDB需要另行下载

mysql cluster

• 分布式集群
• 数据节点间冗余,高可用
• 支持事务
• 设计上易于扩展
• 面向未来,线上慎用

Archive引擎

提供高速的插入和压缩功能

Federated引擎

将不同的MySQL服务器联合起来,逻辑上组成一个完整的数据库

performance schema引擎

1.MySQL数据库的存储引擎,存储在内存,没有持久化到磁盘存储

性能影响
不同的引擎
是否开启binlog
InnoDB是否关闭自动事务提交

---

innodb

innodb相关的磁盘文件

文件	名称	数量	位置
系统表空间	ibdata1	一个实例一个	innodb_data_home_dir
日志文件	ib_logfile0/1	一个实例两个(可配置)	innodb_log_group_home_dir
表定义文件	表名.frm	每个表一个	schema目录下
表数据文件	表名.ibd	如果innodb_file_per_table=1则每表一个	schema目录下

innodb系统表空间文件
ibdata1里存放什么:

• 回滚段
• 所有innodb表元数据信息
• double write,insert buffer dump等等

配置参数
show global variable like ‘%innodb%’

ignore_builtin_innodb	OFF
innodb_adaptive_flushing	ON
innodb_adaptive_flushing_lwm	10
innodb_adaptive_hash_index	ON
innodb_adaptive_hash_index_parts	8
innodb_adaptive_max_sleep_delay	150000
innodb_api_bk_commit_interval	5
innodb_api_disable_rowlock	OFF
innodb_api_enable_binlog	OFF
innodb_api_enable_mdl	OFF
innodb_api_trx_level	0
innodb_autoextend_increment	64
innodb_autoinc_lock_mode	1
innodb_buffer_pool_chunk_size	134217728
innodb_buffer_pool_dump_at_shutdown	ON
innodb_buffer_pool_dump_now	OFF
innodb_buffer_pool_dump_pct	25
innodb_buffer_pool_filename	ib_buffer_pool
innodb_buffer_pool_instances	1
innodb_buffer_pool_load_abort	OFF
innodb_buffer_pool_load_at_startup	ON
innodb_buffer_pool_load_now	OFF
innodb_buffer_pool_size	134217728
innodb_change_buffer_max_size	25
innodb_change_buffering	all
innodb_checksum_algorithm	crc32
innodb_checksums	ON
innodb_cmp_per_index_enabled	OFF
innodb_commit_concurrency	0
innodb_compression_failure_threshold_pct	5
innodb_compression_level	6
innodb_compression_pad_pct_max	50
innodb_concurrency_tickets	5000
innodb_data_file_path	ibdata1:12M:autoextend


#数据主目录
innodb_data_home_dir	"/data/mysql/node_1"
innodb_deadlock_detect	ON
innodb_default_row_format	dynamic
innodb_disable_sort_file_cache	OFF
innodb_doublewrite	ON
innodb_fast_shutdown	1
innodb_file_format	Barracuda
innodb_file_format_check	ON
innodb_file_format_max	Barracuda

#建议开启
innodb_file_per_table	1
innodb_fill_factor	100
innodb_flush_log_at_timeout	1
innodb_flush_log_at_trx_commit	1
innodb_flush_method	""
innodb_flush_neighbors	1
innodb_flush_sync	ON
innodb_flushing_avg_loops	30
innodb_force_load_corrupted	OFF
innodb_force_recovery	0
innodb_ft_aux_table	""
innodb_ft_cache_size	8000000
innodb_ft_enable_diag_print	OFF
innodb_ft_enable_stopword	ON
innodb_ft_max_token_size	84
innodb_ft_min_token_size	3
innodb_ft_num_word_optimize	2000
innodb_ft_result_cache_limit	2000000000
innodb_ft_server_stopword_table	""
innodb_ft_sort_pll_degree	2
innodb_ft_total_cache_size	640000000
innodb_ft_user_stopword_table	""
innodb_io_capacity	200
innodb_io_capacity_max	2000
innodb_large_prefix	ON
innodb_lock_wait_timeout	50
innodb_locks_unsafe_for_binlog	OFF
innodb_log_buffer_size	16777216
innodb_log_checksums	ON
innodb_log_compressed_pages	ON
innodb_log_file_size	50331648
innodb_log_files_in_group	2

#数据文件存在位置
innodb_log_group_home_dir	"/data/mysql/node_1"
innodb_log_write_ahead_size	8192
innodb_lru_scan_depth	1024
innodb_max_dirty_pages_pct	75.000000
innodb_max_dirty_pages_pct_lwm	0.000000
innodb_max_purge_lag	0
innodb_max_purge_lag_delay	0
innodb_max_undo_log_size	1073741824
innodb_monitor_disable	""
innodb_monitor_enable	""
innodb_monitor_reset	""
innodb_monitor_reset_all	""
innodb_old_blocks_pct	37
innodb_old_blocks_time	1000
innodb_online_alter_log_max_size	134217728
innodb_open_files	2000
innodb_optimize_fulltext_only	OFF
innodb_page_cleaners	1
innodb_page_size	16384
innodb_print_all_deadlocks	OFF
innodb_purge_batch_size	300
innodb_purge_rseg_truncate_frequency	128
innodb_purge_threads	4
innodb_random_read_ahead	OFF
innodb_read_ahead_threshold	56
innodb_read_io_threads	4
innodb_read_only	OFF
innodb_replication_delay	0
innodb_rollback_on_timeout	OFF
innodb_rollback_segments	128
innodb_sort_buffer_size	1048576
innodb_spin_wait_delay	6
innodb_stats_auto_recalc	ON
innodb_stats_include_delete_marked	OFF
innodb_stats_method	nulls_equal
innodb_stats_on_metadata	OFF
innodb_stats_persistent	ON
innodb_stats_persistent_sample_pages	20
innodb_stats_sample_pages	8
innodb_stats_transient_sample_pages	8
innodb_status_output	OFF
innodb_status_output_locks	OFF
innodb_strict_mode	ON
innodb_support_xa	ON
innodb_sync_array_size	1
innodb_sync_spin_loops	30
innodb_table_locks	ON
innodb_temp_data_file_path	ibtmp1:12M:autoextend
innodb_thread_concurrency	0
innodb_thread_sleep_delay	10000
innodb_tmpdir	""
innodb_undo_directory	.\
innodb_undo_log_truncate	OFF
innodb_undo_logs	128
innodb_undo_tablespaces	0
innodb_use_native_aio	ON
innodb_version	5.7.24
innodb_write_io_threads	4

innodb数据文件存储结构

• 索引组织表(聚簇表)

• 根据表逻辑主键排序

• 数据节点每页16k

• 根据主键寻址速度很快

• 主键随机insert插入操作效率差

• 主键递增的insert插入效率较好

• 没有主键的情况下系统会自动分配一个字符串主键

innodb数据块缓存池

buffer POOL

• 数据的读写需要经过缓存
• 数据以整页(16k)为单位读取到缓冲中
• 缓存
• 缓存中的数据以LRU策略换出
• IO效率高,性能好

innodb数据持久化与实务日志

redo log

• 事务日志实时持久化
• 内存变化数据(脏数据)增量异步刷出到磁盘
• 实例鼓掌靠重放日志恢复
• 性能好,可靠,恢复快

innodb_flush_log_at_trx_commit	1	0/每隔1s写入并持久化一次日志;1/每次commit都写入并持久化日志;2/每次提交日志写到内存,每1s持久化一次

数据库索引

索引是一种单独的,物理的对数据表中的一列或多列值进行排序的一种存储结构
索引提供指向存储在表中的指定列中的数据值的指针

不同的存储引擎会有不同的索引
InnoDB,MyISAM的索引都采用B+树数据结构

B+树

左节点<根节点<=右节点

1所有的关键字都出现在子节点的链表中,且链表中的关键字是有序的
2搜索只在叶子结点命中
3非叶子结点相当于叶子结点的索引,叶子结点是存储关键字数据的数据层

B+树做索引的原因
1.局部性原理与磁盘预读
当一个数据被用到时,其附近的数据也通常会马上被使用----程序运行期间所需要的数据通常比较集中.
磁盘顺序读取效率很高(不需要寻道时间,只需很少旋转时间)因此对于局部性的程序来说,预读可以提高I/O效率
2.B+树在数据库索引中的优化

• 数据库系统的设计者利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只要一次I/O就可以完全载入
• B+树中一次检索最多需要深度h-1 次I/O(根节点常驻内存)
• 一般实际应用中,广度d(树的分叉数)是非常大的数字,通常超过100;深度h非常小,通常不超过3

索引分类

1聚簇索引(InnoDB中的主键索引Primary Key)
InnoDB中聚簇索引和数据存储在一起
MyISAM中没有聚簇索引

2非聚簇索引(辅助索引或二级索引 Seconday Key 存放主键索引)
比聚簇索引多查找一次,先找到主键再找主键对应的数据
MyISAM是非聚簇索引

INNODB的插入缓冲(insert buffer)
对于非聚簇索引的插入和更新,不是每一次直接插入索引页中,而是首先判断插入的非聚簇索引页是否在缓冲池中,如果在,直接插入,否则放入插入缓冲区中,以一定的频率执行插入缓冲和非聚簇索引页子节点的合并操作,将多个插入合并到一操作中.

按照索引列的数量划分
1普通索引 index(xx) or key(xx) 索引类型normal
2唯一索引 unique index(xx)
3主键索引 primary key(xx)
4组合索引 查询条件用到不同的组合 name=? and age=?,用seq_in_index 2展示
5全文索引 like ‘%xx%’
6空间索引

查看索引

show index from tableName;

删除索引

drop index indexName on tableName;

alter table tableName drop index indexName;

添加索引

create table tableName(
id int,
name varchar(30),
sex tinyint,
indexType index indexName(columnName)
);

普通索引

create table tableName(
	id int,
	name varchar(30),
	sex int,
	index(name),
	key id_index(id) 
);

create index id_index on tableName(id);

alter table tableName
add index id_index(id);

主键索引

create table tableName(
	id int,
	name varchar(30),
	sex int,
	primary key(id)
);

或者

alter table tableName
add Primary key(id);
自增主键不连续的情况:
唯一主键冲突
事物回滚
InnoDB主键自增持久化是指
MySQL重启后从redo log日志中恢复

唯一索引

create table tableName(
	id int,
	name varchar(30),
	sex int,
	index(name),
	unique key id_unique_index(id)
);

alter table tableName
add unique index id_index(id);

组合索引

create table tableName(
	id int,
	name varchar(30),
	sex int,
	index name_sex_index(name,sex)
);

查看

show index from tableName;

drop index indexName on tableName;

删除主键索引
主键索引操作只能用alter

alter table tableName
drop primary key;

---

---

覆盖索引

索引的信息足够满足查询请求,不需要回到主键去取数据

优点

• 索引项通常比记录要小,MySQL访问更少的数据
• 索引按值的大小顺序存储,相对于随机访问记录需要更少的I/O
• 大多数引擎能更好的缓存索引,比如MyISAM只缓存索引不缓存数据
• 覆盖索引对于InnoDB表尤其有用,InnoDB使用聚簇索引阻止数据,如果二级索引包含所需的数据就不需要在聚簇索引中查找了
• 不同的存储引擎实现覆盖索引的方式不同,并不是所有存储引擎都支持覆盖索引

select *from tableName where age=19;

select id from tableName age=19;

后者比前者快,二次索引中存储主键索引(索引覆盖),尽量少写select*

索引的优缺点

• 优点
  提高查询速度
  可以给任何字段添加索引
• 缺点
  创建和维护索引需要时间
  索引需要占用存储空间
  数据增删改时,也需要动态维护索引,降低操作时间

---

索引使用原则
索引不是越多越好
经常更新的表避免进行过多的索引
数据量小的表最好不要使用索引
避免更新聚簇索引数据列(primary key)
并不是所有的索引都对查询有效(索引不合理,用与不用没什么区别)
避免在不同值少的列上加索引(性别)
根据业务需求建立索引(不要脑补)

---

执行计划

explain select *from user;

字段含义
1 id该语句执行的优先级
2 select_type使用的select查询类型
3 table使用的数据表名
4 partions 分区表相关信息
5 type访问类型
6 possible_keys 哪些索引可以使用
7key 实际使用的索引
8 key_len索引中使用的字节数
9 ref显示哪个字段或常数与key一起被使用
10rows 估算的找到所需的记录所需要读取的行数
11 filter 通过条件过滤的行数的百分比估计值
12 extra额外信息

select_type

1 simple :简单的select查询,不包含子查询或者union
2 primary:查询中若包含任何复杂的子部分,最外层标记为primary
2 subquery:在select或者where列表中包含子查询,该子查询标记为subquery
4 union:若第二个select出现在union之后则标记为union
5 derived (衍生)用来表示包含在from字句的子查询
6 union result:从union表获取结果的select标记为union result
7 dependent:select 依赖于外层查询中发现数据
8 uncacheable:select中的某些特性阻止结果被缓存在一个item_cache中

type访问类型

1 ALL :full table scan 全表扫描

explain select *from tableName;

2 index : full index scan全索引扫描

explain select count(*) from tableName;

二级索引优化count(*)
3 range : 索引范围扫描 ,between ,>,<等
4 index_subquery:子查询中使用了普通索引值
5 unique_subquery:子查询使用了unique或者primary key类型的值
6 index_merge:表示使用了索引合并优化的方法
7 ref_or_null:专门搜索MySQL中null值的行
8 ref使用非唯一索引,使用索引键取得的记录条数大于1
9 eq_ref:使用的索引为唯一索引或者主键索引
10 const:查询可优化为一个常量,如主键查询
11 system 查询的表只有一行
12 null 查询在缓存中存在

优化达到ref级别

extra 额外信息
1 using index: 使用了覆盖索引(covering index)
2 using where:存储进去检索行后再进行过滤
3 using temporary:使用零时表来存储结果集,常见于排序和分组查询
4using filesort:MySQL中无法利用索引完成的排序操作称为"文件排序"
5using join buffer 没有使用索引,需要连接缓冲区存储中间结果
6 impossible where:这个值强调了where语句会导致没有符合条件的行
7 select tables optimized away 仅通过使用索引优化器可能仅从聚合函数结果中返回一行
8 index merges:在一个给定的表上使用超过一个索引

执行计划

1 explain 不考虑各种cache
2explain 不能显示MySQL执行查询时的优化工作
3 explain 部分统计信息是估算的,并非精确值
4explain只能解释select操作,其他操作要重写为select后查看执行计划
5explain不会告诉关于触发器,存储过程的信息或用户自定义函数对查询的影响情况

type:查询access的方法
key:本次查询最终使用哪个索引,null为未使用索引
key_len 选择的索引使用的前缀长度或者整个长度
rows查询需要扫描的行数,越少越好
extra

性能优化相关的常用命令

show variables like '%profiling%'

set profiling =false;
set profiling_history_size=100;

显示最近执行的sql语句

show profilings; 

sql语句代价

show status like 'last_query_cost';

查看进程

show processlist ;

杀掉进程

kill id;
kill 48;

查看表锁

show open tables where In_use>0;
show status like 'innodb_row_lock%;';

性能优化
了解项目
开启慢查询日志,关闭查询缓存
导入或者生成大量测试数据
分析慢查询日志找到问题
通过执行计划分析并进行优化
优化方案线上验证

show variables like 'query_cache%'

show status like 'qcache%';

Qcache_hits查询缓存命中几次

性能调试为了避免查询缓存的影响,会关闭查询缓存
关闭缓存,0关闭,1开启

set query_cache_type=0;

慢查询日志

查看慢查询日志状态

show variables like 'slow_query_log';

show variables like 'long_query_time';

查看数据路径

show variables like 'data%';

避免索引失效:

索引口诀(一)
全职匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
减少查询次数及优化技巧
用内存换取交换次数

**
减少数据访问
返回更少数据
减少交互次数
减少CPU开销**

临时关闭慢查询日志

set global slow_query_log=0;

数据分页limit优化

type类型为all全表扫描,后面页数查询会很慢
解决办法

• 1后面页数不展示
• 2 在limit 前面加上where id>xxx
• 3 select id 将结果和原表内连接

表连接查询时,小表在前

对索引列进行操作会使索引失效

is not null /or索引失效

solr

---

---

---

索引优化口诀

全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
var引号不可丢,.避免sql隐式转换;

数据库参数的优化
INNODB_BUFFER_POOL_SIZE
InnoDB缓存池内存大小,存储缓存的数据,二级索引,脏数据以及各种内部结构等
独立的数据库服务器应该分配整个机器总内存的80%

INNODB_BUFFER_POOL_INSTANCES
缓冲实例用来减少内部锁争用而提高MySQL吞吐量
高负载下建议设置8-16,注意在高并发负载的服务器上才看得出区别

INNODB_THREAD_CONCURRENCY
控制INNODB的并发机制,0表示关闭并发控制,因此会立即处理所有进来的请求
低负载设置innodb_thread_concurrency=0,高负载先设置为8再根据情况调整

QUERY_CACHE_TYPE=0
低负载且主要为读操作有用,开启并设置query_cache_size=256M,不要超过256M
高负载建议关闭查询缓存,并设置query_cache_size=0,停止使用查询缓存以及查询缓存的互斥锁

插入更新速度优化
innodb_log_file_size=128M
事物日志文件(ib_logfile0)大小,需容纳服务器1小时的活动内容
innodb _log_files_in_group=2
事物日志文件数,默认值2,循环覆写
innodb_log_buffer_size=16M
事物日志缓冲区大小
innodb_log_group_home_dir=./
事物日志存储位置
innodb_flush_log_at_trx_commit=0
默认为1
刷新redo文件
sync_binlog=1
数据库安全及备份恢复
innnodb_flush_method=O_DIRECT
默认为空
无需内存缓存,直接写入磁盘文件

本地缓存

web三个级别
request:请求期间有效
session:登录状态
application:应用程序的生命周期

1使用JDK集合对象进行数据缓存
2使用guava cache进行缓存

网络缓存
1 使用MySQL Memory Engine进行缓存
2 使用Redis ,Memcached等内存数据进行缓存

Redis
基于内存的,单进程单线程模型的key-value数据库,由C语言编写

1内存操作,不受硬盘I/O速度限制
2数据存在内存中使用hash算法,查到和操作的时间复杂度是O(1)
3单线程,避免上下文切换和竞争条件及锁问题

缓存
本地缓存
网络缓存

数据库集群
主从复制
集群中各个数据库的数据要一致

读写分离
使用MaxScale实现
使用MySQL Proxy实现

写类型的操作在主数据进行
读类型的操作在从库进行
增加从库数据实现负载均衡

主从复制概念

MySQL主从复制是指
数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点
MySQL默认采用异步复制方式,这样从节点不用一直访问服务器来更新数据,数据可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定数据库,或者特定表

Master主库

slave从库

引擎优化
索引优化

sql语句优化

(尽量避免全表扫描,避免select*,避免where字句使用!=h或<>操作符,like语句避免前置百分号,避免在where子句中对字段进行表达式,函数操作,避免向客户端返回大量数据,保证join语句中被驱动表的join条件字段已经被索引,减少最内层的循环次数,group by 实质是先排序后分组,遵照索引最左前缀)

表结构优化

永远为每张表设置一个主键,有限值字段使用enum而不是varchar,尽量使用数字型字段,固定长度的表会更快,字段长度不确定使用varchar,尽可能使用not null

数据库参数优化
缓存优化
硬件优化
读写分离
分库分表’

---

查询语句的执行流程

---

1查询缓存存在,则直接返回缓存数据
2缓存不存在,进入分析器,分析器判断sql语句是否正确,错误返回错误信息
3sql语句正确进入优化器,优化器进行查询语句优化处理
4之后进入执行器,查询满足条件的数据并返回

独立表空间和共享表空间
共享表空间:
数据库的所有表数据,索引文件全部放在一个文件中,默认共享表空间的文件路径在data目录下
独立表空间:
每一个表都是独立文件
独立表空间放在共享表空间中,删除表后,空间不会被删除,独立表空间会删除表和表空间.
独立空间表示:

• innodbfileper_table=on

内存表和临时表
内存表,指的是使用memory引擎的表,数据保存在内存中,系统重新启动数据被清空单保留表结构
临时表,可使用各种引擎,比如innodb和myisam,数据写在硬盘上

查看所有连接,command为sleep为空闲连接

• show processlist ;

使用外键的注意事项

• 必须是innodb表,myisam和其他引擎不支持外键
• 相互约束的字段类型必须一样
• 主表的约束字段要有索引
• 约束名称必须唯一,即便不在一张表

外键约束

alter table `order` add CONSTRAINT constraint_uid FOREIGN KEY(userid) REFERENCES user(userid);

alter table `order` drop foreign key constraint_uid;