索引优化，非重点

1.

-- 当一个字段同时有单列索引和联合索引时，会选择联合索引
create index idx_user_pro on tb_user(profession);
explain select * from tb_user where profession='软件工程';
-- ignore忽略某索引
explain select * from tb_user ignore index(idx_user_pro_age_stu)where profession='软件工程';
-- 强制使用某个索引
explain select * from tb_user force index (idx_user_pro)where profession='软件工程';

2

-- 覆盖索引
explain select id, profession from tb_user where profession = '软件工程' and age =
31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'
and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';

后两句出现回表查询，因为建立的索引只有profession,age,status,id，若还输出其他的则需回表查询

3.

-- 前缀索引，有些字段很长，以此为索引浪费效率
-- 表示以email字段的前五个元素为索引
create index idx_user_email_5 on tb_user(email(5));
show index from tb_user;
            -- distinct去重
select count(distinct (email))/count(*) from tb_user ;
-- 截取email前五个元素，统计去重后他们的个数与总字段个数比较，若不为1则表示email前五元素有重复的
select count(distinct (substring(email,1,5)))/count(*) from tb_user ;

4.主键优化

        在InnoDB引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

        页分裂：

                页可以为空，也可以填充一半，也可以填充100%。

                每个页包含了2-N行数据(如果一行数据过大，会行 溢出)，根据主键排列。

        页合并：

                当删除一行记录时，实际上记录并没有被物理删除，

                只是记录被标记（flaged）为删除并且它的空间 变得允许被其他记录声明使用。

        索引设计原则：

                索引设计原则 满足业务需求的情况下，尽量降低主键的长度。

                插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

                尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

                业务操作时，避免对主键的修改。

5.order by优化

create index idx_user_age_phone on tb_user(age,phone);
explain select id,age,phone from tb_user order by age,phone;-- using index
explain select id,age,phone from tb_user order by age desc;-- backward index反向索引
explain select id,age,phone from tb_user order by age desc,phone desc;-- backward index反向索引
-- 排序也存在最左前缀法则，且与出现顺序有关
explain select id,age,phone from tb_user order by phone,age;-- using filesort
-- 一个升序一个降序也行不通
explain select id,age,phone from tb_user order by age,phone desc ;-- using filesort
-- 除非创建索引时设置为一个升一个降，注意phone后加个_ad表示一个升一个降，da则相反
drop index idx_user_pro_age_stu on tb_user;
show index from tb_user;
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc );

6.group by优化

explain select count(*),age from tb_user group by age;-- 无索引
-- 分组也要遵循最左前缀法则
create index idx_user_pro_age_stu on tb_user(profession,age,status);

7.count优化

count(*) >= count(数字) > count(主键) > count(字段) 推荐使用 count(*)