mysql

mysql：

安装： 

yum remove mariadb.x86_64 
cd /tmp/
https://dev.mysql.com/downloads/repo/yum/
wget  https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum localinstall mysql80-community-release-el7-3.noarch.rpm
yum search mysql 
yum install mysql-community-service. 
ps -ef | grep mysql
service mysqld restart mysql -uroot -p 1111111

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root_12root';

1，mysql表复制

   1>, create table b like a; 把a表结构复制到b

   2>, insert into b(id,name,password,sex) select id,name,password,'纯爷们' from a; 把a指定字段数据插入b

2，mysql索引(统一用alter，兼容性最好)

   2.1，增加索引

          alter table table_name add index index_name(column_list)

          alter table table_name add unique(column_list)

          alter table table_name add primary key(column_list)

 

    2.2，删除索引

          alter table table_name drop index index_name;

 

          查看索引：show index from table_name;

 

3, mysql 视图

    视图是一种中间表，这种中间表的结果集依赖于实体表

    创建一个视图：create view v_t1 as select * from t1 where id>2 and id <12; 创建了一个v_t1视图，它的结果集是依赖t1表的查寻条件

    视图的好处：比如上面视图中，如果id>2 and id <12经常被查询，就可以新建视图，让这部分数据走视图查询，效率就很高

 

4,mysql 内置函数

    Concat 连接字符串。。。。

 

5，mysql 预处理语句

6, 事务处理

   myisam 不支持事务，只有innodb支持

 

7，mysql的存储

 

 

8，mysql的正则表达式 ，关键字regexp ,mysql的输出是select

     select "linux is very good" regexp "^very"

 

9, SELECT name,pname,COUNT(pname) from test GROUP BY name ,pname WITH ROLLUP;

    使用with rollup 关键字后可以得到更多的统计信息，但with rollup不可以和orderby 使用

10，sql语句的优化

      优化步骤：

         1，慢查询查看耗时

         2，查看耗时sql影响条数

         3，索引（通过影响条数查看是否需要加索引）

 

       show status命令了解各种sql的执行频率

       show status LIKE "Com_delete%" 查询自从登录以来有过多少次删除

       show status LIKE "Com_insert%" 。。。。插入

       show status LIKE "Com_select%" 。。。。查询

       show status LIKE "Com_update%" 。。。。更新

       show STATUS LIKE "INNODB_rows_insert%"; 查看innodb的操作情况

 

       查看慢查询配置开启没有：show variables like "%slow%"，慢查询默认查询时间是10秒

       显示是否开启慢查询，以及慢查询的文件位置

       开启慢查询配置：set global slow_query_log = on;

 

       用explain/desc查看sql是否使用索引：DESC SELECT * from test;

 

 

 11 , mysql 索引的优化

        frm文件： 存放表结构

        myd文件： 存放表数据

        myi文件： 存放表索引

 

        分区的作用：在物理上将一张表分为三个文件，分割成许多小块，这样呢，我们查找数据时，就不用全部查找了，只要知道查询数据在哪一块，然后在那一块找就行了；

        分区的两种方式：a，横向分区 b，纵向分区

 

        分区的优点：

            1，分区可以在多个磁盘，存储更大一点

            2，根据查询条件，查找只有相应的分区就不用全部查找了

            3，进行大数据搜索时并进行并行处理

            4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

 

           myisam 存储三个文件，分开存储

           innodb 数据和索引文件存储在一起，两个文件

11.1，不能使用索引的情况：

       11.1.1， like查询时，like ”%3“，这种情况不能使用索引

       11.1.2，and或者or的前后字段都必须有索引，但凡其中一个字段没建索引，带有and/or的sql将不能使用索引

       11.1.3，查询时，where字段的值和表设计时类型不一致，不能使用索引

 

11.2, show status like 'Handler_read%'; 查看可能会用到索引的情况

       Handler_read_rnd_next的值越高，说明建立索引势在必得，再结合慢查询、desc语句优化

 

12，mysql表的优化

      12.1, CHECK table table_name; 检查表是否有错

 

13，mysql中SQL的优化技巧

      13.1，导数据的时候可以关闭索引

      13.2，group by之后本身是升序的，可以通过order by null 来不排序增强性能

      13.3，尽量少用嵌套查询，嵌套查询有时不使用索引，但是左连接右连接可以

 

14，数据库的优化

      优化表的类型：查询多就用myisam，如果需要用事务、外键就用innodb

      通过拆分表提高表的访问效率（分表、分区）

      使用中间表提高查询速度

15，数据库服务器优化

      15.1，lock table t1 read 读锁，只能读，不能写

      Lock table ti write 写锁，只有本人可以增删改查，其他人不能操作

      需要再了解脏读、幻读？？？？？

15.2，四种字符集的问题, 四种一致很重要

     \s 查看四种字符集问题

        服务器字符集、数据库字符集、客户端字符集、连接字符集

15.3，binary log日志问题

15.4，socket问题

15.5，root密码丢失问题

        假想：有一个数据上亿的表，其中一部分数据基本不咋使用，但其中一部分特别活跃，怎么提高其查询速度？

        思路：使用视图，首先统计出点击比较高的帖子导入视图，每次查询的时候先查视图，视图有就直接返回，没有的话说明是非热门帖子，去原表查询；再启动一个定时任务，扫描点击量到一定量就导入视图，点击量小了就从视图中踢出；

 

16,

      16.1， mysq用户授权

         主从的时候，因为不在一态服务器，所以得授权用户，能够从从库登录主库去复制数据

      16.2，mysql bin-log日志

      16.3，mysql主从复制

          原理：从服务器到主服务器获取二进制bin-log日志，执行bin-log达到数据一致原理

          通过执行flush logs命令之后，再次查询binary log信息，发现已经使用了一个新的bin log文件了

 

16.4，mysql的分区技术

        海量数据的优化：1，大表拆为小表 2，SQL语句的优化（索引）

 

 

https://blog.csdn.net/huryer/article/details/103759384 分区

 

查看表结构的SQL： show create table table-name

 

 

17，事务的隔离性

mysql中，innodb存储引擎是支持事务的，而且innodb的存储引擎的事务完全符合ACID的特性

A: atomicity 原子性 ：整个事务过程中要么全部成功，要么全部失败回滚到最初状态

C: consistency 一致性 ：数据库总是从一个一致性转为另外一个一致性

I ：isolation 隔离性 ：事务在操作过程中是否对其他事务可见，不同的业务场景，有不同的隔离级别

D：durability 持久性 ：事务一旦提交，事务过程的修改就永久保存

 

redo log ：mysql会将事务中的sql以及设计数据库操作先记录到redo log中，然后从redo log中同步到对应数据文件中；redo log保证了ACID中的A，即原子性，即事务中的sql被当做一个执行单元。

undo log ： undo log可以理解为数据被修改前的备份，回复数据可以使用undo log

重做日志（redo log）

作用： 　　确保事务的持久性。 　　防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。 内容： 　　物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。 什么时候产生： 　　事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。 什么时候释放： 　　当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

 

回滚日志（undo log）

作用： 　　保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容： 　　逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

什么时候产生： 　　事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

什么时候释放： 　　当事务提交之后，undo log并不能立马被删除， 　　而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

 

二进制日志（binlog）：

作用： 　　1，用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 　　2，用于数据库的基于时间点的还原。 内容： 　　逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。 　　但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息， 　　也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。 　　在使用mysqlbinlog解析binlog之后一些都会真相大白。 　　因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

什么时候产生： 　　事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。 　　这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。 　　因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。 　　这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

什么时候释放： 　　binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

 

 

写锁是排它锁，innodb采用了“一致性非锁定读”的机制提高了数据库并发性。一致性非锁定读表示如果当前行被施加了排它锁，那么当它需要读取行数据的时候，则不会等待行上锁的释放，而是会去读取一个快照数据（undo log）

在可重读的隔离级别下，可能会出现幻读的情况：在两个开启的事务中，事务1 insert 一条记录，且提交了事务1；但是事务2还没执行完，此时select该记录是为空的，但是此时可以update、delete该记录

 

串行化：当事务处于串行化隔离级别是，不可能出现幻读，因为因为对表进行update、insert的时候都会加写锁，另外事务想select数据，必须等update、insert的执行完提交事务；所以，数据库就丧失了并发能力，故很少将隔离级别设置为串行化；

读提交：同时开启的两个事务，事务1操作提交事务后，事务2在未提交情况下就能select到事务1操作过的结果，但这种情况也带来了 不可重读、幻读等问题

读未提交：即使在事务所做的修改未提交，也能select到其修改的数据，也就是出现脏读，这种情况下并发能力最强，但是隔离性和安全性也最差；

 

总结：事务的隔离性越强、并发越差、存在的问题越少，但也要根据业务情况设置隔离级别

sphinx和elasticsearch对比：sphinx安装配置简单，功能单一，但性能不差，个人比较推荐作为mysql的插件来安装使用（还未有时间尝试）。ES呢，安装配置复杂了很多，功能比较强大，分布式可以进行很多细微调整，就是安全性和传统数据库导入不是很容易