mysql优化

mysql优化,其中有自己的见解,也有转载别人的知识。

地址:http://blog.csdn.net/zhuxineli/article/details/7954865

1部分

字段应尽量避免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个字节,默认范围不清楚

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

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

Date 只存储年月日

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

Time 时分秒

索引:

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

数据库优化

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

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

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

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

Const该类型用在 表中最多只有一行匹配的记录,它在一开始查询的时候就被读取出来。并且用到了primary或者unique的时候。(也就是说只要用到了primary或者unique索引的并且匹配的记录只有一条的,所用的类型就是const

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

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

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

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

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

2部分

sql语句优化:

    1 避免使用*,即使搜索出所有列也不要使用*,直接使用列名

    2 where 中连接条件的顺序,select empmo,ename,salary,eptno FROM employee WHERE salary>1000 AND salary<3000

    修改select empmo,ename,salary,eptno FROM employee WHERE salary < 3000 AND salary > 1000

     很容易理解,原则就是尽量减少检索的数据

     3 避免使用OR关键字 ,建议使用IN,因为OR不会使用索引

     4 避免使用!=,目前不知道原因,建议改成SELECT tealD,teaname,salary FROM T_teacher WHERE salary < 3000 OR salary

     < 3000  

      5 在相关子查询中使用exists代替in关键字,因为数据库首先会执行in关键字的查询然后将查询结果放在一个临时表中在执行

      主查询的,而带有exists的查询会先进行主查询的sql,在通过子查询匹配数据

       6 使用like ,实现模糊查询。SELECT stuID,sutNAME,age,sex,birthday,birth FROM T_student WHERE stuID LIKE '%100%'

       修改成SELECT stuID,sutNAME,age,sex,birthday,birth FROM T_student WHERE stuID LIKE 's100%'

       因为第一种不会使用索引,注意。

       7 避免使用having,如果可以通过使用where 就尽量使用where

       8 使用存储过程,存储过程是经过编译的存放在服务器端的sql语句,不需要再进行额外的编译 解析,一般大型项目使用

       9 规范sql语句

      10 任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。 

       11 不要再索引列上进行运算,否则会导致无法使用索引

       12 count(column)和count(*)是两个完全不同的概念,第一个是查找该字段中所有值不为空的数量,第二个是查找所有字段的数量

优化实例:1 查找与用户同一地区性别为女的信息,按照最后登录时间排序

前提是:数据库有百万数据,而且用户访问很频繁

查询SQL:select * from user where area = '$area' and sex = '$sex' order by lastlogin desc limit 0 ,30;

需要注意的是,数据库中有百万条数据,但这里只要求检索出30条数据就可以

如果单纯的只在area上建立索引,首先会检索出所有area匹配的数据(因为建了索引,所以这是很快的),但是其他的字段没有建立索引,所以还要在命中sex,仍然会检索地区是$area的数据量。如果建立了area和sex的复合索引,效果略好,但仍然会检索area = '$area' and sex = '$sex',然后排序。如果建立了area sex 和lastlogin的复合索引,该索引基于area sex和lastlogin的三个字段合并排序,很轻松的就能将数据检索处理,而且只需要检索出30条数据,无需检索百万数据。

认识影响结果集,影响结果集是检索数据所检索的行数,输出结果集是真正返回的行数,对我们起真正决定意义的还是影响结果集

优化实例:2  毫秒级优化案例

游戏用户登录后台,显示最新账户信息。

查询为: select *  from user where uid = $uid order by timeline desc limit 20;

这是高频操作,每天有数百万次次执行,出现的问题:因为该搜索每执行一次的影响结果集是几百至几千条数据,在上千条结果集的情况下,该sql查询开销通常在0.01秒左右,建立uid+timeline复合索引,将排序引入到索引结构中,影响结果集就变成了20,每条sql查询的开销变成了0.001,数据库负载骤降。

优化实例:3 Innodb锁表案例

某游戏数据库的存储引擎是innodb,innodb是行表锁,理论上来说很少存在锁表情况。有一条语句,

delete grom username where uid = $uid

该语句执行的很少,每天大约只执行10次,20次左右,但这个数据库表容量百万级,而且悲催的是这个uid未建立索引,于是更悲催的事发生了,由于未建立索引,每次执行该操作的时候,delete遍历全表,全表被delete锁定,由于百万条记录遍历时间过长,期间大量select被阻塞,导致数据库连接崩溃

posted @ 2016-10-30 17:47  tianye_guazi  阅读(121)  评论(0编辑  收藏  举报