MySQL

索引

聚簇索引（聚集索引）

• 聚簇索引是顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条；

• 主键索引，就是聚集索引

• 如果表中没有主键索引，唯一索引会变为聚集索引。

非聚簇索引（非聚集索引、也叫二级索引、也叫辅助索引）

• 辅助索引 辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据叶，再通过数据叶中的Page Directory找到数据行。

• 非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

  #我们给 user 表的 UniqueCode 字段添加一个聚簇索引：
  CREATE CLUSTERED INDEX Index_user_uniquecode ON user(UniqueCode)

  ​

• 非聚集索引创建方式

  #我们给 user 表的Name字段添加一个非聚簇索引：
  CREATE NONCLUSTERED INDEX index_user_name ON user(Name);

• 复合索引 由多列创建的索引称为符合索引，在符合索引中的前导列必须出现在where条件中，索引才会被使用 ALTER TABLE test.users ADD INDEX idx_users_id_name (name(10) ASC, id ASC) ;

• 前缀索引 当索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，就用索引的前部分字串索引，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多。而且是对BLOB和TEXT列进行索引，或者非常长的VARCHAR列，就必须使用前缀索引，因为MySQL不允许索引它们的全部长度。

  注意：不支持ORDER BY和GROUP BY、覆盖扫描

  https://www.cnblogs.com/gomysql/p/3628926.html

  # city varchar(50);
  alter table city_demo add key (city(6));

  ​

• 唯一索引

  create unique index indexname on tablename(columnname); 
  alter table tablename add unique index indexname(columnname);

  ​

• 全文索引 for MyISAM and English （采用Sphinx（斯芬克斯）/Coreseek技术来处理中文）

  ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);
  SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

  ​

• ​

稠密索引

img

稀疏索引

img

两者优缺点：

• 1.稠密索引比稀疏索引更快地定位一条记录。

• 2.稀疏索引所占空间小，并且插入和删除时所需的维护开销也小。

锁机制

乐观锁

悲观锁

间隙锁（Gap lock）

引擎

MyISAM

Yandex.Metrika产品成立于2008年，最开始使用了MyISAM作为存储引擎。熟悉MySQL的同学都知道，这是MySQL的重要存储引擎之一（另外一个是InnoDB)。MyISAM中的实现也是使用LSM-Tree的设计，基本思路就是将对数据的更改hold在内存中，达到指定的threadhold后将该批更改批量写入到磁盘，在批量写入的过程中跟已经存在的数据做rolling merge。

img

使用MyISAM的方法，刚开始数据量不大，访问请求也不大的时候，这个方法非常有效，特别是对于一些固定的报告生成，效率非常高，系统能够保持很好的系统写能力。

数据格式也是传统的索引结构：一个数据文件+一个索引结构； 索引结构是一个B-Tree结构，叶子节点保持着数据文件的OffSet; 通过Index文件找到数据范围，然后进行数据文件读取；早期的实现是将Index文件装在内存中，数据文件在磁盘当中，或则SSD等。当时7200RPM的硬盘，每秒进行100-200次随机读；SSD硬盘可以支持30000次随机读/每秒。

• 索引扫描方式

  • 紧凑索引扫描(dense index)：

    在最初，为了定位数据需要做权表扫描，为了提高扫描速度，把索引键值单独放在独立的数据的数据块里，并且每个键值都有个指向原数据块的指针，因为索引比较小，扫描索引的速度就比扫描全表快，这种需要扫描所有键值的方式就称为紧凑索引扫描

  • 松散索引扫描(sparse index)：

    为了提高紧凑索引扫描效率，通过把索引排序和查找算法（B+trre），发现只需要和每个数据块的第一行键值匹配，就可以判断下一个数据块的位置或方向，因此有效数据就是每个数据块的第一行数据，如果把每个数据块的第一行数据创建索引，这样在这个新创建的索引上折半查找，数据定位速度将更快。这种索引扫描方式就称为松散索引扫描。

  • 覆盖索引扫描(covering index)：

    包含所有满足查询需要的数据的索引称为覆盖索引，即利用索引返回select列表中的字段，而不必根据索引再次读取数据文件

• https://www.zhihu.com/question/47604963/answer/119395192

Innodb

问题

• 同一个库，不同实例，不同表是否支持事务？

  支持，同一实例内支持

• 不同数据库是否支持数据？

  支持 开启 XA