索引优化
一、索引基本
1.概念及删除:
1.1 索引是存储引擎快速找到记录的一种数据结构。
1.2 索引不能修改,只能删除重建。删除方法:
DROP INDEX warehouse_id ON job_summary;
2.索引类型及创建:
SHOW INDEX FROM job_summary;
2.1 主键索引 PRIMARY KEY: 特殊的唯一索引,通常在建表同时建立(如id),不允许重复和空值。
2.2 唯一索引 UNIQUE: 唯一索引(如果是组合唯一索引则列组合的值必须唯一)所在列的值必须唯一,可以为空值。创建方法:
ALTER TABLE job_summary ADD UNIQUE (`warehouse_id`);
2.3 组合索引 INDEX: 一个索引包含多个列,常用于避免回表查询。创建方法:
ALTER TABLE job_summary ADD INDEX warehouseid_summarizerid(`warehouse_id`,`summarizer_id`);
2.4 普通索引 INDEX: 最基本也是用的最多的索引,没有任何限制。创建方法:
ALTER TABLE job_summary ADD INDEX create_time(`create_time`);
2.5 全文索引 FULLTEXT: 全文检索,搜索引擎的重要技术。创建方法:
ALTER TABLE job_summary ADD FULLTEXT job_content(`job_content`);
二、索引优化
1. 查看索引性能
1.1 通过EXPLAIN语句
创建的表及key如下:
CREATE TABLE `invoice_company` (
`id` char(32) NOT NULL,
`warehouseid` char(32) DEFAULT NULL,
`name` varchar(200) DEFAULT NULL,
`type` char(1) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `invoice_company_key1` (`warehouseid`,`type`) USING BTREE,
KEY `invoice_company_key2` (`warehouseid`) USING BTREE,
KEY `invoice_company_key3` (`warehouseid`,`type`,`name`) USING BTREE,
KEY `invoice_company_key4` (`type`) USING BTREE,
KEY `invoice_company_key5` (`name`) USING BTREE,
KEY `invoice_company_key6` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
其中的key如下:
// 例1语句如下:
EXPLAIN SELECT * FROM invoice_company WHERE type='A';
// 例2语句如下:
EXPLAIN SELECT * FROM invoice_company WHERE name LIKE '产品销售有限公司%';
//例3语句如下:
EXPLAIN SELECT * FROM invoice_company WHERE name LIKE '%产品销售有限公司%';
//例3语句如下:
EXPLAIN SELECT * FROM invoice_company WHERE type='A' AND name LIKE '产品销售有限公司%';
例1结果如下:
例2结果如下:
例3结果如下:
例4结果如下:
其中:
key: 表示该查询所用到的索引,若为空则代表未用到任何索引。
filtered: 返回记录比例数,或者说跟据索引能查到记录占总记录的比例。比例越高,走索引的命中率越高。一般来说,大于30%会走索引,否则全表扫描。
Extra: Using where表示不走索引查询 – 即需要通过回表查询结果,而Using Index则表示走索引查询 - 即直接通过索引就能查询到主要数据。
1.2 通过SHOW STATUS语句
SHOW STATUS LIKE 'Handler_read%';
其中:
Handler_read_key: 值很高说明索引当前正在使用的数量多
Handler_read_rnd_next: 数据文件中读取下一行的请求数,如果正在进行大量的全表扫描,
值会比较高,值越高说明索引的利用效果越差。
2.索引优化
2.1 建立的索引及其使用要比全表扫描快
否则将全表扫描。( 具体看上面的filtered值是否在30%内)
2.2 根据最左原则使用索引
避免like 前缀查询(上面例2、3)、组合索引避免缺少索引左列导致索引失效(上面例4)
2.3 尽量用in
因为union要多查一步更耗cpu,而or的条件列中有索引,其后面列如果没有索引涉及到的索引都会失效,另外像负向条件查询:!=、<>、not in、not exists、not like都不会使用索引
2.4 范围条件查询可以命中索引
范围条件查询:<、<=、>、>=、between等
不过:
2.4.1 范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引;
2.4.2 范围查询和等值查询同时存在,优先匹配等值查询列的索引;
2.5 条件查询列如果有计算、函数不会用到索引
例如:
EXPLAIN SELECT id,warehouseid, name FROM invoice_company WHERE DATE_FORMAT(create_time,"%Y-%m-%d") >= '2021-09-30'
结果:
3.explain结果中type字段
3.1 定义
找到所需数据使用的扫描方式
3.2 方式(从前到后扫描速度由快到慢)
- system: 系统表,少量数据,一般不需要进行磁盘IO
EXPLAIN SELECT * FROM mysql.time_zone_name
- const: 常量连接
- eq_ref: 主键索引或者非空唯一索引等值扫描
- ref: 非主键非唯一索引等值扫描
- range: 范围扫描
- index: 索引树扫描
- all: 全表扫描
其他
一、分析不走索引的其他原因
未走索引可以再用 force index()强制执行看是否走
1.表或索引字段的字符集格式不同
2.索引字段基数太小
但如果又需要用到,则可以用上述强制执行索引方法。
随心所往,看见未来。Follow your heart,see light!
欢迎点赞、关注、留言,一起学习、交流!