深入浅出mysql值开发篇一

mysql学习笔记之开发篇一

存储引擎的选择

插件式存储引擎是mysql的重要特性,用户甚至可以定制自己的存储引擎

存储引擎的分类和他们的特点

myisam,innodb,bdb,memory,merge,example,ndb cluseter, archive,csv
blackhole,federated

查看支持的存储引擎:

show engines 或者  show variables like 'have%'

修改存储引擎

alter table tabname engine=innodb;

存储引擎对比

myisam特点

存储在3个文件中.frm,MYD,MYI
不支持事务，不支持外键，其优势是访问速度快
索引和数据文件可以放置到不同的路径下
使用check table检测表，使用repair table修复表

myisam支持3种不同的存储格式
    静态，动态，压缩表

myisamchk  -r可以修复磁盘碎片

innodb特点

支持事务，写的处理效率差一些，并且会占用更多的磁盘空间可以保留数据和索引

自动增长列。对于innodb 自动增长列必须是索引，如果是组合索引必须是组合索引的第一列。
外键约束。
    创建外键的时候，要求父表必须有对应的索引，字表穿件的时候也会自动创建对应的索引
    可以开启或者关闭外键检查
存储方式：
    1 使用共享表存储，表结构在*.frm文件，数据和索引保存在 innodb_data_home_dir innodb_data_file_path
    2 使用多表空间存储。  表结构在*.frm文件，文件保存在单个表名+分区名上

    使用多表空间必须重启服务才能生效
    恢复和备份数据
    alter table tblname discard tablespace
    alter table tblname import tablespace

memory特点

内存存储引擎，在内存中，使用hash索引，服务关闭后丢失

merge特点

一组myisam表的组合,必须结构完全相同，

存储引擎的总结

myisam:mysql默认的存储引擎。如果应用是以读操作和插入操作为主，很少的更新和删除，
并且对事务的完整性，并发性要求不是很高，那么选择myisam是不错的。web，数据仓库的最佳选择

innodb:用于事务处理应用程序，支持外键。
    如果应用对事物的完整性比较高，在并发条件下要求数据的一致性，除了读操作和插入操作外还有很多的更新和删除操作，
    那么innodb是比较合适的选择。除了能有效的降低由于删除和更新导致的锁定，还可以保证数据的提交和回滚，对于计费系统或者财务软件比较合适的选择

memory：数据放在内存中，大小有限制，无法恢复，适合频繁访问的小表。
merge：适合突破myisam表大小的限制

选择合适的数据类型

char和varchar的

myisam存储引擎:建议使用固定长度代替可变长度
memory存储引擎:都是使用固定长度
innodb存储引擎:使用可变长度,因为在innodb中char和vchar没有区别，但是char更占用空间

text和blob

text和blob在删除大量数据的时候会产生性能问题
    定期optimize table xxx
可以使用合成索引来提高大文本字段的性能，前多少位或者md5
在不必要的时候避免检索大型的blob或text值
把blob或text列分离到单独的表中

浮点数和定点数

浮点数(double,float)超出精读四舍五入，存在误差
定点数实际上是以字符串形式存放的，可以精确的保持数据。
对货币数据或者精读敏感数据采用定点数存储
使用浮点数特别注意误差
要注意浮点数的特殊值得处理

日期的选择

根据需要选取最小的满足需求的日期类型
年份救援最好使用DTAEIME
如果数据对应不同的时区，使用TIMESTAMP

字符集

unicode

常用编码

ACSII       7位编码
ISO         8位编码
GB2312      双字节
GBK         双字节
GB18030 2或4字节
UTF-32      4字节
UCS-2       2字节
UTF-16      2字节或4字节
UTF-8       1-4字节

如何选择合适的字符集

满足应用支持语言的需求
字符集的兼容性
如果支持中文，数据量大，性能呢高，一般选择定长的GBK
如果要做大量的字符运算，定长更好
客户端使用相同的字符集，可以选择相同的字符集

查看支持的字符集

show character set;
校对规则 show collation like 'gbk%';

mysql 字符集设置

 default-character-set=gbk

可以指定字符集校订规则

客户端set names

索引的设计和使用

索引：每个引擎每张表至少支持16个索引，索引总长度为256字节

myisam和innodb的表默认创建的都是btree索引，不支持函数索引，但支持前缀索引

只用myisam支持全文索引，且只限于char和varchar和text且不支持前缀索引

memory引擎默认的是hash索引，也支持btree索引

索引语法

create [fulltext| unique|spatial]index indexname 
(using index_type) on tabname(col1,col2) 

drop index indexname on tabname;

设计索引的原则

搜索的索引列，不一定是索要选的列。where 不等同于你需要的列

使用唯一索引。

使用短索引。节省空间和提高速度。

利用索引的左前缀。

不要过度使用索引。

对于innodb存储引擎的表，记录默认会按照一定的顺序保存，如果有主键，按主键保存，如果有唯一索引，按照唯一索引保存。否则会给出默认顺序
    按照主键或者内部顺序访问最快。

    选择最常用的作为主键，提高效率。

    innodb的普通索引会保存主键的键值，索引主键应该尽量短的类型

hash索引的特征

只用于使用=或<=>操作符的等式比较
优化器不能使用hash索引来加速order by操作
mysql不能确定两个值之间大概有多少行，将myisam改为hash索引的memory表会影响性能
只能使用关键字搜索一行

btree

> < >= <= between,!=或者<>,like 都可以使用索引