雄安网站建设优化公司,wordpress访问权限,集团网站开发多少钱,织梦cms wordpress数据库调优的维度#xff1a;
索引建立SQL优化#xff08;本文重点#xff09;my.cnf的调整#xff08;线程数#xff0c;缓存等#xff09;分库分表
SQL查询优化的技术从大方向上可以分为 物理查询优化#xff0c;逻辑查询优化
物理查询优化#xff1a;即通过建立索…数据库调优的维度
索引建立SQL优化本文重点my.cnf的调整线程数缓存等分库分表
SQL查询优化的技术从大方向上可以分为 物理查询优化逻辑查询优化
物理查询优化即通过建立索引表连接的方式来进行优化逻辑查询优化SQL等价变换提升效率
1. 数据准备
学员表 插 50万 条 班级表 插 1万 条。
CREATE TABLE class (
id INT(11) NOT NULL AUTO_INCREMENT,
className VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
monitor INT NULL ,
PRIMARY KEY (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
stuno INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
classId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
#CONSTRAINT fk_class_id FOREIGN KEY (classId) REFERENCES t_class (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;命令开启允许创建函数设置
set global log_bin_trust_function_creators1; # 不加global只是当前窗口有效。创建函数
保证每条数据都不同。随机产生字符串
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ;
DECLARE return_str VARCHAR(255) DEFAULT ;
DECLARE i INT DEFAULT 0;
WHILE i n DO
SET return_str CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));
SET i i 1;
END WHILE;
RETURN return_str;
END 随机产生班级编号
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i FLOOR(from_num RAND()*(to_num - from_num1)) ;
RETURN i;
END创建存储过程
#创建往stu表中插入数据的存储过程
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit 0; #设置手动提交事务
REPEAT #循环
SET i i 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((STARTi),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i max_num
END REPEAT;
COMMIT; #提交事务
END创建往class表中插入数据的存储过程
CREATE PROCEDURE insert_class( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit 0;
REPEAT
SET i i 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i max_num
END REPEAT;
commit;
END#执行存储过程往class表添加1万条数据
CALL insert_class(10000);执行存储过程往stu表添加50万条数据
CALL insert_stu(100000,500000);# 删除索引存储过程
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT ;
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schemadbname AND table_nametablename AND
seq_in_index1 AND index_name PRIMARY ;
#每个游标必须使用不同的declare continue handler for not found set done1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index DO
SET str CONCAT(drop index , _index , on , tablename );
PREPARE sql_str FROM str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index;
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END2. 索引失效的11种情况
2.1 全值匹配我最爱
explain select * from student where age 20explain select * from student where age 20 and classId 4explain select * from student where age 30 and classId 4 and name abcd上面三条sql语句的type全部为ALL
性能由好到最坏依次是 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
SQL 性能优化的目标至少要达到 range 级别要求是 ref 级别最好是 consts级别。阿里巴巴开发手册要求
加上索引
CREATE INDEX idx_age on student(age)
CREATE INDEX idx_age_classid on student(age,classId)
CREATE INDEX idx_age_classid_name on student)age,classId,name)再次进行explain 就会使用上索引
2.2 最佳左前缀法则
要遵守最佳左前缀法则
CREATE INDEX idx_age_classid_name on student)age,classId,name)explain select * from student where age 30 and classId 4 and name abcd 上述sql是可以使用到联合索引的因为查询条件的顺序和个数都是完全匹配上索引的。
explain select * from student where classId 4 and name abcd and age 30 那这里为什么顺序和索引的顺序不一致了还能使用上索引呢因为顺序不一致但是字段是能够匹配的上索引的字段的所以能够使用上索引满足总结的3
explain select * from student where name abcd and classId 4此时就用不到索引了因为不满足下述总结的1
explain select * from student where name abcd and age 30 为什么这个sql又能用到索引呢总结的2
总结对于联合索引是否能用到索引的条件
查询条件的字段要能够从最左边开始覆盖到索引的字段如果能从最左边开始覆盖到索引那怕中间断开了也能使用索引只不过使用的索引不是索引的全部顺序如果与索引定义的顺序不一致也没关系只要能满足1优化器在底层也会给我们自动排序
2.3 主键插入顺序
对于InnoDB存储引擎的表来说表中的实际数据都是存储在聚簇索引的叶子节点的记录是存在数据页中数据页和记录是按照主键值从小到大进行排序的如果我们插入的记录的主键值是依次增大的话那么插入的记录会依次往后排但是如果主键值忽大忽小那么就会存在页分裂的情况。 例如现在这个数据页已经满了此时再插入id为9的数据 可这个数据页已经满了再插进来咋办呢我们需要把当前 页面分裂 成两个页面把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么意味着 性能损耗 所以如果我们想尽量避免这样无谓的性能损耗最好让插入的记录的 主键值依次递增 这样就不会发生这样的性能损耗了。
所以我们建议让主键具有 AUTO_INCREMENT 让存储引擎自己为表生成主键而不是我们手动插入
但是在分布式系统中主键一般都是代码里生成的所以…
2.4 计算、函数、类型转换(自动或手动)导致索引失效
CREATE INDEX idx_name ON student(NAME);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE abc%;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) abc;上述两个sql哪个性能更好
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE abc%;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) abc;由此可见是第一个更好使用函数后已经不能使用上索引了。上述like可以使用上索引
接下来看看这三条sql语句
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE abc%;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE %abc;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE %abc%;其对应的结果如下 由此可见只有第一条可以使用上索引
CREATE INDEX idx_sno ON student(stuno);EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno1 900001;EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno 900000;上述两个sql一个对字段进行了计算一个没有答案显而易见 不做运算的能够使用上索引 类型自动转换不能使用索引
INSERT INTO sql_optimize.student(id, stuno, name, age, classId) VALUES (817239817, 1111111, 123, 12, 317);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name123;显而易见第二条sql能够使用上索引因为name是varchar第一条sql的name为int第二条sql的name为字符串类型虽然第一条也能匹配的上记录但是是由于底层给我们使用了函数进行类型转换。 2.5 范围条件右边的列索引失效
create index idx_age_classId_name on student(age,classId,name)EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age30 AND student.classId20 AND student.name abc ;观察上述sql能否用到索引 虽然是用到索引了但是只用到了age和classId两个字段name字段没有用到。因为classId是范围条件范围条件右边的列索引失效
那如果我交换查询条件classId和name的顺序呢
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age30 AND student.name abc AND student.classId20;结果也还是一样因为你的索引的顺序是不变的(age,classId,name)你的sql查询条件的顺序变化了优化器底层还是会改变查询条件的顺序来匹配索引列的顺序来使用上索引除非改变索引列的顺序为(age,name,classId)这样就能使用完全索引了。
下述都是属于范围查询 between总结应用开发中范围查询例如金额日期等设计索引时应该将这些字段放到联合索引的最后。
2.6 不等于(! 或者)索引失效
create index idx_name on student(name)EXPLAIN SELECT * from student where name abcEXPLAIN SELECT * from student where name abc观察上述sql哪个不能使用上索引
由此可见不等于是不能使用索引的
2.7 is null可以使用索引is not null无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;is not null无法使用索引
2.8 like以通配符%开头索引失效
上述最左匹配原则时有提到过
拓展Alibaba《Java开发手册》【强制】页面搜索严禁左模糊或者全模糊如果需要请走搜索引擎来解决
2.9 or 前后存在非索引的列索引失效
create index idx_age on student(age)EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age 10 OR classid 100;可以看到possible_keys有值但是key没有因为age能匹配上索引但是classId匹配不上那为什么不用idx_age索引呢
因为如果走了idx_age索引后面跟上or classid 100 classid没有索引就相当于还是得走一遍全表扫描所以idx_age还不如不走直接走全表扫描来的更快。
create index idx_age on student(age)此时在创建classId的索引就使用上了索引 type为index_merge
2.10 数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。
3. 关联查询优化
数据准备
CREATE TABLE IF NOT EXISTS type (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
#图书
CREATE TABLE IF NOT EXISTS book (
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (bookid)
);#向分类表中添加20条记录
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 (RAND() * 20)));#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));3.1 左外连接
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card book.card;上述没有使用上索引
CREATE INDEX Y ON book(card);给book加上索引 给type加上索引
CREATE INDEX X ON type(card);我删除掉book的索引
DROP INDEX Y ON book;可以得到book没有使用上索引
3.2 内连接
删除掉上述的book和type的索引
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card book.card;CREATE INDEX Y ON book(card);CREATE INDEX X ON type(card);对于内连接来说查询优化器是可以决定谁作为驱动表谁作为被驱动表。
现在删除book的索引
DROP INDEX Y ON book;可以看到book的位置跑到了type的上面即book是驱动表
结论 1. 如果内连接有索引的话索引给被驱动表成本消耗是最低的。 2. 对于内连接来说如果两个表的连接条件都存在索引的情况下会选择小表作为驱动表。即小表驱动大表
因为join连接中例如 A inner join B … 例如是取出A的一条数据来匹配B的所有数据若此时B有索引则匹配的时候就能使用上索引但是A是必须得全部取出来的所以这满足了结论1
如果B的数量级很大那么索引的优势越明显所以小表驱动大小满足了结论2
3.3 join的底层原理
join方式连接表本质就是各个表之间数据进行循环匹配Mysql5.5之前Mysql只支持一种表间关联方式就是嵌套循环Nested Loop Join。如果关联表的数据量很大则join关联的执行时间会很长。在Mysql5.5之后的版本中Mysql通过引入BNLJ算法来优化嵌套执行。
上述我们看到了Mysql优化器会帮我们决定inner join中驱动表与被驱动表。那么对于外连接left joinright joinMysql优化器也会帮我们决定驱动表与被驱动表
3.3.1 Simple Nested-Loop Join 开销统计SNLJ外表扫描次数1内表扫描次数A读取记录数AA*Bjoin次数B*A回表读取次数0 因为没有索引
3.3.3 Index Nested-Loop Join Index Nested-Loop Join其优化的主要思路就是减少内层的匹配次数所以要求被驱动表必须有索引。
开销统计SNLJ外表扫描次数1内表扫描次数0读取记录数ABmatchjoin次数A*Index(Height)回表读取次数Bmathc
3.3.3 Block Nested-Loop Join
如果存在索引那么会使用index的方式进行join如果join的列没有索引被驱动表扫描的次数太多了每次访问被驱动表其表中的记录都会被加载到内存中然后再从驱动表中进行匹配匹配完之后取出内存然后再从驱动表中取出一条数据加载被驱动表的记录到内存中继续比较周而复始这种方式大大的增加了IO次数为了减少被驱动表的IO次数就出现了Block Nested-Loop Join。
不再是逐条获取驱动表的数据而是一块一块的获取存入join buffer缓冲区中将驱动表join相关的部分数据列大小受到join buffer的限制缓存到join buffer中然后全表扫描被驱动表被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配内存中操作将简单的嵌套循环中的多次比较合并为一次降低了被驱动表的访问频率。
注意
这里缓存的不只是关联表的列select 后面的列也会进行缓存在一个有N个join关联的SQL中会分配n-1个join buffer所以查询的时候尽量减少不必要的字段可以让join buffer中存放更多的列所以尽量别用select * … 3.3.4 相关参数
show variables like %optimizer_switch% 查看block_nested_loop的状态默认是ON
show variables like %join_buffer_size% 查看join_buffer_size的大小默认是256K
join_buffer_size在32位系统上可以申请4G在64位系统上可以申请大于4G的空间64位windows系统除外其最大值会被截断位4G并发出警告
3.3.5 总结
效率上Index Nested-Loop Join Block Nested-Loop Join Simple Nested-Loop Join永远使用小结果集驱动大结果集本质就是减少外层循环数量小的度量单位是指 表的行数*每行大小被驱动表匹配的条件增加索引列增大join buffer size的大小减少驱动表不必要的字段查询为什么是驱动表因为如果是Block Nested-Loop Join驱动表的查询字段也会加载到join buffer中
3.3.6 Hash Join 4. 子查询优化
Mysql从4.1开始支持子查询使用子查询可以进行SELECT语句的嵌套查询即一个子查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。但是子查询的效率不高原因如下
执行子查询时Mysql需要为内层查询语句的查询结果建立一个临时表然后外层查询语句从临时表中查询记录查询完毕后再撤销这些表这样会消耗过多的cpu和io资源产生大量慢查询子查询的结果存储的临时表不论是内存临时表还是磁盘临时表都不会存在索引所以查询性能会有影响对于返回结果集比较大的子查询其对查询性能的影响也越大
建议实际开发中使用join操作来替代子查询
5. 排序优化
为什么在order by字段上添加索引
Mysql中支持两种排序方式分别是FileSort和Index排序。
Index排序中索引可以保证数据的有序性不需要在进行排序效率更高FileSort排序则是在内存中进行排序占用CPU资源如果待排序的数据较大会产生临时文件IO到磁盘进行排序效率低下
优化建议
SQL中可以在where和order by子句中使用索引目的是在where子句中避免全表扫描order by子句中避免使用FileSort排序但是某些情况下全表扫描或者FileSort排序不一定比索引排序慢。尽量使用Index排序如果where和order by是同一个字段则单列索引就可以满足如果不一致则使用联合索引无法使用Index排序则对FileSort方式进行调优
5.1 fileSort算法
双路排序MySQL 4.1之前是使用双路排序 字面意思就是两次扫描磁盘最终得到数据 读取行指针和order by列 对他们进行排序然后扫描已经排序好的列表按照列表中的值重新从列表中读取对应的数据输出从磁盘取排序字段在buffer进行排序再从 磁盘取其他字段单路排序 快从磁盘读取查询需要的 所有列 按照order by列在buffer对它们进行排序然后扫描排序后的列表进行输出 它的效率更快一些避免了第二次读取数据。并且把随机IO变成了顺序IO但是它会使用更多的空间 因为它把每一行都保存在内存中了。
在sort_buffer中单路比多路要占用很多的空间因为单路是把所有字段都取出所以有可能取出的数据的总大小超过了sort_buffer的容量导致每次只能去sort_buffer容量大小的数据进行排序然后创建tmp文件多路合并排完后取sort_buffer容量大小再排…导致多次IO
优化策略
提高sort_buffer_size大小 Mysql5.7默认位1M show variables like %sort_buffer_size%尝试提高max_length_for_sort_data提高这个参数会增加用改进算法的概率但是如果设置的太高数据总容量容易超过max_buffer_size明显症状就是IO增加如果需要返回列的总长度大于max_length_for_sort_data则使用双路否则使用单路该值建议在1024-8192字节之间进行调整order by时候建议不要使用 select * 原因见优化策略12条
6. group by 优化
group by 使用索引的原则几乎跟order by一致 group by 即使没有过滤条件用到索引也可以直接使用索引。group by 先排序再分组遵照索引建的最佳左前缀法则当无法使用索引列增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置where效率高于having能写在where限定的条件就不要写在having中了减少使用order by和业务沟通能不排序就不排序或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU数据库的CPU资源是极其宝贵的。包含了order by、group by、distinct这些查询的语句where条件过滤出来的结果集请保持在1000行以内否则SQL会很慢。
7. 分页优化
优化思路1使用order by
在索引上完成排序分页操作最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
a WHERE t.id a.id;优化思路2该方案适用于主键自增的表可以把Limit 查询转换成某个位置的查询 。
EXPLAIN SELECT * FROM student WHERE id 2000000 LIMIT 10;8. 覆盖索引
理解方式一索引是高效找到行的一个方法但是一般数据库也能使用索引找到一个列的数据因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据当能通过读取索引就可以得到想要的数据那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二非聚簇复合索引的一种形式它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列即建索引的字段正好是覆盖查询条件中所涉及的字段。
简单说就是 索引列主键 包含 SELECT 到 FROM之间查询的列 。 查询的字段在索引中存在即不需要回表进行查找
优点
避免Innodb表进行索引的二次查询回表可以把随机IO变成顺序IO加快查询效率我们二级索引是有顺序的但是如果没有索引覆盖就得回表从二级索引获取的主键值在聚簇索引中不一定是连续的所以就有可能是随机IO
缺点
索引字段的维护 总是有代价的。因此在建立冗余索引来支持覆盖索引时就需要权衡考虑了。
具体的联合索引内容见Innodb索引还不清楚看这一篇就够啦
9. 索引下推ICP
Index Condition Pushdown(ICP)是MySQL 5.6中新特性是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
create index idx_name_stuno_age on student(name,stuno,age)explain select * from student where name 大帅 and stuno like %10 and classId 11可以看到Extra中有Using index condition;即ICP索引下推
原理过程分析
name使用到了索引在二级索引过滤name的查询条件后然后就直接回表了吗此时并没有直接回表因为使用到的联合索引中还包含了stuno字段我们可以在过滤name后的数据集中进行stuno的过滤然后再去回表
减少了回表的随机IO的次数
在不使用ICP索引扫描的过程
storage层只将满足index key条件的索引记录对应的整行记录取出返回给server层
server 层对返回的数据使用后面的where条件过滤直至返回最后一行 使用ICP扫描的过程 storage层首先将index key条件满足的索引记录区间确定然后在索引上使用index filter进行过滤。将满足的indexfilter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃不回表、也不会返回server层。
server 层对返回的数据使用table filter条件做最后的过滤 使用前后的成本差别
使用前存储层多返回了需要被index filter过滤掉的整行记录使用ICP后直接就去掉了不满足index filter条件的记录省去了他们回表和传递到server层的成本。ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例
ICP的使用条件
只能用于二级索引(secondary index)explain显示的执行计划中type值join 类型为 range 、 ref 、 eq_ref 或者 ref_or_null 。并非全部where条件都可以用ICP筛选如果where条件的字段不在索引列中还是要读取整表的记录 到server端做where过滤。ICP可以用于MyISAM和InnnoDB存储引擎MySQL 5.6版本的不支持分区表的ICP功能5.7版本的开始支持。当SQL使用覆盖索引时不支持ICP优化方法ICP是一定基于有回表操作的情况下的
10 一切基于成本考虑
上述说明了索引失效的很多种情况但是实际中并不是死板的所有的一切是否使用索引最终还是交由Mysql的优化器来根据成本进行决策。
举个例子
! 或者 无法使用索引
上述结论有提到过
create index idx_age_name on student(age,name)创建一个索引然后执行sql
explain select * from student where age 20可以看到并没有使用上索引然后我再修改一下sql
explain select age,name from student where age 20可以看到已经使用上了索引因为此时我改变查询列完全满足索引覆盖没必要回表开销小所以就使用上了索引。总之一切都是基于开销来做决定。
