mysql数据库优化

mysql 优化

1.项目中虽然有缓存系统，可以减少MySQL的连接，但实际有很多业务逻辑都需要查询数据库，比如用户的登陆操作，还比如缓存丢失，则也会导致用户丢失，所以需要连接数据库

2.数据表中的数据很多，每一次查询会消耗很多时间

连接数很多
数据量很多

mysql优化的入口点

存储层：存储引擎选择，列类选择，建表规范

设计层：缓存(查询缓存)，分区分表，索引

架构层：使用MySQL的读写分离技术，或者MySQL分布式存储

SQL语句层：选择合适的sql语句

2.存储引擎
技术的统称
sql层，实现MySQL初始化，建立连接，缓存，权限等

存储引擎层：实现不同存储引擎的相关操作

show engines (有9台存储引擎)

MySQL支持8种存储引擎，其中innodb为默认的存储引擎，也只有innodb才支持事务功能
在实际使用过程中，就是选择使用innodb与myisam引擎

3.myisam与innodb的区别

innodb支持事务，myisam不支持
myisam读写速度比innodb快
innodb支持外键（约束，不是索引）
myisam支持特有的压缩功能，innodb没有压缩功能
myisam只支持表的锁定，innodb可以支持到数据行的锁定。innodb的锁定级别更高
myisam支持全文索引功能
innodb5.6版本以上才支持全文索引（只支持英文，中文可以使用全文搜索引擎实现）

4.myisam与innodb存储机制

在配置文件中的datadir指定数据所保存的路径
在此目录下，每一个文件夹（目录）对应一个数据库

create table t_myisam(
id int not null auto_increment,
name varchar(10) not null default ” ,
primary key(id)
)engine=myisam default charset=”utf8”;

myisam存储一个表，对应三个文件
.frm:记录数据表结构信息
.myd:存储数据表中的数据内容(d:date)
.myi:存储数据表当中索引信息(i:index)

innodb创建一个表，对应一个文件.frm
.frm:数据表的结构文件
默认情况下，innodb数据保存到了datadir配置项所指定的目录ibdata1下，此方式叫共享文件存储数据

实现innodb引擎单独文件存储数据
show variables like %inno_file%查看
innodb_file_per_table=off
默认为共享模式(就是所有用innodb引擎创建的表的数据都放在datadir跟目录下了，只是将表结构放在对应的数据库下)
set global innodb_file_per_table=1设置为单文件存储
innodb将数据和索引和在一起
myisam将数据和索引分为两个文件存储

insert into t_attress values(null,’guangdong’,’shenzhen’),(null,’guangdong’,’guangzhou’),(null,’guangdong’,’dongwan’),(null,’guangdong’,’huizhou’),
(null,’hubei’,;wuhan),

insert into t_address select * from t_address 蠕虫复制

flush table t_attress

压缩功能
用命令行执行myisampack.exe “要压缩的表的绝对路径”

表压缩完，索引失效，解决
myisamchk.exe -rq ‘表的绝对路径’

数据表一旦压缩完成，就变成了只读文件，不能往里面写数据，如果想写数据，就将表解压
myisamchk.exe –unpack 表的绝对路径

myisam中的压缩功能可以使用在只需用读取数据的场景，例如日志信息存储

4.备份与还原
方式：
使用第三方工具进行备份还原（数据量少）
使用MySQL提供内置工具备份还原（数据量大的时候选择内置）

myisam引擎备份与还原
由于myisam引擎存储数据时，都是使用单独文件存储，因此其备份还原只需复制其数据表的三个文件

innodb数据表的备份还原要使用命令
mysqldump.exe -u root -p 123456 数据库 数据表>c:/bak.sql

mysql.exe -u root -p 123456 test