一:哪些情况适合创建索引?
-
字段的数值有唯一性的限制
-
频繁作为 WHERE 查询条件的字段
-
经常 GROUP BY 和 ORDER BY 的列
-
DISTINCT 字段需要创建索引:有时候我们需要对某个字段进行去重
SELECT DISTINCT(student_id) FROM `student_info`; 运行结果(600637 条记录,运行时间 0.683s ): 如果我们对 student_id 创建索引,再执行 SQL 语句: SELECT DISTINCT(student_id) FROM `student_info`; 运行结果(600637 条记录,运行时间 0.010s ):
-
多表 JOIN 连接操作时,创建索引注意事项
首先, 连接表的数量尽量不要超过 3 张, 其次, 对 WHERE 条件创建索引 最后, 对用于连接的字段创建索引 ,并且该字段在多张表中的 类型必须一致
-
使用列的类型小的创建索引
-
使用字符串前缀创建索引
-
使用最频繁的列放到联合索引的左侧
-
在多个字段都要创建索引的情况下,联合索引优于单值索引
二:哪些情况不适合创建索引
-
在where中使用不到的字段,不要设置索引
-
. 数据量小的表最好不要使用索引
-
有大量重复数据的列上不要建立索引
-
避免对经常更新的表创建过多的索引
-
不建议用无序的值作为索引
例如身份证、UUID、MD5、HASH、无序长字符串等。
-
删除不再使用或者很少使用的索引
-
不要定义冗余或重复的索引
(1)冗余索引CREATE TABLE person_info( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, birthday DATE NOT NULL, phone_number CHAR(11) NOT NULL, country varchar(100) NOT NULL, PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name(10), birthday, phone_number), KEY idx_name (name(10)) );
我们知道,通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索,再创建一个专门针对 name 列的索引就算是一个 冗余索引
(2)重复索引
CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);
我们看到,col1 既是主键、又给它定义为一个唯一索引,还给它定义了一个普通索引,可是主键本身就会生成聚簇索引,所以定义的唯一索引和普通索引是重复的,这种情况要避免
三:慢SQL的问题如何排查?
慢查询是指数据库中查询时间超过指定阈值的SQL,这个阈值根据不同的业务来说一般是不一样的,慢SQL的问题排查一般分为几个步骤:发现问题,定位问题,解决问题
发现问题
在MySQL的配置文件 my.cnf, 配置启用慢SQL日志
slow_query_log = 1
slow_query_log_file =/path/to/slow-query.log
long_query_time = 1
定位问题
通过上述的日志中,我们就可以找到对应的慢SQL的具体SQL了,然后就可以进一步分析为什么这个SQL是慢SQL了,大多数情况,是可以通过执行计划分析出一条SQL的慢的原因的,大部分来说,主要是索引的问题和join的问题。
解决问题
一旦一个问题被定位到了,解决起来都比较容易。缺索引就加索引,join太多就拆分就好了。
四:SQL执行计划分析的时候,要关注哪些信息?
一个执行计划中,共有12个字段,每个字段都挺重要的,先来介绍下这12个字段
1.id:执行计划中每个操作的唯一标识符。对于一条查询语句,每个操作都有一个唯一的
id。但是在多表join的时候,一次explain中的多条记录的id是相同的。
2、select_type:操作的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY、UNION
等。不同类型的操作会影响查询的执行效率。
3:table:当前操作所涉及的表。
4.partitions:当前操作所涉及的分区。
5.type:表示查询时使用的索引类型,包括ALL、index、range、ref、eq_ref、const
等。
6. possible_keys:表示可能被查询优化器选择使用的索引。
7. key:表示查询优化器选择使用的索引。
8. key_len:表示索引的长度。索引的长度越短,查询时的效率越高。
9. ref:用来表示哪些列或常量被用来与key列中命名的索引进行比较。
10. rows:表示此操作需要扫描的行数,即扫描表中多少行才能得到结果。
11.filtered:表示此操作过滤掉的行数占扫描行数的百分比。该值越大,表示查询结果越准
确。
12.Extra:表示其他额外的信息,包括Using index、Using filesort、Using temporary
等。
五:执行计划中,key有值,还是很慢怎么办?
执行计划中,type=index,key=idx_abcd 很多人会认为这表示这条SQL走了索引,如果是走了索引Extra中的内容应该是Using index 而不是 using where; using index,这个执行计划表明,这个SQL确实用到了idx_abcd的这个索引树,但是他并没有直接通过索引进行匹配或者范围查询,而是扫描了整颗索引树。
所以,[type=index)意味着进行了全索引扫描,会遍历索引树来查找匹配的行,这个效率比扫表扫描快一些,但是很有限,和我们通常意义上理解的走了索引是两回事儿。遇到这种情况,大概率是因为没有遵守最左前缀匹配导致的索引失效了。
六:索引失效的问题如何排查?
第一步:先找到要分析的SQL语句,然后通过explain查看他的执行计划。主要关注type、key和extra这几个字段。
第二步:我们需要通过key+type+extra来判断一条SQL语句是否用到了索引。
第三步:如果通过执行计划之后,发现一条SQL没有走索引,进一步分析没有走索引的原因。以下几种情况可能会导致没走索引:
- 没有正确创建索引:当查询语句中的where条件中的字段,没有创建索引,或者不符合最左前缀匹配的话,就是没有正确的创建索引。
- 查询语句中,索引字段因为用到了函数、类型不一致等导致了索引失效
- 索引区分度不高:如果索引的区分度不够高,那么可能会不走索引,因为这种情况下走索引的效率并不高。
- 表太小:当表中的数据很小,优化器认为扫全表的成本也不高的时候,也可能不走索引
七:索引失效案例
- 全值匹配我最爱
- 最佳左前缀法则
- 计算、函数、类型转换(自动或手动)导致索引失效
- 范围条件右边的列索引失效
- 不等于(!= 或者<>)索引失效
- is null可以使用索引,is not null无法使用索引
- like以通配符%开头索引失效
- OR 前后存在非索引的列,索引失效
八:什么是最佳左前缀匹配?
在MySQL中,最佳左前缀匹配是指在查询中利用索引最左边的一部分来进行匹配,如果查询条件涉及到组合索引的前几个列,MysQL 就可以利用这个复合索引来进行匹配。
举例说明:假如我们创建了一个组合索引(col1, col2, col3)
如果你的查询条件是针对 col1 、(col1, col2)或者(col1, col2, col3),那么MySQL可以利用这个复合索引进行最左前缀匹配。
如果查询条件涉及到的列只有 col2 或者只有col3或者只有col2和col3,总之就是如果不包col1的话,那么是没有遵守最左前缀匹配,就不能利用这个索引进行最左前缀匹配。
并且,需要注意的是,最左前缀匹配和查询条件的顺序没有关系,不管你写的是 where col1 =“Holiis” and col2 ="666"还是(where col2 =“666” and col1=“Holiis”对结果都没有影响,该命中还是会命中。
九:MySQL索引一定遵循最佳左前缀匹配吗?
索引底层是一个B+树,如果是联合索引的话,在构造B+树的时候,先按照左边的key进行排序,左边的key相同时再依次按照右边的key排序。所以,在通过索引查询的时候,也需要遵守最佳左前缀匹配的原则,也就是需要从联合索引的最左边开始进行匹配,这时候就要求查询语句的where条件中,包含最左边的索引的值。
十:MySQL中like的模糊查询如何优化
当like值前后都有匹配符时 %abc%,无法使用索引
当like值前有匹配符时 %abc,无法使用索引
当like值后有匹配符时abc%,可以使用索引
十一:如何进行SQL调优?
某一次线下报警出现了慢SQL,做了性能分析发现瓶颈是在SQL查询上面,然后定位到具体的SQL语句,我们就知道具体是哪张表、哪个SQL慢了,一个SQL慢可能有以下几种原因:
- 索引失效
- 索引区分度不高
- 多表join
- 表中数据量太大
- 查询字段太多
- 数据库连接数不够
- 数据库的表结构不合理
- 数据库IO或者CPU比较高心
- 数据库参数不合理
- 事务比较长
- 锁竞争导致的等待