MySQL数据库表的优化与列类型选择、索引优化策略讲解

MySQL优化

表的优化与列类型选择

一、表的优化

定长与变长分离

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

而varchar、text、blob等。这种变长的字段，适合单放一张表中，用主键与核心表关联起来。 常用字段和不常用字段要分离。

需要结合网站具体业务来分析字段的查询场景，查询频度低的字段，单拆出来。 在1对多，需要关联统计的字段上，添加冗余字段。

二、列的选择

字段类型优先级。整型>date、time>enum,char>varchar>blob、text

列特点分析:

整型：定长，没有国家地区之分，没有字符集差异。比如：tinyint 1、2、3、4、5 <-char(1) a、b、c、d、e, 从空间上都是占一个字节，但是order by排序，前者更快。因为后者需要考虑字符集和校对集（就是排序规则）。 time：定长，运算快,节省空间。考考虑时区，写sql时不方便，where ‘2017-09-11’。 enum：能起约束值的目的，内部用整型来存储，但是与char联查时，内部要经历串与值的转化。 char：定长，考虑字符集和（排序）校对集。 varchar：不定长要考虑字符集的转换和排序时的校对集，速度慢。 text/Blob：无法使用内存临时表（排序等操作只能在磁盘一进行）。

如：性别，以utf8为例
1. char(1) 3个这字长字节。
2. enum(‘男’,’女’) 内部转成数字来存,多了一个转换过程。
3. tinyint() 定长一个字节 0、1、2。

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

大的字段浪费内存影响速度。

以年龄为例：tinyint unsigned not null，用存储到255岁足够用，用int会浪费三个字节。
varchar(10)和varchar(100)存储的内容相同,但在表联查时,后者会花更多内存。

尽量避免用NULL()

NULL不利于索引，要用特殊字节来标注，在磁盘上占据的空间也更大，mysql5.5已对NULL做改进，但查询仍然不便

例如：

可以建立两张字段相同的表，一个允许为NULL，一个不允许为NULL，各自加入一万条数据，查看索引文件的大小，可以发现为NULL的索引要大一些。 另外：NULL也不便于查询

where 列名 = null; where 列名 != null; 都查不到值 where 列名 is null 或 is not null 才能查到值

Enum列的查询

enum列在内部是用整型来储存的。 enum列与enum列相关联速度最快。 enum列比(var)char 的弱势：在碰到与char关联时，要转化，要花时间; 优势：当char非常长时，enum仍然是整型固定长度。当查询量越大时enum优势越明显。 enum与char/varchar 关联，因为要转化，速度要比enum -> enum、char -> char要慢，但有时也这样用：数据量特别大时，可以节省IO

索引优化策略

一、索引类型

B-tree 索引

名叫B-tree索引，大的方面看都用的是平衡树，但具体实现上，各引擎稍有不同，

比如：严格的说，NDB引擎使用的是T-tree，Myisam,InnoDB中默认的是B-tree索引，但抽象一下B-tree系统可理解为“排好序的快速查找结构”。

B-tree的常见误区

在where常用的列上都加上索引。例如：where id = 3 and price 100; 查询id为3价格大于100的

id和price上都加上索引 只能用上id和price索引，因为是独立的索引，同时只能使用上1个 在多列上建立索引后，查询哪个列，索引都将发挥作用

多列索引上，索引发挥作用，需要满足左前缀要求，以index(a,b,c)为例（注意只和顺序有关），见下表

SQL语句	索引能否发挥作用
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 = 5	a有用，c没用
where a = 3 and b > 4 and c = 5	a、b有用，c没用
同上：where a = 3 and b = like ‘xx%’ and c = 5	a、b有用，c没用

2. Hash 索引

在memory表中默认的是hash索引，hash的理论查询时间复杂度为O（1）。

hash 查询的缺点：

索引是hash函数计算后的随机结果，如果是在磁盘上放置数据，比如以id为例，那么随着id增长，id对应的行，在磁盘上随机放置。 无法对范围查询进行优化 无法利于前缀索引。比如，在B-tree中field列的值“helloworld”，并加索引查询 xx = helloworld，自然可以利用索引 xx = hello 也可以利用索引。因为hash(“helloworld”)和hash(“hello”),两者的关系为伪随机。 排序也无法优化 必须回行，就是说通过索引只能拿到数据位置，必须要再通过这个地址再回到表中拿数据。

面试题

商品表，有主键good_id，栏目 cat_id，价格price; 在价格上加了牵引，按价格查询时还是很慢,什么原因，怎么解决？

答：实际场景中一个电商网站商品分类很多，直接在所有商品中，按价格查询商品是极少的，一般客户都是来到分类下再查询

改正：去掉单独的price列的索引，加(cat_id, price)复合索引，再查询; 如果根据日志统计，发现好多人这样查：

电脑 –> 品牌 –> 价格 index(cat_id, brand_id, price)

二、非聚簇索引和聚簇索引

非聚簇索引

Myisam引擎是使用非聚簇索引 聚簇索引

innodb 的主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的引用

myisam 中，主索引和次索引，都指向物理行（磁盘位置）

注意：

主键索引，即存储索引值，又在叶子中存储行的数据。 如果没有主键（primary key），则会 Unique key 做主键。 如果没有unique key，则系统生成一个内部的rowid 做主键。 像innodb中，主键的索引结构中，即存储了主键值，又存储了行数据，这种结构称为“聚簇索引”。

聚簇索引：

优势：根据主键查询条目较少时，不用回行（数据就在主键节点下面） 劣势：如果碰到不规则数据插入时，造成频繁的页分裂

索引覆盖

索引覆盖是指：如果查询的列恰好是索引的一部份，那么查询只需要在索引文件上进行，不需要再到磁盘上找数据。这种查询速度非常快，称为“索引覆盖”。

理想索引

查询频繁 区分度高：100万用户性别上基本男女各50万，区分度低 长度小：牵引长度直接影响索引文件大小，影响增删改的速度，并间接影响查询速度（占用内存多） 尽量能覆盖常用字段

建索引方法：针对列中的值，从左到右截取部分，来建索引

截的越短，重复度越高，区分度越低，索引效果越不好 截的越长，重复度越低，区分度越高，索引效果越好，带来影响也越大，增删改变慢，并间接影响查询速度。

所以：我们要在 区分度 + 长度 两者取得平衡

惯用手法：截取不同长度测试其区分度;

例如：
mysql> select count(distinct left(coulm, 6)) / count(*) from table;

索引和排序

排序可能发生的2种情况：

对于覆盖索引，直接在索引上查询时，就是有顺序的，using index，在 innodb引擎中，沿着索引字段排序，也有自然排序的，对于myisam引擎，如果按某索引字段排序。

如id，但取出的字段中，有未索引字段，如goods_name,myisam的做法，不是 索引 -> 回行…
而是先取出所有行，再进行排序。

先取出数据，形成临时表做filesort（文件排序，但文件可能在磁盘上，也可能在内存中）

using where：按照字段索引取出的结果，本身就是有序的 using filesort：用到文件排序，即取出的结果两次排序

争取目标： 取出来的数据本身就是有序的，利用索引来排序。

重复索引和冗余索引

重复索引：是指在同一个列，或者顺序相同的，几个列，建立多个索引，称为重复索引。其没任何帮助，只会增大索引文件，拖慢更新速度，去掉。 冗余索引：冗余索引是指2个索引覆盖的列有重叠。比较常见。

索引碎片和维护

在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，形成碎片。我们可以通过一个nop操作（不产生对数据影响的实质操作）来修改表。

比如：表的引擎为innodb，可以 alter table xxx engine innodb
optimize table 表名，也可以修复

注意： 修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐，这个过程，如果表的行数比较大，也是非常耗费资源的操作。所以，不能频繁修复。如果表的update操作很频繁，可以按周、月来修复。如果不频繁，可以更长的周期来修复。

explain 列分析

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
查询编号	simple：不含子查询	查询针对的表	all：从表的第一行，逐行扫描，运气还好可能扫描到最后一行	可能用到的索引	最终使用的索引	使用的索引的最大长度	当连接查询时，表字段之间的引用关系	估计要扫描多少行	index：用到了索引覆盖，效率很高
	primary：含子查询或派生查询	实际表名	index：性能比all稍好，通俗的说：all是扫描所有的数据行，相当于data_all; index是扫描所有的索引节点。相当于 index_all	注意：系统估计可能用到的几个索引，但最终只能用一个					using where：只靠索引定位不了，不要where判断一下
	subquery：非from子查询	表的别名	索引覆盖的情况下能利用上索引数据，但利用不上索引查找，必须全索引扫描。 mysql> select goods_id from goods where goods_id + 1 > 30						using temporary：指用上了临时表，group by 与 order by不同列时，或group by,order by另的表的列。
	derived：from型子查询	derived：派生表	2.是利用索引进行排序，但是取出所有节点。mysql> select goods_id from goods order by goods_id desc; 分析：没有加where条件，就得取所有索引节点。同时，又没有回行，只取索引节点，再排序，经过所有节点						using filesort：文件排序（文件可能在磁盘，也可能在内存）
	union	null：直接计算，不走表	rang：查询时能根据索引做范围扫描						注：如果取出的列包含text，或者更大的如mediumtext等，filesort会发生在磁盘上
	union result		ref：通过索引列可以直接引用某些数据行						show status like ‘%_table%’ 查询是否发生在磁盘上
			eq ref：通过索引列可以直接引用某一数据行
			const、system、null：查询优化到常量级别，甚至不需要查找时间

in型子查询引出的陷阱

mysql的查询优化器，针对in做了优化，被改成了exists子查询的执行效果，当表越大，查询越慢。exists子查询和in子查询在mysql底层相互转换。

改进：用连接查询来代替子查询。

from子查询

注意：内层from语句查到的临时表，是没有索引的。所以，from的返回内容要尽量小，需要排序，在内层先排好序。

count优化

误区：myisam 的count()非常快！
答：是比较快，但仅限于查询表的“所有行”比较快，因为myisam对行数进行了存储。一旦有条件的查询，速度就不再快了，尤其是where条件的列上没有索引。 假如，id<100的商家都是我们内部测试的，我们想查查真实的商家有多少？

小技巧：

select count(id) from table; 快 select count(id) from table where id < 100; 快 select( (select count(id)from table) - (select count(id) from table where id < 100) ); 快

group by

注意：

分组用于统计，而更是用于去重的
不重复的行，分组统计数据，而不要让查询产生N多重复数据。group by的列要有索引，可以防止临时表和文件排序。
order by 的列要和group by的列一致，否则也会引起临时表。

以A、B表连接为例，主要查询A表的列
那么group by, order by的列尽量相同，而且列应该显示声明为A的列

例：mysql> select A.id, A.cat_id from inner join B group by A.cat_id order by A.cat_id;

union 优化

union总是要产生临时表

注意：

union的子句条件要尽量具体，即要查询更少的行 子句的结果在内存时并成结果集，需要去重，去重就要先排序，而加了all之后，不需要去重，union尽量加all

limit 及翻页优化

limit offset,N 当offset非常大时，效率及低。原因是因为mysql并不是跳过offset行，然后单取N行，而是取offset+N行，放弃前offset行，返回N行。效率及低，当offset越大，效率越低。

优化办法：

从业务上解决：不允许翻过100页，以百度为例，一般翻到70页左右。

不用offset，用条件查询

比如： mysql> select id, name from table limit 10000000, 10; 可以这样
mysql> select id, name from table where id > 10000000 limit 10;

如果数据要作物理删除，无法用id作为条件查询，不要offset精确查询，还不限制用户分页，可以这样：
分析：优化思路：不查，少查，查索引，少取列，我们现在必须要查，则只查索引，不查数据，得到id，再用尖支查具体条目，这种技巧就是：延迟关联

比如：mysql> select id,name from table inner join (select id from table limit 10000000, 10) as tmp on table.id = tmp.id;