数据库中的索引

我会带着你远行 2023-08-17 15:25 59阅读 0赞

数据库操作(四)  
1.索引原理  
1.为什么要有索引?

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。  
2.什么是索引?

索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。  
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。  
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。  
3.对索引的正确理解

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。  
索引的影响:①在表中有大量数据的前提下,创建索引速度会很慢②在索引创建完毕后,对表的查询性能会大幅度提升,但是写性能会降低  
索引原理:

通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。  
4.磁盘IO与预读

简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

img

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

5.索引的数据结构

树

树状图是一种数据结构，它是由n（n>=1）个有限结点组成一个具有层次关系的集合。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。

它具有以下的特点：每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树

img

根结点 : A

父节点 : A是B,C的父节点

叶子节点：D,E是叶子节点

树的深度/树的高度：高度为3

B+树

前面讲了索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。

img

b+树性质  
1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。  
2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

6.聚集索引与辅助索引

聚集索引与辅助索引相同的是:不管是聚集索引还是辅助索引,其内部都是B+树的形式,即高度是平衡的,叶子结点存放着所有的数据

聚集索引与辅助索引不同的是:叶子结点存放的是否是一整行的信息

聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。  
而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。  
聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。

如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。  
在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。  
此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。  
聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

聚集索引与非聚集索引的区别

聚集索引  
1.纪录的索引顺序与无力顺序相同  
因此更适合between and和order by操作  
2.叶子结点直接对应数据  
从中间级的索引页的索引行直接对应数据页  
3.每张表只能创建一个聚集索引

非聚集索引  
1.索引顺序和物理顺序无关  
2.叶子结点不直接指向数据页  
3.每张表可以有多个非聚集索引，需要更多磁盘和内容  
多个索引会影响insert和update的速度  
2.MySQL索引管理  
1.功能

1.  索引的功能就是加速查找
2.  mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能  
    2.MySQL常用的索引

普通索引INDEX：加速查找

唯一索引：  
\-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）  
\-唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：  
\-PRIMARY KEY(id,name):联合主键索引  
\-UNIQUE(id,name):联合唯一索引  
\-INDEX(id,name):联合普通索引  
1.各个索引的应用场景

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表  
有下列字段：  
会员编号 INT  
会员姓名 VARCHAR(10)  
会员身份证号码 VARCHAR(18)  
会员电话 VARCHAR(10)  
会员住址 VARCHAR(50)  
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY  
会员姓名 如果要建索引的话，那么就是普通的 INDEX  
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

# 除此之外还有全文索引，即FULLTEXT #

会员备注信息,如果需要建索引的话，可以选择全文搜索。  
用于搜索很长一篇文章的时候，效果最好。  
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。  
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

# 其他的如空间索引SPATIAL，了解即可，几乎不用 #

2.索引的两大类型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 等索引；  
3.创建/删除索引的语法

方法一：创建表时  
　　CREATE TABLE 表名 (  
字段名1 数据类型 \[完整性约束条件…\],  
字段名2 数据类型 \[完整性约束条件…\],  
\[UNIQUE | FULLTEXT | SPATIAL \] INDEX | KEY  
\[索引名\] (字段名\[(长度)\] \[ASC |DESC\])  
);

方法二：CREATE在已存在的表上创建索引  
CREATE \[UNIQUE | FULLTEXT | SPATIAL \] INDEX 索引名  
ON 表名 (字段名\[(长度)\] \[ASC |DESC\]) ;

方法三：ALTER TABLE在已存在的表上创建索引  
ALTER TABLE 表名 ADD \[UNIQUE | FULLTEXT | SPATIAL \] INDEX  
索引名 (字段名\[(长度)\] \[ASC |DESC\]) ;

删除索引：DROP INDEX 索引名 ON 表名字;

举例:

# 方式一 #

create table t1(  
id int,  
name char,  
age int,  
sex enum('male','female'),  
unique key uni\_id(id),  
index ix\_name(name) \#index没有key  
);  
create table t1(  
id int,  
name char,  
age int,  
sex enum('male','female'),  
unique key uni\_id(id),  
index(name) \#index没有key  
);

# 方式二 #

create index ix\_age on t1(age);

# 方式三 #

alter table t1 add index ix\_sex(sex);  
alter table t1 add index(sex);

# 查看 #

mysql> show create table t1;  
| t1 | CREATE TABLE `t1` (  
`id` int(11) DEFAULT NULL,  
`name` char(1) DEFAULT NULL,  
`age` int(11) DEFAULT NULL,  
`sex` enum('male','female') DEFAULT NULL,  
UNIQUE KEY `uni_id` (`id`),  
KEY `ix_name` (`name`),  
KEY `ix_age` (`age`),  
KEY `ix_sex` (`sex`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
测试索引

1.准备

数据准备:

# 1. 准备表 #

create table s1(  
id int,  
name varchar(20),  
gender char(6),  
email varchar(50)  
);

# 2. 创建存储过程，实现批量插入记录 #

delimiter $$ \#声明存储过程的结束符号为$$  
create procedure auto\_insert1()  
BEGIN  
declare i int default 1;  
while(i<3000000)do  
insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));  
set i=i+1;  
end while;  
END$$ \#$$结束  
delimiter ; \#重新声明分号为结束符号

# 3. 查看存储过程 #

show create procedure auto\_insert1\\G

# 4. 调用存储过程 #

call auto\_insert1();  
2.在没有索引的前提下测试查询速度

# 无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢 #

mysql> select \* from s1 where id=333333333;  
Empty set (0.33 sec)  
3.在表中已经存在大量数据的前提下,为某个字段建立索引,建立速度会很慢

mysql>create index a on s1(id);  
Query OK,0 rows affected(5.30 sec)  
Records:0 Duplicates:0 Warnings:0  
4.在索引建立完毕后,以该字段为查询条件时,查询速度提升明显

mysql>select \* from s1 where id = 333333333;  
Empty set(0.00 sec) \#提速效果明显

ps:  
1.mysql先去索引表根据B+树的搜索原理很快搜索到id=333333333的记录不存在,IO大大降低,因而速度明显提升  
2.可以去mysql的data目录下找到该表,可以看到占用的硬盘空间变多了  
3.mysql> select \* from s1 where email = 'xxxx';  
Empty set(0.34 sec)  
没有为email加索引,因而以该字段为查询条件,速度依然很慢

5.总结

1.  一定是为搜索条件的字段创建索引，比如select \* from s1 where id = 333;就需要为id加上索引
2.  在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快  
    比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。  
    建完以后，再查询就会很快了。
3.  需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。  
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.  
6.正确使用索引

创建索引但是无法命中索引,在添加索引时必须注意以下问题

1.范围问题,或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、

举例:  
id = 1000 明确指定要找1000这个id,在索引树中可以快速找到  
id > 1000 也会利用索引树,但是指定的范围过大,mysql会挨个到搜索树中搜索,跟全表扫描没多大区别  
id > 1000 and id < 2000 范围很小,查询速度仍然很快  
id != 1000 范围很大,所以速度也会很慢  
id between 1 and 300000 范围大,查询速度很慢  
id between 1 and 3 范围小,查询速度很快  
email = 'xxxx' 没有给email字段添加索引,查询速度慢,添加索引后很快  
email like 'xxxx' like指定的是一个明确的值,速度依然很快  
email like 'xx%' 通配符在末尾,查询速度依然很快  
email like '%xx' 通配符在开头,查询速度慢

2.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

举例:  
select count(\*) from s1 where name = 'xxx';

# 没有为name添加索引,查询速度很慢 #

添加索引:create index b on s1(name);  
select count(\*) from s1 where name = 'xxx';

# 添加索引后,查询速度变快 #

select count(\*) from s1 where name = 'egon';

# 查询速度变慢,因为无法从树的某个位置得到一个明确的范围,需要类似全表扫描. #

3.索引列不能在条件中参与计算，保持列“干净”，比如from\_unixtime(create\_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create\_time = unix\_timestamp(’2014-05-29’)

select count(\*) from s1 where id = 3000;

# id字段有索引所以查询速度很快 #

select count(*) from s1 where id*3 = 3000;

# 索引字段id参与了计算,无法拿到一个明确的值去索引树中查找,每次都得临时计算以下,所以速度变慢 #

4.and/or

①and与or的逻辑  
条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立  
条件1 or 条件2:只要有一个条件成立则最终结果就成立

②and的工作原理  
条件：  
a = 10 and b = 'xxx' and c > 3 and d =4  
索引：  
制作联合索引(d,a,b,c)  
工作原理:  
对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

③or的工作原理  
条件：  
a = 10 or b = 'xxx' or c > 3 or d =4  
索引：  
制作联合索引(d,a,b,c)

工作原理:
        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

5.最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

6.其他情况

*  使用函数  
    select \* from tb1 where reverse(email) = 'egon';
 *  类型不一致  
    如果列是字符串类型，传入条件是必须用引号引起来，不然...  
    select \* from tb1 where email = 999;

# 排序条件为索引，则select字段必须也是索引字段，否则无法命中 #

*  order by  
    select name from s1 order by email desc;  
    当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢  
    select email from s1 order by email desc;  
    特别的：如果对主键排序，则还是速度很快：  
    select \* from tb1 order by nid desc;
 *  组合索引最左前缀  
    如果组合索引为：(name,email)  
    name and email -- 命中索引  
    name -- 命中索引  
    email -- 未命中索引
 *  count(1)或count(列)代替count(\*)在mysql中没有差别了
 *  create index xxxx on tb(title(19)) \#text类型，必须制定长度

其他注意事项:

*  避免使用select \*
 *  使用count(\*)
 *  创建表时尽量使用 char 代替 varchar
 *  表的字段顺序固定长度的字段优先
 *  组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
 *  尽量使用短索引
 *  使用连接（JOIN）来代替子查询(Sub-Queries)
 *  连表时注意条件类型需一致
 *  索引散列值（重复少）不适合建索引，例：性别不适合  
    3.联合索引与覆盖索引  
    联合索引

联合索引是指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于有多个索引列，如下

mysql> create table t(  
\-> a int,  
\-> b int,  
\-> primary key(a),  
\-> key idx\_a\_b(a,b)  
\-> );

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引就是一棵B+树，不同的是联合索引的键值得数量不是1，而是>=2。接着来讨论两个整型列组成的联合索引，假定两个键值得名称分别为a、b如图

img

可以看到这与我们之前看到的单个键的B+树并没有什么不同，键值都是排序的，通过叶子结点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1,1），（1,2），（2,1），（2,4），（3,1），（3,2），数据按（a,b）的顺序进行了存放。

因此，对于查询select \* from table where a=xxx and b=xxx, 显然是可以使用(a,b) 这个联合索引的，对于单个列a的查询select \* from table where a=xxx,也是可以使用（a,b）这个索引的。

但对于b列的查询select \* from table where b=xxx,则不可以使用（a,b） 索引，其实你不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b) 索引

联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了，

覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性

对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，...,key1，key2，...）.

覆盖索引的另外一个好处是对某些统计问题而言的。innodb存储引擎并不会选择通过查询聚集索引来进行统计.

对于（a,b）形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引

查询优化命令 -- explain

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

执行计划：让mysql预估执行操作(一般正确)  
all < index < range < index\_merge < ref\_or\_null < ref < eq\_ref < system/const  
id,email

慢：
        select * from userinfo3 where name='alex'
        
        explain select * from userinfo3 where name='alex'
        type: ALL(全表扫描)
            select * from userinfo3 limit 1;
    快：
        select * from userinfo3 where email='alex'
        type: const(走索引)

4.索引操作  
普通索引\\聚集索引(主键)\\唯一索引(unique)

索引操作:

添加主键索引:  
创建的时候添加: 添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加.

聚集索引的添加方式  
创建的时候添加:  
create table t1(id int primary key,);  
create table t1(id int,primary key(id));

表创建完了之后添加:  
alter table 表名 add primary key(id)  
删除主键索引:  
alter table 表名 drop primary key;

唯一索引:  
Create table t1(id int unique);  
Create table t1(id int,unique key uni\_name (id));

表创建好之后添加唯一索引:  
alter table s1 add unique key u\_name(id);  
删除:  
alter table s1 drop index u\_name;

普通索引:  
创建:  
Create table t1(id int,index index\_name(id));  
表创建后添加:  
alter table s1 add index index\_name(id);  
create index index\_name on s1(id);

删除:  
alter table s1 drop index u\_name;  
drop index 索引名 on 表名字;

联合索引(联合主键\\联合唯一\\联合普通索引)  
create table t1(Id int,  
name char(10),  
index index\_name(id,name)  
);  
5.事务  
事务是由一组SQL语句组成的逻辑处理单元，事务具有ACID属性。

原子性（Atomicity）：事务是一个原子操作单元。原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。

一致性（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。

隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。

持久性（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

事务常见问题:

更新丢失（Lost Update）  
　　原因：当多个事务选择同一行操作，并且都是基于最初选定的值，由于每个事务都不知道其他事务的存在，就会发生更新覆盖的问题。类比github提交冲突。

脏读（Dirty Reads）  
　　原因：事务A读取了事务B已经修改但尚未提交的数据。若事务B回滚数据，事务A的数据存在不一致性的问题。

不可重复读（Non-Repeatable Reads）  
　　原因：事务A第一次读取最初数据，第二次读取事务B已经提交的修改或删除数据。导致两次读取数据不一致。不符合事务的隔离性。

幻读（Phantom Reads）  
　　原因：事务A根据相同条件第二次查询到事务B提交的新增数据，两次数据结果集不一致。不符合事务的隔离性。

幻读和脏读有点类似  
脏读是事务B里面修改了数据，  
幻读是事务B里面新增了数据。

事务用于将某些操作的多个SQL作为原子性操作，也就是这些sql语句要么同时成功，要么都不成功，事务的其他特性在我第一篇博客关于事务的介绍里面有，这里就不多做介绍啦，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

简单来说：给别人转账，他收到了200，你的账户上扣了200，这两个操作是不是两个sql语句，这两个sql语句是你的应用程序发给mysql服务端的，并且这两个sql语句都要一起执行，不然数据就错了.并且如果你通过应用程序发送这两条sql的时候，由于网络问题，你只发送了一个sql过来，那只有一个账户改了数据，另外一个没改，那数据就出错了。这就是事务要完成的事情。

举例:

create table user(  
id int primary key auto\_increment,  
name char(32),  
balance int  
);

insert into user(name,balance)  
values  
('wsb',1000),  
('chao',1000),  
('ysb',1000);

# 原子操作 #

start transaction;  
update user set balance=900 where name='wsb'; \#买支付100元  
update user set balance=1010 where name='chao'; \#中介拿走10元  
update user set balance=1090 where name='ysb'; \#卖家拿到90元  
commit; \#只要不进行commit操作，就没有保存下来，没有刷到硬盘上

# 出现异常，回滚到初始状态 #

start transaction;  
update user set balance=900 where name='wsb'; \#买支付100元  
update user set balance=1010 where name='chao'; \#中介拿走10元  
uppdate user set balance=1090 where name='ysb'; \#卖家拿到90元,出现异常没有拿到  
rollback; \#如果上面三个sql语句出现了异常，就直接rollback，数据就直接回到原来的状态了。但是执行了commit之后，rollback这个操作就没法回滚了

# 我们要做的是检测这几个sql语句是否异常，没有异常直接commit，有异常就rollback，但是现在单纯的只是开启了事务，但是还没有说如何检测异常，我们先来一个存储过程来捕获异常，等我们学了存储过程，再细说存储过程。 #

commit;

# 通过存储过程来捕获异常：(shit！，写存储过程的是，注意每一行都不要缩进！！！按照下面的缩进来写，居然让我翻车了！！！我记住你了~~~)，我的代码直接黏贴就能用。 #

delimiter //  
create PROCEDURE p5()  
BEGIN  
DECLARE exit handler for sqlexception  
BEGIN  
rollback;  
END;

START TRANSACTION;  
update user set balance=900 where name='wsb'; \#买支付100元  
update user set balance=1010 where name='chao'; \#中介拿走10元

# update user2 set balance=1090 where name='ysb'; \#卖家拿到90元 #

update user set balance=1090 where name='ysb'; \#卖家拿到90元  
COMMIT;

END //  
delimiter ;

mysql> select \* from user;  
\+----+------+---------+  
| id | name | balance |  
\+----+------+---------+  
| 1 | wsb | 1000 |  
| 2 | chao | 1000 |  
| 3 | ysb | 1000 |  
\+----+------+---------+  
3 rows in set (0.00 sec)  
6.数据备份与还原

1.  物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
2.  逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
3.  导出表： 将表导入到文本文件中。  
    使用mysqldump实现备份

# 语法： #

mysqldump -u用户名 -p密码 -B -d 库名>保存路径(G:\\sql\\t1.sql)

使用mysqldump实现还原

# 语法： #

mysql -u用户名 -p密码 <保存路径(G:\\sql\\t1.sql)

\-B参数导出的文件中自带创建数据库和连接数据库的功能：(使用-B参数备份出来的内容自带create database 库名和use 库名的功能)

执行备份语句的时候，其中可以加上很多的参数，用来添加一些备份的时候的特殊要求的，其中有一个-B参数，执行备份语句时，如果加上了-B参数，那么将来再执行数据还原的时候，就不需要自己到数据库里面去先创建同名的这个库了，并且执行数据还原语句的时候就不需要指定这个库了，如果没有加-B参数，就需要自行到数据库中先创建一个同名库，并且执行语句是要指定将数据恢复到这个库里面

mysqldump的关键参数说明：  
　　1.-B指定多个库，增加建库语句和use 语句  
　　2.--compact 去掉注释，适合调试输出，生产上不用  
　　3.-A或者--all-databases  
　　　　例如：C:\\WINDOWS\\system32>mysqldump -uroot -p -B -A> f:\\数据库备份练习\\all.sql  
　　　　　　 Enter password: \*\*\*

4.-F刷新binlog日志(binlog具体是什么，后面咱们再解释)  
　　5.--master-data 增加binlog日志文件名及对应的为支点。  
　　6.-x，--lock-all-tables 将所有的表锁住，一般mysql引擎都是锁表，全部都不能使用了  
　　7.--add-locks这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作(mysql默认是加上的)  
　　8.-l，--lock-tables Lock all tables for read  
　　9.-d 只备份表结构  
　　10.-t 只备份数据  
　　11. --single-transaction 开启事务，适合innodb事务数据库备份  
　　　　InnoDB表在备份时，通常启用选项--single-transaction来保证备份的一致性，实际上他的工作原理时设定本次会话的隔离界别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了数据。

MyISAM全库备份指令推荐：(gzip是压缩文件为zip类型的)  
　mysqldump -uroot -p666 -A -B --master-data=2 -x|gzip>f:\\数据库备份练习\\all.sql.gz  
InnoDB全库备份指令推荐：  
　mysqldump -uroot -p666 -A -B --master-data=2 --single-transaction|gzip>f:\\数据库备份练习\\all.sql.gz  
7.锁

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

表级锁定

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。  
 当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。  
　使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

行级锁定

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。  
　虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。  
　使用行级锁定的主要是InnoDB存储引擎。

页级锁定

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。  
　在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。  
　使用页级锁定的主要是BerkeleyDB存储引擎。

总的来说，MySQL这3种锁的特性可大致归纳如下：  
　　表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；  
　　行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；  
　　页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。  
　适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

表级锁定(以myisam为例)操作

表锁，读锁会阻塞写，不会阻塞读。而写锁则会把读写都阻塞。

显示加锁：  
　　共享读锁：lock table tableName read;  
　　独占写锁：lock table tableName write;

同时加多锁：lock table t1 write,t2 read;  
　　批量解锁：unlock tables;

行级锁定(以Innodb为例)

共享锁: select 字段列表 from 表名 where 条件 lock in share mode;

排他锁: select 字段列表 from 表名 where 条件 for update;

转载于:https://www.cnblogs.com/hualibokeyuan/p/11468519.html