mysql 之索引

ゞ浴缸里的玫瑰 2023-06-19 07:19 89阅读 0赞

**什么是索引：**

索引是一种高效获取数据的 存储结构，一般包含了 hash   二叉树 红黑树。

但是mysql中索引一般使用的是B树 准确说是使用的B+树构建的索引：若仅仅是进行select \* from table where id = 1，用上述的三种方法都会很轻松的实现，因为条件很准确，可以直接查找到，但是若是 where id>100这样的条件，那么就需要进行全表扫描查询了，但是上述三种数据结构类型的存储方式决定了需要进行整个树的遍历，而上述三种数据结构的树状层次是难以控制的，会因为数据的多少而导致树的高度发生变化，从而导致IO的次数较多。B+树却有先天的高度可控的优势，mysql通常是3层到5层，而B+树的数据结构是只会在叶子节点存储数据，叶子节点是以链表的形式互相指向的。

**MyIsam引擎（非聚集索引）**

Myisam全名为：ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法，不是事务安全的，而且不支持外键，会形成锁表，当大量访问同一个表时，会导致响应受阻塞，若执行大量的select  insert MyIsam比较合适。在创建表后生成三个文件 user.myi(索引文件) user.myd(数据文件) user.frm（数据结构类型）。如下图，当我们执行 select \* from table where id =1时的过程如下：

1.查看该表的myi文件，是否存在该id对应的索引

2.根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址（叶子节点存储的是索引和数据的地址，和Innodb区别之处，Innodb直接存储的是数据值本身，通过外键index找到主键index，再从主键index树中直接找到了对应叶子节点，即值本身）

3.根据步骤2中获取到的数据地址，去myd文件中依据这个地址找到这个地址对应的值并返回。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTE2MTY1NjAw_size_16_color_FFFFFF_t_70][]

**Innodb引擎（聚集索引）**

Innodb支持事务安全的引擎，支持外键 、行锁，事务是它最大的特点，若有大量的update和insert操作，建议使用Inoodb，特别是在多个并发和QPS较高的时候。

若此时，使用Innodb创建表，它只会生成两个文件 user.ibd(索引文件)、user.frm（数据结构类型），和MyIsam区别是因为MyIsam是非聚集索引，索引分散的存储在索引文件中 数据存储在数据文件中，而Innodb引擎是聚集索引类型的，创建表时默认的就使用主键作为了索引，不需要myi文件了，索引和数据均聚集在一棵树的叶子节点中了，故而只需要一个索引文件即可，数据结构类型文件仍然都需要，维护不同数据的数据结构类型。

innodb的表结构图如下，很显然它与myIsam最大的区别是将整条数据存在了B+树的叶子节点而不是存储的地址(和ASCM区别)，叶子节点存的是主键索引和数据信息。若此时，你在其他列创建索引如name上，它就会另外再创建一个以name为索引的索引树（叶子节点存的是索引和主键索引）。即若没有再其他的列上创建索引 那么就只维护了一棵以主键索引为主的索引树，树的叶子节点存储的是主键索引和数据信息；若在其他的列上又创建了其他的索引，那么会再维护一棵新的索引树，树的叶子节点存储的是该索引和主键索引的信息了，若此时执行如select \* from table where name ='test' 执行过程如下：

1.找到name对应的索引树（因为此时name不为主键，mysql又新维护了一棵以name+主键索引的 索引树，存在在该树的叶节点中）

2.通过步骤1在name索引树上找到的name的索引及其对应的主键索引地址值

3.去主键索引树中通过步骤2找到的主键索引地址值，寻址到对应的叶节点，并取出该叶节点中的数据值，返回信息。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTE2MTY1NjAw_size_16_color_FFFFFF_t_70 1][]

**Innodb引擎和isam引擎的区别：**

**       1.数据库文件差异：**

myIsam属于堆表，在磁盘上存储了三个文件:.myi索引文件，.myd数据文件，.frm数据结构类型，

myIsam支持三种不同的存储格式：静态表，动态表，压缩表

Innodb属于索引组织表，有共享存储空间和多表空间存储，两种存储方式表结构：.myi的索引表和.frm的数据结构类型，使用共享存储空间时，所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以是多个文件，，通过innodb\_data\_file\_path和innodb\_data\_home\_dir参数设置共享表攻坚的位置和名字，一般共享空间的名字叫idbdata1-n,若为夺标共享空间，那么每一个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表明开头，以.ibd为扩展名。

2.索引差异：

2.1 索引自增长

myisam引擎的自增长列必须是索引，若是组合做引，自增长可以不是第一列，他可以依据几列排序后递增

innodb引擎自增长列必须是索引，若是组合索引也必须是组合索引的第一列。

2.2 主键

myisam允许没有任何索引和主键的表存在

innodb的索引都是保存行的地址。若没有设置主键或者非空唯一索引，就会自动生成一个6字节的主键（用户不可见），innodb的数据是索引的一部分，附加索引保存的是主索引的值。

2.3关于count()函数

myisam保存有表的总行数，若select count(\*) from table;会直接取出来

innodb没有保存表的总函数，若使用select count(\*)就会遍历整个表，消耗大，但是加了where条件后，两者方式一样了

2.4 全文索引

myisam支持全文索引 fulltext类型的

innodb不支持fulltext类型全文索引，但是innodb可以使用sphinx插件支持全文索引，效果更好

2.5 delete from table

使用这条命令时，innodb不会从新建表，而是一条一条的删除数据，在inndob上如果要清空保存有大量的表，最好不要使用这个命令(推荐使用truncate table，不过需要用户有drop此表的权限)

2.6 索引保存位置

myisam的索引以表明+.myi文件分别保存

innodb的索引和数据一并保存在表空间里。

**3.并发开发的注意**

可以使用show create table tablename命令查看表的引擎类型

对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交了

可以执行以下命令来切换非事务表到事务（数据不会丢失），innodb比myisam更安全：alter table tablename type=innodb；或者alter table tablename engine=innodb

默认情况下innodb开启自动commit，若你按照myisam的使用方法来变成不会出错，只是性能低，如何避免？：

a 尽量将多个语句绑定在一个事务中提交，避免多次提交导致数据库开销

b 在一个事务获得非排他锁或者意向排他锁后，若后面还有需要处理的sql语句，在这两条或者多条sql语句之间程序应尽量少进行逻辑处理，减少锁开销

c 尽量避免死锁。

d、sql语句如果有where子句一定要使用索引，尽量避免获取意向排他锁。

f、针对我们自己的数据库环境，日志系统是直插入，不修改的，所以我们使用混合引擎方式，ZION\_LOG\_DB照旧使用myisam存储引擎，只有ZION\_GAME\_DB，ZION\_LOGIN\_DB，DAUM\_BILLING使用Innodb引擎。

4 如何选择引擎

下面先让我们回答一些问题：   
    
    ◆你的数据库有外键吗？   
    
    ◆你需要事务支持吗？   
    
    ◆你需要全文索引吗？   
    
    ◆你经常使用什么样的查询模式？   
    
    ◆你的数据有多大？   
      
    myisam只有索引缓存   
    innodb不分索引文件数据文件 innodb buffer   
    myisam只能管理索引，在索引数据大于分配的资源时，会由操作系统来cache；数据文件依赖于操作系统的cache。innodb不管是索引还是数据，都是自己来管理  
      
    思考上面这些问题可以让你找到合适的方向，但那并不是绝对的。如果你需要事务处理或是外键，那么InnoDB 可能是比较好的方式。如果你需要全文索引，那么通常来说 MyISAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从InnoDB中获得全文索引。  
      
    数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事，InnoDB只需要几分钟。  
      
    操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM 表中会非常快，而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在 MyISAM下会快一些，但是updates 在InnoDB下会更快一些——尤其在并发量大的时候。  
      
    所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM 也许会更适合。当然，在大型的环境下使用 MyISAM 也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用 InnoDB方式。但需要记住InnoDB 的表需要更多的内存和存储，转换100GB 的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。  
      
    对于支持事务的InnoDB类型的表，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事务（即使autocommit打开也可以），将大大提高性能。  
    
     
    
    InnoDB
    
    InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力 (crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。 InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non- locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定 (lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。 InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。  
    
    InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。  
    InnoDB所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是独立的表空间文件）,相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。  
    
    
    MyISAM   
    MyISAM 是MySQL缺省存贮引擎 .   
    每张MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是 MYI (MYIndex) 引伸。   
    因为MyISAM相对简单所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择.   
    MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦   
      
    以下是一些细节和具体实现的差别：   
      
    1.InnoDB不支持FULLTEXT类型的索引。   
    2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。  
    3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。   
    4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。   
    5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。  
    
    另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如 update table set num=1 where name like “%aaa%”  
    
    任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。

6 总结：

1.MyIsam不支持事务，Innodb支持事务类型。需要事务时选择innodb
    2.myIsam只支持表级锁，DBD支持页级锁默认为页级锁；Innodb支持行级和表级锁 默认为行级锁
      表级锁：直接锁定整张表，在锁定期间，其他进程都无法对该表操作，若设置是写锁，那其他进程读也不允许
      MyIsam是表级锁定的引擎，它不会出现死锁问题，
          对于write 锁表原理如下：若表上没有锁，加锁，否则把锁定请求放在写锁队列中。
          对于read，表锁定原理如下：若表上没有写锁，那么把一个读锁放在其上面，否则把锁请求放在读锁队列中，当一个锁定被释放时，表可以被写锁定队列中的线程得到，然后才是读线程队列中的线程。这意味着，若你在一个表上有许多更新，阿么你的select语句就将一直处于等待状态知道所有写锁定线程执行完。
       行级锁：值对指定的行进行锁定，它能大大减少DB操作的冲突，但是细粒度越小实现成本越大
    行级锁可能会导致“死锁”，那到底是怎么导致的呢，分析原因：Mysql行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，那么Mysql就会锁定这个主键索引，如果sql语句操作的是非主键索引，那么Mysql会先锁定这个非主键索引，再去锁定主键索引。在UPDATE 和 DELETE操作时Mysql不仅会锁定所有WHERE 条件扫描过得索引，还会锁定相邻的键值。
        
     “死锁”举例分析：
        表Test：（ID,STATE,TIME）  主键索引：ID  非主键索引：STATE
        当执行"UPDATE  STATE =1011 WHERE STATE=1000"  语句的时候会锁定STATE索引，由于STATE 是非    
        主键索引,所以Mysql还会去请求锁定ID索引
        当另一个SQL语句与语句1几乎同时执行时：“UPDATE STATE=1010 WHERE ID=1”  对于语句2 Mysql会先 
        锁定ID索引，由于语句2操作了STATE字段，所以Mysql还会请求锁定STATE索引。这时。彼此锁定着对方 
        需要的索引，又都在等待对方释放锁定。所以出现了"死锁"的情况。
     行级锁的优点： 
        有许多线程访问不同的行时，只存在少量的冲突。
        回滚时只有少量的更改
        可以长时间锁定单一的行
      行级锁缺点：
        相对于页级锁和表级锁来说占用了更多的内存
        当表的大部分行在使用时，比页级锁和表级锁慢，因为你必须获得更多的锁
        当在大部分数据上经常使用GROUP BY操作，肯定会比表级锁和页级锁慢。
        页级锁：表级锁速度快，但是冲突多；行级锁速度慢，但冲突少；页级锁就是他俩折中的，一次锁定相邻 
        的一组记录。
    3.MyISAM引擎不支持外键，InnoDB支持外键
    4.MyISAM引擎的表在大量高并发的读写下会经常出现表损坏的情况:
      我们以前做的项目就遇到这个问题，表的INSERT 和 UPDATE操作很频繁，原来用的MyISAM引擎，导致表隔 
       三差五就损坏，后来更换成了InnoDB引擎。其他容易导致表损坏原因：
       服务器突然断电导致数据文件损坏，强制关机（mysqld未关闭情况下）导致表损坏
       mysqld进程在写入操作的时候被杀掉
       磁盘故障
       表损坏常见症状：
       查询表不能返回数据或返回部分数据
       打开表失败： Can’t open file: ‘×××.MYI’ (errno: 145) 。Error: Table 'p' is marked as 
       crashed and should be repaired 。Incorrect key file for table: '...'. Try to repair it
     
        Mysql表的恢复：
       对于MyISAM表的恢复：
      可以使用Mysql自带的myisamchk工具： myisamchk -r tablename  或者 myisamchk -o 
      tablename（比前面的更保险） 对表进行修复
    5、对于count()查询来说MyISAM更有优势
        因为MyISAM存储了表中的行数记录，执行SELECT COUNT() 的时候可以直接获取到结果，而InnoDB需要扫    
        描全部数据后得到结果。但是注意一点：对于带有WHERE 条件的 SELECT COUNT()语句两种引擎的表执行 
        过程是一样的，都需要扫描全部数据后得到结果
    6、 InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
    7、MyISAM支持全文索引（FULLTEXT），InnoDB不支持
    
    8、MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高
       上截取了前辈们测试结论： 
    
    测试方法：连续提交10个query， 表记录总数：38万 ， 时间单位 s
     
            引擎类型                    MyISAM                InnoDB              性能相差
     
            count                      0.0008357            3.0163                3609
     
            查询主键                  0.005708              0.1574                27.57
     
            查询非主键                  24.01                  80.37                3.348
     
            更新主键                  0.008124            0.8183                100.7
     
            更新非主键                0.004141            0.02625              6.338
     
            插入                        0.004188            0.3694                88.21
      （1）加了索引以后，对于MyISAM查询可以加快：4 206.09733倍，对InnoDB查询加快510.72921倍，同时对MyISAM更新速度减慢为原来的1/2，InnoDB的更
      新速度减慢为原来的1/30。要看情况决定是否要加索引，比如不查询的log表，不要做任何的索引。
     
        （2）如果你的数据量是百万级别的，并且没有任何的事务处理，那么用MyISAM是性能最好的选择。
     
        （3）InnoDB表的大小更加的大，用MyISAM可省很多的硬盘空间。
     
            在我们测试的这个38w的表中，表占用空间的情况如下：
                引擎类型                    MyISAM              InnoDB
                数据                      53,924 KB          58,976 KB
                索引                      13,640 KB          21,072 KB
                占用总空间              67,564 KB          80,048 KB
      
            另外一个176W万记录的表， 表占用空间的情况如下：
     
                引擎类型                MyIsam              InnorDB
                数据                  56,166 KB          90,736 KB
                索引                  67,103 KB          88,848 KB
                占用总空间        123,269 KB        179,584 KB

**七、性能对比**

**      **

测试的版本是mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686)，使用的是Innodb plugin 1.0.8（官方称比built-in版本性能更好）和默认的MyISAM。
    
    测试机器是笔记本，配置如下：Intel 酷睿2双核 P8600，2G*2 DDR3 1066内存，320G硬盘5400转。
    
    测试一：数据插入性能测试，这里我分别对innodb_flush_log_at_trx_commit参数打开和关闭都测了了一下，每次测试都是运行40s，表中数字都是实际插入条数。
    
     
                          MyISAM                 Innodb (打开)      Innodb
     (关闭)
    
    单线程，逐个插入         120000                 60000              60000
    
    4线程，逐个插入          40000*4
                    20000*4
                40000*4
    
    单线程，批量100条/次插入  3600*100
                   800*100            3000*100
    
    单线程，批量200条/次插入  1800*200
                   400*200            1600*200
    
    可以发现批量插入的性能远高于单条插入，但是一次批量的大小对性能影响不大。每条记录是否都刷新日志的参数对innodb性能的影响巨大。总体上来说，MyISAM性能更优一点。这里有一点需要注意，在插入测试过程中，我对系统资源进行了监控，发现MyISAM对系统资源占用很低，但是Innodb对磁盘占用却很高，应该是对事务控制多了很多需要记录的日志。
    
    测试二：数据读取性能测试。每次随机读取1000条记录，反复进行读取。
    
     
                           MyISAM        Innodb
    
    单线程，200次读取         5.7s
              16.7s
    
    4线程，200次读取          12s
               40.8s
    
    可以看出MyISAM的读取性能非常恐怖，性能差距在3倍的样子。
    
    以上两个测试发现MyISAM在无事务的需求下几乎完胜，但是要知道它是表锁，Innodb是行锁，那么在并发读写同时存在的情况下，那结果会是怎么样呢？！
    
    测试三：两个线程并发写入，2个线程并发读取。
    
     
                          MyISAM                                 Innodb
    
    逐个插入                写入40s：10000*2
     读取200次*2：14s        写入40s：60000*2
     读取200次*2：50s
    
    批量100条/次插入        写入40s：1000*100*2 读取200次*2：10s      写入40s：1500*100*2 读取200次*2：50s
    
    这下立刻显示出Innodb在并发情况下强劲的性能，几乎没有什么性能衰减。而MyISAM单条插入速度变得非常慢，批量插入也下降了40%性能。
    
    总结一下，在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。
    
    
    http://blog.csdn.net/wjtlht928/article/details/46641865

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTE2MTY1NjAw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191205101824419.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTE2MTY1NjAw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTE2MTY1NjAw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191205103027369.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMTE2MTY1NjAw,size_16,color_FFFFFF,t_70