mysql-性能优化

表的优化与列类型选择

空间换时间，时间换空间

表的优化

1.定长与变长分离
如id int占4个字节 char（4） 也占4个字节，这样在磁盘上好查找
核心且常用的字段 宜放在同一张表

varchar\text\blob变长字段适合单放一张表，用主键与核心表关联起来
2. 常用字段和不常用字段分离
3. 在一对多关系中，在一的一方增加冗余字段
例如：catelog下有多条topic,统计每个catelog下topic的数量

列的优化

1. 字段类型优先级
   整型>date,time>enum,char>varchar>blob,text
   整型：没有国家地区的区分，没有字符集的差异

2. 够用就行 不要慷慨
   人的年龄
   tinyint unsigned not null可以存255岁足够了 使用int会浪费3个字节

3. 尽量避免使用null
   原因null不利于索引，要用特殊的字节标注
   在磁盘上占据的空间更大（mysql5.5已对null做改进，但查询仍不便）

可以建立两张相同的表，一个允许为null,一个不允许为null，各加入1万条数据，查看索引文件的大小，可以发现为null的索引要大一些。

另外null也不便于查询：
where 列名=null
where 列名！=null都查询不到值
where 列名 is null或is not null 才能查到值

索引优化策略

mysql中有两种索引类型，B-tree索引，hash索引，各有优势

B-tree索引

一般32层的b-tree树就能存42亿的数据

索引检索方式，先到树上找数据（使用索引较快），每个索引数据会保存该数据对应磁盘上的地址信息，那么就可以检索到对应记录

hash索引

建立索引常见误区

索引不仅能提升查询速度，也能提升排序速度（比如可以先遍历b-tree索引树，然后再对数据进行排序）和分组统计速度（一般分组之前都要排序）

在多个经常使用的列上都加索引是错误的，常用N列每列都加，变成了N个索引，最后只能用上一个

一般常用的情况是，经常使用的列联合起来定义一个索引，但想要索引生效，需要满足左前缀原则

索引实验(1)

创建表之后，在查询语句时使用,输出的key_len（指索引用在查询上，不包括排序和分组上）：4,说明索引中的4个列都用上了（因为每个列长度1）
explain select …

A where c1=x and c2=x and c4>x and c3=x
key_len: 4,说明索引列c1,c2,c3,c4都用上了
B where c1=x and c2=x and c4=x order by c3
key_len: 2,说明c1,c2用上了，c3在查询中用不上，在排序中可以用上
D where c1=x and c5=x order by c2,c3
key_len: 1，只能用上c1
E where c1=x and c2=x and c5=x order byc2,c3
key_len: 2，只能用上c1,c2

联合索引面试题

聚簇索引与非聚簇索引

这两种分别对应Myisam引擎和innodb引擎，这两种引擎都使用的是B-tree索引，但具体略有不同

非聚簇索引

对Myisam来说，数据是一份单独的文件new.myd,索引是一份单独的文件new.myi，所以它使用的是非聚簇索引。索引指向行在磁盘上的位置（回行）

聚簇索引

重要不同：innodb直接在主键索引的树中，直接存储行的数据；非主键的索引树中，不存储数据，它的叶子节点指向主键索引树的叶子节点；

页分裂实验

非常有意思的现象是，聚簇索引中插入是乱序的，但是查询出来是有规律的，这就是因为聚簇索引把数据存在叶子节点上。

索引覆盖

针对Myisam引擎，如果发生回行，即在内存中找到索引树，然后去硬盘中找对应的数据文件，那么查询会比较慢；如果想要使查询不发生回行，那么就需要在内存中找到想要查询的数据，则需要建立联合索引，发生索引覆盖

下面的情况，不会产生回行，直接从索引树中取数据

比较一下发生索引覆盖和没有发生索引覆盖的差别：发生索引覆盖显示：Extra: Using index

索引覆盖实验

1. 推断
2. 创建3张表
3. 向3张表中各插入10000条数据（t7,t8,t9:
   t7和t8可以用来比较Myasim和innodb引擎，t8和t9可以用来比较大字段列对查询的影响）
   
   
   

实验过程： set profiling=1;
执行6条sql语句；
show profiles;
分析：通过 2和3的执行时间可以看出，对于Myisam引擎，两者比较相差不大。
通过4和5的执行时间可以看出，对于innodb引擎，表中包含大字段的情况，两者时间差距较大。因为大字段包含的数据存储在主键的B-tree树中。
通过6和7的执行时间可以看出，对于innodb引擎，如果表中不包含大字段，两者时间差距不大。

理想的索引

伪hash索引

在crc列上建索引