MySQL知识体系——索引

MySQL可以使用索引来扫描人 zipcode='95054'。第二部分（lastname LIKE '%etrunia%'）不能用于限制必须扫描的行数，因此如果没有Index Condition Pushdown，此查询必须为所有拥有的人检索完整的表行 zipcode='95054'。

使用索引条件下推，MySQL lastname LIKE '%etrunia%'在读取整个表行之前检查该 部分。这样可以避免读取与索引元组相对应的完整行，这些行匹配 zipcode条件而不是 lastname条件。

默认情况下启用索引条件下推。可以optimizer_switch通过设置index_condition_pushdown标志来控制 系统变量 ：   

SET optimizer_switch = 'index_condition_pushdown=off';  
     SET optimizer_switch = 'index_condition_pushdown=on'; 

实践

*注意语句中的“[ ··· ]”中括号指代变量，书写时记得去掉

普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

1. 创建索引

CREATE INDEX indexName ON mytable(username(length)); 

如果不是字符类型的字段，如int，则不要指定length；如果是CHAR，VARCHAR类型，length可以不指定，也可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2. 修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName) 

3. 创建表的时候直接指定

CREATE TABLE mytable(     
ID INT NOT NULL,      
username VARCHAR(16) NOT NULL,    
INDEX [indexName] (username(length))     
); 

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

1. 创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

2. 修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length)) 

3. 创建表的时候直接指定

CREATE TABLE mytable(     
ID INT NOT NULL,      
username VARCHAR(16) NOT NULL,    
UNIQUE [indexName] (username(length))     
); 

删除索引的语法

DROP INDEX [indexName] ON mytable; 

总结 

  合理利用索引对于提升数据库的性能、减轻数据库服务器的负担是最直接有效的手段。

  其实，索引的本质就是通过缩小范围、把随机事件变成顺序事件来筛选出最终结果，同时可以总是用同一种查找方式来定位数据，这样就可以兼顾高效率和稳定性。

【编辑推荐】

1. 数据库 | 一次非常有趣的SQL优化经历
2. 你在 Docker 中跑 MySQL？恭喜你，好下岗了！
3. Oracle迁移到MySQL，必须要搞明白这几个问题
4. 查询数据库，你还在 Select * 吗？
5. 抛开复杂的架构设计，MySQL优化思想基本都在这了

（编辑：辽源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!