[B站尚硅谷](https://space.bilibili.com/302417610/?spm_id_from=333.999.0.0) ## 1. 什么是索引 官方: 本质是一种数据结构, 是帮助MySQL高效获取数据的数据结构 是排好序的快速查找数据结构 ## 2. 索引的优劣 ### 1. 优势 * 提高数据检索效率，降低数据库的IO成本 * 降低数据排序成本，降低了CPU的消耗 ### 2. 劣势 * 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的 * 因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息 * 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句 ## 3. 索引分类 * 单值索引 即一个索引只包含单个列，一个表可以有多个单列索引 * 唯一索引 索引列的值必须唯一，但允许有空值 * 复合索引 即一个索引包含多个列 ## 4. 索引创建的基本语法 ### 创建 如果是CHAR,VARCHAR类型，length可以小于字段实际长度； 如果是BLOB和TEXT类型，必须指定length。 ``` CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length)); ``` ``` ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length)); ``` ### 删除 ``` DROP INDEX [indexName] ON mytable; ``` ### 查看 ``` SHOW INDEX FROM table_name G ``` ## 5. 哪些情况需要创建索引 1. 主键自动建立唯一索引 2. 频繁作为查询的条件的字段应该创建索引 3. 查询中与其他表关联的字段，外键关系建立索引 4. 频繁更新的字段不适合创建索引 因为每次更新不单单是更新了记录还会更新索引，加重IO负担 5. Where条件里用不到的字段不创建索引 6. 单间/组合索引的选择问题，who？（在高并发下倾向创建组合索引） 7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度 8. 查询中统计或者分组字段 ## 6. 哪些情况不要创建索引 1. 表记录太少 2. 经常增删改的表 3. 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。 ## 7. 性能分析 ### 7.1 Explain 使用: Explain + SQL语句 结果  作用: 1. 表的读取顺序 id 字段 2. 数据读取操作的操作类型 select_type 3. 哪些索引可以使用 possible_keys 4. 哪些索引被实际使用 key 5. 表之间的引用 ref 6. 每张表有多少行被优化器查询 rows ### 7.2 Explain 结果介绍 #### 7.2.1 ID 表的读取顺序 * select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 1. id相同，执行顺序由上至下  2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行  3. id相同不同，同时存在  #### 7.2.2 select_type 数据读取操作的操作类型 1. SIMPLE 简单的select查询，查询中不包含子查询或者UNION 2. PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY 3. SUBQUERY 在SELECT或者WHERE列表中包含了子查询 4. DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。 5. UNION 若第二个SELECT出现在UNION之后，则被标记为UNION; 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED 6. UNION RESULT 从UNION表获取结果的SELECT 7. #### 7.2.3 table #### 7.2.4 type  从最好到最差依次是 system > const > eq_ref > ref > range > index > ALL 一般达到range, 最好能达到ref 1. system 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计 2. const 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量 3. eq_ref 唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描 4. ref 非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 5. range 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引 6. index Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的） 7. all FullTable Scan,将遍历全表以找到匹配的行 #### 7.2.5 possible_keys 显示可能应用在这张表中的索引,一个或多个。 查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用 #### 7.2.5 key * 实际使用的索引。如果为null则没有使用索引 查询中若使用了覆盖索引，则索引和查询的select字段重叠 #### 7.2.6 ref 显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值  #### 7.2.7 rows * 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数 #### 7.2.8 Extra 1. Using filesort 说明MySQL 会对数据使用一个外部的索引排序, 而不是按照表内的索引顺序进行读取. MySQL 无法利用索引完成的排序操作称为 "文件排序" 2. Using temporaty 使用了临时表保存中间结果, 常见于排序 和分组查询 3. Using Index 效率高 ## 8. 索引优化 1. 全值匹配我最爱 2. 最佳左前缀法则 如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。带头大哥不能死,中间兄弟不能断 3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 4. 存储引擎不能使用索引中范围条件右边的列 5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select* 6. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描 7. is null,is not null 也无法使用索引 8. like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作 9. 字符串不加单引号索引失效 10. 少用or,用它连接时会索引失效 ## 9. 查询优化 ### 9.1 永远小表驱动大表, 类似嵌套循环Nested Loop   ### 9.2 order by关键字优化 联合索引的各个排序列的排序顺序必须要一致 ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序 使用Index排序的两种情况 order by 语句使用索引最左前列 使用where子句与order by字句条件列组合满足索引最左前列  ### 9.3 group by关键字优化 和order by 相似 ## 10 slow_query_log 慢查询日志 默认是关闭的 开启: set global slow_query_log = 1 本次有效, 重启数据库就没用了, 长期开启要设置配置文件 Mysql 日志工具  ## 11 Mysql锁机制 ### 11.1 锁的分类 从数据操作的类型（读、写）分 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响, 此时无法修改数据 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。 从对数据操作的颗粒度 表锁 行锁 页锁(前俩锁之前, 了解即可) ### 11.2 表锁（偏读） 偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低  ### 11.3 行锁（偏写） 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁 #### 间隙锁危害  ## 如何锁定一行