索引
索引，类似书籍的目录，可以根据目录的某个页码立即找到对应的内容。
索引的优点：1. 天生排序。2. 快速查找。
索引的缺点：1. 占用空间。2. 降低更新表的速度。
注意点：小表使用全表扫描更快，中大表才使用索引。超级大表索引基本无效。
索引从实现上说，分成 2 种：聚集索引和辅助索引（也叫二级索引或者非聚集索引）
从功能上说，分为 6 种：普通索引，唯一索引，主键索引，复合索引，外键索引，全文索引。
详细说说 6 种索引：
1、普通索引：最基本的索引，没有任何约束。
2、唯一索引：与普通索引类似，但具有唯一性约束。
3、主键索引：特殊的唯一索引，不允许有空值。
4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎（ES，Solr）。
注意：主键就是唯一索引，但是唯一索引不一定是主键，唯一索引可以为空，但是空值只能有一个，主键不能为空。
另外，InnoDB 通过主键聚簇数据，如果没有定义主键且没有定义聚集索引， MySql 会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个 6 字节的主键作为聚簇索引，用户不能查看或访问。
简单点说：
- 设置主键时，会自动生成一个唯一索引，如果之前没有聚集索引，那么主键就是聚集索引。
- 没有设置主键时，会选择一个不为空的唯一索引作为聚集索引，如果还没有，那就生成一个隐式的 6 字节的索引。
MySql 将数据按照页来存储，默认一页为 16kb，当你在查询时，不会只加载某一条数据，而是将这个数据所在的页都加载到 pageCache 中，这个其实和 OS 的就近访问原理类似。
MySql 的索引使用 B+ 树结构。在说 B+ 树之前，先说说 B 树，B 树是一个多路平衡查找树，相较于普通的二叉树，不会发生极度不平衡的状况，同时也是多路的。
B 树的特点是：他会将数据也保存在非页子节点。
看图可知：
而这个特点会导致非页子节点不能存储大量的索引。
而 B+ Tree 就是针对这个对 B tree 做了优化。如下图所示：
我们看到，B+ Tree 将所有的 data 数据都保存到了叶子节点中，非也子节点只保存索引和指针。
我们假设一个非页子节点是 16kb，每个索引，即主键是 bigint，即 8b，指针为 8b。那么每页能存储大约 1000 个索引（16kb/ 8b + 8b）.
而一颗 3 层的 B+树能够存储多少索引呢？如下图：
大约能够存储 10 亿个索引。通常 B+ 树的高度在 2-4 层，由于 MySql 在运行时，根节点是常驻内存的，因此每次查找只需要大约 2 -3 次 IO。可以说，B+ 树的设计，就是根据机械磁盘的特性来进行设计的。
知道了索引的设计，我们能够知道另外一些信息：
- MySql 的主键不能太大，如果使用 UUID 这种，将会浪费 B+ 树的非叶子节点。
- MySql 的主键最好是自增的，如果使用 UUID 这种，每次插入都会调整 B+树，从而导致页分裂，严重影响性能。
那么，如果项目中使用了分库分表，我们通常都会需要一个主键进行 sharding，那怎么办呢？在实现上，我们可以保留自增主键，而逻辑主键用来作为唯一索引即可。
锁
2. 锁机制
关于 Mysql 的锁，各种概念就会喷涌而出，事实上，锁有好几种维度，我们来解释一下。
1. 类型维度
共享锁（读锁 / S 锁）
排它锁（写锁 / X 锁）
类型细分：- 意向共享锁
- 意向排他（互斥）锁
- 悲观锁（使用锁，即 for update）
- 乐观锁（使用版本号字段，类似 CAS 机制，即用户自己控制。缺点：并发很高的时候，多了很多无用的重试）
2. 锁的粒度（粒度维度）
- 表锁
- 页锁（Mysql BerkeleyDB 引擎）
- 行锁（InnoDB）
3. 锁的算法（算法维度）
- Record Lock（单行记录）
- Gap Lock（间隙锁，锁定一个范围，但不包含锁定记录）
- Next-Key Lock（Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身， MySql 防止幻读，就是使用此锁实现）
4. 默认的读操作，上锁吗？
- 默认是 MVCC 机制（“一致性非锁定读”）保证 RR 级别的隔离正确性，是不上锁的。
可以选择手动上锁：select xxxx for update (排他锁); select xxxx lock in share mode(共享锁)，称之为“一致性锁定读”。
使用锁之后，就能在 RR 级别下，避免幻读。当然，默认的 MVCC 读，也能避免幻读。
既然 RR 能够防止幻读，那么，SERIALIZABLE 有啥用呢？
防止丢失更新。例如下图：
这个时候，我们必须使用 SERIALIZABLE 级别进行串行读取。
最后，行锁的实现原理就是锁住聚集索引，如果你查询的时候，没有正确地击中索引，MySql 优化器将会抛弃行锁，使用表锁。
事务
事务是数据库永恒不变的话题， ACID：原子性，一致性，隔离性，持久性。
四个特性，最重要的就是一致性。而一致性（（由DBMS的完整性子系统执行测试任务））由原子性，隔离性，持久性来保证。
- 原子性由 Undo log 保证。Undo Log 会保存每次变更之前的记录，从而在发生错误时进行回滚。（由DBMS的事务管理子系统来实现）
- 隔离性由 MVCC 和 Lock 保证。这个后面说。（由DBMS的并发控制子系统来实现）
- 持久性由 Redo Log 保证。每次真正修改数据之前，都会将记录写到 Redo Log 中，只有 Redo Log 写入成功，才会真正的写入到 B+ 树中，如果提交之前断电，就可以通过 Redo Log 恢复记录。（由DBMS的恢复管理子系统来实现）
然后再说隔离性。
隔离级别：
- 未提交读（RU）
- 已提交读（RC）
- 可重复读（RR）
- 串行化（serializable）
每个级别都会解决不同的问题，通常是3 个问题：脏读，不可重复读，幻读。一张经典的图：
这里有个注意点，关于幻读，在数据库规范里，RR 级别会导致幻读，但是，由于 Mysql 的优化，MySql 的 RR 级别不会导致幻读：在使用默认的 select 时，MySql 使用 MVCC 机制保证不会幻读；你也可以使用锁，在使用锁时，例如 for update（X 锁），lock in share mode（S 锁），MySql 会使用 Next-Key Lock 来保证不会发生幻读。前者称为快照读，后者称为当前读。
原理剖析：
- RU 发生脏读的原因：RU 原理是对每个更新语句的行记录进行加锁，而不是对整个事务进行加锁，所以会发生脏读。而 RC 和 RR 会对整个事务加锁。
- RC 不能重复读的原因：RC 每次执行 SQL 语句都会生成一个新的 Read View，每次读到的都是不同的。而 RR 的事务从始至终都是使用同一个 Read View。
- RR 不会发生幻读的原因： 上面说过了。
那 RR 和 Serializble 有什么区别呢？答：丢失更新。本文关于锁的部分已经提到。
MVCC 介绍：全称多版本并发控制。
innoDB 每个聚集索引都有 4 个隐藏字段，分别是主键（RowID），最近更改的事务 ID（MVCC 核心），Undo Log 的指针（隔离核心），索引删除标记（当删除时，不会立即删除，而是打标记，然后异步删除）；
本质上，MVCC 就是用 Undo Log 链表实现。
MVCC 的实现方式：事务以排它锁的方式修改原始数据，把修改前的数据存放于 Undo Log，通过回滚指针与数据关联，如果修改成功，什么都不做，如果修改失败，则恢复 Undo Log 中的数据。
多说一句，通常我们认为 MVCC 是类似乐观锁的方式，即使用版本号，而实际上，innoDB 不是这么实现的。当然，这不影响我们使用 MySql。
事务
事务：指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全都成功，要么全都失败。
开启事务：start transaction;
提交事务：commit;
回滚事务：rollback; //恢复到事务开启之前的状态（也相当于提交了事务）
事务的特性：
原子性：事务的不可分割，组成事务的各个逻辑单元不可分割。
一致性：事务执行的前后，数据完整性保持一致。（总和前后相等）
隔离性：事务执行不应该受到其他事务的干扰。
持久性：事务一旦结束，数据就持久化到数据库中。
如果不考虑隔离性（一个事务执行受到其他的事务的干扰），引发一些安全问题，主要体现在读取数据上：
l 脏读：一个事务读到了另一个事务未提交的数据，导致查询结果不一致
l 不可重复读：一个事务读到了另一个事务已经提交的update的数据，导致多次查询结果不一致。
l 虚读/幻读：一个事务读到了另一个事务已经提交的insert的数据，导致多次查询结果不一致。
设置事务的隔离级别：安全性越高的效率越低，一般使用中间两种
read uncommitted（未提交读）：脏读，不可重复读，虚读都有可能发生
read committed（已提交读）：避免脏读。但是不可重复读和虚读是有可能发生
repeatable read（重复读，默认）：避免脏读和不可重复读，但是虚读有可能发生。
serializable（串行化读（一个事务得接着另一个事务执行，禁止并发存在））：避免脏读，不可重复读，虚读。
设置事务的隔离级别： set session transaction isolation level 隔离级别
查看当前的隔离级别： select @@tx_isolation;
演示脏读：
\1. 开启两个窗口A,B
\2. 设置A窗口的隔离级别为read uncommitted;
\3. 在A，B两个窗口中开启事务
\4. 在B窗口中完成转账的功能，但是不提交事务
\5. 在A窗口中进行查询：查询到已经转账成功了。（这就发生了脏读）
演示不可重复读：
\1. 开启两个窗口A,B
\2. 设置A窗口的隔离级别为read committed;
\3. 分别在两个窗口中开启事务
\4. 在B窗口中完成转账，不提交事务
\5. 在A窗口中进行查询：仍然是未到账（这就避免了脏读）
6. *在B窗口中提交事务*
\7. 在A窗口查询：发现到账了（两次A窗口的查询结果不同，这就是不可重复读）
避免不可重复读：将2中的隔离级别设置为repeatable read；（一个事务中的多次查询结果一致，跳出这个事务才能出现改变后的结果）
（虚读只是概率发生，无法演示）
演示串行化：
\1. 开启两个窗口A,B
\2. 设置A窗口的隔离级别：serializable
\3. 分别在两个窗口中开启事务
\4. 在B窗口中插入一条记录
\5. 在A窗口中进行查询：发现无反应，但是当B窗口提交事务的时候，A窗口会立马输出结果。（说明事务不允许吹袭案并发，A窗口需要等B窗口事务执行完成以后，才会执行A窗口的事务）
视图
解释器explain
1 | +----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+ |
id
select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。一般有三种情况： 第一种：id全部相同，sql的执行顺序是由上至下； 第二种：id全部不同，sql的执行顺序是根据id大的优先执行； 第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。
select_type
select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询
simple：简单的select 查询，查询中不包含子查询或者union
primary：查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery：在select或where 列表中包含了子查询
derived：在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived union
result：从union表获取结果的select
table
显示这一行的数据是关于哪张表的
partitions
表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。
type
这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all
对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
index：（full index scan）全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。
range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。 eq_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，
const：表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可
1 | type中包含的值： |
possible_keys
显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。如果为空，表示没有可能应用的索引
key
显示查询语句实际使用的索引。若为null，则表示没有使用索引。
MySQL很少会选择优化不足的索引，此时可以在SELECT语句中使用FORCE INDEX（index_name）来强制使用一个索引或者用IGNORE INDEX（index_name）来强制忽略索引
key_len
显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。
key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。
ref
显示索引的哪一列或常量被用于查找索引列上的值。
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好。
extra
关于MySQL如何解析查询的额外信息
Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where： 表明使用了where 过滤
Using join buffer： 表明使用了连接缓存
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
1 | Extra中包含的值：using index： 只用到索引,可以避免访问表，性能很高。 |
filtered
一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数
见到Using temporary和Using filesort，就意味着MySQL根本不能使用索引，结果是检索会很慢，需要优化sql了。
MySQL本身的功能架构分为三个部分，分别是 应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。
- 应用层，主要负责与客户端进行交互，建立链接，记住链接状态，返回数据，响应请求，这一层是和客户端打交道的。
- 逻辑层，主要负责查询处理、事务管理等其他数据库功能处理，以查询为例。
首先接收到查询SQL之后，数据库会立即分配一个线程对其进行处理，第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划，然后交由计划执行器来执行。
计划执行器需要访问更底层的事务管理器，存储管理器来操作数据，他们各自的分工各有不同，最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。
- 物理层，实际物理磁盘上存储的文件，主要有分文数据文件，日志文件。
通过上面的描述，生成执行计划是执行一条SQL必不可少的步骤，一条SQL性能的好坏，可以通过查看执行计划很直观的看出来，执行计划提供了各种查询类型与级别，方便我们进行查看以及为作为性能分析的依据。
MySQL为我们提供了 explain 关键字来直观的查看一条SQL的执行计划。
explain显示了MySQL如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。
下面我们使用 explain 做一个查询，如下：
查询结构中有12列，理解每一列的含义，对理解执行计划至关重要，下面进行说明。
id
SELECT识别符，这是SELECT的查询序列号。
select_type
SELECT类型,可以为以下任何一种:
- SIMPLE:简单SELECT(不使用UNION或子查询)
- PRIMARY:最外面的SELECT
- UNION:UNION中的第二个或后面的SELECT语句
- DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
- UNION RESULT:UNION 的结果
- SUBQUERY:子查询中的第一个SELECT
- DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
- DERIVED:导出表的SELECT(FROM子句的子查询)
table
输出的行所引用的表
partitions
如果查询是基于分区表的话，显示查询将访问的分区。
type
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
- system:表仅有一行(=系统表)。这是const联接类型的一个特例。
- const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
- eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
- ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
- ref_or_:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
- index_merge:该联接类型表示使用了索引合并优化方法。
- unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
- index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
- range:只检索给定范围的行,使用一个索引来选择行。
- index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
- ALL:对于每个来自于先前的表的行组合,进行完整的表扫描，说明查询就需要优化了。
一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys
指出MySQL能使用哪个索引在该表中找到行
key
显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len
显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。在不损失精确性的情况下，长度越短越好
ref
显示使用哪个列或常数与key一起从表中选择行。
rows
显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered
显示了通过条件过滤出的行数的百分比估计值。
Extra
该列包含MySQL解决查询的详细信息
- Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
- Select tables optimized away MySQL根本没有遍历表或索引就返回数据了，表示已经优化到不能再优化了
- Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
- range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
- Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行，说明查询就需要优化了。
- Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
- Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果，说明查询就需要优化了。
- Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
- Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
- Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
mysql添加用户并设置权限
1.登录本地用户
命令：[root@elk mysql]# mysql -uroot -p123456
登录外网用户（需要注意服务器可能只允许本地登录，需要修改响应的配置文件）
配置文件是/etc/mysql/my.cnf
命令：vim /etc/mysql/my.cnf
修改bind-address =127.0.0.1 将其注释掉；//作用是使得不再只允许本地访问
重启mysql：/etc/init.d/mysql restart
然后登录
2.创建用户
命令：mysql> CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;
username:用户名；
host：指定在哪个主机上可以登录，本机可用localhost，%通配所有远程主机；
password：用户登录密码；
3.设置权限
命令：GRANT ALL PRIVILEGES ON . TO ‘username’@‘%’ IDENTIFIED BY ‘password’；
刷新权限：FLUSH PRIVILEGES; #刷新之后才会生效，重启也可以
格式：grant 权限 on 库名.表名 to 用户@登录主机 identified by “用户密码”；*.*代表所有权；
@ 后面是访问的是客户端IP地址（或是 主机名） % 代表任意的客户端，如果填写 localhost 为本地访问（那此用户就不能远程访问该mysql数据库了）
一般情况下，修改MySQL密码，授权，是需要有mysql里的root权限的。
注：本操作是在WIN命令提示符下，phpMyAdmin同样适用。
用户：phplamp 用户数据库：phplampDB
1.新建用户。
//登录MYSQL
@>mysql -u root -p
@>密码
//创建用户
mysql> insert into mysql.user(Host,User,Password) values(“localhost”,”phplamp”,password(“1234”));
//刷新系统权限表
mysql>flush privileges;
这样就创建了一个名为：phplamp 密码为：1234 的用户。
然后登录一下。
mysql>exit;
@>mysql -u phplamp -p
@>输入密码
mysql>登录成功
2.为用户授权。
//登录MYSQL(有ROOT权限)。我以ROOT身份登录.
@>mysql -u root -p
@>密码
//首先为用户创建一个数据库(phplampDB)
mysql>create database phplampDB;
/授权phplamp用户拥有phplamp数据库的所有权限。
mysql>grant all privileges on phplampDB. to phplamp@localhost identified by ‘1234’;*
//刷新系统权限表
mysql>flush privileges;
mysql>其它操作
1 | /* 如果想指定部分权限给一用户，可以这样来写: |
3.删除用户。
@>mysql -u root -p
@>密码
mysql>DELETE FROM user WHERE User=”phplamp” and Host=”localhost”;
mysql>flush privileges;
//删除用户的数据库 mysql>drop database phplampDB;
4.修改指定用户密码。
@>mysql -u root -p
@>密码
mysql>update mysql.user set password=password(‘新密码’) where User=”phplamp” and Host=”localhost”;
mysql>flush privileges;