• 1. 表的优化
  • (1) 字段类型优先选择顺序
  • (2) 字段长度够用就行(tinyint、 varchar(N))
  • (3) 避免使用null
  • (4) 对于字段属性分类数量确定而且数量比较少时，优先选择枚举类型enum，例如性别、学历等
• 2. 索引优化
  • (1) B-tree索引
  • (2) hash索引
  • (3) 聚簇索引
  • (4) 多列索引
  • (5) 伪哈希索引
  • (6) 延时索引
  • (7) 冗余索引
  • (8) 索引的长度和区分度
  • (9) 索引与排序
  • (10) 索引碎片和维护

1. 表的优化

列选择原则：

(1) 字段类型优先选择顺序

整型 > date、time > char 、varchar > blob

因为整形、time类型运算快又节省空间；char、varchar要考虑字符集的转换与排序时的校对集(a B排序哪个优先)、速度慢；blob无法使用内存临时表，设计到排序必须在硬盘完成，速度慢。

(2) 字段长度够用就行(tinyint、 varchar(N))

因为大的字段浪费内存，影响速度，例如某字段varchar(30)能存下的内容，如果用varchar(100)的话，在联表查询时，varchar(100)要花跟多内存。

(3) 避免使用null

因为null不利于索引，要用多一个字节来特殊标注该字段值是否为null，在磁盘上占据多一个字节。一般声明字段时都带有not null define属性

(4) 对于字段属性分类数量确定而且数量比较少时，优先选择枚举类型enum，例如性别、学历等

enum列在内部是用整型来存储的

• 优点：当一个表enum列与另一个表的enum相关联时速度最快，当enum成员是char类型，并且字节比较多时，enum依然时整型存储，可以节省IO。
• 缺点：在碰到与char关联时，需要转化消耗点时间，速度要比enum<->enum和char<->char要慢。

2. 索引优化

理想索引：

• 查询频繁
• 区分度高
• 长度小
• 尽量能覆盖常用查询字段

(1) B-tree索引

B-tree（多路搜索树，并不是二叉的）是一种常见的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B通常认为是Balance的简称。这个数据结构一般用于数据库的索引，综合效率较高，常被用于对检索时间要求苛刻的场合。B-tree可以理解为已经排好序的快速查找数据结构。

btree的左前缀规则：

• 按f1、f2……建立的复合索引，在where条件中，按f1、f2……由左到右的顺序（当and时不用按顺序），索引才会发挥作用。
• 如果中间某列没有条件或like条件，导致后面的列，索引用不上。
• 索引也能用于排序和分组，因为分组要先排序后计算。所以我们的order by或group如果能针对有顺序的表进行，可以避免临时表和文件排序。也就是说我们的order by或group按顺序使用索引的列，则可发挥索引的作用。

B-tree索引的误区：

在表中的常用的列都独立加上索引就以为常用的查询都完全用到索引了。例如user表中的gender和age列都独立加上索引，查询出表中大于60岁的女性的语句。

select * from user where gender=‘女’ and age>60;

注意：上面语句只能用上gender或age索引，因为是独立索引，同时只能用上一个，上面sql查询要完全使用索引，需要建立多列索引，例如：index(gender,age)

(2) hash索引

哈希索引包含以数组形式组织的Bucket集合。哈希函数将索引键映射到哈希索引中对应的Bucket，使用哈希索引必须要使用哈希集群。哈希索引可能是访问数据库中数据的最快方法(时间复杂度为O(1))，但它也有自身的缺点，只支持等值计算，不支持范围搜索或排序。

hash索引查找速度最快，为什么不用hash索引呢？

• 哈希函数计算的结果是随机的，如果在磁盘上随机放置数据，hash找到数据在磁盘位置很快，但是磁盘随机读取数据却很慢。
• hash无法对范围查询进行优化
• hash无法利用前缀索引，不能前缀优化。例如在mysql查找列值为zhangsan，B-tree索引支持’zhang’或’zhangsan’关键字索引，而hash必须为’zhangsan’全部搜索。
• 排序也无法优化

B-tree索引和hash索引区别：

• hash索引查找数据基本上能一次定位数据，当然有大量碰撞的话性能也会下降。而btree索引就得在节点上挨着查找了，很明显在数据精确查找方面hash索引的效率是要高于btree的。
• 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持。
• 对于btree支持的联合索引的最优前缀，hash也是无法支持的，联合索引中的字段要么全用要么全不用。
• hash不支持索引排序，索引值和计算出来的hash值大小并不一定一致。

(3) 聚簇索引

聚簇索引：索引和数据混合一起，如InnoDB的id和数据是混合在一起的。有个缺点，当数据文件比较大时，查询数据时不断翻越磁盘扇区的页，使用时间会比较长。

回行：从索引定位到获取磁盘数据的过程，查询时回行时比较耗时，索引查找是快的，回行取数据是慢的。如果能直接从索引中获取数据，将会省去回行过程，提高查询速度，因为索引在内存运行的。

索引覆盖：查询的列恰好时索引的一部分，只在索引就能获取想要的数据，不需要回行到磁盘取数据。如果查询的数据能用到索引覆盖，速度是最快的。用explain 查询语句，看extra项目是否有Using index，如果有则使用到索引覆盖。

MyISAM的索引特点：指向的是数据在磁盘上的位置。

InnoDB的索引特点：

• 主键作为索引，既储存索引值又储存行数据。
• 次级索引是先指向主键id，然后再从id获取行数据，也就是说InnoDB的索引是间接获取数据，中间多了从id获取数据的过程。
• 如果没有主键系统把unique key作为主键，如果也没有unique key，系统内部自动生成row id作为主键。

主键值为随机的插入行数据时，主键节点会分裂，对于MyISAM类型的表，影响不是很大，因为节点的包括的内容比较小(只有指向磁盘地址)，在内存里完成，转移数据时耗时小；对于InnoDB类型的表影响比较大，因为表使用的聚簇索引，每个节点都包括行内容，节点分裂时需要转移的数据比较多，耗时也比较多。

高性能索引是使用自动递增的整型，例如定义主键类型id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT; 插入数据时不用指定主键值，让其有规律的自动增加，减少节点分裂的耗时。

聚簇索引排序慢原因分析

# 有一个表结构和引擎如下，有100000行数据。# 创建表
CREATE TABLE ts(id CHAR(30) PRIMARY KEY AUTO_INCREMENT,val INT NOT NULL DEFAULT 0,str1 VARCHAR(2000),str2 VARCHAR(2000),KEY idval(id,val)
)ENGINE innodb CHARSET utf8;# 查询速度慢
SELECT id FROM ts ORDER BY id;# 查询速度快
SELECT id FROM ts ORDER BY id,val;

查询速度慢的原因：表驱动为InnoDB，主键索引id属于聚簇索引，每个节点包含一行数据内容，而且字段内容比较大，储存时超越磁盘最小块，对主键id排序时会跨很多磁盘的数据块，导致速度很慢。

对联合索引排序快的原因：因为联合索引不是聚簇索引，节点内容很小(只有指向主键id地址)，联合排序时使用了索引覆盖，不需要回行取数据，而且是在内存中完成，索引速度比较快。

当把str1和str2内容比较大的列删除后，两条语句执行速度差别不大。 如果表类驱动型为MyISAM两条语句执行速度差别不大。

(4) 多列索引

在建立多列索引后，必须满足左前缀要求(从左到右按顺序，中间不能断开)，索引才能发挥作用，例如多列索引index(a,b,c)

使用多列索引误区：查询哪个列索引都会发挥作用。

注意：多列索引一定要结合业务逻辑进行优化，例如查100~200元的男装商品，这条查询涉及到价格和商品栏目两列，可以把这两列作为一个多列索引。

(5) 伪哈希索引

在实际中的表的列有可能比较长，例如网址url的列，网址前面的几个字节http://www都是相同的，如果把url列作为索引，前面10个字节都是重复的，要区分网址至少截取16个字节以上，使得索引长度很长。不利于增删改查。

为了解决这个问题，在表中多添加一个url对应hash值的列(例如列名urlhash)，即url值对应唯一hash值，然后使用列urlhash作为索引(整型)，从而大大减少索引的长度，提高查询速度。

(6) 延时索引

用大量数据分页优化说明延时索引技巧。 例如显示搜索结果有100000条，分页显示每页20条，sql语句为 SELECT filed FROM table LIMIT (N-1)*20,20;其中N表示第几页。

• 优先从业务逻辑优化，条件是限制分页数量，也就是说搜到100000条结果，给用户显示最多是40页就以及满足客户需求了，查询时间是ms级别。

为什么要限制N的大小呢？因为limit的偏移量(offset)很大时，效率非常低，limit offset,num的工作机制是先查询，然后再跳过offset获取num条数据，当offset很大时，说明查询结果的数据很大，通过开启profile分析sql语句，发现大部分时间都用在Sending data(即回行传输的数据)上了，限制N的大小非常有必要，百度或谷歌都是现在查询结果的页数，一般都是看前几页搜索结果，极少人回去翻页到几十页去看搜索结果。

• 如果不允许从业务逻辑优化，还有个办法就是利用主键id作为查找条件再筛选结果，大量数据查询时间是ms级别。例如sql语句：SELECT filed FROM baidu WHERE id>100000 LIMIT 0,20;

这种优化查询用到索引所以速度也很快，但是条件是要求数据完整性，不允许删除数据。否则会造成每次查询结果不一致，解决办法时不进行物理删除，用逻辑标记删除，最终在页面上显示时，逻辑删除的条目不显示结果即可。

• 延时索引优化，这种方法没有条件限制，大量数据查询时间为秒级别，例如语句SELECT filed FROM table WHERE id IN (SELECT id FROM table WHERE id>200000 LIMIT (N-1)*20,20);

首先查找取出主键id，然后再通过id来取数据，这种通过索引中间过程再从表中取数据过程叫延时索引，延时索引的好处时，节省了大量的回行时间(sending data时间)，提高查询速度。

上面语句的工作过程是通过分页去取主键id，取主键id过程用到了索引覆盖，速度比较快，省去回行过程，获取id之后在回行取表中的内容，因为回行数量很少，sending data时间也少，索引查询速度也快，当数据量很大时，上面语句的据大多数时间都在取id过程上了。

当数据量很大时，利用延时索引比直接取数据速度要快4倍左右。

(7) 冗余索引

在某个列上可能存在多个索引，例如：

CREATE TABLE user(id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,first_name CHAR(5) NOT NULL DEFAULT '',last_name CHAR(10) NOT NULL DEFAULT '',KEY first_name(first_name),KEY last_name(last_name),KEY full_name(first_name,last_name)
)ENGINE myisam CHARSET utf8;

表中的列first_name既有独立作为索引，也有联合索引full_name，这叫冗余索引，实际中也会用到，单列查询时可以用到索引，多列查询时也可以用到索引。

注意index AB(A,B)和index BA(B,A)是不同的索引，对于列A或B来说，AB和BA属于冗余索引。

(8) 索引的长度和区分度

索引的长度和区分度是矛盾的，长度大区分度高，反之区分度小。到底选多少个字节作为索引长度比较合理呢，需要根据表内容的实际试验。 一般使用方法，针对表截取长度和区分度测试，例如一个中文单词表，共一万多行，有一个字到14个字的行都有。通过测试从1个词开始测试，使用sql语句SELECT count(distinct left(word,6))/count(*) FROM dict;

对于一般系统，区别度能达到10%时，索引性能就可以接受。 针对列中的值，从左到右截取来建立索引：

• 截得越短，重复度越高，区分度越低，索引效果越不好。
• 截得越长，重复度越低，区分度越高，索引效果越好，但是带来负面影响越大，占有空间越大，也会减慢查询速度，增删改操作变慢。

(9) 索引与排序

对于带有排序的sql语句，例如下面语句，可能发生2种情况。

SELECT * FROM goods WHERE cat_id=3 ORDER BY shop_price DESC;

• 跳过排序过程，直接取出最终结果，查询速度快。要达到这个结果，必须事先建立索引，利用索引本身有序的特点，取出来就是有顺序的，所以省略了排序过程，达到查询速度快的效果。
• 没有索引，先从表中取出数据作为临时表，然后再对临时做排序(在内存或磁盘排序)，排序是比较耗时的，因尽量避免，所以查询速度会比较慢。

所以在碰到有排序的查询时，排序的列尽量作为索引(或联合索引)，目的是取出来的数据本身就是有顺序的，避免排序这个耗时的过程，从而提高查询速度。

(10) 索引碎片和维护

索引碎片的形成原因：在长期的数据更改中，索引文件和数据文件都将会产生空洞，形成碎片，索引碎片会对查询速度有影响。

维护索引碎片有两种方法：

# 修改表的nop操作，这个操作不影响表数据
ALTER TABLE 表名 ENGINE 驱动名;# 释放表空间
OPTIMIZE TABLE 表名# 注意：当数据比较大时，维护是很耗时间的，通常在访问量比较少的夜里维护，当数据修改不频繁可以按年来做维护，如果数据修改比较多可以按月来修复。