MySQL如何使用索引
初始化测试数据
创建一个测试用的表
create table dept(
id int primary key auto_increment ,
deptName varchar(32) not null unique,
salary decimal(12,6) not null,
remark varchar(256),
createDate date);
使用存储过程在表中插入一万条数据
DELIMITER $$ CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE `test`.`insert_data`() /*LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ BEGIN DECLARE i INT DEFAULT 0; WHILE(i<=10000) DO BEGIN SELECT i; SET i=i+1; INSERT INTO dept(deptName,salary,createDate,remark) VALUES(i,20000,NOW(),'test'); END ; END WHILE; END$$
通过EXPLAIN分析SQL的执行计划
type:表示MySQL在表中找到所需行的方式,或者叫访问类型,常见类型如下(从左到右,性能由差到好)
ALL index range ref eq_ref const,system NULL
ALL: 全表扫描
index: 索引全扫描
range:索引范围扫描
ref:使用非唯一索引扫描
eq_ref:使用唯一索引扫描
const,system:单表中最多只有一个匹配行
以上:通过explain分析type是ALL 是性能最差的一种 以下:开始优化。
MySQL中BTREE索引的用法
普通的BTREE索引
优化前 explain select remark from dept where deptName = ?; type ALL
增加索引 ALTER TABLE dept ADD INDEX index_remark (`remark`);
优化测试 explain select remark from dept where deptName = ?; type ref
联合BTREE索引
增加联合索引 ALTER TABLE dept ADD INDEX index_all (`deptName`,`remark`,`createDate`);
explain select deptName,remark,createDate from dept where deptName='2' and remark = 'test'and createDate ='2018-07-22'; type ref
但是explain select deptName,remark,createDate from dept where remark = 'test'and createDate ='2018-07-22'; type index
——type从ref(使用非唯一索引扫描)变成了index(索引全扫描),
上面两个SQL 索引相同 Type不同是因为 BTREE索引中匹配最左前缀 比如给col1+col2+col3字段上加联合索引能够被包含
col1+col2 、col1+col2+col3、col1+col3使用(顺序没有影响 比如 where col1=? and col2=? 和 where col2=? and col1=? 结果一样)
使用联合索引 相当于一下子创建了如上的三个索引,这就是联合索引的好处
存在索引但不能使用索引的经典场景
例句:explain select deptName,remark,createDate from dept where deptName='2' and remark = 'test'and createDate ='2018-07-22'; type ref
下面是反面教材:
1.以%开头的LIKE查询不能使用BTREE索引
explain select deptName,remark,createDate from dept where deptName like'%2' and remark = 'test'and createDate ='2018-07-22'; type index
2.数据类型出现隐式转换时也不能使用索引
explain select deptName,remark,createDate from dept where deptName =2 and remark = 'test'and createDate ='2018-07-22'; type index
3.复合索引时 不符合最左匹配原则(上面已经提到)
explain select deptName,remark,createDate from dept where remark = 'test'and createDate ='2018-07-22'; type index
复合索引最左匹配原则的成因:
Mysql创建复合索引的规则是首先会对复合索引最左边,也就是第一个字段的索引进行排序,
在第一个字段排序的基础上,在对第二个字段进行排序,所以直接使用第二个字段是没有顺序的
4.用or分隔开的条件,如果or前的条件中的列有索引,后面的列中没有索引,那么涉及到的索引都不会使用到
explain select deptName,remark,createDate from dept where deptName ='2' and remark = 'test'and createDate ='2018-07-22' or salary =200; type ALL
建议or之间的每个条件都要有索引
优化 Order By语句、Group By语句 和Limit语句
1)Order By
order by 字段混合使用DESC ASC 不会使用索引
select * from table order by key1 desc,key2 asc (尽量不要出现这种混合排序)
Where条件过滤的关键字和Order by中所使用的不同 不会使用索引
select * from table where key2 = ? order by key1 (order by 出现的关键字 尽量 在where条件中也出现)
2)Grouy By
当我们的SQL语句需要Group By分组时: 默认情况下,mysql在使用group by之后,会产生临时表,而后进行排序(此处排序默认是快排),这会消耗大量的性能
group by column 默认会按照column分组, 然后根据column升序排列; group by column order by null 则默认按照column分组,然后根据标的主键ID升序排列
explain分析SQL的时候 Extra那一列有时候会出现 using Filesort表示需要额外的排序(对 group by 产生临时表的排序 此时我强制order by salary 才出现Using filesort)
我用Mysql8.0做实验,发现grouy by 默认是按照主键ID升序排列,而不是group的column,8.0的版本 MySQL应该做了优化
3) Limit
MySQL分页查询的时候一个比较头疼的问题是 limit 100,10 此时MySQL需要排序前110个数据然后返回100-110这十条数据
解决方案:
1.在索引上完成排序分页操作,根据主键关联回原表查询所需要的其他列内容
select * from t t1 join (select id from t limit 100,10 ) t2 on (t1.id=t2.id)
2.把limit查询转换成某个位置的查询(仅适用主键自增的列 而且主键不能出现断层)
select * from t where id >100 limit 10
推荐文章:MySQL分页为什么越往后越慢
4)配置MySQL慢日志查询
更改my.cnf文件
开启查询日志 general_log=1
配置查询日志文件 general_log_file = mysql_query.log (var/lib/mysql 数据目录下)
1)配置slow_query_log=1 开启MySQL慢查询日志
2)配置查询日志名称 slow_query_log_file=show_query.log
3)配置慢查询阀值 long_query_time=10 (超过10s为慢查询)
B+索引和Hash索引的区别,主键索引和普通索引的区别
1)B+索引和Hash索引:
B+索引的数据结构是红黑树,查询的时间复杂度是Ologn级别的 Hash索引的存储数据结构是哈希表,查询时间复杂度是O(1)级别的,
我们之所以用B+索引是因为Hash无序,不支持范围查找和排序功能
2)主键索引和普通索引的区别:
平时我们用索引的时候 进来用主键查询:因为主键索引的data存的是那一行的数据(innodb引擎),而普通索引(辅助索引)的data存的是主键,需要通过主键回表再次查询
Mysql索引的原理
新写了一个文章,专门解释索引的底层结构