MySql优化原理（二）

数据库优化

1、使用索引：

           应该尽量避免全表扫描，首先考虑在where及order by、group by涉及到的列上建立索引。

2、优化SQL语句

           （1）在任何地方都不要使用select * from tab这样的语句，应该使用具体的字段列表代替“*”，不要返回任何用不到的字段。

           （2）不在索引列做运算或者使用函数。

           （3）查询尽可能使用limit减少返回的行数。

3、优化数据库对象

           （1）使用数据库连接池；

           （2）使用查询缓存机制，存储select查询的文本及相应结果，指定查询缓冲区大小，指定允许的最大连接进程数；

           （3）优化表的数据类型（使用正确的表示和能存储数据的最短类型，减少内存使用）；

4、优化硬件   

（1）扩大内存，分配更多的内存给Mysql做缓存。

5、特别大的访问量到数据库上，如何做优化？

           （1）使用优化查询的方法；

           （2）主从复制、读写分离、负载均衡

                     通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。一个系统的读操作远远多于写操作。

           （3）数据库分表、分区、分库

            分区就是把一张表的数据分成多个区块，可以在一个磁盘上，分区后，表面上还是一张表，但是数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘的IO性能。

Sql注入的问题

1、什么叫sql注入，如何防止，请举例说明。

buf.append(select  empno, ename, deptno  from emp where ename = ‘‘ ‘ ).append(ename).append(“ ‘ ”);

如果输入为 ‘ or‘1’= ‘1

select  empno, ename, deptno  from emp where ename = “  or  ‘1’ = ’1’  ;

上面的where条件是永远成立的，如果表中有权限限制，比如只能查询本地市的信息，过滤条件中有地市过滤，不过因为输入中有 ‘1’ = ’1’，条件总是成立，导致能够看到所有的城市的职员信息，就产生了权限问题了，用户能看到不该看到的信息。同理，如果是insert或者update等语句的话，通过sql注入将产生不可估量的问题。

解决方法：

           （1）参数绑定。可以使用预编译方法解决，使用prepareStatement进行编译，参数用set方法填装。

           （2）检查变量的数据类型和格式。

连接的问题

1、内连接和外连接的区别。

          

内连接（inner join）：只显示符合连接条件的记录。

而外连接分左外连接、右外连接、全外连接三种。

（1）左外连接（LEFT JOIN ON或LEFT OUTER JOIN ON）：

           即以左表为基准，到右表找匹配的数据，找不到用null补齐。显示左表的全部记录及右表符合连接条件的记录。

如上面两张表：select * from t1 left join t2 on t1.id=t2.id。

（2）右外连接（RIGHT JOIN ON 或RIGHT OUTER JOIN ON）

           即以右表为基准，到左表找匹配的数据，找不到用null补齐。显示右表的全部记录及左表符合连接条件的记录。

如上面两张表：select * from t1 right join t2 on t1.id=t2.id。

（3）全外连接（FULL JOIN 或 FULL OUTER JOIN）

           除了显示符合连接条件的记录外，在2个表中的其他记录也显示出来。

2、联合查询的索引使用。

           在where子句中要加筛选条件，才可以都用上索引。

         交集（inner join）、差集（left join或right join）、并集（union）

 

数据库范式

1、数据库的三级范式

           1NF：强调的是列的原子性，即列不能够在分为其他几列。字段不可再分，原子性。

           2NF：满足2NF必须先满足1NF。一个表必须包含主键，只能说明一个事务，且非主键属性必须完全依赖于主键，不能只依赖于主键的一部分。

           3NF：满足3NF必须先满足2NF。每列都与主键列有直接关系，不存在依赖传递。任何非主属性不依赖于其他非主属性。

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

 

数据库事务

1、事务的4个属性

           事务是由一组SQL语句组成的逻辑处理单元。

 事务的4个属性ACID：

原子性（atomicity）	事务是应用中最小的单位，不可在分割。事务中的所有操作，要么全部完成，要么全部不完成。事务在执行过程中发生错误，会回滚，回到事务开始前的状态。
一致性（Consistency）	事务执行前后，业务数据保持一致，数据的完整性约束没有被破坏。
隔离性（Isolation）	一个事务的执行不能被其他事务所干扰。多个事务并发操作时隔离开来。
持续性（Durability）	一个事务一旦提交，它对数据库的改变就是永久性的，不会回滚。

事务并发带来的问题

           脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。

不可重复度：是指在一个事务内，多次读同一数据。在这个事务还没结束时，另外一个事务对这条数据进行了修改，那么导致第一个事务两次读到的数据可能是不一样的，这样就发生了在一个事务内，两次相同的查询读取到不同的数据，因此称为不可重复读。（一个事务内，两个相同的查询返回了不同的值）　　

例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

幻读：幻读是事务非独立执行时发生的一种现象。

例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

注意：不可重复读的重点是修改：同样的条件，你读取过的数据，再次读取出来发现值不一样了；而幻读的重点在于新增或删除：同样的条件，第一次和第二次读出来的记录数不一样。

           更新丢失：两个事务T1和T2读入同一数据并修改，T2提交的结果覆盖了T1提交的结果，导致T1的修改被丢失。

2、数据库事务的隔离级别

           （1）读未提交：级别最低，可能发生脏读、不可重复读、幻读的问题。

           （2）读已提交：可以避免脏读，可能有不可重复读和幻读发生。

           （3）可重复度（Mysql默认隔离级别）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，可能会有幻读。

           （4）可串行化：所有事务一个接一个的串行执行，可以避免脏读、不可重复读、幻读。

3、Mysql事务控制语句

           BEGIN 或 START TRANSACTION : 显式的开启一个事务。

           COMMIT: 提交事务，并使已对数据库的所有修改称为永久性的。

           ROLLBACK：回滚会结束事务，撤销正在进行的所有未提交的修改。

 

锁的问题

1、2种锁类型：

共享锁（S）：多个事务封锁一个共享页，任何事务都不能修改该页。通常使该页被读取完毕，S锁立即被释放。

排他锁（X）：仅允许一个事务封锁此页，其他任何事务必须等到X锁被释放才能对该页进行访问。X锁一直到事务结束才能被释放。

2、死锁产生：

定义：死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。

           四个必要条件：

           （1）互斥条件：一个资源每次只能被一个进程使用。

           （2）请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

           （3）不剥夺条件：进程已获得的资源，在未使用完之前，不能强行剥夺。

           （4）循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系

3、锁的两种机制（乐观锁和悲观锁）：

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

数据库索引

1、聚集索引与非聚集索引

           聚集索引（聚簇索引），表中记录的物理顺序与键值的索引顺序相同。一个表只能有一个聚集索引。

           聚集索引和非聚集索引的根本区别是表中记录的物理顺序和索引的排列顺序是否一致。

           聚集索引的表中记录的物理顺序与索引的排列顺序一致。优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理的紧随其后。缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

           建议使用聚集索引的场合为：

           A。某列包含了小数目的不同值。 B。排序和范围查找。

           非聚集索引的记录的物理顺序和索引的顺序不一致。

其他方面的区别：

           （1）聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。

           （2）非聚集索引添加记录时，不会引起数据顺序的重组。

建议使用非聚簇索引的场合：

A。此列包含了大数目的不同值。 B。频繁更新的列。

2、组合索引（联合索引）

           基于多个字段而创建的索引就成为组合索引。

3、数据库索引原理的实现

B+Tree（B+树）是数据库系统实现索引的首选数据结构。在Mysql中索引属于存储引擎级别的概念。主要使用MYISAM和InnoDB两个存储引擎的索引实现。

（1）MyISAM索引实现：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引文件仅仅保存数据记录的地址。也叫“非聚簇索引”。不支持外键，只支持表级锁，不提供事务支持。支持全文索引。允许没有主键的表存在。

（2）InnoDB索引实现：（MySQL默认存储引擎）

InnoDB的数据文件本身就是索引。在MyISAM中，索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。也叫“聚集索引”。支持外键，支持行级锁和表级锁，默认是行级锁，提供事务支持。如果没有主键，就会自动生成一个6字节的主键。

4、关系型数据库和非关系型数据库（nosql）的区别：

非关系型数据库的优势：

（1）性能：nosql是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过sql层的解析，所以性能非常高。

（2）可扩展性：基于键值对，数据之间没有耦合性，非常容易扩展。

关系型数据库的优势：

（1）复杂查询：可以用sql语句方便的在一个表以及多个表之间做非常复杂的数据查询。

（2）事务支持：使得对于安全性很高的数据访问要求得以实现。

5、数据库连接池的原理。

Java程序访问数据库的过程是：

           ①加载数据库驱动程序；②通过jdbc建立数据库连接；③访问数据库，执行sql语句；④断开数据库连接。

           连接池的工作原理：思想是连接的复用，通过建立一个数据库连接池以及一套连接使用、分配和管理策略，使得该连接池中的连接可以得到高效安全的复用，避免了数据库连接频繁建立和关闭的开销。

           原理主要由三部分组成，分别为连接池的建立，连接池中连接的使用管理，连接池的关闭。

           第一，连接池的建立。一般在系统初始化时，连接池会根据系统配置建立，并在池中建立几个连接对象，以便使用时能从连接池中获取。

           第二，连接池的管理。连接池内连接的分配和释放对系统的性能有很大影响。

           当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用并作相应处理；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没有达到最大连接数，就重新创建一个连接给请求的客户；如果达到，就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给用户。

           当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过了，就从连接池中删除该连接，并判断当前连接池内总的连接数是否小于最小连接数，若小于，就将连接池充满；如果没超过就将该连接标记为开放状态，可供再次复用。

           第三，连接池的关闭。当应用程序退出时，关闭连接池中所有的连接，释放连接池相关资源，该过程与创建相反。

使用连接池的优点：

           （1）减少连接的创建时间。连接池中的连接是已准备好的，可以重复使用的，获取后可以直接访问数据库，因此减少了连接创建的次数和时间。

           （2）更快的系统响应速度。数据库连接池在初始化过程中，初始化工作已经完成，可以直接利用现有的可用连接，避免了数据库连接初始化和释放过程的时间开销，缩减系统整体响应的时间。

           （3）统一的连接管理。