MySQL数据库基本操作(下)

2018-08-24 本文已影响0人月亮是我踢弯得

一. MySQL事务

事务：将数据库从一种一致性状态转到另一种一致性状态

1．事务的特性

原子性：事务中的所有操作，要么全部执行，要么都不执行
一致性：事务开始和结束后，数据库的完整性不会被破坏
持久性：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失
隔离性：事务之间互不影响

2. 事务的隔离级别

读未提交(read uncommited)：即事务对数据库数据所做的修改，在事务未提交之前，可以被其他事务看到。
读已提交(read commited)：　会产生的问题：不可重复读、幻读
可重复读（默认隔离级别（repeatable read）：A事务读取数据库数据，在处理业务的过程中事务B读取数据并修改完成后，事务A再次读取数据时，所得结果和之前读取的数据一致，并且事务A提交后，不会损害数据的一致性。
串行化(serializable)：表上的一个事务开启后，在该事务提交之前，其他事务不能对表中的数据做任何修改不会出现：脏读、不可重复读、幻读.

3．事务的操作

1. 开启事务　begin 或start transaction
1. 提交事务 commit
1. 回滚事务 rollback
1. 设置保存点 savepoint 保存点名称
1. 回滚到具体的保存点 rollback to 保存点名称

4. 操作事务隔离级别的基本命令

1.查看当前隔离级别
select @@tx_isolation;
2.设置当前会话（连接）的事务隔离级别：
set session transaction isolation level read uncommitted | read committed | repetable read | serializable;

二. MySQL的索引

MySQL索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

1. 索引的分类

注意： 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换MEMORY/HEAP存储引擎：支持HASH和BTREE索引

索引一般分为四类: 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引。

1). 单列索引

(1). 普通索引：这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
- 创建索引，例如CREATE INDEX 索引的名字 ON tablename (列名1，列名2,...);
- 修改表，例如ALTER TABLE tablename ADD INDEX 索引的名字 (列名1，列名2,...);
- 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX 索引的名字 (列名1，列名 2,...) );
  CREATE TABLE book
  (
  　bookid INT NOT NULL,
  　bookname VARCHAR(255) NOT NULL,
  　authors VARCHAR(255) NOT NULL,
  　info VARCHAR(255) NULL,
  　comment VARCHAR(255) NULL,
  　year_publication YEAR NOT NULL,
  　INDEX(year_publication)
  　KEY(year_publication)
  );

唯一索引：这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：
- 创建索引，例如CREATE UNIQUE INDEX 索引的名字 ON tablename (列的列表);
- 修改表，例如ALTER TABLE tablename ADD UNIQUE 索引的名字 (列的列表);
- 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE 索引的名字 (列的列表) );
  CREATE TABLE t1
  (
  　id INT NOT NULL,
  　name CHAR(30) NOT NULL,
  　UNIQUE INDEX UniqIdx(id)
  );

主键索引： 主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。
　　主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。（主键相当于聚合索引，是查找最快的索引）。
CREATE TABLE t2
(
　id INT NOT NULL,
　name CHAR(10),
　PRIMARY KEY(id)
);

2). 组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
组合索引就是在多个字段上创建一个索引
创建一个表t3，在表中的id、name和age字段上建立组合索引
CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),　　　　　　　　　　 INDEX MultiIdx(id,name,age)
);

3) . 全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思．

全文索引可以用于全文搜索，但只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列服务。索引总是对整个列进行，不支持前缀索引：
CREATE TABLE t4
(
　id INT NOT NULL,
　name CHAR(30) NOT NULL,
　age INT NOT NULL,
　info VARCHAR(255),
　FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;

4). 空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。 要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
CREATE TABLE t5
(
　g GEOMETRY NOT NULL,
　SPATIAL INDEX spatIdx(g)
) ENGINE = MyISAM;

2. 根据sql查询语句确定创建哪种类型的索引，如何优化查询

选择索引列：

1). 性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列：在where子句中出现的列，在join子句中出现的列。
2). 考虑列中值的分布，索引的列的基数越大，索引的效果越好。
3). 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。
4). 利用最左前缀
组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，不明白没关系，举几个例子就明白了，例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询
5). 不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。

在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。

三. MySQL的优化及查看数据库占用的内存

1. 查看数据库占用的内存

1)、进去指定schema 数据库（存放了其他的数据库的信息）
use information_schema
2)、查询所有数据的大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES
3)、查看指定数据库的大小
比如说数据库apoyl
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='apoyl';
4)、查看指定数据库的表的大小
比如说数据库apoyl 中apoyl_test表
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='apoyl' and table_name='apoyl_test';

2.MySQL数据库的优化

MySQL数据库优化的八种方式>>>
mysql数据库常见的优化操作总结>>>
MySQL优化的技巧>>>

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

四.如何防SQL注入

SQL注入总结>>>

1. SQL注入

SQL注入，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的，这类表单特别容易受到SQL注入式攻击.

2.如何判断是否被sql注入了

SQL注入一般会在http://xxx.xxx.xxx/abc.asp?id=XX这样等带有参数的ASP动态网页中，有些动态网页中可能只有一个参数，有些可能有n个参数；有些参数是整型，有些参数是字符串型。只要是带有参数的动态网页访问了数据库就有可能存在SQL注入。

我们首选要修改浏览器的设置，以便更好的了解动态网页参数里包含的信息。以IE浏览器为例，把IE菜单-工具-Internet选项－高级－显示友好HTTP错误信息前面的勾去掉。

1)、整型参数的判断

当输入的参数YY为整型时，通常abc.asp中SQL语句原貌大致如下：
select * from 表名 where 字段=YY，所以可以用以下步骤测试SQL注入是否存在。

（1）http://xxx.xxx.xxx/abc.asp?p=YY and 1=2, abc.asp运行异常；
（2）http://xxx.xxx.xxx/abc.asp?p=YY’(附加一个单引号)，此时abc.ASP中的SQL语句变成了select * from 表名 where 字段=YY’，abc.asp运行异常；
（3）http://xxx.xxx.xxx/abc.asp?p=YY and 1=1, abc.asp运行正常，而且与http://xxx.xxx.xxx/abc.asp?p=YY运行结果相同；
如果这三个方面全部满足，abc.asp中一定存在SQL注入漏洞！

2)、字符串型参数的判断

当输入的参数YY为字符串时，通常abc.asp中SQL语句原貌大致如下：
select * from 表名 where 字段=’YY’，所以可以用以下步骤测试SQL注入是否存在。

（1）http://xxx.xxx.xxx/abc.asp?p=YY&nb … 39;1’=’2′, abc.asp运行异常；
（2）http://xxx.xxx.xxx/abc.asp?p=YY&nb … 39;1’=’1′, abc.asp运行正常，而且与http://xxx.xxx.xxx/abc.asp?p=YY运行结果相同；
（3）http://xxx.xxx.xxx/abc.asp?p=YY’(附加一个单引号)，此时abc.ASP中的SQL语句变成了select * from 表名 where 字段=YY’，abc.asp运行异常；
如果这三个方面全部满足，abc.asp中一定存在SQL注入漏洞！

3)、字符被过滤的判断

有安全意识的ASP程序员会过滤掉单引号等字符，以防止SQL注入。这种情况可以用下面几种方法尝试。

（1）ASCII方法:所有的输入部分或全部字符的ASCII代码,如U = CRH(85),一个= CRH(97),等等。
(2)UNICODE方法:在IIS UNICODE字符集实现国际化,我们可以在输入字符串即输入UNICODE字符串。如+ = % 2 b,空格= % 20,等;

混合设置方法:大小是大小写不敏感的,因为根据当时和过滤器的程序员通常要么过滤所有大写字母的字符串,或过滤所有小写的字符串,大小写混合往往会被忽略。如用SelecT代替select,SELECT等。

3.如何防止sql注入

1）普通用户与系统管理员用户的权限要有严格的区分。

如果一个普通用户在使用查询语句中嵌入另一个Drop Table语句，那么是否允许执行呢?由于Drop语句关系到数据库的基本对象，故要操作这个语句用户必须有相关的权限。在权限设计中，对于终端用户，即应用软件的使用者，没有必要给他们数据库对象的建立、删除等权限。那么即使在他们使用SQL语句中带有嵌入式的恶意代码，由于其用户权限的限制，这些代码也将无法被执行。故应用程序在设计的时候，

2）强制使用参数化语句。

如果在编写SQL语句的时候，用户输入的变量不是直接嵌入到SQL语句。而是通过参数来传递这个变量的话，那么就可以有效的防治SQL注入式攻击。也就是说，用户的输入绝对不能够直接被嵌入到SQL语句中。与此相反，用户的输入的内容必须进行过滤，或者使用参数化的语句来传递用户输入的变量。参数化的语句使用参数而不是将用户输入变量嵌入到SQL语句中。采用这种措施，可以杜绝大部分的SQL注入式攻击。不过可惜的是，现在支持参数化语句的数据库引擎并不多。不过数据库工程师在开发产品的时候要尽量采用参数化语句。

3）多使用SQL Server数据库自带的安全参数。

为了减少注入式攻击对于SQL Server数据库的不良影响，在SQLServer数据库专门设计了相对安全的SQL参数。在数据库设计过程中，工程师要尽量采用这些参数来杜绝恶意的SQL注入式攻击。
　　如在SQL Server数据库中提供了Parameters集合。这个集合提供了类型检查和长度验证的功能。

4）加强对用户输入的验证。

总体来说，防治SQL注入式攻击可以采用两种方法， 一是加强对用户输入内容的检查与验证; 二是强迫使用参数化语句来传递用户输入的内容。在SQLServer数据库中，有比较多的用户输入内容验证工具，可以帮助管理员来对付SQL注入式攻击。测试字符串变量的内容，只接受所需的值。拒绝包含二进制数据、转义序列和注释字符的输入内容。这有助于防止脚本注入，防止某些缓冲区溢出攻击。测试用户输入内容的大小和数据类型，强制执行适当的限制与转换。这即有助于防止有意造成的缓冲区溢出，对于防治注入式攻击有比较明显的效果。

如可以使用存储过程来验证用户的输入。利用存储过程可以实现对用户输入变量的过滤，如拒绝一些特殊的符号。如以上那个恶意代码中，只要存储过程把那个分号过滤掉，那么这个恶意代码也就没有用武之地了。在执行SQL语句之前，可以通过数据库的存储过程，来拒绝接纳一些特殊的符号。在不影响数据库应用的前提下，应该让数据库拒绝包含以下字符的输入。如分号分隔符，它是SQL注入式攻击的主要帮凶。如注释分隔符。注释只有在数据设计的时候用的到。一般用户的查询语句中没有必要注释的内容，故可以直接把他拒绝掉，通常情况下这么做不会发生意外损失。把以上这些特殊符号拒绝掉，那么即使在SQL语句中嵌入了恶意代码，他们也将毫无作为。

故始终通过测试类型、长度、格式和范围来验证用户输入，过滤用户输入的内容。这是防止SQL注入式攻击的常见并且行之有效的措施。