前言
如果我们仔细思考一下程序到底做了什么事情，抛开具体的业务逻辑细节而言，程序运行其实是只做了两件事情，一是将数据保存至（新增，修改，删除）数据库，二是将数据从数据库中查询出来。
这些数据的来源可能是前端页面传入，可能是其他程序系统推送的文本文档，消息等。
如果将业务逻辑展开讨论，对于我们程序员来说，业务逻辑的真正体现并不主要在代码上，而是体现于存储数据的表结构的设计上。因为好的表结构设计可以使你的业务处理代码更简洁高效，类比于什么样的数据结构决定了哪些算法才是比较合理高效的。毕竟程序为业务服务，而业务关心的是数据，而代码逻辑只是为数据的流转服务的。
一个老手程序员来说，接触一个陌生的程序，首先要关注它所对应需求文档或者ppt（如果有的话），而后是表结构设计（有哪些表，各个表里都有什么字段，分别用来存储什么业务数据），最后才应该是看代码。
抛开程序的配置性的流程（集群部署，服务器性能，网络因素）， 此文探讨一下单体程序的执行速度。
以我的经验来谈谈几点，你可以尝试来分析是否可以使你的程序更快点。
1.表结构设计
1.1 数据库设计的三大范式
1.1.1 第一所有字段都不可再分解。
1.1.2 表中每列都和主键相关。
1.1.3 第二的基础上，表中每列和主键直接相关而非间接相关。
这三个范式层层递进，具体含义见诸于各种网络博客，我不再赘述。简单谈下我的理解，这三个范式的最终达到的效果是，每张表都仅且只能存贮一种不可再拆分的数据模型，表中字段也只能是代表一种最小粒度的属性。
然而，规范是规范，如果一定严格按照这种范式去设计表结构，那么就忘了程序并不应该是一种遵循一个模板生成的产品，而应该是满足于业务场景的定制产品。
大概率我们都遇见这样的业务场景：主表A存有相关表B的id字段，而如果页面展示主表A信息和B的信息肯定要展示B的name字段，而非仅仅是B的id（甚至不展示id，只展示B的name）。如果严格按照三大范式则B的name必然仅仅在B表中。那么为了展示你可能需要一次关联查询，或者做两次单表查询。如果这两张表中数据量庞大，那么使用关联查询性能你懂的。如果需要两次查询再在代码里面做处理，则又是一次数据库链接，链接也是很宝贵的。
我举这个例子是最简单的，如果是真实业务场景，一个综合查询，你要做的可能就不仅仅是查询两张表了，而是三张，四张都有可能。
如果你将B的name字段冗余进当前的主表A中，则显然这种设计不符合第三范式，因为A表中的存有B表的id和name，这两个字段有依赖传递。但是我们大概都明白，对于一个程序来说，查询的触发次数总是大于新增或者修改的次数。我觉得有必要的话，B的name字段可以冗余进A表当中，毕竟磁盘空间节省抑或对范式的遵循比起业务响应速度的提升，后者才是业务真正关心的。好处是避免了关联查询或者第二次查询，提升程序处理速度，坏处就是你就需要在修改name字段的时候记得除了修改B表的name值，同时还要修改A表中的字段值。但是这种修改操作太少啦，而且以我对社会规范的理解而言，各种系统都并不愿意去修改既有基础信息的。想修改名称？是要付出代价的，身份证上的名字，那可不是好改的，就连王者荣耀改个名字还需要改名卡呢！
2.关联查询
对于数据量大的表来说，关联查询能避免就避免。我倾向于将关联查询拆分为多次单表查询，在代码当中用循环来匹配。之前做某个银行的信用卡积分项目，需要对提前对客户的不用的积分类型进行提前额度的计算，单表用户数据量千万级别，测试时没造那么多造了一百万条。场景是这样的，如果有10中不同的积分类型，100W的用户，则我需要在本月对这个客户的所有的不同的积分类型的客户额度都计算好记录进表，那就是1000W的数据量。
第一次我使用关联表的方式，因为不同的积分类型要关联3张表，星级系数，卡等级系数，类型系数，再加上客户表(100W),写了一条相当复杂的sql关联了4张表将客户的不同额度都计算出来。虽然那3张表数据量并不大，但客户表数据量大啊，架不住sql复杂，各种判断，其速度简直不堪入目。后来我尝试将sql拆分为单表查询，用java代码来做匹配生成想要的数据，速度简直超乎我的想象。虽然写出复杂sql很有成就感，但对于程序而言它或许并不优雅，聪明反被聪明误。就像人生有时候感动的是自己。
3.索引
在失去它之前，你并不知道它有多重要。
我们开发中使用的开发库，往往数据量和生产环境的实际数据量是有差异的。开发环境的数据量小，往往只关注了sql功能的实现，会忽视索引的功能。啊，查出来了，然后止步于此。没有真正的去分析这个sql，有没有优化的空间。当数据量激增，而又没有索引。你就明白，索引重要性不应该仅仅存在于面试题中，有了它，就是这么神奇的提升了你的查询速度。
3.1 索引是啥
索引是数据位用来维护方便数据查找的一种数据结构，指向查找的数据。
如果没有索引，针对某条数据的查找只能像java循环那样逐个遍历匹配（全表扫描）。
3.2 索引的结构
BTREE索引最常见，大部分索引都支持B树索引。InnoDB,MyISAM引擎都支持它。
Mysql的InnoDB索引用的B+tree索引，是BTREE索引的变种。聚簇索引，复核索引，前缀索引，唯一索引默认都是使用的B+tree索引。
B+tree和BTREE的区别是：
B+tree非叶子节点可以说只起到一个目录的作用指向下级节点，而BTREE的每个节点都直接指向对应的数据。
在InnoDB引擎强制为每张表建立聚簇索引，如果有主键那主键就是聚簇索引，如果没有则将第一个唯一索引中的非空字段建立聚簇索引，如果都没有则系统会自动创建隐含的聚簇索引。
一般来说主键就是聚簇索引，而其他的比如普通的索引，唯一索引，前缀索引等辅助索引是非聚簇索引。
聚簇索引叶子节点是包含两种信息：一是所有的上层节点的信息，二是索引对应的数据信息。
非聚簇索引的叶子结点存储的是当前索引和主键的映射关系，也就是说，如果通过非聚簇索引来查找数据一般来说，先找到主键然后再通过主键索引去找到数据。额所以说，通过主键来查找数据效率是最高的。
Mysql中的B+tree：
在B+tree的基础上，在相邻的叶子结点之间增加了指针，好处是方便进行范围搜索。
3.3 优劣
3.3.1 优势
通过与数据建立映射关系，提高数据检索，排序效率，降低数据库IO，降低CPU的消耗
3.3.2 劣势
索引作为一种数据结构，也是一张表，额外占用了数据库空间。
对表数据进行修改，作为用来映射数据的索引，会重新调整索引结构，引起CPU消耗。
3.4 建立原则
数据量大查询频率高的表。
经常被用作查询条件的表字段上建立索引。
使用唯一索引，区分度越高，使用索引效率越高。
索引并不是越多越好，一是数据库维护索引需要一定代价，而是命中多条索引的话，数据库选择去选择索引也 耗费时间。
使用短索引，索引也是存在磁盘中的，相同存贮空间可以存贮更多的短索引，短索引可提高数据库的I/O效率。
利用最左匹配原则，N个列组合的索引，相当于创建了N个索引，如果查询时候使用了组成该索引的前几个字段，这个查询可以使用组合索引来提升查询效率。例如对A,B,C三列建立组合索引，相当于对A,AB,ABC建立了索引。
4.SQL优化
4.1分析系统中SQL执行批次
优化sql,如果你有足够的精力和时间你可以将程序中使用的所有sql都进行分析，但是显然几乎没有人这样做。首先我们可以通过一个些sql指令来分析下针对当前的数据库来说，什么类型的sql执行频次最高。
SHOW GLOBAL STATUS LIKE "Com_______"; 全局来看哪种类型的sql执行频次
SHOW STATUS LIKE "Com_______"; 当前会话级别 哪种类型的sql执行频次
SHOW GLOBAL STATUS LIKE "Innodb_rows_%" 查看Innodb引擎下的相关行数
由于我们大部分情况下都使用的Innodb引擎，可以看出来 read即读取操作是我们操作数据库表最常用的操作。这也就是为啥我们总是强调索引的重要性。在一个系统里读取操作是主流，而索引提升查询效率。
4.2 找出低效SQL
show processlist; 实时查看mysql在执行的线程，包括线程的状态，是否锁表等。
4.3 分析具体SQL explain
首先看下我的表结构
EXPLAIN SELECT * FROM student WHERE id=3; id是索引
EXPLAIN SELECT * FROM student WHERE age=200; age并不是索引
这些分析中的字段都是什么含义呢？如下：
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。代表了关联多个表查询时的优先级，id越大优先级越大，比如，子查询先于外层查询则子查询的id会比外层查询大。如果内连接则，查询的id是同级的。 |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（单表查询 ，即不使用表连接或者子查询）、PRIMARY（主查询，即子查询外层的查询）、UNION（包含UNION的查询中的第二个或者后面的查询语句）、SUBQUERY（子查询，在select或者where列表中包含的子查询），DERIVED(FROM列表中包含的子查询会被标记为DERIVED,即衍生，mysql会递归执行这些子查询，把结果放在临时表中) |
| table | 输出结果集的表 |
| type | 表示表的连接类型，性能由好到差的连接类型为( null—->system —> const —–> eq_ref ——> ref ——-> ref_or_null—-> index_merge —> index_subquery —–> range —–> index ——> all ) |
| possible_keys | 表示查询时，可能使用的索引 |
| key | 表示实际使用的索引 |
| key_len | 索引字段的长度 越短执行效率越高 |
| rows | 扫描行的数量 |
| extra | 执行情况的说明和描述 |
其中type类型展开说下
| type | 含义 |
|---|---|
| NULL | MySQL不访问任何表，索引，直接返回结果 |
| system | 表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现 |
| const | 表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较 |
| eq_ref | 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描 |
| ref | 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个） |
| range | 只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。 |
| index | index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。 |
| all | 将遍历全表以找到匹配的行 |
extra展开说明下
| extra | 含义 | |
|---|---|---|
| using filesort | 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。 | |
| using temporary | 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低 | |
| using index | 表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。 | |
| using where | 在查找使用索引的情况下，需要回表去查询所需的数据 | |
| using index condition | 查找使用了索引，但是需要回表查询数据 |
4.4 分析具体SQL profile
Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
SELECT @@have_profiling; 结果为YES表示支持
SELECT @@profiling; 结果为1表示开启，0没开启。set profiling=1; //开启profiling 开关;
show profiles;展示如下
show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：
SHOW profile FOR QUERY 32;
Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：
SHOW profile cpu FOR QUERY 32;
| 字段 | 含义 |
|---|---|
| Status | sql 语句执行的状态 |
| Duration | sql 执行过程中每一个步骤的耗时 |
| CPU_user | 当前用户占有的cpu |
| CPU_system | 系统占有的cpu |
4.5避免索引失效
4.5.1 隐式转换索引失效。
4.5.2 索引列上运算导致失效。
4.5.3 范围查询（>,<,等）之后的字段索引失效。
4.5.4 遵循最左匹配法则
即多个字段建立的联合索引，例如对A,B,C字段建立联合索引。在使用的时候相当于三种索引，A,AB,ABC这三个索引，所以查询条件至少命中这三种组合其中的一种才能走索引。需要注意的是于查询条件中A,B,C字段出现的顺序没有关系。
4.5.5 尽量使用覆盖索引
即尽量避免select * 操作，最好只查询有索引的列。
4.5.6 or导致索引失效
用or分开的条件，如果or前面有条件有索引而后面没有，那么所有的索引都失效，如果前后条件即使是组合的复合索引，仍旧失效。（and则不会）
建议使用union来替换or。
4.5.7 like %放在前面导致失效
可以使用覆盖索引来解决，但并不能完全避免，如果查询中含有没有索引的列，仍不会走索引。
4.5.8 全表扫描更快
比如某一列上虽然有索引，但是如果根据查询条件来筛选，这个条件能筛选出来的数据量非常大，数据库索性全表扫描，不会走索引。
4.5.9 IS NULL IS NOT NULL有时失效
走不走索引，除了和列上有没有索引外，还与查询条件与数据的匹配量有关系，比如A字段上有索引，但大部分数据的A字段为null ，则is null不会走索引，is not null会走索引。
4.5.10 IN走索引，not in 不走索引
4.6 查看索引的使用情况
show status like 'Handle_read%'; 查看当前会话的索引使用情况
show global status like 'Handle_read%';查看全局的索引使用情况
4.7 尽量避免子查询 用连接查询代替
4.8 limit优化
在对大数据量的表进行分页查询的时候，越往后的数据需要的时间越大，因为对前面的所有的数据进行排序，虽然只是为了查询出最后的几条数据。可以使用对主键进行先查询limit，然后将主键作为关联条件与原来的表进行关联查询。
5.数据库设计
5.1 主从分离
根据数据库的记录操作记录的二进制文件来进行主从复制，将增删改的操作放在主库进行操作，查询的操作放在从库。
5.2 数据分库分表
可以采用sharding-jdbc或者myCat这些组件来讲业务数据分配到不同的数据库，提升数据的操作效率。
6. 数据库锁
6.1 锁分类
按照锁定的数据量，可以分为行锁（开销大，加锁慢，发生冲突的概率低，并发度高），表锁（开销小，加锁快，发生冲突的概率高，并发度低）。按照对数据的操作类型可以分为，读锁（共享锁，对于读取操作同一份数据互不影响），写锁（排它锁，当前操作的数据，其他操作包括读和写都处于等待状态）。
InnoDB,MyISAM,都支持表锁。但InnoDB还支持行锁，这也是它支持事务的主要体现。
6.2 注意
select * from table for update 相当于对查询到的数据加上了悲观锁，在事务完成之前，其他事务或者连接是无法完成对已经上锁的数据进行任何的读取或者写入操作的。
6.3 事务
6.3.1 事务特点
| ACID属性 | 含义 |
|---|---|
| 原子性（Atomicity） | 事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。 |
| 一致性（Consistent） | 在事务开始和完成时，数据都必须保持一致状态。 |
| 隔离性（Isolation） | 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。 |
| 持久性（Durable） | 事务完成之后，对于数据的修改是永久的。 |
6.3.2 事务带来的问题
| 问题 | 含义 |
|---|---|
| 丢失更新（Lost Update） | 当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。 |
| 脏读（Dirty Reads） | 当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。 |
| 不可重复读（Non-Repeatable Reads） | 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。 |
| 幻读（Phantom Reads） | 一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。 |
6.3.3 快照读和当前读
select快照读（照片），第一次执行select语句时，会生成照片，在当前事务中无论再执行此查询时，查询到的数据是一样的。
update,insert,delete是当前读，在update时，首先执行当前读，把涉及到的数据进行加锁（并非一定只是所要修改的数据加锁，如果行锁升级到表锁，则会对所有的数据加锁）。
mysql的默认的隔离级别是Repeatable read,但是可重复读并不能解决幻读的问题。而mysql通过mvcc机制和间隙所来解决幻读问题。mvcc解决快照读的幻读问题。间隙所用来解决当前读的快照问题。
所谓间隙所是指进行范围条件查询而不是相等条件查询的时候，在请求使用共享锁或者排它锁的时候，InnoDB会给涉及到的已有数据以及在范围内但是不存的记录也会加锁。这是需要避免的操作，因为会导致其他数据的阻塞，所以尽量避免锁定的范围。
6.3.4 MVVC
多版本并发控制即MVVC可以认为是行级锁的变种，它在很多情况下避免了加锁的操作。它通过保存数据在某个时间点的快照来说实现的。也就是说，不管需要执行多长的时间，每个事务看到的数据都是一致的。根据事务开始的时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
6.3.5 行锁升级表锁
对于普通的select语句并不会加锁（共享锁也不会加）。
但是update，insert，delete语句必然加排它锁。而且如果在update语句中如果没有走索引，会引起全表扫描，就会导致锁表而非锁定你想修改的行。行表升级为锁表之后，会导致其他事务对此表的所有update操作都处于等待状态。这可能就会导致你程序执行卡顿。
6.4 锁总结
尽量使用索引来筛选出读的数据范围，不用索引会导致行锁升级为表锁，引起其他事务的等待。
尽量缩小间隙锁锁定的范围。
尽量控制事务的大小，减小数据锁定的范围和时间。
7 总结
根据我的经验来看，程序的快慢主要决定于数据库的性能，而非代码处理。
而对数据库访问数据的提升主要体现在索引的架构。
如果数据量巨大，可以考虑分库分表。但索引的设计依然是避免不了的环节。
本文主要是从表设计，数据库设计及索引方面探讨了如何提升。
简单来说就是：建立合适的索引，同事避免索引失效（全表扫描，行锁升级为表锁），避免复杂SQL的使用，注意事务的粒度。
下文探讨如何在避免SQL的基础上如何结合java代码来处理程序。因为我建议尽量多次单表查询后在java代码中做数据的匹配，即将各个表数据的关联的过程从数据库转移到代码程序来处理。
支付宝扫一扫
微信扫一扫
