mysql数据优化之索引

什么是索引？

索引用来快速地寻找那些具有特定值的记录，所有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 （安全模式启动，数据库将操作写入日志，以备恢复）
此时会生成慢查询文件。该文件记录着所有慢查询的语句