mysql优化

1、首先选择合适的存储引擎

 如何选择MyISAM、InnoDB引擎

1) 如果项目对数据的完整性要求不高，偏重SQL的执行效率就选择MyISAM。例如：CMS(内容管理系统), 论坛, 微博,  qq空间。

2) 项目对数据的完整性要求非常高，必须使用InnoDB。例如：银行系统，财务系统，ERP（企业资源计划），OA， 网上商城。

2、选择合适的字段类型

   1) 人的年龄(unsigned tiny int)、狗的年龄（tinyint）、乌龟的年龄（unsigned smallint）

   2) 京东商城的商品类别id（unsigned smallint）

   3) 华为erp系统的用户id（mediumint）

   4) 优酷会员id（unsigned int）

   5) 新浪的新闻id（int）， 会使用备份服务器，来存储之前的数据。

 3.3 字符串类型优化

Varchar、char、text

Varchar: 可变类型。 Varchar(20)  存储：abc   实际占用长度3字节。

Char: 固定长度。 Char(20)    存储：abc     实际占用20字节。

 

Char的SQL效率更高，varchar效率低，占用空间小

 

手机号： char(11)

座机号： 3位区号-8位号码    4位区号-8位号码    4位区号-7位号码  char(12)

用户密码： MD5  char(32)。  

用户名： varchar(30)

文章标题： varchar(30)

文章摘要： varchar(255)

 3.5 枚举类型与集合类型（复合类型的数据）

Enum(枚举): 单选。

Set(集合):  多选。

能够使用枚举或者集合，一定不要使用字符串。

Sex: 使用枚举。

Hobby: 使用集合。

在底层，枚举和集合是整型保存。

 3.7尽量使用 not null

除非你有一个非常特殊的需求，否则一定要加 not null属性。 允许空比非空占用的空间更大，允许空字段在执行sql时，会执行更复杂的运算。

最好再加上  default。

 3.8 字段类型使用总结

1) 整型： 在满足需求的情况下，尽量选择小类型。

2) 能用整型就不用字符串： 枚举、集合来代替字符串，保存ip地址时将字符串转为整型

3) 在使用字符串时，固定长度一定选择char；可变长度，尽量减小长度。

4) 时间类型，参与运算比较多的采用int，不参与运算的采用date、datetime等。

Unix_timestamp() 将字符串时间转为时间戳

From_unixtime() 将时间戳转为字符串时间

5) 尽量使用 not null。

3、三范式原则

第一范式(确保每列保持原子性)

第二范式(确保表中的每列都和主键相关)

第三范式(确保每列都和主键列直接相关,而不是间接相关)

4、索引

1.1 什么是索引

索引是把字段A的内容储存在一个独立区间S里，里面只有这个字段的内容和其对应的物理地址。在找查与这个字段A相关的内容时会直接从这个独立区间里查找，而不是去到数据表里查找。该查找速度非常快，因为索引内部有排序算法。

 1.3 索引类型

主键索引，唯一索引，普通索引，全文索引。

1.4 主键索引

创建表时设置主键索引：

create table user1(

  Uid int auto_increment,

  Uname varchar(20) not null,

  Primary key (uid)

);

 

主键索引的特点: 整型 auot_increment

 

在已经存在表的情况下，增加主键索引：

Alter  table  表名  add  primary  key(字段名)

Alter  table  user1  add  primary  key(uid);

 

删除主键索引：

Alter  table  表名  drop primary  key;

Alter  table  user1  drop primary  key;

 1.5 唯一索引

unique: 给不会重复的字段增加， 用户名。

创建表时增加唯一索引：

删除唯一索引：

Alter  table  表名  drop  index  索引名称

Alter  table  user2  drop  index  uni_name;

1.6 普通索引

普通索引： 用户名， 商品类别等。

 关键词： index  /  key

删除普通索引：

Alter  table  表名  drop  index  索引名称;

Alter  table  user3  drop  index  index_name;

 

增加普通索引：

Alter  table  表名 add  index/key  [索引名称](字段名)

Alter  table  user3  add  key (uanme);

 1.7 全文索引

 ①MyISAM支持，MySQL5.6以上版本支持InnoDB的全文索引

 ②全文索引目前只支持英文，不支持中文

 ③分词技术：将一个搜索关键词拆分为若干个单词，并且匹配每个关键词

 ④主要作用于 varchar，char，text类型字段

 ⑤MySQL内置的全文检索有自己的一套分词算法，其会自动忽略语气词，常见词，出现在50%以上的关键词。

1.10 索引的优势和劣势

优势： 提升SQL的查询速度，加速表之间的链接。

劣势： 索引会增加磁盘占用空间，降低增删改的效率，因为增删改时，需要同时维护 MYI 文件。

结论： 合理的建立索引，提升系统效率。 因为绝大多数系统当中，查询的操作是远超于增删改的操作数量的。但是，不能滥建索引，要分析数据表中哪些字段经常作为查询条件，对经常查询的字段建立索引。

平常写语句过程中的一些建议：

sql语句优化

1、尽量去掉 in、or

含有in、or、的where子句会使索引失效

like子句尽量前端匹配

因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。

例6：select * from city where name like ‘%S%’

以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：

select * from city where name like ‘S%’