mysql作为最流行的数据库,在开发过程中仍然有较多优化的空间,mysql的优化主要有4个方向:
(1) 存储格式
表结构存储于* .frm文件中。
默认所有表的数据/索引存储在同一个表空间文件中。
可以通过配置将不同表的数据/索引单独存储在*.ibd中,方便管理。
# 创建表
mysql> create table t1(id int, name varchar(32))engine innodb charset utf8;
mysql> show variables like 'innodb_file_per_table%';
mysql> set global innodb_file_per_table=1;
注意:innodb不能直接通过文件的复制粘贴进行备份还原,备份还原需要使用mysqldump。
(2) 存储顺序: 主键顺序
数据按照主键顺序存储,写入顺序与存储顺序不同,因此速度比Myisam稍慢。
(3) 并发处理
擅长并发处理,支持行级锁和表级锁。
(1) 存储格式
mysql5.5以下默认存储引擎。
结构、数据、索引分别存储于frm、MYD、MYI文件中,支持直接通过文件复制粘贴进行备份还原。
(2) 存储顺序: 插入顺序
写入速度快
(3) 并发处理
不如innodb,只支持表级锁
(4) 压缩性
对于不频繁发生变化的数据,可以进行压缩,压缩后只读,写操作需要先解压
内存存储引擎,速度快、数据容易丢失,可以用作缓存。
myisam:写入快,适合写入、读取操作多的系统,如微博。表锁,全文索引。
innodb:适合业务逻辑强、修改操作多的系统,如商城、办公系统。行(记录)锁,事务(回滚),外键。
mysql服务器提供的用于缓存select语句结果的一种内部内存缓存系统。
mysql> show variables like 'query_cache%';
# query_cache_size: 缓存空间大小
# query_cache_type: 是否开启缓存
# 在 my.ini 中开启缓存,设置缓存空间为128M
query_cache_type=1
query_cache_size=134217728
# 重启mysql
数据表的数据发生变化(数据修改)或结构改变(字段增删),则会清空全部的缓存数据,即缓存失效。
sql语句中有变化表达式(时间、随机数等),则不会使用缓存。
mysql> select name, now() from t2 where id=1234; # 不使用缓存
mysql> select * from t2 order by rand() limit 5; # 不使用缓存
生成缓存的sql语句对"空格"、"大小写"敏感,相同结果的sql语句,由于空格、大小写问题就会分别生成多个缓存。
mysql> select sql_no_cache * from t2 where id=1234;
mysql> show status like 'Qcache%';
Cache_free_memory 缓存中空闲内存总量
Qcache_hits 缓存命中次数
Qcache_inserts 缓存失效次数
Qcache_queries_in_cache 当前缓存的数量
范式主要分为四类范式,在开发过程中没有特殊情况数据表尽量要设计为第三范式。
第一范式:
(1) 表属性(列)具有原子性(不可分割)
(2) 表属性(列)不能重复
第二范式:
表中不能存在完全相同的两条记录,通常通过设置主键来实现。
第三范式:
表中不能存在冗余数据,列数据不能通过推导得到。
反三范式:
有时出于性能考虑,有意违反三范式,适度冗余,提高查询效率,例如存储浏览量。
长期数据操作过程中,索引和数据文件产生空洞碎片,会拖慢执行效率,需要修复,修复可以把数据文件重新整理,使之对齐。由于修复十分消耗资源,如果表的update、delete操作频繁,可以按周月修复。
# 方式1:
mysql> alter table engine innodb;
# 方式2:
mysql> optimize table <表名>;