MySQL高级
1.MySQL的结构介绍
1.1MySQL简介
1.1.1 概述
1.1.2 高级MYSQL
- mysql内核
- sql优化工程师
- mysql服务器的优化
- 查询语句优化
- 主重复制
- 软硬件升级
- 容灾备份
- sql编程
1.2MySQL-Linux版安装
1.3MySQL主要配置文件
1.3.1 二进制日志log-bin
- 主从复制
1.3.2 错误日志log-error
- 默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等.
1.3.3 查询日志log
- 默认关闭,记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的
1.3.4 数据文件
- 两个系统
- windows
- D:\ProgramFiles\MySQL\MySQLServer5.5\data目录下可以挑选很多库
- linux
- 看看当前系统中的全部库后再进去
- 默认路径：/var/lib/mysql
- windows
- frm文件
- 存放表结构
- myd文件
- 存放表数据
- myi文件
- 存放表索引
1.3.5 如何配置
- windows
- my.ini文件
- Linux
- /etc/my.cnf文件
1.4Mysql逻辑架构介绍
总体概览
1.Connectors
指的是不同语言中与SQL的交互
2 Management Serveices & Utilities：
系统管理和控制工具
3 Connection Pool: 连接池
管理缓冲用户连接，线程处理等需要缓存的需求。
负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。
4 SQL Interface: SQL接口。…查询说明
1.5Mysql存储引擎
- 查看命令
- MyISAM和InnoDB
- 阿里巴巴，淘宝用哪个
2.索引优化分析
2.1 性能下降SQL慢 执行时间长 等待时间长
2.1.1查询语句写的烂
2.1.2索引失效
- 单值
- 复合
2.1.3关联查询太多join(设计缺陷或不得已的需求)
2.1.4服务器调优及各个参数设置(缓冲\线程数等)
2.2 常见通用的join查询
2.2.1 SQL执行顺序
- 手写
- 机读
- 总结
2.2.2 Join图
2.2.3 建表SQL
2.2.4 7种Join
2.3 索引简介
2.3.1 是什么
- MySQL官方对索引的定义为：索引(Index)是帮助MySQL高校获取数据的数据结构。
可以得到索引的本质：索引是数据结构 - 你可以简单理解为”排好序的快速查找数据结构”。
- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上
- 我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，
复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然,除了B+树这种类型的索引之外，还有哈希索引(hash index)等。
2.3.2 优势
- 类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗
2.3.3 劣势
- 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。
因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息 - 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句
2.3.4 mysql索引分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
建议一张表索引不要超过5个
优先考虑复合索引
唯一索引
- 索引列的值必须唯一，但允许有空值
复合索引
- 即一个索引包含多个列
基本语法
- 创建
- 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命令
- 创建
2.3.5 mysql索引结构
BTree索引
Btree索引(或Balanced Tree)，是一种很普遍的数据库索引结构，oracle默认的索引类型（本文也主要依据oracle来讲）。其特点是定位高效、利用率高、自我平衡，特别适用于高基数字段，定位单条或小范围数据非常高效。理论上，使用Btree在亿条数据与100条数据中定位记录的花销相同。
数据结构利用率高、定位高效
Btree索引的数据结构如下：
结构看起来Btree索引与Binary Tree相似，但在细节上有所不同，上图中用不同颜色的标示出了Btree索引的几个主要特点：
树形结构：由根节(root)、分支(branches)、叶(leaves)三级节点组成，其中分支节点可以有多层。…Hash索引
了解
full-text全文索引
了解
R-Tree索引
了解
2.3.6 哪些情况需要创建索引
1.主键自动建立唯一索引
2.频繁作为查询的条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引
因为每次更新不单单是更新了记录还会更新索引，加重IO负担
5.Where条件里用不到的字段不创建索引
6.单间/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
8.查询中统计或者分组字段
2.3.7 哪些情况不要创建索引
- 1.表记录太少
- 2.经常增删改的表
- 3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
