微盟属于营销型手机网站,黄山建设厅官方网站,微信下安装,可以做多边形背景的网站目录
一、存储引擎
二、索引
索引结构
索引分类 索引语法 联合索引
前缀索引
索引使用规则
最左前缀法则
范围查询使索引失效
字段做运算操作索引失效
字符串字段不加单引号索引失效 字段做前模糊查询索引失效
or连接条件索引失效
数据发布情况索引失效
指定使用…目录
一、存储引擎
二、索引
索引结构
索引分类 索引语法 联合索引
前缀索引
索引使用规则
最左前缀法则
范围查询使索引失效
字段做运算操作索引失效
字符串字段不加单引号索引失效 字段做前模糊查询索引失效
or连接条件索引失效
数据发布情况索引失效
指定使用索引
三、帮助 SQL优化的工具
慢查询日志记录 profiles
explain
四、SQL优化
为什么不要使用select *
插入优化 insert
主键优化
排序优化 order by 分组优化group by
limit优化
count优化
update优化 一、存储引擎
MySQL体系结构 查询当前数据库支持的存储引擎
show engines ; InnoDB是默认的存储引擎也是最常用的广泛应用于各种需要高并发和高可靠性的应用场景支持事务、行级锁和外键 InnoDB引擎的数据文件 xxx.ibdxxx代表的是表名innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构、数据和索引。 表空间 : InnoDB存储引擎逻辑结构的最高层ibd文件其实就是表空间文件在表空间中可以 包含多个Segment段。段 : 表空间是由各个段组成的 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管 理都是引擎自身完成不需要人为对其控制一个段中包含多个区。区 : 区是表空间的单元结构每个区的大小为1M。 默认情况下 InnoDB存储引擎页大小为 16K 即一个区中一共有64个连续的页。页 : 页是组成区的最小单元页也是InnoDB 存储引擎磁盘管理的最小单元每个页的大小默 认为 16KB。为了保证页的连续性InnoDB 存储引擎每次从磁盘申请 4-5 个区。行 : InnoDB 存储引擎是面向行的也就是说数据是按行进行存放的在每一行中除了定义表时 所指定的字段以外 创建表时没有指定存储引擎默认是InnoDB存储引擎。查看指定表使用的存储引擎等信息
show create table emp; MyISAM是MySQL早期的默认存储引擎。(不支持事务不支持外键 支持表锁不支持行锁 访问速度快)
储存的文件 xxx.sdi存储表结构信息 xxx.MYD: 存储数据 xxx.MYI: 存储索引
Memory引擎的表数据时存储在内存中的由于受到硬件问题或断电问题的影响只能将这些表作为临时表或缓存使用 在选择存储引擎时应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统还可以根据 实际情况选择多种存储引擎进行组合。 InnoDB: 是Mysql的默认存储引擎支持事务、外键。如果应用对事务的完整性有比较高的要 求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操 作那么InnoDB存储引擎是比较合适的选择。MyISAM 如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完 整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。MEMORY将所有数据保存在内存中访问速度快通常用于临时表及缓存。MEMORY的缺陷就是 对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性。 二、索引 索引是一种用于提高数据库查询效率的数据结构。通过为表中的数据创建索引即利用数据结构为数据创建一种映射关系可以显著减少查询所需的时间从而提高数据库的整体性能 索引结构
MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的索引结构索引主要有以下几种
索引结构描述BTree索引最常见的索引类型大部分引擎都支持 B 树索引Hash索引底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不 支持范围查询R-tree(空间索 引空间索引是MyISAM引擎的一个特殊索引类型主要用于地理空间数据类 型通常使用较少Full-text(全文 索引)是一种通过建立倒排索引,快速匹配文档的方式。类似于Elasticsearch
B Tree的结构
对于二叉树而言最多只能有两个孩子节点度数最多为 2但B树可以有多个孩子节点
最多有 n 个节点,那么就称为 n 阶B树以 5 阶B树为例
那这个B树每个节点最多存储4个key5 个指针即每个节点最多存储4个值和指向孩子节点的5个指针 查数据时与二叉搜索树类似比插入值大的走右边小的走左边以下5阶B树插入数值19为例其结构示意图 key的数量为5超过了4需要分裂中间元素上移。
BTree索引
BTree是B-Tree的变种在分裂上移时只是将key值赋值一份上移值仍然存储在最下面的叶子节点上也就是说数据只存储在叶子节点 上其它节点储存的是索引key在Mysql的BTree索引中所有的叶子节点会形成双向链表这样就可以做范围查询和排序等操作 为什么InnoDB存储引擎选择使用Btree索引结构? A. 相对于二叉树层级更少搜索效率高 B. 对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储 的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低 C. 相对Hash索引Btree支持范围匹配及排序操作 MySQL中除了支持BTree索引还支持一种索引类型---Hash索引。
Hash索引
将数据库中每一行数据有对应的哈希值当我们对name字段添加索引时就会为name所有值生成对应的hash值映射到对应的槽位上然后将name值和每一行得hash值存储在 hash表中如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决也就是相同hash值得使用链表储存。 Hash索引只能用于对等比较(in)不支持范围查询between ...无法利用索引完成排序操作查询效率高通常(不存在hash冲突的情况)只需要一次检索就可以了效率通常要高于Btree索 引
索引分类 在MySQL数据库将索引的具体类型主要分为以下几类 主键索引PRIMARY、唯一索引UNIQUE、常规索引、全文索引 其中主键索引只能有一个其它可以有多个
主键索引PRIMARY、唯一索引UNIQUE在建表时指定主键或唯一约束时会自动创建索引
常规索引为对特定数据创建索引
聚集索引、二级索引
在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种
聚集索引(Clustered Index)将数据存储与索引放到了一块索引结构的叶子节点保存了行数据必须有,而且只 有一个二级索引(Secondary Index)辅助索引将数据与索引分开存储索引结构的叶子节点存放的值是对应的主键和索引列的值
聚集索引选取规则:
如果存在主键主键索引就是聚集索引。 如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。 如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索 引。 在查询二级索引中的数据时会根据创建的索引去查询对应的主键再根据主键去查询对应的整行数据例如以下SQL
select * from emp where nameArm ;
查询过程是实现在二级索引中查询对应的主键id再根据主键id去聚集索引去查询对应的行数据
而下面的SQL则是直接从聚集索引中查询整条数据效率会更高些
select * from emp where id 10 ; 回表查询 这种先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取 数据的方式就称之为回表查询。 假设: 一行数据大小为1k一页中可以存储16行这样的数据即一页一个叶子节点可以存放16K的数据 InnoDB的指针占用6个字节的空间主键假使为bigint占用字节数为8。一个节点中指针数量永远比主键数量多一所以 高度为2 (n 1) * 6 n * 8 16*1024 , 算出n约为 1170即指针有1171个所以叶子节点有1171个又每个叶子节点可以存储16行数据所以 1171 * 16 18736 即高度为2的树可以存储 18000 多条记录。 高度为3 1171 * 1171 * 16 21939856因为1170个节点中每个节点又可以存储1171个子节点也就是说如果树的高度为3则可以存储 2200w 左右的记录。 阿里巴巴 Java 开发手册V1.2中 14. 【推荐】单表行数超过500万行或者单表容量超过2GB才推荐进行分库分表。 说明如果预计三年后的数据量根本达不到这个级别请不要在创建表时就分库分表。 索引语法
#查询索引
show index from tb_user;
#创建唯一索引
create unique index idx_user_phone on tb_user(phone);
#创建单列索引
create index idx_email on tb_user(email);
#创建联合索引
create index idx_profession_age_status on tb_user (profession, age, status); 联合索引 Composite Index是由两个或多个列组成的索引。这种索引可以提高在多个列上执行查询时的性能。使用联合使用需要遵循最左前缀法则 单列索引和联合索引的选择
当需要多个条件查询时建议创建联合索引。例如
select id,username,password from tb_user where phone 18081404110 and username 张三 ; 若phone和username都是单列索引只能走一个索引所以需要去查询另一个字段必然会走回表查询只是可以对两字段建立联合索引避免。能使用联合索引尽量使用联合索引
前缀索引 前缀索引是指在针对某一字段创建索引时字段类型为字符串varchartextlongtext等例如文章数据时该字段的内容可能会很庞大建立的索引的体积就会非常大浪费大量的磁盘IO 影响查询效率。可以减少索引的存储空间和维护成本 语法通过截取该字段的前一部分来做为索引的值
#以email字段的前五个字符创建索引
create index idx_content on tb_user(content(5)); 前缀长度 可以根据不重复的索引值基数和数据表的记录总数的比值来确定也称选择性 比值越高则查询效率越高 唯一索引的选择性是 1这是最好的索引选择性性能也是最好的。 比值查询示例 select count(distinct content) / count(*) from tb_user ;select count(distinct substring(content,1,5)) / count(*) from tb_user ; 查询流程先通过前缀去索引中查在通过id回表查询查出该字段的整体值比对是否与给出条件符合。 索引设计原则 1). 针对于数据量较大且查询比较频繁的表建立索引。2). 针对于常作为查询条件where、排序order by、分组group by操作的字段建立索 引。3). 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。4). 如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。5). 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间 避免回表提高查询效率。6). 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增 删改的效率。7). 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时它可以更好地确定哪个索引最有效地用于查询。 索引使用规则
最左前缀法则 在使用联合索引时应注意遵守的法则 最左前缀法则查询必须需包含的最左列 并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。 例如以tb_user表的profession, age, status三个字段创建联合索引为例 使用explain关键字可以显示 SQL 查询的执行计划详细看【帮助 SQL优化的工具】
正常使用三字段时走索引三字段都走索引索引长度为49 当查询时没有最左列profession则不会使用索引查询 当只使用profession字段正常走索引索引长度只有profession的长度47 当使用前两个字段时会走索引profession和age两字段时索引长度为49 当中间age字段跳过没有使用时则此后面的字段status使用索引失效索引长度只有profession的长度47 范围查询使索引失效
若某字段出现范围查询小于()、大于()则该字段右侧的列索引会失效例如age使用此范围查询时status的索引会失效但是使用小于等于()、大于等于()时不会失效 【注意】这与我们写的SQL条件先后顺序无关例如必须需包含的最左列是指查询条件中有索引的最左列profession即可使索引失效。SQL中的条件书写顺序无需与索引顺序一致 字段做运算操作索引失效 当我们在使用某字段索引时若对该索引做了运算则会使索引失效。 例如我们对手机号phone字段创建了索引在使用时加了字符串截取的运算查询手机号后四位为4110的用户信息此时会使索引失效 select * from tb_user where substring(phone,7,4) 4110; 字符串字段不加单引号索引失效
在字符串类型的字段做条件查询时没加单引号虽然能查出数据但是不会走索引
select * from tb_user where substring(phone,7,4) 4110 ; 字段做前模糊查询索引失效
当对某字段做模糊查询时只要%加在前面就会使索引失效比如查询姓名以”三“结尾的用户信息
select * from tb_user where username like %三 ; 而以下形式就不会使索引失效查询姓张的用户
select * from tb_user where username like 张% ;
or连接条件索引失效
当使用or做连接条件查询时需要or连接的条件的字段都要有索引才能使索引生效有其中一个字段没有索引索引都不会生效
例如查询性别为男或则年龄为20的用户若age有索引而gender没有索引则索引失效
select * from tb_user where gender 男 or age 20 ;
数据发布情况索引失效 当我们在做查询时绝大部分数据是满足我们的条件时MySQL评估使用索引比全表更慢则不使用索引相反Mysql评估走索引会更快才会走索引。 指定使用索引
当我们在做条件查询时字段有多个索引时可以指定Mysql使用哪个索引有以下三种方式
use 建议Mysql走指定索引其中Mysql会做评估有更快的索引时不会走建议的索引
explain select * from tb_user use index(idx_profession) where profession 英语 ;
ignore 忽略指定的索引
explain select * from tb_user ignore index(idx_profession) where profession 英语 ;
force 强制使用指定索引
explain select * from tb_user force index(idx_profession) where profession 英语 ;
三、帮助 SQL优化的工具 在MySQL中SHOW GLOBAL STATUS 是一个非常有用的命令它显示了服务器级别的状态变量。这些状态变量提供了关于服务器运行时的各种统计信息和性能指标。使用这个命令可以帮助你了解服务器的当前状态诊断问题或者优化数据库性能 使用like关键字进行过滤Com后面7个 _ 模糊查询Com后7个字符的状态信息
show global status like Com_______; 索引主要是针对查询当数据库查询占绝大部分比例时可能需要考虑创建索引来优化数据库性能
慢查询日志记录
Mysql默认关闭需要配置开启才能失效可以通过这些记录去优化这些SQL
查询是否开启慢查询日志记录
show variables like slow_query_log ; 编辑my.cnf文件末尾追加以下内容
sudo vim /etc/mysql/my.cnf 我的是Ubuntu系统其它系统类似windows中是my.ini
[mysqld]
#开启慢查询日志记录当SQL执行时间超过指定时间时就会记录日志到/var/lib/mysql/localhost-slow.log
slow_query_log1
#日志存放地址
slow_query_log_file /var/log/mysql/mysql-slow.log
#指定时间
long_query_time2
查询是否配置成功 profiles
使用profiles查询SQL执行时间等情况
查看当前MySQL是否支持profile操作
SELECT have_profiling ; 开启profiling
SET profiling 1; 查询所有命令执行耗时和根据命令id查询耗时 explain 在MySQL中EXPLAIN 是一个非常有用的命令用于显示 SQL 查询的执行计划。执行计划是数据库如何执行查询的详细说明包括它如何使用索引、表的连接顺序、预计需要扫描的行数等。通过分析执行计划你可以优化查询性能理解查询的工作原理。 EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息多条数据时包括在 SELECT 语句执行过程中表如何连接和连接的顺序 使用直接在select语句之前加上关键字 explain / desc 各字段含义 字段含义idselect查询的序列号表示查询中执行select子句或者是操作表的顺序 (id相同执行顺序从上到下id不同值越大越先执行)。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接 或者子查询、PRIMARY主查询即外层的查询、 UNIONUNION 中的第二个或者后面的查询语句、 SUBQUERYSELECT/WHERE之后包含了子查询等type表示连接类型性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。possible_key显示可能应用在这张表上的索引一个或多个。key实际使用的索引如果为NULL则没有使用索引。key_len表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长 度在不损失精确性的前提下 长度越短越好 。rowsMySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值 可能并不总是准确的。filtered表示返回结果的行数占需读取行数的百分比 filtered 的值越大越好。extra 额外信息。这里可以显示一些额外的执行计划信息如是否使用了临时表、是否需要进行文件排序等。 using index condition : 查找使用了索引,但需要回表查询 using where;using index: 查找使用了索引在索引中能查到所需的数据无需回表查询
四、SQL优化
为什么不要使用select *
因为 * 会包含没有创建索引的字段此时通过索引不能直接查到信息的字段需要通过id做回表查询也就是说尽量使用覆盖索引查询的字段写明并且这些字段都可以通过走一边索引即可查询到所需的数据无需回表查询索引有聚集索引和二级索引两类聚集索引包含整行数据二级索引也就是我们对字段创建的索引只包含该索引字段的数据和id。 以根据用户名查询用户的id例当已经为username创建单列索引时
select * from tb_user where username 张三 ;
该查询会走username的索引能走索引查出username 和 id但是select * 还需要查询其它信息索引还要根据id在聚集索引中查询其它信息即回表查询。这样走了两次索引效率就有所降低。 而使用以下SQL不用走回表查询时效率更高
select id from tb_user where username 张三 ; 再以用户名查询用户id、用户名、用户密码为例
select id,username,password from tb_user where username 张三 ;
当我们要查询效率更高时可以为username和password建立联合索引时不需要走回表查询去查询其它信息查询效率更高。
插入优化 insert
批量插入防止频繁的执行SQL语句
insert into tb_user values
(1,张三),
(2,李四),
(3,王五); 手动提交事务防止事务频繁的开启和提交
start transaction;insert into tb_test values(1,Tom),(2,Cat),(3,Jerry);insert into tb_test values(4,Tom),(5,Cat),(6,Jerry);insert into tb_test values(7,Tom),(8,Cat),(9,Jerry);
commit; 主键顺序插入性能要高于乱序插入
比如按照主键id 1、2、3、4、5...的顺序插入
大批量数据插入
如果一次性需要插入大批量数据(比如: 几百万的记录)使用insert语句插入性能较低此时可以使 用MySQL数据库提供的 load 指令进行插入。操作如下
1、客户端连接服务端时加上参数 -–local-infile mysql –-local-infile -u root -p 2、设置全局参数local_infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;
3、执行load指令将准备好的数据加载到表结构中
load data local infile /root/load_user.sql into table tb_user fields terminated by , lines terminated by \n ; 其中 /root/load_user.sql 是大批量数据文件的路径
插入到 tb_user 表中字段之间是 , 分割每行之间是 \n 分割
这样插入性能高于insert若主键是顺序插入性能高于乱序插入
主键优化
在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表
逻辑存储结构是表空间--段--区--页--行 页分裂
因为BTree索引中数据都是存储在叶子节点并且数据是按照索引字段主键顺序存放当乱序插入时会出现以下情况 页合并
当相邻两页之间的数据只有50%时InnoDB会判断是否可以将两页进行合并若可以则会将该相邻两页的数据进行合并50%可以在创建表或者创建索引时指定参数MERGE_THRESHOLD设置默认是50%。 满足业务需求的情况下尽量降低主键的长度减小索引体积插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键如身份证号。 业务操作时避免对主键的修改。因为这些都是乱序的主键会出现页分裂的情况
排序优化 order by MySQL的排序有两种方式 Using filesort : 通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。 Using index : 通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要 额外排序操作效率高。 Using filesort 通常比使用索引排序慢得多因为它需要在磁盘上创建临时文件并进行排序操作。这会增加 I/O 操作从而影响查询性能。在大数据量的情况下Using filesort 可能会显著降低查询效率。Using index的性能高而Using filesort的性能低我们在优化排序 操作时尽量要优化为 Using index 当根据年龄(age)字段排序查询时可以看出extra额外信息显示的是Using filesort 当我们对price字段创建了索引进行排序时正常走索引效率高当使用select * 即需要回表查询做排序操作时还是会另开辟空间做Using filesort 查询 当根据age倒叙排列而BTree中索引中数据是根据索引字段正序排列反向扫描索引Backward index scan; 对多字段进行排序
先对价格(price)进行排序若价格相同则根据评分排序
explain select id, price, score from tb_hotel order by price, score; 针对多字段进行排序的情况需要对这多字段创建联合索引才能让排序走索引 这时需要注意两字段要都是升序或都是降序才能走索引并且必须是先根据price排序再根据score排序才会走索引,因为这是创建的联合索引若是先根据score排序则违背了最左前缀法则不会走索引Collection列是A表示索引中的数据根据升序排列 若一个升序一个降序即price升序score降序则根据price排序时会走索引而price相同时根据score降序排序则不会走索引这时若想走索引则需要再创建一个索引指定索引中的字段数据price升序score降序
创建索引时指定规则ASC/DESC默认是升序ASC。 create index idx_price_score_AD on tb_hotel(price asc,score desc); 若不得不走缓存区使用Using filesort 进行排序在大数据量的情况下可以增加缓冲区的大小
查看缓冲区大小
show variables like sort_buffer_size; order by优化总结 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。尽量使用覆盖索引。多字段排序, 一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC。如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。 分组优化group by
需要对分组优化同样需要对需要分组的字段添加索引走索引进行分组查询效率更高需要注意的是也需要满足最左前缀法则尽量使用联合索引
没有索引走临时表type为ALL走全表扫描效率低 建立索引后 联合索引时不满足最左前缀法则 有where条件时是满足最左前缀法则的走索引 limit优化
在数据量比较大时如果进行limit分页查询在查询时越往后分页查询效率越低。
优化方式使用覆盖索引加子查询形式进行优化。 explain select * from tb_sku t,
(select id from tb_sku order by id limit 2000000,10) a #将该页数据的id查询然后对做多表查询
where t.id a.id; Mysql不支持 in 后加 limit 关键字 count优化
用法count*、count主键、count字段、count数字
count(主 键) InnoDB 引擎会遍历整张表把每一行的 主键id值都取出来返回给服务层。 服务层拿到主键后直接按行进行累加(主键不可能为null)count(字 段) 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加。有not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。count(1) InnoDB 引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字“1” 进去直接按行进行累加。可以是其它数字count(*) InnoDB引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接 按行进行累加。 综上按效率排列count(字段) count(主键 id) count(1) ≈ count(*)所以尽 量使用 count(*)。 update优化
在执行update更新操作时要根据有索引的字段进行更新
将城市为成都的都改为四川若city字段没有创建索引则InnoDB会对整个表tb_hotel都加锁即表级锁若事务还未提交此时就不能执行该表的其它更新操作
若创建了索引则会对该行数据添加锁即行级锁。
update tb_hotel set city 四川 where city 成都;
在执行上述的SQL时 当开启多个事务发现行锁升级为了表锁。 导致该update语句的性能大大降低。会影响效率。 所以在执行update更新操作时尽量使用有索引的字段做为where条件
InnoDB的行锁是针对索引加的锁不是针对记录加的锁 ,并且该索引不能失效否则会从行锁 升级为表锁 。 \G SQL最后加以键值对一列形式返回
