行业网站设计,如何建设彩票网站,网站开发 前端 外包,衣柜全屋定制排名系列文章目录
mysql 讲解一 博客链接 点击此处即可 文章目录 系列文章目录一、事务1.1 事务的四个原则1.2 脏读 不可重复读 幻读 二、索引三,数据库用户管理四、mysql备份 一、事务
1.1 事务的四个原则
什么是事务
事务就是将一组SQL语句放在同一批次内去执行
如果一个SQ…系列文章目录
mysql 讲解一 博客链接 点击此处即可 文章目录 系列文章目录一、事务1.1 事务的四个原则1.2 脏读 不可重复读 幻读 二、索引三,数据库用户管理四、mysql备份 一、事务
1.1 事务的四个原则
什么是事务
事务就是将一组SQL语句放在同一批次内去执行
如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
MySQL事务处理只支持InnoDB和BDB数据表类型
事务的ACID原则 百度 ACID 原子性(Atomic) 整个事务中的所有操作要么全部完成要么全部不完成不可能停滞在中间某个环节。事务在执行过程中发生错误会被回滚ROLLBACK到事务开始前的状态就像这个事务从来没有执行过一样。 一致性(Consist) 一个事务可以封装状态改变除非它是一个只读的。事务必须始终保持系统处于一致的状态不管在任何给定的时间并发事务有多少。也就是说如果事务是并发多个系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)以转账案例为例假设有五个账户每个账户余额是100元那么五个账户总额是500元如果在这个5个账户之间同时发生多个转账无论并发多少个比如在A与B账户之间转账5元在C与D账户之间转账10元在B与E之间转账15元五个账户总额也应该还是500元这就是保护性和不变性。 隔离性(Isolated) 隔离状态执行事务使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务运行在相同的时间内执行相同的功能事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化为了防止事务操作间的混淆必须串行化或序列化请求使得在同一时间仅有一个请求用于同一数据。 持久性(Durable) 在事务完成以后该事务对数据库所作的更改便持久的保存在数据库之中并不会被回滚。
1.2 脏读 不可重复读 幻读
什么是脏读、不可重复读、幻读 脏读 脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。 比如在事务 A 修改数据之后提交数据之前这时另一个事务 B 来读取数据如果不加控制事务 B 读取到 A 修改过数据之后 A 又对数据做了修改再提交则 B 读到的数据是脏数据此过程称为脏读。不可重复读 不可重复读是指在数据库访问中一个事务范围内多次查询却返回了不同的数据值。这是由于在查询间隔中其他事务修改并提交而引起的。 比如事务 T1 读取某一数据事务 T2 读取并修改了该数据T1 为了对读取值进行检验而再次读取该数据便得到了不同的结果。幻读 幻读是指当事务不是独立执行时发生的一种现象例如第一个事务对一个表中的数据进行了修改比如这种修改涉及到表中的全部数据行。同时第二个事务也修改这个表中的数据这种修改是向表中插入一行新数据。那么以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行就好象发生了幻觉一样。 比如事务 A 在按查询条件读取某个范围的记录时事务 B 又在该范围内插入了新的满足条件的记录当事务 A 再次按条件查询记录时会产生新的满足条件的记录。二、索引
索引的作用
提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.
分类
主键索引 (Primary Key)
唯一索引 (Unique)
常规索引 (Index)
全文索引 (FullText)
主键索引
主键 : 某一个属性组能唯一标识一条记录
特点 :
最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置
唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
主键索引只能有一个
唯一索引可能有多个
CREATE TABLE Grade( GradeID INT(11) AUTO_INCREMENT PRIMARYKEY, GradeName VARCHAR(32) NOT NULL UNIQUE – 或 UNIQUE KEY GradeID (GradeID) ) 常规索引
作用 : 快速定位特定数据
注意 :
index 和 key 关键字都可以设置常规索引
应加在查询找条件的字段 不宜添加太多常规索引,影响数据的插入,删除和修改操作 CREATE TABLE result( – 省略一些代码 INDEX/KEY ind (studentNo,subjectNo) – 创建表时添加 ) – 创建后添加 ALTER TABLE result ADD INDEX ind(studentNo,subjectNo); 全文索引 百度搜索全文索引
作用 : 快速定位特定数据
注意 :
只能用于MyISAM类型的数据表
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集 #方法一创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); #方法二CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; #方法三ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; #删除索引DROP INDEX 索引名 ON 表名字; #删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY; #显示索引信息: SHOW INDEX FROM student; */ /增加全文索引/ ALTER TABLE school.student ADD FULLTEXT INDEX studentname (StudentName); explain /EXPLAIN : 分析SQL语句执行性能/ EXPLAIN SELECT * FROM student WHERE studentno‘1000’; /使用全文索引/ – 全文搜索通过 MATCH() 函数完成。 – 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行MATCH() 返回一个相关性值。即在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。 EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST(‘love’); /* 开始之前先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本只有 MyISAM 存储引擎支持全文索引 MySQL 5.6 及以后的版本MyISAM 和 InnoDB 存储引擎均支持全文索引; 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。 测试或使用全文索引时要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。 */
拓展测试索引
建表app_user CREATE TABLE app_user ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, name varchar(50) DEFAULT ‘’ COMMENT ‘用户昵称’, email varchar(50) NOT NULL COMMENT ‘用户邮箱’, phone varchar(20) DEFAULT ‘’ COMMENT ‘手机号’, gender tinyint(4) unsigned DEFAULT ‘0’ COMMENT ‘性别0:男1女’, password varchar(100) NOT NULL COMMENT ‘密码’, age tinyint(4) DEFAULT ‘0’ COMMENT ‘年龄’, create_time datetime DEFAULT CURRENT_TIMESTAMP, update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT‘app用户表’ 批量插入数据100w DROP FUNCTION IF EXISTS mock_data; DELIMITER $$ CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i num DO INSERT INTO app_user(name, email, phone, gender, password, age) VALUES(CONCAT(‘用户’, i), ‘24736743qq.com’, CONCAT(‘18’, FLOOR(RAND()*(999999999-100000000)100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100)); SET i i 1; END WHILE; RETURN i; END; SELECT mock_data(); 索引效率测试
无索引
SELECT * FROM app_user WHERE name ‘用户9999’; – 查看耗时 SELECT * FROM app_user WHERE name ‘用户9999’; SELECT * FROM app_user WHERE name ‘用户9999’;
mysql EXPLAIN SELECT * FROM app_user WHERE name ‘用户9999’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: app_user partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 992759 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) 创建索引
CREATE INDEX idx_app_user_name ON app_user(name); 测试普通索引
mysql EXPLAIN SELECT * FROM app_user WHERE name ‘用户9999’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: app_user partitions: NULL type: ref possible_keys: idx_app_user_name key: idx_app_user_name key_len: 203 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
mysql SELECT * FROM app_user WHERE name ‘用户9999’; 1 row in set (0.00 sec)
mysql SELECT * FROM app_user WHERE name ‘用户9999’; 1 row in set (0.00 sec)
mysql SELECT * FROM app_user WHERE name ‘用户9999’; 1 row in set (0.00 sec) 索引准则 索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段 索引的数据结构 – 我们可以在创建上述索引的时候为其指定索引类型分两类 hash类型的索引查询单条快范围查询慢 btree类型的索引b树层数越多数据量指数级增长我们就用它因为innodb默认支持它
– 不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务支持行级别锁定支持 B-tree、Full-text 等索引不支持 Hash 索引 MyISAM 不支持事务支持表级别锁定支持 B-tree、Full-text 等索引不支持 Hash 索引 Memory 不支持事务支持表级别锁定支持 B-tree、Hash 等索引不支持 Full-text 索引 NDB 支持事务支持行级别锁定支持 Hash 索引不支持 B-tree、Full-text 等索引 Archive 不支持事务支持表级别锁定不支持 B-tree、Hash、Full-text 等索引
三,数据库用户管理
对于用户的管理 比如添加用户删除用户或者给用户授予权限其实都是在修改mysql中的user表
首先如果我们使用sqlyog的话可以直接可视化创建用户以及其权限。 基本命令
/* 用户和权限管理 */ ------------------ 用户信息表mysql.user
– 刷新权限 FLUSH PRIVILEGES – 增加用户 CREATE USER kuangshen IDENTIFIED BY ‘123456’ CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串) 必须拥有mysql数据库的全局CREATE USER权限或拥有INSERT权限。只能创建用户不能赋予权限。用户名注意引号如 ‘user_name’‘192.168.1.1’密码也需引号纯数字密码也要加引号要在纯文本中指定密码需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值需包含关键字PASSWORD – 重命名用户 RENAME USER kuangshen TO kuangshen2 RENAME USER old_user TO new_user – 设置密码 SET PASSWORD PASSWORD(‘密码’) – 为当前用户设置密码 SET PASSWORD FOR 用户名 PASSWORD(‘密码’) – 为指定用户设置密码 – 删除用户 DROP USER kuangshen2 DROP USER 用户名 – 分配权限/添加用户 GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] ‘password’] 比如 给cky这个用户 添加所有的权限 GRANT ALL PRIVILEGES ON . TO cky 注意ALL PRIVILEGES 并不包含 授予权限 例如 在添加用户的同时 也授予了权限 GRANT ALL PRIVILEGES ON . TO ‘cky4’ IDENTIFIED BY ‘123456’ GRANT ALL PRIVILEGES ON . TO ‘cky3’‘%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION; all privileges 表示所有权限. 表示所有库的所有表库名.表名 表示某库下面的某表 – 查看权限 SHOW GRANTS FOR rootlocalhost;查看本地root权限 SHOW GRANTS FOR 用户名 查看某个具体用户 – 查看当前用户权限 SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER(); – 撤消权限 REVOKE 权限列表 ON 表名 FROM 用户名 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 – 撤销所有权限 权限解释
– 权限列表 ALL [PRIVILEGES] – 设置除GRANT OPTION之外的所有简单权限 ALTER – 允许使用ALTER TABLE ALTER ROUTINE – 更改或取消已存储的子程序 CREATE – 允许使用CREATE TABLE CREATE ROUTINE – 创建已存储的子程序 CREATE TEMPORARY TABLES – 允许使用CREATE TEMPORARY TABLE CREATE USER – 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 CREATE VIEW – 允许使用CREATE VIEW DELETE – 允许使用DELETE DROP – 允许使用DROP TABLE EXECUTE – 允许用户运行已存储的子程序 FILE – 允许使用SELECT…INTO OUTFILE和LOAD DATA INFILE INDEX – 允许使用CREATE INDEX和DROP INDEX INSERT – 允许使用INSERT LOCK TABLES – 允许对您拥有SELECT权限的表使用LOCK TABLES PROCESS – 允许使用SHOW FULL PROCESSLIST REFERENCES – 未被实施 RELOAD – 允许使用FLUSH REPLICATION CLIENT – 允许用户询问从属服务器或主服务器的地址 REPLICATION SLAVE – 用于复制型从属服务器从主服务器中读取二进制日志事件 SELECT – 允许使用SELECT SHOW DATABASES – 显示所有数据库 SHOW VIEW – 允许使用SHOW CREATE VIEW SHUTDOWN – 允许使用mysqladmin shutdown SUPER – 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句mysqladmin debug命令允许您连接一次即使已达到max_connections。 UPDATE – 允许使用UPDATE USAGE – “无权限”的同义词 GRANT OPTION – 允许授予权限
四、mysql备份
数据库备份必要性
保证重要数据不丢失
数据转移
MySQL数据库备份方法
mysql备份方法
①数据库管理工具,如SQLyog
②直接拷贝数据库文件和相关配置文件
③命令行 mysqldump命令
作用 :
转储数据库
搜集数据库进行备份
将数据转移到另一个SQL服务器,不一定是MySQL服务器
导出 导出一张表 – mysqldump -uroot -p123456 school student D:/a.sql 语法: mysqldump -u用户名 -p密码 库名 表名 文件名(D:/a.sql)导出多张表 – mysqldump -uroot -p123456 school student result D:/a.sql mysqldump -u用户名 -p密码 库名 表1 表2 表3 文件名(D:/a.sql)导出所有表 – mysqldump -uroot -p123456 school D:/a.sql mysqldump -u用户名 -p密码 库名 文件名(D:/a.sql)导出一个库 – mysqldump -uroot -p123456 -B school D:/a.sql mysqldump -u用户名 -p密码 -B 库名 文件名(D:/a.sql) 可以-w携带备份条件
导入 在登录mysql的情况下-- source D:/a.sql 推荐 source 备份文件位置在不登录的情况下 mysql -u用户名 -p密码 库名 备份文件
