MySQL的架构介绍
MySQL简介
概述
- MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性
- Mysql是开源的，所以你不需要支付额外的费用
- Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统
- Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库
- MySQL使用标准的SQL数据语言形式
- Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等
- MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB
MySQL高级
提示：完整的mysql优化需要很深的功底，大公司设置有专门的DBA写上述
- MySQL内核
- SQL优化工程师
- MySQL服务器的优化
- 各种参数常量设定
- 查询语句优化
- 主从复制
- 软硬件升级
- 容灾备份
- SQL编程
Linux版MySQL安装
官网
拷贝解压缩
- xftp，将下载的压缩包拷贝至 /opt 目录下并解压
检查工作
- 检查当前系统是否安装过mysql
1 | shell复制代码 rpm -qa|grep -i mysql |
- 删除命令
1 | shell复制代码rpm -e --nodeps mysql-libs |
- CentOS的默认数据库已经不再是MySQL,而是MariaDB,先删除自带的MariaDB
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品
1 | scss复制代码//查看mariadb |
安装
- 依次安装
1 | shell复制代码rpm -ivh mysql-community-common-5.7.31-1.el7.x86_64.rpm |
查看MySQL安装版本
- 查看安装的mysql版本
1 | shell复制代码mysqladmin --version |
- 查看mysql的用户和用户组
+ 1
2
3
4
shell复制代码//mysql用戶
cat /etc/passwd|grep mysql
//mysql用戶組
cat /etc/group|grep mysql
MySQL服务启动、停止
- 启动
1 | shell复制代码service mysqld start |
- 停止
1 | shell复制代码service mysqld stop |
首次登陆
Mysql5.7默认安装之后root是有密码的
+ 获取MySQL的临时密码
+ 为了加强安全性，MySQL5.7为root用户随机生成了一个密码，在error log中，关于error log的位置，如果安装的是RPM包，则默认是/var/log/mysqld.log，只有启动过一次mysql才可以查看临时密码
1
shell复制代码 grep 'temporary password' /var/log/mysqld.log
- 使用临时密码登录
mysql -u root -p - 使用临时密码登录后，并不能操作数据库，必须修改临时密码
1
2
3
4
5mysql复制代码//如果修改的密码过于简单 必须先修改两个全局策略
set global validate_password_policy=0;
set global validate_password_length=1;
//策略修改成功后再设置密码
alter user 'root'@'localhost' identified by '123456';
自启动MySQL服务
- 设置mysql自启动
1 | shell复制代码systemctl enable mysqld |
- 查看
1 | shell复制代码systemctl list-unit-files | grep mysqld |
MySQL的安装位置
参数 路径 解释 备注 –basedir /usr/bin 相关命令目录 mysqladmin、mysqldump等命令 –datadir /var/lib/mysql mysql数据库文件的存放路径 –plugin-dir /usr/lib64/mysql/plugin mysql插件存放路径 –log-error /var/lib/mysql/xx.err mysql错误日志路径 –pid-file /var/lib/mysql/xx.pid 进程pid文件 –socket /var/lib/mysql/mysql.sock 本地连接时用的unix套接字文件 /usr/share/mysql 配置文件目录 mysql脚本及配置文件 /etc/init.d/mysql 服务启停相关脚本
修改配置文件位置
- 查看默认配置
1 | shell复制代码vim /etc/my.cnf |
修改字符集
- 连接mysql，建库建表插入数据，一切正常
1 | mysql复制代码//建库db01 |
- 向user表中插入中文
- 查看字符集
1 | mysql复制代码show variables like 'character%'; |
1
sql复制代码insert into user values(3,'张三');
插入失败，插入中文字符集需修改配置
- 编辑 /ect/my.cnf 文件
1 | shell复制代码vim /etc/my.cnf |
增加一行character-set-server=utf8,保存退出并重启mysql服务 service mysqld restart
注意重新连接后，之前建立表仍然不允许，插入中文。需要重新create database并使用新建库，再重新建表
1 | mysql复制代码//建库db02 |
MySQL配置文件
如何配置
- windows环境
my.ini文件 - Linux环境
/etc/my.cnf文件
二进制日志log-bin
- 主从复制（先了解，后面会详细讲解）：log-bin 中存放了所有的操作记录(写？)，可以用于恢复。相当于 Redis 中的 AOF
错误日志log-error
- 默认是关闭的,记录严重的警告和错误信息，每次启动和关闭的详细信息等
查询日志log
- 默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的
- 可自定义“慢”的概念:0-10秒之间的一个数。慢查询日志会将超过这个查询事件的查询记录下来，方便找到需要优化的 sql 。用于优化sql语句是使用。
数据文件
- 两种环境
- Windows安装路径
- Linux：
/var/lib/mysql
- frm文件（framework）：存放表结构
- myd文件（data）:存放表数据
- myi文件（index）：存放表索引
MySQL逻辑架构介绍
总体概览
设计思路
+ 和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，**插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离**。这种架构可以根据业务的需求和实际需要选择合适的存储引擎- 层级结构
+ 1.连接层
- 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限
+ 2.服务层
- 2.1 Management Serveices & Utilities： 系统管理和控制工具
- 2.2 SQL Interface: SQL接口接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
- 2.3 Parser: 解析器，SQL命令传递到解析器的时候会被解析器验证和解析
- 2.4 Optimizer: 查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。 用一个例子就可以理解： select uid,name from user where gender= 1;优化器来决定先投影还是先过滤
- 2.5 Cache和Buffer： 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据，这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
缓存是负责读，缓冲负责写
+ 3.引擎层
- 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介MyISAM和InnoDB
+ 4.存储层
- 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互查询说明
- 查询流程图
- 解释说明
+ mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中(一模一样的sql才能命中)，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能
+ 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法
+ 查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划
+ 然后，mysql默认使用的BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引MySQL存储引擎
查看命令
- 查看当前mysql使用的存储引擎
1 | ini复制代码show engines; |
- 查看你的mysql当前默认的存储引擎：
1 | sql复制代码show variables like '%storage_engine%'; |
各个引擎简介
- InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况 - MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复 - Archive引擎
Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引
Archive表适合日志和数据采集类应用，适合低访问量大数据等情况
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83% - Blackhole引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐 - CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引
CSV引擎可以作为一种数据交换的机制，非常有用
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取 - Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis) - Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的
MyISAM和InnoDB（*）
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整张表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | Y | Y |
| 用户表默认使用 | N | Y |
| 自带系统表使用 | Y | N |
- innoDB 索引，使用B+树，MyISAM 索引使用 B-树
- innoDB 主键为聚簇索引，基于聚簇索引的增删改查效率非常高
阿里巴巴、淘宝用哪个
- Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为
- 该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好
- 阿里巴巴大部分mysql数据库其实使用的是percona的原型加以修改
- AliSql+AliRedis
索引优化分析
性能下降SQL慢
查询语句写的差
- 能不能拆，条件过滤尽量少
索引失效
- 单值
- 复合
- 条件多时，可以建共同索引(混合索引)。混合索引一般会偶先使用。有些情况下，就算有索引具体执行时也不会被使用
关联了太多join
- 设计缺陷或不得已的需求
服务器调优及各个参数设置（缓冲、线程数等）(不重要DBA的工作)
常见通用的Join查询
SQL执行顺序
- 手写
+ - 机读（先从
From开口）
+ 随着Mysql版本的更新换代，其优化器也在不断的升级，**优化器**会分析不同执行顺序产生的性能消耗不同而**动态调整执行顺序**
+ 下面是经常出现的查询顺序：
- 总结
+ JOIN关系图
- 所有的join关系
- 共有独有的理解
共有：满足 a.deptid = b.id 的叫共有
A独有: A 表中所有不满足 a.deptid = b.id 连接关系的数据
同时参考 join 图
建表SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31mysql复制代码CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('RD',11);
INSERT INTO t_dept(deptName,address) VALUES('HR',12);
INSERT INTO t_dept(deptName,address) VALUES('MK',13);
INSERT INTO t_dept(deptName,address) VALUES('MIS',14);
INSERT INTO t_dept(deptName,address) VALUES('FD',15);
INSERT INTO t_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO t_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO t_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO t_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO t_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO t_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO t_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO t_emp(NAME,deptId) VALUES('s9',51);
7中JOIN
- 笛卡尔积
1 | mysql复制代码select * from t_dept,t_emp |
- 两表共有的 （INNER JOIN）
1 | mysql复制代码select * from t_dept d inner join t_emp e on d.id=e.deptId; |
- 左外连接（LEFT JOIN）两表共有的加左表独有
1 | mysql复制代码select * from t_dept d left join t_emp e on d.id=e.deptId; |
- 右外连接（RIGHT JOIN）两表共有的加右表独有
1 | mysql复制代码select * from t_dept d right join t_emp e on d.id=e.deptId; |
- 左表独有的
1 | mysql复制代码select * from t_dept d left join t_emp e on d.id=e.deptId where e.id is null; |
- 右边独有的
1 | mysql复制代码select * from t_dept d right join t_emp e on d.id=e.deptId where d.id is null; |
- 两表全有
1 | mysql复制代码#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法 |
- 左表独有＋右表独有
1 | mysql复制代码select * from t_dept d left join t_emp e on d.id=e.deptId where e.id is null union select * from t_dept d right join t_emp e on d.id=e.deptId where d.id is null; |
索引简介
是什么(*)
- MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
+ 索引的目的在于提高查询效率，可以类比字典
+ 如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql
+ 如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？你可以简单理解为“排好序的快速查找数据结构”
+ 详解（\*）
- 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据
- 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是**索引**。下图就是一种可能的索引方式示例
- 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录
- 二叉树弊端之一：二叉树很可能会发生两边不平衡的情况
B-TREE: (B:balance) 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。(myisam使用的方式)
B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整平衡，消耗性能)从侧面说明了索引不是越多越好
B+TREE:Innodb 所使用的索引
+ 结论
- > 数据本身之外，数据库还维护着一个**满足特定查找算法的数据结构**，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是**索引**- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
- 我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
优势
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE,因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句
mysql索引结构
BTree索引（*）
BTree又叫多路平衡查找树，一颗m叉的BTree特性如下：
+ 树中每个节点最多包含m个孩子
+ 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子（ceil()为向上取整）
+ 若根节点不是叶子节点，则至少有两个孩子
+ 所有的叶子节点都在同一层
+ 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1- Myisam普通索引
- 检索原理
这是一个3叉（只是举例，真实会有很多叉）的BTree结构图，每一个方框块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，紫色代表的是磁盘块中的数据key，黄色代表的是数据data，蓝色代表的是指针p，指向下一个磁盘块的位置
- 来模拟下查找key为29的data的过程：
+ 根据根结点指针读取文件目录的根磁盘块1。【磁盘IO操作**1次**】
+ 磁盘块1存储17，35和三个指针数据。我们发现17<29<35，因此我们找到指针p2
+ 根据p2指针，我们定位并读取磁盘块3。【磁盘IO操作**2次**】
+ 磁盘块3存储26，30和三个指针数据。我们发现26<29<30，因此我们找到指针p2
+ 根据p2指针，我们定位并读取磁盘块8。【磁盘IO操作**3次**】
+ 磁盘块8中存储28，29。我们找到29，获取29所对应的数据data- 由此可见，BTree索引使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率
B+Tree索引（*）
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度- innodb的普通索引
- 原理图
B+TREE 第二级的 数据并不能直接取出来，只作索引使用。在内存有限的情况下，查询效率高于 B-TREE
B-TREE 第二级可以直接取出来，树形结构比较重，在内存无限大的时候有优势
- B+Tree与B-Tree 的区别：结论在内存有限的情况下，B+TREE 永远比 B-TREE好。无限内存则后者方便
+ 非叶子节点只存储键值信息， 数据记录都存放在叶子节点中， 将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，所以B+Tree的高度可以被压缩到特别的低
+ 在B+Tree上通常有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。所以我们除了可以对B+Tree进行主键的范围查找和分页查找，还可以从根节点开始，进行随机查找- 思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？
+ B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了
+ B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当- B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）
聚簇索引与非聚簇索引
- 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值紧凑的存储在一起
- 如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致
- 聚簇索引的好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作
聚簇索引的限制：
对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键，为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种（参考聚簇索引的好处） - 这里说明了主键索引为何采用自增的方式：1、业务需求，有序 2、能使用到聚簇索引
Hash索引
full-index全文索引
R-Tree索引
mysql索引分类
单值索引
- 即一个索引只包含单个例，一个表可以有多个单列索引
唯一索引
- 索引列的值必须唯一，但允许有空值
复合索引
- 即一个索引包含多个列
- 在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)当表的行数远大于索引列的数目时可以使用复合索引
基本语法
- 创建：ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length)) ;
- 删除：DROP INDEX [indexName] ON mytable;
- 查看：SHOW INDEX FROM table_name\G;
- 使用ALTER命令
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT 用于全文索引
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录还需要更新索引
- where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
哪些情况不需要创建索引
- 表记录太少
- 经常增删改的表–提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件 - 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
性能分析
MySQL Query Optimizer
- MySQL优化器
MySQL常见瓶颈
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- SQL中对大量数据进行比较、关联、排序、分组
- IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
- 实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO
- 查询执行效率低，扫描过多数据行
- 锁
- 不适宜的锁的设置，导致线程阻塞，性能下降
- 死锁，线程之间交叉调用资源，导致死锁，程序卡住
- 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
Explain（*）
- 是什么(查看执行计划)
+ 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
+ [官网介绍](http://dev.mysql.com/doc/refman/5.5/en/explain-output.html)
- 能干嘛
+ 表的读取顺序---id字段
+ 数据读取操作的操作类型---select\_type
+ 哪些索引可以使用---possible\_key
+ 哪些索引被实际使用---key
+ 表之间的引用
+ 每张表有多少行被优化器查询---rows- 怎么玩
+ Explain + SQL语句
+ 执行计划包含的信息
- 各字段解释
+ id
- select查询的序列号，包含一组数字，表示**查询中执行select子句或操作表的顺序**
- 三种情况
1. id相同，执行顺序由上至下（t1--t3--t2）
id相同，执行顺序由上至下。此例中 先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id ，而 t2.id 的结果建立在 t2.id=t3.id 的基础之上
2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行（t3--t2--t1）
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id相同不同，同时存在(t3--derived2--t2)
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引）
+ select\_type
- 有哪些类型
| 类型 | 解释说明 |
| --- | --- |
| SIMPLE | 简单的 select 查询,查询中不包含子查询或者UNION |
| PRIMARY | 查询中若包含任何复杂的子部分，最外层查询则被标记为Primary |
| DERIVED | 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里 |
| SUBQUERY | 在SELECT或WHERE列表中包含了子查询 |
| DEPENDENT SUBQUERY | 在SELECT或WHERE列表中包含了子查询,子查询基于外层 |
| UNCACHEABLE SUBQUREY | 无法被缓存的子查询 |
| UNION | 若第二个SELECT出现在UNION之后，则被标记为UNION,若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED |
| UNION RESULT | 从UNION表获取结果的SELECT |
- 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
+ table
- 显示这一行的数据是关于哪张表的
+ type
- 
- type显示的是访问类型，是较为重要的一个指标，结果值**从最好到最坏**依次是：
system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range(尽量保证) > index > ALL
> system>const>eq\_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref
- 显示查询使用了何种类型，**从最好到最差**依次是：system>const>eq\_ref>ref>range>index>ALL
- | 类型 | 解释说明 |
| --- | --- |
| system | 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计 |
| const | 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量 |
| eq\_ref | 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 |
| ref | 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 |
| range | 只**检索给定范围**的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引 |
| index | Full Index Scan，index与ALL区别为**index类型只遍历索引树**。这通常比ALL快，因为索引文件通常比数据文件小。（**也就是说虽然all和Index都是读全表**，但index是从索引中读取的，而all是从硬盘中读的） |
| all | Full Table Scan，将遍历全表以找到匹配的行 |
| | |
> 备注：一般来说，得**保证查询至少达到range级别**，最好能达到ref
+ possible\_key(理论上)
- 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，**但不一定被查询实际使用**
+ key
- **实际使用的索引**。如果为NULL，则没有使用索引
- **查询中若使用了覆盖索引，则该索引和查询的select字段重叠**
+ key\_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度**越短越好**
- key\_len显示的值为索引字段的最大可能长度，**并非实际使用长度**，即key\_len是根据表定义计算而得，不是通过表内检索出的
- 
- **同样的查询结果，key\_len 越小越好**
+ ref
- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
+ rows
- rows列显示MySQL认为它执行查询时必须检查的行数
- 越少越好
+ Extra
- 包含不适合在其他列中显示但**十分重要的额外信息**
| 字段 | 解释说明 |
| --- | --- |
| Using filesort | 说明mysql会对数据使用一个外部的索引排序，而**不是按照表内的索引顺序进行**读取，MySQL中无法利用索引完成的排序操作称为“文件排序” |
| Using temporary | 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 |
| USING index | 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找 **覆盖索引(Covering Index)**：查询字段和索引字段重合 |
| Using where | 表明使用了where过滤 |
| using join buffer | 使用了连接缓存 |
| impossible where | where子句的值总是false，不能用来获取任何元组 |
| select tables optimized away | 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(\*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化 |
| distinct | 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作 |
- Using filesort
- Using temporary
- Using index和Using where
- 案例Case（*）
+ 描述执行顺序------id:4-3-2-1-null
- 第一行（执行顺序4）：id列为1，表示union里的第一个select，select\_type列的**primary表示该查询为外层查询**，table列被标记为,表示查询结果来自一个衍生表，其中derived3中代表该查询衍生自第三个select查询，即**id为3的select** 【select d1.name......】
- 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分，因查询包含在from中，所以为derived【select id，name from t1 where other\_column=""】
- 第三行（执行顺序3）：select列表中的子查询select\_type为subquery，为整个查询中的第二个select【select id from t3】
- 第四行（执行顺序1）：select\_type为union，说明第四个select是union里的第二个select，最先执行【select name，id from t2】
- 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作【两个结果union操作】本文转载自: 掘金