Mysql索引

MySQL

1.索引

1.1索引概述

索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定算法的数据结构，这些数据结构以某种方式引用(指向数据)，这样可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1.2索引优势和劣势

优势

• 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势

• 索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
• 虽然索引大大提高了查询效率，同时却也降低更新表的速度。比如insert,update,delete。更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

1.3索引结构

平常所说的索引，如果没有特别指明，都是指B+树(多路搜索树)结构组织的索引。其中聚集索引，复合索引，前缀索引，唯一索引默认都是使用B+tree索引，统称为索引。

1.3.1BTREE结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

• 树中每个节点最多包含m个孩子。

• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。

• 若根节点不是叶子节点，则至少有俩个孩子。

• 所有的叶子节点都在同一层。

• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1

1.3.2 B+TREE结构

B+TREE为BTree的变种，B+Tree与BTree的区别为：

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key
• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列
• 所有的非叶子节点都可以看作是key的索引部分。

1.3.3 MySQL中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问的性能。

1.4索引的分类

1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 复合索引：即一个索引包含多个列

1.5索引语法

1.5.1创建索引

create index idx_name on city(city_name);
idx_name 索引名字
city 给哪个表创建索引
city_name 给哪个列创建索引

1.5.2查看索引

show index from city
city 给哪个表创建索引

1.5.3删除索引

drop index idx_name on city;
idx_name 要删除的索引名字
city 删除哪张表的索引

2.存储引擎

2.1存储引擎概述

和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

InnoDB是MySQL默认选择的存储引擎（支持事务，支持行锁，支持外键）

3.SQL优化

如果出现慢SQL，首先我们先排查SQL慢的原因，其次在考虑如何优化

3.1查看SQL执行频率

SHOW GLOBAL STATUS	LIKE 'Com_______';  查看本次的连接
SHOW GLOBAL STATUS  LIKE 'Innodb_rows_%'; 针对Innodb存储引擎来查看的

3.2定位低效率的执行SQL

• 慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句，用–log-slow-queries[=file_name] 选项启动时，mysqld写一个包含所有执行时间超过long-query-time秒的SQL语句的日志文件。
• show processlist:慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j9QSCGBD-1617081911318)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20210324175304066.png)]

3.3explain分析执行计划

EXPLAIN SELECT * FROM disc_file_info WHERE id = '1334041876043661314';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Snw190gu-1617081911322)(C:\Users\Dell\AppData\Roaming\Typora\typora-user-images\image-20210324175811603.png)]

3.3.1 explain之ID

ID字段是select 查询的序列号，是一组数字，表示的是查询中执行select子句或者操作表的顺序。

1. ID相同表示加载表的顺序是从上到下。
2. ID不同ID值越大，优秀级越高，越先被执行。
3. ID有相同，也有不同，同时存在。ID相同的可以认为是一组，从上往下顺序执行;在所有的组中，ID的值越大，优先级越高，越先执行。

3.3.2 explain之select_type

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在from列表中包含的子查询，被标记为DERIVER(衍生)MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

注意：以上在从上往下效率是越来越低

3.3.2 explain之table

展示这一行的数据是关于哪一张表的。

3.3.3 explain之table

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行的数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。const于将“主键”或“唯一”索引的所有部分与常量值进行比较
eq_ref	类似ref,区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描。
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。where之后出现between,<,>,in等操作
index	index与ALL的区别为index类型只是遍历了索引树，通常比ALL快。ALL是遍历数据文件。
all	将遍历全表以找到匹配的行

3.3.4 explain之key

possible_keys: 显示可能应用在这张表的索引，一个或多个。

key: 实际使用的索引，如果为NULL,则没有使用索引

key_len: 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

3.3.5 explain之rows

扫描的行数

3.3.6 explain之extra

其他的额外的执行计划信息，在该列展示。 ·

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”，效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by 和group by;效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错

3.3.7 show profiles;

​ SELECT @@have_profiling; 查看是否支持

​ SELECT @@profiling; 查看是否开启 1开启 0未开启

​ set profiling=1; 设置成开启状态

查看SQL的执行阶段的时间

show profile for query 5;  5是在执行  show profiles;后的标识

4.索引的使用

4.1.1避免索引失效

create index idx_name on city(city_name,age,sex);

1. 全值匹配,对索引中所有列都指定具体指。
2. 最左前缀索引法则
   • city_name age 走索引
   • city_name sex 走索引 走city_name的索引
   • age sex 不走索引
   • city_name 走索引
3. 范围查询右边的列，不能使用索引。
4. 不要在索引列上进行运算操作，索引将失效。
5. 字符串不加单引号,索引将失效。
6. 尽量使用覆盖索引，避免select *(会有回表查询的概率)
7. 用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
8. 以%开头的Like模糊查询，索引失效。如果仅仅尾部匹配，索引不会失效。如果是头部模糊匹配，索引失效。解决：可以使用覆盖索引来解决
9. is NULL , is NOT NULL 有时索引失效 NULL值不多的情况，IS NULL走索引 NULL值多的时候，IS NOT NULL走索引
10. in走索引，not in 索引失效。
11. 单列索引 和 复合索引，尽量使用符合索引，而少用单列索引。

4.1.2索引的使用情况

show status like 'Handler_read%';
show global status like 'Handler_read%';

5.MySql锁

5.1锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制

5.2锁分类

从对数据操作的粒度分：

• 表锁：操作时，会锁定整个表（MyISAM支持表锁）
• 行锁：操作时，会锁定当前操作行。(InnoDB支持行锁)

从对数据操作的类型分：

• 读锁：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁：当前操作没有完成之前，它会阻断其他写锁和读锁

6.二进制日志

6.1概述

​ 二进制日志记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句，但是不包括数据查询语句，此日子对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过binlog实现的

默认不开启：配置文件为my.cnf

#配置开启binlog日志，日志的文件前缀为mysqlbin---->生成的文件名如：mysqlbin.00001 以此类推
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=STATEMENT

6.2日志格式

STATEMENT

该日志格式在日志文件中记录的都是SQL语句，每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

ROW

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句：update tb_book set status =‘1’; 如果是STATEMENT日志格式，在日志中会记录一行SQL文件；如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中会记录每一行的数据变更。

6.3 慢查询日志

#1开启 0代表关闭
slow_query_log=1
#指定慢查询日志的文件名
slow_query_log_file=slow_query.log
#该选项配置查询的时间限制，超过这个时间将认为是慢查询
long_query_time=2

7Mysql复制

复制分成三步

• Master主库在事务提交时，会把数据变更为时间Events记录在二进制日志文件Binlog中。
• 主库推送二进制日志文件Binlog中的日志事件到从库的中继日志Relay Log.
• slave重做中继日志中的事件，将改变反映它自己的数据。

优势

• 主库出现问题，可以快速切换到从库提供服务。
• 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。
• 可以在从库中执行备份，以避免备份期影响主库的服务

7.1 配置master的配置文件

#mysql 服务ID，保证整个集群环境中唯一
server -id =1;
#mysql binlog 日志的存储路径和文件名
log-bin = /var/lib/mysql/mysqlbin
#是否只读，1代表只读，0 代表读写
read-only = 0
#忽略的数据，指不需要同步的数据库
binlog-ignore-db=mysql

创建同步数据的账户，并且进行授权操作：

grant replication slave on * . *  to 'itcast'@'192.168.192.131' identified by 'itcast';

flush privileges; 刷新权限列表

查看master的状态信息
show master status;

7.2配置从数据库

#mysql 服务ID，保证整个集群环境中唯一
server -id =1;
#mysql binlog 日志的存储路径和文件名
log-bin = /var/lib/mysql/mysqlbin

重启Mysql
service mysql restart;

change master to master_host = '192.168.1.131',master_user='用户名'，master_password='密码',master_log_file='mysqlbin.00001',master_log_pos=413;

#开启同步机制
start slave;
show slabe status\G;