Mysql索引和数据接结构

Mysql索引和数据接结构

mysql操作索引：

mysql bin目录下： ./mysql -u root -proot
create index name_1 on user(name) 建立索引
alter table user（username） add index username_index 修改索引
drop index name_1 on user 删除索引
show index from user; 查看索引

create unique index <索引名字>on tablename（字段名）

普通索引–唯一索引–主键索引–复合索引

sql执行计划； explain + sql语句；

这里对联合索引做一个总结，联合索引，顾名思义，普通的索引我们只会对单个字段去构建索引，又分为主键索引和辅助索引，那么联合索引就是对多个字段去构建索引，

从而在某些场景下提升提升查询效率。

最左匹配原则
提到联合索引，不得不提的就是最左匹配原则

所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。

假设，我们对(a,b)字段建立一个索引，也就是说，你where后条件为

a = 1
a = 1 and b = 2
是可以匹配索引的,因为他们都遵循了最左匹配原则。但是要注意的是~你执行

b= 2 and a =1
也是能匹配到索引的，有的小伙伴就会有疑问了，这里没有遵循最左匹配原则呀，不用担心，MySQL内部有一个优化器，Mysql的优化器会自动调整a,b的顺序与索引顺序一致。 相反的，你执行

b = 2
就匹配不到索引了。 而你对(a,b,c,d)建立索引,where后条件为

a = 1 and b = 2 and c > 3 and d = 4
那么，a,b,c三个字段能用到索引，而d就匹配不到。因为遇到了范围查询，当遇到范围查询(>、<、between、like)就会停止匹配。！

联合索引的原理和数据结构
OK，知道了联合索引的最左匹配原则，那它的数据结构和原理是什么样子的呢。

其实它的结构和普通索引一样，还是B+树，但是细节上还是有些不一致的。

虽然依然是B+树，但联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

如图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。

因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态! 什么意思呢？

从全局来看，b的值为1，2，1，4，1，2，是无序的，因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1,4也是有序状态。 因此，你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。

综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

慢查询

开启慢查询日志

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：
通过如下命令开启慢查询日志：
long_query_time：指定慢查询的阈值，单位秒。如果SQL执行时间超过阀值，就属于慢查询
记录到日志文件中。
log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log
的值为ON，否则不会奏效。

查看慢查询日志

文本方式查看
直接使用文本编辑器打开slow.log日志即可。
time：日志记录的时间
User@Host：执行的用户及主机
Query_time：执行的时间
Lock_time：锁表时间
Rows_sent：发送给请求方的记录数，结果数量
Rows_examined：语句扫描的记录条数
SET timestamp：语句执行的时间点
select…：执行的具体的SQL语句

慢查询原因总结

全表扫描：explain分析type属性all
全索引扫描：explain分析type属性index
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *，使用覆盖索引

优化慢查询

1、加索引 alter table student add index(name); //追加name索引
2、加联合索引
alter table student add index(age,name); //追加age,name索引
3、可以看到，index condition pushdown 优化的效果还是很不错的。再进一步优化，我们可以把名
字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。
//为user表添加first_name虚拟列，以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as
(left(name, 1)), add index(first_name, age);
explain select * from student where first_name=‘张’ and age=18;

查看查询日志时间：
show variables like ‘profiling’
set profiling=1; # 打开监控
show profiles; 查看sql执行时间记录