mysql 优化（一）

1. 优化的方面

1.1 存储层：数据表“存储引擎”选取、字段选取、逆范式（3范式）

1.2 设计层：索引、分区/分表

1.3 架构层：分布式部署（主从模式/共享）

1.4 sql语句层：结果一样的情况下，要选择效率高，速度快，节省资源的sql语句执行

2. 存储引擎

2.1 存储引擎：数据表存储数据的一种格式。使用不同的特性也是不一样的。innodb存储引擎的特性有：支持事务，行级锁，外键等；Myisam存取速度快

查询数据库支持存储引擎的语句 show engines

查询某张数据库表的存储引擎 show create table 表名

2.2 Myisam：结构，索引，数据文件

2.2.1 数据的写入顺序与读出顺序保持一致。Myisam数据表存入数据的时候没有排序，按照写入的顺序存储，有利于提高存取速度

2.2.2 并发性：并发性稍低，多人同时做请求速度稍慢；锁机制：每次都锁住整张数据表

2.2.3 压缩机制：如果一个myisam数据表存储的数据非常多，就会占据很大的硬盘空间，为了优化处理，可把这个myisam数据表进行压缩处理，节省硬盘空间资源（这个部分本人尝试过，没成功，总是出现文件路径打开错误）所以具体步骤就不详细给出

A. 压缩技术：myisampack.exe 表名（在mysql安装目录的bin目录里面）

B. 重建索引

    根据压缩后的数据把索引重建建立起来。

    重建索引的工具：myisamchk.exe -rq  表名（在mysql安装目录的bin目录里面）

C. 只读特性

压缩的数据表不能再写数据了，因此对数据表进行压缩的时候，一定需要考虑好

a.有的数据表适合压缩，数据不频繁发生变化的数据适合，例如全国的邮编信息、用户的收货地址信息。

b.有的不适合，数据频繁发生变化的就不适合压缩

 

如果必须要对压缩的数据表进行“写”操作，就“解压”

具体解压：myisamchk.exe --unpack  表名

(解压缩的同时，索引会自动重建)

3. innodb

3.1innodb:存储引擎数据表，有单独的“结构文件”，默认情况下，所有数据库所有innodb数据表的索引，数据文件都合并到一起

该集中的文件数据如果遭到破坏，就难以恢复。最好给每个innodb数据表创建单独的“数据/索引”文件

一般该innodb数据表信息的备份、恢复要通过命令方式：

> mysqldump -uroot -p密码 数据库名字 >f:/20151214.sql  [备份]

> mysql -uroot -p密码 数据库   < f:/20151214.sql  [还原]

3.2 单独设置“数据/索引”文件

要为每个innodb数据单独设置其“数据/索引”文件

最后每个innodb数据表形成的两种格式文件：*.frm 数据/索引文件*.ibd

服务器重启之后，innodb_file_per_table的值要归位，不过不影响之前已经创建好的数据表结构

3.3 数据存入顺序

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

3.4 并发性

并发性高，多人同时请求速度快，效率高

锁机制：行级锁，每次值锁住一条记录信息

3.5 memory

内存存储引擎

特点：内部数据运行速度非常快

缺点：服务器如果断电。就会清空该存储引擎的全部数据

4. 字段选取

4.1 选取占据空间小的字段

int 整形字段的选取

3.2 内容长度固定字段

varchar（长度） 1--65535字节

内容范围：单字节内存近65535个内容，内部要保留1-2个字节，保存内存的长度使用

         存储汉字(utf-8字符集 3个字节=一个汉字)  1--2万多汉字

char(长度) 1--255字符

单字节：每个字节等于1个字符

汉字：3个字节等于1个字符

无论单字节内容、汉字 都可以存储1-255个

 

char() 的运行速度快  ，例如char(20)  实际存储16个字符，分配20个空间

varchar()运行速度少慢，例如varchar(20)  实际存储16个字符，分配16个空间

 

内容最好固定住长度

手机号码存储：char(11)

存储邮箱：速度快char(40)   、空间节省 varchar(40)

3.3 整型存储

时间变为整型存储：

int

date  time    datetime   timestamp 

 

集合:set(‘篮球’,’排球’,’足球’,’棒球’)

枚举:enum(‘男’,’女’,’保密’)

建议使用集合、枚举类型，他们底层内部使用的整型进行存储

 

ip地址也可以转换为整型存储。

mysql： inet_aton(ip)   inet_ntoa(数字)

php:     ip2long(ip)       long2ip(数字)

myisam：写入数据非常快，适合使用场合dedecms/phpcms/discuz等写入、读取操作多的系统

innodb: 适合业务逻辑比较强的系统，例如ecshop、crm、办公系统、商城系统