mysql(四)通过索引进行优化-优快云博客

本文链接：https://blog.youkuaiyun.com/ypqdcsdn/article/details/127399006

索引基本知识

在这里插入图片描述

哈希索引

在这里插入图片描述

组合索引

在这里插入图片描述

聚簇索引与非聚簇索引

在这里插入图片描述

覆盖索引

在这里插入图片描述

优化小细节

当使用索引列进行查询的时候尽量不要使用表达式,把计算放到业务层而不是数据层
selecr actor_id from actor where id = 4;
select actor_id from actor where id+1=5;
尽量使用主键查询,而不是其他索引,因此主键查询不会触发回表查询
使用前缀索引

前缀索引实例说明:
有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

在这里插入图片描述
4. 使用索引扫描来排序

使用索引扫描来做排序:mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序

在这里插入图片描述
5. union,all,in,or 都能够使用索引,但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;explain select * from actor where actor_id in (1,2); explain select * from actor where actor_id = 1 or actor_id =2;

范围列可以用到索引

范围条件是:<,>范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列

强制类型转换会全表扫描

create table user(id int,name varchar(10),phone varchar(11));alter table user add index idx_1(phone);
explain select * from user where phone=13800001234; (不会触发索引)
explain select * from user where phone=‘13800001234’;(会触发索引)

更新十分频繁,数据区分度不高的字段上不宜建立索引

1.更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能
2.类似于性别这类区分不大的属性,建立索引是没有意义的,不能有效的过滤数据
3.一般区分在80%以上的时候就可以建立索引,区分度可以使用count(distinct(列名))/count(*)来计算

创建索引的列,不允许为null,可能会得到不符合预期的结果
当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致
能使用limit的时候尽量使用limit
单表索引建议控制在5个以内
单索引字段数不允许超过5个(组合索引)
创建索引的时候应该避免以下错误概念

1.索引越多越好
2.过早优化,在不了解系统的情况下进行优化

索引监控

在这里插入图片描述

预先准备好数据 SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS itdragon_order_list;
CREATE TABLE itdragon_order_list ( id bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键id，默认自增长’, transaction_id varchar(150) DEFAULT NULL COMMENT ‘交易号’, gross double DEFAULT NULL COMMENT ‘毛收入(RMB)’, net double DEFAULT NULL COMMENT ‘净收入(RMB)’, stock_id int(11) DEFAULT NULL COMMENT ‘发货仓库’, order_status int(11) DEFAULT NULL COMMENT ‘订单状态’, descript varchar(255) DEFAULT NULL COMMENT ‘客服备注’, finance_descript varchar(255) DEFAULT NULL COMMENT ‘财务备注’, create_type varchar(100) DEFAULT NULL COMMENT ‘创建类型’, order_level int(11) DEFAULT NULL COMMENT ‘订单级别’, input_user varchar(20) DEFAULT NULL COMMENT ‘录入人’, input_date varchar(20) DEFAULT NULL COMMENT ‘录入时间’, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

INSERT INTO itdragon_order_list VALUES (‘10000’, ‘81X97310V32236260E’, ‘6.6’, ‘6.13’, ‘1’, ‘10’, ‘ok’, ‘ok’, ‘auto’, ‘1’, ‘itdragon’, ‘2017-08-28 17:01:49’); INSERT INTO itdragon_order_list VALUES (‘10001’, ‘61525478BB371361Q’, ‘18.88’, ‘18.79’, ‘1’, ‘10’, ‘ok’, ‘ok’, ‘auto’, ‘1’, ‘itdragon’, ‘2017-08-18 17:01:50’); INSERT INTO itdragon_order_list VALUES (‘10002’, ‘5RT64180WE555861V’, ‘20.18’, ‘20.17’, ‘1’, ‘10’, ‘ok’, ‘ok’, ‘auto’, ‘1’, ‘itdragon’, ‘2017-09-08 17:01:49’);