mysql 性能优化

表的优化与列类型的选择

表的优化

1:定长与变长分离

如id int类型，占4个字节，char(4)占4个字节，也是定长，即每一单元占的字节是固定的。核心且常用字段，宜建成定长，放在一张表。

而varchar 、txt、blob，这种变长字段，适合单独放一张表，用主键和核心表关联起来

2、常用字段和不常用字段要分离

要结合网站具体业务来分析，分析字段的查询场景，查询频度低的字段，单拆出来

比如用户信息表，姓名、性别、年龄 都是固定的，且是比较常用的，而个人介绍缺特别长，也不太常用，可以单独放在一张表

列表页

3、在1对多需要关联统计字段上，添加冗余字段，提高速度

第一范式 、范式越高

两表联查 时，考虑是否能增加一个冗余字段，防止多个两个表

空间换时间，

4、列选择原则

1、）字段类型优先级 整型> date.time>enum ,char>vchar >blob,text

字符char(1) 与tinyint 1,2 都是一个字节时，但是order by 排序时，tiny int 比char快，应为字符集需要考虑校对集（就是排序规则）

enum 能起约束值的目的，内部用整型来存储，但与char联查时，内部要经历串与值的转化

char定长，考虑字符集的转换与排序的校对集，速度慢

varchar不定长，要考虑字符集的转换与排序的校对集，速度慢

text blob 无法使用内存临时表（排序等操作只能在磁盘上进行）

关于data time选择，大师的明确意见，直接选择

int unsigned not null；

够用就行，不要慷慨（如 small int ,varchar(N))

原因：大的字段浪费内存，影响速度

以年龄为例，tinyint unsigned not null ，可以存储255 岁足够了，用int 浪费3个字节

以varchar(!0)、varchar(300)存储的内容相同，但在联查时，varchar(300)要花费跟多内存

3、尽量避免用NULL

拥有NULL ，不利于索引，要用特殊的字节来标注

在磁盘上占据的空间其实更大

索引优化 高效查询的数据结构

1、btree 索引 时间复杂度 log2N

2、hash 索引

hash索引 只能在mymory 表里（放在内存中的），默认是hash索引，hash的理论查询时间复杂度为O(1)

疑问：既然hash 的查找如此高效，为什么不都用hash索引

hash地址可能有冲突，hash函数后，返回的值是无顺序的。对精准查询，比如

where id>4 ，用hash不行

2）无法进行范围优化

3）hash索引无法利用前缀索引，比如在btree中，field列的值“helloword”，加索引查询 xx=helloword ，自然可以利用索引x=hello（左前缀索引），而hash不行，因为hash(helloword）和hash(hello)不相等

4）排序也无法优化

5）必须回航，就是说，通过索引拿到数据位置，必须回到表中取数据

在where条件常见的列上加上索引，意义不大。

2.2在多列上建立索引后，查询哪个列，索引都能发挥作用

错误

btree的左前缀原则，想象老师讲的三块木板

|-------------|-------------------|---------------------|

| a | b | c |

|-------------|-------------------|-------------------- |-

从左到右，只有走过a，才能到b,走了b才能到c，

如果中间某个列跳过了，或者模糊查询了，后面的列的索引就不能用了

索引：提高查询速度，提高排序速度、提高分组统计的速度

考虑多列索引

树上只保存主键值，所以如果select name from xx where id>5，

通过索引查到了，主键值，想得到name得回行到 数据保存文件，此时需要访问磁盘

聚簇索引与非聚簇索引

1、

myisam 与innodb引擎索引文件的异同

非聚簇索引，数据得从索引树上所指的位置，到数据文件的指定位置找到数据。。这个过程叫回行

2、

树上保存所有数据，主键，name ,email等等

innodb 数据 直接存在叶子节点上，不用再通过节点保存的索引值再去找数据

3、

innodb索引

4、myisam索引

5、聚簇索引

innodb 主键值不规律时，叶子节点需要进行不停的页片分裂，速度减慢。 但是你查询时，查询出来的数据是有规律的，因为innodb数据放在了树的节点上，在插入时，会按

二叉树的规律进行插入，自然取出来时，数据也是有规律的。

如果换成myisam引擎，如果主键不规律插入，取出来的数据也是不规律的

t99 表中是id，name 联合索引 ，如果select name from t99 where id=2;

此时不需要回行，因为 建立索引时 是（id,name）,name在节点上存着，直接可以访问。。

而 select intro from t99 where id=2;此时需要回行，因为intro没有保存在索引树上

1、是否回行对比

1、常用的sql命令

show profiles； 查看语句执行是时间

取数据的左边一个

select left<word,1> from dict limit 10

去除左边一个字符，去重复，然后数数一共有多少

select count <distinct left<word,1>> from dict;

5、理想的索引

10、索引与排序

11、索引碎片与维护

12、防止三表以上的联查 left join

查询 、排序 、分组尽量走索引 ，尽量不要回行，走索引覆盖。索引覆盖，顺着索引进行查询，不需要回行，速度快

sql语句 left join ,inner join

mysql in优化

转】mysql in语句优化

一、

mysql会对sql语句做优化， in 后面的条件不超过一定数量仍然会使用索引。

mysql 会根据索引长度和in后面条件数量判断是否使用索引。

另外，如果是in后面是子查询，则不会使用索引。

一个文章库，里面有两个表：category和article。category里面有10条分类数据。article里面有 20万条。article里面有一个"article_category"字段是与category里的"category_id"字段相对应的。 article表里面已经把 article_category字义为了索引。数据库大小为1.3G。

问题描述：

执行一个很普通的查询：

Select * FROM `article` Where article_category = 11 orDER BY article_id DESC LIMIT 5

//执行时间大约要5秒左右

解决方案：

建一个索引：

create index idx_u on article (article_category,article_id);

Select * FROM `article` Where article_category = 11 orDER BY article_id DESC LIMIT 5

 减少到0.0027秒

继续问题：

Select * FROM `article` Where article_category IN ( 2 , 3 ) orDER BY article_id DESC LIMIT 5

执行时间要11.2850秒。

使用OR:

select * from article where article_category = 2 or article_category = 3 order by article_id desc limit 5

执行时间：11.0777

解决方案：避免使用in 或者 or (or会导致扫表)，使用union all

使用UNION ALL：

( select * from article where article_category = 2 order by article_id desc limit 5 ) UNION ALL ( select * from article where article_category = 3 order by article_id desc limit 5 ) orDER BY article_id desc limit 5

执行时间：0.0261

二、强制使用索引

select id from it_area use index(primary) where pid=69 limit 1;

三、union

总是会产生临时表

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

查看当前数据库 中的临时表

show status like '%tmp%';

union all不去重，如果union 去重的话，会很耗时

三、设置数据库某列的编码格式

show profile 可以查看详细的时间耗费

show profile for query 2;

limit语句换成条件查询

limit 5,3

where id>5 limit 3;这样可以用到索引，当 limit 10000000 ,100 ； 换成

select *from tab where id>10000000 limit 100;时 效率明显提高，索引的作用。前提时，列中的数据是连续的，别删除。如果列有删除，两条语句就不等效了。

select *from lx_com inner join (select id from lx_com

limit 12000000,3)as tmp on lx_com.id=tmp.id;

内部是查询用到了索引

燕十八老师 mysql高级教程

http://www.php.cn/course/197.html

精通mysql视频教程

http://down.51cto.com/zt/5962/3