mysql缓存簇检查,上课笔记第三十一天MySQL并发控制、引擎、用户权限管理、查询缓存...

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：