MySQL 索引和查询优化

1.索引

1.1 索引的概念

        索引(index)是帮助MySQL高效获取数据的有序数据结构，这些数据结构以某种方式指向数据，这样就可以在这种数据结构之上实现高级查找，而不是全表扫描。

        MySQL中选择的数据结构是 B+ 树。

        innoDB的逻辑存储结构

表空间 -- 段 -- 区 -- 页 --- 行，innoDB的最小操作单元是页。一个区固定大小1M，页固定大小16K，所以一个区有64个页

1.2 索引的分类

在innoDB中，根据索引的存储形式，又可以分为聚焦索引和二级索引：

如果我们查询设置的条件是二级索引，那么它会先通过二级索引查找到主键值，然后再到聚焦索引中查询对应的数据，这个过程被称作回表查询

1.3 索引语法

1.3.1 创建索引

        create [unique/fulltext] index 索引名 on 表名(字段名列表)

        eg. create unique index student_name on student(name)

如果只有一个字段，那么这个索引被称作单列索引，多个字段则是联合索引

1.3.2 查看索引

        show index from 表名

        eg. show index from student

1.3.3 删除索引

        drop index 索引名 on 表名

        eg. drop index student_name on student

2.SQL性能优化

2.1 SQL性能分析

2.1.1 查看当前数据库SQL执行频率

        show global/session status like 'com_______'(七个下划线，一个下划线代表一个字符，insert，update，delete，select都是六个字符加上一个下划线)

如果当前数据库是查询的频率最高，那么我们就可以考虑优化查询SQL。

2.1.2 慢查询日志

慢查询日志只能记录超过设置时间的查询语句，如果我们的超时设置为2，但是有些查询花费了1.9s，那么它是不能被慢日志记录的，此时我们需要使用profiling进行查看

-- 查看当前是否支 profiling
select @@have_profiling ;-- 查看profiling是否打开  0-关闭  1-打开
select @@profiling ;-- 打开profiling 如果是0就是关闭
set profiling = 1 ;-- 一系列查询操作
select * from student ;
select * from student where id = 1 ;
select name from student where id = 1 ;-- 查看开启profiling期间所有查询花费的时间
show profiles ;