mysql

一、用户管理

    创建用户

        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。清理完成碎片之后。

        

转载于:https://my.oschina.net/u/2555277/blog/1648977