mysql优化
表的优化与列类型选择
- 定长与变长分离
如：id int ,占4个字节，smallint 占2个字节，tinyint 占1个字节，char(n) 占n个字符，也是定长，time也是定长，即每个单元值占的字节是固定的。
核心且常用的字段，可以建成定长，放在一张表，而varchar,text,blob 这种变长字段，适合单放一张表，用主键与核心表关联起来。
2.常用字段和不常用字段要分离。
需要结合网站具体的业务来分析，分析字段的查询场景，查询频度低的字段，但拆出来。
3.在1对多，需要关联统计的字段上添加亢余字段。
列的选择原则：
- 字段类型优先级：整形>date,time>enum,char>varchar>blob,text
列的特点分析：
整型：定长，没有字符集的差异，
比如：tinyint char(1) 都占一个字节，但是order by 排序，前者快，原因：后者要考虑字符集与校对集（排序规则）
Time:定长，运算快，节省空间，存储案例：2005-10-01，不好的地方：考虑时区，写sql时不方便
Enum:能起到约束值得目的，内容用整形来存储，但与char联查时，内部要经历串与值得转化
Char:定长，考虑字符集和排序（校对集）
Varchar:不定长，要考虑字符集的转换与排序时的校对集，速度慢。
Text,blob :无法使用内存临时表排序等操作，只能在磁盘上进行
附:关于date,time的选择，大师的明确意见，直接选int unsigned not null 存储时间戳
Tinyint 255最大数字为255，占一个字节
Char(1) utf8编码，占一个字符，3个字节
Enum(“男”，“女”)：多了一个转换过程
- 够用就行，不要慷慨
原因：大的字段浪费内存，影响速度
比如年龄，如果用int 浪费3个字节，用tinyint 可以存储255岁
比如：varchar(10),varchar(100) 联查的时候，更花内存
- 尽量避免用null
原因：null不利于索引，不便与查询，要用特殊的字节来标注。索引在磁盘上占据的空间更大
Where 列名=null;
Where 列名！=null ;查询不到
Where 列名 is null 或者 is not null 才可以查询到
索引优化策略：
索引的作用：快速查询用的
按算法分两类
1.b-tree 索引
注意：名叫btree索引，大的方面看，都用的平衡树，但具体的实现上，各引擎有不同，
比如：严格的说，ndb引擎使用的是T-tree
Myisam.innodb中默认用的b-tree索引
Btree索引常见的误区：
在常用查询字段加索引，错误的。
联合索引，把多个列看成一个整体的值，建立索引。
普通的索引，同时只能用上一个。
1.在多列上建立索引后，查询哪个列索引都将发挥作用
误区：多列索引上，索引发挥作用需要满足左前缀要求。
以index(a,b,c)为例（注意和顺序有关）
where a=3 是，只使用了a列
where a=3 and b=4 是，使用了a,b列
where a=3 and b=4 and c=5 使用了a,b,c
where b=3 / where c =4 否，没有使用索引
where a=3 and c=4 a列能发挥索引，c不能
where a=3 and b>10 and c=7 a,b能，c不能
where a=3 and b like "xxxx%" and c=7 a,b能，c不能
可以用explain select * from user weher id=3 查看查询计划
题目：假设某个表有一个联合索引（t1,t2,t3,t4）-下，只能使用该联合索引的c1,c2,c3部分
A,where c1=x and c2=x and c4>x and c3=x 使用了ci,ci2
b.where c1=x and c2=x and c4=4 order by c3 使用了ci,c2,c3,
c.where c1=x and c4=x group by c2,c3 使用了cI1,
d.where c1=? and c5=? order by c2,c3
e.where c1=? and c2=? and c5=? order by c2,c3
create table t4(
c1 tinyint(1) not null detault 0,
c2 tinyint(1) not null detault 0,
c3 tinyint(1) not null detault 0,
c4 tinyint(1) not null detault 0,
c5 tinyint(1) not null detault 0,
index c1234(ci,c2,c3,c4)
);
insert info t4 values(1,3,5,6,7),(1,3,5,6,7),(1,3,5,6,7);
Hash 散列特点，memory 引擎
Myisam ,innodb 磁盘，btree，组织数据上，有区别，分为聚簇，非聚簇
Myisam是索引和数据分开为非聚簇，反之为聚簇
Memory 内存，不怕散列查询
innodb的主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的引用
myisam中，主索引和次索引都指向物理行（磁盘位置）
注意：innodb来说
1.主键索引，即存储索引值，又在叶子中存储行的数据
2.如果没有主键primary key ，则会unique key 做主键
3.如果没有unique，则系统生成一个内部的rowid主键
4.像innodb中，主键的索引结构中，既存储了主键值，又存储了行数据，这种结构称为‘聚簇索引’
聚簇索引
优势：根据主键查询条目比较少时，不用回行（数据就在主键节点下）
劣势：如果碰到不规则数据插入时，造成频繁的页分裂。
实验：聚簇索引使用值导致页频繁分裂影响速度
过程：建立innodb表，利用php连接mysql
分别规则插入10000条数据，不规则插入10000条数据
观察时间的差异，体会聚簇索引，页分裂的影响。
create table t5( id int primary key, c1 varchar(500), c2 varchar(500), c3 varchar(500), c4 varchar(500), c5 varchar(500), c6 varchar(500), )engine innodb charset utf8; create table t6( id int primary key, c1 varchar(500), c2 varchar(500), c3 varchar(500), c4 varchar(500), c5 varchar(500), c6 varchar(500), )engine innodb charset utf8;
//插入数据seqinsert.php
$conn=mysql_connect('localhost','root','123456');
mysql_query('use test',$conn);
$str=str_repeat('hello',100);
$start=microtime(true);
for($i=1;$i<=1000;$i++){
$sql = "insert into t5 values($i,'$str','$str','$str','$str','$str','$str')";
mysql_query($sql,$conn);
}
$end = microtime(true);//返回当前时间微妙数
echo $end-$start;
//不规则 rndinnodb.php
$conn=mysql_connect('localhost','root','123456');
mysql_query('use test',$conn);
$base=range(1,1000);
shuffle($base);
$str=str_repeat('hello',100);
$start=microtime(true);
foreach($base as $i){
$sql = "insert into t5 values($i,'$str','$str','$str','$str','$str','$str')";
mysql_query($sql,$conn);
}
$end = microtime(true);
echo $end-$start;
通过上面的实验可以看出：
1.innodb的buffer_page 很强大
2.聚簇索引的主键值，应尽量是连续增长的值，而不是要是随机值（不要用随机字符串或UUID）
否则会造成大量的页分裂与页移动
高性能索引策略
对于innodb而言，因为节点下有数据文件，因此节点的分裂将会比较慢
对于innodb的主键，尽量用整型，而且是递增的整型
如果是无规律的数据将会产生的页的分裂，影响速度
索引覆盖
索引覆盖是指：如果查询的列恰好是索引的一部分，那么查询只需在索引文件上进行，不需要回行到磁盘在找数据。
这种查询速度非常快，称为“索引覆盖”
//自己设置索引
理想的索引
1.查询频繁
2.区分度高 //比如：性别字段，区分度低，只有男，女两种值
3.长度小 //索引长度直接影响索引文件的大小，影响增s删改的速度，并间接影响查询速度（占用内存多）
4.尽量能覆盖常用查询字段
针对列中的值，从左往右截取部分，来建索引
1.截取的越短，重复度越高，区分度越小，索引效果越不好。
2.截取的越长，重复都越低，区分度高，索引效果越好，但带来的影响也越大，增删改变慢，查询变慢
所以，我们要在 区分度和长度 上娶一个平衡，惯用手法：截取不同长度，并比较它们的区分度。
对于一般的系统应用：区别度能达到0.1索引的性能就可以接受
3。对于左前缀不易区分的列，建立索引的技巧：如：url列
解决方法：
1.把列内容倒过来存储，并建立索引，这样左前缀区分度大。
2.伪hash索引效果 crcurl=crc32(url),crc的结果是32位int无符号数（索引长度为int 4个字节）
索引与排序
1.对于覆盖索引，直接在索引上查询时，就是有顺序的
在innodb中，沿着索引字段排序，也是自然有序的。
在myisam中，是先取出所有行，再进行排序。
2.先取出数据，形成临时表做filesort(文件排序，但文件可能在磁盘上，也可能在内存中)
我们的争取的目标是取出的数据本身就是有序的，利用索引来排序。
using filesort 磁盘排序
using index
重复索引与冗余索引
重复索引：是指在同1个列或者顺序相同的几个列，建立了多个索引称为重复索引，重复索引没有任何的帮助，只会增大索引文件，拖慢更新速度，去掉。
冗余索引
冗余索引是指：2个索引所覆盖的列有重叠，称为冗余索引。
索引碎片和维护
在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，形成碎片，比如：删除数据
重新整理索引：表的引擎是innodb，可以alter table test engine innodb
或者optimize table test ;也可以修复
如果表的update 操作很频繁，可以按周/月来修复
如果不频繁，可以更长的周期来做修复。
sql语句优化
1.sql语句的时间花在哪里？
答：等待时间，执行时间。
这两个时间并非孤立的，如果单条语句执行的快了，对其他语句的锁定的也就少了。
2.sql语句的执行时间，又花在哪里了？
答：查找和取出
3.如何查询快？
1.找的快 联合索引的顺序，区分度，长度
2.取出的少的行与列
3.取出的快，索引覆盖。
切分查询，按数据拆成多次
例子：插入10000行数据，每1000条为单位
分解查询：按逻辑把多表连接查询分成多个简单的sql
3.sql语句的优化思路？
答：不查 少查 高效查
explain的列分析
id:代表select语句的编号。如果是连接查询，表之间是平等的关系，select 编号都是1，从1开始，如果某select中有子查询。则编号递增。
select_type:simple 查询类型是简单查询
table 查询针对的表
type 查询的方式 有all 扫描所有的数据行，index 扫描所有索引节点，const 常量 极快，range 范围查询，ref引用极快
possible key :可能用到的索引
注意：系统估计可能用的几个索引，但最终，只能用1个。
key 最终用的索引
key len 使用的索引的最大长度
注意：有可能possible keys 为null 而key不为null这种情况
possible keys 分析的是索引用于查找的过程
而最终的key可能是被用于查找排序，或者索引覆盖
例如：select 4*4 \G
int 型子查询引出的陷阱
例子：select goods_id ,cat_id ,goods_name from goods where cat_id in (select cat_id from category where parent_id =6);
误区：给我们的感觉是：先查到子栏目id,然后再查外层的
实际上是goods表全扫描，并逐行与category表对照，看parent_id=6是否成立。
原因：mysql的查询优化器，针对int型做优化，被改成了exist子查询的执行效果
当goods表越大时，查询速度越慢。
改进：用连接查询来代替子查询
explain select goods_id,g.cat_id,g.goods_name from goods as g inner join (select cat_id from category where parent_id =6) as t on g.cat_id=t.cat_id \G
内层select cat_id from category where parent_id =6 用到parent_id 索引，返回4行
exists子查询
题：查询有商品的栏目。
按上面的理解，我们用join来操作，如下：
select c.cat_id,cat_name from category as c inner join goods as g on c.cat_id =g.cat_id group by cat_name
优化1：在group时，用带有索引的列来group，速度会稍快一些，另外，用int型比char型分组，也要快一些。
优化2：在group时，我们假设只取了A表的内容，group by 的列，尽量用A表的列，会比B表的列要快。
优化3.从语义上去优化
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id=category.cat_id)
36:0.00039075|select cat_id,cat_name from category as c inner
join goods as g on c.cat_id =g.cat_id group by cat _name
37:0.00038675|select cat_id,cat_name from category as c inner
join goods as g on c.cat_id =g.cat_id group by cat _id
38:0.00035650|select cat_id,cat_name from category as c inner
join goods as g on c.cat_id =g.cat_id group by c.cat _id
40:0.00033500|select cat_id,cat_name from category where exists
(select * from goods where goods.cat_id = category.cat_id)
from型子查询
注意：内层from语句查到的临时表，是没有索引的
所以：from的返回内容要尽量少，需要排序，在内层先排好序
小技巧：
min/max优化，在myisam/innodb表中，一般都是经过优化的
我们查询min(id) id是主键，min(id) 非常快
但是，pid上没有索引，现在要求查询3113地区的min(id)
set profiling=on;打开性能观测
show profiles;//查看性能
count()优化
误区：
1.myisam的count()非常快
答：是非常快，但仅限于查询表的所有行比较快，因为myisam对行数进行了存储，一旦有条件的查询，速度就不再快了，尤其是where条件的列上没有索引。
小技巧：select (select count(*) from lx_com) - (select count(*) from lx_com where id<100)
3.group by
注意：
1.分组用于统计，而不用于筛选重复数据
2.group by 的列要有索引，可以避免临时表及文件排序
order by 的列要和group by 的一致，否则也会引起临时表。原因是因为group by 和 order by 都需要排序，所以如果两者的列不一致，那必须经过至少1次排序
4，union优化
union 总是要产生临时表对union的优化比较棘手
1.注意union的子句条件要尽量的具体，即查询更少的行
2.子句的结果在内存里并成结果集，需要去重复，去重复就得先排序，而加all之后，不需要去重，union尽量加all,可以在php中去重复。
limit 及翻页优化
limit offset ,n 当offset 非常大时，效率极低，原因是mysql并不是跳过offset行，然后单取n行，而是取offset+n行，返回放弃前offset行，返回n行
优化办法：
1.从业务上去解决
办法：不允许翻过100页
2.不用offset，用条件查询，例如：select id ,name from lx_com where id>500000 limit 10;
mysql优化总结：
建表
表的分割（常用/不常用，定长/变长）
字段的选择，int，date/time,char,enum,varchar,text
索引
联合索引
btree/hash索引 /算法上分
发挥作用：左前缀规则
索引和数据的组织上分为
聚簇索引，非聚簇索引，myisam,innodb
索引覆盖
统计实际的sql查询情况，分析查询的列和顺序，建立多列复合索引
查询
不查，少查，快查
查索引，少查列，少查行，explain,show profiles;
