临沂网站建设那家好,淘宝联盟怎样做新增网站推广,唯品会信息科技有限公司,龙华网站建设哪家公司好MySQL6#xff1a;索引使用原则#xff0c;联合索引#xff0c;联合主键/复合主键#xff0c;覆盖索引、什么是回表#xff1f;索引条件下推#xff0c;索引的创建与使用#xff0c;索引的创建与使用#xff0c;索引失效 索引使用原则列的离散(sdn)度 联合索引创建联合… MySQL6索引使用原则联合索引联合主键/复合主键覆盖索引、什么是回表索引条件下推索引的创建与使用索引的创建与使用索引失效 索引使用原则列的离散(sdn)度 联合索引创建联合索引联合索引最左匹配建立联合索引之后联合索引的最左字段还要再建普通索引吗 联合索引使用场景什么时候能用到联合索引 联合主键/复合主键覆盖索引什么是回表什么是覆盖索引如何判断是覆盖索引 索引条件下推(Index Condition Pushdown)判断使用索引条件下推 索引的创建与使用索引的创建 索引失效MySQL合集 索引使用原则
我们容易有以一个误区就是在经常使用的查询条件上都建立索引索引越多越好那到底是不是这样呢? 并不是越多越好索引好占用磁盘空间的你的表200M你的索引可能就是4G如果搞很多索引磁盘空间浪费会很大因此只在必要的字段上建立索引。那么什么样的字段适合建立索引呢一般选择列的离散度高的列建立索引。
列的离散(sdn)度
列的离散度的公式
## 列的全部不同值和所有数据行的比例
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;数据行数相同的情况下分子越大列的离散度就越高。简单来说如果列的重复值越多离散度就越低重复值越少离散度就越高。
如果在BTree里面的重复值太多(比如性别建立索引)MySQL的优化器发现走索引跟使用全表扫描差不了多少的时候就算建了索引也不一定会走索引。
联合索引
前面我们说的都是针对单列创建的索引但有的时候我们的多条件査询的时候也会建立联合索引。单列索引可以看成是特殊的联合索引。
创建联合索引
CREATE TABLE stu_innodb (id INT(11) NOT NULL,name VARCHAR(50) NOT NULL COLLATE utf8mb4_unicode_ci,sex INT(11) NULL DEFAULT NULL,age INT(11) NULL DEFAULT NULL,card_id VARCHAR(10) NOT NULL COLLATE utf8mb4_unicode_ci
)
COLLATEutf8mb4_unicode_ci
ENGINEInnoDB
;比如我们在stu_innodb表上面给card_id和name建立了一个联合索引。
## 删除索引
ALTER TABLE stu_innodb DROP INDEX idx_cardid_name;
## 创建联合索引
ALTER TABLE stu_innodb ADD INDEX idx_cardid_name (card_id,name);
## 查看索引
show index from stu_innodb;联合索引最左匹配
联合索引在BTree中是复合的数据结构它是按照索引的顺序从左到右的顺序来建立搜索树的比如联合索引idx_cardid_name (card_id,name)就是card_id在左边name在右边。 如图card_id是有序的name是无序的。当card_id相等的时候 name才是有序的。
这个时候我们使用where card_idstu006 and nameBlue去査询数据的时候BTree会优先比较card_id来确定下一步应该搜索的方向往左还是往右。如果card_id相同的时候再比较name但是如果查询条件没有card_id就不知道第一步应该查哪个节点因为建立搜索树的时候card_id是第一个比较因子所以用不到索引。
建立联合索引之后联合索引的最左字段还要再建普通索引吗
CREATE INDEX idx_cardid on user_innodb(card_id);
CREATE INDEX idx_cardid_name on user_innodb(card_id,name); 当我们创建一个联合索引的时候按照最左匹配原则用左边的字段card_id去査询的时候也能用到索引所以第一个索引完全没必要。因为联合索引(card_id,name)相当于建立了两个索引(card_id)(card_id,name)。
如果我们创建三个字段的索引index(a,b,c)相当于创建三个索引
index(a)index(a,b)index(a,b,c)
同理根据最左原则
where a?可以where a? and b?可以where a? and b? and c?可以where a? and c?可以用到a的索引不可以用到c的因为中断(跳过)bwhere b?不可以where b? and c?不可以
在不含最左的a的where条件中是不能使用到联合索引的且中断(跳过)不能使用到联合索引的。
联合索引使用场景
根据联合索引的特性什么时候会用到联合索引呢 那肯定是查询条件覆盖了联合索引的列。 比如查询高考成绩必须输入考生姓名和学生的身份证号那么这种场景就可以对二者建立联合索引。
什么时候能用到联合索引
顺序使用两个字段用到联合索引 ## 顺序使用两个字段用到联合索引
explain select * from stu_innodb where card_idstu006 and nameBlue;乱序使两个字段用到联合索引(查询优化器的功劳) ## 乱序使两个字段用到联合索引
explain select * from stu_innodb where nameBlue and card_idstu006;使用左边的索引字段用到联合索引 ## 使用左边的索引字段用到联合索引
explain select * from stu_innodb where card_idstu006;不使用左边的索引字段无法使用索引全表扫描 ## 不使用左边的索引字段无法使用索引全表扫描
explain select * from stu_innodb where nameBlue;所以我们在建立联合索引的时候一定要把最常用的列放在最左边查询条件必须由第一个索引字段开始不能中断(跳过)。
联合主键/复合主键
主键有唯一的约束。当多个字段组合成唯一标识的时候创建复合主键。 比如一个学生表中学生卡号、姓名、学院是一条记录的唯一标识那么就可以建立复合主键通过定义复合主键减少了表的数量不是一个college(学院)一张表。
CREATE TABLE stu_innodb (name VARCHAR(50) NOT NULL COLLATE utf8mb4_unicode_ci,sex INT(11) NULL DEFAULT NULL,age INT(11) NULL DEFAULT NULL,card_id VARCHAR(10) NOT NULL COLLATE utf8mb4_unicode_ci,college VARCHAR(10) NOT NULL COLLATE utf8mb4_unicode_ci,PRIMARY KEY (card_id, name, college) USING BTREE
)
COLLATEutf8mb4_unicode_ci
ENGINEInnoDB
;复合主键的特点
因为复合主键需要存储多个字段的值相对于单列主键来说要消耗更多的存储空间联合主键包含多个列的时候不允许所有的字段都相同。因为判断是否重复更复杂(代码中重写hashCode和equals也是) 举例一张表有a、b、c三个字段不允许a、b、c三个字段的值都相同可以有部分的值都相同例如 record11 2 3record12 3 4record11 1 3record11 1 3 不允许 表结构修改或者数据迁移会更加困难如果目的是限制唯一性可以直接拼接两个字段的内容比如CAIJING1001CHUANMEI1001或者用唯一索引UNIQUE KEY也可以实现如果目的不是为了限制唯一性或者有其它检查唯一性的方法用自增ID之类的无业务意义的字段作为主键更合适。自增ID在插入数据时引起的页分裂和合并更少
MBG(MyBatis GeneratorMBG 在配置中为每个表简单的CRUD操作生成 SQL)对复合主键会生成两个实体类
覆盖索引
什么是回表
回表非主键索引我们先通过索引找到主键索引的键值再通过主键值查出索引里面没有的数据它比基于主键索引的查询多扫描了一棵索引树这个过程就叫回表。
当我们用name索引查询一条记录它会在二级索引的叶子节点找到nameSusan拿到主键值也就是id 3然后再到主键索引的叶子节点拿到数据。
什么是覆盖索引
覆盖索引在二级索引里面不管是单列索引还是联合索引如果select的数据列只用从索引中就能够取得不必从数据区中读取这时候使用的索引就叫做覆盖索索引这样就避免了回表。。比如上图select name from tbl_xxx where name? 因为二级索引的叶子节点上就已经存储了name的数据因此不需要回表性能就会高点。
如何判断是覆盖索引
覆盖索引并不是索引的一种类型而是使用索引产生的一种情况。Explain中的Extra列里面值为Using index代表属于覆盖索引的情况。
在联合索引idx_cardid_name (card_id,name)中
这三个查询语句属于覆盖索引
①使用联合索引②select的所有列已经包含在了用到的索引中 explain select name,card_id from stu_innodb where card_idstu006 and nameBlue; 属于覆盖索引 ①使用联合索引②select的所有列已经包含在了用到的索引中 explain select name from stu_innodb where card_idstu006;用到了索引属于覆盖索引因为联合索引的创建使得当前的BTree存储了card_id,name的数据因此不需要回表 ①不使用联合索引②select的所有列已经包含在了用到的索引中 explain select name from stu_innodb where nameBlue; 还是用到了索引并且属于覆盖索引查询优化器优化了优化器觉得用索引更快所以还是用到了索引
覆盖索引减少了I/O次数减少了数据的访问量可以大大地提升查询效率。
索引条件下推(Index Condition Pushdown)
MySQL架构是分层的可以分为连接层、服务层、存储引擎层。 不同的索引是在存储引擎层中实现的。如果where条件包含索引那么是可以在存储引擎层完成数据过滤的。如果用不到索引过滤数据就需要去Server层。那么如何优化呢 如果返回了一大堆数据都不是就客户端需要的那么能不能把这个过滤的动作先在存储引擎层做完即使查询条件用不到索引也先在存储引擎中过滤一遍这个动作就叫做索引条件下推。
索引条件下推(Index Condition Pushdown)5.6以后完善的功能不需要我们开启是InnoDB自动开启自动优化的。只适用于二级索引。ICP的目标是减少访问表的完整行的读数量从而减少I/O操作。这里说的下推其实是意思是把过滤的动作在存储引擎做完而不需要到Server层过滤把原来存储引擎无法使用的过滤条件还是先让他在存储引擎中先过滤。
我们建个表来深入理解一下idx_college_cardid作为二级联合索引
-- 导出 表 test.stu_innodb 结构
-- 导出 表 test.stu_innodb 结构
CREATE TABLE IF NOT EXISTS stu_innodb (id int(11) NOT NULL,name varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,sex int(11) DEFAULT NULL,age int(11) DEFAULT NULL,card_id varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,college varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,PRIMARY KEY (id) USING BTREE,KEY idx_college_cardid (college,card_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;-- 正在导出表 test.stu_innodb 的数据~12 rows (大约)
INSERT INTO stu_innodb (id, name, sex, age, card_id, college) VALUES(2, Tom, 1, 23, stu001, 财经),(4, Bill, 1, 30, stu002, 财经),(5, Susan, 0, 27, stu003, 财经),(6, Marry, 0, 28, stu004, 财经),(7, Sky, 1, 19, stu005, 财经),(8, Blue, 1, 22, stu006, 财经),(12, Tom, 1, 23, stu001, 金融),(14, Bill, 1, 30, stu002, 金融),(15, Susan, 0, 27, stu003, 金融),(16, Marry, 0, 28, stu004, 金融),(17, Sky, 1, 19, stu005, 金融),(18, Blue, 1, 22, stu006, 金融);当我们执行SQL语句SELECT * FROM stu_innodb where college金融 AND card_id LIKE %stu002;正常情况来说(也就是不适用索引条件下推的情况)因为字符是从左往右排序的当你把加在前面的时候是不能基于索引去比较的所以只有college这个字段能够用于索引比较和过滤。
按有没有使用索引条件下推查询过程分为两种情况 没有索引下推查询过程是这样的 ① 根据联合索引查出所有college金融的二级索引数据在其叶子节点上得到对应的主键索引值(121415161718)② 回表到主键索引上查询全部复合条件的数据6条数据③ 把这6条数据返回给Server层在Server层对这6条数据进行符合card_id LIKE %stu002条件的过滤 注意索引的比较是在存储引擎层进行的数据记录的比较是在Server层进行的。而当card_id LIKE %stu002条件不能用于索引过滤时Server层不会把card_id LIKE %stu002条件传递给存储引擎所以读取很多条没有必要的记录(Server层并不需要)。 这时候如果满足college金融的记录有10万条就会有99999条没有必要读取的记录而且都还要返回给Server层。所以根据college字段过滤的动作能不能在存储引擎层完成呢就是下面的查询方法。 使用索引下推查询过程是这样的 ① 根据联合索引查出所有college金融的二级索引数据在其叶子节点上得到对应的主键索引值(121415161718)② 然后从二级索引中筛选出card_id LIKE %stu002条件的索引这下就只有一个了(14)③ 回表到逐渐索引上查询全部符合条件的数据(1条数据)返回Server层 先用college金融条件进行二级索引范围扫描读取数据表记录然后进行比较检查是否符合card_id LIKE %stu002的条件。此时6条中只有1条符合条件。
判断使用索引条件下推
Extra是Using index condition就代表使用索引条件下推全称其实就是Using index condition push down。
查询是否开启索引条件下推
show variables LIKE optimizer_switch;可以看到index_condition_pushdownonInnoDB中默认开启索引条件下推
index_mergeon,index_merge_unionon,index_merge_sort_unionon,index_merge_intersectionon,engine_condition_pushdownon,index_condition_pushdownon,mrron,mrr_cost_basedon,block_nested_loopon,batched_key_accessoff,materializationon,semijoinon,loosescanon,firstmatchon,duplicateweedouton,subquery_materialization_cost_basedon,use_index_extensionson,condition_fanout_filteron,derived_mergeon我们先把索引条件下推关闭
set optimizer_switchindex_condition_pushdownoff;关闭之后查看SQL执行计划会发现Extra是Using where
explain SELECT * FROM stu_innodb where college金融 AND card_id LIKE %stu002;Extra返回Using where表示存储引擎返回的数据不全是Server层需要的需要在Server层再做过滤
启用索引条件下推之后再次查看SQL执行计划
set optimizer_switchindex_condition_pushdownon;
explain SELECT * FROM stu_innodb where college金融 AND card_id LIKE %stu002;会发现Extra变成了Using index condition全称其实就是Using index condition push down。
索引的创建与使用
因为索引对于改善查询性能的作用是巨大的所以我们的目标是尽量使用索弓I。
索引的创建 在用于where判断order排序和join的(on)、group by的字段上创建索引。 索引的个数不要过多。——浪费空间更新变慢。 过长的字段但是匹配只需要前面的内容(具体是几个字符在建立索引时指定)建立前缀索引。 alter table 表名 add index 索引名(列名(长度));注意在前缀索引中BTree里保存的就不是完整的该字段的值必须要回表才能拿到需要的数据。所以用了前缀索引就用不了覆盖索引了。 区分度低的字段例如性别不要建索引。——离散度太低导致扫描行数过多。 频繁更新的值不要作为主键或者索引。——造成页分裂和合并引起BTree的结构调整会浪费很大的性能 随机无序的值不建议作为索引(例如身份证、UUID)。——无序分裂 联合索引把散列性高(区分度高)的值放在前面。 创建复合索引而不是修改单列索引。
索引失效
-- 导出 表 test.stu_innodb 结构
CREATE TABLE IF NOT EXISTS stu_innodb (id int(11) NOT NULL,name varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,sex int(11) DEFAULT NULL,age int(11) DEFAULT NULL,card_id varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,college varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,PRIMARY KEY (card_id,name,college) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;-- 正在导出表 test.stu_innodb 的数据~6 rows (大约)
INSERT INTO stu_innodb (id, name, sex, age, card_id, college) VALUES(2, Tom, 1, 23, stu001, ),(4, Bill, 1, 30, stu002, ),(5, Susan, 0, 27, stu003, ),(6, Marry, 0, 28, stu004, ),(7, Sky, 1, 19, stu005, ),(8, Blue, 1, 22, stu006, );以这个表为例建立了复合主键 索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式计算(、-、×、÷)。因为会得到一个不确定的结果所以无法使用索引宁愿计算出它的结果放在表达式的右边都比函数计算要好 explain SELECT * FROM stu_innodb where id1 4;字符串不加引号出现隐式转换 explain SELECT * FROM stu_innodb where card_id 11;索引的最左前缀原则like条件中前面带。where条件中LIKE %001%、LIKE 001%、%001都用不到索引为什么? explain SELECT * FROM stu_innodb where card_id LIKE %001%;
explain SELECT * FROM stu_innodb where card_id LIKE 001%;
explain SELECT * FROM stu_innodb where card_id LIKE %001;过滤的开销太大。这个时候可以用全文索引。 负向查询 NOT LIKE不能:explain SELECT * FROM stu_innodb where card_id NOT LIKE 001%;和NOT IN在某些情况下可以:explain SELECT * FROM stu_innodb where card_id stu001;explain SELECT * FROM stu_innodb where card_id NOT LIKE 001%;其实InnoDB到底用不用索引最终都是优化器说了算。
优化器是基于什么的优化器 基于cost开销(Cost Base Optimizer)也叫基于成本的优化器成本包括两方面I/OCPU。它不是基于规则(Rule-Based Optimizer)的优化器也不是基于语义。怎么样开销小就怎么来。
比如你回家的路线有三条那么两个优化器会怎么做
基于规则(Rule-Based Optimizer)的优化器每次固定选择A路线基于成本(Cost Base Optimizer)的优化器会看当前哪条路到家最快(基于成本考虑)然后选择一条最合适的路线
也就是说使用索引有基本原则但是没有具体细则没有什么情况一定用索引什么情况一定不用索引的规则。
MySQL合集
MySQL1MySQL发展史MySQL流行分支及其对应存储引擎 MySQL2MySQL中一条查询SQL是如何执行的 MySQL3MySQL中一条更新SQL是如何执行的 MySQL4索引是什么索引类型索引存储模型发展1.二分查找2.二叉查找树3.平衡二叉树4.多路平衡查找树5. B树6.索引为什么不用红黑树7.InnoDB的hash索引指什么 MySQL5MySQL数据存储文件MylSAM中索引和数据是两个独立的文件它是如何通过索引找到数据呢聚集索引/聚簇索引InnoDB中二级索引为什么不存地址而是存键值row ID如何理解 MySQL6索引使用原则联合索引联合主键/复合主键覆盖索引、什么是回表索引条件下推索引的创建与使用索引的创建与使用索引失效
