一、索引介绍
1、什么是索引
索引就是帮助mysql高效获取数据的数据结构，是一组排好序的数据结构
mysql 除了存储数据，还有数据结构，我们可以通过数据结构的查找算法快速找到数据，这种数据结构就是索引。类似于 字典中的目录，帮助我们快速查找数据。
2、建立索引与否的区别
1、不建索引：数据库会做全表扫描，一条条遍历，直到找到数据
比如查找mysql这个单词，会先从A 找到M 再从A 找到Y。。效率低
2、建索引：会直接定位到 M Y 数据上，效率高
3、索引数据结构
常见的索引数据结构：
-
B-Tree（Myisam 普通索引）
-
二叉树
典型二叉树：大的在右边 小的在左边，比如查找15李四，会拿15和10比较 比10大找右边节点 。
缺点： 当数据插入时，如果一直往右边插入，会形成链表，查询效率低。
-
B+Tree（Innodb 普通索引）、
-
Hash(memory 存储引擎)等
问：为了减少IO，索引树会一次性加载吗？
如果数据量很大，索引的大小也会很大
当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。
问：Hash索引与B+树索引的区别
- Hash索引不能进行范围查询，而B+树可以
- Hash索引不支持联合索引的最左侧原则，而B+树可以
- Hash索引不支持 ORDER BY 排序，而B+树可以
- InnoDB不支持哈希索引，而B+树可以
问：InnoDB数据存储结构
数据页
4、索引的优劣
优势：
- 提高数据查询效率，降低IO成本
- 通过索引进行排序，降低数据排序成本 降低 CPU消耗
劣势：
- 索引也是一张表，保存了主键和索引的字段，并指向实体表的记录，所以索引占用空间
- 降低表的更新速度 对数据进行增删改的时候，mysql不仅要更新数据 也更新索引的表
5、索引使用场景
- 主键自动建立唯一索引
- 查询中与其它表关联的字段，外键关系建立索引
- 频繁作为查询条件的字段应该创建索引(where 后面的语句)
- 多字段查询下倾向创建组合索引
- 查询中排序的字段 order by
- 查询中统计或者分组字段 count()、 group by
- DISTINCT 字段需要创建索引
不推荐建立索引
- 表记录太少
- 有大量重复数据的列上不要建立索引
- Where条件里用不到的字段不建立索引
- 避免对经常更新的表创建过多的索引
- 删除不再使用或者很少使用的索引
- 不要定义冗余或重复的索引
6、索引分类
6.1、主键索引
设置主键 自动建立主键索引
创建主键索引语法： alter table 表名 add primary key (字段);
删除主键索引语法： alter table 表名 drop primary key;
6.2、唯一索引
给表字段设置唯一索引后，该字段列值唯一 不能重复
创建唯一索引语法
alter table 表名 add unique 索引名(字段)；
create unique index 索引名 on 表名(字段)；
删除唯一索引：
drop index 索引名 on 表名;
6.3、普通索引
只是索引，没有其他约束
单独创建和删除单值索引
alter table 表名 add index 索引名(字段);
create index 索引名 on 表名(字段);
删除普通索引
drop index 索引名 on 表名;
6.4、复合索引
一个索引包含多个列
创建复合索引
create index 索引名 on 表名(字段1,字段2);
alter table 表名 add index 索引名(字段,字段2);
删除复合索引
drop index 索引名 on 表名;
6.5、全文索引
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。 对于大文本数据字段，like模糊查询效率极低，全文索引效率很高。
创建全文索引
create fulltext index content_tag_fulltext
on fulltext_test(字段1，字段2);
删除全文索引
drop index content_tag_fulltext
on fulltext_test;
使用全文索引
select * from fulltext_test
where match(content,tag) against('xxx xxx');
注意：
- match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引
- 需要配置最小搜索长度，mysql配置文件修改，修改后 删除全文索引并重新创建
[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1
- 需要添加 修饰符搜索 (常见词不会搜索出的问题)
select * test where match(content) against('a*' in boolean mode);
- 对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。
问：为什么使用组合索引
比如使用组合索引 a b c 相当于 创建 三个索引：a ,ab ,abc，但其实是一个索引。
比单独设置三个索引，节省空间。
一个表的查询查询会命中几个索引
优化器会选择一个效率最好的索引
物理实现方式分类
非聚簇索引子节点 存放 聚簇索引key，
聚簇索引子节点存放真实数据记录
6.5、聚簇索引
Innodb使用的是 聚簇索引 mysql主键就是聚簇索引
聚簇索引：既是索引目录也是真实数据，根节点记录行的key，叶节点记录真实数据，
每张表都会自动创建有且只有一个的聚簇索引，聚簇索引树根据主键作为key来创建，
如果没有主键，则根据第一个唯一索引作为key创建，
如果唯一索引也没有。则创建一个 6字节递增的隐藏列 db_row_id，以此作为key来创建聚簇索引树，
**查询流程：**以key作为查询条件，通过树可以快速定位子节点，子节点存放的是真是数据，直接返回。
优点：
- 数据访问快：索引和数据在一个 B+Tree中，比非聚簇索引快
- 聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点：
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
- 更新主键的代价很高，因此，对于InnoDB表，我们一般定义主键为不可更新。
-非聚簇索引需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
6.6、非聚簇索引(二级索引、辅助索引)
非聚簇索引就是我们创建的索引
在聚簇索引上创建的索引叫非聚簇索引，除了聚簇索引之外的每个索引都会构成非聚簇索引树。
树的key是 索引对应的列，树的根节点记录索引值作为目录，叶子节点记录聚簇索引key，
查询流程： 以索引key 先查询其子节点 得到聚簇索引key，再拿聚簇索引的key 到聚簇索引树去查找，因为聚簇索引的子节点是真实数据，所以直接返回。
非聚簇索引访问数据总是需要二次查找(在有主键的表上创建普通索引等，再创建的索引就是非聚簇索引)。
辅助索引叶节点存在的是主键值，通过非聚簇索引先找到主键值，再通过主键值找到数据行的数据页，再通过数据页找到数据行。
问：主键索引是聚集索引还是非聚集索引？
在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引
问：什么是覆盖索引
索引列+主键包含SELECT 到 FROM之间查询的列。
就是 查询的字段 都在索引中。
回表
在非聚簇索引查询中，先根据索引key查找对应的子节点，子节点包括对应的聚簇索引key
拿这个聚簇索引key再次查找聚簇索引树的过程就是回表。
如何减少回表
-
1、使用覆盖索引：覆盖索引是一种特殊的索引，它包含了查询中需要的所有数据字段，因此在查询时无需回到数据表中查询数据，而只需直接从索引中获取数据。使用覆盖索引可以减少回表的次数，提高查询性能。
-
2.使用连接查询：当需要查询多个表的数据时，可以使用连接查询，将多个表的数据连接在一起，避免多次回表，提高查询性能。
-
3.使用Redis缓存数据：对于经常访问的数据，可以将其缓存在Redis中，这样在查询时就不需要每次都回表获取数据，而可以直接从Redis中获取，提高查询性能。
-
4.使用分页查询：对于需要查询的数据量很大的情况，可以使用分页查询，将数据分为多个部分查询，避免一次性查询过多数据导致回表次数过多，影响性能。
-
5.尽量避免使用子查询：子查询需要在其外层查询完成后再执行，会增加回表的次数，因此尽量避免使用子查询，可以使用连接查询等方式代替。
-
6.使用索引：为需要查询的字段创建索引，可以加快查询速度，减少回表次数
什么是索引下推
举例：在有 age索引的基础上 执行如 selct * from user where age > 20 and gender = 男’;
MySQL会先将 age大于20的数据筛选出来 ，同时使用索引中的性别信息进行过滤
好处： 减少了读取磁盘的次数和I/O的开销，提升了查询性能。
限制： 不能使用外键、视图和复杂查询
COUNT(*) 、 COUNT(column_name)和COUNT(1)的区别
性能： COUNT(**) 、COUNT(1)、 COUNT(column_name)
COUNT(*) ：返回表的总行数，（表的物理行数）
COUNT(1)：对查询结果集的每一行设置常量，并将常量计数，也是返回总行数
COUNT(column_name)： 返回该字段不为空的 总行数
-
COUNT()：COUNT() 计算在 WHERE 子句中指定的条件下，返回结果集中的所有行数，包括 NULL 值。此语句将对表进行全表扫描，并返回所有满足条件的行数。如果使用聚合函数而没有任何其他列，则通常应使用 COUNT(*)，因为它是最快和最简单的方法。
-
COUNT(column_name)：COUNT(column_name)计算在 WHERE 子句中指定的条件下，指定列中的非空值的行数，忽略 NULL 值。此语句只对指定列中的非空值进行计数，并忽略其中的 NULL 值。
-
COUNT(1)：COUNT(1) 或者 COUNT(*) 的效果是一样的，都会返回结果集中的所有行数，包括 NULL 值。但是，在执行 COUNT(1) 时，并不会检查表格中的数据，而是直接根据表的定义执行计数操作。因此，COUNT(1) 可能比 COUNT(column_name) 快一些。
总的来说，如果只是想统计查询结果集中的行数，不需要进行 NULL 值的计数，则推荐使用 COUNT(1) 或者 COUNT(*)，因为它们的执行效率通常最高。而如果想统计某个指定列中的非空值的行数，则应该使用 COUNT(column_name)。
二、性能分析
1、mysql常见瓶颈
SQL中对大量数据进行比较、关联、排序、分组时CPU的瓶颈。
实例内存满足不了缓存数据或排序等需要，导致产生大量的物理IO。查询数据时扫描过多数据行，导致查询效率低。
2、explain
使用 explain 关键字可以模拟优化器执行sql查询语句，从而知道 mysql 如何处理SQL语句的，可以用来分析查询语句或表结构的性能瓶颈
作用：
-
表的读取顺序
-
哪些索引可以使用
-
数据读取操作的操作类型
-
那些索引被实际使用
-
表之间的引用
-
每张表有多少行被优化器查询
explain 使用：
explain + sql
3、explain 重要字段
1、id
- select查询的序列号，表示查询中执行select子句或操作表的顺序。
- id相同时，执行顺序由上至下。
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行。
- id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。
2、select_type
- SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。
- PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。
- DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。
- SUBQUERY: 在SELECT或WHERE列表中包含了子查询。
3、table
显示这一行的数据是关于哪张表的。
4、type
-
System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
-
Const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
-
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
-
Ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
-
Range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。 -
Index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。
-
All：Full Table Scan，将遍历全表以找到匹配的行。
从最好到最差依次是:system>const>eq_ref>ref>range>index>All 。
一般来说，最好保证查询能达到range级别，最好能达到ref。
5、possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则改索引将会被列出来，但不一定会被查询实际使用上（索引会失效）。
6、key
实际用到的索引
7、ref
显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。
8、rows
rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。
9、extra
一些常见的重要的额外信息：
- Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。
- Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。
- Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。
- Using where：表示使用了where过滤。
三、查询优化
1、索引失效
- 最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
如：该表设置了复合索引，索引列的顺序是 sname，age、score ，可能会使用的索引是 sname、sname，age 、 sname，age、score。
联合索引是在 建立在sname上，在sname上建立age，在age上建立score，可以理解为 sname排好序后，对age排序，再对score排序。 所以where 后一定要有sname 才会走索引。
where 后的 sname，age、score 的顺序无所谓，优化器会帮我们整理成索引的顺序
-
where 中：不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描，from 之前没事
-
不能使用索引中范围条件右边的列。
因为 age做了范围查询，所以 第三个索引列 score 不生效，
就算是 where sname and score and age > 0 ，只会走 （sname，age）索引。因为 sname是有序的，age也是有序的，但因为 age是范围查询，会导致 score是 无序的
-
Mysql在使用不等于时无法使用索引会导致全表扫描
-
like以通配符开头会使索引失效导致全表扫描。like “明%” 是可以使用索引的
如：sname like “明%” and age = 13 , 也是会走sname ，age 索引的 只有%在 前面 会导致 后面的索引字段失效
-
字符串不加引号索引会失效。
-
使用or连接时索引失效。
总结：
建议：
- 对于单值索引，尽量选择针对当前查询字段过滤性更好的索引
- 对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好
- 对于组合索引，尽量选择能够包含在当前查询中where子句中更多字段的索引。
- .尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
- 将需要范围查询的字段 从组合索引中去掉
如索引 a b c： where a =1 and b >1 order by c desc 会导致 C 索引失效，那么把 b从组合索引中删除。
索引失效总结
1、不遵守 最左前缀原则
2、索引列进行进行计算
3、大于等于等范围判断之后的索引会失效
4、like %索引 -会失效
5、or 会失效
6、索引列类似是字符串 不加 引号
7、!= 会是索引失效
2、排序优化(分组优化同理)
组合索引 a b c :在 where 和 order by 和 group by 组合使用 组合索引，满足最左前缀。
- 尽量避免使用Using FileSort方式排序。
- where子句中如果出现索引范围查询会导致order by索引失效。
- order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。
案例：
将需要范围查询的字段 从组合索引中去掉
如索引 a b c： where a =1 and b >1 order by c desc 会导致 C 索引失效，那么把 b从组合索引中删除。
3、关联查询优化
mysql 会把小结果集的为 驱动表，所以大表加上索引
左连接：会左边全表查询，那么右表为驱动表 建议左边加上索引
右连接：会右边全表查询，那么左表为驱动表 建议右边加上索引
四、慢查询日志
1、简介
MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。
2、使用
- 查看是否开启：
show variables like '%slow_query_log%';
- 开启日志：
set global slow_query_log = 1;
- 设置时间:
set global long_query_time = 1;
- 查看时间:
SHOW VARIABLES LIKE 'long_query_time%';
- 查看超时的sql记录日志：Mysql的数据文件夹下
5.5\Data\设备名称-slow.log
注意：调优场景下，一般启动改参数，调优结束 关闭
慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来一定的性能影响。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
