MySQL笔记

第1章  初识MySQL

1、什么是收据库

　　　　（数据库DataBase，DB）是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合，在关系数据库中，数据库表是一系列二维数组的集合。

主键：用于唯一地标识表中的每一条记录。可以定义表中一列后者多列为主键，主键列上的值唯一且不为空。

       数据定义语言（DDL）：DROP、CREATE、ALTER

       数据操作语言（DML）：INSERT、UPDATE、DELETE

       数据查询语言（DQL）：SELECT

       数据控制语言（DCL）：GRANT、REVOKE、COMMIT、ROLLBACK

 

第3章数据库的基本操作

3.1、创建数据库

       创建数据库是在系统磁盘中划分一块区域用于数据的存储和管理。语法：

CREATE  DATABASE  database_name;

　　　　查看数据库的定义:

SHOW  CREATE  DATABASE  database_name;

3.2、删除数据库

       删除数据库就是将已存在的库从磁盘删除。且不能恢复       

DROP  DATABASE  database_name;

3.3 数据库存储引擎

       数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据的操作。不同的存储引擎提供不同的存储机制。索引技巧、锁定水平

       查看系统所有存储引擎

SHOW  ENGINES

       MySQL的存储引擎：

              InnoDB

              MyISAM

              MeMory

              Merge

              Archive

              FeDerated

              CSV

              BLACKHOLE

          3.3.2、InnoDB存储引擎

1)  InnoDB给MySQl提交了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级别并且也是在SELECT语句中提供一个类似Oracle的非锁定读[l1] 。在SQL查询中，可以自由的将InnoDB类型的表与其他MySQL的表混合起来。甚至在同一个查询中也可以混合。

2)  InnoDB是处理巨大数据量的最大性能设计。他的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹配的

3)  InnoDB存储引擎完全与MySQl服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维护它自己的缓冲池。InnoDB将它的表和索引存在一个逻辑表中，表空间可以包含数个文件

4)  InnoDB支持外键完整性约束。创建表时，如果没有显形在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID，并以此为主键。

5)  InnoDB被用在众多需要高性能的大型数据库站点。InnoDB不创建目录，使用InnoDB时，MySQl将在MySQL数目下创建一个名为ibdata1的10MB[l2] 大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

          3.3.3 MyISAM存储引擎

       MyISAM基于ISAM的存储引擎，并对其进行扩展。它是在Web、数据存储和其他应用环境下常用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。

特性：

1)       在支持大文件的文件系统和操作系统上被支持

2)       当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一个块来自动完成。

3)       每个MyISAM表的最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16个。

4)       最大键长度是1000B，也可以通过编译来改变。对于键长度超过250B的情况，一个超过1024B的键将被用上

5)       BLOB和TEXT列可以被索引。

6)       NULL值被允许在索引的列中。这个值占每个键的0~1个字节。

7)       所有数字键值以高字节优先被存储以允许一个更高的索引压缩。

8)       每表一个AUTO_INCREMENT列的内部处理。MyISAM为INSERt和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快。在序列顶的值被删除后就不能再使用。

9)       可以将数据文件和索引文件放在不同目录

10)    每个字符列可以有不同的字符集

11)    有VARCHAR的表可以固定或动态记录长度

12)    VARCHAR和CHAR列可以多达64KB        

       使用MyISAM引擎创建数据库，将生产3个文件。文件的名称以表的名字开始，扩展名支出文件类型：frm文件存储表定义，数据文件的扩展名为.MYD,索引文件的扩展名.MYI

  3.3.4 MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存，为查询和引用其他表数据提供快速访问。

       特性：

1)       MEMORY表的每个表可以可以有多达32个索引，每个索引16列，以及500B的最大键长度。

2)       MEMORY存储引擎执行HASH和BTREE索引

3)       可以在一个MEMORY表中有非唯一键

4)       MEMORY表使用一个固定的记录长度格式

5)       MEMORY不支持BLOB或TEXT列

6)       MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

7)       MEMORY表在所有客户端之间共享

8)       MEMORY表内容被存在内容中，内存时MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。

9)       当不再需要MEMORY表的内容时，要释放被MEMORy表使用的内存，应该执行 DELETE FROM 或 TRUNCATE TABLE，或者删除整个表

3.3.5存储引擎的选择

             

功能	MyISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事物	NO	NO	YES	NO
支持全文搜索	YES	NO	NO	NO
支持数索引	YES	YES	YES	NO
支持哈希索引	NO	YES	NO	NO
支持数据缓存	NO	N/A	YES	NO
支持外检	NO	NO	YES	NO

 

• 如果要提供提交、回滚和崩溃恢复能力的事物安全能力，并要求实现并发控制，选择InnoDB
• 如果数据表主要用来插入和查询记录，则选择MyISAM
• 如果存放临时数据，数据量不大，且对数据安全性要求不高可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果，
• 如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive支持高并发的插入操作，但不是事物安全的，Archive适合存储归档数据如记录日志

3.4 数据库的增、删

语法：

 CREATE  DATABASE  database_name
 DROP    DATABASE  database_name;

       查看系统中所有的存储引擎：

SHOW  ENGINES;

       查看默认存储引擎          

  SHOW VARIABLES LIKE 'storage_engine'

第4章数据表的基本操作

      4.1创建表

CREATE TABLE<表名>
       （
       字段1  数据类型 [列级别的约束条件] [默认值],

       字段2  数据类型 [列级别的约束条件] [默认值],

       字段3  数据类型 [列级别的约束条件] [默认值],
       .      .
       .
       [表级别约束条件]
       ）engine='engine_name',charset=’字符集’;

• 使用主键(三种方式)

              方式一：在定义时使用主键

   字段名数据类型   PRIMARY KEY [默认值]

              方式二：在定义完所有列之后指定主键

  [CONSTRAINT <约束名>] PRIMARY KEY [字段名]

              方式三：多字段联合主键

PRIMARY KEY [字段1，字段2，。。。。。。]

• 使用外键

              　　外键是用来在两个表之间创建关联的，可以是一列，或者多列，一个表可以有一个或者多个外键。   一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。               外键对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除   另一个表中具有关联关系的行        

 [CONSTRAINT <外键名>] FOREIGN KEY <字段名1>[,字段名2，。。。。]   REFERENCES <主表名>主键列1 [主键列2,.....]

• 使用非空约束

  字段名   数据类型  NOT NULL

• 使用唯一性约束

                     定义完直接使用唯一约束   

  　　　　字段名  数据类型 UNIQUE

                     定义完所有列之后指定唯一约束

 　　　  [CONSTRAINT <约束名>] UNIQUE (<字段>)

• 使用默认约束

                     如果插入一条新纪录时没有为这个字段赋值，那么系统就会自动为这个字段赋值

    字段名  数据类型  DEFAULT  默认值

• 设置表的属性值自动增加        

 　　字段名 数据类型  AUTO_INCREMENT    

      4.2 查看数据表结构            

  　　desc table_name;
     show CREATE TABLE table_name;

NULL：表示该列是否可存储null值

Key：表示该列是否已经有索引

Default：表示是否有默认值，默认值是多少

Extra:获取给定列的有关信息，如自增。

      4.3 修改表

• 修改表明                     

　　ALTER  TABLE  <旧表名>  RENAME  [TO]  新表明　　

• 修改字段的数据类型                     

　　ALTER  TABLE  <表名>   MODIFY  <字段名> <数据类型>　　

• 修改字段名                 

    ALTER  TABLE  <表名>  CHANGE  <旧字段名>  <新字段名><新数据类型>　　

• 添加字段                    

 　　ALTER  TABLE  <表名>  ADD  <新字段名><数据类型> [约束条件] [FIRST | AFTER 已存在字段名]　　

• 删除字段                  

   ALTER  TABLE  <表名>  DORP  <字段名>　　

• 修改字段的排列位置                     

ALTER  TABLE  <表名>  MODIFY  <字段1><数据类型>  FIRST|AFTER  <字段2>；　　

• 更改表的存储引擎

                     查看当前表的存储引擎：　　　　

　　SHOW CREATE TABLE table_name;　　

                     修改：

　　ALTER TABLE <表名> ENGINE = <更改后的存储引擎>　　

                     外键约束是用来保证数据的参照完整性，如果表之间需要关联外键，却指定了不同的存储引擎，这些表之间是不能创建外键约束的。

• 删除表的外键约束                    

　　ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>　　

      4.4 删除表

• 删除没有被关联的表                   

  DROP TABLE  [IF EXISTS] 表1，表2，表3.....　　

• 删除被其他表关联的主表

                     关联表的情况下，如果直接删除父表，会失败，是因为直接删除，将会破坏表的参照完整性。如果必须删除，可以删除出字表，再删除父表。如果想单独删除父表。只需要将关联表的外键                          约束取消，就可删除父表

                     1.删除字表中的外键列

                     2.删除父表

第5章数据类和运算符

5.1 数据类型介绍

5.1.1整数类型

       数值型数据主要用来存数字。整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。

                            TINYINT                    1个字节。

                            SMALLINT                2个字节。

                            MEDIUMINT             3个字节。

                            INT（INTEGER）             4个字节。

                            BIGINT（大整数）     8个字节。

           INT(11)这11表示的是该数据类型指定的显示宽度，数值的位数小于指定的宽度时，会由空格填充，如果插入大于显示宽度，只要该值不超过该类型整数的取值范围，数值依然可以插入而且能够显示出来

5.1.2浮点数类型和定点数类型

       浮点数分为两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M,N）来表示，其中M称为精度，表示总共的位数；N称为标度，是表示最小数的位数。DECIMAL实际上是以串存放。

       在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币,科学数据等）

       使用DECIMAL的类型比较好。另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点型时要注意

5.1.3 日期与时间类型

YEAR

       YEAR存储时只需要一个字节

1. 以4位字符串或者4位数字格式表示YEAR 范围为 1901~2155
2. 以2位字符串表示 "00"~"69" 代表2000~2069 "70"~"99" 带包1970~1999
3. 以2位数字表示表示YEAR 1-69代表 2001~2069 70~99 代表 1970~1999

TIME:

       TIME类型用于只需要时间信息的值，在存储时需要3个字节。格式"HH:MM:SS"的字符串，TIME类型的取值范围为-838:59:59~838:59:59,TIME类型不仅可以用于表示一天的时间，还可能是某个事件过去的时间或者两个事件之间的时间间隔。TIME可以是“HHMMSS”格式的、没有间隔的字符串，或者，HHMMSS格式的数据，假定是有意义的时间。

       为TIME分配简写值时注意啊例如数值1122

              如果没有冒号，MySQL解释值时，假定最右边的两位是秒，解释为过去的时间1122 解释为00:11:22

              如果有冒号则解释为当天的时间则解释为11:22:00     

DATE: 

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3个字节。在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，但要符合DATE的日期格式

格式：

       字符串类型 ""、""、""、""

       数字类型YY-MM-DD 或者YYMMDD

       函数 CURRENT_DATE 和NOW() 插入当前系统日期

DATETIME

DATETIME类型用在需要同时包含日期和事件信息的值，存储时需要8个字节，格式"YYYY-MM-DD HH:MM:SS"，在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入。

MySQL允许“不严格”语法。任何标点符号都可以用作日期部分或者时间部分之间的间隔符。

 

TIMESTAMP

       TIMESTAMP的显示格式与DATETIME相同，但是TIMESTAMP列的取值范围小于DATETIME的取值范围。

       DATETIME存储日期数据时，按实际输入的格式存储，与时区无关，而TIMESTAMP值的存储时以UTC格式保存的，存储时对当前时区进行转换，检索时再转回当前时区。就是说，查询时，根据当前时区的不同，显示的时间值不同。

5.1.4 文本字符串类型

字符串类型除了存储字符串数据外，还可以存储比如图片、声音的二进制数据。

MySQL中支持两类字符串数据：文本字符串、二进制字符串。

MySQl中文本字符串有：CHAR、VARCHAR、TEXT、ENUM、SET

VARCHAR和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度，而不是取决于类型的最大可能尺寸。

1.CHAR和VARCHAR

       CHAR(M)为固定长度字符串，在定义是指定字符串列长。导尿管保存时再右侧填充空格以达到指定的长度。M表示类长度。当检索CHAR值时，尾部的空格被删除。

       VARCAHR(M)的长度可变的字符串，M表示最大列长度。M的返回时0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际上占用的空间为字符串的实际长度加1.VARCHAR在值保存和检索时尾部的空格仍保留

2.TEXT类型

保存如文章内容，评论等。当保存或查询时，不删除尾部空格。

3.ENUM类型

       是一个字符串对象，其值在创建时再列规定中枚举一个值

       语法：字段名 ENUM （'值1','值2','值3'......）

       ENUM类型的字段在取值时，只能在制定的枚举列表中取（insert的值的枚举的索引），而且一次只能去一个，如果创建的成员中有空格时，其尾部的空格将自动被删除，ENUM值在内部用整数表示，每一个枚举值均有一个索引，列表值所允许的成员值从1开始编号。ENUM值一种列索引顺序排列，并且空字符串排在飞空字符串前，NULL值排在其他所有枚举值前

       4.SET类型

       SET是一个字符串对象，可以有零个或多个值，最多可以有64个成员。

       语法：字段名 SET('值1','值2','值3'.....'值n')

       与ENUM类型形同，SET值在内部用整数表示，列表中每一个值有一个索引编号。当创建表时，SET成员值的尾部空格将会自动删除。ENUM类型字段只能从定的列中选择一个值插入,SET类型的列可以从定义的列值中选择多个字符,如果插入SET字段中的列值重复，则MySQL自动删除重复的值，插入SET字段值的顺序并并不重要，MySQL会在存入数据时，按照定义的顺序显示。

     5.1.5二进制字符串类型

       1.BIT类型

              BIT类型是位字段类型。M表示每个值的位数，范围为1~64如果M省略，默认为1.如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。

       2.BINARY和VARBINARY类型

              列名称 BINNARY（M）或者VARBINARY(M)

              BINNARY长度固定，不足长度右边用\0补齐

              VARBINARY长度可变

       3.BLOB

              是一个二进制大对象，用来存储可变两的数据。

5.2 如何选择数据类型

1.CHAR 和VARCHAR之间的选择

1)  区别：

• CHAR是固定长度字符，VARCHAR是可变长度字符。
• 检索时CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格
• CAHR是固定长度，索引它的处理速度比VARCHAR的速度快，但是缺点就是浪费空间，对存储不大，速度有要求的则用CHAR反之用VARCHAR

2)       存储引擎对CHAR和VARCAHR的影响

•  对于MyISAM存储引擎：最好使用固定长度的数据列代替可变长度的数据列。这样可以是整个表静态化，从而使数据检索更快，用空间换时间
•  对于InnoDB存储引擎：使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，单由于VARCHAR是按照实际的长度存储的，比较节省空间，所以对磁盘I/O和数据存储总量比较好      

     2.ENUM 和SET

              ENUM 是存储在多选一类型 SET是多选多类型

              ENUM和SET的值是一字符串形式出现的，但在内部，MySQL以数值的形式存储。

     3.BLOB和TEXT

              都是存储大容量信息的。BLOB主要存储图片、音频文件。TEXT只能存储纯文本 

5.3比较云算符

       1.等于运算符 =

       等号用来判断数字、字符串、和表达式是否相等。相等返回1 不等返回0；比较字符串不区分大小          写，不能用空值NULL的判断。

       2完全等于<=>

       比等于加个判空处理，两边都为空则返回1，否则0，

       3.不等于 != 后者<>

       用来判断数字、字符串、和表达式是否不相等，相等返回0 不等返回1

         4.ISNUll和IS NOT NULL 检验值是否为空

         5.BETWEEN..AND...

         6.LEAST语法 LEAST(值1,值2,......,值n)；返回最小值。

         7.GREATEST(值1,值2,.....);返回最大值。

         8.IN 、NOT IN

         9.LIKE 

       10.REGEXP用来匹配字符串      expr REGEXP 匹配条件

                     通配符：

                             '^' 匹配以该字符后面字符开头的字符串

                             '$' 匹配以该字符后面的字符结尾的字符串。

                             '.'匹配任意单个字符

                             '[...]' 匹配在方括号内的任意字符。[abc] 匹配a,b,c[a-z]匹配任一字母，[0-9]匹配任意数字

                             '*'匹配零个多多个在他前面的字符。例如X*，匹配人一多个X.[0-9]*,匹配任意多个数字。

      

       在MySQL中需要输入特殊符号如单引号、双引号、或者反斜线时注意使用转义字符\

 

第6章 MySQL函数

6.1 数学函数

• 绝对值函数ABS（X）
• 返回圆周率的函数PI（）；      
• 平方根SQRT（X）
• 求余MOD（X,Y）；
• 获取整数的函数
• CEIL（X）返回不小于X的最小整数值
• CEILING（X）返回不小于X的最小整数值
• FLOOR（X）返回不小大X的最小整数值
• 获取随机数的函数
• RAND（）
• RAND（X）返回一个随机浮点数值v，范围在0到1，不带参数的RAND()每次产生的随机数值不同，当参数x相同时，将产生相同的随机数，不同参数产生不同随机数
• ROUND（x）返回最接近参数x的整数
• ROUND（X,Y）返回最接近参数x的整数，其值保留到小数点后面y位
• TRUNCATE(X,Y) 返回舍去至小数点后y位的数字x
• 符号函数SIGN（x）：返回参数的符号，x的值为负、零或正时返回结果一次为-1、0或1.
• n  幂运算

              POW（X,y）、POWER（X,y）返回x的y次乘方的结果值

              EXP（X）返回e的x乘方后的值

• n  对数运算

              LOG（X）LOG10(X)

6.3字符串函数

•  CHAR_LENGTH(str):返回字符串中的字符个数
•  LENGTH（str）：返回字符串的字节长度。
•  CONCAT（s1,s2....）连接参数产生新字符串
•  CONCAT_WS (X,s1,s2)：拼接 X为分隔符
•  INSERT（s1,x,len,s2）：替换字符串，返回s1
•  返回指定长度的字符串

                            LEFT（s,n）:返回字符串s开始的最左边n个字符

                            RIGHT（s,n）

•  填充字符串

                            LPAD（s1,len,s2）

                            RPAD(s1,len,s2)

                            在s1左/右拼接s2

•    删除空格LTRIM（s）RTRIM（s）TRIM（s）
•   删除指定字符串的函数TRIM（s1 FROM s):删除s中两端所有的字符串s1,
•    重复生成字符：REPEAT（s,n）
•  空格函数SPACE(n)：返回一个有n个空格组成的字符串
•  替换函数REPLACE(s,s1,s2)使用字符串s2代替字符串s中所有的字符串s1.
•   比较字符串大小的函数STRCMP(s1,s2):如所有的字符串均相同，则返回0，如根据当前分类次序，第一个参数小于第二个，则返回-1，其他返回1.
•   获取字符串函数SUBSTRING(s,n,len)、MID(s,n,len):两个作用相同，从字符创的s的n位置开始截取长度为len的字符。
•   匹配子串开始的函数：LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1):3个函数作用相同。返回字符串str1在字符串str中的开始位置。
•   字符串逆序的函数REVERSE(s):将字符串反转。
•   返回指定位置的字符串的函数：ELT(N,值1,值2,值3.....)；N为数字类型几返回该位置的值。
•   返回指定字符串的位置的函数：FIELD(s,s1,s2,s3......);返回字符串s在列表s1,s2...中第一次出现位置。
•   返回子串位置的函数FILD_IN_SET(s1,s2)：返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号分开的字符串组成的列表；
•    选取字符串的函数MAKE_SET(X,s1,s2,....)：返回由X的二进制数指定的相应位置的字符串组成的字符串。

 

6.4 日期函数

n  获取当前日期/当前时间

                     CURDATE()/CURRENT_DATE()作用相同，返回当前日期 YYYYMMDD

                     CURTIME()/CURRENT_TIME()作用相同，返回当前时间  HHMMSS

                     CURRENT_TIME()/CURRENT_DATE()+0将日期/时间转成数值型

                     CURRENT_TIMESTAMP()/LOCALTIME()/NOW()/SYSDATE();  YYYYMMDD HHMMSS

      

n  获取月份的函数

                     MONTH(date)返回date对应的月份

                     MONTH(date)返回date对应的月份的应为全名

      

n  获取星期的函数

                     DAYNAME(d)返回d对应的工作日的英文名称

                     DAYOFWEEK(d)返回d对应的一周中的索引

                     WEEKDAY(d)返回d对应的工作日的索引

      

n  获取星期数的函数

                     WEEK(d):计算日期d是一年中的第几周

                     WEEKOFYEAR(d):计算某天是一年中的第几周

      

n  获取天数的函数

                     DAYOFWEEK(d)返回d是一年中的第几天

                     DAYOFMONTH(d)返回是一个月中的第几天

      

n  获取年份、季度、小时、分钟和秒钟的函数

                     YEAR(date) 返回date对应的年份

                     QUARTER(date) 返回date对应一年中的季度值。

                     MINUTE(time) 返回time对应的分钟数。

                     SECOND(time) 返回time对应的秒数

      

n  获取日起指定值的函数

                     EXTRACT(type FORM date): type取值 YEAR YEAR_MONTH

                     时间和描红转换函数

                     TIME_TOS_SEC(time): 转成秒

                     SEC_TO_TIME(seconds)：转成HH：MM：SS

      

n  计算日期和时间的函数

                     DATE_ADD

n  将日期和事件格式转化的函数

                     DATE_FORMAT(date,format)

                     TIME_FORMAT(time,format)

                     GET_FORMAT(val_type,format_type);

6.5 条件判断函数

n  IF(expr,v1,v2):如果表达式expr是TRUE则返回v1，否则返回v2

n  IFNULL(v1,v2):假如v1不为null，则返回v1,否则返回v2。

n  CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END:如果表达式expr值等某个vn则返回对应位置THEN后面的结果。

      

6.6 系统信息

n  VERSION()返回指定MySQL服务器版本的字符串。

n  CONNECTION_ID():返回MySQL服务器当前连接的次数。每个连接都有唯一的ID

n  SHOW PROCESSLIST；/SHOW FULL PROCESSLIST: 显示有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态，帮助识别出那些有问题的查询语句等。DATABASE()和SCHEMA()函数返回使用utf8字符集的默认（当前）数据库名称。

      

n  获取用户名函数：USER(),CURRENT_USER、SYSTEM_USER()、SESSION_USER()

n  获取字符串的字符集合排序方式：CHARSET(str) 返回字符串 str自变量的字符集；COLLATION(str)返回字符串str的字符排列方式

      

n  获取最后一个自动生成的ID值的函数

       LAST_INSERT_ID()

      

6.7 加/解密函数

n  加密函数：PASSWORD(str)/MD(str),ENCODE(str,pswd_str);ENCODE使用pwsd_str作为密码加密str。

n  解密函数：DNCODE(crypt_str,pswd_str);

6.8 其他函数

n  不同进制转化函数：CONV(N,from_base,to_base)

n  IP地址和数字相互转换的函数：INET_ATON(expr)

n  加锁函数和解锁函数：

                     GET_LOCk(str,timeout)设法使用字符串str给定的名字得到一个锁，持续时间timeout秒。

                     RELEASE_LOCK(str)解开被GET_LOCK()获取的，用字符串str所命名的锁，

                     IS_FREE_LOCK(str)检查锁是否可用

                     IS_USED_LOCK(str) 检查锁是否正在使用

n  重复执行指定操作的函数

                     BENCHMARK(count,expr)重复count次执行表达式expr

n  改变字符集的函数

                     CONVERT(str USING 字符集)

n  改变数据类型的函数

                     CAST(x,AS type) CONVERT(X,type)

      

             

第7章查询数据

• 条件查询中where后面同时有AND和OR时，AND的优先级高于OR
• 查询结果去重distinct
• 在分组查询时，使用GROUP_CONCAT(column_name);将每个分组中column_name字段的所有种类值显示出来；
• HAVING和WHERE区别？

                     HAVING在数据分组后进行过滤来分组，而WHERE在分组之前用来选择记录。

                     GROUP BY子句中使用WITH ROLLUP

• 使用WITH ROLLUOP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和

 

limit

limit [偏移量] 行数

偏移量：从什么位置开始，如果偏移量没有，则默认从0开始

 

正则查询

选项	说明	例子
^	匹配文本的开始字符	‘^b’ 匹配以b开头的字符串
$	匹配文本的结束字符	‘st$’匹配以st结尾的字符串
.	匹配任意一个字符	‘b.t’匹配任何b和t之间有一个字符的字符串
*	匹配0个或者多个在它前面的字符	‘f*n’匹配字符串n前面有任意个字符串f
+	匹配前面的字符1次后者多次	ba+匹配以b开头后面紧跟至少有一个a
<字符串>	匹配包含指定字符串的文本
[字符集]	匹配字符集中的任何一个字符	[az]匹配a或者z
[^]	匹配不在括号中的任何字符	[^abc]匹配任意不含a、b、c的字符串
字符串{n,}	匹配前面的字符串至少n次	b{2}匹配2个或者更多个b
字符串{n,m}	匹配前面的字符串至少n次，至多m次。如果n为0，此参数为可选参数。	b{2,4}匹配最少2个，最多4个b

 

第8章插入删除数据

n  插入数据

              insert into table_name(column_list) values (values_list);

n  同时插入语多条记录

              insert into table_name(column_list) values (values_list1),(values_list2),(values_list3)....;

n  将查询结果插入列表中

              insert into table_name1(column_list1) select (column_list2) from table_name2 where (condition)

n  更新数据

              update table_name set column_name1=value1,column_name2=value2.....where (condition);

n  删除数据

              delete from table_name [where <condition[l3] [l4] >];

              删除表中的所有记录还可以使用TRUNCATE TABLE语句，truncate将直接删除原来的表，并重新                 创建一个表，其语法是truncate table table_name.truncate直接删除的是表，而不是记录，因此执行速            度比delete快

 

第9章索引

       索引是用于快速找出在某一列中有一个特定值的行。不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行。

9.1 索引简介

索引是对数据库表一列或者多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。

9.1.1 索引的含义特定

       索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出某个或多个列中有一特定值的行，所有MySQl列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

       索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有的存储引擎支持每个表至少 16个索引。总索引的长度至少为256字节。MySQL的存储类型有两种BTREE和HASH

       MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

n  索引的优点：

       1.创建唯一索引，保证数据库中每条数据的唯一性。

       2.加快查询速度。

       3.实现数据的参考完整性方面，可以加速表和表之间的连接。

       4.在分组和排序查询时，可以显著减少查询中分组和排序的时间。

n  索引的不足：

       1.随着数据量增加创建、维护索引耗费时间。

       2.索引耗费磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

       3.当表中的数据进行增加，删除，修改时，索引也要动态地维护，降低了数据的维护速度。

n  索引的分类

       1.普通索引和唯一索引

              普通索引是MySQL中的基本索引类型，允许在定义所以的列表中插入重复的值和空值。

              唯一索引的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。主见索引是一种         特殊的唯一索引，不允许有空值。

       2.单列索引和组合索引

              单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

              组合索引指在表的多个字段组合上创建的索引，只有在查询条件

       3.全文索引

              全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复   值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM           存储引擎支持全文索引。

       4.空间索引

              空间索引是对空间数据类型的字段建立索引，MySQL中的空间类型数据有4中

              geometry、point、linestring、polygon,MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正      规索引类似的语法创建空间索引，创建空间索引的列，必须将其声明为 NOT NULL，空间索引只能在存 储引擎为MyISAM的表中创建。

9.1.3索引的设计原则。

       1.索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、  UPDATE的语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更改。

       2.避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段创建索引。

       3.数据量小尽量不用索引。

       4.在条件表达式中经常用到的不同值较多的列建立索引。

       5.当唯一性是某种数据本身的特征时，指定唯一的索引

       6.在频繁进行排序和分组的列上建立索引。

9.2 创建索引

     9.2.1 创建表的时候创建索引

n  语法

                     CREATE TABLE table_name [col_name data_type]

                     [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]

n  参数解释

1. UNIQUE、FULLTEXT、SPATIAL:为可选参数，分别表示唯一索引、全文索引、空间索引。
2. INDEX、KEY：为同义词，两者作用相同，用来指定创建索引，
3. col_name:需要创建索引的字段列，
4. index_name:索引名称

              1.创建普通索引

                     CREATE TABLE table_name(

                     col_name data_type,

                     ..

                     INDEX(col_name)

                     );

              使用 explain 语句查看索引是否正确使用，explain语句输出结果的各行解释如下：

              select_type:指定所使用的查询类型。取值用SIMPLE、PRIMARY、UNION、SUBQUERY

              table：指定数据库读取数据表的字段

              type:指定本数据表和其他数据表之间的关系，可能取值有system、const、eq_ref、ref、range、index、                  All

              prossible_keys:给出MySQL在搜索数据记录时可选用的各个索引。

              key：MySQL实际使用得索引。

              key_len:索引按字节计算的长度，key_len数值越小，表示越快

              ref：给出关联关系中另一个数据表里的数据列的名字。

              rows：mysql在执行这个查询时预计会在从这个数据表中读出的数据的个数。

              extra：有关联操作有关的信息。

              2.创建唯一索引

                     唯一索引能减少查询索引列操作的执行时间，尤其是对比较庞大的数据表，它与前面的普通索          引类似，不同的就是：索引的值位置，单允许有空值，如果是组合索引，则列值的组合必须唯一。

              CREATE TABLE table_name(

              col_name data_type,

              ..

              UNIQUE INDEX index_name(col_name)

              );

              3.创建单列索引

              单列索引是指在数据表中某一个字段上创建索引，一张表中可以同时创建多个单列索引。

              CREATE TABLE table_name(

              col_name data_type,

              ..

              INDEX index_name(col_name)

              );

             

              4.创建组合索引

              CREATE TABLE table_name(

              col_name data_type,

              ..

              INDEX index_name(col_name1,col_name2)

              );

              组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可使用组合索引，而是遵循      "最左前缀"：利用索引最左边的列集来匹配行，这样的列集称为最左前缀。例如id、name、age这三个  字段构成组合索引。索引行中按id/name/age的顺序存放，索引可以搜素下面的字段组合(id,name,age)、      (id,name)、(id)。如果列不构成索引最左的前缀，MySQL不能使用局部索引，如(age)或者(name,age)组合       则不能使用索引。

              5.创建全文索引：

              FULLTEXT(全文索引)可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引。并且只      为CHAR、VARCHAR、和TEXT列创建索引。

              CREATE TABLE table_name(

              col_name data_type,

              ..

              FULLTEXT INDEX index_name(col_name1,col_name2)

              );

             

              6创建空间索引

              CREATE TABLE table_name(

              col_name data_type,

              ..

              SPATIAL INDEX index_name(col_name1,col_name2)

              );

     9.2.2在已经存在的表上创建索引

              方式一：

              ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]                     (col_name[length],....) [ASC|DESC]

              方式二：

              CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[length],...)        [ASC|DESC]

      9.3删除索引

       方式一：

       ALTER TABLE table_name DROP index_name;

       方式二：

       DROP INDEX index_name ON table_name;

             

第10章存储过程和函数

一、     存储过程[l5] 

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

       1、提高代码的重用性

       2、简化操作

       3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

(一)        、创建语法

DELIMITER $

CREATE PROCEDURE 存储过程名(参数列表)

       BEGIN

              存储过程体（一组合法的SQL语句）

       END

DELIMITER ;

注意：

1、参数列表包含三部分

       参数模式参数名参数类型

       举例：

       in stuname varchar(20)

       参数模式：

              in：该参数可以作为输入，也就是该参数需要调用方传入值

              out：该参数可以作为输出，也就是该参数可以作为返回值

              inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略

       存储过程体中的每条sql语句的结尾要求必须加分号。

       存储过程的结尾可以使用 delimiter 重新设置

语法：

       delimiter 结束标记

案例：

delimiter $

(二)        、调用语法

CALL 存储过程名(实参列表);

1.空参列表

#案例：插入到admin表中五条记录

创建存储过程

       DELIMITER $

       CREATE PROCEDURE myp1()

       BEGIN

              INSERT INTO admin(username,`password`)

              VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');

       END $

       DELIMITER ;

调用

CALL myp1()$

2.创建带in模式参数的存储过程

案例1：创建存储过程实现根据女神名，查询对应的男神信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))

BEGIN

       SELECT bo.*

       FROM boys bo

       RIGHT JOIN beauty b ON bo.id = b.boyfriend_id

       WHERE b.name=beautyName;

END $

#调用

CALL myp2('柳岩')$

 

#案例2 ：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))

BEGIN

       DECLARE result INT DEFAULT 0;#声明并初始化

       SELECT COUNT(*) INTO result#赋值

       FROM admin

       WHERE admin.username = username

       AND admin.password = PASSWORD;

       SELECT IF(result>0,'成功','失败');#使用

END $

 

#调用

CALL myp3('张飞','8888')$

3.创建out 模式参数的存储过程

#案例1：根据输入的女神名，返回对应的男神名

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))

BEGIN

       SELECT bo.boyname INTO boyname

       FROM boys bo

       RIGHT JOIN

       beauty b ON b.boyfriend_id = bo.id

       WHERE b.name=beautyName ;

      

END $

 

 

#案例2：根据输入的女神名，返回对应的男神名和魅力值

 

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)

BEGIN

       SELECT boys.boyname ,boys.usercp INTO boyname,usercp

       FROM boys

       RIGHT JOIN

       beauty b ON b.boyfriend_id = boys.id

       WHERE b.name=beautyName ;

      

END $

 

 

#调用

CALL myp7('小昭',@name,@cp)$

SELECT @name,@cp$

4.创建带inout模式参数的存储过程

#案例1：传入a和b两个值，最终a和b都翻倍并返回

 

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)

BEGIN

       SET a=a*2;

       SET b=b*2;

END $

 

#调用

SET @m=10$

SET @n=20$

CALL myp8(@m,@n)$

SELECT @m,@n$

(三)        、删除存储过程

#语法：drop procedure 存储过程名

DROP PROCEDURE p1;

DROP PROCEDURE p2,p3;

(四)        、查看存储过程的信息

DESC myp2;

SHOW CREATE PROCEDURE  myp2;

 

二、     函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

       1、提高代码的重用性

       2、简化操作

       3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新

函数：有且仅有1 个返回，适合做处理数据后返回一个结果

(一)        创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型

BEGIN

       函数体

END

注意：

       1.参数列表包含两部分：

              参数名参数类型

       2.函数体：肯定会有return语句，如果没有会报错

       如果return语句没有放在函数体的最后也不报错，但不建议return 值;

       3.函数体中仅有一句话，则可以省略begin end

       4.使用 delimiter语句设置结束标记

(二)        调用语法

SELECT 函数名(参数列表)

1.无参有返回

#案例：返回公司的员工个数

CREATE FUNCTIONmyf1() RETURNS INT

BEGIN

       DECLARE c INT DEFAULT 0;#定义局部变量

       SELECT COUNT(*) INTO c#赋值

       FROM employees;

       RETURN c;

END $

 

SELECT myf1()$

2.有参有返回

#案例1：根据员工名，返回它的工资

 

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE

BEGIN

       SET @sal=0;#定义用户变量

       SELECT salary INTO @sal   #赋值

       FROM employees

       WHERE last_name = empName;

      

       RETURN @sal;

END $

 

SELECT myf2('k_ing') $

 

#案例2：根据部门名，返回该部门的平均工资

 

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE

BEGIN

       DECLARE sal DOUBLE ;

       SELECT AVG(salary) INTO sal

       FROM employees e

       JOIN departments d ON e.department_id = d.department_id

       WHERE d.department_name=deptName;

       RETURN sal;

END $

 

SELECT myf3('IT')$

 

(三)        查看函数

 

SHOW CREATE FUNCTION myf3;

 

(四)        删除函数

DROP FUNCTION myf3;

 

#案例

#一、创建函数，实现传入两个float，返回二者之和

 

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT

BEGIN

       DECLARE SUM FLOAT DEFAULT 0;

       SET SUM=num1+num2;

       RETURN SUM;

END $

 

SELECT test_fun1(1,2)$

三、     变量的使用

系统变量：

       全局变量

       会话变量

 

自定义变量：

       用户变量

       局部变量

 

(一)        系统变量

说明：变量由系统定义，不是用户定义，属于服务器层面

注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

使用步骤：

1、查看所有系统变量

show global|[session]variables;

2、查看满足条件的部分系统变量

show global|[session] variables like '%char%';

3、查看指定的系统变量的值

select @@global|[session]系统变量名;

4、为某个系统变量赋值

方式一：

set global|[session]系统变量名=值;

方式二：

set @@global|[session]系统变量名=值;

1)       全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

①查看所有全局变量

SHOW GLOBAL VARIABLES;

②查看满足条件的部分系统变量

SHOW GLOBAL VARIABLES LIKE '%char%';

③查看指定的系统变量的值

SELECT @@global.autocommit;

④为某个系统变量赋值

SET @@global.autocommit=0;

SET GLOBAL autocommit=0;

 

2)       会话变量

作用域：针对于当前会话（连接）有效

①查看所有会话变量

SHOW SESSION VARIABLES;

②查看满足条件的部分会话变量

SHOW  SESSION  VARIABLES  LIKE  '%char%';

③查看指定的会话变量的值

SELECT @@autocommit;

SELECT @@session.tx_isolation;

④为某个会话变量赋值

SET @@session.tx_isolation='read-uncommitted';

SET SESSION tx_isolation='read-committed';

(二)        自定义变量

说明：变量由用户自定义，而不是系统提供的

使用步骤：

       1、声明

       2、赋值

       3、使用（查看、比较、运算等）

1.      用户变量

作用域：针对于当前会话（连接）有效，作用域同于会话变量

赋值操作符：=或:=

①声明并初始化

SET @变量名=值;

SET @变量名:=值;

SELECT @变量名:=值;

 

②赋值（更新变量的值）

方式一：

       SET @变量名=值;

       SET @变量名:=值;

       SELECT @变量名:=值;

方式二：

       SELECT 字段 INTO @变量名 FROM 表;

③使用（查看变量的值）

SELECT @变量名;

2.      局部变量

作用域：仅仅在定义它的begin end块中有效

应用在 begin end中的第一句话

 

①声明

DECLARE 变量名类型;

DECLARE 变量名类型[DEFAULT 值];

 

 

②赋值（更新变量的值）

 

方式一：

       SET 局部变量名=值;

       SET 局部变量名:=值;

       SELECT 局部变量名:=值;

方式二：

       SELECT 字段 INTO 具备变量名

       FROM 表;

③使用（查看变量的值）

SELECT 局部变量名;

 

 

#案例：声明两个变量，求和并打印

 

用户变量

       SET @m=1;

       SET @n=1;

       SET @sum=@m+@n;

       SELECT @sum;

 

局部变量

       DECLARE m INT DEFAULT 1;

       DECLARE n INT DEFAULT 1;

       DECLARE SUM INT;

       SET SUM=m+n;

       SELECT SUM;

 

 

用户变量和局部变量的对比

	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

四、     流程控制结构

顺序、分支、循环

(一)        分支结构

1.if函数

语法：if(条件,值1，值2)

功能：实现双分支

应用在begin end中或外面

2.case结构

语法：

情况1：类似于switch

case 变量或表达式

when 值1 then 语句1;

when 值2 then 语句2;

...

else 语句n;

end

 

情况2：

case

when 条件1 then 语句1;

when 条件2 then 语句2;

...

else 语句n;

end

应用在begin end 中或外面

 

3.if结构

 

 

语法：

if 条件1 then 语句1;

elseif 条件2 then 语句2;

....

else 语句n;

end if;

功能：类似于多重if

 

只能应用在begin end 中

 

案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

 

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR

BEGIN

       DECLARE ch CHAR DEFAULT 'A';

       IF score>90 THEN SET ch='A';

       ELSEIF score>80 THEN SET ch='B';

       ELSEIF score>60 THEN SET ch='C';

       ELSE SET ch='D';

       END IF;

       RETURN ch;

       END $

 

SELECT test_if(87)$

 

#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500

 

 

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)

BEGIN

       IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;

       ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;

       ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;

       END IF;

      

END $

 

CALL test_if_pro(2100)$

 

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

 

CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR

BEGIN

       DECLARE ch CHAR DEFAULT 'A';

      

       CASE

       WHEN score>90 THEN SET ch='A';

       WHEN score>80 THEN SET ch='B';

       WHEN score>60 THEN SET ch='C';

       ELSE SET ch='D';

       END CASE;

      

       RETURN ch;

END $

 

SELECT test_case(56)$

 

 

 

(二)        循环结构

 

分类：

while、loop、repeat

 

循环控制：

 

iterate类似于 continue，继续，结束本次循环，继续下一次

leave 类似于  break，跳出，结束当前所在的循环

1.while

语法：

[标签:]while 循环条件 do

       循环体;

end while[标签];

 

联想：

 

while(循环条件){

 

       循环体;

}

2.loop

语法：

[标签:]loop

       循环体;

end loop [标签];

可以用来模拟简单的死循环

3.repeat

语法：

[标签：]repeat

       循环体;

until 结束循环的条件

end repeat [标签];

 

#1.没有添加循环控制语句

#案例：批量插入，根据次数插入到admin表中多条记录

DROP PROCEDURE pro_while1$

CREATE PROCEDURE pro_while1(IN insertCount INT)

BEGIN

       DECLARE i INT DEFAULT 1;

       WHILE i<=insertCount DO

              INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');

              SET i=i+1;

       END WHILE;

      

END $

 

CALL pro_while1(100)$

 

 

/*

 

int i=1;

while(i<=insertcount){

 

       //插入

      

       i++;

 

}

2.添加leave语句

#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止

TRUNCATE TABLE admin$

DROP PROCEDURE test_while1$

CREATE PROCEDURE test_while1(IN insertCount INT)

BEGIN

       DECLARE i INT DEFAULT 1;

       a:WHILE i<=insertCount DO

              INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');

              IF i>=20 THEN LEAVE a;

              END IF;

              SET i=i+1;

       END WHILE a;

END $

CALL test_while1(100)$

 

3.添加iterate语句

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次

TRUNCATE TABLE admin$

DROP PROCEDURE test_while1$

CREATE PROCEDURE test_while1(IN insertCount INT)

BEGIN

       DECLARE i INT DEFAULT 0;

       a:WHILE i<=insertCount DO

              SET i=i+1;

              IF MOD(i,2)!=0 THEN ITERATE a;

              END IF;

             

              INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');

             

       END WHILE a;

END $

 

 

CALL test_while1(100)$

 

/*

 

int i=0;

while(i<=insertCount){

       i++;

       if(i%2==0){

              continue;

       }

       插入

      

}

五、MySQL事务

用 begin, rollback, commit 来实现，begin 开始一个事务，rollback 事务回滚，commit 事务确认。

 

1.事务的四大特性（ACID）

   原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

   一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

   隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

   持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

 

下面演示zhangsan给lisi转账1000元的示例：

   放弃事务，转账失败：

              START TRANSACTION;

              UPDATE account SET balance=balance-1000 WHERE id=1;

              UPDATE account SET balance=balance+1000 WHERE id=2;

              ROLLBACK;

 

   提交事务，转账成功：

              START TRANSACTION;

              UPDATE account SET balance=balance-1000 WHERE id=1;

              UPDATE account SET balance=balance+1000 WHERE id=2;

              COMMIT;

 

2.设置回滚点：

       如下，第二条update语句失效后，便COMMIT，提交了事务。

              START TRANSACTION;

              UPDATE account SET balance=balance-1000 WHERE id=1;

              SAVEPOINT  rol_01;

              UPDATE account SET balance=balance+1000 WHERE id=2;

              ROLLBACK  TO  SAVEPOINT  rol_01;

              COMMIT

3.并发事务

1、事务的并发问题是如何发生的？

     多个事务 同时 操作 同一个数据库的相同数据时

2、并发问题都有哪些？

     脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据

    不可重复读：一个事务多次读取，结果不一样

     幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据

3、如何解决并发问题

         通过设置隔离级别来解决并发问题

4、隔离级别

• Read Uncommitted（读取未提交内容）

ü  在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读取提交内容）

ü  这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

• Repeatable Read（可重读）

ü  这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

• Serializable（可串行化） 

ü  这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

         这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

• 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
• 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

 

                            脏读          不可重复读         幻读

read uncommitted:读未提交     ×                ×              ×       

read committed：读已提交      √                ×              ×

repeatable read：可重复读     √                √              ×

serializable：串行化          √                √              √

六、MySQL的锁

       锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制 的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。

       Mysql用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。

1.MySQL锁概述

       相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

   表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

   行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

   页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

2.MyISAM表锁

       MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

       对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！根据如表20-2所示的 例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

2.1 MyISAM存储引擎的写锁阻塞读例子：

       当一个线程获得对一个表的写锁后，只有持有该锁的线程可以对表进行读写操作。其他线程的读、写操作都会等待，直到锁被释放为止。

       加锁:  lock  table  table_name write;

       释放锁: unlock  tables;

 

2.2 MyISAM存储引擎的读锁阻塞写例子:

一个session使用LOCK TABLE命令给表film_text加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。

（对锁的表都是能读，不能写，锁的那个会话不能读其他未锁的表）

加锁:  lock  table  table_name  read;

释放锁: unlock  tables;

\

2.3如何加表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在示例中，显式加锁基本上都是为了演示而已，并非必须如此。

给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。例如， 有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL：

2.4查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

 

mysql> show status like 'table%';

      Variable_name | Value

       Table_locks_immediate | 2979

       Table_locks_waited | 0

       rows in set (0.00 sec))

 

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

 

 

2.5并发插入（Concurrent Inserts）

上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

 

   当concurrent_insert设置为0时，不允许并发插入。

   当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

   当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

在下面的例子中，session_1获得了一个表的READ LOCAL锁，该线程可以对表进行查询操作，但不能对表进行更新操作；其他的线程（session_2），虽然不能对表进行删除和更新操作，但却可以对该表进行并发插入操作，这里假设该表中间不存在空洞。

 

 

可以利用MyISAM存储引擎的并发插入特性，来解决应 用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

 

2.6MyISAM的锁调度

前面讲过，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后 到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原 因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

l  通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

l  通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

l  通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。 
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制带来的问题和解决办法。这 里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语 句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每 一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

3.InnoDB锁

1、事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

l  原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

l  一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

l  隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

l  持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

 

l  更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

l  脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。

l  不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

l  幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

3、事务隔离级别

        

       “脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

 

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

 

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

• 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)
• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。 
  下面语句都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

select * from table where ?; 

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏 感，可能更关心数据并发访问的能力。

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。下表很好地概括了这4个隔离级别的特性。 

4.获取InonoD行锁争用情况

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

 

 

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

 

5.InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁。

• 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
• 
  对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据。 
  对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁模式兼容性列表: 

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。 
事务可以通过以下语句显式给记录集加共享锁或排他锁：

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
• 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

6.InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;

• 1

Query OK, 0 rows affected (0.15 sec)

mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

 

在上面的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下例所示： 
创建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;

mysql> alter table tab_with_index add index id(id);

 

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。 
在下面的例子中，表tab_with_index的id字段有索引，name字段没有索引：

mysql> alter table tab_with_index drop index name;

• 1

Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 
Warnings: 0

mysql> insert into tab_with_index  values(1,'4');

• 1

Query OK, 1 row affected (0.00 sec)

mysql> select * from tab_with_index where id = 1;

• 1

 

InnoDB存储引擎使用相同索引键的阻塞例子 

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 
在下面的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);

• 1

Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 
Warnings: 0

InnoDB存储引擎的表使用不同索引的阻塞例子 

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。 
比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

mysql> explain select * from tab_with_index where name = 1 \G

mysql> explain select * from tab_with_index where name = '1' \G

• 1
• 2

7.间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。 
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

• 1

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！下面这个例子假设emp表中只有101条记录，其empid的值分别是1,2,……,100,101。 
InnoDB存储引擎的间隙锁阻塞例子 

小结

本文重点介绍了MySQL中MyISAM表级锁和InnoDB行级锁的实现特点，并讨论了两种存储引擎经常遇到的锁问题和解决办法。

对于MyISAM的表锁，主要讨论了以下几点： 
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。 
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。 
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。 
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，本文主要讨论了以下几项内容： 
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。 
（2）介绍了InnoDB间隙锁（Next-key)机制，以及InnoDB使用间隙锁的原因。 
在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.共享锁（又称读锁）、排它锁（又称写锁）：

       InnoDB引擎的锁机制：InnoDB支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁。

   共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

   排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

   意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

   意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

说明：

1）共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。

2）对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

**对于锁定行记录后需要进行更新操作的应用，应该使用Select...For update 方式，获取排它锁。（用共享锁，在读了之后再写会阻塞，会导致死锁）

这里说说Myisam：MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁。

3）InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 

2.乐观锁、悲观锁：

悲观锁：悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

1）使用悲观锁，我们必须关闭mysql数据库的自动提交属性，采用手动提交事务的方式，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

2）需要注意的是，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)。

3）补充：MySQL select…for update的Row Lock与Table Lock

使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键（或有索引的地方），MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

乐观锁：

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做（一般是回滚事务）。那么我们如何实现乐观锁呢，一般来说有以下2种方式：

 

1）.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

2）.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

 

总结：两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

　　另外，高并发情况下个人认为乐观锁要好于悲观锁，因为悲观锁的机制使得各个线程等待时间过长，极其影响效率，乐观锁可以在一定程度上提高并发度。

 

3.表锁、行锁

表级锁(table-level locking)：MyISAM和MEMORY存储引擎

行级锁(row-level locking) ：InnoDB存储引擎

页面锁(page-level-locking)：BDB存储引擎

 

表级锁：开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

 

第11章视图

11.1 视图概述

从一个或者多个表中导出的一个虚表。

 11.1.1视图的含义

视图是一个虚表，是从数据库中一个或者多个表中导出来的表。还可以从已存在的视图的基础上定义。通过视图看到的数据只是存放在基本表中的数据，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

 11.1.2视图的作用

       1,简单化

              看到的就是需要的，可以对经常被使用的查询定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

       2.安全性

              通过视图用户只能查询和修改它们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索先知道特定的数据库对象上，但不能授权到数据库特定行和特定列上。

              (1)使用权限可被限制在基表的行、列的子集上

              (2)使用权限可被限制在多个基表的连接所限定的行上

              (3)使用权限可被限制在基表中的数据的统计汇总上。

              (4)使用权限可被限制在另一个视图的子集上，或是一些视图和基表合并后的子集上。

       3.逻辑数据独立性

              视图可以帮助用户屏蔽真实表结构变化带来的影响。

11.2 创建视图

       创建视图基于SELECT查询的结果

        11.2.1创建视图的语法

               CREATE [OR REPLACE] [ALGORITHM= {UNDEFINED |MERGE |TEMPTABLE}]

               VIEW view_name [(column_list)]

               AS SELECT_statement

               [WITH [CASCADED | LOCAL] CHECK OPTION]

              REPLACE：表示替换已创建的视图

              ALGORITHM:表示视图选择的算法，

                     UNDEFINED：表示MySQL将自动选择算法。

                     MERGE：表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应部分

                     TEMPTABLE:表示将视图的结果存入临时表，然后用临时表来执行语句。

              SELECT_statement:表示SELECT 语句

              [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内。

                     CASCADED:为默认值，表示更新视图时要满足所有相关视图和表的条件

                     LOCAL表示更新视图时满足该视图本身定义的条件即可。

11.3 查看视图

n  DESC view_name

n  SHOW TABLE STATUS LIKE '视图名'

n  SHOW CREATE VIEW view_name;

n  SELECT * FROM informatiom_schema.views;

11.4 修改视图

       视图的CRUD 和表基本一样

      

11.5视图和表的区别和联系

       区别：

1. 视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化表，而表不是。
2. 视图没有实际的物理记录，而表有。
3. 表是内容，视图是窗口
4. 表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改
5. 视图是查看数据表的一种方法，可以查询数据表中某一个字段构成的数据，只是一些SQL语句的集合。从安全角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
6. 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
7. 视图的建立和删除只影响试图本身，不影响对应的基本表。

       联系：

              视图是基本表之上建立的表，它的结构和内容都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

      

第12章 MySQL触发器

12.1 创建触发器

       触发器(trigger)是个特殊的存储过程，但是触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MySQL自动调用。触发器可以包含复杂的SQL语句，主要用于满足复杂业务规则和要求

12.1.1 创建只有一个执行语句的触发器 

       创建触发器的语法：

              CREATE TRIGGER trigger_name trigger_time trigger_event

              ON table_name FOR EACH  ROW trigger_stmt[l6] 

             

              trigger_time:触发时机，before 、after

              trigger_event:触发事件 INSERT、UPDATE、DELETE、

              table_name：建立触发器的表明，即在那张表上创建触发器

              trigger_stmt:触发器执行语句

             

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程勋，触发器是由事假来触发某个操作，这些事件包括INSERT、UPDATE、DELETE语句。

 

 

CREATE TRIGGER ins_sum BEFOER INSERT ON account FOR EACH ROW SET @sum =@sum +NEW.amount;

 

SET @sum=0;

 

SELECT @sum;

12.1.2创建有多个执行语句的触发器

CREATE TRIGGER trigger_name trigger_time trigger_event

       ON tbl_name FOR EACH ROW

       BEGIN

              语句执行列表

       END

12.2 查看触发器

       SHOW TRIGGERS;

       在triggers表中查看触发器信息

       在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过select来查看，语法：SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition

      

12.3 删除触发器

       DROP TRIGGER [scheme_name] trigger_name

       scheme_name:表示数据库名称，是可选的。如果省略了scheme_name，将从当前数据库中舍弃触发程序；

 

13.1 权限表

 MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db脚本初始化。存储账户权限信息的表有：user表、db表、host表、table_priv表、columns_priv表和procs_priv表

 13.1.1 user表

              记录允许连接到服务器的账号信息，里面的权限是全局性的。user表中一般有42个字段。分为4类：用户列、权限列、安全列、资源控制列

              用户列：包括Host、User、Password分别表示主机名、用户名、密码。其中User和Host为User表的联合主键。只有3个值都都匹配时，才允许建立连接。

             

              权限列：权限列的字段取决于用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询查询权限、修改权限、等普通权限，还包括关闭服务器、超级权限和加载用户等高级权限。user表中对应的权限是针对所有用户数据库的。这些字段值的类型为ENUM默认是N，使用GRANT和UPDATE语句更改用户权限。

             

              安全列：安全列只有6个字段，两个是ssl相关。两个是x509相关，另外两个是授权插件相关。ssl用于加密。x509标准可用于标识用户；Plugin字段标识可用于验证用户身份的插件，如果该字段为空，服务器使用内建授权授权验证机机制验证用户身份。可以通过SHOW VARIABLES LIKE ‘have_openssl’查询服务器是否支持ssl功能。

              4.资源控制列

              资源控制列的字段用来限制用户使用的资源，包含4个字段

              （1）max_questions------用户每小时允许执行的查询操作次数

              （2）max_updates------用户每小时允许执行的更新操作次数

              （3）max_connections------用户每小时允许执行的连接操作次数

              （4）max_user_connections------用户允许同时建立的连接次数

              一个小时内用户查询或者连接数量查过资源控制限制，用户将被锁定，直到下一个小时才可以在此执行对应的操作，可以使用GRANT语句更新这些字段的值。

13.1.2 db表和host表

       db表存储用户对某个数据库的操作权限，决定用户能从哪个主机存取那个数据库。

       host表存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致的控制，这个权限表不受GRANT和REVOKE语句影响，

13.1.3 table_priv表、columns_priv表

              table_priv表用来对表设置操作权限

              columns_priv表用来对表的某一列设置权限。

             

13.1.4 procs_priv表

       对存储过程和存储函数设置操作权限

13.2 账户管理

     13.2.1 登录和退出MySQL服务器

       使用root用户登录本地MySQL服务器的 test库

       mysql -h localhost -u -p test

       使用root用户登录本地MySQL服务器的MySQL数据库，同时执行一条查询语句

       mysql -h localhost -u root -p MySQL -e "DESC person"

       -e执行SQL语句。

     13.2.2 新建普通用户

       1、使用CREATE USER 语句创建新用户

       使用CREATE USER 或者GRANT语句时，服务器会修改相应的用户授权表，添加或者修改用户及其权限。

       语法：

       CREATE USER user_specification

              [,user_specification]....

      

       user_specification:

              user@host

              [

                     IDENTIFIED BY [PASSWORD] 'password'

                     | IDENTIFIED WITH auth_plugin [AS 'auth_string']

              ]

       user:要创建的用户名称

       host：表示允许登录的用户主机名称

       IDENTIFIED BY：表示用来设置用户的密码

       [PASSWORD]:表示使用哈希值设置密码

       ‘password’：表示用户登录时使用的普通明文密码

       IDENTIFIED WITH：为用户指定一个身份验证插件

       auth_plugin:插件名称，

       auth_string:

       每添加一个用户，CREATE USER语句会在MySQL.user表中添加一条记录，但是新创建账户没有任何权限。如果添加的账户已经存在，CREATE USER 语句会返回一个错误

       2.使用GRANT语句创建新用户

       GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权

       语法：  

              GRANT privileges ON db.table

              TO user@host [IDENTIFIED BY 'password'][,user [IDENTIFIED BY 'password']]

              [WITH GRANT OPTION]

              privileges：表示赋予用户的权限类型

              db.table :表示用户的权限所作用的数据库中的表

       3.直接操作MySQL用户表

              不管是CREATE USER 或者GRANT，在创建新用户时，实际上都是在user表中添加一条新的记录。因此可以使用INSERT语句向user表中直接插入一条巨鹿来创建一个新的用户。

              INSERT INTO MySQL.user(Host,User,Password,[privilegelist])

              VALUES ('host','username',PASSWORD('password'),privilegelist);

              privilegelist:权限值，只能去‘Y’/'N'

     13.2.3 删除普通用户

              1.使用DROP USER

              DROP USER user[,user];

              DROP USER 'user'@'localhost':删除user在本地登录权限

              DROP USER;：删除所有授权表的账户权限

              2.使用DELETE语句删除用户

              DELETE FROM MySQL.user WHERE host='hostname' and user='username'

     13.2.4 root用户修改自己的密码

              1.使用MySQLadmin命令在命令行指定新密码

              mysqladmin -u username -h localhost -p password "newpwd"

2.修改MySQL数据库的user表

UPDATE mysql.user set Password = PASSWORD("rootpwd") WHERE User ="root" and Host="localhost"

3.使用set语句修改root用户密码

SET PASSWORD=PASSWORD("rootpwd")

13.2.5 root用户修改普通用户的密码

SET PASSWORD FOR 'user'@'host' =PASSWORD("somepassword")

使用UPDATE语句修改普通用户的密码

UPDATE MySQL.user SET Password=PASSWORD("pwd") WHERE User="username" AND Host ="hostname"

使用GRANT语句修改普通用户密码

GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIFIED BY 'somepassword';

13.2.6普通用户修改密码

SET PASSWORD = PASSWORD("newpassw")

13.2.7 root用户密码丢失的解决办法

13.3权限管理

13.3.1 MySQL的各种权限

       账户权限信息被存储在MySQL数据库的 user、db、host、table_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存

1. CREATE 和DROP权限，可以创建新数据库和表，或删除（移掉）已有数据库和表。如果将MySQL数据库中的DROP权限授予某个用户，用户可以删除MySQL访问权限保存的数据库
2. SELECT、INSERT、UPDATE和、DELETE权限允许在一个数据库现有的表上实施操作
3. SELECT权限只有在它们真正从一个表中检索行时才被用到
4. INDEX权限允许创建或删除索引，INDEX适用已有表。如果具有某个表的CREATE权限，可以在CREATE TABLE语句中包含索引定义
5. ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表
6. CREATE ROUTINE 权限来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。
7. GRANT权限允许授权给其他用户。可用于数据库、表和保存的程序
8. FILE权限给予用户使用LOAD DATA INFILE 和SELECT ..INTO OUFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件FILE权限允许用户在MySQL服务器具有写权限的目录下创建文件，单不能覆盖已有文件。

其余的权限用于管理性操作，它使用MySQLadmin程序或SQL语句实施。

1. reload命令告诉服务器将授权表重新读入内存；flush-privileges是reload的同义词；refresh命令清空所有表并关闭/打开记录文件；其他flush-xxx命令执行雷士refresh的功能，但是范围更有限，并且在某些情况下可能更好用。例如，如果只想清空记录文件，flush-logs是比refresh更好的选择。
2. showdown命令关掉服务器。只能从MySQLadmin发出命令
3. processlist命令显示在服务器内执行的线程的信息（即其他账户相关的客户端执行的语句）。kill命令杀死服务器线程。用户总是能显示或杀死自己的线程，但是需要PROCESS权限来显示或杀死其他用户和SUPER权限启动的线程。
4. kill命令能用来终止其他用户或更改服务器的操作方式。

13.3.2 授权

              授权可以分为多个层级

              1.全局层级

                     全局权限适用于一个给定服务器中的所有数据库。这些权限存储在MySQL.user表中。GRANT            ALL ON *.* 和REVOKE ALL ON *.* 只授权和撤销全局变量

              2数据库层级

                     数据库权限适用于一个给定数据库中的所有目标。这些权限存储在MySQL.db和MySQL.host           表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.*只能授予和撤销数据库权限

              3表层级

                     表权限适用于一个给定表中的所有列。这些权限存储在MySQL.table_priv表中。GRANT ALL              ON        db_name.table_name和REVOKE ALL ON db_name.table_name 只授予和撤销表权限

              4.列层级

                     列权限适用于一个给定表中的单一列。这些权限存储在MySQL.columns_priv表中。当使用                    REVOKE时，必须指定与被授权列相同的列。

              5.子程序层级

                     CREATE ROUTINE 、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。             这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以                 被授予子程序层级，并存储在MySQL.procs_priv表中

      

              GRANT语法：

                     GRANT priv_type [(columns)] [,priv_type [(columns)]] ...

                     ON [object_type] table1,table2,...,tablen

                     TO user [IDENTIFIED BY [PASSWORD] 'password']

                     [,user [IDENTIFIED BY [PASSWORD] 'password']] ..

                            [WITH GRANT OPTION]  

                     object_type =TABLE | FUNCTION | PROCEDURE

                    

u  priv_type:权限类型

u  columns:权限作用于那些列上，不指定该参数，表示作用于整个表

u  table1,table2,tablen表示授予权限的列所在的表

u  object_type:指定授权作用的对象类型包括TABLE、FUNCTION和PROCEDURE当从旧版本的                                 MySQL升级时，要使用object_type子句，必须升级授权表；

u  user：表示用户账户，由用户名和主机名构成，形式是‘username’@hostname‘；

u  IDENTIFIED BY 参数用于设置密码

u  WITH 关键字后可以跟一个或者多个GRANT OPTION。GRANT OPTION 的取值有5个

                            （1）GRANT OPTION：被授权的用户可以将这些权限赋予别的用户。

                            （2）MAX_QUERIES_PER_HOUR count：设置每个小时可以执行count次查询

                            （3）MAX_UPDATES_PER_HOUR count:设置每个小时可以执行count次更新

                            （4）MAX_COUNNECTIONS_PER_HOUR count：设置每小时可以建立count个连接

                            （5）MAX_USER_CONNECTIONS count:设置单个用户可以同时你建立count个连接

13.3.3收回权限

       MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、table_priv、表中删除，

       REVOKE语句有两种语法，

              第一种：收回所有用户的所有权限，此语法用于取消对于已命名的用户的所有全局层级、数据库层                        级、表层级和列层级的权限。

                                          语法：

                                                 REVOKE ALL PRIVILEGES GRANT OPTION

                                                 FROM ’user‘@’host‘ [,'user'@'host']

                                                 from语句指明需要收回权限的账户。

              第二种：收回指定权限

                                                 REVOKE priv_type[(columns)] [,priv_type [(columns)]] ...

                                                 ON table1,table2,...tablen

                                                 FROM ’user‘@’host‘ [,'user'@'host']

13.3.4查看权限

              show GRANT FOR 'user'@'host'；

 

 

第14章数据库备份与恢复

1.数据库备份

1.     备份一个数据库（表）

       mysqldump -u username -h host -p password  dbname [tbname,[tbname]] > filename.sql

2.     备份多个库

       mysqldump -u username -h host -p  --database [dbname,[dbname]...] > filename.sql

       使用--database后，必须指定至少一个数据库的名称

3.     数据恢复

                     mysql -u username -p [dbname] < filename.sql

                     执行该语句前，必须在MySQL服务器中创建数据库。如果不存恢复过程报错

2.表的导出和导入

导出文本文件

u  SELECTI ...INTO OUTFILE 'filename'

              格式：SELECTI columnlist FROM table WHEREcondition  INTO OUTFILE 'filename'                           [OPTIONS]

                --OPTIONS 选项

                     FIELDS TERMINATED BY ‘value’

                     FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

                     FIELDS ESCAPED BY ‘value’

                     LINES STARTING BY ‘value’

                     LINES TERMINATED BY ‘value’

      

• FIELDS TERMINATED BY ‘value’:设置字段之间的分隔符，可以为单个或者多个字符，默认情况下为制表符‘\t’
• FIELDS [OPTIONALLY] ENCLOSED BY ‘value’:设置字段的包围字符，
• FIELDS ESCAPED BY ‘value’：设置如何写入和读取特殊字符，只能为单个字符，即设置转义字符，默认是‘\’
• LINES STARTING BY ‘value’:设置每行数据开头的字符，可以为单个或者多个字符默认是不使用任何字符
• LINES TERMINATED BY ‘value’：设置每行数据结尾的字符，可以为单个或者多个字符，默认为‘\n’

              使用MySQLdump命令导出文本文件

       mysqldump -T path -u root -p dbname [tables] [OPTIONS]

       -T:指定参数参数可以导出纯文本，

       path：表示导出数据的目录

      

使用mysql命令导出文本文件

mysql -u root -p --execute = "SELECT 语句" dbname > filename.txt

--execute:表示执行该选项后面的语句并推出

14.4.4 使用LOAD DATA INFILE 方法导入文本文件

       LOAD DATA语句的基本格式

       LOAD DATA INFILE 'filename.txt' INFO TABLE tablename [OPTIONS] [IGNORE number LINES]

       OPTIONS 选项

       FIELDS TERMINATED BY 'value'

       FIELDS [OPTIONALLY] ENCLOSED BY 'value'

                     FIELDS ESCAPED BY 'value'

                     LINES STARTING BY 'value'

                     LINES TERMINATED BY 'value'

14.4.5 使用MySQLimport命令导入文本文件

              语法格式：

              mysqlimport -u root -p dbname filename.txt

      

第15章 MySQl日志

15.1 二进制日志

       二进制日志，主要记录MySQL数据库的变化。包含所有更新了数据或者已经潜在更新了数据的语句(例如：没有匹配任何行的一个DELETE)的语句。语句以事件的形式保存，描述数据更改。

       二进制日志，还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果想要记录所有语句，需要使用一般查询日志。使用二进制日志的主要目的是最大可能地恢复数据库，因为二进制日志备份后进行的所有更新。

     15.2.1 启动和设置二进制文件

       默认情况下二进制日志是关闭的。

       my.ini中[MySQLd]组下面是设置关于二进制日志的：

              log-bin="D:/sql-log/"

              expire_logs_days=10

              max_binlog_size=100M

       lob_bin:开启二进制日志 log-bin的变量值为ON则表示二进制日志已经打开

       expire_logs_days:二进制日志自动删除的天数

       max_binlog_size:定义了单个文件的大小限时，如果文件大小大于设置的值，日志就会关闭，重新打开一个日志文件。

      

       可以通过show variables like '' 来查询日志设置。

     15.2.2 查看二进制日志

                     show binary logs ：查看当前的二进制文件个数及名字

                     通过MySQLbinlog+日志名字的命令查看日志内容例如：mysqlbinlog D:/sql-log/binlog.000001

                    

     15.2.3 删除二进制文件

                     删除所有二进制文件

                      RESET MASTER

                     删除指定日志文件

                            删除文件名编号比指定文件编号小的所有日志

                                   PURGE{MASTER | BINARY} LOGS TO 'log_name'

                            删除指定日期以前的所有日志文件

                                   PURGB{MASTER | BINARY} LOGS BEFORE 'date'

     15.2.4 使用二进制日志恢复数据库

                     使用MySQLbinlog工具从指定的时间开始直到现在，或从另一个指定的时间点的日志中恢复数据

                     需要知道二进制日志文件的路径和文件名，一般在my.cnf或者my.ini中可以知道

                     语法：mysqlbinlog [option] filename | mysql -urser -ppass

                     option:--start-date、--stop-date和--start-option、--stop-position，其中--start-date、--stop-date可以指定恢复数据库的起始时间和结束时间--start-option、--stop-position指定恢复数据的开始位置和结束位置。

                     例如：mysqlbinlog --stop-date="2016-01-30 15:27:48" D:\mysql\binlog.000008 | mysql -uroot -proot

     15.2.5 暂停二进制日志功能

                     set sql_log_bin =0;暂停记录二进制日志

                     set sql_log_bin =1;恢复记录二进制日志

     15.3错误日志

       错误日志文件包含了当 MySQLd启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。在MySQL中，错误日志也是非常有用的，MySQL会将启动和停止数据库信息以及一些错误信息记录到错误日志

     15.3.1 启动和设置错误日志

       默认情况下，错误日志会被记录到数据库的数据目录下，如果没有在配置文件中指定文件名，则文件名默认为hostname.err。如果重新执行 flush logs 错误日志文件重新加载

       启动、停止、以及指定日志文件名称。都可以在my.ini（或者 my.cnf）来配置。

       配置：在[MySQLd]下配置

       [MySQLd]

       log-error=[path/[filename]]

       修改配置后需要重启

     15.3.2 查看错误日志

       MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看MySQL错误日志

       如果不知道错误日志路径可以使用 show variables like 'log-error';查看错误日志的路径

     15.3.3 删除错误日志

       MySQL5.5.7 之前使用flush logs 可以将错误日志文件重命名filename.err_old，并创建新的日志文件

       但是在5.5.7之后flush logs 只是重新打开日志文件，并不会日志备份和创建的操作。

       如果需要创建则服务器端 mysqladmin -u root -p flush-logs

       或者在客户登录端 flush logs

      15.4 通用查询日志

       通用查询日志记录MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句

     15.4.1启动和设置通用查询日志

       默认情况下没有开启通用查询日志，在my.ini（或者my.cnf）来配置。

       在[MySQLd]组件下

       log[=path/[filename]]

       如果不指定路径和文件名查询日志将默认在MySQL数据目录中的hostname.log

     15.4.2查看通用查询日志

       Windows 下可以使用记事本，Linux下可以使用vim 和gedit

      15.5 慢查询日志

       满查询日志记录查询时长超过指定时间的日志。慢查询日志主要用来记录执行时间较长的查询语句，通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化

     15.5.1 启动和设置慢查询日志

       在[mysqld]

       log-slow-queries=[=path/[filename]]

       long_query_time=n

      

       long_query_time:指定记录阈值，如果某条查询语句的查询时间超过这个值，这个查询过程将被记录到慢查询日志中。

       如果没有设置long_query_time选项，默认时间是10秒。

                           

 

 

第16章性能优化

       MySQL优化一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调账，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷非服务

       MySQL数据库优化是多方的，原则上是：减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘I\O的读写速度，通过优化操作系统调度策略提高MySQL在高负荷情况下的索引能力。优化表结构，索引、查询语句等使查询响应更快

 

 16.2 优化查询

16.2.1 分析查询语句

  explain [extended] select select_options

 expalian查询结果字段解释

   id：select标识符，select的查询序列号

   select_type:表示select语句的类型。取值种类

ü  SIMPLE：简单查询，不包括子查询和连接查询

ü  PRIMARY：主查询，或者是最外层的查询

ü  UNION：连接查询的第2个，或者后面的查询语句

ü  DEPENDENT UNION：连接查询中的第2个或者后面的SELECE语句，取决于外面的查询

ü  UNION RESULT：连接查询的结果

ü  SUBQUERY：子查询中的第一个SELECT语句

ü  DEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询

ü  DERIVED：导出表的SELECT

   table：查询的表

   type：连接的类，下面是从最佳到最差

   system：该表是仅有一行的系统表，这是const的特例

ü  const：数据表最多只有一个匹配行，它将在查询开始被读取，并在余下的查询优化中作为常量对待。const表的查询速度很快，因为它们读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE 索引的所有部分的场合。

ü  eq_ref:对于每个来自前面表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UniQUE或PRIMARY KEY时，

ü  ref：

ü  ref_of_null

ü  index_merge

ü  unique_subquery

ü  index_subquery

ü  range

ü  index

ü  all

ü  possible_keys:指出MySQL能使用哪个索引在该表中找到行。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看它是否引用某些列或者适合索引的列来提高查询速度，如果是这样，可以创建适合的索引来提高性能

   key：查询实际使用到的索引，

   key_len:表示选择的索引字段按字节计算的长度

   ref:表示使用哪个列或常数与索引一起来查询记录

   rows：显示MySQL在表中进行查询时必须检查的行数

   Extra：表示MySQL在处理查询时的详细信息

      

       DESCRIBE SELECT select_options和EXPLAIN用法一样

16.2.3 使用索引查询

       使用索引有几种特殊的情况，在这种情况下，有可能使用带有索引的字段查询时，索引并没有起作用。

       1.使用LIKE关键字的查询语句

       使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%",索引不会起作用

       2.使用多列索引的查询语句

              MySQL可以创建多字段的索引。一个索引可以包括16个字段，对于多列索引，只有查询条件中使   用这些字段中第一个字段时，索引才会被使用。

       3.适用OR关键字的查询语句

       查询鱼护的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则使用不到索引

16.2.4 优化子查询

       子查询虽然可以使查询语句灵活，但是执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，因此，子查询的速度会受到一定的影响，

       连接查询不需要建立临时表，其速度比子查询更快，如果查询中使用索引的话，性能会更好

      16.3 优化数据库结构

     16.3.1.将字段很多的表分解成多个表

              对于字段多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新的表

     16.3.2增加中间表

       对于需要经常联合查询的表，可以建立中间表以提高查询效率，通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询

     16.3.3 增加冗余字段

     16.3.4优化插入记录的速度

              插入数据时，影响插入速度的主要是索引、唯一性效验、一次插入记录条数等

              1.禁用索引

                     对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会奖励插入记录的速度。我们可以在插入记录之前急用索引，

                     数据插入完毕之后开启索引

                     禁用索引的语法：

                            ALTER TABLE table_name DISABLE KEYS

                     重新开启索引的语法

                            ALTER TABLE table_name ENABLE KEYS;

                     对于空表鼻梁导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后建立索引

                     2.禁用唯一性检查

                     插入数据时，MySQL会对插入的记录进行唯一性效验。这种唯一性校验也会降低插入记录的速度。

                     禁用语法：SET UNIQUE_CHECKS=0

                     开启语法：SET UNIQUE_CHECKS=1;

                     4.使用LOAD DATE INFILE 批量导入

                            对于InnoDB引擎的表：

                                   1.禁止唯一性检查

                                   2.禁止外键检查

                                          SET foreign_key_checks=0;

                                          SET foreign_key_checks=1;

                                   3.禁止自动提交

                                          SET autocommit =0;

                                          set autocommit=1;

     16.3.5 分析表、检查表和优化表

              分析表主要是分析关键字的分布

              查检表主要是检查表是否存在错误

              优化表主要是消除删除或者更新造成的空间浪费

              1.分析表

              ANALYZE [local|No_WRITE_TO_BINLOG] TABLE tab_name[,tab_name] ...

              local:是关键字，No_WRITE_TO_BINLOG：关键字别名，二者都是执行过程中不写入二进制日志ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。不能插入数据。

              ANALYZE TABLE语句能够分析InnoDB、DBD和MyISAM类型的表

             

              2.检查表

              MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误，对于MyISAM类型的表，CHECK TABLE语句还有更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误。

              语法：

                     CHECK TABLE tbl_name [,tab_name] ....[option]...

                     option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

ü  QUICK:不扫描行，不检查错误的连接

ü  FAST：只检查没有被正确关闭的表

ü  CHANGED：只检查上次检查后被更改的表和没有别正确关闭的表

ü  MEDIUM：扫描行，已验证被删除的连接是有效的。也可以计算各行的关键字效验和，并使用计算出的校验和验证这一点

ü  EXTENDED:对每行的所有关键字践行一个全面的关键字查找，这可以确保表示100%一致的。耗时长

                     3.优化表

              OPTIMIZE TABLE语句来优化表，但是只能优化表中VARCHAR、BLOB或者TEXT类型的字段

                     语法：

                            OPTIMIZES [LOCAL|No_WRITE_TO_BINLOG] TABLE tab_name [,tab_name] ...

16.4 优化MySQL服务器

                     一方面是对硬件的优化，另一方面是对MySQL服务器参数进行优化。

               16.4.1优化服务器硬件

a)       配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的速度比磁盘I/O快的多。通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘I\O

b)       配置高速磁盘系统，以减少读盘的等待时间，提高响应速度

c)       合理分布磁盘I/O，把磁盘分散在多个设备上，以减少资源竞争，提高并行操作能力

d)       配置多处理器，MySQL是多线程的数据库。多处理器可同时执行多个线程

                           

                    16.4.2 优化参数

n  key_buffer_size:表示索引缓冲区的大小，索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好处理的索引

n  table_cache:表示同时打开表的个数

n  query_cache_size:表示查询缓冲区的大小，该参数需要query_cache_type配合使用当query_cache_type 值是0时，所有的查询都不使用查询缓冲区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓冲区内存。当query_cache_type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定 SOL_NO_CACHE，如

n  SELECT NO_SQL_CACHE * from table.当query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓冲区，使用查询缓冲区可以提高查询速度，这种方式只适应于修改操作少且经常执行相同的查询操作的情况

n  sort_buffer_size：表示排序缓冲区的大小，这个值越大，进行拍新的速度越快

n  read_buffer_size：表示线程连续扫描时为扫描每个表分配的缓冲区大小

n  read_rnd_buffer_size：表示为每个线程保留的缓冲区大小。主要用于存储安特定存出顺序读取出来的记录

n  innodb_buffer_pool_size：表示innodb类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。

n  max_connections：数据库最大连接数

n  InnoDB_flush_log_at_trx_commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘，该参数有3个值：0、1、2。0：表示每隔一秒将数据写入日志文件并将日志文件写入磁盘。值为1时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘。值为2时，表示每次提交事务时将数据写入日志文件，每个1秒将日志文件写入磁盘，默认值为1

n  back_log:表示对到来的TCP/IP连接的侦听队列的大小

n  interactive_timeout:表示服务器在关闭连接前等待的秒数

n  thread_cache_size：表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。

 

n  sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或者GROUP BY 操作的速度，默认值为2M

n  with_timeout:表示服务器在关闭一个连接时等待行动的秒数，默认是28800

---

 [l1]一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(multi versionning)的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行DELETE或UPDATE操作，这是读取操作不会因此等待行上锁的释放。相反的，InnoDB会去读取行的一个快照数据

快照数据是指该行之前版本的数据，该实现是通过undo段来完成。而undo用来事务中的回滚数据，因此快照数据本身没有额外的开销，此外，读取快照数据不需要上锁，因为没有事务需要对历史数据进行修改操作

可以看到，非锁定读机制极大地提高了数据库的并发性，在InnoDB存储引擎的默认设置下，这是默认的读写方式，即读不会占用和等待表上的锁

 

 [l2]

 [l3]2，分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

 

分库分表区别：

1 基本思想之什么是分库分表？

从字面上简单理解，就是把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。

 

2 基本思想之为什么要分库分表？

     数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；另外，一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈,。

 

3 分库分表的实施策略。

     如果你的单机性能很低了，那可以尝试分库。分库，业务透明，在物理实现上分成多个服务器，不同的分库在不同服务器上。分区可以把表分到不同的硬盘上，但不能分配到不同服务器上。一台机器的性能是有限制的，用分库可以解决单台服务器性能不够，或者成本过高问题。

当分区之后，表还是很大，处理不过来，这时候可以用分库。

orderid,userid,ordertime,.....

userid%4=0，用分库1

userid%4=1，用分库2

userid%4=2, 用分库3

userid%4=3，用分库4

上面这个就是一个简单的分库路由，根据userid选择分库，即不同的服务器

 

分库分表有垂直切分和水平切分两种。

     3.1 何谓垂直切分，即将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数据库workDB、商品数据库payDB、用户数据库userDB、日志数据库logDB等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等。

   

如userid,name,addr一个表，为了防止表过大，分成2个表。

userid,name

userid,addr

 

       3.2 何谓水平切分，当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如userID散列、按性别、按省，进行划分，然后存储到多个结构相同的表，和不同的库上。例如，我们的userDB中的用户数据表中，每一个表的数据量都很大，就可以把userDB切分为结构相同的多个userDB：part0DB、part1DB等，再将userDB上的用户数据表userTable，切分为很多userTable：userTable0、userTable1等，然后将这些表按照一定的规则存储到多个userDB上。

 

      3.3 应该使用哪一种方式来实施数据库分库分表，这要看数据库中数据量的瓶颈所在，并综合项目的业务类型进行考虑。

如果数据库是因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、低耦合，那么规则简单明了、容易实施的垂直切分必是首选。

而如果数据库中的表并不多，但单表的数据量很大、或数据热度很高，这种情况之下就应该选择水平切分，水平切分比垂直切分要复杂一些，它将原本逻辑上属于一体的数据进行了物理分割，除了在分割时要对分割的粒度做好评估，考虑数据平均和负载平均，后期也将对项目人员及应用程序产生额外的数据管理负担。

在现实项目中，往往是这两种情况兼而有之，这就需要做出权衡，甚至既需要垂直切分，又需要水平切分。我们的游戏项目便综合使用了垂直与水平切分，我们首先对数据库进行垂直切分，然后，再针对一部分表，通常是用户数据表，进行水平切分。

 

4 分库分表存在的问题。

 

4.1 事务问题。

       在执行分库分表之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

 

4.2 跨库跨表的join问题。

    在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

 

4.3 额外的数据管理负担和数据运算压力。

额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算，例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个or

der by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。

 

 [l4]分表和分区的区别：

一，什么是mysql分表，分区

 

什么是分表，从表面意思上看呢，就是把一张表分成N多个小表，具体请看:mysql分表的3种方法

 

什么是分区，分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，具体请参考mysql分区功能详细介绍，以及实例

 

二，mysql分表和分区有什么区别呢

 

1，实现方式上

 

a），mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。

 

Sql代码

 

[root@BlackGhost test]# ls |grep user

alluser.MRG

alluser.frm

user1.MYD

user1.MYI

user1.frm

user2.MYD

user2.MYI

user2.frm  

 

 

简单说明一下，上面的分表呢是利用了merge存储引擎（分表的一种），alluser是总表，下面有二个分表，user1，user2。他们二个都是独立的表，取数据的时候，我们可以通过总表来取。这里总表是没有.MYD,.MYI这二个文件的，也就是说，总表他不是一张表，没有数据，数据都放在分表里面。我们来看看.MRG到底是什么东西

 

Sql代码

[root@BlackGhost test]# cat alluser.MRG |more

user1

user2

#INSERT_METHOD=LAST

 

从上面我们可以看出，alluser.MRG里面就存了一些分表的关系，以及插入数据的方式。可以把总表理解成一个外壳，或者是联接池。

 

b），分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了。

 

 

Sql代码

[root@BlackGhost test]# ls |grep aa

aa#P#p1.MYD

aa#P#p1.MYI

aa#P#p3.MYD

aa#P#p3.MYI

aa.frm

aa.par

 

 

从上面我们可以看出，aa这张表，分为二个区，p1和p3，本来是三个区，被我删了一个区。我们都知道一张表对应三个文件.MYD,.MYI,.frm。分区呢根据一定的规则把数据文件和索引文件进行了分割，还多出了一个.par文件，打开.par文件后你可以看出他记录了，这张表的分区信息，根分表中的.MRG有点像。分区后，还是一张，而不是多张表。

如orderid,userid,ordertime,.....

ordertime<2015-01-01 #p0

ordertime<2015-04-01 #p1

ordertime<2015-07-01 #p2

ordertime<2015-10-01 #p3

ordertime<2016-01-01 #p4

按照时间分区。大部分只查询最近的订单数据，那么大部分只访问一个分区，比整个表小多了，数据库可以更加好的缓存，性能也提高了。这个是数据库分的，应用程序透明，无需修改。

 

2，数据处理上

 

a），分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。看下面的例子：

 

select * from alluser where id='12'表面上看，是对表alluser进行操作的，其实不是的。是对alluser里面的分表进行了操作。

 

b），分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表。数据处理还是由自己来完成。

 

3，提高性能上

 

a），分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢，本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了。

 

b），mysql提出了分区的概念，我觉得就想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。

在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

 

4），实现的难易度上

 

a），分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。

 

b），分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

 

三，mysql分表和分区有什么联系呢

 

1，都能提高mysql的性高，在高并发状态下都有一个良好的表面

 

 [l5]存储过程和函数：类似于java中的方法

 

 [l6]CREATE TRIGGER ins_sum BEFOER INSERT ON account FOR EACH ROW SET @sum =@sum +NEW.amount;

 

SET @sum=0;

 

SELECT @sum;

SHOW ENGINES;

转载于:https://www.cnblogs.com/jdy1022/p/11268185.html