MYSQL索引优化


一. 数据准备

生成百万测试数据用于测试索引优化查询

步骤:

  • 创建内存表 test_user_memory 和普通表 test_user

  • 创建函数用于生成数据

  • 创建存储过程在向表内插入数据

  • 通过函数循环执行 INSERT INTO 普通表 SELECT * FROM 内存表;加快数据生成。

    • 因为通过存储过程一条一条将数据插入表内速度很慢,而一次性把内存表的数据插入到普通表,这个过程是很快的

1)创建普通表 test_user

CREATE TABLE `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int(9) NOT NULL COMMENT '本地网',
  `region_id` int(9) NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COMMENT='普通表';

2)创建内存表 test_user_memory

CREATE TABLE `test_user_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int(9) NOT NULL COMMENT '本地网',
  `region_id` int(9) NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=MEMORY AUTO_INCREMENT=51250 DEFAULT CHARSET=utf8mb4 COMMENT='内存表';

3 ) 创建生成数据的函数

  • 创建生成n个随机数字的函数,生成手机号码字段的数据时需要用到
DELIMITER $$
CREATE FUNCTION randNum(n int) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str varchar(20) DEFAULT '0123456789';
    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(1 + RAND()*10 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER;
  • 生成随机手机号码函数
    • 定义常用的手机头 :130 131 132 133 134 135 136 137 138 139 186 187 189 151 157
    • SET head = substring(bodys,starts,3); 在字符串bodys中从starts位置截取三位
DELIMITER $$
CREATE FUNCTION generatePhone () RETURNS VARCHAR ( 20 ) BEGIN
	DECLARE
		head CHAR ( 3 );
	DECLARE
		phone VARCHAR ( 20 );
	DECLARE
		bodys VARCHAR ( 100 ) DEFAULT "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";
	DECLARE
		STARTS INT;
	SET STARTS = 1+floor ( rand()* 15 )* 4;
	SET head = trim(
	substring( bodys, STARTS, 3 ));
	SET phone = trim(
		concat(
			head,
		randNum ( 8 )));
	RETURN phone;
END $$DELIMITER;
  • 创建随机字符串和随机时间的函数
DELIMITER $$
CREATE FUNCTION `randStr` ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 DETERMINISTIC BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	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( 1 + RAND() * 62 ), 1 ));
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;
	
END $$DELIMITER;

4 ) 创建插入数据的存储过程

  • 创建插入内存表数据存储过程 ,入参n是多少就插入多少条数据
DELIMITER $$
CREATE PROCEDURE `add_test_user_memory` ( IN n INT ) BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			( i <= n ) DO
			INSERT INTO test_user_memory ( user_id, user_name, phone, lan_id, region_id, create_time )
		VALUES
			(
				uuid(),
				randStr ( 20 ),
				generatePhone (),
				FLOOR( RAND() * 1000 ),
				FLOOR( RAND() * 100 ),
			NOW());
		
		SET i = i + 1;
		
	END WHILE;
	
END $$DELIMITER;
  • 创建内存表数据插入普通表存储过程
    • 此处利用对内存表的循环插入和删除来实现批量生成数据,这样可以不需要更改mysql默认的max_heap_table_size值也照样可以生成百万或者千万的数据。
    • max_heap_table_size默认值是16M。
    • max_heap_table_size的作用是配置用户创建内存临时表的大小,配置的值越大,能存进内存表的数据就越多。
#参数描述 n表示循环调用几次;count表示每次插入内存表和普通表的数据量
DELIMITER $$
CREATE PROCEDURE `add_test_user_memory_to_outside` ( IN n INT, IN count INT ) BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			( i <= n ) DO
			CALL add_test_user_memory ( count );
		INSERT INTO test_user SELECT
		* 
		FROM
			test_user_memory;
		DELETE 
		FROM
			test_user_memory;
		
		SET i = i + 1;
		
	END WHILE;
	
END $$DELIMITER;
  1. 调用存储过程生成数据

将以上函数和存储过程都创建完毕后,通过执行存储过程生成数据。

#先调用存储过程往内存表插入一万条数据,然后再把内存表的一万条数据插入普通表
CALL add_test_user_memory(10000);
#一次性把内存表的数据插入到普通表,这个过程是很快的
INSERT INTO test_user SELECT * FROM test_user_memory;
#清空内存表数据
delete from test_user_memory;

测试往内存表内生成一万条数据需要多久:

而把内存表的一万条数据一次性插入普通表,只需要很短的时间:

查看数据是否插入test_user表: (一万条数据成功插入)

因为现在还没有更改数据库默认的内存表可容纳的内存大小,所以单次插入内存表一万条数据是没问题的,但是单次插入内存表十万条数据就不行了,会报内存表已满的异常:

所以想插入百万条测试数据,可以通过执行add_test_user_memory_to_outside存储过程,循环调用 INSERT INTO test_user SELECT * FROM test_user_memory;语句插入数据;

#第一个参数表示循环次数,第二个参数表示每次生成的数据;100*10000=一百万
CALL add_test_user_memory_to_outside(100,10000);

但这样耗时太长,且每次不能插入太多数据量(插入数据过多mysql会报内存已满的异常)

可以通过修改mysql的内存默认值,通过调用一次存储过程插入普通表十万或百万的数据

#通过执行mysql命令修改
SET GLOBAL tmp_table_size=2147483648;
SET GLOBAL max_heap_table_size=2147483648;

修改完成后,需要重启mysql进程才可生效;再次单次插入10万以上数据量可以正常插入,不会再报内存已满异常

如果 SET GLOBAL修改内存后,插入10万条数据还是报内存已满的异常,可以修改my.ini的配置文件:

在[mysqld]下加上这两行,mysql的max_heap_table_size默认值是16M ( 修改完成后需重启mysql进程生效 )

[mysqld]
max_heap_table_size = 2048M
tmp_table_size = 2048M

生成百万条测试数据:(这个过程大概需要8到10分钟)

#第一个参数表示循环次数,第二个参数表示每次生成的数据;10*100000=一百万
CALL add_test_user_memory_to_outside(10,1000000);

至此,测试数据准备完毕。

二. 索引查询优化

通过查看test_user的表结构,可以看到当前索引为空

没有索引的情况下,测试在百万的数据量里查询一条数据需要耗时:

SQL_NO_CACHE关键字表示无缓存查询,确保查询时间的真实准确性

select SQL_NO_CACHE * from test_user where phone='18714891720' and lan_id=477 and region_id=28

无索引情况下查询一条数据需要0.43秒

为test_user表添加一个复合索引(即一个索引包含多个字段)

ALTER TABLE test_user ADD INDEX idx_phone_lan_region(phone, lan_id, region_id);

执行完成语句后查看索引创建:

索引创建完成后,再次运行查询语句:(执行时间缩短为0.001秒)

2.1 EXPLAIN关键字

EXPLAIN关键字,可以用来分析SQL语句的执行计划;查看SQL语句查询是否命中索引。

也可以使用DESCRIBE或DESC,语法一样。

使用:在查询语句前加上EXPLAIN关键字

EXPLAIN select SQL_NO_CACHE * from test_user where phone='18714891720' and lan_id=477 and region_id=28

以上表示该查询使用到了idx_phone_lan_region索引,ref表明使用了三个常量命中索引去查找;

explain结果值及其含义:

参数值含义
id表示SELECT语句的编号;
select_type表示SELECT查询语句的类型。 该参数有几个常用的取值:1)SIMPLE :表示简单查询,其中不包括连接查询和子查询; 2)PRIMARY:表示主查询,或者是最外层的查询语句;3)UNION:表示连接查询的第二个或后面的查询语句;
table表示查询的表;
type表示表的连接类型。该参数有几个常用的取值:1)const :表示表中有多条记录,但只从表中查询一条记录。2)eq_ref :表示多表连接时,后面的表使用了UNIQUE或者PRIMARY KEY。3)ref :表示多表查询时,后面的表使用了普通索引。4)unique_ subquery:表示子查询中使用了UNIQUE或者PRIMARY KEY。5)index_ subquery:表示子查询中使用了普通索引。 6)range:表示查询语句中给出了查询范围。7)index:表示对表中的索引进行了完整的扫描。8)system:系统表,少量数据,往往不需要进行磁盘IO。9)all:表示此次查询进行了全表扫描(该条SQL需要优化)。上面各类扫描方式由快到慢顺序为:system > const > eq_ref > ref > range > index > ALL
possible_keys表示查询中可能使用的索引;如果备选的数量大于3那说明已经太多了,因为太多会导致选择索引而损耗性能, 所以建表时字段最好精简,同时也要建立联合索引,避免无效的单列索引;
key表示查询使用到的索引;
key_len表示索引字段的一长度;
ref表示使用哪个列或常数与索引一起来查询记录;
rows表示查询的行数;试图分析所有存在于累计结果集中的行数,虽然只是一个估值,却也足以反映 出SQL执行所需要扫描的行数,因此这个值越小越好;
Extra表示查询过程的附件信息。(会备注是否使用了索引信息等)

2.2 索引优化

  • 优化一:全部用到索引

建立的复合索引包含了几个字段,查询的时候最好能全部用到,而且严格按照索引顺序,这样查询效率是最高的。(最理想情况)

创建的索引idx_phone_lan_region内字段顺序是phone, lan_id, region_id,而查询的时候也按照这个顺序来拼接and条件,则这种情况下查询时间最短;

  • 优化二:最左前缀法则

如果建立的是复合索引,索引的顺序要按照建立时的顺序,即从左到右,如:a->b->c(和 B+树的数据结构有关)

如果查询时的查询条件字段未按照创建索引的顺序,则可能会导致后面字段的索引失效

idx_phone_lan_region复合索引的字段顺序为phone, lan_id, region_id。

无效索引举例:

1)查询时使用 a and c作为查询条件:a 有效,c 无效,因为中间缺少b

查询的连接条件缺少了中间的lan_id,ref表明只使用了一个常量查询命中了索引

2)查询时使用 b and c作为查询条件:b、c 都无效,因为缺少a

查询的连接条件缺少了开头的phone,ref与key为空表面该条查询没有使用到索引

3)查询时只使用c字段作为查询条件:a、b、c 索引都无效

查询的连接条件只有索引字段顺序最后的region_id,ref与key为空表面该条查询没有使用到索引

就好比过桥,如果桥头都没有则无法走到桥中与桥尾,没有桥中也无法走到桥尾。

  • 优化三:不要对索引做以下处理,以下用法会导致索引失效
    • 计算,如:+、-、*、/、!=、<>、is null、is not null、or
    • 函数,如:sum()、round()等等
    • 手动/自动类型转换,如:id = "1",本来是数字,给写成字符串了

使用 is not null导致索引失效

  • 优化四:索引不要放在范围查询右边

比如复合索引:a->b->c,当 where a=' ' and b>10 and c=' ',这时候索引只能用到 a 和 b,c 用不到索引,因为在范围之后索引都失效(和 B+树结构有关)

中间字段使用了>导致 region_id索引失效,type=range表示使用了范围查找索引

将连接条件 region_id=28 去掉查询,key_len索引长度与没去掉一致,都是86,说明当中间为范围查询时,没有使用到右边的region_id索引

  • 优化五:减少 select * 的使用

使用select * 会导致覆盖索引失效且会查找很多不必要的字段。

覆盖索引:即 select 查询的字段与 where 连接条件字段一致。

例:查询语句 select a , b from table where a=1 and b=2;

虽然查询条件缺少phone导致创建的 idx_phone_lan_region索引失效,但是命中了覆盖索引。

将查询条件 lan_id,region_id改为 * ,则没有使用索引

  • 优化六:不使用like 左模糊查询

使用like导致索引失效情况: 1) like "%张三%" 2)like "%张三"

1)因为phone使用了左模糊查询导致索引失效

2)如果必须要使用到左模糊查询,可以使用覆盖索引,即 like 字段是 select 查询内的字段

3)使用右模糊查询 like "张三%"

  • 优化七:order by 优化

当查询语句中使用 order by 进行排序时,如果没有使用索引进行排序,会出现 filesort 文件内排序,这种情况在数据量大或者并发高的时候,会有性能问题,需要优化。

首先给 create_time 字段建立一个单值索引 idx_create_time,用于测试

ALTER TABLE test_user ADD INDEX idx_create_time(create_time);

filesort 出现的情况举例:

1)order by 字段不是索引字段

2)order by 字段是索引字段,但是 select 中没有使用覆盖索引,如:select * from test_user order by create_time asc;

3)order by 中同时存在 ASC 升序排序和 DESC 降序排序,如:select lan_id, region_id from test_user order by lan_id desc, region_id asc;

4)order by 多个字段排序时,不是按照索引顺序进行 order by,即不是按照最左前缀法则 select a, b from test_user order by b asc, a asc;

正确使用:命中覆盖索引

但在实际使用时,覆盖索引排序一般无法满足业务需求,所以可以通过以下方式优化

索引层面解决方法:

  • 使用主键索引排序
  • 按照最左前缀法则,并且使用覆盖索引排序,多个字段排序时,保持排序方向一致
  • 在 SQL 语句中强制指定使用某索引,force index(索引名字)
  • 不在数据库中排序,在代码层面排序

优化八:group by

其原理也是先排序后分组,其优化方式可参考order by。where高于having,能写在where限定的条件就不要去having限定了。

三. 索引概念

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。

可以得到索引的本质:索引是数据结构,索引的目的是提高查询效率,可以类比英语新华字典,如果我们要查询MySQL这个单词,首先我们需要在目录(索引)定位到M,然后在定位到y,以此类推找到SQL。

如果没有索引呢,那就需要从A到Z,去遍历的查找一遍,直到找到我们需要的,一个一个找和直接根据目录定位到数据,查询时间差的天壤之别,这就是索引的妙用。

索引底层数据结构:

当数据量大的时候,索引的数据量也很大,所以索引不可能全部放到内存中,因此索引一般以文件的形式存储到硬盘上。

数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些结构以某种方式指向数据,这样就可以基于这些数据结构实现高级查找算法。

索引算法种类

  • B-tree索引(重点掌握,之后文章详细讲解)
  • Hash索引
  • full-text索引
  • R-tree索引

索引的优势:

  • 类似大学图书馆书目索引,提高数据检索效率,降低数据库IO成本

  • 通过索引列对数据进行排序,降低数据排序成本,降低了CPU消耗

索引的劣势

  • 实际上索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也是要占用空间的

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时,MySQL不仅要不存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息

  • 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立优秀的索引,或优化查询语句

索引分类

  • 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引

  • 唯一索引:索引列的值必须唯一,但允许有空值

  • 复合索引:即一个索引包含多个列

索引语法:

#创建方式一
create [unique] index indexName on tableName (columnName (length))。
#创建方式二
alter tableName add [unique] index [indexName] on (columnName (length))
#删除
DROP INDEX [indexName] ON mytable;
#查看
SHOW INDEX FROM table_name\G

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定length。

哪些情况需要建索引:

  • 主键自动建立唯一索引

  • 频繁作为查询的条件的字段应该创建索引

  • 查询中与其他表关联的字段,外键关系建立索引

  • 频繁更新的字段不适合创建索引:因为每次更新不单单是更新了记录还会更索引,加重IO负担

  • Where条件里用不到的字段不创建索引

  • 单间/组合索引的选择问题(在高并发下倾向创建组合索引)

  • 查询中排序的字段,若通过索引去访问将大大提高排序的速度

  • 查询中统计或者分组字段

哪些不适合建索引:

  • 表记录太少

  • 经常增删改的表

  • 数据重复且分布平均的表字段,因此应该只为经常查询和经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

四. 慢查询SQL日志

  • 慢查询日志是MySQL提供的一种日志记录,它用来记录查询响应时间超过阀值的SQL语句
  • 这个时间阀值通过参数long_query_time设置,如果SQL语句查询时间大于这个值,则会被记录到慢查询日志中,这个值默认是10秒
  • MySQL默认不开启慢查询日志,在需要调优的时候可以手动开启,但是多少会对数据库性能有点影响

开启慢查询日志:

#查看是否开启了慢查询日志
SHOW VARIABLES LIKE '%slow_query_log%'

#用命令方式开启慢查询日志,但是重启MySQL后此设置会失效
set global slow_query_log = 1

#永久生效开启方式可以在my.cnf里进行配置,在[mysqld]下新增以下两个参数,重启MySQL即可生效
slow_query_log=1
slow_query_log_file=日志文件存储路径

慢查询时间阀值:

#查看慢查询时间阀值
SHOW VARIABLES LIKE 'long_query_time%';

#修改慢查询时间阀值 单位秒
#修改后的时间阀值生效,需要重新连接或者新开一个回话才能看到修改值。
set global long_query_time=3;

在MySQL配置文件中修改时间阀值:

[mysqld]下配置
slow_query_log=1
slow_query_log_file=日志文件存储路径
long_query_time=3 
log_output=FILE
MySQL
  • 作者:管理员(联系作者)
  • 发表时间:2020-08-04 19:55
  • 版权声明:自由转载-非商用-非衍生-保持署名(null)
  • undefined
  • 评论