网站建设是设,邯郸注册公司流程和费用,家装网站建设预算,京津冀协同发展心得体会2023Mysql知识梳理 索引索引分类索引未命中的原因性能调优命令Explain回表 mysql性能优化事务四大特性事务隔离级别设置事务隔离级别 存储引擎聚簇索引和非聚簇索引聚簇索引非聚簇索引 最左前缀结合原则全文索引 索引
索引分类
mysql有普通索引、空间索引、主键索引、唯一索引、组… Mysql知识梳理 索引索引分类索引未命中的原因性能调优命令Explain回表 mysql性能优化事务四大特性事务隔离级别设置事务隔离级别 存储引擎聚簇索引和非聚簇索引聚簇索引非聚簇索引 最左前缀结合原则全文索引 索引
索引分类
mysql有普通索引、空间索引、主键索引、唯一索引、组合索引和全文索引。
普通索引使用字段关键字建立的索引主要目的是提高查询速度。空间索引前只有MyISAM引擎支持用于空间类型的字段不允许为空。唯一索引索引列中的值必须是唯一的但允许为空值。主键索引是一种特殊的唯一索引不允许有空值组合索引在表中的多个字段组合上创建的索引只有在查询条件中使用了这些字段的左边字段时索引才会被使用遵循最左前缀集合原则。全文索引一种特殊类型的索引主要用于全文搜索。
索引未命中的原因
没有查询条件或查询条件中没有索引条件中有or并且不是所有的条件都有索引like左匹配或者int字段索引列是字符串但是sql中未加引号条件中在索引列使用函数采用not in/not exist查询B-tree索引 is null不走索引is not null才走索引联合索引不满足最左原则
性能调优命令Explain
explain用于解析sql的执行计划执行计划是MySQL为了执行查询语句而制定的优化方案包括数据的读取顺序、表的访问方式、索引的使用情况等信息。 以下是使用EXPLAIN指令的基本语法
EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;使用EXPLAIN指令后MySQL会返回一个表格其中包含以下信息
id查询的标识符id越大的越先执行。select_type查询的类型如SIMPLE、PRIMARY、SUBQUERY等。table被查询的表名。partitions被查询的分区信息。type访问表的方式如ALL、INDEX、RANGE等。 – ALL: 全表扫描 – INDEX 索引全扫描。 – range: 范围扫描。 – ref: 使用非唯一索引或唯一索引的前缀扫描返回匹配某个单独值得记录行。 – eq_ref: 多表连接中使用主键或唯一建作为关联条件。 – const/system: 单表中最多有一个匹配行。主要用于比较primary key 或unique索引因为数据都是唯一的所以性能最优。 – null: 不用访问表或索引直接就能得到结果。possible_keys可能使用的索引。key实际使用的索引。key_len使用的索引长度。ref用于比较的值。rowsMySQL估计需要读取的行数数值越大越不好说明没用好索引。filteredMySQL估计返回的行数占总行数的比例。Extra附加信息如使用了临时表、文件排序等。
回表
回表指当查询条件where条件和返回列不能被索引索引锁覆盖时需要访问索引中没有的数据需要通过回表技术访问数据所在页获取具体得数据。 与回表相对应的是覆盖查询即查询条件能够被索引所覆盖不需要回表查询数据这样查询效率会更高。因此在设计数据库时需要根据具体的情况选择合适的索引策略以提高查询性能。
mysql性能优化
优化sql 1.1 使用explain命令解析sql执行计划 1.2 尽量命中索引 1.3 合理设计索引不是越多越好优化表结构 2.1 经常查询的表尽量避免过大。 2.2 多余常用的字段尽量冗余避免大表进行联合查询。分库分表进行表瘦身 3.1 水平分库以字段为依据将一个库中的数据进行拆分到多个库中。比如按年份进行分库。 3.2 水平分表以字段为依据将一个表中的数据进行才分到多张表中。 3.3 垂直分库以表为依据按业务将表分到不同的库中。 3.4 垂直分表以字段为依据按字段中的活跃度将表中的字段拆分到不同的表比如主表和扩展表。
事务
四大特性
事务的四大特性ACID,ACID是原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability的缩写是数据库事务的基本属性用于确保事务的可靠性和一致性。
原子性事务是一个原子操作单元事务的执行要么全部成功要么全部失败回滚。一致性事务必须保证数据库从一个状态改变为另一个状态并保持数据的一致性和完整性。隔离性多个事务并发执行时一个事务的执行不应影响其他事务的执行。持久性一旦事务提交对数据库的修改是永久的即使系统崩溃或故障也能保持。
事务隔离级别
MySQL数据库的事务隔离级别有四个分别是读未提交(Read Uncommitted)、读提交(Read Committed)、可重复读(Repeatable Read)和串行化(Serializable)。
读未提交事务中的修改即使未提交对其他事务也是可见的。读提交事务只能看见已经提交的数据修改。可重复读同一事务中多次读取数据的结果是一致的但是并发的事务可能会对数据产生影响导致 不可重复读和幻读。串行化最严格的事务隔离级别通过锁机制实现确保事务串行化顺序执行能避免不可重复读和幻读但是效率低下。
mysql默认的事务隔离级别是可重复读。
设置事务隔离级别
在MySQL中可以使用以下SQL语句设置事务的隔离级别
SET TRANSACTION ISOLATION LEVEL isolation_level;存储引擎
mysql主要的存储引擎是MyISAM和InnoDB默认使用的是InnoDB引擎。 使用以下命令查询当前使用的存储引擎
SHOW VARIABLES LIKE storage_engine;使用以下命令查询所有存储引擎
SHOW ENGINES;MyISAM不支持事务、不支持外键索引采用的是非聚簇索引。InnoDB提供了具有提交、回滚等事务功能支持自动增长列外键等功能。采用聚簇索引索引和数据是存储在同一个文件下。
聚簇索引和非聚簇索引
聚簇索引
数据挂载在主索引的叶子节点上。辅助索引的叶子节点指向主索引。
非聚簇索引
主索引和辅助索引的叶子节点指向数据存储的位置。
最左前缀结合原则
最左前缀原则就是使用联合索引时查询条件需要遵循索引中列的顺序从左到右进行匹配。 在创建多列索引时要根据业务需求where子句中使用最频繁的一列放在最左边。当创建abc复合索引时想要索引生效的话只能使用a、ab、ac和abc四种组合。
select a,b,c from table where a v ; #索引生效使用索引a
select a,b,c from table where a v and bv1; #索引生效使用索引ab
select a,b,c from table where a v and cv3; #索引生效使用索引a
select a,b,c from table where a v and bv1 and cv3; #索引生效使用索引abc注意:实践证明where后面的条件顺序并不会影响索引的命中条件即 ba和ab效果一致。原因是mysql在执行前会对where语句进行自行调优。 全文索引
MySQL的全文索引是一种用于搜索文本内容的索引方式它可以通过分析文本内容中的关键词帮助提高检索效率。 MySQL使用全文索引主要分为以下步骤
创建全文索引
ALTER TABLE table_name ADD FULLTEXT(column_name);创建全文索引的查询
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST(keywords);在查询语句中column_name是要查询的列名keywords是要搜索的关键词。
需要注意的是只有MyISAM和InnoDB存储引擎支持全文索引其中MyISAM引擎默认全文索引InnoDB引擎需要手动创建全文索引。全文索引的查询还需要考虑相关性和阈值即匹配结果太少可能会查不到结果。
