Mysql--主键，索引，引擎

小灰灰 2022-12-20 02:51 135阅读 0赞

### 主键，索引，引擎 ###

CREATE TABLE `表名` (
      `列名1` int(11) NOT NULL,
      `列名2` varchar(255) NOT NULL,
       ....
      PRIMARY KEY (`主键1`,`主键2`,`主键3`...)，
      KEY `索引名` (`列名1`,`列名2`...) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    tips:
      PRIMARY KEY (`主键1`,`主键2`,`主键3`...)
      KEY `索引名` (`列名1`,`列名2`) USING BTREE
      
      KEY `索引名` (`列名`) USING HASH
      UNIQUE KEY `索引名` (`列名`) USING BTREE
      UNIQUE KEY `索引名` (`列名`) USING HASH

#### 1）索引详解 ####

Tips：

一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。 B join A ，A表上建on条件的索引。

索引其他连接：

https://blog.csdn.net/w892824196/article/details/82592373

一些存储引擎允许您在创建索引时指定索引类型。例如:  
CREATE TABLE lookup (id INT) ENGINE = MEMORY;  
CREATE INDEX id\_index ON lookup (id) USING BTREE;

B-TREE索引的特点  
B-TREEB-TREE以B+树结构存储数据，大大加快了数据的查询速度  
B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）  
B-TREE索引使用场景

全值匹配的查询SQL，如 where act\_id= ‘1111\_act’  
联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx\_actid\_name(act\_id,act\_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act\_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）  
匹配模糊查询的前匹配，如where act\_name like ‘11\_act%’  
匹配范围值的SQL查询，如where act\_date > ‘9865123547215’（not in和<>无法使用索引）  
覆盖索引的SQL查询，就是说select出来的字段都建立了索引

HASH索引的特点  
Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引  
存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询  
Hash索引无法用于排序  
Hash不适用于区分度小的列上

问题1：mysql索引类型normal，unique，full text的区别是什么？  
normal：表示普通索引  
unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique  
full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。  
总结，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

问题2：在实际操作过程中，应该选取表中哪些字段作为索引？  
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则：  
1．选择唯一性索引  
2．为经常需要排序、分组和联合操作的字段建立索引  
3．为常作为查询条件的字段建立索引  
4．限制索引的数目  
5．尽量使用数据量少的索引  
6．尽量使用前缀来索引  
7．删除不再使用或者很少使用的索引

一、 MySQL: 索引以B树格式保存  
Memory存储引擎可以选择Hash或BTree索引，Hash索引只能用于=或<=>的等式比较。  
1、普通索引：  
create index on Tablename(列的列表)  
alter table TableName add index (列的列表)  
create table TableName(\[…\], index \[IndexName\] (列的列表)

2、唯一性索引：create unique index  
alter … add unique  
主键：一种唯一性索引，必须指定为primary key

3、全文索引：从3.23.23版开始支持全文索引和全文检索，FULLTEXT，  
可以在char、varchar或text类型的列上创建。

4、单列索引、多列索引：  
多个单列索引与单个多列索引的查询效果不同，因为：  
执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

5、最左前缀(Leftmost Prefixing)：多列索引，例如：fname\_lname\_age索引，以下的搜索条件MySQL都将使用  
fname\_lname\_age索引：firstname,lastname,age;firstname,lastname;firstname，其他情况将不使用。

二、根据sql查询语句确定创建哪种类型的索引，如何优化查询  
选择索引列：  
a.性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有  
两种类型的列：在where子句中出现的列，在join子句中出现的列。  
b.考虑列中值的分布，索引的列的基数越大，索引的效果越好。  
c.使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。  
d.利用最左前缀  
e.不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。  
在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或\_开头的情形)。

mysql 索引分类  
在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令 MySQL的查询和运行更加高效。索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。  
1、普通型索引  
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：  
（1）创建索引，例如CREATE INDEX 索引的名字 ON tablename (列名1，列名2,…);  
（2）修改表，例如ALTER TABLE tablename ADD INDEX 索引的名字 (列名1，列名2,…);  
（3）创建表的时候指定索引，例如CREATE TABLE tablename ( \[…\], INDEX 索引的名字 (列名1，列名  
2,…) );

2、唯一索引  
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：  
（1）创建索引，例如CREATE UNIQUE INDEX 索引的名字 ON tablename (列的列表);  
（2）修改表，例如ALTER TABLE tablename ADD UNIQUE 索引的名字 (列的列表);  
（3）创建表的时候指定索引，例如CREATE TABLE tablename ( \[…\], UNIQUE 索引的名字 (列的列  
表) );

3、主键  
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO\_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( \[…\], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引）

4、单列索引和多列索引  
索引可以是单列索引，也可以是多列索引。  
（1）单列索引就是常用的一个列字段的索引，常见的索引。  
（2）多列索引就是含有多个列字段的索引  
alter table student add index sy(name,age，score);  
索引sy就为多列索引，多列索引在以下几中情况下才能有效：  
select \* from student where name=‘jia’ and age>=‘12’ //where条件中含有索引的首列字段和  
第二个字段  
select \* from student where name=‘jia’ //where条件中只含有首列字段  
select \* from student where name=‘jia’ and score<60//where条件中含有首列字段和第三个字  
段  
总结：多列索引只有在where条件中含有索引中的首列字段时才有效

5、选择索引列  
应该怎样选择索引列，首先要看查询条件，一般将查询条件中的列作为索引

#### 2）引擎详解 ####

······InnoDB······  
Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引；因此，过程为：将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 13”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（聚集索引–索引指向主键key，主键key本身就是索引）

InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。  
对于InnoDB来说，最大的特点在于支持事务。但是这是以损失效率来换取的。  
InnoDB是默认的数据库存储引擎，他的主要特点有：  
（1）可以通过自动增长列，方法是auto\_increment。  
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。  
（3）使用的锁粒度为行级锁，可以支持更高的并发；  
（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。  
（5）配合一些热备工具可以支持在线热备份；  
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；  
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

当然InnoDB的存储表和索引也有下面两种形式：  
（1）使用共享表空间存储：所有的表和索引存放在同一个表空间中。  
（2）使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。  
适用场景：  
1）经常更新的表，适合处理多重并发的更新请求。  
2）支持事务。  
3）可以从灾难中恢复（通过bin-log日志等）。  
4）外键约束。只有他支持外键。  
5）支持自动增加列属性auto\_increment。

······MyISAM······  
MyISAM索引结构: MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据；B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。因此，过程为： MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，根据data域的值去读取相应数据记录。（非聚集索引–索引指向元数据，元数据的地址存数据）

MyISAM有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎。  
MyISAM引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。

支持数据的类型也有三种：  
（1）静态固定长度表  
这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。  
（2）动态可变长表  
优点是节省空间，但是一旦出错恢复起来比较麻烦。  
（3）压缩表  
上面说到支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。

适用场景：  
1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。  
2）不支持外键的表设计。  
3）查询速度很快，如果数据库insert和update的操作比较多的话比较适用。  
4）整天 对表进行加锁的场景。  
5）MyISAM极度强调快速读取操作。  
6）MyIASM中存储了表的行数，于是SELECT COUNT(\*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。  
缺点：  
就是不能在表损坏后恢复数据。（是不能主动恢复）

······Memory······  
（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；  
（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；  
（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；  
（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型表，性能会急剧降低；  
（5）默认使用hash索引。  
（6）如果一个内部表很大，会转化为磁盘表。

适用场景：  
1）那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。  
2）目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max\_heap\_table\_size控制Memory表的大小，限制Memory表的最大的大小。  
3）数据是临时的，而且必须立即可用得到，那么就可以放在内存中。  
4）存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。

InnoDB和MyISAM使用建议：  
以下两点必须使用 InnoDB：  
1）可靠性高或者要求事务处理，则使用InnoDB。这个是必须的。  
2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。  
对比之下，MyISAM的使用场景：  
1）做很多count的计算的。如一些日志，调查的业务表。  
2）插入修改不频繁，查询非常频繁的。