db 优化 基础总结

少取字段，建立合理的索引

表的优化与类型的选择：

1. 定常与变长的分离：定常查询速度快，如果每一行的大小固定，很容易根据大小计算出位置。变长的字段适合单放一张表用主键与核心关联起来。
2. 常用字段与不常用字段分离。
3. 添加冗余字段，反范式。空间换时间。

列类型的选择

1. 字段类型优先级 整型>date,time>enum,char>varchar>blob,text  
2. 够用就行不用慷慨：大字段浪费内存影响速度
3. 尽量避免用null：null不利于索引，要用特殊的字节来标注。
4. Enum列的说明：

    1. enum列在内部使用整数来存储的
    2. enum列与enum列相关联的速度最快
    3. enum列比（var）char的弱势：在于char相关联是，要转化花时间。
    4. 优势：在char非常长时，enum依然是整型固定长度。查询越大时越明显

索引（btree，hash）(提高查询，排序，分组的速度)：
btree:

树最大的查询次数是数的层数。 2**（树的层数-1）=数据的个数
hash：memory内存表里是hash索引。时间复杂度是O(1 )
缺点：
1. 地址冲突
2. 数据存储的地址是通过hash算出来的，存储是位置变化，存储会有空洞。
3. 无法对范围查询进行优化。
4. 无法利用前缀索引，因为hash（‘helloworld’）和hash（‘hello’）的值是没有关系的。
5. 排序也无法优化。
6. 必须回行，必须去数据表中取数据。

建索引的常见误区：

1. 在where常用的列上都加上索引。错：独立的索引同时只能用上一个。（可用联合索引）
2. 建立索引的联合索引，需要使用左前缀原则:  

例index(a,b,c)索引为例 注意顺序：

查询	用上的索引
where a = 3	a
where a=3 and b = 5	a,b
where a =2 and b=5 and c=4	a,b,c
where b = 3 / c=4	无
where a = 3 and c = 4	a
where a = 3 and b>10 and c=7	a,b
where a = 1 and b like ‘hello% ’ c= 7	a,b

题：联合索引index(c1,c2,c3,c4)

查询	用上的索引
where c1 = x and c2 = x and c4= x and c3 = x	c1,c2,c3,c4(sql自动调整语序)
where c1 = x and c2 = x and c4 = x order by c3	c1,c2查询 c3排序
where c1 = x and c4 = x group by c3,c2	c1， 因为c2,c3的顺序错了所以用不上。
where c1 = x and c5 = x order by c2,c3	c1查询 c2 c3排序
where c1 =x and c2 = x and c5 = x order by c2,c3	c1c2查询 c3 排序

聚簇索引和非聚簇索引：
myisam引擎和innodb引擎 都是btree
非聚簇索引 myisam 数据和索引不在一起：.frm 说明 .myd数据 .myi索引 查询时先去btree查找，在根据地址去数据表里查找（回行：慢 因为要查磁盘）。

聚簇索引 innodb 数据在主键所在的btree的叶子下，所以不用回行，而二级索引（次级索引）的btree的叶子下是主键的引用，查询时先指向主键，再去主键叶子下找数据。对于不规则的数据造成也分裂。

innodb 叶分裂：（实验：叶子很重且主键插入没有规律） 即使插入的主键没有规律索引是也是有规律的。时间长（内存和固态硬盘不明显，机械硬盘明显）
索引覆盖（myisam）：
查询的列恰好是索引的一部分，那么查询只需要在索引上进行，不需要在回行到磁盘上找数据。（查磁盘很慢）
explain时 Extra：using index 是利用了索引覆盖。

例子：create table A(
id varchar(64) primary key,
ver int
…..
)
id ver 上有联合索引
10000条数据

为什么select id from A order by id 很慢？
而 select id from A order by id ,ver 就很快。
表有几个长的字段text(3000)

id 和 id ver 都索引覆盖，应该是innodb ,有几个叶子重，二级索引很轻，指向主键的引用，索引之索引主键是很慢因为主键下面有重的叶子，而联合的时候应该是搜索的ver，指向的主键，不用跳太多。

性能跟踪分析工具profiles：
Set profiling=1;
Show profiles;
Show warnings;

理想的索引：
1 查询频繁。2 区分度高。3长度小。尽可能的覆盖常用的查询字段。
区分度和长度是不能共存的，区分度高索引的长度就会大一点，但是索引的长度达会占用内存，要取到平衡点。

伪哈希索引：
对于左前缀原则，如果左侧确实用不上： 倒序或者伪哈希技巧（增加对应得哈希字段，有重复的可能）

多列索引的原则：
考虑因素：列的查询频率， 列的区分度，列的查询顺序，结合实际的业务场景。
可以有冗余字段。根据左前缀原则判断哪个索引是可以不用的。
索引和排序：
排序可能出现的2种情况：
1，对于索引覆盖，直接在索引上查询时，就是有顺序的，using index。
2，先取出数据，做一个临时表filesort，再排序，临时文件排序优先在内存中。也有可能在磁盘上

myisam 如果请求所有行，那么一定会先一下返回所有数据，建立一个临时表filesort，再排序（不用频繁回行），如果查询部分数据，而且排序使用到索引，那么执行过程是在索引上找到id，在回行拿数据，不会用到filesort，只会显示usingwhere，也不用排序，以为id已经是索引，有顺序了，按照id索引的顺序拿数据就可以了。优化防止有filesort。

重复索引和冗余索引：
重复索引：同一个列或者顺序相同的几个列，建立了多个索引，没有任何帮助，要去掉。
冗余索引：2个索引覆盖的列有重叠（顺序不同）。称为冗余索引。是可以的。

碎片与维护：
可以通过nop操作（不产生对数据实质影响的操作） 来修改表。
如：表的引擎是innodb 可以alter table xxx engine innodb
或者 optimize table 表明 来修复 碎片。

sql语句优化：
1.语句的时间花在什么地方：
等待时间，执行时间。
2.执行的时间花在什么地方：
查找
取出sending date

如何查询快：
查询：联合索引的顺序，区分度，长度。
取得快：索引覆盖。
传输的少（切分查询）

切分查询：把大量的数据分成小一点的，一步步查询。减少多表联查。

3，优化的具体思路：
不查，少查，尽量走索引，索引覆盖。

explain的列分析：

mysql> explain select user_ptr_id,cname from webapp_userprofile \G
***************** 1. row *****************
id: 1
select_type: SIMPLE
table: webapp_userprofile
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 71
Extra:
1 row in set (0.00 sec)

id: 查询的序号
select_type:SIMPLE 查询类型：简单
table: 表名
type: 索引发挥的作用怎么样 扫描了一部分还是所有。
possible_keys:可能用到的键
key:真正用到的键
key_len:用到的键用了多长
ref:引用
rows:估计要查多少行
Extra:额外信息

select_type的类型：

simple(不含子查询)
primary(含子查询或派生查询，的主查询)：子查询：1，subquery（非from子查询）2，derived（from子查询）3，union 4，union result .
table: 真实表明，别名，派生表（derived2）,null(直接计算结果，不走表)
possible_key:可能用到的索引
注意：系统腹肌可能用到几个索引，但是真正用到的只能用一个。
key:最终用到的索引
key_len:使用的索引的长度

当查询条件是一个表达式的时候，将用不到索引。。。。。。条件必须是一个确定的索引字段才行。
possible_key是null时key也有可能有，原因是在查询条件是没有用到索引，但是在取出数据是用到了索引覆盖。
type列：是指查询的方式，是查数据过程的重要依据。
all: 意味着从表的第一行，往后，逐行做全表扫描，运气不好就扫描到左后一行。
index：比all好一点，all是扫描所有的数据行，index是扫描所有的索引节点。
range:意思是查询时能够根据索引做范围扫描。
ref:值通过索引可以直接引用到某些数据行。精准查询。
eq_ref:引用，而且能过引用到1行
const，null，system，：优化到常量级别。更快。
ref列：指链接查询时，表之间的字段引用关系。
rows：估计要扫描多少行。
extra：
index：是指用到了索引覆盖，效率非常高。
using where:是指光靠索引定位不了，还得用where判断一下，
using temporary:是指用上了临时表，group by 与 order by 不同列是，或者group by，order by 别的表的列时。
using filesort:文件排序（文件可能在磁盘也可能在内存） 慢
注意：如果取出的列含有text，或者更大的，排序filesort将会发生在磁盘上。
in/exist型子查询的陷阱：
mysql in转成了exist，先查出左面的在带入右面的in，数据多效果不好，因为第一个表type：all
from 型子查询：
注意内层from语句查到的临时表，是没有索引的
所以：from的返回内容要尽可能的少。需要排序就在内层先排好序。
强制使用索引：
use index（xxx） 不推荐。
count和union：
select count(*) from xxx; 数据有存储。
select count(*) from xxx where > 1000; 加上条件查询就很慢了， 此时可以先查少的，再用总的减。

group by 是分组的不是去重的，列上有索引。（因为想分组，就得排序，如果有索引的话，排序就省了）

select A_id ,A_cat_id from A inne join B group by A_cat_id order by A_cat_id;group by 和order by要相同。

union：总是产生临时表（去重，也需要排序）
select 3 union select 4；

show status like ‘%tmp%’ 查看临时表

limit和翻页优化：
limit offset ，n；跳过o行取n行；
实际上是取出offset+n条数据，不要offer条数据，所以limit随着offset的增大，效率会下降。

show profiles；显示最近使用的15条命令使用资源的情况。
show variables like ‘%profil%’; 查看是否开启profiles
set profiling = 1；开启profiles
show profil for query 5; 查看具体那一条命令的资源消耗。

优化：1，翻页不到100页；
2，不用offset，加上条件查询，where id >offet limit 3;
3，索引覆盖+延迟关联。