什么是索引?
索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B+树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。可理解为是一种快速查找的数据结构。
索引分类:
主键索引
ALTER TABLE 表名 ADD PRIMARY KEY (列);
唯一索引
alter table 表名 add constraint 约束名 约束类型 (列);
普通索引
create index 索引名 on 表 表名(列1,列名2);
联合索引
alter table 表名 add index my_ind (列名,列名);
全文索引
ALTER TABLE 表名 ADD FULLTEXT ft_stu_name (列名)(ft_stu_name为
索引名)
正确使用索引
explain 关键字 可在查询语句前加上该关键字,可查询是否使用了索查询,当查询的type为ALL时表示为全局查询,并未使用索引,反之。
show keys from 表名 可查看建立了那些索引 -----
索引原理
假设有表 emp ,表中数据量400万条,此时无索引
执行如下语句:
select * from emp where empno=‘xxxx’;
本次执行查询用了1.39秒左右
添加主键索引
ALTER TABLE EMP ADD PRIMARY KEY (empno);
再次执行查询
select * from emp where empno=‘xxxx’;
本次执行查询用了0.003秒左右
到这里你就该问了,为什么会这样?,同样是执行查询,怎么结果差这么多?
在没有索引之前是进行全文查找,而建立了索引是以二叉树+二分查找方式进行查找
看个例子:
1到11为数据条数
每次对半查找中间节点作为跟节点,比根节点大的在右边,比根节点小的在左边,由此循环。形成二叉树后,在进行查找时每次都会进行二分查找,由此速度就飞快啦,这下你理解了吗?
索引的代价
占用磁盘空间(会生成索引文件 (mysql安装目录下)C:\ProgramData\MySQL\MySQL Server 5.7\Data\atm)的.ibd文件为索引文件
对DML(update、delete、insert)语句的效率影响(每次都要动态生成更新索引)
增删改会对索引影响,因为索引要重新整理。
什么时候用索引?
1.出现慢查询
2.频繁需要查询的字段
3.字段不是唯一的几个值
ps: 全局扫描方式比索引高时,不建议建索引
慢查询
在规定时间内未返回数据,称为慢查询
–查询慢查询时间
show variables like ‘long_query_time’; mysql默认为10秒
–修改慢查询时间
set long_query_time=1; —但是重启mysql之后,long_query_time依然是默认值
如何将慢查询定位到日志中
在默认情况下,我们的mysql不会记录慢查询,需要在启动mysql时候,指定记录慢查询才可以
1.先关闭mysql,再启动慢查询, 如果启用了慢查询日志,默认把这个文件放在mysql安装目录下my.ini 文件中记录的位置
#Path to the database root
datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
2.进入该路径cmd中执行.bin\mysqld.exe --safe-mode --slow-query-log (安全模式启动,数据库将操作写入日志,以备恢复)
此时会生成慢查询文件。该文件记录着所有慢查询的语句