网易MySQL数据库工程师微专业学习笔记（六）

一、InnoDB中的索引

InnoDB中的索引采用B+树和二分查找来实现快速查询，并且其中定义的B+树一般不是严格定义的B+树，InnoDB中的B+树往往层高并不高，这样查询的次数就可以相对较少。

InnoDB是聚簇表，是索引组织表。表本身就是根据主键排序的大索引。而表上建立的其他索引只存储索引项和主键，利用索引查询时先定位主键，再根据主键在聚簇表中快速定位数据。

mysql中创建索引的语句如下。

create index index_name on table_name(column_name1,column_name2);

InnoDB中的索引可以只索引一列，也可以索引多列。

索引的维护是由数据库自动完成的。插入/修改/删除每一个索引行都自动变成一个内部封装的事务。索引越多，事务越长，代价越高。索引越多对表的插入和索引字段上的修改就越慢。因此，索引并不是越多越好。

二、如何使用索引

1. 依据where查询条件建立索引

例1：select a,b from tab_a where c=?

可以对c列简历索引来提升查询效率。

例2：select a,b from tab_a where c=? and d=?

可以对c和d两列建立联合索引，但是要注意索引列的前后顺序要与where中的顺序一致索引才能生效。如果只对c或d列建立单列索引，也是可以加速查询的，但是只能提升单列的查询速度。

2. 排序order by, group by distinct字段加索引

例1：select * from tab_a order by a;和select a,count(*) from tab_a group by a;

可以通过对a列建立索引来提速

例2：select * from tab_a order by a,b

可以通过对a列和b列建立联合索引来提升效率，同样需要主要索引中列的顺序。

例3：select * from tab_a where c=? order by a

可以通过建立c和a的联合索引来提速，同样需要主要索引中列的顺序。
三、索引与字段选择性

字段选择性是指字段值的重复程度。例如，身份证号一般是不会重复的，其字段选择性就极好。姓名偶尔会有重复的情况，因此选择性也是较好的。而像性别这样的字段会有大量的重复值，其选择性就很差。一般在创建索引时选择性差的字段不适合建立单列索引。但是也有特殊情况，例如要查询理工类专业下的女学生的数据，而且不需要查询男生的数据，因为这类专业中往往女生比较少，因此可以给性别建立一个索引。而在联合索引中选择性好的字段应该放在前面。

四、联合索引与前缀查询

联合索引能为前缀单列、复列查询提供帮助。例如给a、b、c三列简历一个联合索引idx(a,b,c)，这个索引可以对where a=?和where a=? and b=?提速，而对于where a=? and c=?在mysql5.5及以前的版本中只能对前面的a列相关的数据查询进行提速，而在5.5版本以后的版本中可以对两个列都进行提速。因此建立了联合索引idx(a,b,c)可以不用创建idx(a)、idx(a,b)、idx(a,c)这样的索引了，避免冗余。

五、长字段索引

在非常长的字段上建立索引是会影响性能的。InnoDB中索引单字段(utf-8)只能取前767bytes的数据。超过该长度的数据是无法被索引到的。对于像email类型的长字段，其前面的几个字符的选择性较好，可以对其前面的若干个字符进行索引，mysql支持前缀索引的，一般如email和姓名截取前10到30个字符就可以有很好的选择性了。而像住址类数据，一般前半部分的省市信息的选择性不好，而后面的详细地址的选择性较好。这样就不能用前缀索引了，一般就是将字段拆分，省一个字段，市一个字段，以此类推，然后再建立联合索引或单列索引。

六、索引覆盖扫描

对于最核心的sql语句可以考虑索引覆盖，如对于语句select name from tb_user where userId=?可以通过对userId和name建立联合索引来提速，注意顺序，where中的列要在前。这样就不需要回表中去获取name的值了，可以省去一次随机IO，效率最高。在实际应用中一般会对username和password建立这样的索引，因为用户登陆是常用的功能，其中根据用户名查询密码是一个十分核心的sql语句。

七、无法使用索引的情况

1. 索引列进行数学运算或函数运算时无法使用索引

例1：where id+1=10

修改为where id=10-1就可以使用索引了

例2：where year(col)=2007

修改为where col>'2007-01-01' and col<'2007-12-31'就可以使用索引了

2. 未含复合索引前缀的查询

例1：有索引idx(a,b,c)where b=? and c=?无法使用索引

3. 有前缀通配符的查询

例1：like '%XXX%'和like '%XXX'

like 'XXX%'是可以使用索引的。

4. where条件中使用了not，<>，!=的查询也不能使用索引

5. 字段类型不匹配

例1：有一个int类型的a字段并建立索引，where a='123'是不能走索引的。

6. 排序中不能用索引的情况

有索引idx(a,b)

例1：order by b

例2：where a>5 order by b

例3：order by a asc, b desc

要确定查询是否走了索引，可以在具体的查询sql语句前加上explain来分析sql语句的执行计划，需要关注如下几个项目。

1. type，表示查询的方式，full表示全表扫描，其他表示走索引或者其他的提升查询效率的方法。

2. key，表示本次查询最终使用的索引，null表示未使用索引。

3. key_len，表示索引的前缀长度或整个索引的长度。

4. rows，表示扫描了的数据量，值越小越好。

5. extra，表示额外的信息，主要指fetch data的具体方式。

八、Schema设计

Schema设计除了要满足业务的功能需求，还需要满足性能需求，同时还要有一定的扩展性和满足周边需求（如统计、迁移等）。因为关系数据库中修改Schema是高危操作，因此设计要有前瞻性，避免后期大量的修改。要做好Schema设计需要从以下几个方面入手，根据查询需要设计好索引；根据核心查询需求适当的调整表结构；基于一些特殊的业务需求，调整实现方式。

如何设计索引上文中已经详细说明了，而根据查询需求适当的调整表结构或基于特殊需求实现方式就需要DBA的积累和创造性了，这里有几个常见的例子。

1. 反范式，冗余必要的字段

针对核心的sql保留查询结果所必须的冗余字段，避免join。例如，实现用户消息查看的功能，在消息表中冗余用户昵称字段，可以避免查询消息时要做大量的与用户表的join操作。当然，这样做当用修改昵称时，需要级联的修改消息表中的昵称，一般实际项目中不会将所有的昵称都修改，只会修改最近一段时间的消息中的昵称，一般这样的实现用户是可以接受的。

2. 拆分大字段

拆分大字段到单独的表中，避免范围扫描代价大。例如，博文表拆分为两个表，标题表只保留标题、摘要等用于现实文章列表的数据，正文保存在正文表中，用于点开单个文章时显示用。

3. 避免过多字段或过长行

根据sql必要返回设计字段，有必要就拆表，避免过多的字段。因为InnoDB中每个数据页是16K，因此如果每一行的数据变大了，每次扫描就要访问更多的数据页，IO就上升了，因此会降低范围扫描和更新操作的效率。此外，因为InnoDB是聚簇表，因此每个数据页作为B+树的叶子节点，至少存储两条数据，而如果行过长就可能出现行迁移，这样IO就更高了，效率就更差了。

4.分页查询

避免limit+offset中的值过大，这样分页的效率是很差的。应该使用自增主键Id模拟分页。查第一页数据时正常分页，然后得到第一页的max(Id)，也就是最后一个记录的Id，比如说Id是123。那么在第二页的查询中就用where Id>123 limit 100来实现分页。当然这样是有问题的，例如当需要对数据进行排序时，排序后就不能保证Id是有序的，就不能使用这个方法了。而在一些大型的电商平台中，一般是禁止查询100页之后的数据的，这样也能保证分页的效率。

5.热点读数据的特殊处理

根据数据获取的频率或数量不同对热点数据做特殊处理。例如，论坛系统中的置顶帖、公告帖可以单独拆分存储，由于每次访问时都要全部取出来，单独放一起可以避免每次到普通表中随机找出来。

6.热点写数据的特殊处理

根据数据获取的频率或数量不同对热点数据做特殊处理。例如，微博中当一个用户发了一个新的微博时，是会在消息表中为该用户的粉丝没人插入一条消息的。但是当一个用户的粉丝非常多时，比如有一千万的粉丝，如果还是采用前面的方法，那就需要作一千万次的insert操作，这显然是不合理的。因此实际上微博中设定了一个门限值，当用户的粉丝数量大于这个门限值时，就不会在为其每个粉丝插入一条消息数据，而是改为当粉丝获取消息时去查看该用户的微博表，看是否有新的微博需要更新。

7.准实时统计

对不需要实时统计的数据，建立定期更新结果表。例如，一些P2P网站的首页上往往要显示当前的交易总额，如果每次都是去对交易表中的交易额字段进行sum操作的话，在高并发下效率是非常差的，因此实际实现时往往是维护一个计数表，每分钟根据数据的插入时间获取sum值的更新数据，然后更新计数表，避免每次用户访问都要全表扫描交易表。

8. 实时统计改进1-触发器实时统计

对需要精确实时统计的数据利用数据库触发器维护计数表。例如，有的网站有用户量冲亿活动，业务要求数据要实时统计，因为对于第一亿个用户网站会有一些优惠活动，这时候可以通过在用户表上加一个触发器，每次有新用户注册时就给计数表加1，当然这样插入操作的效率会有一定的影响。

9. 实时统计改进2-缓存实时统计

对需要精确实时统计的数据用前端缓存实时维护，对于网站有用户量冲亿活动这个方法比触发器实现要更好，因为不会影响插入的效率。但是当服务器掉电时，重新启动服务器后之前缓存中的统计数据就没有了，当然可以通过对用户表做一次count操作来找回之前缓存的用户总数，但是这样服务器的启动就会变慢。

10. 实时统计改进3-最大自增Id获取总数

很多逻辑可以用自增Id主键的最大值直接作为总数。例如在用户量冲亿活动中，因为大部分网站中用户是没有删除功能的，最多是冻结用户，因此自增Id主键的最大值就是用户总数。

九、可扩展性设计

可扩展性就是指在硬件资源增大有极限的情况下处理尽可能久的线上业务。一般有两个方法，一个是数据分级，就是将一些老数据归档并淘汰；另一个是为数据分布式作准备，主要就是为数据分库分表和水平拆分做准备。一般就是用mysql的分区来实现可扩展性设计。

1. range分区

适合数据需要定期过期处理的打表，可以通过对数据创建时间字段进行按年份分区，这样当需要将某个年份的数据移出表放入历史库中的时候只要对该年份的分区进行数据扫描即可，避免了全表扫描。此外如果要删除某个年份的数据，直接删除改分区的分区文件即可，效率非常高。

2. list分区

适合将来可能要基于地区、类目等方式垂直拆分数据的方式。例如有一个仓库表，里面有仓库所在地区的id，这样就可以对地区id进行list分区，把北方的地区放在北方分区中，南方的放在南方分区中，这样以后如果需要建立北方数据库和南方数据库就可以很快的将数据切分出来。另外如果某个地区的仓库都取消了，那么删除这些仓库数据的操作也是很快的，只要删除一个分区文件即可。

3. hash分区

适合将来要做水平拆分表的情况。例如，对用户表的用户Id做一个hash分区，分成24个区。这样后面如果需要对用户表进行水平拆分，将数据放到多台服务器上时，只要服务器的数量能被24整除即可。例如要分布到8台服务器上，那么只要将每3个分区放到一个服务器上即可。

分区虽然有很多好处，但是在mysql中分区并不是十分的好用。因为mysql中要求每个分区中必须包含主键或者唯一键，且分区字段必须是整数类型或者加上返回整数的函数。

十、满足周边需求

Schema设计中还要在上线前尽可能满足一些未来的额外需求，从而降低未来Schema维护的代价和难度。周边需求一般包括为后台统计数据加特殊的索引，以及定期导出数据增量。

为后台统计数据加特殊的索引往往是因为后台的统计sql与线上的差别较大，因此需要与线上不同的索引来优化后台统计。一般的解决办法是利用mysql的一主多从，在主从数据库上建立不同的索引，将统计数据分流到特定的从库上。而定期导出数据增量往往是为了满足大数据分析的需求，例如将mysql中的增量数据导入hadoop中进行大数据分析。解决办法是在表中添加一个update_time字段，并且该字段是表中第一个timestamp类型的字段。这样在数据插入或更新时，如果不填写该字段，则系统会自动写入一个系统时间戳，这样就可以获取每个数据的插入或者修改时间了。当然这么做要告知web开发人员，在数据写入时不要写入这个数据。

十一、Schema设计与前瞻性

Schema设计与前瞻性就是DBA在之前的设计中出现的错误进行总结并在新项目中避免之前出现的错误的行为。这个只能依赖DBA的设计经验了。这里举几个常见的例子。

1. 对用敏感信息进行加密

早期的数据库中的用户信息都是明文存储的，所以后来有许多网站的账号密码被盗取并在网上公开。为了避免这种情况的再次发生，在数据设计时就要提前设计好加密字段和验证策略表等。

2. 增加is_delete字段进行伪删除

因为有时会因为程序bug导致大量数据被误删除，这样如果数据真的被delete操作delete掉了，那么数据的找回是十分的麻烦的，因此加一个is_delete做伪删除。

十二、Schema设计审核

Schema设计审核有以下几点。

1. 表和字段名是否符合规范。

2. 字段类型和长度设计是否恰当。

3. 表关联关系是否合理。

4. 主键、更新时间保留字段等是否符合要求。

5. 约束和默认值等配置是否恰当。

例如用户名是唯一的且不能为空。

6. 了解业务，表数据量，增长模式

重点需要分析数据量大的表，然后这些表的数据是如何增长的，是定期导入的还是用户不断更新的。然后数据的增长模式是怎么样的，是单纯插入新纪录，还是高频的对数据进行更新。例如，用户状态表和用户操作记录表，这两个表的数据增长模式就是不同的，用户状态表是一次插入高频更新，而用户操作记录表是高频插入基本不会更新的。

7. 数据访问模式和均衡度。

均衡度就是是否有一些数据重复性很高。例如，在转账表中，买家的转账记录可能非常少，而卖家的转账记录非常的多，这样如果只是对用户Id建立一个索引，卖家查看转账记录的时候性能可能还是非常差的。因此就需要将卖家的转账记录和买家的转账记录分表存储，卖家查看转账记录时采用一些准实时的方法来实现。

8. 根据业务需求判断表是否需要分区，表数据需要进行定期归档。

十三、SQL语句审核

SQL语句审核有以下几点。

1. SQL语句的执行频率。

高频的SQL语句可以考虑使用索引覆盖。

2. 表上是否有合适的索引。

3. 单次执行的成本。

主要关注语句需要扫描多少行数据后能够返回结果。

4. 执行模式，锁情况分析。

需要注意一些热点数据可能引发热点锁的问题。例如用户余额表中，大部分用户的余额更新不是很频繁，速度也很快。但是像一些商家用户可能每秒钟都有大量的余额更新操作，导致个别用户的数据长时间被锁住，这就是热点锁。

5. 关注事务上下文。

例如需要审核一些高并发的sql语句是否存在与一些长事务中，这样是否会导致死锁和长时间锁等待的问题。