mysql学习

一、mysql配置文件(主要配置文件)

1、二进制日志(log-bin)：

定义：

        二进制日志，记录对数据发生或潜在发生更改的sql语句，并以二进制的形式保存在磁盘。

作用：

        可以用来查看数据库的变更历史(具体的时间点所有的sql操作)、数据库增量备份和恢复(增量备份和基于时间点的恢复)、Mysql的复制(主主数据库的复制、主从数据库的复制)

2、查询日志(log)

默认是关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的

 3、错误日志(log-error)

默认是关闭的，记录严重的警告和错误信息，每次开启关闭的详细信息等

4、数据文件

windows：D:\devSoft\mysqlServer5.5\data目录下可以挑选很多库

linux：默认路径：/var/lin/mysql

frm文件：存放表结构

myd：存放表数据

myi：存放表索引

二、mysql架构图

   

 1、连接层

最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。

主要完成一些类似连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为了通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2、服务层

第二层架构主要完成大多数的核心服务功能，如sql接口并完成缓冲查询，sql的分析和优化及部分内置函数执行。所有跨存储引擎的功能也是在这一层实现，如存储过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，对其完成相应优化确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器海货查询内部的缓存。如果缓存空间足够大，这样在解决大量读写操作的环境中能很好的提升系统的性能。

3、引擎层

存储引擎真正的负责了mysql中数据的存储和提取，服务器通过API与存储引擎进行通信，不同存储引擎具有的功能不同。

4、存储层

主要是将数据存储在运行于裸设备的文件系统之上，并完成于存储引擎的交互。

 

 三、sql执行顺序

1、form 2、on 3、join 4、where 5、group by 6、having 7、select 8、distinct 9、order by 10、limit

四、常见通用的join查询

 select * from tableA a inner join tableB b on a.key=b.key

 select * from tableA a right join tableB on a.key=b.key

 select * from tableA a left join tableB on a.key=b.key 

  select * from tableA a left join tableB on a.key=b.key where b.key is null

 select * from tableA a right join tableB on a.key=b.key where a.key is null

 

select * from tableA a full outer join tableB b on a.key=b.key

select * from tableA a full outer join tableB b on a.key=b.key where a.key is null or b.key is null

 五、索引

什么是索引？

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

1、优势

1、提高数据检索的效率，降低数据库的IO成本

2、通过索引列对数据库进行排序，降低数据库排序的成本，降低了CPU的消耗

2、劣势

1、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。所以索引列也是占用空间的

2、虽然索引打大提高了查询速度，同事却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3、索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询。

六、MySQL索引分类

1、单值索引

即一个索引包含单个列，一个表中可以有多个单列索引

2、唯一索引

索引列值必须唯一，但运行有空值

3、复合索引

复合索引即一个索引包含多个列

原理：

        就像你拿到一本书的目录，里头有标题和对应的页码，当你想知道第200也的标题是什么的时候，直接看目录。

        同理，当你要select的字段，已经在索引树里面存储了，那就不需要再去检索数据库，直接拿来用就行了。

案例：

        a、b、c三个字段建立了复合索引，那么 select b,c from tabelName where a = 'xxx'; explain一下，你就会发现extra字段 "Using index"，或者使用explain fromat = json...输出一个json结构的结果，看 "Using_index"属性你会发现是true，这就意味着使用到了覆盖索引

覆盖索引和联合索引区别：

        没有区别，覆盖索引只是特定于具体select语录而言的联合索引。也就是说一个联合索引对于某个select语句，通过索引可以直接获取查询结果，而不再需要回表查询啦，就称该联合索引覆盖了这条select语句。

4、主键索引

设定为主键后数据库自动建立索引，innodb为聚簇索引

5、聚簇索引

聚簇索引说明

聚簇索引并不是一种单的的索引类型，而是一种数据存储的方式。innodb中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。

优点

1、数据访问更快，因为聚簇索引将索引和数据保存在同一个B+tree中，因此从聚簇索引中获取数据比非聚簇索引更快

2、聚簇索引对于主键的排序和范围查找速度非常快

缺点

1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于innodb表，我们一般都会定义一个自增的id列为主键。

2、更新主键的代价很高，因为将会导致被更新的行移动。因此对于innodb表，我们一般定义主键为不可更新

3、二级索引访问需要两次索引查找。第一次找到主键，第二次根据主键值找到行数据。

6、基础语法

-- 创建唯一索引
create [unique] index indexName on mytable(columnname（length))

-- 修改唯一索引
alter mytable add [unique] index [indexName] on (columnname（length）

-- 删除
drop index [indexName] on mytable

-- 查看
show index from table_name\G

--该语句添加一个主键
alter table tbl_name add primary key(column_list)

-- 这条语句创建索引的值必须是唯一的(除了null外，null可能会出现多次)
alter table tbl_name add unique index_name(column_list)


-- 添加普通索引，索引值可出现多次
alter table tbl_name add index index_name(column_list)

-- 该语句指定了索引为FULLTEXT，用于全文索引
alter table tbl_name add fulltext index_name(column_list)

七、索引原理

初始化：

         一颗B+tree，浅蓝色的块我称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色)和指针(黄色所示)

P1：表示小于17的磁盘块

P2：表示在17和35之间的磁盘块

P3：表示大于35的磁盘块

真实数据存在叶子节点，即：3、5、9、10、13、15、28、36、60、75、79、90、99。飞叶子节点不存储真是数据，只存储指引搜索方向的数据项，如17、35并不是真实存在于数据表中。

查找过程：

        如果要查找的数据项是29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针的磁盘地址把磁盘块3加载到内存，此时发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，此时发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。