索引

索引

---

概述

• 索引定义：索引（Index）是帮助MySQL高效获取数据的数据结构
• 索引本质：索引是数据结构
• 索引描述：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引
• 索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列
• 索引分类：
  
  • 单列索引：一个索引包含一列，一个表可以包含多个单列索引
    
    • 普通索引：
    • 唯一索引：可以为NULL，其他与主键索引相似
    • 主键索引：不可以为NULL
  • 联合索引：一个组合索引包含两个或两个以上的列

相关操作

创建索引

// 语法一
create INDEX index_name on table_name(column_name);  
// 示例
CREATE INDEX account_Index ON `award`(`account`); 
-- 注意字段上的符号，为数字1与TAB间之间的按钮，不是单引号

// 语法二
alter table table_name add INDEX index_name(column_name);  
// 示例
ALTER TABLE award ADD INDEX account_Index(`account`);  

// 语法三：建表时带有辅助索引（与主键索引相区别）
CREATE TABLE `p2p_td_project_advance_repayment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `repayment_date` date NOT NULL COMMENT '还款日期',
  `create_time` datetime NOT NULL COMMENT '记录创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_repayment_date` (`repayment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='提前还款表';

// 语法四：创建指定类型的索引
// 普通索引，索引名称：index_name（name为列名）
alter table table_name add index index_name (column_list) ;
// 唯一索引，索引名称：uniq_idx_name（name为列名）
alter table table_name add unique uniq_name(column_list) ;
// 主键索引（通常建表时直接指定主键）
alter table table_name add primary key primary_name(column_list) ;
// 复合索引，索引名称：union_index_name1_name2（复合索引，按照最左匹配原则，按照字段的使用频率从左至右依次递减）
alter table table_name add index index_name_a_b (column_list_a,column_list_b) ;

删除索引

DROP INDEX IndexName ON `TableName`；  

查看索引

show INDEX from `TableName`；

---

索引实现原理

B-Tree

基于 InnoDB 存储引擎的索引实现原理 与 基于 MyISAM 存储引擎的索引实现原理

存储引擎	InnoDB	MyISAM
存储结构	数据文件（主键索引与数据记录的组合）、日志文件	表定义文件、数据文件、索引文件
数据结构	B+Tree	B+Tree
索引类型	聚集索引	非聚集索引
叶子节点的键值KEY	表的主键	索引的相应字段的值
叶子节点的值域DATA	完整的数据记录	数据记录的地址（数据文件中的对应行数据的物理地址）
辅助索引DATA域	主键索引的键值（即若通过辅助索引检索，需检索两次，一次是查找对应的主键，一次是根据主键查找对应的数据行）	同主键索引，存储数据记录的地址（因为索引文件与数据文件是分开的）

---

问题：

• InnoDB存储引擎中的辅助索引文件如何存放？
• InnoDB存储引擎若表中主键非主键自增或无主键，数据文件中的主键如何处理？

---

Hash

---

优势

• 索引加快了检索的速度；在索引文件中检索，不必全表逐行比较

---

劣势

• 减慢了插入和删除的速度，同时还减慢了更新被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行，还需要改变所有的索引。数据表带有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大
• 索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制；在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储

---

建索引的几大原则

4.1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
4.2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
4.3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ‘2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(‘2014-05-29’);
4.5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

---

参考资料

• MySQL索引背后的数据结构及算法原理
• mysql索引学习—-2—-创建索引、修改索引、删除索引的命令语句
• 由浅入深探究mysql索引结构原理、性能分析与优化
• Mysql使用索引实现查询优化