12 Mysql之工作中常用操作
Mysql 专题讲解
一、用户创建与权限管理
a) 创建和删除用户
- 创建用户：
1 CREATE USER jack@localhost; 2 3 UPDATE USER SET password=password(‘123456’) WHERE USER = ‘jack’; 4 5 或 6 7 CREATE USER jack@localhost IDENTIFIED BY ‘123456’; 8 9 刷新内存中的权限 10 11 FLUSH PRIVILEGES;
2. 删除用户：
1 Drop USER jack@localhost;
3. 修改密码：
1 SET PASSWORD FOR jack@localhost = password(‘654321’);
b) 用户权限管理
- 授权：
1 GRANT SELECT, INSERT, UPDATE on test.* to jack@localhost;
2. 收回：
1 REVOKE ALL PRIVILEGES, GRANT OPRTION FROM jack;
c) 忘记root密码
1.
1 --skip-grant-tables 跳过密码验证 2 ~%mysqld --skip_grant_tables
新窗口中直接登陆
2. 修改phpMyadmin中的账号配置
1 进入phpMyadmin目录index.php文件 搜索mysql_connect() 查找到配置变量$cfg 2 进入存储该变量文件 '~/phpmyadmin/'.'config.inc.php'中 搜索’password’配置项 修改其值；
二、数据准备
a) 新建user（用户表）、post（日志表）、category（分类）三张数据表
1 CREATE TABLE `user` ( 2 3 `uid` int(11) NOT NULL AUTO_INCREMENT, 4 5 `uname` varchar(200) CHARACTER SET utf8 DEFAULT NULL, 6 7 `password` char(32) DEFAULT NULL, 8 9 `createtime` datetime DEFAULT NULL, 10 11 PRIMARY KEY (`uid`) 12 13 ) ENGINE=innodb DEFAULT CHARSET=utf8; 14 15 16 17 CREATE TABLE post ( 18 19 `pid` INT(11) NOT NULL AUTO_INCREMENT, 20 21 `title` VARCHAR(200) CHARACTER SET UTF8 DEFAULT NULL, 22 23 `content` TEXT CHARACTER SET UTF8, 24 25 `uid` INT(11) NOT NULL, 26 27 `cid` INT(11) NOT NULL, 28 29 `createtime` timestamp default current_timestamp() not null, 30 31 PRIMARY KEY(`pid`) 32 33 ) ENGINE=MYisam DEFAULT CHARSET=utf8; 34 35 36 37 CREATE TABLE category ( 38 39 Cid INT(11) NOT NULL AUTO_INCREMENT, 40 41 Cname VARCHAR(20） NOT NULL, 42 43 Priamry key (cid) 44 45 ）ENGINE = innodb default charset=utf8; 46 47 48 49 b) 创建大数据-存储引擎PROCEDURE 50 51 DELIMITER // 52 53 CREATE PROCEDURE loop_insert_post() 54 55 BEGIN 56 57 DECLARE i INT; 58 59 SET i = 1; 60 61 WHILE i <= 10000000 DO 62 63 INSERT INTO post SET title = CONCAT(‘lamp’,CEIL(RAND()*10), uid= CEIL(RAND()*10), cid = CEIL(RAND()*10), content = MD5(TRUNCATE(RAND(),2)); 64 65 SET i = i + 1； 66 67 END WHILE; 68 69 END// 70 71 DELIMITER;
三、数据的迁徙
1 a) 导入数据 2 3 LOAD DATA INFILE 4 /!*插入数据 使用本地文本文件*/ 5 6 LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE ‘FILE_NAME’ 7 8 /!*进到TABLE_NAME表中*/ 9 10 INTO TABLE TABLE_NAME [CHARACTER SET CHARSET_NAME] 11 12 /!*字段分隔符使用 FIELD_SEPERATOR*/ 13 14 FIELDS TERMINATED BY ‘\t’ [ENCLOSED BY ‘ENCLOSED_STRING’] [ESCAPED BY ‘ESCAPED_STRING’] 15 16 /!*行间分割使用 LINE_SEPERATOR*/ 17 18 LINES TERMINATED BY ‘\n’ 19 20 *LOCAL表示从本地客户端读取文件 本地文件的内容首先被发送至服务器 然后执行语句 所以相对较慢；如果没有指定则从服务器读取文件 当在服务器主机上为文件定位时，服务器使用以下规则： 21 22 如果给定一个绝对路径 则服务器使用此绝对路径 23 24 如果给定一个带引导组件的相对路径 服务器会搜索相对于服务器【数据目录】的文件 25 26 如果给定一个不带引导组件的文件名称 服务器会在默认数据库的数据库目录中寻找文件 27 28 现要将my.txt内容导入TEST数据库中表 如果使用./my.txt则会在服务器目录中定位文件 如使用my.txt则会在TEST数据库目录中定位文件 29 30 ii. Mysqlimport 31 32 Mysql LOAD DATA INFILE的命令行版本 33 34 35 36 b) 导出数据 37 38 SELECT INTO OUTFILE 39 将所有数据导入FILE_NAME文件 从TABLENAME 表 40 41 Select * INTO OUTFILE ‘./data.txt’ FROM TABLE_NAME; 42 43 ii. Mysqldump工具 44 45 ~bin/mysqldump命令行 可以将整个数据库装在到一个文本文件中 46 47 ~bin/mysqldump -h host -u user -p password dbname [-d|-t|] [tbname] > filename 48 49 /!*将dbname数据库中所有信息载入test.db.sql文件中*/ 50 51 ~bin/mysqldump -hlocalhost -uroot -p123456 test > test.db.sql; 52 53 /!*导出dbname.tbname数据表中的所有信息*/ 54 55 ~bin/mysqldump -hlocalhost -uroot -p123456 test.message > test.message.sql; 56 57 /!*导出dbname中所有的表结构（不含表数据）*/ 58 59 ~bin/mysqldump -hlocalhost -uroot -p123456 test -t > test.tbs.sql; 60 61 /!*导出dbanme中所有表数据 （不含表结构）*/ 62 63 ~bin/mysqldump -hlocalhost -uroot -p123456 test -d > test.data.sql;
四、SQL语句扩展和内置函数
a) 内置函数
- 字符串操作函数
1 concat(s1,s2,…Sn) 连接s1,s2..Sn为一个字符串 2 3 CONCAT_WS(sepa, s1, s2) 用sepa 连接 s1 s2 4 5 insert(str,x,y,instr) 将字符串str从第xx位置开始，y字符串的子字符串替换为字符串str 6 7 lower(str) 将所有的字符串变为小写 8 9 upper(str) 将所有的字符串变为大写 10 11 left(str,x) 返回字符串中最左边的x个字符 12 13 rigth(str,y) 返回字符串中最右边的x个字符 14 15 lpad(str,n,pad) 用字符串pad对str最左边进行填充，直到长度为n个字符串长度 16 17 rpad(str,n,pad) 用字符串pad对str最右边进行填充，直到长度为n个字符串长度 18 19 trim(str) 去掉左右两边的空格 20 21 ltrim(str) 去掉字符串str左侧的空格 22 23 rtrim(str) 去掉字符串str右侧的空格 24 25 repeat(str,x) 返回字符串str重复x次 26 27 replace(str,a,b) 将字符串的的a替换成b 28 29 strcmp(s1,s2) 比较字符串s1和s2 30 31 substring(s,x,y) 返回字符串指定的长度 32 33 length(str) 返回值为字符串str 的长度
2. 日期时间函数：
1 获取日期格式的时间 NOW()、CURRENT_DATE()、CURRENT_TIME()、CURRENT_TIMESTAMP()、YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND() 2 日期时间格式和UNIX时间戳互换：FROM_UNIXTIME()、UNIX_TIMESTAMP() 3 计算日期差值函数： TO_DAYS()、DATEDIFF()、SUBDATE()、ADDDATE() 4 数值函数
abs(x) 返回x的绝对值
ceil(x) 返回大于x的最小整数值
floor(x) 返回小于x的最大整数值
mod(x,y) 返回x/y的取余结果
rand() 返回0~1之间的随机数
round(x,y)返回参数x的四舍五入的有y位小数的值
truncate(x,y) 返回x截断为y位小数的结果
- 其他常用函数
1 database() 返回当前数据库名 2 3 version() 返回当前服务器版本 4 5 user() 返回当前登陆用户名 6 7 inet_aton 返回当前IP地址的数字表示 inet_aton("192.168.80.250"); 8 9 inet_ntoa(num) 返回当前数字表示的ip inet_ntoa(3232256250); 10 11 password(str) 返回当前str的加密版本 12 13 md5(str) 返回字符串str的md5值 14 15
b) 索引创建和使用
1 1. 一个未建立任何索引的的表只是未经排序的数据的集合，查找数据需要全表扫描，查询效率低、速度慢，尤其是在执行联表查询时; 2 2. 索引是经过排序的键->值数组，通过键值对应关系快速定位数据行，它的本质是表字段的有序子集，每个记录项都指向相应的表记录；
数据库开发中引入索引可对数据库进行有效的（搜索）查询优化。
c) Myisam引擎中的索引单独文件存在，innodb的索引和数据行存放在一起
1 Myisam存储引擎 db.frm(framework)存放表格式 db.myd(mydata)存放表数据 db.myi(myindex)存放表索引 2 ii. Innodb存储引擎 db.frm(framework)包含表格式、数据、索引
d) 索引类型
- 主键索引
1 根据主键自身的唯一性来【标记】每条记录 可视为用户的操作记录 ；一张表中只能有一个主键索引
2. 唯一索引
1 对于值不能重复的字段创建索引可有效避免插入数据时的重复，协助脚本完成验证；一张表中可以可以设置多个唯一索引
3. 常规索引
1 对非主键、非唯一字段设置索引 可以实现搜索或者查询的有效优化；
4. 全文索引
1 全文索引提供了一种快速获取数据的机制、能生成最符合用户需求的结果，它对搜索的字符串进行有效分割和提取，默认忽略4个字符以下的词组；
它存在于myisam 引擎中并且只对char、varchar、text类型字段有效
e) 索引的运行过程
假设tb1、tb2、tb3各有1000条记录 执行如下语句： sql = “select tb1.c1,tb2.c2,tb3.c3 from tb1,tb2,tb3 where tb1.c1 = tb2.c2 and tb1.c1 = tb3.c3”; 未使用索引的情况下：首先从tb1中提取一条记录 然后依次全盘扫描tb2、tb3中的记录 tb1中每条记录需要扫描1000*1000次，tb1执行完毕共需扫描1000*1000*1000=10亿次； 如果c1、c2、c3字段均被设为索引则当从tb1中提取一条记录后 利用tb2中索引直接定位数据行、tb3中的索引定位数据行 无需全表扫表 ；tb1执行完只需对tb1全表扫描一次，执行1000次；
f) 科学合理的设计索引
索引极大的方便了用户对数据的搜索，但同时任何事物都有两面性
1 索引需要更多的磁盘空间，当数据量巨大或者数据表增多时 这不容小觑。 2 每次的增删改操作，索引都需进行更新，给操作增加了额外的负担；因此索引越多 相应增删的执行越慢 3 由于索引对查询的优化 对有需求的字段设置索引才有意义，即Where 子句和 order by 子句中的字段 4 使用组合索引时Index(firstName,lastName) 无需再为firstName 创建索引 因为mysql能搜索索引前缀 5 对于准备创建索引的列要设为not null，这样保证null不会存在 6 Explain用以查看查询过程 表如何连接 按什么顺序连接，协助进行优化设计；
尽量对少重复值的字段设置索引，最好是唯一性的字段，这样查询效果会非常好，对于数据重复超过30%的字段 索引帮不上多大忙，如性别M or F，通常还是使用全表扫描
1 索引长度要尽量短 2 充分利用左侧前缀
g) 索引操作之Alter 语句
- 修改索引
1 ALTER TABLE user ADD INDEX idx_uname(uname);/CREATE INDEX index_email ON user(email(10)); 2 3 ALTER TABLE user DROP INDEX idx_uname;/DROP INDEX IDX_NAME ON user;
2. 修改列信息
1 ALTER TABLE user ADD COLUMN nickname VARCHAR(200) NULL; 2 3 ALTER TABLE user CHANGE nickname CHAR(100) NOT NULL; 4 5 ALTER TABLE user DROP COLUMN nickname;
3.添加外键和约束（innodb支持）
1 ALTER TABLE post ADD CONSTRAINT fk_user FORIGN KEY(uid) 2 3 REFERENCES BY user(uid) ON UPDATE CASCADE ON DELETE CASCADE;
4. 在进行大数据插入时 停止更新表中索引会非常有用（因为索引的更新会非常慢 非常慢 非常慢 重要事说三遍....）
1 ALTER TABLE user DISABLED KEYS;
添加完毕后重新更新索引 PS：一般执行alter操作除了rename 外都会创建临时表 更新索引在所有操作中尤为的缓慢
1 ALTER TABLE user ENABLED KEYS;
h) Create语句
- 创建数据库
1 ~bin/mysqladmin create LAMP119 -hlocalhost -uroot -p 2 3 ~bin/mysqladmin drop LAMP119 -hlocalhost -uroot -p //删除数据表 4 5 Do you really want to drop LAMP119[y/n]? Y //确定删除
2. 创建数据表
1 CREATE TABLE `user` ( 2 `uid` int(11) NOT NULL AUTO_INCREMENT, 3 4 `uname` varchar(200) CHARACTER SET utf8 DEFAULT NULL, 5 6 `password` char(32) DEFAULT NULL, 7 8 `createtime` datetime DEFAULT NULL, 9 10 PRIMARY KEY (`uid`) 11 12 ) ENGINE=InnoDB AUTO_INCREMENT=112963538 DEFAULT CHARSET=utf8 DATA DIRECTEORY= INDEX DIRECTORY=
当磁盘空间有限时 可以设置DATA DIRECTORY 和 INDEX DIRECTORY选项 指定数据文件和索引文件存放位置
Windows系统会忽略data directory和index directory选项
数据库特性 如指定数据库的默认字符集和默认校对字符集 存储于数据库目录中db.opt文件，打开 ~mysql/data/db.opt
default-character-set=utf8
default-collation=utf8_bin
- DEFAULT用于为列指定一个默认值，这个值必须是常数，不能使用函数或者表达式;
除了TIMESTAMP类型字段可以设置CURRENT_TIMESTAMP()以外；
BLOB和TEXT列不能被赋予默认值；
1 使用ALTER修改AUTO_INCREMENT的值 Alter TABLE TBNAME AUTO_INCREMENT = N 2 MAX_ROWS和MIN_ROWS表分别表示表存储的最大和最小行数 3 创建表索引
1 CREATE [UNIQUE|FULLTEXT|INDEX] index_name on user(nickname) 2 3 CHAR和 VARCHAR可以只用列的一部分来创建索引，创建时使用column_name(length)的语法对指定长度前缀进行索引，这样可以减少磁盘文件的大小，节省磁盘空间，并能提高INSERT和DELETE的索引速度. 4 5 CREATE INDEX index_email ON user(email(10)); 6 7 SHOW INDEX FROM user; //查看表中索引
i) Do语句
1 Do 是select语句的简化版 不返回结果 但执行效率更快 2 3 /*用字符串str得到一个锁 超时为10S*/ 4 5 Do GET_LOCK(‘str’, 10); 6 7 /*解除该锁*/ 8 9 Do RELEASE_LOCK(‘str’);
j) Handler语句
1 它能直接建立与表之间的通道 并且不被其他线程共享 是低级别语言 但执行效率比select快 2 3 /*打开一个表*/ 4 5 HANDLER TBNAME OPEN； 6 7 /*建立读取通道 该通道不会被其他线程共享 在未设置limit的情况下 默认只读取一行【读取的列必须是索引 使用索引名】*/ 8 9 HANDLER TBNAME READ index_column_name > () ； 10 11 /*关闭打开表 */ 12 13 HANDLER TBNAME CLOSE；
k) Select 查询
1 1. SELECT [DISTINCT|ALL] [HIGH_PRIORITY] [FIELD EXP] [FROM TABLE EXP] [WHERE EXP] 2 3 [GROUP BY EXP] [HANVING EXP] [ORDER BY EXP] [LIMIT EXP]
1 2. SELECT 支持所有MYSQL运算 如 SELECT 1+1; 可以给变量赋值 SELECT MAX(uid) INTO @maxid; SELECT @maxid; 2 ii. HIGH_PRIORUTY 表明查询优先权高于【更新】语句 3 4 3. FROM 用于将多个表联接 用，隔开
14.
WHERE 设置搜索条件 不能在条件语句中使用聚合函数 可以使用子查询替代 2 3 SELECT * FROM user WHERE uid = (SELECT MAX(uid) FROM user); 4 5 多个条件可以使用 AND OR IN LIKE BETWEEN...AND... 联接 6 7 Select * FROM post where uid IN (select substr(uid,1,1) from user where uid < ); 8 9 Select * FROM post where uid = ANY(select substr(uid,1,1) from user where uid < ); 10 11 Select * FROM post where uid = SOME(select substr(uid,1,1) from user where uid < );
1 5.GROUP BY 划分群组 可以在SELECT 中使用聚合函数 统计分组的结果
1 6. HAVING 为群组指定条件 HAVING表达式中可以包含聚合函数
l) Delete语句
1 Delete low_priority|quick|ignore 2 Low_priority(低优先级) 等待其他客户端读取完毕时 再执行删除 3 Quick(快速) 4 对于MYISAM表 删除表数据时存储引擎不会合并索引端节点 可以加快删除 5 6 建议执行时加上OPTIMIZE TABLE;这样同时可以恢复索引的建立 7 8 Ignore(忽略、驳回) 忽略删除中的错误 忽略掉的错误会以警告的形式返回
2. Delete多表操作
1 删除tb1,tb2中满足条件的记录 2 3 Delete tb1,tb2 from tb1,tb2,tb3 where tb1.id = tb2.id and tb2.id=tb3.id 或 4 5 Delete from tb1,tb2 using tb1,tb2,tb3 where tb1.id=tb2.id and tb2.id=tb3.id
m) Update语句
1 Update [low_priority][ignore] tbname set column=..... 2 3 Update 支持多条语句运算 自左向右运行Update goods set price = price+1, price = price*2;
2. Update 支持多表运算 如 update items,month set items.price = month.price where items.id=month.id
1 其中需要表进行关联 2 3 SELECT MAX(uid) FROM user INTO @uid; 4 5 SELECT @uid; 6 7 UPDATE user SET uname = ‘lampp’ WHERE uid = @uid;
n) REPLACE语句
1 Replace的运行和insert很像，当插入的值与表中的主键或者唯一索引相重复时，表中的旧记录在新纪录前会被删除。 2 3 REPLACE INTO TBNAME () VALUES ();
1 Insert语句 2 INSERT LOW_PRIORITY|DELAYED IGNORE INTO TBNAME SET FIELD=VALUE.... //单条插入 3 4 INSERT LOW_PRIORITY|DELAYED IGNORE INTO TBNAME () VALUES () //支持多条
LOW_PRIORITY|DELAYED
- low_priority(低优先级)
a) 当前如果有读取操作 系统【必须等待】并且稍后再插入 当不断增加的读取者到达 插队到写入前 会发生插入阻塞
- Delayed(延迟)
a) 插入的数据将被缓存 当服务器繁忙时 可以进行查询 不必等待insert操作完成
b) 当delayed插入操作到达时 服务器会把数据行放入一个队列中 并立即给客户端反馈一个状态消息 让客户【继续】其他操作 当读取者从数据表中读取数据时 队列中的数据被保留着 直到没有读者为止 接着服务器才开始真正插入延迟队列中的数据 在执行过程中还会反复检查是否有新的读取到达 如果有则延迟写入将被挂起以允许读取者继续操作 直到没有读取者再次进行延迟插入 这个过程反复执行 直到延迟队列为空
c) Low_priority会强制让用户等待 直到可以执行插入 而延迟操作则允许客户端继续操作 服务器会在内存中缓冲数据行 直到有时间处理他们。
ii. IGNORE
追加 IGNORE关键字则会自动忽略错误，如当给primary或者unique插入重复的值时会将错误当做警告来处理。可选的处理方式还可以是执行 on duplicate key update expression
Insert into user set uid=1,password=md5(‘12345’) on duplicate key update password=12345;
五、Mysql 优先级调度策略(处理并发)
Mysql允许改变语句调度的优先级，使来自多个客户端的查询更好的协作，这样单个客户端就不会由于锁定而常时等待，改变优先级可以使得特定类型的查询处理的更快；
Myisam和Memory存储引擎借助数据表锁来完成这样的调度策略，当客户端访问某张表时首先必须获取相应操作的锁，当客户端完成操作时，锁就会被解除；
通过lock tables和unlock tables 来显示获取和释放它的锁是可行的，通常情况下服务器的锁管理器会在需要的时候自动获取锁，获取锁的类型依赖客户端是写入还是读取操作；
a) mysql默认的调度的优先级：（出现并发的处理）
读写同时发生时写入操作优于读取操作
对某张表进行写入操作时不仅数据而且索引需要进行更新，这种变化过程中如果允许其他客户端查询则会呈现不一致性，换而言之 未更新的索引对应出的是旧的数据 并非客户需要的查询结果，因此用户写入时必须拥有独占（排他）的权限锁；
写入操作不可同时进行 只能按请求队列依次等待；对某张表的读取操作可以同时进行（并发）
同时允许写入操作也是不利的，同时更新索引会导致表的破损，极有可能使表变成一堆无用的垃圾；读取时对表的结构和数据并无任何影响，因此可以进行并发；
【场景】：现有A用户在查询时，B用户进来执行插入 由于查询不能中断 则B用户会等待A查询完毕 等待过程中C也进入查询 由于插入的高优先级和锁表机制 那么C则会等待B执行完毕 B等待期间如有不间断的查询进入则会发生【查询阻塞】(block)
【演示】：
Select * from user limit 10000; /!*查询10000条记录 给观察留有一定的时间*/
Insert into user set ...... /!*查询期间 执行插入 观察是立即执行还是等待查询完毕*/
Select max(uid) from user; /!*由于插入一条速度很快 肉眼无法对比后两条语句的执行顺序 我们选择查看总数变化判断是在插入的前或是后执行*/
b) Mysql允许的几种语句调节符，允许修改他的调度策略 避免发生阻塞
LOW_PRIORITY 应用于DELETE\INSERT\LOAD DATA\REPLACE\UPDATE
继续刚才的场景，如果此时插入变成LOW_PRIORITY 则系统则不会认为它的优先级高于【读取】 因此C可以插队先期执行 以此类推 等待期间不断的查询和插队 可能发生的情况是发生【插入】阻塞
HIGH_PRIORITY 应用于SELECT\INSERT
将优先级提高到不会发生查询阻塞：它允许select插入默认优先级的insert之前，同时可以插队到默认级别的select之前；因为这两者都有可能发生插入阻塞。
DELAYED 应用于INSERT\REPLACE
如果DELAYED insert发生在有用户读取期间 则会等待至无用户读取时开始操作 操作期间同样会不间断检查是否有新读者到达 如果有延迟数据行队列会被挂起 让至其它读者 这个过程一直在执行 直到队列为空 它和LOW_PRIORITY的区别如下：
·LOW_PRIORITY会等待至无其他查询 而DELAYED 则在整个执行期间无节操的让权 时常被挂起
·由于DELAYED的间断性执行 服务器需要不断在内存中缓存数据 直到有时间处理它们
·DELAYED在执行完毕后会返回OK或者ERROR状态给客户端
c) 插入的并发实现
对于Myisam存储引擎有一条规则 它允许读取者阻塞写入者。这种现象发生在当数据表中没有“空洞”时，即数据是连续的存储，那么任何的INSERT必然发生在数据行的末尾而不是中间，这种情况下mysql允许当客户端读取时进行插入操作 这就是“并发性插入”，他们同时进行，检索并没有被阻塞，如果需要并发性插入 需要注意的是：
- INSERT 中不要使用LOW_PRIORITY 以防插入被阻塞
ii. 如果读取者需要显式的锁表 要使用 LOCK TABLES user READ LOCAL 而不是LOCK TABLES user READ,这的LOCAL关键字表示应用于表中已有的数据行 不包括从末尾添加的新的数据行 保证了插入的正常并发....
- 包含“空洞的”myisam的数据表 可以使用OPTIMIZE TABLE user 来整理表中的碎片。
d) 锁的层次和并发性
- 上面的提到的调度调节符 允许数据表锁层次的优先级调度，这些都是myisam和memory存储引擎用来管理数据表争用的问题；BDB和Innodb存储引擎实现了不同层次的锁，所以其某些特征和争用管理是不同的；BDB使用页面层的锁，innodb使用数据行级锁。
ii. 锁的层次对并发有较大影响；假设有两个用户在某个时刻都希望对表进行更新，那么每个客户端都需要一个写入锁，myisam会为第一个用户分配锁，让第二客户进行等待；如果是BDB存储引擎 则有着更好的并发性 他们可以同时对表进行更新，除非他们的操作位于同一个页面；Innodb的并发性更高，只要两个客户端不是更新数据表的同一行，两个更新就能同步操作；
- 一般而言 锁的层次越细微 并发性越好；只要客户端使用表的部分不同， 就可以容纳更多的客户端进行操作，这就意味着不同的操作语句适用于不同存储引擎；鉴于myisam检索的速度较快 同时表级锁容易产生写入阻塞，所以一般用在查询较多的表中；而对于写入较多的数据表 使用innodb引擎可执行更好的并发。
六、存储引擎
a) MYSQL的存储引擎，从事务的角度出发大致分为 处理事务安全表的引擎和处理非事务安全表的引擎
- Myisam管理非事务表 它提供告诉存储和检索的能力，以及全文搜索。早期的mysql默认的存储引擎为myisam
- ii. Memory提供内存中的表 MRG_MYISAM 允许将结构相同的myisam表合并为一个表；和myisam一样 memory和merge存储引擎都处理非事务
- Innodb和BDB支持事务。
- iv. ARCHIVE存储引擎特别适合存储大量的 独立的 作为历史记录的数据
- CSV存储引擎将数据以”,”隔开 存储在文本文件中
vi. BLACKHOLE接受 但不存储数据 检索时总是返回空集
MYSQL总是创建一个.frm文件来存储表和列的定义 表的数据和索引被存储在一个或多个文件里 这取决于表的类型，一个数据库可以包含不同存储引擎的表，支持事务引擎对于非事务引擎有以下几大优势：
更安全 即使MYSQL崩溃或者遇到硬件故障 要么自动恢复 要么根据备份和事务日志来恢复 可以取回数据
多语句 使用commit提交一组SQL指令
回滚机制 发生错误可以恢复到原始状态
高并发 写入时的并发支持度高（innodb)
非事务安全表因为没有事务开支 ，有点主要如下：
快 更快的执行效率
少 更少的磁盘空间
简 执行更新需要更少的内存
b) 在磁盘上每个Myisam存储引擎都存储为 .frm（FRAMEWORK）表定义文件 .myd（MYDATA）表数据文件 .myi（MYINDEX）表定义三个文件，它的部分特征如下：
- 所有数据值先存储低字节，这需要数据库和操作系统分离
ii. 支持大文件的文件系统和操作系统
- 当插入、更新、删除混合时 产生的碎片更少 它会自动合并相邻的删除块
iv. 支持最大索引数为64个 每个索引最大的列数为16个
- BLOB和TEXT列可以被索引
- vi.
七、Mysql分区管理
数据库分区是一种物理数据库设计技术，数据库管理员DBA对其很了解 虽然分区可以实现很多效果 但总的需求是减少I/O请求次数和缩减相应的响应时间，它分为水平分区和垂直分区（横切、竖切）
a) 水平分区
水平分区是对表的行进行分区，通过这样的方式使得不同分组里的【物理列分割的数据集】得以组合，从而进行个体分割（单分区）或者集体分割（一个或多个分区）；所有在表中定义的列在分区中都能找到，表的特性依然保持。Mysql5.1开始增加了对表的水平分区；分区可以有效提升性能和简化数据管理
提升性能
在扫描操作中，如果mysql知道哪个哪个分区包含特定查询所需的数据 则直接去扫描该分区 而不需花时间去扫描不必要的数据
数据管理
分区技术提升DBA对数据库的管理能力 通过优良的分区 DBA还可以简化特定数据操作的执行方式 例如DBA可在删除某些分区内容的同时还能保证余下分区数据的完整性 这样可以瞬间移除大量的记录，如论创建何种分区 它的标号都是从0开始；
- Rang分区
ii. List分区
b)
c) 垂直分区
垂直分区是通过对表的垂直划分来减少列的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列和对应的行，如一个包含BLOB和text列的表 因为他们不经常被访问 可以将他们划分到另一个分区以保证他们在数据相关的同时还能提高访问的速度
八、事务和级联操作
a) 事务就是将一组sql放在同一批次去处理，其中某条sql执行失败，整个批次的操作都会返回原始状态，称为回滚（rollback)；所有操作都正确执行数据才会进行提交（commit）； 现实的案例是用户在线购买商品的业务流程中必须使用事务；
b) 事务的4原则 ACID ：
□原子性（Atomic）：所有步骤必须操作成功 否则任何操作都不会提交
□一致性（Consist）：所有步骤必须操作成功 否则所有数据都会恢复到之前状态
□隔离性（Isolated）：事务过程中的步骤必须与系统隔离 直到完成为止
□持久性（Durable）：所有提交数据必须由系统以某种方式保存 一旦发生未知的故障可恢复至之前的状态
c) 事务的操作流程
Begin 开启事务 禁止自动提交 也可通过设置 SET AUTOCOMMIT = 0完成(关闭自动提交功能)
-》批处理SQL1
SQL2
.......
-》发生问题进行rollback回滚 或 成功进行commit提交
在事务期间 由于回滚的存在 当操作失败 整个业务流程可恢复有效的解决了并发的实现。
d) 非支持事务的实现
使用lock tables tb1, tb2 write //进行锁表
-》批处理SQL1
SQL2
........
最后unlock tables //解锁
它能有效防止操作期间由于并发产生的错误；但是不能自动回滚，需要手动的干预；同时锁表期间其他用户不能访问
e) 外键与参照完整性
在关系型数据库中，某些表信息（子表）是依赖其他表（父表）信息而存在，这种依赖关系决定了当父表记录更新时，字表需要做出相应的动作，即级联操作；对于这种非唯一SQL语句（组）的顺利完成 需要事务的支持。
- 外键是连接父表和字表、实现级联操作的桥梁，一般在子表中定义
Foreign key (index_name) references 主表 (index_column)
[On update action]
[On delete action]
- On [update|delete] cascade
父表记录修改或者删除时 子表同步执行 innodb会为我们自动完成
- On [update|delete] set null
父表记录修改或者删除时 子表中的关联外键值会被设置成NULL
- On [update|delete] no action
父表记录修改或者删除时 如果该字段值存在于在子表中 则会产生错误 相关update|delete 操作将回滚 操作失败~
ii. 构成外键关系的父表和子表中的字段必须是索引，同时字段类型兼容！
- 添加外键约束
Alter table 子表 add constraint ‘fk_子表’
foreign key ‘index_column’ references 主表 (‘index_column’)
On [update|delete] [cascade|set null|no action]
九、SQL编程和存储过程
a) 存储过程是一组为完成特定功能、经过编译后存储在数据库中的SQL语序集。用户通过指定存储过程的名字并给出参数来执行它，它有以下几大优势：
- 灵活性：存储过程中可以进行流程控制和循环操作来完成复杂的判断和运算
ii. 一致性：通过存储过程可以使一些关联的操作一起发生，从而维护了数据库的完整性
- 高效性：存储过程有效减少了数据库开发人员和程序员的工作量
b) 创建存储过程
- CREATE PROCEDURE SP_NAME(IN PRAM TYPE, OUT PRAM TYPE);
IN 表示传入参数 默认不写时表示传入 TYPE 表示类型
OUT 表示传出参数 TYPE表示类型
INOUT 可以使一个传入参数在存储过程中被修改 并传出
存储过程内部语句以”；”结尾，因此在定义存储过程中需要切换控制台的命令结束符号 以避免歧义，可以使用DELIMITER //
ii. 调用存储过程使用 CALL SP_NAME()
- 删除存储过程 DROP PROCEDURE SP_NAME
iv. 查看已定义存储过程 SHOW PROCEDURE STATUS/SHOW CREATE PROCEDURE SP_NAME(详细信息)
//定义一个存储过程
DELIMITER //
CREATE PROCEDURE USER_ID(IN id INT)
BEGIN
SELECT * FROM user WHERE uid = ID;
END//
DELIMITER ;
CALL USER_ID(100);
十、数据库优化
- Mysql分区
- 存储引擎介绍
- Explain解释查询
运行explain select 语句 能够取得查询相关信息 了解如何运行该Select 以便我们合理的进行优化，它向我们展示了以下信息
l ID: mysql查询的序列号
l Select_type: 所用的查询类型 主要有以下几种
n Dependent subquery: 子查询内层的第一个select，依赖外部查询的结果
n Dependent union:子查询中的union 且为union中从第二个select开始的后面所有select 同样依赖于外部的结果集
n Primary ： 子查询中的最外层查询 注意不是主键查询
n Simple： 除了子查询和union之外的其他查询
n Subquery:子查询内层查询的第一个select 结果不依赖于外部的查询结果集
n Uncacheable subquery:结果集无法缓存的子查询
n Union：union语句中第二个select开始后面的所有select，第一个select为primary
n Union result：union中的合并结果
l Table：正在访问的表名称
l Type: 对表的访问方式
n System：系统表 表中只有一行数据
n Const：读常量 最多只会有一条匹配记录 由于是常量 所以实际上只需要读一次 如：
Select * from user where primary_key=1;
n Eq_ref:最多只会有一条匹配结果 一般是通过主键或者唯一索引来访问：如
Select * from user, posts where user.uid = post.uid
n Rel:表关联不是基于主键或者唯一索引的查询 如
Select * from user,posts where user.authname = auth.authname
n
