一、用户管理
创建用户
create user 'mark'@'localhost' identified by '1234';
create user 'public'@'%' identified by 'public';
授权
grant select on mydatabase.* to 'mark'@'localhost' ; -- mydatabase所有的表
grant all privileges on mydatabase.student to 'mark'@'localhost';
回收权限
revoke all privileges on mydatabase.student from 'mark'@'localhost';
刷新权限
flush privileges;
删除用户
drop user 'public'@'%';
找回root密码
1)关闭mysql服务器 net stop mysql
2)启动服务器 mysqld.exe --skip-grant-tables
3)新开客户端,进入服务器 mysql
4)修改root密码 update user set password = password('root') where user = 'root' and host = 'localhost';
5)退出服务器,重新启动服务器
二、备份和还原数据库
①数据内容备份和还原
备份:select * into outfile 'd:/backup/student.txt' from student;
select * into outfile 'd:/backup/class.txt' fields enclosed by '"' terminated by '----' lines starting by 'BEGIN:' from class;
还原:load data infile 'd:/backup/student.txt' into table student;
load data infile 'd:/backup/class.txt' into table class fields enclosed by '"' terminated by '----' lines starting by 'BEGIN:';
②数据结构+内容备份和还原
-- 多表备份
mysqldump.exe -hlocalhost -P3306 -uroot -proot mydatabase student class > D:/backup/student_class.sql
-- 还原
外部还原:mysql.exe -hlocalhost -P3306 -uroot -proot mydatabase < D:/backup/student_class.sql
内部还原:source D:/backup/student_class.sql
三、通过binlog还原数据库
前提:开启慢日志。 在配置文件中添加如下语法:log-bin=log日志的文件名。
bin-log 默认是存储在data目录(和数据库的目录一致),我们也可以指定它的存储地址。
查看最后一个bin日志:show master status 清空所有的bin-log日志:reset master
查看log-bin日志里面的内容:mysqlbinlog.exe –-no-defaults log-bin日志的完整路径
还原:
(1)封存记录
刷新日志:flush logs
(2)还原之前备份的数据库
(3)查看binlog日志,找到错误操作,恢复错误之前的所有操作
mysqlbinlog mysql-bin.000001 | less
mysqlbinlog mysql-bin.000001 --start-position=808 | mysql -u root -p 12345
四、主从数据库
解决问题:mysql 并发问题 保证多态数据库数据一致(实时) 多态服务器
主从服务器配置步骤:
主服务器和从服务器都开启bin-log日志 log-bin=mysql-bin
要给主从服务器设置一个唯一的server_id的值。并重启服务器。 server-id=1
①启动mysql服务 /usr/local/mysql/init.d/mysql start
②通过命令行登录管理MySQL服务器 /usr/local/mysql/bin/mysql -uroot -p123456
③授权给从数据库服务器
mysql>grant all on *.* to sync@192.168.10.131 identified by 123456
④查看主服务器的最后一个log-bin日志的名字和pos点
show master status; 要注意:此时要停止对主服务器的任何操作。主服务器有读写操作postion会跟着变
⑤先关闭从服务器(每次重新配置时需要先关闭) stop slave;
⑥配置从数据库
mysql> change master to
master_host=’192.168.10.130’,
master_user=’sync’,
master_password=’password’,
master_log_file=’mysql-bin.000001’,
master_log_pos=261;
⑦开启从服务器,启动slave同步进程 mysql>start slave;
⑧查看是否配置成功: show slave status \G
Slave_IO_Running:Yes 此进程负责从服务器从主服务器上读取binlog 日志,并写入从服务器上的中继日志
Slave_SQL_Running:Yes 此进程负责读取并执行中继日志中的binlog日志
注:以上两个都为yes表明成功,只要其中一个状态是no,则表示复制进程停止,错误原因可从"last_error"字段的值中看
五、读写分离
主数据库:写入
从数据库:查询
锁定
读锁 只能读,其他人也能读 任何人不能写
写锁 只能自己写 其他人不能做任何操作
六、视图
-- 创建视图
create view stu_v1 as select s_id,name,number,height,age from student;
-- 创建视图,视图内容是学生表中的部分字段
-- 查看视图
show tables; -- 查看名字
desc stu_v1; -- 查看结构
show create table stu_v1; --
show create view stu_v1; -- 查看视图创建语句
select * from stu_v1; -- 查看视图内容
-- 修改视图
alter view stu_v1 as select s_id,name,number,height,age,class_id from student;
-- 删除视图
drop view stu_v1;
七、触发器
-- 完整商品触发器
delimiter $$ -- 改变临时语句结束符
create trigger order_goods_insert after insert on `order` for each row
begin
-- 商品表应该减去新增的订单表记录所对应的商品及其数量
-- new代表新增的订单记录
update goods set inv = inv - new.g_num where id = new.g_id;
end
$$
delimiter ; -- 改回语句结束符
-- 订单插入前的触发器
delimiter $$
create trigger order_goods_before_insert before insert on `order` for each row
begin
-- 判断商品的库存是否足够
-- 1.要获得对应的商品以及需要改变的数量
-- new能够获得插入前 的对应的insert语句内的值
-- new.g_id,new.g_num
-- 2.获取商品对应的库存量
-- 将获取到的库存值放到自定义变量goods_inv中
select inv from goods where id = new.g_id into @goods_inv;
-- 3.判断库存是否比订单数量大
-- 如果小于订单数量,那么应该破坏订单记录的生成
if @goods_inv < new.g_num then
-- 破坏语句执行,暴力执行
insert into XXX values('XXX');
-- 没有XXX表,所以执行会失败,所以就终止了SQL执行
end if;
end
$$
delimiter ;
-- 删除触发器
drop trigger order_goods_insert;
八、系统函数
substring:字符串截取(字符长度截取)
char_length:字符长度
length:字节长度
instr:判断字符串是否在目标字符串中,返回字符串的起始位置
lpad:左填充,左边填充内容(SELECT lpad(`sn`,25,'0') FROM `orders`;)
insert:向目标字符串中替换字符串
strcmp:比较,第一个大于第二个返回1,等于返回0,小于返回-1
九、自定义函数
-- 从1开始,求和到指定的数值,返回计算结果,如果数据有5这个数值,不要
delimiter $$
create function myfunc_while(int_1 int) returns int
begin
-- 定义一个局部变量保存计算结果
declare res int default 0;
-- 定义一个循环变量,默认值是1
declare i int default 1;
-- 循环操作,条件是判断i与int_1的关系
mywhile:while i <= int_1 do
-- 判断i是否等于5
if i = 5 then
-- 修改循环变量
set i = i + 1;
-- 继续执行循环
iterate mywhile;
end if;
-- 满足条件,求和
set res = res + i;
-- 修改循环变量
set i = i + 1;
end while;
-- 返回值
return res;
end
$$
delimiter ;
十、存储过程
-- 创建存储过程
delimiter $$ -- 改变临时语句结束符
create procedure mypro1()
begin
select * from student;
end
$$
delimiter ; -- 改回语句结束符
-- 使用
call mypro1();
-- 删除
drop mypro1;
十一、常见数据库的优化方式
1、表的设计要符合三范式
2、添加适当的索引,索引对查询速度影响很大,必须添加索引。主键索引,唯一索引,普通索引,全文索引
3、添加适当存储过程,触发器,事务等
4、读写分离(主从数据库)
5、对sql语句的一些优化,(查询执行速度比较慢的sql语句)
6、分表分区 分表:把一张大表分成多张表。分区:把一张表里面的分配到不同的区域存储
7、对mysql服务器硬件的升级操作
十二、提高效率,反三范式
1、三范式:
第一范式:原子性:表里面的字段不能再分割,只要是关系型数据库,就天然的自动满足第一范式。
第二范式:一个表中没有完全相同的记录,通过一个主键即能解决。
第三范式:表中不能存储冗余数据
2、反三范式
十三、定位慢查询
慢查询:找出在一个网站中查询速度比较慢的语句,可以开启一个日志文件,记录查询速度比较慢的sql语句。在默认情况下,慢查询日志是关闭的,默认记录时间是超过10秒的sql语句。
1、以记录慢查询的方式来启动mysql
mysqld.exe --safe-mode --slow-query-log
2、查看慢查询日志的存储路径和慢sql时间的修改
开启了慢查询日志后,会建立一个慢查询日志文件。该日志文件保存在数据库的目录下,数据库的目录可以通过配置文件查看。
在Data目录下查看文件名为HP-HP-slow.log的文件
查看当前数据库下慢查询记录时间:show variables like 'long_query_time';
修改慢查询时间:set long_query_time=2;
benchmark(count,expr)函数测试执行count次expr需要的时间:select benchmark(200000000,10000*10);
一般情况下,出现查询比较慢的语句,是没有添加索引导致的。
十四、索引
1、主键索引
可以在建立表的添加create table emp(id int primary key)
可以在建立完表之后添加:alter table emp add primary key(列1,列2)
主键索引的特点:
(1)一个表中最多只有一个主键索引 (2)一个主键索引可以指向多个列
(3)主键索引的列,不能有重复的值,也不能有null(4)主键索引的效率高
2、唯一索引
可以在建立表的时候添加:create table emp(name varchar(32) unique)
在建完表之后添加:alter table emp add unique [索引名](列1,列2)
唯一索引的特点:
(1)一个表中可以有多个唯一索引 (2)一个唯一索引可以指向多个列 (3)唯一索引的效率较高
(4)如果在唯一索引上,没有指定not null,则该列可以为空,同时可以有多个null
3、普通索引
使用普通索引主要是提高查询效率
添加alter table tablename add index [索引名](列1,列3)
4、全文索引
mysql自带的全文索引mysql5.5不支持中文,支持英文,同时要求表的存储引擎是myisam。支持中文有两个方案
(1)使用aphinx中文版coreseek (来替代全文索引) (2)插件mysqlcft。
5、查看索引
(1)show index from 表名 (2)show indexes from 表名
(3)show keys from 表名 (4)desc 表名
6、删除索引
(1)主键索引的删除:alter table tablename drop primary key;在删除主键索引时,要去掉auto_increment属性
(2)唯一索引的删除:alter table tablename drop index 唯一索引的名字
(3)普通索引的删除:alter table tablename drop index 普通索引的名字
7、添加索引主要的问题
(1)较频繁的作为查询条件字段应该创建索引,如select * from emp where empno = 1
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件,如select * from emp where sex = '男‘
更新非常频繁的字段不适合创建索引,如select * from emp where logincount = 1
(2)不会出现在WHERE子句中字段不该创建索引
索引是有代价的,虽然是查询速度提高了,但是,会影响增删改的效率,而且索引文件会占用空间。
十五、explain工具的讲解
该工具能够分析sql执行效率,但是并不执行sql语句。主要是查看sql语句是否用到索引。
语法:explain sql语句\G 或 desc sql语句\G
使用索引时: 没有使用索引时:
explain工具的参数说明:
select_type:表示查询的类型 table:输出结果集的表 type:表示表的连接类型
possible_keys:表示查询时,可能使用的索引 key:表示实际使用的索引 key_len:索引字段的长度
rows:扫描出的行数(估算的行数) Extra:执行情况的描述和说明
十六、索引优化应用案例
1、对于创建的多列(复合)索引,只要查询条件使用了最左边的列,索引一般就会被使用。
mysql> alter table user add index (name,email);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
2、使用like的查询,如果是"%XXX",不会使用到索引,"XXX%"会使用到索引
在有些情况下,还是会用到like查询,比如通过歌词搜索歌名,通过剧情搜索电影名。可以借助于sphinx里面的coreseek软件。
3、如果条件中有or,则要求or的字段都必须有索引,否则不能用到索引
4、如果列类型是字符串,一定要在条件中将数据使用引号引用起来,否则不使用索引
5、优化group by语句
默认情况下,mysql对所有的group by col1,col2字段进行排序。如果查询中包括group by但用户想要避免排序结果的消耗,则可以使用order by null禁止排序。
6、当取出的数据量超过表中数据的20%,优化器就不会使用索引,而是全表扫描。
扫描的行数太多了,优化器认为全表扫描比索引快,尽量少使用子查询,可以使用连接查询
7、查看索引的使用情况
handler_read_key值越高越好,越高表示使用索引查询到的次数。handler_read_rnd_next这个值高,说明查询低效。
8、对应大批量插入数据,需要注意
对于MyISAM:
alter table table_name disable keys;//先禁用索引:
loading data//insert语句; 执行插入语句
alter table table_name enable keys;//执行完成插入语句后,开启索引,统一添加索引。
对于Innodb:
①将要导入的数据按照主键排序
②set unique_checks=0,关闭唯一性校验。
③set autocommit=0,关闭自动提交。
十七、并发处理的锁机制
锁机制:在执行时,只有一个用户获得锁,其他用户处于阻塞状态,需要等待解锁。
mysql 的锁有以下几种形式:
表级锁:开销小,加锁快,发生锁冲突的概率最高,并发度最低。myisam引擎属于这种类型。
行级锁:开销大,加锁慢,发生锁冲突的概率最低,并发度也最高。innodb属于这种类型。
1、表级锁的演示:lock table t5 read;//对t5表加读锁
①对myisam表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进程的操作。
表添加读锁后,其他进程对该表只能查询操作,修改时会被阻塞。
当前进程,能够执行查询操作,不能执行修改操作。不能对没有锁定的表进行操作。
表级锁的语法:lock table 表名 read|write 也可以锁定多个表,语法是:lock table 表1 read|wirte,表2 read|wirte
②对myisam表的写操作(加写锁),会阻塞其他进程对锁定表的任何操作,不能读写
表加写锁后,则只有当前进程对锁定的表,可以执行任何操作。其他进程的操作会被阻塞。
2、行级锁的演示
innodb存储引擎是通过给索引上的索引项加锁来实现的,这意味着只有通过索引条件检索数据时innodb才会使用行级锁,否则innodb使用表级锁。
行锁的语法:
begin
sql语句
commit
开启行锁后,当前进程在针对某条记录执行操作时,其他进程不能操作和当前进程相同id的记录。
php里面有文件锁,在实际的项目中多数使用文件锁,因为表锁,会阻塞,当对一些表添加写锁后,其他进程就不能操作了。这样会阻塞整个网站,会拖慢网站的速度。
十八、分表技术
分表:把一个大表分成几个小表
垂直分割:
在一个数据库中想要存储各种数据,比如说文章数据,电影,音乐,商品数据,内容主表+附加表:
内容主表:存储各种数据的一些公共信息,比如数据的名称,添加时间等,可以使用多个附加表,附加表存储一些数据的独特的信息。
主要原因:是内容主表里面的数据访问比较频繁。
水平分割:
通过id取模
十九、分区技术
就是把一个表存储到磁盘不同区域,仍然是一张表。
1、基本的概念:
mysql5.1后有4种分区类型:
(1)Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
(2)List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割
(3)Hash(哈希)–这种模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表
(4)Key(键值)-上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的
2、range分区:
假如你创建了一个如下的表,该表保存有20家超市的职员记录,这20家超市的编号从1到20。如果你想将其分成4个小分区,可以采用range分区,创建的数据表如下:
创建range分区语法:
create table emp(
id int not null,
name varchar(32) not null default '' comment ‘职员的名称’,
store_id int not null comment ‘超市的编号范围是1-20’
)engine myisam charset utf8
partition by range(store_id)(
partition p0 values less than(6), //是store_id的值小于6的存储区域。
partition p1 values less than(11), //是store_id的值大于等于6小于11的存储区域。
partition p2 values less than(16),
partition p3 values less than(21)
)
insert into emp values(1,’杨过’,1)-->数据是存储到p0区
insert into emp values(23,’小龙女’,15)-->数据是存储到p2区
insert into emp values(100,’李莫愁’,11)-->数据是存储到p2区
测试使用取出数据时是否用到分区:在取出数据时,条件中必须partition by range(store_id),range里面的字段。
3、list分区
创建一个表,该表保存有20家超市的职员记录,这20家超市的编号从1到20。而这20家超市分布在4个有经销权的地区,如下表所示:
create table emp(
id int not null,
name varchar(32) not null default '',
store_id int not null
)
partition by list(store_id)(
partition p0 values in(5,6,7,8),
partition p1 values in(11,3,12,11),
partition p2 values in(16),
partition p3 values in(21)
)
注意:在使用分区时,where后面的字段必须是分区字段,才能使用到分区。
4、分区表的限制
(1)只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列
(2)最大分区数目不能超过1024
(3)如果含有唯一索引或者主键,则分区列必须包含在所有的唯一索引或者主键在内
(4)按日期进行分区很非常适合,因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多
二十、其他调优
1、选择合适的存储引擎(myisam innodb)
MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子表)
InnoDB:提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM,写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高,则使用innodb)。[账户,积分]
Memory/heap [一些访问频繁,变化频繁,又没有必要入库的数据 :比如用户在线状态]
说明: memory表的数据都在内存中,因此操作速度快,但是缺少是当mysql重启后,数据丢失,但表的结构在.
注:从mysql5.5.x开始,默认的存储引擎变更为innodb,innodb是为处理巨大数据量时拥有最大性能而设计的。它的 cpu效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
2、数据类型的选择
(1)在精度要求高的应用中,建议使用定点数来存储数值,以保证结果的准确性。decimal 不要用float
(2)要用于存储手机号,哪个类型比较合适
假如我们要用char(11),如果字符集是utf8 则占用多少个字节,11*3==33。如果是gbk字符集则占用11*2=22个字节,如果用bigint型存储,则占用8个字节
(3)如果要存储ip地址。假如用char(15)占用很多字节,能否用整型来存储呢?
可以通过一个函数,把ip地址转换成整数。可以使用int来存储
inet_aton():把ip地址转换成整数。 inet_ntoa():把整数转换成ip地址。
(4)根据需求选择最小整数类型。比如用户在线状态:离线,在线,离开,忙碌,隐式等,可以采用0,1,2,3,5来表示,没有必要用char()或varchar()型来存储字符串。
3、myisam表的定时维护
对于myisam存储引擎而言,需要定时执行optimize table 表名,通过optimize table语句可以消除更新和删除造成的磁盘碎片,从而减少空间的浪费。
create table temp2( id int) engine=MyISAM;
insert into temp2 values(1);
在之前数据的容量:
语法格式:optimize table temp2。清理完成碎片之后。