四川省建设厅的注册中心网站首页,网站快照明天更新是什么情况,详细网络设计方案,微信网站是多少钱一年1.索引的代价 在介绍如何更好的使用索引之前先要了解一下使用这玩意儿的代价#xff0c;它在空间和时间上都会拖后腿#xff1a; (1). 空间上的代价 这个是显而易见的#xff0c;每建立一个索引都要为它建立一棵 B 树#xff0c;每一棵 B 树的每一个节点都是一个数据页它在空间和时间上都会拖后腿 (1). 空间上的代价 这个是显而易见的每建立一个索引都要为它建立一棵 B 树每一棵 B 树的每一个节点都是一个数据页一个页默认会占用 16KB 的存储空间一棵很大的 B 树由许多数据页组成那可是很大的一片存储空间呢。
(2). 时间上的代价 每次对表中的数据进行增、删、改操作时都需要去修改各个 B 树索引。而且我们讲过 B 树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录还是内节点中的记录也就是不论是用户记录还是目录项记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏所以存储引擎需要额外的时间进行一些记录移位页面分裂、页面回收啥的操作来维护好节点和记录的排序。
所以说一个表上索引建的越多就会占用越多的存储空间在增删改记录的时候性能就越差。为了能建立又好又少的索引我们先得学学这些索引在哪些条件下起作用的。
2.B树索引适用的条件 首先 B 树索引并不是万能的并不是所有的查询语句都能用到我们建立的索引。 为了故事的顺利发展我们需要先创建一个表这个表是用来存储人的一些基本信息的
CREATE TABLE person_info(id INT NOT NULL auto_increment,name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,PRIMARY KEY (id),KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);一个表中有多少索引就会建立多少棵 B 树 person_info 表会为聚簇索引和 idx_name_birthday_phone_number 索引建立2棵 B 树。下边我们画一下索引 idx_name_birthday_phone_number 的示意图不过既然我们已经掌握了 InnoDB 的 B 树索引原理那我们在画图的时候为了让图更加清晰所以在省略一些不必要的部分比如记录的额外信息各页面的页号等等其中内节点中目录项记录的页号信息我们用箭头来代替。 从图中可以看出这个 idx_name_birthday_phone_number 索引对应的 B 树中页面和记录的排序方式就是这样的 (1). 先按照 name 列的值进行排序。 (2). 如果 name 列的值相同则按照 birthday 列的值进行排序。 (3). 如果 birthday 列的值也相同则按照 phone_number 的值进行排序。
2.1.全值匹配 如果我们的搜索条件中的列和索引列一致的话这种情况就称为全值匹配比方说下边这个查找语句
SELECT * FROM person_info WHERE name Ashburn AND birthday 1990-09-27 AND phone_num
ber 15123983239;这种情况可以借助索引进行记录定位。
2.2.匹配左边的列 其实在我们的搜索语句中也可以不用包含全部联合索引中的列只包含左边的就行比方说下边的查询语句
SELECT * FROM person_info WHERE name Ashburn;或者包含多个左边的列也行
SELECT * FROM person_info WHERE name Ashburn AND birthday 1990-09-27;那为什么搜索条件中必须出现左边的列才可以使用到这个 B 树索引呢 如果我们想使用联合索引中尽可能多的列搜索条件中的各个列必须是联合索引中从最左边连续的列。 比方说联合索引 idx_name_birthday_phone_number 中列的定义顺序是 name 、birthday 、 phone_number 如果我们的搜索条件中只有 name 和 phone_number 而没有中间的 birthday 比方说这样
SELECT * FROM person_info WHERE name Ashburn AND phone_number 15123983239;这样只能用到 name 列的索引 birthday 和 phone_number 的索引就用不上了因为 name 值相同的记录先按照 birthday 的值进行排序 birthday 值相同的记录才按照 phone_number 值进行排序。
2.3.匹配列前缀 我们前边说过为某个列建立索引的意思其实就是在对应的 B 树的记录中使用该列的值进行排序比方说 person_info 表上建立的联合索引 idx_name_birthday_phone_number 会先用 name 列的值进行排序所以这个联合索引对应的 B 树中的记录的 name 列的排列就是这样的
Aaron
Aaron
...
Aaron
Asa
Ashburn
...
Ashburn
Baird
Barlow
...
Barlow比较字符串大小就用到了该列的字符集和比较规则。这里需要注意的是一般的比较规则都是逐个比较字符的大小也就是说我们比较两个字符串的大小的过程其实是这样的 (1). 先比较字符串的第一个字符第一个字符小的那个字符串就比较小。 (2). 如果两个字符串的第一个字符相同那就再比较第二个字符第二个字符比较小的那个字符串就比较小。 (3). 如果两个字符串的第二个字符也相同那就接着比较第三个字符依此类推。
也就是说这些字符串的前 n 个字符也就是前缀都是排好序的所以对于字符串类型的索引列来说我们只匹配它的前缀也是可以快速定位记录的比方说我们想查询名字以 ‘As’ 开头的记录那就可以这么写查询语句
SELECT * FROM person_info WHERE name LIKE As%;但是需要注意的是如果只给出后缀或者中间的某个字符串比如这样
SELECT * FROM person_info WHERE name LIKE %As%;则无法借助索引来定位。因为字符串中间有 ‘As’ 的字符串并没有排好序所以只能全表扫描了。
2.4.匹配范围值 回头看我们 idx_name_birthday_phone_number 索引的 B 树示意图所有记录都是按照索引列的值从小到大的顺序排好序的所以这极大的方便我们查找索引列的值在某个范围内的记录。比方说下边这个查询语句
SELECT * FROM person_info WHERE name Asa AND name Barlow;由于 B 树中的数据页和记录是先按 name 列排序的所以我们上边的查询过程其实是这样的 (1). 找到 name 值为 Asa 的记录。 (2). 找到 name 值为 Barlow 的记录。 (3). 哦啦由于所有记录都是由链表连起来的记录之间用单链表数据页之间用双链表所以他们之间的记录都可以很容易的取出来喽。 (4). 找到这些记录的主键值再到 聚簇索引 中 回表 查找完整的记录。
不过在使用联合进行范围查找的时候需要注意如果对多个列同时进行范围查找的话只有对索引最左边的那个列进行范围查找的时候才能用到 B 树索引比方说这样
SELECT * FROM person_info WHERE name Asa AND name Barlow AND birthday 1980-01-01;上边这个查询可以分成两个部分 (1). 通过条件 name ‘Asa’ AND name ‘Barlow’ 来对 name 进行范围查找的结果可能有多条 name 值不同的记录 (2). 对这些 name 值不同的记录继续通过 birthday ‘1980-01-01’ 条件继续过滤。
这样子对于联合索引 idx_name_birthday_phone_number 来说只能用到 name 列的部分而用不到 birthday 列的部分因为只有 name 值相同的情况下才能用 birthday 列的值进行排序而这个查询中通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的所以在搜索条件中继续以 birthday 列进行查找时是用不到这个 B 树索引的。
2.5. 精确匹配某一列并范围匹配另外一列 对于同一个联合索引来说虽然对多个列都进行范围查找时只能用到最左边那个索引列但是如果左边的列是精确查找则右边的列可以进行范围查找比方说这样
SELECT * FROM person_info WHERE name Ashburn AND birthday 1980-01-01 AND birthday 2000-12-31 AND phone_number 15100000000;这个查询的条件可以分为3个部分 (1). name ‘Ashburn’ 对 name 列进行精确查找当然可以使用 B 树索引了。 (2). birthday ‘1980-01-01’ AND birthday ‘2000-12-31’ 由于 name 列是精确查找所以通过 name ‘Ashburn’ 条件查找后得到的结果的 name 值都是相同的它们会再按照 birthday 的值进行排序。所以此时对 birthday 列进行范围查找是可以用到 B 树索引的。 (3). phone_number ‘15100000000’ 通过 birthday 的范围查找的记录的 birthday 的值可能不同所以这个条件无法再利用 B 树索引了只能遍历上一步查询得到的记录。
2.6. 用于排序 我们在写查询语句的时候经常需要对查询出来的记录通过 ORDER BY 子句按照某种规则进行排序。一般情况下我们只能把记录都加载到内存中再用一些排序算法比如快速排序、归并排序、吧啦吧啦排序等等在内存中对这些记录进行排序有的时候可能查询的结果集太大以至于不能在内存中进行排序的话还可能暂时借助磁盘的空间来存放中间结果排序操作完成后再把排好序的结果集返回到客户端。在 MySQL 中把这种在内存中或者磁盘上进行排序的方式统称为文件排序。
但是如果 ORDER BY 子句里使用到了我们的索引列就有可能省去在内存或文件中排序的步骤比如下边这个简单的查询语句
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;因为这个 B 树索引本身就是按照上述规则排好序的所以直接从索引中提取数据然后进行 回表 操作取出该索引中不包含的列就好了。
2.6.1. 使用联合索引进行排序注意事项 对于 联合索引 有个问题需要注意 ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出如果给出 ORDER BY phone_number, birthday, name 的顺序那也是用不了 B 树索引这种颠倒顺序就不能使用索引的。同理 ORDER BY name 、 ORDER BY name, birthday 这种匹配索引左边的列的形式可以使用部分的 B 树索引。当联合索引左边列的值为常量也可以使用后边的列进行排序比如这样
SELECT * FROM person_info WHERE name A ORDER BY birthday, phone_number LIMIT 10;这个查询能使用联合索引进行排序是因为 name 列的值相同的记录是按照 birthday , phone_number 排序的。
2.6.2.不可以使用索引进行排序的几种情况 (1). ASC、DESC混用 对于使用联合索引进行排序的场景我们要求各个排序列的排序顺序是一致的也就是要么各个列都是 ASC 规则排序要么都是 DESC 规则排序。
如果查询中的各个排序列的排序顺序是一致的比方说下边这两种情况 a. ORDER BY name, birthday LIMIT 10 这种情况直接从索引的最左边开始往右读10行记录就可以了。 b. ORDER BY name, birthday LIMIT 10 这种情况直接从索引的最右边开始往左读10行记录就可以了。
但是如果我们查询的需求是先按照 name 列进行升序排列再按照 birthday 列进行降序排列的话比如说这样的查询语句
SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;这样如果使用索引排序的话过程就是这样的 (1). 先从索引的最左边确定 name 列最小的值然后找到 name 列等于该值的所有记录然后从 name 列等于该值的最右边的那条记录开始往左找10条记录。 (2). 如果 name 列等于最小的值的记录不足10条再继续往右找 name 值第二小的记录重复上边那个过程直到找到10条记录为止。
这样不能高效使用索引而要采取更复杂的算法去从索引中取数据设计 MySQL 的大叔觉得这样还不如直接文件排序来的快所以就规定使用联合索引的各个排序列的排序顺序必须是一致的。
(2). WHERE子句中出现非排序使用到的索引列 如果WHERE子句中出现了非排序使用到的索引列那么排序依然是使用不到索引的比方说这样
SELECT * FROM person_info WHERE country China ORDER BY name LIMIT 10;这个查询只能先把符合搜索条件 country ‘China’ 的记录提取出来后再进行排序是使用不到索引。注意和下边这个查询作区别
SELECT * FROM person_info WHERE name A ORDER BY birthday, phone_number LIMIT 10;虽然这个查询也有搜索条件但是 name ‘A’ 可以使用到索引 idx_name_birthday_phone_number 而且过滤剩下的记录还是按照 birthday 、 phone_number 列排序的所以还是可以使用索引进行排序的。
(3). 排序列包含非同一个索引的列 有时候用来排序的多个列不是一个索引里的这种情况也不能使用索引进行排序比方说
SELECT * FROM person_info ORDER BY name, country LIMIT 10;(4). 排序列使用了复杂的表达式 要想使用索引进行排序操作必须保证索引列是以单独列的形式出现而不是修饰过的形式比方说这样
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;2.7. 用于分组 有时候我们为了方便统计表中的一些信息会把表中的记录按照某些列进行分组。比如下边这个分组查询
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number这个查询语句相当于做了3次分组操作 (1). 先把记录按照 name 值进行分组所有 name 值相同的记录划分为一组。 (2). 将每个 name 值相同的分组里的记录再按照 birthday 的值进行分组将 birthday 值相同的记录放到一个小分组里所以看起来就像在一个大分组里又化分了好多小分组。 (3). 再将上一步中产生的小分组按照 phone_number 的值分成更小的分组所以整体上看起来就像是先把记录分成一个大分组然后把 大分组 分成若干个 小分组 然后把若干个 小分组 再细分成更多的 小小分组 。
然后针对那些 小小分组 进行统计比如在我们这个查询语句中就是统计每个 小小分组 包含的记录条数。如果没有索引的话这个分组过程全部需要在内存里实现而如果有了索引的话恰巧这个分组顺序又和我们的 B 树中的索引列的顺序是一致的而我们的 B 树索引又是按照索引列排好序的这不正好么所以可以直接使用B 树索引进行分组。
3.回表的代价 需要回表的记录越多使用二级索引的性能就越低。甚至让某些查询宁愿使用全表扫描也不使用 二级索引 。
那什么时候采用全表扫描的方式什么时候使用采用 二级索引 回表 的方式去执行查询呢 这个就是查询优化器做的工作查询优化器会事先对表中的记录计算一些统计数据然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数需要回表的记录数越多就越倾向于使用全表扫描反之倾向于使用 二级索引 回表 的方式。
对于有排序需求的查询上边讨论的采用 全表扫描 还是 二级索引 回表 的方式进行查询的条件也是成立的比方说下边这个查询
SELECT * FROM person_info ORDER BY name, birthday, phone_number;由于查询列表是 * 所以如果使用二级索引进行排序的话需要把排序完的二级索引记录全部进行回表操作这样操作的成本还不如直接遍历聚簇索引然后再进行文件排序 filesort 低所以优化器会倾向于使用 全表扫描 的方式执行查询。如果我们加了 LIMIT 子句比如这样
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;这样需要回表的记录特别少优化器就会倾向于使用 二级索引 回表 的方式执行查询。
3.1.覆盖索引 为了彻底告别 回表 操作带来的性能损耗我们建议最好在查询列表里只包含索引列比如这样
SELECT name, birthday, phone_number FROM person_info WHERE name Asa AND name Barlow因为我们只查询 name , birthday , phone_number 这三个索引列的值所以在通过idx_name_birthday_phone_number 索引得到结果后就不必到 聚簇索引 中再查找记录的剩余列也就是 country 列的值了这样就省去了 回表 操作带来的性能损耗。我们把这种只需要用到索引的查询方式称为 索引覆盖 。排序操作也优先使用 覆盖索引 的方式进行查询比方说这个查询
SELECT name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_number;虽然这个查询中没有 LIMIT 子句但是采用了 覆盖索引 所以查询优化器就会直接使用 idx_name_birthday_phone_number 索引进行排序而不需要回表操作了。
4.如何挑选索引 4.1.只为用于搜索、排序或分组的列创建索引 也就是说只为出现在 WHERE 子句中的列、连接子句中的连接列或者出现在 ORDER BY 或 GROUP BY 子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了
SELECT birthday, country FROM person name WHERE name Ashburn;像查询列表中的 birthday 、 country 这两个列就不需要建立索引我们只需要为出现在 WHERE 子句中的 name 列创建索引就可以了。
4.2.考虑列的基数 列的基数 指的是某一列中不重复数据的个数比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 虽然有 9 条记录但该列的基数却是 3 。也就是说在记录行数一定的情况下列的基数越大该列中的值越分散列的基数越小该列中的值越集中。这个 列的基数 指标非常重要直接影响我们是否能有效的利用索引。
最好为那些列的基数大的列建立索引为基数太小列的建立索引效果可能不好。
4.3.索引列的类型尽量小 我们这里所说的 类型大小 指的就是该类型表示的数据范围的大小。如果我们想要对某个整数列建立索引的话在表示的整数范围允许的情况下尽量让索引列使用较小的类型比如我们能使用 INT 就不要使用 BIGINT 能使用 MEDIUMINT 就不要使用 INT 这是因为 (1). 数据类型越小在查询时进行的比较操作越快这是CPU层次的东东 (2). 数据类型越小索引占用的存储空间就越少在一个数据页内就可以放下更多的记录从而减少磁盘 I/O 带来的性能损耗也就意味着可以把更多的数据页缓存在内存中从而加快读写效率。
4.4.索引字符串值的前缀 我们知道一个字符串其实是由若干个字符组成如果我们在 MySQL 中使用 utf8 字符集去存储字符串的话编码一个字符需要占用 1~3 个字节。假设我们的字符串很长那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时那就意味着在对应的 B 树中有这么两个问题 (1). B 树索引中的记录需要把该列的完整字符串存储起来而且字符串越长在索引中占用的存储空间越大。 (2). 如果 B 树索引中索引列存储的字符串很长那在做字符串比较时会占用更多的时间。
我们前边儿说过索引列的字符串前缀其实也是排好序的所以索引的设计者提出了个方案 — 只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置但是能定位到相应前缀所在的位置然后根据前缀相同的记录的主键值回表查询完整的字符串值再对比就好了。这样只在 B 树中存储字符串的前几个字符的编码既节约空间又减少了字符串的比较时间还大概能解决排序的问题何乐而不为比方说我们在建表语句中只对 name 列的前10个字符进行索引可以这么写
CREATE TABLE person_info(name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);name(10) 就表示在建立的 B 树索引中只保留记录的前 10 个字符的编码这种只索引字符串值的前缀的策略是我们非常鼓励的尤其是在字符串类型能存储的字符比较多的时候。
4.4.1.索引列前缀对排序的影响 如果使用了索引列前缀比方说前边只把 name 列的前10个字符放到了二级索引中下边这个查询可能就有点儿尴尬了
SELECT * FROM person_info ORDER BY name LIMIT 10;因为二级索引中不包含完整的 name 列信息所以无法对前十个字符相同后边的字符不同的记录进行排序也就是使用索引列前缀的方式无法支持使用索引排序只好乖乖的用文件排序喽。
4.5.让索引列在比较表达式中单独出现 假设表中有一个整数列 my_col 我们为这个列建立了索引。下边的两个 WHERE 子句虽然语义是一致的但是在效率上却有差别 (1). WHERE my_col * 2 4 (2). WHERE my_col 4/2 针对第一个存储引擎会依次遍历所有的记录计算这个表达式的值是不是小于 4 所以这种情况下是使用不到为 my_col 列建立的 B 树索引的。 针对第二个WHERE 子句中 my_col 列并是以单独列的形式出现的这样的情况可以直接使用 B 树索引。
所以结论就是如果索引列在比较表达式中不是以单独列的形式出现而是以某个表达式或者函数调用形式出现的话是用不到索引的。
4.6. 主键插入顺序 所以如果我们插入的记录的主键值是依次增大的话那我们每插满一个数据页就换到下一个数据页继续插而如果我们插入的主键值忽大忽小的话这就比较麻烦了假设某个数据页存储的记录已经满了它存储的主键值在 1~100 之间 可这个数据页已经满了啊再插进来咋办呢我们需要把当前页面分裂成两个页面把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么意味着性能损耗所以如果我们想尽量避免这样无谓的性能损耗最好让插入的记录的主键值依次递增这样就不会发生这样的性能损耗了。所以我们建议让主键具有 AUTO_INCREMENT 让存储引擎自己为表生成主键而不是我们手动插入 比方说我们可以这样定义 person_info 表
CREATE TABLE person_info(id INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,PRIMARY KEY (id),KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性在插入记录时存储引擎会自动为我们填入自增的主键值。
4.7. 冗余和重复索引
CREATE TABLE person_info(id INT UNSIGNED NOT NULL AUTO_INCREMENT,name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,PRIMARY KEY (id),KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),KEY idx_name (name(10))
);我们知道通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索再创建一个专门针对 name 列的索引就算是一个 冗余 索引维护这个索引只会增加维护的成本并不会对搜索有什么好处。
另一种情况我们可能会对某个列重复建立索引比方说这样
CREATE TABLE repeat_index_demo (c1 INT PRIMARY KEY,c2 INT,UNIQUE uidx_c1 (c1),INDEX idx_c1 (c1)
);我们看到 c1 既是主键、又给它定义为一个唯一索引还给它定义了一个普通索引可是主键本身就会生成聚簇索引所以定义的唯一索引和普通索引是重复的这种情况要避免。
