mysql优化

Mysql性能优化——实战篇

 

一、组合索引

1、什么是单列索引、组合索引？

       单列索引：就是对某一个列建立索引，比如只对username这列建立索引（对where后面的列创建索引）

add index(username(10))。

查询语句：

select column from tbl_user where username=?;

      

       组合索引：顾名思义多一个列建立索引，比如对username, email, address建立索引

add index(username(10), email(12), address(10))。

查询语句：

select column from tbl_user where username=? and email=? and address=?

 

2、如何设置索引

       对于varchar等类型他的长度是不固定的，为了提高检索效率，我们一般会对字段取前面的固定几位进行索引，比如上面的username取10位。

3、什么是左侧原则

add index(username(10), email(12), address(10))。

查询语句：

select column from tbl_user where email=? and address=?

       考虑上面这个语句有什么问题？效率为什么低？

       原因：建立上面这样的组合索引，其实数据库中相当于分别建立了以下三条：

add index(username(10)

add index(useranem(10), email(12))

add index(username(10), email(12), address(10))

       上面就是所谓的“左侧原则”，从最左边的开始进行组合。

       所以：下面几个效率会低，因为他找不到索引

select column from tbl_user where email=? and address=?

select column from tbl_user where email=?

select column from tbl_user where address=?

 

下面几个效率会高，因为他找到索引

select column from tbl_user where username=? and email=? and address=?

select column from tbl_user where username=? and email=?

select column from tbl_user where username=?

 

4、遇到or，如何写sql语句？

add index(username(10), email(12), address(10))。

查询语句：

select column from tbl_user where username=? or email=?

       上面的查询语句很鸡肋，没办法靠索引方式来，怎么办了？优化sql语句（自我组合）。

则需要建立email索引，并用以下sql语句union

add index(email(12))

查询语句：

select column from tbl_user where username=? union select column from tbl_user where email=?

 

二、Text全文检索

       对于text必须使用MyISAM存储引擎

CREATE TABLE articles (

    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

    title VARCHAR(200),

    body TEXT,

    FULLTEXT (body)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8;   

      

       查询的时候：

SELECT * FROM articles

WHERE MATCH (body) AGAINST ('database');

 

三、水平数据剥离

比如：tbl_article

article_id	title	content	add_time	author	hit
int	varchar	text	timestamp	varchar	int

 

将大数据字段进行水平剥离content字段：

现优化如下：

比如：tbl_ article(使用innodb，hit字段经常更新)

article _id	Cotent	content_id	add_time	author	hit
int	varchar	int	timestamp	varchar	int

tbl_content：（使用MyISAM，支持全文检索）

content _id	cotent
int	varchar

 

这样就可以把tbl_article数据列转换成固定的数据行格式，减少表的碎片，在查询的时候运行select * 不会通过网络传输大量的content内容了。性能上大大的提高了。

四、IP地址

       使用unsigned int来表示ip地址，比如：10.204.79.125，他存储的时候是：

       “10*2^24+204*2^16+79*2^8+125”计算的值

       注意：java中需要使用long来进行操作。

 

user_id	ip
int	unsigned int

五、时间问题

       建议使用unsigned int来保存，如果使用DateTime类型来保存，见下面例子

select * from tbl_user where date(add_time) between ‘2012-12-1’ and ‘2012-12-10’

      

需要转化获取日期，date函数结果对索引无效。所以上面的方式在检索的时候效率很慢（无法使用索引）

       请使用如下方式保存字段：

user_id	add_time
int	unsigned int

      

查询的时候(时间是秒数)

select * from tbl_user where add_time>123123125553 and addtime<1231231235