MYSQL

目录

• MYSQL数据库
  • 索引结构
  • 索引分类
  • SQL 性能分析
  • 索引使用
  • SQL优化

MYSQL数据库

索引结构

MySql的索引是在存储引擎层实现的, 不同的存储引擎有不同的结构, 主要包括以下几种

索引结构	描述
B+Tree索引结构	最常见的索引结构,大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才会有效,不支持范围查询
R-tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式.类似于Lucene, Solr, ES

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 可以通过这个网址了解一些算法结构

B Tree和B+Tree相比:

• B+Tree所有的数据都会出现在叶子节点
• 叶子节点之间形成一个单向链表

为什么 InnoDB存储引擎选择使用B+Tree索引结构?

• 相对于二叉树, 层级更少, 搜索效率更高
• 对于B-Tree, 无论叶子节点还是非叶子节点, 都会保存数据, 这样导致一页中存储的键值减少, 指针跟着减少, 要同样保存大量数据, 只能增加树的高度, 导致性能降低
• 相对Hash索引, B+Tree支持范围匹配及排序操作

索引分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键字,而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中, 根据索引的存储形式, 可以分为两种:

分类	含义	特点
聚集索引	将数据存储与索引放在一起, 索引结构的叶子节点保存了整行的数据	必须有,且只有一个
二级索引	将数据与索引分开存储, 索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则

• 如果存在主键, 那么主键就是聚集索引
• 如果不存在主键, 将使用第一个唯一索引 (UNIQUE) 索引作为聚集索引
• 如果表中没有索引, 或没有合适的唯一索引, 则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

索引语法

• 创建索引

  CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);

• 查看索引

  SHOW INDEX FROM table_name;

• 删除索引

  DROP INDEX index_name ON table_name;

SQL 性能分析

SQL 执行频率

MySQL 客户端连接成功之后, 通过 show [session | global] status 命令可以提供服务器状态信息, 通过如下指令, 可以查看数据库的 INSERT, UPDATE, DELETE, SELECT 的频次: SHOW GLOBAL STATUS LIKE 'Com_______' 有七个下划线

慢查询日志

慢查询日志极了所有执行时间超过指定参数 (long_query_time, 单位: 秒, 默认是 10 s) 的所有 SQL 语句的日志. MySQL的慢查询日志默认是没有开启的, 需要在 MySql的配置文件(/etc/my.cnf) 中进行配置:

# 开启MySql慢查询日志开关
slow_query_log=1
# 设置慢查询日志的时间为 2s, SQL语句执行时间超过2s, 就会视为慢查询 从而记录在日志中
long_query_time=2

查看慢查询日志中记录的信息: /var/lib/mysql/locahost-slow.log

查看是否开启了慢查询: show variables like 'show_query_log' 查看是否开启了慢查询日志

profile

执行一些列的业务SQL的操作, 然后通过如下指令查看指令的耗时:

# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定 query_id 的SQl语句每个阶段的耗时情况
show profile for query query_id;
# 查看指定 query_id 的SQL语句 CPU 使用情况
show profile cpu for query query_id;

explain 执行计划每个字段的详情

• id: select 查询的序列号, 表示查询中执行 select 子句或则操作表的顺序(id 相同, 执行顺序从上到下; id 不同, 值越大, 越先执行)
• select_type: 表示select的类型, 常见的取值有 SIMPLE (简单表, 即不适用表连接或则子查询), PRIMARY (主查询, 即外层的查询), UNION (UNION 中的第二个或则后面的查询语句), SUBQUERY(SELECT/WHERE)之后包含了子查询 等
• type: 表示连接查询, 性能由好到差的连接类型为 NULL, system, const, eq_ref, 热风, range, index, all
• possible_key: 显示可能应用在这表上的索引, 一个或则多个
• key: 实际使用索引, 如果为 NULL, 则没有使用索引
• key_len: 表示索引中使用的字节数, 该值为索引字段最大可能长度, 并非实际使用长度, 在不损失精确性的前提下, 长度越短越好
• rows: MySql 认为必须要执行查询的行数, 在 innodb 引擎中, 是一个估量值
• filtered: 表示返回结果的行数占用读取行数的百分行, filtered 的值越大越好

索引使用

验证索引的效率

计算添加索引和未加索引的耗时

最左前缀原则 (建立索引的顺序)

如果索引了多列(联合索引), 要遵守最左前缀法则, 最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列, 如果跳跃某一列, 索引将部分失效(后面的字段索引失效)

范围查询

联合索引中, 出现范围查询(>, <), 范围查询右侧的列索引失效, 只有使用>= 或则 <= 时会使用索引, 所以在业务情况允许的情况下使用>= 代替>, 使用 <= 代替 <

模糊查询

如果仅仅是尾部模糊查询匹配, 索引不会失效, 如果是头部模糊匹配, 索引失效

索引列运算

不要在索引列上进行运算操作, 索引将失效; select * from tb_user where substring(phone, 10, 2) = '15' 等这种直接在 where 后进行运算的

字符串不加引号

字符串类型字段使用时, 不加引号, 索引将会失效

or 连接的条件

用 or 分割开的条件, 如果 or 前的条件中的列有索引, 而后面的列中没有索引, 那么涉及的索引都不会被用到, 所以需要针对 or 后面的列在添加索引

覆盖索引

尽量使用覆盖索引 (查询使用了索引, 并且需要分会的列, 在该索引中已经全部能够找到)

explain 中最后一列

using index condition: 查找使用了索引, 但是需要回表查询数据, using where; using index: 查找使用了索引, 但是需要的数据都在索引中能找到, 所以不需要回表查询数据

前缀索引

当字段类型为字符串(varchar, text等)时, 有时候需要索引很长的字符串, 这回让索引变得很大, 查询时, 浪费大量的磁盘IO, 影响查询效率. 此时可以只将字符串的一部分前缀, 建立索引, 这样可以节约索引空间, 提高效率 语法: create index index_namexxx on table_name(column(n)) n: 就是字符串的需要建立索引的长度

SQL优化

order by优化

• Using filesort: 通过表的索引或全表扫描, 读取满足条件的数据行, 然后再排序缓冲区 sort buffer 中完成排序操作, 所有不是通过索引直接返回排序结果的排序都叫Filesort排序
• Using index: 通过有序索引顺序扫描直接返回有序数据, 这种情况即为 Using index, 不需要额外的排序, 操作效率很高

需要注意的是创建的聚合索引, 在排序中使用时只能同时升序或则同时降序,不能一个降序一个升序这样是不会使用索引的, 除非创建对应的索引, 如: create index index_name on table_name(age asc, phone desc)