day45 mysql索引终章(数据库数据导出,导入)

数据导出:

导出可以指定数据库的表,

mysqldump -u root -p s8day130db user > a.sql 这里的-p后面就是数据库名，紧接着就是表名 user就是s8day130db数据库里面的表名,这样就把指定表给导出了.

数据导入:

进入mysql控制台,然后use数据库,输入source 文件路径:文件名以这样的格式写出来即可,示例接着上面的导出示例,source D:a.sql 即可完成导入.

我们为什么要用到索引呢,索引到底是什么呢,

下面来解决一下这两个疑问,

我们在建立一个数据库的时候,设计完成后,就会投入使用,然后在使用过程中,我们一般对其进行增加,修改(删除几乎不会用到)内容的操作,使用频率最高的就是查询操作,我们在使用数据库的时候会有大量的复杂的查询方法,那么问题来了,既然要查询,我们要通过什么方式,最高效地查出来结果呢?在数据类型里面我们要查一个数据在列表或者字典或者字符串里面会有下标,会有键对值,这样的话就很轻易可以得到结果,那么我们在数据库里面也可以建立一个类似这样东西来提高我们的效率,他就是索引,索引的存在价值就是提高我们的查询效率,所以我们的查询方法会基于索引来进行优化已达到最高效.

我们的索引是针对查询而言的,如果有改变或者增加操作的时候索引会反而降低效率,所以我们要根据不同的场景来设定相应的索引,不可一概而论.

功能

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

索引的原理:

就像我们要在新华字典或者中英词典里面找到一个数据一样,需要通过目录来以最快的速度得到想要的结果,如果没有目录我们需要遍历整本字典或者书籍才能得到最终结果,查询的次数就太多了,而有了目录后,我们每次查询一个数据的时候都是使用一套固定的方法来寻找,这样就把随机的事件变成了有序的事件,极大地提高了效率,减少了查询的次数,在mysql中我们每一次的查询都是一次io操作,我们要提高查询效率就要尽可能地减少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毫秒的时间，显然是个灾难。

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

浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

我们把真实的数据都存在上图从上往下依次数过来的最下面一层,这一层用来存储真实数据,这里叫做叶子节点,叶子上一层就是树枝,树枝可以有很多层,最上面一层就是树根,也叫做根节点,这幅图就是我们的索引概念图,我们的每次查询都是要从最上面的根节点开始查询,从根节点一步一步细分到下面的树枝部分,最后再到叶子结点找到真实的数据,然后把结果返回,我们索引就是建立在数据的基础上,它本身就是会占用硬盘空间的,就相当于书的目录也占书的页数是一样的.例如我们要找一个100这个数,从上图的根节点开始100>35,所以在它的右边,100>87,继续去右边找,100>99,但是已经到叶子节点了,所以该表格里面根本就没有100这个数据,那么返回结果为空.这就是程序的内部执行顺序,按照这个顺序下来,我们一共找到最终结果查询了3次,效率已经很高了,如果没有索引的话我们需要把叶子结点的数据统统遍历一遍才能得到结果,那样查询的次数会大大的增多,io一多,效率异常低下.我们的数据是从左往右依次按照大小排序的,不论是树枝部分还是叶子结点部分还是根节点部分都是如此,这是索引的特点.需要提一点的是我们的树枝部分和根节点数据都是虚拟的是基于叶子节点的数据衍生出来的带有指向性的数据,指引着你找到最终的叶子结点的数据.

###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的数据了，这个是非常重要的性质，即索引的最左匹配特性。 {我们在建立联合索引的时候所建立的几个索引字段应该遵循从左往右的顺序,依次要符合的条件是要有索引,然后区别性高(即每一行的数据都不一样),然后要能够最大限度的缩小查找范围}这样效率才会最大化

我们要尽可能在叶子节点放更多的数据内容,然后再建立索引的时候要让b+树的层数尽可能的少一些,也就是树的高度尽可能降下来,这样我们查询的时候io就会相应地减少,效率自然就会高

聚集索引:

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

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

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

#由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

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

2、辅助索引

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

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

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

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

我们的辅助索引只是包含一个字段的内容(在单独一个字段作为索引的时候,而不是联合索引的时候)我们通过该字段只能找到该字段里面的值,比如一个表格info里面有id和name以及sex,我们给name建立了辅助索引,id是主键索引,我们执行sql语句select * from info where name='egon'因为name里的索引是辅助索引,我们只能通过它得到name的信息但是无法同时得到该name对应的id以及sex的内容,那么我们的name辅助索引里面的同时会有隐含的主键的地址,我们找到了name对应的值之后还需要通过隐含的主键地址返回到主键中去找一次,通过主键就可以得到该条数据的完整内容,那么如果我们找name的io是三次,最后得到该sql语句的完整内容就是6次io,

索引类型:

普通索引INDEX：加速查找

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

联合索引：
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

View Code

索引的创建和删除固定写法:

#方法一：创建表时
    　　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 表名字;

View Code

在没有索引的前提下,我们要查询一个条件速度会很慢,如果所查询的字段有索引的话会快很多倍,我们在建立索引的时候如果一开始创建一个表格就把索引建立好的话,

那么建立索引就会很快,就好比我们在写一本书之前就已经把目录建立好了,那么我们书的内容就根据目录来填充即可,但是我们的书如果已经写好了,然后没有建立目录,那么

我们在一本已经完成的书里面添加目录的话,可想而知,比起一开始就创建好目录,效率要低很多倍.

1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

索引的正确使用:

我们不是随便的加上索引就可以轻易提高查询效率的,下面我们一一列举出来,具体情况

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

大于号、小于号

比如select * from info where id>2;这句话里面只要满足条件大于2就可以拿出结果,如果我们的info表格里面有几百万的数据,id从1开始依次递增,那么我们的检索范围过于大,就相当于是遍历的整个表格里面的几百万数据,即便不建立索引,也不会有太大的区别,这句sql语句本身就没有可优化的空间,这种情况下,我们的索引并不能解决什么实际问题.

不等于！=

select * from info where id !=2;这里同上,也几乎是遍历了整个表格,因为满足条件的数据实在太多了,范围过于庞大,无法提高效率.

between ...and...

select * from info where id between 1 and100000000000;也是范围的问题

like

select * from info where id like '2'; 这里如果没有给id加上索引的话,效率是很低的,加上索引就会快很多,因为它指定了一个具体的值;如果是

select * from info where id like '2%';这样也很快,但是如果是这样select * from info where id like '%2';的话就会慢很多了,因为它是以%开头的话,所有的内容都符合条件,所以从左往右匹配的原则,就会慢很多.

2 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，

区分度越高,我们的b+树越高度越低,io就会越少,尽量不要使用sex来作为索引,它只有两个选项,重复率太高了,以它建立索引树的高度太高,io次数过多会极大地影响效率

我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）

回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<...

而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，name字段所有的值均为'egon'

#现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？

#1：如果条件是name='xxxx',那么肯定是可以第一时间判断出'xxxx'是不在索引树中的（因为树中所有的值均为'egon’），所以查询速度很快

#2：如果条件正好是name='egon',查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢

分析原因

View Code

select * from info where id *2=890; 这样在得到的结构前进行计算即便有索引也相当于把整个表格遍历了一遍,效率还会很低, 我们可以把写法改变一下,

select * from info where id =445; 这样就可以了,我们不要在查询的时候进行计算,就可以避免类似的情况出现

还有and 和or逻辑运算,在and连接中,我们得到了一个结果为真还不够需要把所有的条件一一过滤一遍才可以.但是在or中我们得到一个结果为真的条件就不会继续过滤了,

select * from info where name='xxx' and sex ='male';这里虽然我们后面的sex满足条件,但是name字段里面根本就没有这个值,所以直接就返回结果,根本不会去检索and后面的

条件.这样得到结果也会很快,

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

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

#3、or的工作原理
    条件：
        a = 10 or b = 'xxx' or c > 3 or d =4
    索引：
        制作联合索引(d,a,b,c)
        
    工作原理:
        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

View Code

select * from info where sex ='male';如果我们没有给sex字段添加索引会很慢,因为它的区分度太低了,

select * from info where sex ='xxx';这样就会很快,因为不满足条件,sex里面根本就没有这个选项,直接就返回结果为空了,

select * from info where name='egon' and sex ='xxx' ;这里我们的mysql优化系统会先检索sex='xxx'这个条件,因为这里的两个条件中,

mysql会从这些条件中找到主键,因为主键的检索是最快的,看看这些条件里有没有主键字段,如果没有那就找有索引的字段,

再没有就找区分度高的字段,再没有就找能最快缩小查找范围的字段,按照这些顺序依次去检索最大限度的提高效率.很显然这句话里面我们的

sex='xxx'是可以立即判断出来没有这条数据存在的,可以立即得到结果所以就不会顾及name条件了.

我们要了解它内部的优化原理然后根据他的原理去更合理地创建索引才会最大化地提高效率

注意事项总结:

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

View Code

创建联合索引:

create index ind_ABC info (id,name);这就是创建联合索引,把几个字段都写入括号里面,就组成了组合索引,它会覆盖id本身的主键索引,也就是说,id索引的功能在这个联合索引里都会包含,这就是覆盖索引,

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

#===========准备表==============
create table buy_log(
    userid int unsigned not null,
    buy_date date
);

insert into buy_log values
(1,'2009-01-01'),
(2,'2009-01-01'),
(3,'2009-01-01'),
(1,'2009-02-01'),
(3,'2009-02-01'),
(1,'2009-03-01'),
(1,'2009-04-01');

alter table buy_log add key(userid);
alter table buy_log add key(userid,buy_date);

#===========验证==============
mysql> show create table buy_log;
| buy_log | CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `userid_2` (`userid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

#可以看到possible_keys在这里有两个索引可以用，分别是单个索引userid与联合索引userid_2,但是优化器最终选择了使用的key是userid因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多
mysql> explain select * from buy_log where userid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys   | key    | key_len | ref   | rows | Extra |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid | 4       | const |    1 |       |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
row in set (0.00 sec)

#接着假定要取出userid为1的最近3次的购买记录，用的就是联合索引userid_2了，因为在这个索引中，在userid=1的情况下，buy_date都已经排序好了
mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys   | key      | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid_2 | 4       | const |    4 | Using where; Using index |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+--------------------------+
row in set (0.00 sec)

#ps：如果extra的排序显示是Using filesort，则意味着在查出数据后需要二次排序


#对于联合索引（a,b）,下述语句可以直接使用该索引，无需二次排序
select ... from table where a=xxx order by b;

#然后对于联合索引(a,b,c)来首，下列语句同样可以直接通过索引得到结果
select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;

#但是对于联合索引(a,b,c)，下列语句不能通过索引直接得到结果，还需要自己执行一次filesort操作，因为索引（a，c)并未排序
select ... from table where a=xxx order by c;

View Code

我们的mysql优化系统会根据最大化提升工作效率原则,决定使用联合索引还是单个索引.

本文转载自egon博客--mysql系列--索引原理与慢查优化===============cnblog.linhaifeng

posted @ 2017-12-19 19:15 dream-子皿阅读(138) 评论(0) 收藏举报

刷新页面返回顶部

孟郊

day45 mysql索引终章(数据库数据导出,导入)

公告