哈希索引基于哈希表实现,只有精确匹配所有列的查询才有效。
hash索引的限制:
=、IN()、<=>
也不支持任何范围查询InnoDB有个特殊功能,自适应哈希索引。某些索引值被使用的非常频繁,它会在内存中基于B-Tree索引之上再创建一个哈希索引,这样就让B-Tree索引也具有哈希表的一些优点。
GIS,GIS的解决方案做的比较好的是PostgreSQL的PostGIS
全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。
优点:
三星索引:
索引将相关的记录放到一起获得一星;
如果索引中的数据顺序和查找中的排列顺序一致则获得二星;
如果索引的列中包含了查询中需要的全部列则获得“三星”
对于非常小的表,大部分情况下简单的全表扫描更高效。
对于中大型的表,索引就非常有效。
对于特大型的表,建立和使用索引的代价将随之增长。
独立的列是指索引列不能是表达式的一部分,也不能是函数的参考。
例如,下面这个就没法使用dt_tskssj的索引。 where id + 1 > 100
这种也不行
where to_char(dt_tskssj, 'yyyy-MM') = to_char(now(), 'yyyy-MM') 修改后: where dt_tskssj>='2018-08-01 00:00:00.000' and dt_tskssj < '2018-08-31 24:00:00.000'
索引的选择性是指,不重复的索引值(也成为基数,cardinality)和数据表的记录总数的比值。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1.
对于BLOB、TEXT或者很长的VARCHAR
类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。
聚簇索引不是一种单独的索引类型,而是一种数据存储的方式。
当表有聚簇索引时,它的数据行实际上存放在索引的叶子页(leaf page)中。
如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。
聚簇索引的缺点:
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”
MySQL有两种方式可以生成有序的结果:通过排序操作;或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”,则说明MySQL使用了索引扫描来做排序。
MySQL压缩每个索引块的方法是,先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。例如,索引块中的第一个值是“perform”,第二个值是“performance”,那么第二个值的前缀压缩后存储的是类似“7,ance”的形式。
重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。冗余索引,例如,创建了索引(A,B)再创建索引A就是冗余索引,因为这只是前一个索引的前缀索引。
InnoDB在二级索引上使用共享锁,但访问主键索引需要排它锁。
大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询,我们发现通过下面两个步骤来分析总是很有效:
对于MySQL,最简单的衡量查询开销的三个指标如下:
一般MySQL能够使用如下三种方式应用WHERE条件,从好到坏依次为:
如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试下面的技巧优化:
MySQL发送一个请求的时候,做了些什么:
MySQL客户端和服务器之间的通信协议是“半双工”的,这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生。
题外话,select * from a join b using(id);
等价于select * from a join b on a.id = b.id;
MySQL只是简单的根据执行计划给出的指令逐步执行。
即使查询不需要返回结果集给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响到的行数。
例如SELECT COUNT(*) FROM t where ID > 5
假如扫描了50000多行,
下面这样查询能减少扫描行:
select (select count(*) from t) - count(*) from t where id <= 5;
关于子查询优化: 尽可能使用关联查询代替。
在MySQL中,当无法使用索引的时候,GROUP BY使用两种策略来完成:使用临时表或者文件排序(filesort)来做分组。
如果没有通过ORDER BY子句显式的指定排序列,当查询使用GROUP BY子句的时候,结果集会自动按照分组的字段进行排序。如果不关心结果集的排序,而这种默认排序又导致了需要文件排序,则可以使用ORDER BY NULL,让MySQL不再进行文件排序。
1是延迟关联
视图是一个虚拟表,是sql的查询结果,其内容由查询定义
优点:
缺点:
复制有三个步骤:
有时候我们学习一样东西的时候,还是要亲自去尝试一下,多思考一下。
测试数据来自MySQL官方数据:https://github.com/datacharmer/test_db 导入之后执行下面的创建索引语句。MySQL版本5.7
假设有这样一张表:(数据量30w)
create table employees ( emp_no int not null primary key, birth_date date not null, first_name varchar(14) not null, last_name varchar(16) not null, gender enum('M', 'F') not null, hire_date date not null ); create index employees_first_name_index on employees (first_name);
执行左模糊或者全模糊explain select * from employees where first_name like '%a';
的时候发现没有走索引,
当改为右模糊的时候
但like左模糊或者全模糊一定不会走索引吗?
还记得有个叫“覆盖索引”
的东西吗?比如这样explain select emp_no, first_name from employees where first_name like 'a%';
另外,非字符串类型的列上建了索引,使用右模糊也不会走索引的。
参加本文前面“独立的列”
!=
或者<>
或者not in
比如
explain select * from employees where first_name not in ( 'Kyoichi'); explain select * from employees where first_name != 'Kyoichi';
first_name上有索引,last_name无索引,索引会失效。
explain select * from employees where first_name = 'Kyoichi' or last_name = 'Georgi';
如果为last_name也建了索引,可以使用a,b上的索引。
如果都是使用first_name来查询也是可以使用索引的。
explain extended select * from employees where first_name is null;
不管是使用is null还是is not null,都不走索引。
那如果用覆盖索引呢?
explain extended select first_name from employees where first_name is not null
is not null可以走索引,is null不走索引。
exists适合于外查询数据少子查询数据多的场景,in则适合外查询数据多子查询数据少的情况。
注意,not in的集合中不能包含null
具体更详细的参考:in exists
重点:
外层大表内层小表,用in。外层小表内层大表,in和exists效率差不多(甚至 in 比 exists 还快,而并不是网上说的 exists 比 in 效率高)
小表做外表的情况下。对于主键来说, not exists 比 not in 快。对于普通索引来说, not in 和 not exists 差不了多少,甚至 not in 会稍快。
大表做外表的情况下,对于主键来说, not in 比 not exists 快。对于普通索引来说, not in 和 not exists 差不了多少,甚至 not in 会稍快。
至于走哪个索引,我建议还是看实际explain结果吧,那么多情况,记起来有点麻烦。
两个表user(10w)/user_group(1w)
建表语句如下:
create table mysql_learning.user ( id int not null primary key, name varchar(45) null, update_time datetime null ) charset=utf8; create index idx_name on mysql_learning.user (name); create table mysql_learning.user_group ( id int not null primary key, user_id int not null, group_id int not null, remark varchar(255) null ) charset=utf8; create index idx_user_id on mysql_learning.user_group (user_id);
使用存储过程插入数据
delimiter ;; create procedure insertData() begin declare i int; set i = 1; while (i < 100000) do insert into user(id, name) values (i, concat(substr(MD5(RAND()), 1, 5), i)); set i = i + 1; end while ; end ;; delimiter ; call insertData(); delimiter ;; create procedure insertData2() begin declare i int; set i = 1; while (i < 10000) do insert into user_group(id, user_id, group_id) values (i, i, (i %3) + 1); set i = i + 1; end while ; end ;; delimiter ; call insertData2();
执行如下sql
select count(*) from user where id in (select id from user_group); select count(*) from user where exists(select 1 from user_group where user.id = user_group.id);
执行会发现差距明显,因此也说明了上面的结论:外表大内表小,用in要快些。
当在上面的sql语句每句前面加上explain extended
的时候,发现了一个有意思的东西:
/* select#1 */ select count(0) AS `count(*)` from `mysql_learning`.`user_group` join `mysql_learning`.`user` where (`mysql_learning`.`user`.`id` = `mysql_learning`.`user_group`.`id`)
in 被优化成了join
然后,我们再将user_group(小表)作为外表试试
select count(*) from user_group where id in (select id from user); select count(*) from user_group where exists(select 1 from user where user.id = user_group.id);
原以为大表在外的时候,exists吃了亏,这个时候会搬回一局,然而事实却是,in还是那么优秀,甚至比exists还要强点。
至于not exists,not in,大表在外,仍然是not in快于not exists
select count(*) from user where id not in (select id from user_group); select count(*) from user where not exists(select 1 from user_group where user.id = user_group.id);
如果再切换下外表,发现not in和not exists差不多(not in稍快)。
总结下:
无论外表大小,in、not in都要强于相应的exists和not exists.
外表大,内表小,用exists是效率比较低的。
结果如何,跟MySQL版本,以及查询的参数列是主键,还是普通列,以及普通列是否有索引有关。要优化还是要依据实际情况。
顺便再插一个知识点,如果not in 里面包含null的话,会发现没有对应的值,执行下面的语句,就会发现,
explain extended select id from user where id not in (null);
console里是这样的(其实如果用id != 1也会变成id <>1)
/* select#1 */ select `mysql_learning`.`user`.`id` AS `id` from `mysql_learning`.`user` where (`mysql_learning`.`user`.`id` <> 1)
用的是<>null
来进行判断的,如果是true的话才返回,但关键是任何任何值跟null做等于判断,结果都是false。
因为执行explain extended select first_name, last_name from employees where first_name = 'Georgi' or last_name = 'Bezalel';
发现有了type为index_merge
如果first_name和last_name上都有索引的话,便简单看了几篇文章,不做深入了解。
index_merge一般有两种,一种是使用or来取index的并集(union),一种是用and来取index的交集(intersect)
or一般会使用index_merge,因为一个条件不满足,还要找另一个条件的数据;但and有时候会出现不使用index_merge的情况。
比如explain extended select first_name, last_name from employees where first_name = 'Georgi' and last_name = 'Facello1';
Facello1
在employees中不存在对应的数据,此时使用last_name上的索引扫描一遍发现没有匹配的数据,那就没必要进行index_merge了。倒是如果给定的first_name和last_name的值在索引上都能找到,那没办法,只能进行index_merge了,例如:
explain extended select first_name, last_name from employees where first_name = 'Georgi' and last_name = 'Facello'
MySQL使用metadata locking来管理对数据库对象的并发访问和用来保证数据的一致性。
《高性能MySQL》
https://www.pianshen.com/article/4642323804/
in not in 和 null , in 判断范围中可以包含null,而not in判断不能包括null
MySQL索引与查询优化