1、MySQL并发控制机制
并发控制:每个会话会启动一个mysql线程
服务器层:用于控制锁
存储引擎层:并发访问控制基本上应该由存储引擎层完成
锁:lock
读锁:共享锁
写锁:独占锁
锁力度:
表级锁:myisam表级锁
行级锁:innodb行级锁
锁分类:
隐式锁:由存储引擎自动完成
显示锁:用户可手动施加锁,表级锁
手动加锁:在服务器级别实现
lock tables tb1 {read|write};添加锁
UNLOCK TABLES:释放所有锁,不能单独释放。
FLUSH TABLES WITH READ LOCK;全局添加读锁。
事务:
一个执行单元:多个sql语句,要么都执行,要不都不执行。事务有2种操作,提交和回滚
事务隔离级别:
select @@global.tx_isolation; :查看数据库隔离级别,会产生脏读
set session tx_isolation='read-uncommitted';:设置事务级别
read-uncommitted(读未提交,就可以访问):最低隔离级别
read-commtted(提交后才可以读取):不可重复读
repeatable-read(可重读):幻读
serializabile(可串行化):加锁读,必须在提交后才可以读取数据
start transaction; :启动事务
commit:提交事务
rollback:回滚事务
select @@global.autocommit;查看是否自动提交 ,多次提交会降低I/O,建议关闭
事务日志;
可将随机I/O变为连续I/O,先把数据存储在连续I/O事务日志中,随后在更新到随机I/O中
注意:
myisam存储引擎不支持事务。
2、MySQL存储引擎介绍
show table status like 'user'\G或 show table status where name='user'\G:查看表的存储引擎属性。
存储引擎:称为表类型,他提供在表级别上,同一个数据库建议用同一个存储引擎。
show table status like 'user'\G:表引擎字段说明
Row_format
myisam:Dynamic:可变长度、fixed:固定长度、compressed:压缩表使用该格式该表数据都为压缩后存放。
innodb:compact:压缩表、redundent:有冗余表、
Rows:表中现有的行数
Avg_row_length:平均每行包含的字节数
Data_length:数据大小
Max_data_length:表 的最大容量,0表示没有限制
Index_length:索引长度
Data_free:空闲的数据空间,
Auto_increment:带有auto_increment字段下个使用数值。
Create_time:表的创建时间
Update_time:表最后的更新时间
Check_time: 检查表时间
Collation:默认排序规则。
Checksum:校验信息
Create_options:创建表时额外添加的选项
Comment:注释信息
mysql存储引擎特性:
innodb:设计用于处理大量短期事务,支持自动的崩溃恢复,优先考虑使用该引擎。
innodb支持Table Space: 表空间可由一个或多个物理文件组成 ,并可以实现自动增长。
table space支持两种风格和存储格式:
把所有的表都放在同一个表空间中。默认使用该方式。
表结构定义:tb_name.frm (数据库目录下)
数据和索引:统一的表空间文件中
每个表都使用专用的表空间。建议使用每表单独表空间机制。
默认在数据库目录下:tb_name.ibd;如果要启用需要在配置文件:
/etc/my.cnf中添加innodb_file_per_table = on
innodb基于聚簇索引建立:基于主键索引查询时性能较好,辅助索引必须包含主键索引
innodb:支持事务、行级锁、mvcc、支持热备。
innodb:存储引擎管理参数
innodb_data_file_path:表空间文件的文件名称及特性 ,可以设定多个文件
innodb_data_file_path = ibdata1:20G;ibdata2:10G;ibdata3:1G:autoextend;
innodb_data_home_dir:表空间的数据文件的存储位置
建议设置innodb_file_per_table = ON
InnoDB缓冲池: buffer pool:由InnoDB维护的内存空间:缓存索引及数据
innodb_buffer_pool_size:设置缓冲池大小
SHOW ENGINE INNODB STATUS;查看缓冲池中数据交互情况
myisam:不支持事务、行级锁和热备,支持全文索引,支持表压缩存放、空间函数。崩溃后无法安全恢复。
myisam存储格式:每张表有3个文件位于数据库目录下
tb_name.frm: 表格式、
tb_name.MYD: 数据
tb_name.MYI: 索引
其他存储引擎:
menory:基于内存实现,数据存储在内存中,无法持久存储数据。
aria:myisam增强引擎兼容myisam,以前使用myisam引擎现在可以使用aria
常用命令:
show table status like 'user'\G或 show table status where name='user'\G:查看表的存储引擎属性。
show engines;显示数据库所有引擎
show global variables like '%storage%';:查看数据库使用默认引擎,如果需要修改默认引擎修改配置文件或变量值即可。
set default_storage_engine='innodb';:修改引擎变量值
3、MySQL用户和权限管理
用户@主机:表示此用户帐号可从@HOST范围内的某主机对此MySQL建立连接;
@HOST: 表示客户端
%:表示任意字符,例如:172.16.0.0/16, 172.16.%.%
_:表示单个任意字符
mysql的权限类别:
库级别和表级别通用(alter、alter routine、create、create routine、create view、drop、index)
表级别(select、insert into、update、delete)
字段级别:明确指明对某字查询
管理类(create temporary tables、create user、file、super、show databases、reload、shutdown、lock tables)
程序类
all:表示赋予所有权限。
grant:授权。
revoke:删除权限。
常用命令:
create user 'xj'@'192.168.%.%' identified by '123456';添加用户
drop user ‘xj’@‘192.168.%.%’:删除用户
show grants for 'xj'@'192.168.%.%';:查看该用户拥有的权限
grant select on phpdb.* to 'xj'@'192.168.%.%';:对来自192.168网段的xj用户授予查询phpdb下所有表的权限。
revoke all on phpdb.* from 'xj'@'192.168.%.%';删除授予权限
grant select on phpdb.* to 'xj'@'192.168.%.%' with grant option;:允许把自己权限授予他人。
rename user 'xj' to 'xxx';把用户xj修改为xxx。
4、MySQL查询缓存管理及数据类型选择
缓存:hit(命中),miss(未命中):衡量缓存的有效性:命中率, hit/(hit+miss)
query_cache_type
on、off、demand(手动指明该语句是否缓存sql_cache | sql_no_cache)
query cache:
key:查询语句的hash码
value:查询语句的执行结果
查询语句中不会缓存的场景:
查询语句中有不确定数据时不会缓存。
查询中包含用户自定义的函数、存储函数、用户变量、临时表、包含权限的语句不会缓存。
需要消耗大量资源的查询,缓存比较有效
缓存优化的思路:
批量写入比单次写入对缓存的影响要小得多;
缓存空间不宜过大,大量缓存的同时失效会导致MySQL假死;
必要时,使用SQL_CACHE或SQL_NO_CACHE手动控制缓存;
对写密集型的应用场景,禁用缓存反而能提高性能;
碎片整理:flush query cache;
清空缓存:reset query cache;
常用命令:
show global variables like 'query_cache%';:显示缓存相关变量
query_cache_limit: 单条语句查询结果大于该值不与缓存,可修改变量值。
query_cache_min_res_unit:查询缓存时内存中分配最小单位。
query_cache_size:缓存最大空间,该数组必须为1024倍数,0为没有开启缓存。
query_cache_type:是否开启手动缓存功能
query_cache_wlock_invalidate:
show global status like 'Qcache%';:为mysql运行过程中的统计数据不能修改。
Qcache_free_blocks :查询缓存中空闲块
Qcache_free_memory :所有未分配的空间
Qcache_hits:查询命中次数
Qcache_inserts:向缓存中插入的次数
Qcache_lowmem_prunes :缓存满后清除缓存中数据次数
Qcache_not_cached:没能被缓存的次数
Qcache_queries_in_cache:还留在缓存中的数据,0表示都流失了。
Qcache_total_blocks:共分配缓存中的块
show global status like 'Com_select';:现实共执行select的次数
转载于:https://blog.51cto.com/5990383/1663733