Mysql 优化 (一)

优化的方面

• 存储层：数据表”存储引擎”选取、字段类型选取、逆范式(3范式)
• 设计层：索引、分区/分表
• 架构层：分布式部署(集群)
• sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行

查看引擎：show engines;
查看表结构：show create table table_name;
查看表状态：show table status like ‘table_name’;

什么是存储引擎
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

MYISAM

每个MyISAM在磁盘上存储成三个文件。

• .frm : 表结构文件
• .MYD : 数据文件(mysql data)
• .MYI : 索引文件(mysql index)

管理非事务表，提供高速存储和检索，以及全文搜索能力。(支持物理复制、粘贴以实现数据的备份、还原操作)

数据写入顺序

数据写入时候，不会按照主键id值给予排序存储，该特点导致数据写入的速度非常快。
写没有固定顺序，存也按照写的顺序存，不给考虑先后顺序。

mysiam的并发性(同时处理工作的能力)较比innodb要稍逊色
因为数据表是“表锁”(innodb行锁)

如果一个myisam数据表存储的数据非常多，就会占据很大的硬盘空间，硬盘空间我们不在乎，在乎的是mysql数据库系统为了处理该数据表就需要分配更多的资源，为了节省资源，可以把这个myisam数据表给进行压缩处理。

[root@localhost ~] pwd
/usr/bin/myisampack
压缩语法：myisampack 表名(绝对路径定义)

压缩后的数据表仍然可以支持查询操作,压缩后的数据表需要根据最新的数据位置把索引重新建立一次。
根据压缩后的据把索引重建建立起来。

[root@localhost ~] find / -name myisamchk
/usr/bin/myisamchk
重建索引语法：myisamchk  -rq  表名(绝对路径定义)

压缩的数据表不能再写入数据了(尝试写入会造成数据表崩溃)，必须解压后才可以。
(解压缩的同时，索引会自动重建)

解压语法：myisamchk  --unpack  表名(绝对路径定义)

innodb

• .frm : 结构文件
• .ibd : 数据/索引文件

innodb数据表不能直接的复制/粘贴文件，以便做进行备份还原，可以通过如下指令完成数据的备份和还原：
存储引擎用于事务处理应用程序，具有众多特性，包括ACID事务支持，提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

mysqldump -uroot -p密码  数据库名字 > /home/xiaogang/xxx.sql  [备份]
mysql -uroot -p密码 数据库名字   <  /home/xiaogang/xxx.sql  [还原]

数据写入顺序

该innodb数据表，数据的写入顺序 与 存储的顺序不一致，需要按照主键的顺序把记录摆放到对应的位置上去，速度比Myisam的要稍慢。

并发性高，多人同时请求，速度快、效率高。
innodb锁机制：行锁，每次只锁住一条记录信息。

myisam , innodb 的取舍

• myisam： 写入数据非常快，适合使用场合dedecms/phpcms/discuz/微博系统等写入、读取操作多的系统。
• innodb： 适合业务逻辑比较强的系统，修改 操作较多的，例如ecshop、crm、办公系统、商城系统
• myisam： 类型不支持事务处理等高级处理，而InnoDB类型支持.
• myisam： 类型的表强调的是性能，其执行数度比InnoDB类型更快.
• myisam： 支持表锁，InnoDB支持行锁。
• innodb： 不支持FULLTEXT类型的索引.
• innodb： 中不保存表的具体行数，执行select count(*) from table时，InnoDB要扫描一整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可.

memory

Memory使用哈希索引，所以数据的存取速度非常快。

内存存储引擎

• 特点：内部数据都运行在内存中，可以应用于临时表中在需要快速查找引用和其他类似数据的环境下，数据存储也在内存中，速度非常快，临时存储一些信息
• 缺点：服务器如果断电，就会清空该存储引擎的全部数据

存储引擎的对比

alter table student5 add primary key (id);
alter table student5 add unique  key  (name);
alter table student5 add         key (height);
alter table student5 add fulltext key (introduce);

语法：
alter table 表名 drop key 索引名称; //[非主键]索引删除(唯一/普通/全文/复合)
alter table 表名 drop primary key; //[主键]索引删除

注意：删除主键索引，必须先去除auto_increment属性

使用以下命令可查看是否开启单独存储数据与索引文件：

* show variables like '%innodb_file_per%';

可以修改配置，改变innodb数据表的数据、索引文件的存储方式：

* set global innodb_file_per_table=1/0;

InnodB数据表数据、索引合并文件分为两种情况：

* 每个数据表都有自己的数据、索引合并文件
* 数据库中全部的数据表共享一个数据、索引合并文件