Mysql学习-01

Mysql架构

一条查询语句的执行过程

查询缓存用于缓存之前查询过的sql，查询语句时，首先会从查询缓存中获得，如果查询缓存中没有，再从数据库中查询

解析器将sql根据逗号解析成解析树，通过预处理器判断用户是否有查询的权限

通过解析树来查询时，可能存在多种查询方案，查询优化器会将查询语句进行优化，找到效率相对较高的方式查询。查询优化器的生成策略：

1. 使用索引进行查询
2. 提前终止查询，防止全表查询

存储引擎

• 一张表使用的存储引擎的不同决定了表中数据存储结构的不同，存储引擎可分为
  1. InnoDB
  2. MyISAM
  3. CSA
  4. Memory
• Mysql支持哪些存储引擎，可以通过show engines查看

存储引擎的使用

• 使用MyISAM创建一张表表

CREATE table user_myisam(
 id int(11) not null auto increment,
 username varchar(20),
 phone varchar(16),
 age int(11),
 primary key (id) useing Btree,
 key index_phone ('phone') using btree
) engine=myisam auto_increment=2 default charset=utf8;

• 创建INNODB存储引擎的表

CREATE table user_innoDB
(
    id       int(11) not null auto_increment,
    username varchar(20),
    phone    varchar(16),
    age      int(11),
    primary key (id) using Btree,
    key index_phone (phone) using btree
) engine = innoDB auto_increment=2 default charset utf8;

• 使用show variables like 'datadir'查看表数据存储位置，通过查询表存储的数据，可以发现inndb生成了两个文件，myisam生成了三个文件。即InnDB文件为frm和ibd，Myisam数据文件为MYD、MYI、frm

tianluhua@tianluhuadeMacBook-Pro~/usr/local/mysql/data/demo~ ll
total 936
drwxrwxrwx  14 _mysql  _mysql     448 11 13 15:48 ./
drwxrwxrwx  26 _mysql  _mysql     832 11 11 17:46 ../
-rw-r-----   1 _mysql  _mysql    8756 11 12 12:15 data_type_int.frm
-rw-r-----   1 _mysql  _mysql   98304 11 12 12:28 data_type_int.ibd
-rw-r-----   1 _mysql  _mysql    8662 11 12 12:35 data_type_numbers.frm
-rw-r-----   1 _mysql  _mysql   98304 11 12 12:40 data_type_numbers.ibd
-rw-r-----   1 _mysql  _mysql    8616 11 12 13:03 data_type_string.frm
-rw-r-----   1 _mysql  _mysql   98304 11 12 13:04 data_type_string.ibd
-rw-r-----   1 _mysql  _mysql      61 10 30 14:25 db.opt
-rw-r-----   1 _mysql  _mysql    8654 11 13 15:48 user_innodb.frm
-rw-r-----   1 _mysql  _mysql  114688 11 13 15:48 user_innodb.ibd
-rw-r-----   1 _mysql  _mysql       0 11 13 15:47 user_myisam.MYD
-rw-r-----   1 _mysql  _mysql    1024 11 13 15:47 user_myisam.MYI
-rw-r-----   1 _mysql  _mysql    8654 11 13 15:47 user_myisam.frm

查看表的存储引擎

• 查看数据库demo中表的状态

show table status from demo like '表名'\G;

mysql> show  table status from demo like 'user_innodb'\G;
*************************** 1. row ***************************
           Name: user_innodb
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 2
    Create_time: 2021-11-13 15:48:03
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

其中，engine为存储引擎的类型

存储引擎的修改

alter table 表名 engine = 存储引擎名称
-- InnDB、MyIsam

MyISAM

特点

1. 表数据由MYD和MYI两个文件组成
2. 支持表级锁
3. 不支持事务

INNDB

特点

1. 支持事务，即原子性、一致性、隔离性和持久性

• 原子性：要么全部成功，要不全部失败。如果事务执行出现了错误，则数据库要回滚到事务开始执行的地方
• 实现原理：基于Mysql日志系统的redo和undo机制

• 隔离性：在同一时间，只有一个事务处理数据，事务之间互不干扰
• 不考虑隔离性可能存在问题

1. 脏读
2. 不可重复读
3. 幻读

• 隔离级别：

1. 读未提交
2. 读已提交
3. 可重复读
4. 串行化

2. MVCC机制，读不加锁，读写可以并发，写操作不会阻塞读操作

3. 并发性-锁：行级锁、表级锁

4. 独特的索引结构

其他存储引擎

CSA

1. 使用普通csv文件存储，将数据存储在csv中
2. 不支持索引
3. 列不能为空

Memory

1. 数据存储在内存中
2. 支持索引类型为HASH和BTREE类型

系统文件

• 主要负责表中数据和日志存储
• 主要包括三类：数据文件、日志文件和表结构文件

日志文件

• 日志文件记录了数据库操作信息以及错误信息
• 常用的日志文件包括：
  1. 错误日志(Error log)
  2. 二进制日志(Binary log)
  3. redo log
  4. undo log
  5. 通用查询日志(General query log)
  6. 慢查询日志(Slow query log)
  7. 中继日志等

redo log

作用

确保事务的持久性，redo日志记录事务执行后的状态，作用主要有两个：

• 用来恢复未写入数据文件但是实物已经成功的数据
• 在重启mysql服务时，根据redo log进行重做，从而达到持久性的特性

内容

错误日志(Error Log)

• 默认情况下，错误日志存储在数据库数据文件目录下，文件名为hostname.err，其中，hostname为服务器文件名

数据文件

1. InnDB

   • frm：描述文件，保存表相关的元数据信息，包括表结构的定义信息等

   • ibd：默认表空间，存储InnoDB系统信息以及用户数据库表数据和索引

   • ibddata1、ibddata2：存储系统表空间文件，所有表共用

   -rw-r-----   1 74  74    8654 11 13 15:48 user_innodb.frm
   -rw-r-----   1 74  74  114688 11 13 15:48 user_innodb.ibd
   

   -rwxrwxrwx     1 _mysql  _mysql       603 11 11 17:45 ib_buffer_pool*
   -rwxrwxrwx     1 _mysql  _mysql  50331648 11 13 16:38 ib_logfile0*
   -rwxrwxrwx     1 _mysql  _mysql  50331648 10 25 18:05 ib_logfile1*
   -rwxrwxrwx     1 _mysql  _mysql  79691776 11 13 16:38 ibdata1*
   

2. MyISAM

   • frm：存储表相关的元数据信息
   • myd：存储MyISAM引擎的表数据
   • myi：存储MyISAM的表索引

   -rw-r-----   1 74  74       0 11 13 15:47 user_myisam.MYD
   -rw-r-----   1 74  74    1024 11 13 15:47 user_myisam.MYI
   -rw-r-----   1 74  74    8654 11 13 15:47 user_myisam.frm
   

磁盘

Mysql读取磁盘方式

• mysql本身是无法直接读取磁盘文件的，只能读取用户空间的数据

• 操作系统会将硬盘数据读取到内核空间，然后将内核空间数据复制到用户空间

• mysql的存储引擎InnDB读取数据采用页为单位读取，默认每页为16K，可以通过innDB_page_size进行配置

基本使用

Linux离线安装

在linux系统中，可以通过命令启动mysql

systemctl start mysqld

mysql -h host  -u user -p

• 使用命令行登录后，可能会要输入密码，此时可以从启动日志中查看默认密码/var/log/mysqld.log

命令

导出和导入

导出

tianluhua:$ mysqldump -h hostname -u user_name -p database_name table1 table2 -d --add-drop-table --column-statistics=0 > data.sql

• hostname：数据库的ip，如果是本地，可以不写
• -p：表示密码登录，随后会要求你输入密码
• table1 table2：导出数据库特定表，如果不写，则导出全部表
• column-statistics:为可选项，如果不加可能会出现异常
• -d：可选项，表示仅仅导出表结构，没有数据
• add-drop-table：在每个create table语句前添加drop table语句

导入

• 导出数据使用source命令

1. 使用mysql -u root -p进入数据库命令行
2. 使用use database_name切换到数据库
3. 使用source data.sql导入数据库

数据库管理

修改用户密码

alter user 用户名 identified by 新密码

查看数据库安装地址

show variables like 'datadir';

数据类型

整数类型

• 数字类型分为整数和实数两种类型。整数主要有，其范围 $-2^{(N-1)}到2^{(N-1)} -1$，N表示位数

类型	位数
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

• 整型类型有可选的UNSIGNED属性，表示不允许复值，即无符号整型。二者使用相同的存储空间，因此使用起来性能差不多。只不过，无符号类型表示范围为$0到2^{(N-1)}$
• MySql可以指定整型类型宽度，例如INT(11)，但是这种制定宽度没有任何意义，只不过在使用工具显示时，会显示出字符的个数，但是存储时还是可以超过制定范围。

实数类型

• 实数类型包括：float、double和decimal

类型	位数
float	4
double	8
decimal	9

• decimal

decimal(m,n)表示最多存储m个数字，n为小数位。如果数字超过m,则会报错，超过n，则会截断(采用四舍五入的方式)。例如：

decimal(5, 2)，最多存储的值为-999.99～999.99，如果插入12.3434，则会报错，因为超过了对多的数字5

字符串类型

Varchar和Char

1. VarChar

varchar类型用于存储可变长度字符串

1. Char

char类型用于存储固定长度的字符串

BLOB 和TEXT类型

• BLOB和TEXT都是为大数据而准备的数据类型，分别采用二进制和字符类型
• 和其他类型不同，当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域来存储，此时每个值在行内只需要1～4个字节存储一个指针，然后在外部区域存储实际的值

使用枚举类代替字符串

MySql索引

索引类型

• 索引是帮助我们高效获取数据的一种数据结构，Mysql采用B+树的数据结构
• 索引种类有：
  1. 主键索引
  2. 普通索引
  3. 唯一索引
  4. 全文索引
  5. 前缀索引
  6. 组合索引

1. 主键索引
   • 索引列中的值必须唯一并且不能为空
   • 使用方式为：

alter table table_name add primary key (column_name);

2. 普通索引

   • 使用没有任何限制，可以为空，也可以插入重复值

   • 使用方式：

alter table table_name add index index_name(column_name);

3. 唯一索引

   • 使用没有任何限制，可以为空，但是值必须为唯一

   • 使用方式

create unique index index_name on table(column_name)

4. 全文索引

   • 只能在文本类型CHAR、VARCHAR、TEXT类型字段上创建全文索引

   • 创建有两种方式：

• 在创建表时，制定全文检索字段

create table t_fulltext(
id int(11) not null auto_increment,
content varchar(100),

fulltext key idx_content(content)
)

• 创建表后，通过alter 修改表结构

alter table table_name add fulltext index index_name(column_name)

• 使用方式，可以使用match() .... against 语法进行全文检索

select * from t_fulltext where match(content) against(Mysql数据库)

5. 前缀索引

   • 在对列的值较大的字段添加索引时，如果值过大，可以通过前缀索引，即索引匹配前多少个字符

   alter table table_name add key(column_name(5));
   

6. 组合索引

   • 由两个或两个以上字段组成的索引

   • 使用方式：

     alter table table_name add index (column_name1, column_name2);
     

索引删除

drop index index_name on table_name

查询索引

show index from auth_user;

索引底层

Hash

索引优化

独立的列

索引分类

聚簇和非聚簇索引

• 聚簇索引：将数据存储和索引放到一起，并且按照一定的顺序组织，数据的物理存放顺序与索引位置一致的。即只要索引是相邻的，则对应的数据一定按照相邻的物理位置存储在磁盘中
• 非聚簇索引：叶子节点不存储数据，存储的是数据行地址。即通过索引找到数据行的位置后，再取磁盘中读取数据。

优势： 1、查询数据可以通过聚簇索引直接获取数据，相对于非聚簇索引效率(覆盖索引情况除外)相对较高 2、聚簇索引对于返回查询效率较高，因为其数据是按照大小排序的 3、聚簇索引适合用在排序的场合中

缺点： 1、维护索引成本较高，特别是在插入新行或主键更新导致分页的时候。 2、表使用UUID(或随机ID)作为主键时，使得数据稀疏，可能导致比全表查询更慢。 3、如果主键比较大时，辅助索引将会变很大，因为辅助索引的叶子节点存储的是主键值，过长的主键值，会是的叶子节点占用较大的物理空间。

组合索引

• 例如，user表，组合索引为 (name,age)

• 组合索引在B+树中的排序方式，首先根据name进行排序，name相同的字段，再根据age进行排序。因此mysql遵循最左匹配原则

  • 例如组合索引 (name,age)

  • select * from user where name = 1 and age = 10

  • select * from user where name > 1 and age > 10

  • select * from user where age = 10

  • select * from user where age > 10

  • explain select id, name, age, DEPT_ID from USER where id = 10 or age > 10
    /**由于dept_ID不是索引列，如果使用组合索引(naame, age)进行查询时，则会出现查询后，又需要回表查询在，这样，查询的行数反而增多，因此，mysql进行了优化，直接使用全表查询，而不是用索引查询*/
    

MySql锁

读锁

• 也叫共享锁，S锁。如果事务A对数据1增加了读锁，则事务A只能读数据1，而不能修改数据1；而其他的事务只能对数据1添加读锁，不能添加写锁，直到事务A释放了锁。保证在读取过程中，没有其他事务对数据进行修改

写锁

• 也叫排他锁，X锁。

表锁

行锁

数据库事务

• MyIsam不支持事务，这里说的是InnoDB搜索引擎

特点

原子性

• 一个事务中的操作要么全部成功，要么全部失败

实现原理

• Undo Log是为了实现事务的原子性，在Mysql数据库中InnoDB存储引擎中，还使用Undo Log来实现多版本并发控制(MVCC)
• 在操作任何数据之前，先将之前的数据备份到临另外一个地方，在进行数据修改时，如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log将数据恢复到事务之前的状态。
• Undo Log是逻辑日志，可以理解为：
  1. 当delete一条记录时，undo log会记录一条insert记录
  2. 当insert一条记录时，undo log会记录一条delete记录
  3. 当update一条记录时，undo log会记录一条相反的update记录

一致性

• 事务的执行结果必须使数据库从一个一致性状态转移到另一个一致性状态
  • 例如：转账业务，A转账给B，最终保持A减少1000，B增加1000。

隔离性

• 事务具有隔离性，即事务之间的执行相互不受影响

隔离级别

• 通过select @@tx_isolation查询当前隔离级别

• 通过set session/global transaction level 隔离级别设置隔离级别。

• 脏读

  • 事务读取到其他事务未提交的数据。

  • 事务1和2对某个数据进行+1操作，但是事务1在操作数据过程中出现了异常，回滚了事务，期望结果增加了1，但是实际情况是增加了2。

• 不可重复读

  • 一个事务内读取数据过程中，由于另外一个事务对数据进行了更新，导致多次读取数据的结果不一致。

• 幻读

  • 和不可重复读类似。事务1和事务2，事务1在开启事务后，添加了一条数据，事务2添加相同的一条记录，首先判断了一下数据不存在。此时事务1提交了事务，事务2也提交了数据，出现了违反唯一性约束的异常。

读未提交(Read Uncommited)

• 一个事务读取到另外一个事务未提交的数据

• 可引发脏读、不可重复读和幻读

读已提交(read commited)

• 一个事务必须等到另外一个事务提交后，才能读取到修改后的值

• 可解决脏读

可重复读(Repeatable read)

• 一个事务在读完数据时，不允许其他事务对数据进行修改

• 可解决脏读和不可重复读，不可解决幻读问题，通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

串行话(Serializable)

• 事务串行顺序执行

• 可解决脏读、不可重复读和幻读

持久性

• 事务一旦被提交，其对数据库的更新是持久的，任何事务或者系统故障都不会导致数据丢失。

InnoDB读写数据原理

• Buffer Pool是数据库数据的缓冲，为了减少读写数据时的IO操作造成的开销。

• 当读取数据时，会首先从Buffer Pool中读取数据，如果Buffer Pool中没有数据，则从磁盘中读取数据后，放入Buffer Pool中；

• 当写入数据时，首先会讲数据写入到Buffer Pool页，这些修改的数据页会在后续某个时刻通过其他后台线程异步刷新到磁盘中。

• 在写入数据时，会产生日志文件，记录数据库操作的sql语句，即Redo Log。为了提高性能，Redo Log也有两种不同的类型，即

  • 内存中的日志缓冲
    • 先修改Buffer Pool，后写 redo log buffer。
    • redo日志比数据页先写回磁盘：事务提交的时候，会把redo log buffer写入redo log file，写入成功才算提交成功（也有其他场景触发写入，这里就不展开了），而Buffer Pool的数据由后台线程在后续某个时刻写入磁盘。
    • 刷脏的时候一定会保证对应的redo log已经落盘了，也即是所谓的WAL（预写式日志），否则会有数据丢失的可能性。
  • 磁盘中的日志缓冲

实现原理

• Redo Log实现事务的持久性。

MVCC(多版本并发控制)

• 在同一行数据进行读写时，会上锁堵塞保持数据能够保持安全性。而MVCC能够做到在发生读写冲突时，不需要加锁
  • 这里的读指的是快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。

快照读

当前读

• 读取的数据库记录都是当前最新的版本，即对当前读取的数据进行加锁，防止其他事务修改数据

mysql 调优

监控数据库工具

Query Profiler

• 诊断Query工具，可以用来分析每条sql语句的执行时间，默认是不开启的，可通过命令开启

  set profiling=1

• 查看每条语句的执行时间

  show profiles;

• 在输入查看语句后，通过命令可以查看执行sql每个过程所耗费的时间

  show profile;

performance_schema

• MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。

1. 提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息

2. performance_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里？一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段（如sql语句执行过程中的parsing 或 sorting阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。

3. performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。

4. performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。

5. 当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象（如mutex或file）相关联的活动。

6. PERFORMANCE_SCHEMA存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同

7. 收集的事件数据存储在performance_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（如动态修改performance_schema的setup_*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）

8. performance_schema的表中的数据 不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表在内的整个performance_schema下的所有数据）

9. MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。

配置和使用

哪类的SQL执行最多？

SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC \G;

哪类SQL的平均响应时间最多？

SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类SQL排序记录数最多？

SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类SQL扫描记录数最多？

SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类SQL使用临时表最多？

SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪类SQL返回结果集最多？

SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

哪个表物理IO最多？

SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;

哪个表逻辑IO最多？

SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC

哪个索引访问最多？

SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC

哪个索引从来没有用过？

SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

哪个等待事件消耗时间最多？

SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC;

剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息

SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';

查看每个阶段的时间消耗

SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;

查看每个阶段的锁等待情况

SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

show processlist

• 用于查询连接的信息

执行计划 explain

explain select * from demo.ath_user;

• explain中的字段

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
extra

select_type

查询的类型，包括普通查询、联合查询，子查询等。

• SIMPLE：简单的select查询，查询中不包括子查询和UNION查询

• PRIMARY：查询中包含子查询，最外层的查询被标记为primary

• UNION：表示UNION中的第二个语句或者select后面的语句

• DEPENDENT UNION：union 中的第二个或后面的 select 语句，依赖于外面的查询

• UNION RESULT：union 的结果

• SUBQUERY：子查询中的第一个 select

• DEPENDENT SUBQUERY：子查询中的第一个 select，依赖于外面的查询

• DERIVED：派生表的 select（from 子句的子查询）

type

访问类型，SQL查询中的一个重要类型。结果值从好到坏依次为：

• system
  • 表中只有一行记录
• const
  • 该表最多由一个匹配行，用于主键或唯一索引查询时
• eq_ref
  • 在join查询中，使用了主键或唯一索引查询
• ref
  • 使用非唯一性索引查询
• range
  • 索引范围查询
• index
  • 使用了索引进行查询
• all
  • 未使用索引查询，全表查询

possible_keys

• 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了

key

• 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

key_length

• 索引长度 char()、varchar()索引长度的计算公式：
• 当索引为固定长度时，比如char,int,datetime。如果没有标记不可为null，则标记需要占用1个字节
• 对于变长的类型，如varchar，处理标记是否为null，还需要有长度信息，需要2个字节
• int占四个字节，date占三个字节，char(n)占n个字节

rows

• 估算出查询所需读取的行数

filtered

• 表示返回的结果行数与总行数的百分比

Extra

• Using index：表示查询过程中，使用了覆盖索引
• Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。
• Using index for group by：优化器只需要使用索引就能处理 group by 或 distinct 语句。
• Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。
• Using MRR：优化器使用 MRR 优化
• Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。
• Using where：表示 SQL 操作使用了 where 过滤条件。
• Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

数据类型优化

1. 更小的数据类型最好
2. 简单最好，即如果是String类型，使用varchar，如果是整型，尽量使用整型
3. 尽量避免使用null

char和varchar

1. varchar适合存字符串不经常更新的数据
2. char可以存储长度最大为255，适合经常更新的数据

dateTime和timestamp

dateTime

1. 占用8个字节
2. 与时区无关存储范围较大，从1000到9999
3. 不要使用字符串存储日期类型，占用空间大，损失日期函数的便捷性

timestamp

1. 占用4个字节
2. 时间范围：1970-01-01至2038-01-19
3. 精确到秒
4. 采用整型存储
5. 依赖与数据库的时区

date

1. 占用字节比int、datetime、字符串少，占用3个字节
2. 可利用日期函数进行日期的计算
3. 保存日期范围为1000-01-01至9999-12-31

使用枚举类型替代字符串

1. mysql存储枚举类型非常紧凑，会 根据列表值的数量压缩到一个或两个字节中，mysql在内部将每个值在列表中位置存储为整型

alter table ath_user add column sex ENUM('男', '女')

索引优化

• 执行计划中的范围优先级

• system > const > eq_ref > ref > range > index > all
  

  1. const 最多匹配一行记录
  2. eq_ref 根据唯一非空索引查询
  3. ref 根据非唯一非空索引查询
  4. range 在一个索引中范围查询
  5. index 遍历索引树进行查询

索引匹配方式

• 创建表并设置组合索引nāme, age, pos

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间') charset utf8 comment '员工记录表';
    
alter table staffs add index idx_nap(name, age, pos);

索引全匹配

• 查询时，和索引的所有列都进行匹配

select * from staffs where name='tianluhua' and age = 13 and pos = '123';

匹配最左前缀

• 只匹配索引前几列，优先匹配最右边的列

-- 使用索引
select * from staffs where name='tianluhua' and age = 13;
-- 不使用索引
select * from staffs where name='tianluhua' and id = 1;

匹配列前缀

• 可以匹配某一列的开头部分，如果在查询前使用%，则不使用索引

-- 使用索引
select * from staffs where name like 'tianlu%';
-- 不使用索引
select * from staffs where name like '%tianlu';

匹配范围值

• 可以查询一个范围的数据

select * from staffs where age > 13;

精确匹配某一列并范围查询另外一列

select * from name = 'tianluhua' and age > 13

只访问索引的查询

• 查询时，只访问索引的数据，不访问其他的数据列，即只查询出索引相关的数据。本质上就是覆盖索引

select name, age, pos from name = 'tianluhua';

索引分类

哈希索引

• 基于哈希表的实现，只能精确匹配索引所有列的查询才有效
• 只有memory存储引擎支持哈希索引

限制

1. 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
2. 哈希索引数据并不是按照索引值顺序排序，因而无法进行排序
3. 无法进行匹配查询和范围查询
4. 哈希冲突比较多时，维护成本较高

场景

• 当需要存储大量的URL，并需要在URL上创建索引进行搜索查找时，使用B+树存储时，直接在URL上创建索引，可能索引的值占用比较大，从而每块磁盘存储的数据相对较少，即B+树深变大，IO读写操作变多
• 可以利用CRC32来做哈希，我们在hash列创建索引可以使用下面的查询方式

select id from url where url='' and url_hash=CRC32('http://enegrtgthgirtghurhtghtr');

组合索引

案例

• 创建组合索引 a,b,c
• 查询语句
  1. where a = 1 只使用了ā
  2. where a = 1 and b = 5 只使用了ā和b
  3. where b = 1 and c = 5 不会使用索引
  4. where a = 1 and b = 5 and c= 6 使用索引 a, b, c
  5. where a = 1 and b > 5 只使用了a和b

聚簇索引和非聚簇索引

聚簇索引

• 数据和索引结构放在一块的索引
• mysql默认使用主键作为聚簇索引，如果没有主键，InnoDB则选择一个唯一且非空索引代替，如果没有这种索引，则隐式定义一个主键作为聚簇索引

优点

1. 可以把相关数据放到一起
2. 数据访问更快，由于索引和数据存放在同一个树中
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

1. 聚簇索引最大限制提高了IO密集型应用的性能，如果全部存储在内存中，则没有优势了
2. 维护索引成本较大，当插入新记录或者主键被更新时，会移动行

非聚簇索引

• 数据和索引结构不放在一块的索引

覆盖索引

• 如果一个索引包含需要查询的字段的值，称之为覆盖索引。例如，查询age、náme两个列，则组合索引nāme和age就是覆盖索引

优点

1. 索引条目远小于数据行大小，只需要读取索引，极大减少数据访问量和IO操作

索引优化

1. 当使用索引列进行查询时，尽量避免使用表达式，而将计算放到业务层

2. 尽量使用主键查询，因为主键查询不会触发回表查询

3. 尽量使用短索引，如果需要使用长字符串列作为索引，应指定一个前缀长度，这样可以节省大量的索引空间

4. 使用索引扫描进行排序

   • 例如创建组合索引 (name, age)，如果要使用name进行排序，则可以使用索引排序

     1. 可以使用索引排序

     select * from ath_user where age = 10 order by name;
     

     2. 不可以使用索引排序，这是由于最左匹配原则，当索引最左字段使用范围查询，则后面的字段将会失效，因此不会使用索引排序

     select * from ath_user where age > 10 order by name;
     

     3. 使用组合索引时，如果一个字段升序，一个字段降序，则也不会使用索引

     select * from ath_user where age = 10 order by age desc, name asc;
     

5. 如果有union all、in和or，则推荐使用in这种方式查询，如果能用union all就别使用union，后者有去重的操作

   • 一般来说，对于索引列最好使用union all，而不是使用or或in，这是由于后者可能会出现索引失效的情况，除非你能确定一定会使用索引
   • 如果是非索引列，则使用or或in效率高

6. 范围列可以用到索引

   • <、<=、>、>=、between
   • 范围列可以使用索引，但是只能用一个范围列