有哪些做任务网站,国际 网站制作公司,企业门户网站建设公司,河南省建设工程质量协会网站我们知道一个字符串其实是由若干个字符组成#xff0c;如果我们在 MySQL 中使用 utf8 字符集去存储字符串的话#xff0c;编码一个字符需要占用 1~3 个字节。假设我们的字符串很长#xff0c;那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时…我们知道一个字符串其实是由若干个字符组成如果我们在 MySQL 中使用 utf8 字符集去存储字符串的话编码一个字符需要占用 1~3 个字节。假设我们的字符串很长那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时那就意味着在对应的 B 树中有这么两个问题
B 树索引中的记录需要把该列的完整字符串存储起来而且字符串越长在索引中占用的存储空间越大。如果 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 个字符的编码这种只索引字符串值的前缀的策略是我们非常鼓励的尤其是在字符串类型能存储的字符比较多的时候。
7.4.4.1 索引列前缀对排序的影响
如果使用了索引列前缀比方说前边只把 name 列的前10个字符放到了二级索引中下边这个查询可能就有点儿尴尬了
SELECT * FROM person_info ORDER BY name LIMIT 10;
因为二级索引中不包含完整的 name 列信息所以无法对前十个字符相同后边的字符不同的记录进行排序也就是使用索引列前缀的方式无法支持使用索引排序只好乖乖的用文件排序。
7.4.5 让索引列在比较表达式中单独出现
假设表中有一个整数列 my_col 我们为这个列建立了索引。下边的两个 WHERE 子句虽然语义是一致的但是在效率上却有差别
1. WHERE my_col *
2 42. WHERE my_col 4/2
第1个 WHERE 子句中 my_col 列并不是以单独列的形式出现的而是以 my_col * 2 这样的表达式的形式出现的存储引擎会依次遍历所有的记录计算这个表达式的值是不是小于 4 所以这种情况下是使用不到为 my_col 列建立的 B 树索引的。而第2个 WHERE 子句中 my_col 列并是以单独列的形式出现的这样的情况可以直接使用B 树索引。
所以结论就是如果索引列在比较表达式中不是以单独列的形式出现而是以某个表达式或者函数调用形式出现的话是用不到索引的。
7.4.6 主键插入顺序
我们知道对于一个使用 InnoDB 存储引擎的表来说在我们没有显式的创建索引时表中的数据实际上都是存储在 聚簇索引 的叶子节点的。而记录又是存储在数据页中的数据页和记录又是按照记录主键值从小到大的顺序进行排序所以如果我们插入的记录的主键值是依次增大的话那我们每插满一个数据页就换到下一个数据页继续插而如果我们插入的主键值忽大忽小的话这就比较麻烦了假设某个数据页存储的记录已经满了它存储的主键值在 1~100 之间 如果此时再插入一条主键值为 9 的记录那它插入的位置就如下图 可这个数据页已经满了啊再插进来咋办呢我们需要把当前页面分裂成两个页面把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么意味着性能损耗所以如果我们想尽量避免这样无谓的性能损耗最好让插入的记录的主键值依次递增这样就不会发生这样的性能损耗了。所以我们建议让主键具有 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 属性在插入记录时存储引擎会自动为我们填入自增的主键值。
7.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 既是主键、又给它定义为一个唯一索引还给它定义了一个普通索引可是主键本身就会生成聚簇索引所以定义的唯一索引和普通索引是重复的这种情况要避免。
7.5 总结
B 树索引在空间和时间上都有代价所以没事儿别瞎建索引。B 树索引适用于下边这些情况
全值匹配匹配左边的列匹配范围值精确匹配某一列并范围匹配另外一列用于排序用于分组
在使用索引时需要注意下边这些事项
只为用于搜索、排序或分组的列创建索引为列的基数大的列创建索引索引列的类型尽量小可以只对字符串值的前缀建立索引只有索引列在比较表达式中单独出现才可以适用索引为了尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况建议让主键拥有 AUTO_INCREMENT 属性。定位并删除表中的重复和冗余索引尽量使用 覆盖索引 进行查询避免 回表 带来的性能损耗。
