MySQL优化(3):索引
MySQL优化中,最重要的优化手段就是索引,也是最常用的优化手段
索引简介:
索引:关键字与数据位置之间的映射关系
关键字:从数据中提取,用于标识,检索数据的特定内容
目的:加快检索
索引检索为什么快:
(1)关键字相对于数据本身,量较小
(2)关键字都是排序好的
MySQL中索引的类型:
普通索引,唯一索引,主键索引,全文索引
不同的类型只是对关键字的限制不同
普通索引:多索引关键字没限制,有长度限制
唯一索引:要求记录提供的关键字不能重复
主键索引:要求关键字不能重复而且不能为NULL
全文索引:不支持中文,后续细讲
索引的语法:
查看索引:
SHOW CREATE TABLE [table-name];
比如查到PRIMARY KEY(‘id’),就是一个主键索引
创建索引:需要修改表结构和创建表时候完成,基于不同的类型,方式也不同
同时创建四个索引,由于使用到了全文索引,这里使用MYISAM引擎
CREATE TABLE USER(
ID INT AUTO_INCREMENT PRIMARY KEY,
FIRST_NAME VARCHAR(16),
LAST_NAME VARCHAR(16),
SN VARCHAR(16),
INFORMATION TEXT,
KEY(FIRST_NAME,LAST_NAME),
UNIQUE KEY(SN),
FULLTEXT KEY(INFORMATION)
)ENGINE=MYISAM;
索引可以命名,比如KEY NAME (FIRST_NAME,LAST_NAME)
这句话创建了一个基于FIRST_NAME和LAST_NAME的复合普通索引
UNIQUE KEY(SN) 创建了基于SN的唯一索引,默认以字段名命名索引
最后一个全文索引很鸡肋,基本不会用
在修改表结构的时候创建索引:
ALTER TABLE USER(
ADD KEY(FIRST_NAME,LAST_NAME),
ADD UNIQUE KEY(SN),
ADD FULLTEXT KEY(INFORMATION)
)
删除索引:
ALTER TABLE [table-name] DROP PRIMARY KEY;
ALTER TABLE [table-name] DROP KEY [key-name];
一般不简易删除主键索引,记录是按照主键来排序的,设计主键要注意一定与业务逻辑无关
执行计划:
执行计划:当MySQL执行SQL语句时,会分析、优化、形成执行计划后按照执行计划来执行
在执行计划中可以清楚的看到当前的查询是否需要用到索引:
EXPLAIN SELECT * FROM [table-name] WHERE ID<20;
结果中有一样:KEY:PRIMARY,代表该查询语句会用到主键索引
索引使用的场景:
如果两张表,学生和班级表,多对一的关系,导入较多的记录来测试
(1)WHERE查询
EXPLAIN SELECT * FROM STUDENT WHERE ID=123456
和上面的例子一样,可以看到使用到了主键索引
EXPLAIN SELECT * FROM STUDENT WHERE USERNAME="XXX"
这句话执行后会发现:KEY:NULL,没有索引
我们给它加上索引:(这里的INDEX和上文的KEY都可以)
ALTER TABLE STUDENT ADD INDEX (USERNAME);
查看执行计划后可以发现:KEY:USERNAME,说明使用到了新建的索引
(2)ORDER BY排序
我们有可能会遇到以下的情况:
SELECT * FROM STUDENT ORDER BY USERNAME;
查看执行计划后可以发现没有使用到任何索引,并且看到了Extra:Using Filesort,使用到了外部文件排序,性能更低,需要先将数据读取到内存,分段读取合并排序
提高效率的方式是增加索引:
ALTER TABLE STUDENT ADD INDEX (USERNAME);
现在查看执行计划后,可以发现使用到了USERNAME索引,并且没有使用外部文件排序,性能会有明显提升
(3)JOIN 连接
使用到这条语句:
SELECT C.* COUNT(S.ID) FROM CLASS C JOIN STUDENT S ON C.ID=S.CLASS_ID GROUP BY C.ID;
当数据量巨大的时候,这句话要执行5S以上
查看执行计划后,发现其中一张表没有索引,且使用到了外部文件排序
解决:
ALTER TABLE STUDENT ADD INDEX (CLASS_ID);
执行后发现速度明显提升,并且两张表都使用到了索引,没有外部文件排序
(4)索引覆盖
前三条很重要,是必须做的优化,这条只是一个现象
比如我们使用这条语句:建立一个复合索引
ALTER TABLE STUDENT ADD INDEX (FIRSTNAME,LASTNAME);
然后再执行:这句话没有使用到以上三种情况
SELECT FIRSTNAME,LASTNAME FROM STUDENT;
但是查看计划后,发现还是使用到了索引,并且Extra:Using index,说明这句话只使用了索引来完成
如果执行这句话
SELECT FIRSTNAME,LASTNAME,USER FROM STUDENT;
再查看计划后发现没有使用到了索引,并且进行了全表扫描
两次的差异只是多了一个USER字段,而复合索引没有包含该字段
总结:MySQL的查询优先使用了索引,由于索引覆盖,建议SELECT后面只写有必要的字段,被覆盖的可能性就会提升,尽可能地优化
语法注意细节:
(1)字段需要独立出现
SELECT * FROM STUDENT WHERE ID+1=20;
这句话是能执行成功的,ID是主键,查看计划后却没有使用到主键索引
字段没有独立出现,不能触发该字段上的索引,避免这种情况
(2)LIKE查询不能以通配符开头
SELECT * FROM STUDENT WHERE USERNAME LIKE "%A%";
这句话无法使用到索引,如果是以下的情况,那么会用到索引
SELECT * FROM STUDENT WHERE USERNAME LIKE "A%";
字符串比较中,不能使用包含的逻辑,比如查询包含Java的字符串,不能写"%Java%",效率过低
解决办法:全文索引,但是MySQL全文索引很鸡肋,应该使用第三方的比如ES,Solr
(3)复合索引的右侧字段不能独立使用索引
已有INDEX NAME(FIRSTNAME,LASTNAME);
使用语句,这句话使用到了索引
SELECT * FROM STUDENT WHERE FIRSTNAME="XXX";
而下面这句话没有使用到索引
SELECT * FROM STUDENT WHERE LASTNAME="XXX";
原因:复合索引是按照左侧字段排序的,如果左侧字段相同再用右侧字段排序,总体上来看,右侧字段是未排序的
既然这样为什么还要建复合索引呢?以下这种情况
SELECT * FROM STUDENT WHERE FIRSTNAME LIKE "XX%" AND LASTNAME LIKE "XX%";
这句话如果建立两个索引,那么计算两个索引的交集会更慢,所以需要复合索引
如果遇到上面的情况,再给LASTNAME建立一个索引即可
(4)早期版本NULL值无法使用索引
SELECT * FROM STUDENT WHERE FIRSTNAME=NULL;
新版本无需关心这一条
(5)OR语法保证两边的条件都有索引可用
SELECT * FROM STUDENT WHERE FIRSTNAME LIKE "XX%" OR USER LIKE "XX%";
如果USER没有索引,那么还是会全表查询
(6)状态值不容易使用到索引
GENDER 0,1,2表示男,女,未知
即使在字段上增加了索引,通常也不会起作用
SELECT * FROM STUDENT WHERE GENDER IN (0,1);
原因:状态值往往导致一个状态值匹配大量记录,查询大量记录的时候,MYSQL认为使用索引开销比全表扫描都要大
如何创建索引:
(1)WHERE,ORDER BY,JOIN字段上建立索引
(2)组合索引的建立:基于业务逻辑
(3)如果条件经常出现在一起,多字段索引可以升级为复合索引
(4)如果通过增加个别字段,就可以出现索引覆盖,那么增加个别字段
(5)不会用到的索引应该删掉
(6)常规情况下我们建立的数据库系统本身性能就不差了
(7)有些字段是否只使用前缀就能完成,使用前缀索引
前缀索引:INDEX(FIELD(10))
使用字段field的前10个字符建立索引,默认是使用字段全部内容建立索引
使用:GIT的COMMIT_ID;密码字段
索引的存储结构:
BTREE索引,HASH索引,聚簇索引
以上概念指的是索引的存储结构,数据结构上的概念,实际使用无需关心,了解即可
BTREE索引:
索引存储在磁盘上所用的基础的通用的存储结构
特征:磁盘上的数据结构,不是二叉树,一定要一个中文,那就是多路平衡查找树
特点:一个BTREE节点,存储多个索引关键字,多少由节点大小和关键字来确定的,
节点大小是固定的,由计算机文件系统来确定,一次性磁盘读取内存量,就是一个节点大小
由于一个节点的大小是固定的,一个节点无法容纳大量关键字,所以分散在多个节点来存储关键字
这时候如何进行排序呢?通过上层节点的子节点指针指向下层节点,用来关联所有的节点,子节点指针位于关键字之间
例如每个节点存储1000个关键字,深度为2的两层BTREE大概可以存储1000000(1000*1000)个关键字
查找一个关键字,需要读取几个节点的内容呢?从根开始,确定下级节点,仅仅两次的磁盘读取就可以做到
Btree的意义在于可以遍历大量关键字,减少磁盘读取量的开销
聚簇索引:
关键字和记录在一起进行存储
是升级后的Btree,数据结构上的B+Tree
MySQL中只有Innodb的主键索引是聚簇结构
HASH索引:
当索引被载入到内存后采用的存储结构,采用哈希结构存储了,类似Java的Map,Key-Value
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341