MySql（七）：索引优化

使用索引(索引设计)原则：

1、索引key值字节越小越好

• 因为当索引key值占用的字节越小时，每个索引节点上指针大小固定，每个数据块能存的key越多，索引树的高度越低，检索越快。

 

2、ID作为主键且让id自增

• 索引维护涉及到：页分裂、页合并
• 页分裂指每个磁盘块大小固定，当新增数据在一个已经存满的磁盘块key范围中时，为了插入该新增数据，则会进行磁盘块分成两块。分裂后上层磁盘块需要增加新的key和指针，也有可能上级磁盘块也需要分裂，所以之前的磁盘块及会收到影响。
• id自增主键的维护只是往后叠加，对前面数据索引影响小，减少页分裂/页合并；

 

3、索引数量根据业务需求来创建，但并不是越多越好

• 索引是提高查询效率的，但索引过多，使得索引的维护成本变高，数据的更新变慢。并不是索引越多就越快，mysql优化器会选择合适的索引进行数据检索。
• 单表索引建议控制在5个内。

 

4、一个表无论有多少索引，数据只会存储一份

• 在innodb中，数据插入时，是必须跟聚簇索引存储在一起；
• 聚簇索引的选择：主键索引 > 唯一索引 > row_id（数据行隐藏的列）；如果表中有主键索引，数据跟主键索引存储在一起，如果没有主键，则和唯一索引存在一起，如果没有唯一索引，会生成一个6字节的rowid(行id)，数据跟rowid存储在一起。
• 非聚簇索引的数据存放都是对应数据所在聚簇索引的key值(主键/唯一键/rowid)。

 

5、更新十分频繁，数据区分度不高的列上不宜建立索引

• 更新频繁会降低数据库写的效率，每次写入都会涉及索引的维护；
• 区分度不大的列，建立索引不能有效的过滤数据，索引意义不大；
• 区分度在80%上的时候可以建立索引，区分度可以使用count(distinct 列名)/count(*)来计算。

 

 

使用索引查询优化原则：

// 数据准备
create table `employees`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
    `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
    `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
    `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
    PRIMARY KEY(`id`),
    KEY `idx_name_age_position` (`name`, `age`, `position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '员工记录表';

// 数据插入
insert into `employees&