mysql-索引、引擎

jwang2021-10-132026-01-23

索引与优化

1.MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

2.索引优缺点：

优点：
    1.所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引
    2.大大加快数据的查询速度
缺点：
    1.创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
    2.索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件
      可能会比数据文件更快达到上线值
    4.当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

3.使用

1.对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引
2.数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，
  索引就可能不会产生优化效果。
3.在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，
  在一个字段上不同值较多可是建立索引。

索引类型

1.索引的类型

1.普通索引 index(key)： MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

2.唯一索引 unique：索引列中的值必须是唯一的，但是允许为空值

3.主键索引 primary key：是一种特殊的唯一索引，不允许有空值

4.全文索引 fulltext: 只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有”好人，二货 …”

5.空间索引 spatial: 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。

2.存储数据结构

1.HASH 只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。

(1)由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记
    录是非常迅速的，不会对性造成太大的影响。 
(2)不能使用hash索引排序。 
(3)Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。 
(4)Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

2.b-tree BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

3.单列索引和组合索引

1.单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。

普通索引: MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快
一点。
唯一索引：索引列中的值必须是唯一的，但是允许为空值
主键索引：是一种特殊的唯一索引，不允许有空值。

2.组合索引(复合索引)

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索
引时遵循最左前缀集合。

1.需要加索引的字段，要在where条件中
2.数据量少的字段不需要加索引
3.如果where条件中是OR关系，加索引不起作用
4.符合最左原则

对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

索引语句

1.创建索引
以User表的username字段(类型为VARCHAR(50))为例，使用username字段的6个字符前缀来创建索引。
CREATE INDEX idx_user_username ON user (username(6));

CREATE INDEX可对表增加普通索引或UNIQUE索引
--例,只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

2.删除索引

ALTER TABLE table_name DROP INDEX index_name;

3.修改索引：先删除再创建同名索引

--先删除
ALTER TABLE user DROP INDEX idx_user_username;
--再以修改后的内容创建同名索引
CREATE INDEX idx_user_username ON user (username(8));

4.查看索引

--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name

5.查看是否使用了索引进行查询
EXPLAIN SELECT * FROM book WHERE year_publication = 1990;

6.添加索引
ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

引擎

概述
存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。存储引擎的概念是MySQl的特点，而且是一个插入式的存储引擎概念。这就决定了MySQl数据库中的表可以使用不同的存储方式存储。用户可以根据自己的不同要求，选择不同的存储方式、是否进行事务处理等。

引擎分类

InnoDB存储引擎

InnoDB是Mysql数据库的一种存储引擎。InnoDB给Mysql的表提供了 事务、回滚、崩溃修复能力、多版本并发控制
的事务安全、间隙锁（可以有效的防止幻读的出现）、支持辅助索引、聚簇索引、自适应hash索引、支持热备、行级
锁。还有InnoDB是Mysql上唯一一个提供了外键约束的引擎。

InnoDB存储引擎中，创建的表的表结构是单独存储的并且存储在.frm文件中。数据和索引存储在一起的并且存储在表
空间中。但是默认情况下mysql会将数据库的所有InnoDB表存储在一个表空间中的。其实这种方式管理起来非常的不
方便而且还不支持高级功能所以建议每个表存储为一个表空间实现方式为：使用服务器变量
innodb_file_per_table = 1。

如果需要频繁的进行更新、删除操作的数据库也可选择InnoDB存储引擎。因为该存储引擎可以实现事务提交和回滚。

MyISAM存储引擎

MyISAM存储引擎是Mysql中常见的存储引擎，MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM支持全文索
引、压缩存放、空间索引（空间函数）、表级锁、延迟更新索引键。但是MyISAM不支持事务、行级锁、更无法忍受的
是崩溃后不能保证完全恢复（只能手动修复）。

MyISAM存储引擎的表存储成3个文件。文件的名字和表的名字相同。扩展名包含frm、MYD、MYI。其中frm为扩展名的
文件存储表的结构；MYD为扩展名的文件存储数据，其是MYData的缩写；MYI为扩展名的文件存储索引，其为MYIndex
的缩写。

MyISAM存储引擎的插入数据很快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择
MyISAM存储引擎能够实现处理的高效率。如果应用的完整性、并发性要求很低，也可以选择MyISAM存储引擎。

MEMORY存储引擎

MEMORY存储引擎是Mysql中的一类特殊的存储引擎。其使用存储在内存中的内存来创建表，而且所有数据保存在内存
中。数据安全性很低，但是查找和插入速度很快。如果内存出现异常就会影响到数据的完整性，如果重启或关机，表中
的所有数据就会丢失，因此基于MEMORY存储引擎的表的生命周期很短，一般都是一次性的。适用于某些特殊场景像查
找和映射，缓存周期性的聚合数据等等。

NDB 集群引擎

作为sql和NDB元素协议之间的接口,用于mysql,ndb集群存储引擎,分布式等

优化建议

1.尽可能让所有的数据检索都通过索引来完成
2.合理设计索引，使索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他
Query的执行；
3.尽量控制事务的大小，减少锁定的资源量和锁定时间长度
4.尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录
5.尽可能按照相同的访问顺序来访问，防止产生死锁
6.尽可能做到一次锁定所需要的所有资源，减少死锁产生概率