索引是帮助MySQL高效获取数据的数据结构。索引内部存在一个键值和对应数据的物理地址,当数据很多的时候,索引文件会很大,所以一般以文件的形式存储于磁盘中,后缀名为.myi
。
unique
# 创建索引
create [unique] index indexName on table(columnName(length)...)
alter table add [unique] index indexName on(columnName(length)...)
#删除
drop index indexName on table
#查看
show index from tableName
B+Tree索引是非常普遍的一种数据库索引结构。其特点是定位高效、利用率高、自我平衡。
这是一个BTree数据结构图。具体的实现思路是:存在一个根节点存放数据的范围(该范围可以存在多个),其支节点存放的该根节点所在层的具体值,然后支节点的叶子节点中存放的是具体的数据。值得一提的是,其叶子节点为双向链表,保存邻近的叶子节点的地址。
下面模拟一下查找56的过程。
注意的几个点:
B树的每一个节点都有一个固定的层级大小。那么必然会出现的情况是,根节点所在层满了,无法继续添加数据。这个时候怎么办?这个时候索引会进行拆分处理,分配两个数据块A,B,如果新添加的数据大于当前最大的元素,则将该元素放于B,其他的全部放入A;如果新添加的元素小于最大元素则平分数据。刚开始的根节点扩大之前的数据范围,此时其层节点将不再变满。扩张结束。
当数据删除的时候其索引中的数据是不会删除的,所以此时如果想要获取最大数据,就会找到一个废弃的节点,这个时候,就发现内部没有数据。由于叶子节点之间是双向链表,所以会寻找当前值邻近的节点数据。此时花费的时间就会增加。而解决这种情况的方法就是重新构建索引。
当客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给 MySQL Query Optimizer(查询优化器),MySQL Query Optimizer 首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接将值换算为常量值。并对Query中的查询条件进行简化和转换,如去掉一些无用或者显而易见的条件、结构调整等。然后分析Query中的Hint信息,看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划,则会读取所设计对象的信息,根据Query进行写相应的计算分析,然后在得出最后的执行计划。
MySQL的架构中的服务层中存在一个SQL语句优化的模块。他的主要功能是:通过计算分析系统手机到的统计信息,为客户端请求的Query提供他认为最优的执行计划。
此时就会延生出一个问题:开发者自己写的SQL与MySQL优化器执行的过程不一样。这种情况之下就会浪费很多的时间。
CPU
饱和。常常发生在将数据加载到内存中或者从磁盘中读取数据的时候。IO
饱和。常常发生在装入数据远大于内存容量的时候。MySQL通过explain 关键字模拟优化器执行SQL语句的过程,从而对SQL语句进行优化。
explain SQL
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
select 查询的序列号,表示了执行select查询的顺序或操作表的顺序。
可能出现的情况:
derived2
这个代表一张临时表,2
为生成临时表的ID即t3
查询类型。用来区分普通查询,联合查询,子查询等的复杂查询
SIMPLE
。简单的select
查询,查询中不包括子查询或者union
PRIMARY
。查询中若包含任何复杂的子部分,最外层被标记为primary
SUBQUERY
。在SELECT
或者WHERE
列表中中包含了子查询,被标记为subquery
DERIVEd
。在From
列表中包含的子查询被标记为derived
(衍生表)。UNION
。若第二个SELECT
出现在union
之后,则被标记为union
union
包含在from
子句的子查询中,外层的select
被标记为derived
union result
。从union
表中获取结果的select
显示这一行数据是关于哪一张表的
查询的访问类型,查找到需要的数据的访问方法
# 从最好---->最差
system -> const -> eq_ref -> ref -> range -> index -> all
system
。表中只有一条记录的查询。速度最快,在生产中一般不会出现const
。通过索引仅仅查找一次就找到了。用于primary key
和unique
索引,数据唯一。eq_ref
。表中仅仅存在一个值与之相对应。ref
。非唯一索引,返回满足该值的所有行。range
。仅仅检索指定范围的行,使用一个索引来选择行。如使用了between
,<>
,in
等的查询条件index
。仅仅依靠索引查询。all
。遍历全表,不使用索引。小结:system
是表中仅仅一条记录;const
是表中有多条记录,其查询条件可以视作为常量的值,子查询也算常量处理;eq_ref
查询条件为变量,另一个表中仅仅存在一条记录与之对应;ref
是另一个表中存在多条记录与之匹配;range
是获取指定范围的值,不需要全表扫描;index
通过索引扫描数据;all
进行全表扫描数据;
这次查询可能使用到的索引。理论计算得出,实际可能并未使用;
实际使用的索引
使用索引所占的字节大小,越少越好。条件越复杂其字节数越大。
引用其他表的字段
查询到所需要的数据扫描的行数。
是否为分区表
包含不适合在其他列中显示但十分重要的额外信息
Using filesort
。对数据使用一个外部的索引排序,而不是按照表内索引的顺序进行排序。出现这种情况的场景为:一般是联合索引,进行分组或者排序的字段的顺序和构建索引时的字段顺序不同,导致内部排序的时候需要再次进行一次排序。非常影响性能。
t1
表中的联合索引using filesort
Using temporary
使用了临时表保存数据,常见于group By
和order by
。和上面的原因相同。非常影响性能。Using index
表示在 进行select
操作的时候使用了覆盖索引,避免访问了表的数据行,增强了性能。如果同时出现了using where
则表明索引用来读取数据而不是进行查找操作。using where
表示使用索引进行过滤数据using join buffer
表示使用了连接缓存impossible where
表示该语句永远不能获取数据select tables optimized away
表示在没有分组的情况下,基于索引优化MIN/MAX
操作或者对于MyISAM
存储引擎优化Count(*)
操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。distinct
优化distinct。找到第一组匹配的值以后就不再查找。开发中经常需要考虑的就是避免
Using filesort
和Using temporary
操作,增加Using index
操作。
name,age,deptId
name,age,deptId
的条件,使用了索引name,age
字段的条件,使用了索引name
个字段的条件,使用了索引索引失效的情况:
age,deptId
,没有使用索引age
,没有使用。使用了部分索引的情况
name,deptId
,使用了部分索引。仅仅在查询name
的时候使用了索引查询name
和查询两个字段的字节数相同。
where
中使用了between and, <> in
等范围修饰符。使用了部分索引,仅仅对name
使用了索引%like%
此时索引失效%like
此时索引失效image-20200316130315357
like%
此时使用了部分索引%like
看做一种范围查询or
以后也会导致索引失效其实索引失效的起因就是无法从已经排序的内容拿到数据。比如最佳左前缀法则,其索引排序为name,age,deptId
即树上的排序就是先排name
相同的,然后到age
,再到deptId
,即,此时的age
和deptId
的单独顺序已经被name
打乱。
举个栗子:
name age
111 12
112 11
此时在数据结构中的体现就是先111 12再到112 11 ,这个时候其age的顺序就被打乱了。所以不能使用索引对没有name开头的进行查询了。
至于说对数据列进行操作,引发其数据本身变化,这样的操作导致B树中的数据和索引中的数据不一样,肯定不能使用索引进行查询了。
age,deptId
用来进行排序了,没有用来查找age = 12
即另age
等于一个常量,所以此时没有进行文件内排序group by
对索引字段进行排序,此时分组顺序正序,直接使用索引数据进行分组排序group by
对索引字段进行排序,此时分组顺序逆序。出现文件内排序,并使用临时表where
条件的字段值。(当然避免范围查询字段索引)like
而言,其只要以%
开头其索引就无法使用。永远使用数据集小的表去驱动数据集大的表
#假设现在存在一张表A数据多于B,此时需要找到表A中与B重合字段的数据,仅仅需要A的数据
#这是in的写法
select * from A where id in (select id from B) ;
#这是exist 的写法
select * from B where exists(selct 1 from A where A.id = B.id)
下面看一下两者执行顺序
in
方案执行。将子查询的数据放到主查询中。即将B表的数据检索结果放在A表的结果中exist
方案执行。将主查询的数据放到子查询中,于是子查询的SQL执行类型变为了eq_ref
可以看到对于A表进行了全表扫描,然后对是否输出A表数据,进行了判断
**小总结:**如果仅仅需要获取A
表中的数据且该数据和B表中的为共有,除了使用join
以外,也可以使用in和exists
。两者使用的区别是:in
将子查询的数据放置在主查询中作为条件,比较适用于主表数据多于从表数据;而exists
是将主表查询结果放置于子查询中,比较适用于主表数据少于从表数据。
MySQL存在两种排序的算法,FileSort
和Index
排序,其中FileSort
的效率比较低
使用索引进行排序。出现这种排序的场景为
ORDER BY
使用索引最左列排序where
字句与order by
字句满足索引最左前列。如,排序字段为第二个索引字段,而第一个字段在where
条件中为常量,此时会使用Index排序使用文件内排序,采用的算法主要有多路排序和单路排序
MySQL4.1
之前使用双路排序,即扫描两次磁盘,首先读取一个指针和需要排序的列,然后写入buffer
中,排序完成以后,再次获取所有的列;即进行了两次IO存在的问题:
单路算法也延伸了一个问题,其占用空间很大,有可能超过了sort_buffer
的最大容量,所以只能进行分片处理,这个时候其IO量就会增加。
解决办法:
sort_buffer_size
参数的值max_length_for_sort_data
参数max_length_for_sort_data
则使用多路算法,否则使用单路算法key a_b_c(a,b,c) # 创建一个名为key的复合索引在a_b_c表中
order by # 可以使用索引的左前缀
order by a
order by a , b
order by a,b ,c
order by a desc , b desc , c desc
order by #如果where的左前缀为常量,则可以使用索引
where a = const order by b , c
where a = const and b = const order by c
where a = const and b <const order by b,c
#不能使用索引进行排序
order by a asc , b desc #排序不一致
where g = const order b ,c ; #丢失a索引
where a = const order c ; #丢失b索引
where a = const order by a ,d # d不是索引
where a in () order by b,c #范围查询
MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阙值的语句,具体指运行时间操作long_query_time
值的SQL,会被记录到慢查询日志中
long_query_time
默认为10,运行时间在10秒以上的SQL
临时改变日志的方式,当MySQL服务重启以后该修改就失效了
#查看当前数据库的慢查询开启情况和日志存放位置
mysql> show variables like '%slow_query_log%' ;
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /opt/mysql/log/slow_query.log |
+---------------------+-------------------------------+
2 rows in set (0.08 sec)
#开启慢查询
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
#默认慢查询界定时间大于这个值的时候被记录
mysql> show variables like 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
#设置慢查询时间
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
#查询当前设置的慢查询时间,如果不添加global则需要在另一个会话中才可以查询到当前的改变
mysql> show global variables like 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
#查询当前SQL中慢查询的条数
mysql> show global status like '%Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.06 sec)
配置文件版
在my.cnf文件中添加
slow_query_log=1;
slow_query_log_file=/opt/mysql/日志名字
long_query_time=3;
log_output=FILE
s:是表示按何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式,大小写不敏感的
MySQL中提供给开发者的分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优
默认状态为关闭状态。且默认保存15条SQL
# 查询当前数据库的profile状态
mysql> show variables like 'profiling' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
# 开启数据库profile
mysql> set global profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#查看最近执行的SQL
mysql> show profiles ;
+----------+------------+----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------+
| 1 | 0.03361600 | show variables like 'profiling' |
| 2 | 0.00012075 | select name from t_emp group by age |
| 3 | 0.00037275 | select name,age from t_emp group by age,name |
| 4 | 0.00018950 | select name from t_emp group by name |
+----------+------------+--------------
# 查看指定SQL的系统消耗信息
show profile 参数 for query_id
日常开发中需要注意的事情:
converting heap to myisam
查询结果太大,内部不够用存放于磁盘中creating tmp table
创建了临时表,用完删除copying to tmp table on disk
将内存中临时表复制到磁盘中locked
加锁在MySQL中的配置文件中,配置
# 开启全局查询日志
general_log=1
#记录日志文件的路径
general_log_file=/opt/mysql/log
#输出格式
log_output=file
命令行中配置,MySQL服务器重启以后失效
set global general_log=1;
set global log_ouput = 'TABLE';
此后所有的SQL都将被记录到mysql.general_log系统表中
select * from mysql.general_log;
不建议使用这个功能,可以直接使用profile
功能更加强大。