Mysql 数据库优化--存储引擎的选择

http://www.php100.com/html/webkaifa/database/Mysql/2011/0908/9021.html 关于php程序员常犯的11个错误，写的挺好的

存储引擎的选择

   存储引擎的选择主要看下面几点因素：事务

   MYISAM和INNODB的区别:

   1 MyISAM注重的是性能，对于读多写少的项目，MyISAM的性能更好。

   2 若select count(*) 用的频繁，用MyISAM更好，因为MyISAM会将记录的总条数存储起来，而InnoDB需要全部检索，但后面若带有where

    条件，2种情况是一样的

    3 对于大尺寸的数据库倾向于InnoDB，因为它支持事务处理和故障恢复。

    4 大批的inserts语句在MyISAM下更快，但update在InnoDB下更快一些（有的材料说这两种在innodb下快）

    5 InnoDB的行锁也不是绝对的，如果在执行一个sql时不能确定扫描的范围，同样会锁全表。

    6 对于auto_increatement类型的字段，InnoDB中必须包含只有该字段的索引,而MyISAM中可以和其他字段一起建立联合索引。

   

  

架构优化和索引

选择正确的数据类型

字段应尽量避免null, 

DECIMAL DECIMAL(5,2)表示数字总长度是5位数，有2个小数点，公司存储价格的字段是decimal(10,2)

整数

一个字节占8个bit，因为一个int占4个字节，所以Int的取值范围就是-2的16次方到2的16次方-1，或者是0到2的32次方，tinyint占1个字节，smallint占2个字节，mediumint占3个字节，bigint占8个字节

还有若是要求数字不能为负数，可以设置属性为unsigned，举个例子

存储价格的时候不可能会出现负数，所以属性一定要有unsigned的设置，so  

Create table test(price int unsigned)

单精度浮点数float

占4个字节，默认范围

双精度 double

占8个字节，默认范围不清楚

 

准确数据类型和不准确的区别是：decimal必须制定小数点，精确。而float和double可以没有

 

日期时间类型存储年月日时分秒

Datetime 支持的范围是1000-01-0100:00:00 到9999-12-31 23:59:59

Date 只存储年月日

Timestamp 存储年月日时分秒，但是支持的范围比datetime要小，1970-2037,精度为1秒，格式跟datetime一样

Time 时分秒

很少用

Year 存年份范围是1901-2155

字符串类型

Char varchar text

这里只列举了几种常用的，还有什么blob什么东西，连用都没用过，有什么用呢?

Char 类型存储固定长度的字符串，不太常用，存储身份证什么的时候常用，因为有的人的身份证不全都是数字所以用到char（18）

Varchar 存储不固定长度的字符串，是最最长用的，最多存储的字节数为255个，所以说存储商品说明等大篇幅字的类型就要用到text了，该类型能存储2的16的字节。

索引

database.51cto.com/art/200910/156685.htm 这篇文章写的挺好的关于索引

http://wenku.baidu.com/view/5be8c3d384254b35eefd34ed.html

首先是，所有的索引都可以通过三种方式创建，第一种是在创建表的时候建，第二种是用create 索引类型索引名称 on 表名（列（length）），第三种是alter table表名 add 索引类型索引名称（列(length)）

 

普通索引

在创建表的时候，比如

Create table test(

         `Id`tinyint(4) not null AUTO_INCREMENT PRIMARY KEY,

         `name`varchar(10) null default ‘0’,   

    Indexidid(‘id’)

)ENGINE MYISAM

用show create table test \G来详细的查看语句

另外设置全文索引的时候要设置表的存储引擎为myisam，因为只有myIsam才支持全文索引

用create 修改

Create index idid on test (‘id’)

 

用alter修改

Alter table test add index idid (‘id’)

 

删除索引

Drop index 索引名 on 表名

 

 

不同类型的索引只需要修改索引类型而已，有

 普通索引<index>

 唯一索引<uniqueindex> <> 

 全文索引<FULLTEXT>

 主键索引<一般在建立表的时候就指定primarykey(id)>

 单列索引

多列索引这两种情况我觉得都不能算是类型，不知道是谁这么分类的

 

创建数据库job

Create database job;

 创建user表

Create table user(

Userid int(10) not nul unique primary key auto_increment,

Passwd varchar(20) not null,

Username varchar(20) not null,

Info text,

Unique index index_uid(userid desc),

Index index_user(username,passwd),

Fulltext index index_info(info),

 

)ENGINE-MyISAM ;

创建information表

Create table information(

Id int(10) not null unique primary key auto_increment,

Name varchar(20) not null,

Sex varchar(4) not null,

Birthday DATE,

Address varchar(50),

Tel varchar(20),

Pic blob

);

在information表中在name字段创建名为index_name的索引

Create index index_name on information(name(10));

创建名为Index_bir的多列索引

Create index index_bir on information(birthday,address);

用alter 语句创建名为Index_id的唯一性索引

Alter table information add unique index index_id(userid asc);

删除index_user这个索引

Drop index index_user on user;

 索引优化：

 1 在使用联合索引的时候尽量按照索引顺序来检索，但因为mysql优化器会对语句进行优化，所以顺序错了也没什么，但是如果只用最左前缀，只能用第一个的索引，如果用了联合索引的第二个索引，则直接不会使用索引。

 

数据库优化

Explain用于显示当前sql语句的执行情况，包括调用了索引，所以我们可以根据这个语句给表加需要的索引。

explain 显示内容的时候最重要的是keys这个字段，这个字段表示该查询中用到了那个索引，如果没有用到索引的话就显示Null，这时候我们根据要求增加合适的索引，如果用到了索引就显示用到的索引。

下面只说两个explain列出的三个重要的参数：

Type 表连接类型 依次是从最好的到最差的

Const该类型用在 表中最多只有一行匹配的记录，它在一开始查询的时候就被读取出来。并且用到了primary或者unique的时候。（也就是说只要用到了primary或者unique索引的并且匹配的记录只有一条的，所用的类型就是const）这里通篇以ecshop中的ecs_goods表为例

Explain select * from ecs_goodswhere goods_id = 10这种情况用的类型是const因为其中goods_id是主键 primary key

Explain select * from ecs_goodswhere goods_id < 10 这种用的就不是const

Explain select * from ecs_goodswhere add_time = 123445324  这种情况用的类型也是const，因为add_time是unique索引

Eq_ref  适用于有表连接的sql语句，并且只能从表中读取传一条语句，并且用到的索引必须是primary_key或者unique

Ref 该表中所有匹配的记录都会被读取出来，并且用到的索引不能是primary或者unique，这是最普通最常用的一种类型

All 这种情况非常糟糕，是因为表中没有建立索引，所以要对该表做全部扫描

 

Possible_keys 指的是该sql语句在搜索表时，坑内会使用哪个索引，但可能不会使用。如果该参数为空，则表示没有索引被用到。这种情况就可以检查where子句中那些字段适合加索引以提高查询性能。

Key 表示在查询中实际用到的索引，如果当前没有任何索引被用到则该参数为null，这时候同上，查看where字句中那些字段适合加索引来提高查询性能。

好的索引非常重要，好的索引让查询有良好的访问类型并且只检查需要的行。但是添加索引并不意味着mysql会访问并且返回同样的行。