帖子信息
mysql 数据库 SQL优化之路
renjun   分享于  2018-12-10   所需:0 积分
0

mysql 数据库  SQL优化之路

本文为研究mysql优化知识而写,本文为文章信息,无需下载

1.索引的使用

1.1 索引作用

      无索引:没有索引的表查数据时,会检索整个表,比如:表中数据有值1-100的数据,我要找到50这条数据,这时候会通过一条条的找,直到找到50 这条数据为止(当数据量大的时候,查询速度可想而知)。 

      有索引:索引就像一本书,把整个表数据根据页数来分类,使用索引查询数据时,直接找到这条数据所在页码,再去搜索你需要的数据。比如:表中数据有值1-100的数据,我要找到50这条数据,索引把1-100分为了1-10页,这时候查找数据时会先找到第5页,然后再找到50这条数据。


1.2 索引类型

主键索引

普通索引

聚合索引(多列索引)

唯一索引

全文索引

唯一索引和全文索引使用较少,我们主要关注主键索引、普通索引和聚合索引。 

1)主键索引:主键是加在主键上的索引,设置主键(primary key)时,mysql 会自动创建主键索引 

2)普通索引:创建在非主键列上的索引 

3)聚合索引:创建在多列上的索引


1.3 索引语法

查看某张表的索引:show index from 表名

创建普通索引:alter 表名 add index 索引名(加索引的列)

创建聚合索引:alter 表名 add index 索引名(加索引的列1,加索引的列2)

删除表中某个索引:drop index 索引名 on 表名

1.4 索引的实际测试

1)创建测试表:


CREATE TABLE `andy_user`(

    `id` BIGINT(20)PRIMARY KEY NOT NULL AUTO_INCREMENT,

    `username` VARCHAR(20)DEFAULT NULL,

    `password` VARCHAR(50)DEFAULT NULL

) ENGINE = MyISAM DEFAULT CHARSET = utf8;


存储引擎使用的MyISAM ,此引擎没有事物等数据库高级应用,方便我们快速插入测试数据,待插入完数据把存储引擎改为InnoDB


2)测试数据插入(存储过程插入,此处仅插入10W 数据,因耗时太长,可自行增加数据测试)


CREATE PROCEDURE andy_user_addData_proc()

BEGIN

    DECLARE num INT;

    SET num = 1;

    WHILE num <= 100000 DO

        INSERT INTO andy_user(username, password) VALUES (num, PASSWORD(num));

        SET num = num + 1;

    END WHILE;

END;

call andy_user_addData_proc();


更改存储引擎


alter table andy_user engine=InnoDB;


3)SQL测试(本测试数据为10W,数据量越大差距越明显) 

①主键索引 id列


select id,username,password from andy_user where id=99999


耗时0.014s


②普通索引列 username列 

未加索引执行sql


select id,username,password from andy_user where username='99999'


耗时0.076s 

下面我们给username 加上普通索引


ALTER TABLE `andy_user` ADD INDEX index_name(username);


再次执行 

耗时0.014s


③聚合索引列 username + password 

先删除username 的索引


drop index index_name on andy_user


执行没有索引的sql


select id,username,password from andy_user where username='99999' and password = '*54D8397040D8186C83AAA36B50EB087538E6E1DD'


耗时0.071s 

下面我们给username +password 加上聚合索引


ALTER TABLE `andy_user` ADD INDEX index_union_name_password(username,password)


再次执行 

耗时0.014s


学习SQL优化最主要学会查看执行计划,用desc 或 explain ,例如:


desc select id,username,password from andy_user where id=99999;

explain select id,username,password from andy_user where username='99999' and password = '*54D8397040D8186C83AAA36B50EB087538E6E1DD';


通过执行计划可以看到该语句查哪些表,各个表是全表扫描还是其他。所使用到的有哪些索引。检索了多少行。等等…




后续会补充其他所学习到的SQL优化,例如:造成索引没有被使用到的关键字、一些要注意的地方等


评论信息  共1条
相关资源
Powered by Java111  |  免责申明   |  侵权举报   |  广告合作   |  站点地图
Copyright © 2012-2018 java视频教程免费下载java111.com 版权所有
备案号:陕ICP备16018672号
本站资源均来自互联网或会员发布,如果侵犯了您的权益请与我们联系,我们将在24小时内删除!谢谢!