MySql 系列二：索引

索引

索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引；

主键索引

非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的B+树包含表数据信息；

PRIMARY KEY(key)

唯一索引

不可以出现相同的值，可以有NULL值；

UNIQUE(key)

普通索引

允许出现相同的索引内容；

INDEX(key)
-- OR
KEY(key[,...])

组合索引

对表上的多个列进行索引

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT;
在短字符串中用 LIKE % ；在全文索引中用 match 和 against;

主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键；

1. 如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键；
2. 如果没有显示设置，则从非空唯一索引中选择；
   • 只有一个非空唯一索引，则选择该索引为主键；
   • 有多个非空唯一索引，则选择声明的第一个为主键；
3. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key，
foreign key， default, not null；

外键约束

外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注释作
用；而innodb完整支持外键；

create table parent (
id int not null,
primary key(id)
) engine=innodb;

create table child (
id int,
parent_id int,
foreign key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE
CASCADE
) engine=innodb;

-- 被引用的表为父表，引用的表称为子表；
-- 外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行为发生时的操作可选择：
-- CASCADE 子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id = 1;

约束于索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束时逻辑上的概念；索引是一个
数据结构既包含逻辑的概念也包含物理的存储方式；

索引实现

索引存储

innodb由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由64个连续页构成）；页的默认值为16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区；

页

页是 innodb 磁盘管理的最小单位；默认16k，可通过 innodb_page_size 参数来修改；B+树的一个节点的大小就是该页的值；

B+树

聚集索引

按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分；

select * from user where id >= 18 and id < 40;

辅助索引

叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还 包含一个bookmark ；该书签存储了聚集索引的 key；

-- 某个表 包含 id name lockyNum; id是主键，lockyNum存储辅助索引;
select * from user where lockyNum = 33;

最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配；

覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树；较少磁盘io；

索引失效

• select … where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
• 索引字段参与运算，则索引失效；例如： from_unixtime(idx) = ‘2021-04-30’;
• 索引字段发生隐式转换，则索引失效；例如： ‘1’ 隐式转换为 1 ；
• LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like ‘%ark’;
• 在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；
• 组合索引中，没使用第一列索引，索引失效；

索引原则

• 查询频次较⾼且数据量⼤的表建⽴索引；索引选择使⽤频次较⾼，过滤效果好的列或者组合；
• 使⽤短索引；节点包含的信息多，较少磁盘io操作；
• 对于很长的动态字符串，考虑使用前缀索引

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，
从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值。索引的选择性越高则
查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。对于BLOB,TEXT,VARCHAR类型的列，必须要使
用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

select count(distinct left(name,3))/count(*) as sel3,
count(distinct left(name,4))/count(*) as sel4,
count(distinct left(name,5))/count(*) as sel5,
count(distinct left(name,6))/count(*) as sel6,
from user;
alter table user add key(name(4));
-- 注意：前缀索引不能做 order by 和 group by

• 对于组合索引，考虑最左侧匹配原则和覆盖索引；
• 尽量选择区分度⾼的列作为索引；该列的值相同的越少越好；
  select count(distinct idx) / count(*) from table_name;
• 尽量扩展索引，在现有索引的基础上，添加复合索引；
• 不要 select * ； 尽量只列出需要的列字段；
• 索引列，列尽量设置为非空；

SQL优化

https://dev.mysql.com/doc/refman/5.7/en/optimization.html

EXPLAIN

用来查看SQL语句的具体执行过程。
原理：模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的