mysql 多列聚集索引 mysql多列索引原理

索引
定义：索引（Index） 是帮助MySQL高效获取数据的数据结构。
索引提高查询速度，降低了增删改的速度，
索引分类
单列索引：普通索引(key)、唯一索引(uniquekey)、主键索引（primary key）、全文索引(full
text)
多列索引：create table duolie (xing char(2),ming char(10),key xm(xing,ming));
作用区别：
创建索引
创建表时 key name(name), unique key email(email)，primary key ()
alter table 表名 add index/unique xm(xing,xing);
alter table 表名 add primary key();
可以设置索引长度 如 key email(email(10));
查看索引
show index from tableName;
删除索引
drop index 索引名 on表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 drop primary key
如

create table t15 (
name char(10),
email char(20),
key name(name),
unique key email (email)
)

使用联合索引注意：

最左前缀：组合索引遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引组合中的字段可以是(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成最左面的前缀原则，那么就不会用索引，比如，age或者（name，age）组合就不会使用索引查询。

优点：

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。

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

被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。

如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势：

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

哪些情况需要建索引：

1 主键，唯一索引

2 经常用作查询条件的字段需要创建索引

3 经常需要排序、分组和统计的字段需要建立索引

4 查询中与其他表关联的字段，外键关系建立索引

哪些情况不要建索引：

1 表的记录太少，百万级以下的数据不需要创建索引

2 经常增删改的表不需要创建索引

3 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。

4 频发更新的字段不适合创建索引

5 where条件里用不到的字段不需要创建索引

索引底层原理

流程图

1. 客户端发送一条查询给服务器；
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器段进行SQL解析、预处理，在优化器生成对应的执行计划；
4. mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端。
   存储引擎
   存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
   存储引擎的类型及特点

引擎名称	优点	缺陷	应用场景
MyISAM	独立于操作系统，这说明可以轻松地将其从Windows服务器移植到Linux服务器	不支持事务/行级锁/外键约束	适合管理邮件或Web服务器日志数据
InnoDB	健壮的事务型存储引擎；支持事务/行级锁/外键约束自动灾难恢复/AUTO_INCREMENT	需要事务支持，并且有较高的并发读取频率
MEMORY	为得到最快的响应时间，采用的逻辑存储介质是系统内存	当mysqld守护进程崩溃时，所有的Memory数据都会丢失；不能使用BLOB和TEXT这样的长度可变的数据类型	临时表
MERGE	是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表		常应用于日志和数据仓库
ARCHIVE	归档的意思，支持索引，拥有很好的压缩机制	仅支持插入和查询功能	经常被用来当做仓库使用

查看数据库可以支持的存储引擎

show engines

mysql默认引擎

　　mysql-5.1版本之前默认引擎是MyISAM，之后是innoDB

2、关系

　　MyISAM是非集聚引擎，支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.

　　innoDB是集聚引擎，5.6以后才有全文索引;支持事务；它是行级锁;不会保存表的具体行数.

MyISAM引擎

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

MyISAM的索引方式也叫做“非聚集”索引

InnoDB引擎

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

为什么InnoDB必须有主键，并且推荐使用整型自增主键？

如果使用非自增主键（如果身份证号或学号等）由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，导致出现节点分裂，平衡书增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页。

索引的两大类型hash与btree

#我们可以在创建上述索引的时候，为其指定索引类型，分两类

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

B+Tree 在 B-Tree 的基础上有两点变化：

数据是存在叶子节点中的；

数据节点之间是有指针指向的。

如何查看MySQL数据文件存储文件位置
方式一：show global variables like “%datadir%”;
方式二：show variables like “%char%”;
Mysql索引优化
Explain优化查询检测
explain中每个列中的信息
id: id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type: SIMPLE – 查询类型（简单查询、联合查询、子查询） primary：复杂查询中最外层的 select3）subquery：包含在 select 中的子查询（不在 from 子句中）
4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为
派生表（derived的英文含义）
table: user – 显示这一行的数据是关于哪张表的 。
type: range – 区间索引（在小于1990/2/2区间的数据)，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中，ALL代表扫描了全表才确定结果。一般来说，得保证查询至少达到range级别,最好能达到ref。
const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show
warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多
有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为
system

explain select * from (select * from film where id = 1) temp

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合

条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种

type。

explain select * from film_actor left join film on film_actor.film_id = film.id;

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要

和某个值相比较，可能会找到多个符合条件的行

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定

范围的行。

index：扫描全表索引，这通常比ALL快一些

ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

possible_keys: birthday – 指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。
key: birthday – 实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。
key_len: 4 – 最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。
key_len计算规则如下：
字符串char(n)：
n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半
部分的字符提取出来做索引。
ref: const – 显示哪个字段或常数与key一起被使用。
rows: 1 – 这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。
Extra: Using where; Using index – 执行状态说明，这里可以看到的坏的例子是Using temporary和Using

索引使用

1. 全值匹配
2. 最左前缀法则
3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转 向全表扫描
4. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
5. like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作
6. is null,is not null 也无法使用索引
7. 字符串不加单引号索引会失效
8. 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、
   表大小等多个因素整体评估是否使用索引，详见范围查询优化
9. 如果mysql觉得全表扫描更快时（数据少）

索引覆盖
　　索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
常见失sql优化

1. Order by与Group by优化
   
   因为索引的创建顺序为
   name,age,position，但是排序的时候age和position颠倒位置了。
   
   虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的
   排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
   Order by与Group by优化优化总结：
   1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
   效率高，filesort效率低。
   2、order by满足两种情况会使用Using index。
2. order by语句使用索引最左前列。
3. 使用where子句与order by子句条件列组合满足索引最左前列。
   3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
   4、如果order by的条件不在索引列上，就会产生Using filesort。
   5、能用覆盖索引尽量用覆盖索引
   6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
   by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
   的限定条件就不要去having限定了。
   排序
   MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低
   filesort文件排序方式
   单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者<sort_key,packed_additional_fields >
   **双路排序（又叫回表排序模式）：**是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
   我们先看单路排序的详细过程：
4. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
5. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
6. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
7. 重复步骤 2、3 直到不满足 name = ‘zhuge’
8. 对 sort_buffer 中的数据按照字段 position 进行排序
9. 返回结果给客户端

我们再看下双路排序的详细过程：

1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
4. 重复 3、4 直到不满足 name = ‘zhuge’
5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

分页查询优化

select * from employees limit 10000,10;

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。
优化
1、根据自增且连续的主键排序的分页查询

select * from employees where id > 90000 limit 5;

表示通过主键排序。我们再看表 employees ，因
为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的前五行数据，
条件

1. 主键自增且连续
2. 结果是按照主键排序的
   2、根据非主键字段排序的分页查询

select * from employees order by name limit 90000,5;

扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引

select * from employees e inner join (select id from employees order by name limit 90000,5) ed
on e.id = ed.id;

其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录.
Join 关联查询优化
mysql的表关联常见有两种算法

1. Nested-Loop Join 算法
2. Block Nested-Loop Join 算法
   关联字段加索引，让mysql做join操作时尽量选择NLJ算法
   小标驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去
   mysql优化器自己判断的时间

in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)
#等价于：
for(select id from B){
select * from A where A.id = B.id
}

exists：当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)
 #等价于:
for(select * from A){
select * from B where B.id = A.id
}

#A表与B表的ID字段应建立索引
count(*)查询优化
临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;
mysql> EXPLAIN select count(1) from employees;
mysql> EXPLAIN select count(id) from employees;
mysql> EXPLAIN select count(name) from employees;
mysql> EXPLAIN select count(*) from employees;
四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count不会统计字段为null值的数

锁及事务
锁是计算机协调多个进程或线程并发访问某一资源的机制。数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素
锁的分类
从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲
突的概率最高，并发度最低
手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
unlock tables;
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改
操作前,会自动给涉及的表加写锁。
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁
冲突的概率最低，并发度最高。
InnoDB与MYISAM的最大不同有两点：

1. 支持事务（TRANSACTION）
2. 支持行级锁
   Mysql事务
   事务（Transaction）及其ACID属性
   事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为
   事务的ACID属性。
   原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执
   行,要么全都不执行。
   一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意
   味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束
   时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
   隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并
   发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是
   不可见的,反之亦然。
   持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系
   统故障也能够保持

并发事务处理带来的问题

脏读（Dirty Reads）所谓脏读，就是指事务A读到了事务B还没有提交的数据，

不可重读（Non-Repeatable Reads）所谓不可重复读，就是指在一个事务里面读取了两次某个数据，读出来的数据不一致。

幻读（Phantom Reads）所谓幻读，就是指在一个事务里面的操作中发现了未被操作的数据。

事务隔离级别

Mysql默认采用可重复读隔离级别

如何解决幻读

间隙锁在某些情况下可以解决幻读问题

要避免幻读可以用间隙锁在Session_1下面执行update account set name =‘zhuge’ where id > 10 and id <=20;，则其他Session没法在这个范围所包含的间隙里插入或修改任何数据无索引行锁会升级为表锁：锁主要是加在索引上，如果对非索引字段更新, 行锁可能会变表锁

session1执行：

update account set balance = 800 where name = ‘lilei’;

session2对该表任一行操作都会阻塞住

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

MVCC(多版本控制)
MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。
MVCC 使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写者写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。
当一个 MVCC 数据库需要更一个一条数据记录的时候，它不会直接用新数据覆盖旧数据，而是将旧数据标记为过时（obsolete）并在别处增加新版本的数据。这样就会有存储多个版本的数据，但是只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据，即使这些在读的过程中半路被别人修改、删除了，也对先前正在读的用户没有影响。这种多版本的方式避免了填充删除操作在内存和磁盘存储结构造成的空洞的开销，但是需要系统周期性整理（sweep through）以真实删除老的、过时的数据。对于面向文档的数据库（Document-oriented database，也即半结构化数据库）来说，这种方式允许系统将整个文档写到磁盘的一块连续区域上，当需要更新的时候，直接重写一个版本，而不是对文档的某些比特位、分片切除，或者维护一个链式的、非连续的数据库结构。
MVCC就是用同一份数据临时保留多版本的方式 的方式，实现并发控制
对于删除操作，mysql底层会记录好被删除的数据行的删除事务id，对于更新操作
mysql底层会新增一行相同数据并记录好对应的创建事务id
在id为12的事务里执行查询操作mysql底层会带上过滤条件，创建事务id <=
max(当前事务id(12)，快照点已提交最大事务id），删除事务id> max(当前事
务id(12)，快照点已提交最大事务id）