石家庄建设网站的公司,可以做装修效果图的网站有哪些,管理咨询包括哪些内容,wordpress中文是什么意思文章目录 1 联合索引1.1 最左前缀法则1.2 范围查询 2 索引失效2.1 常见情况2.2 数据分布影响 3 SQL提示4 覆盖索引和回表查询5 前缀索引6 单列和联合索引7 设计原则结语 1 联合索引
1.1 最左前缀法则 在Mysql数据库中创建了联合索引#xff08;或称复合索引#xff0c;即包含… 文章目录 1 联合索引1.1 最左前缀法则1.2 范围查询 2 索引失效2.1 常见情况2.2 数据分布影响 3 SQL提示4 覆盖索引和回表查询5 前缀索引6 单列和联合索引7 设计原则结语 1 联合索引
1.1 最左前缀法则 在Mysql数据库中创建了联合索引或称复合索引即包含多个列的索引要遵循最左前缀法则。最左前缀法则指的是查询从索引最左列开始不跳过中间列。 如果跳过某一中间列索引将部分失效后面的字段索引失效。
示例以之前的tb_user1表为例,数据如下表所示
idnamephoneemailprofessionagegenderstatuscreatetime1吕布17799990000lvbu666163.com软件工程23162001-02-02 00:00:002曹操17799990001caocao666qq.com通讯工程33102001-03-05 00:00:003赵云1779999000217799990139.com英语34122002-03-02 00:00:004孙悟空1779999000317799990sina.com工程造价54102001-07-02 00:00:005花木兰1779999000419980729sina.com软件工程23212001-04-22 00:00:006大乔17799990005daqiao666sina.com舞蹈22202001-02-07 00:00:007露娜17799990006luna_lovesina.com应用数学24202001-02-08 00:00:008程咬金17799990007chengyaojin163.com化工38152001-05-23 00:00:009项羽17799990008xiaoyu666qq.com金属材料43102001-09-18 00:00:0010白起17799990009baiqi666sina.com机械工程及其自动化27122001-08-16 00:00:0011韩信17799990010hanxin520163.com无机非金属材料工程27102001-06-12 00:00:0012荆轲17799990011jingke123163.com会计29102001-05-11 00:00:0013兰陵王17799990012lanlinwang666126.com工程造价44112001-04-09 00:00:0014狂铁17799990013kuangtiesina.com应用数学43122001-04-10 00:00:0015貂蝉1779999001484958948374qq.com软件工程40232001-02-12 00:00:0016妲己177999900152783238293qq.com软件工程31202001-01-30 00:00:0017芈月17799990016xiaomin2001sina.com工业经济35202000-05-03 00:00:0018嬴政177999900178839434342qq.com化工38112001-08-08 00:00:0019狄仁杰17799990018jujiamlm8166163.com国际贸易30102007-03-12 00:00:0020安琪拉17799990019jdodm1h126.com城市规划51202001-08-15 00:00:0021典韦17799990020ycaunanjian163.com城市规划52122000-04-12 00:00:0022廉颇17799990021lianpo321126.com土木工程19132002-07-18 00:00:0023后羿17799990022altycj2000139.com城市园林20102002-03-10 00:00:0024姜子牙1779999002337483844qq.com工程造价29142003-05-26 00:00:00
tb_user1表中索引如下所示
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
tb_user1 0 PRIMARY 1 id A 24 BTREE YES
tb_user1 0 idx_tb_user1_phone 1 phone A 24 BTREE YES
tb_user1 1 idx_tb_user1_name 1 name A 24 BTREE YES
tb_user1 1 idx_tb_user1_pro_age_sta 1 profession A 16 YES BTREE YES
tb_user1 1 idx_tb_user1_pro_age_sta 2 age A 22 YES BTREE YES
tb_user1 1 idx_tb_user1_pro_age_sta 3 phone A 24 BTREE YES 其中有我们之前创建的联合索引idx_tb_user1_pro_age_sta,字段及顺序profession,age,status
测试开始 执行如下sql语句 explain select * from tb_user1 where profession软件工程 and age31 and status0;
-- 结果
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 54 const,const,const 1 100.00 字段过滤顺序profession,age,status,符合最左前缀法则其中key_len为54 执行如下sql语句 explain select * from tb_user1 where profession软件工程 and age31;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 49 const,const 1 100.00 字段过滤顺序profession,age 符合最左前缀法则其中key_len为49status字段长度为5 执行如下sql语句 explain select * from tb_user1 where profession软件工程;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 47 const 4 100.00 字段过滤顺序profession符合最左前缀法则其中key_len为47profession字段长度47age字段长度2 执行如下sql语句 explain select * from tb_user1 where age31 and status0;
-- 查询结果
1 SIMPLE tb_user1 ALL 24 4.17 Using where缺失索引最左侧列符合最左前缀法则索引失效使用全表扫描。 执行如下sql语句 explain select * from tb_user1 where profession软件工程 and status0;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 47 const 4 10.00 Using index condition缺失索引对应的age列age列对应索引及之后部分索引失效 执行如下sql语句 explain select * from tb_user1 where status0 and profession软件工程 and age31;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 54 const,const,const 1 100.00 最左前缀法则和sql中放的位置无关只是看是否按创建索引顺序有没有包含。
1.2 范围查询 联合索引中出现范围查询(,)范围查询右侧的列索引失效。 以上述表为例 执行sql语句 explain select * from tb_user1 where profession软件工程 and age30 and status0;
-- 查询结果
1 SIMPLE tb_user1 range idx_user1_profession_age_sta idx_user1_profession_age_sta 49 2 10.00 Using index conditionage列索引右侧status列索引失效 执行sql语句 explain select * from tb_user1 where profession软件工程 and age30 and status0;
-- 查询结果
1 SIMPLE tb_user1 range idx_user1_profession_age_sta idx_user1_profession_age_sta 54 2 10.00 Using index condition业务条件允许的情况下范围查询尽量使用或者
2 索引失效
2.1 常见情况 索引列运算不要在索引列上进行运算操作否则索引将失效 我们想查询手机号后两位为15的人员信息 explain select * from tb_user1 where SUBSTR(phone,10,2)15;
-- 查询结果
1 SIMPLE tb_user1 ALL 24 100.00 Using where字符串不加单引号字符串类型字段使用时不加引号单列索引将失效联合索引当前列及之后索引列失效。 explain select * from tb_user1 where phone 17799990002;
-- 查询结果
1 SIMPLE tb_user1 ALL idx_tb_user1_phone 24 10.00 Using where
explain select * from tb_user1 where phone 17799990002;
-- 查询结果
1 SIMPLE tb_user1 const idx_tb_user1_phone idx_tb_user1_phone 46 const 1 100.00
explain select * from tb_user1 where profession软件工程 and age31 and status0;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 49 const,const 1 10.00 Using index condition
-- 虽然使用了索引但是status 字段没加单引号该列索引失效模糊匹配如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。 explain select * from tb_user1 where profession like 软件%;
-- 查询结果
1 SIMPLE tb_user1 range idx_user1_profession_age_sta idx_user1_profession_age_sta 47 4 100.00 Using index condition
explain select * from tb_user1 where profession like %工程;
-- 查询结果
1 SIMPLE tb_user1 ALL 24 11.11 Using whereor链接的条件用or链接的条件如果任一条件没有索引那么所有索引都不会生效当所有条件列都有索引索引才会生效 explain select * from tb_user1 where age20 or id1;
-- 查询结果
1 SIMPLE tb_user1 ALL PRIMARY 24 13.75 Using where
explain select * from tb_user1 where id20 or phone1111;
-- 查询结果
1 SIMPLE tb_user1 index_merge PRIMARY,idx_tb_user1_phone PRIMARY,idx_tb_user1_phone 4,46 2 100.00 Using union(PRIMARY,idx_tb_user1_phone); Using whereage列没有单列索引也没在联合索引的最左侧。
2.2 数据分布影响 如果MySQL评估使用索引比全表慢则使用索引。 explain select * from tb_user1 where phone 17799990012;
-- 查询结果
1 SIMPLE tb_user1 range idx_tb_user1_phone idx_tb_user1_phone 46 12 100.00 Using index condition
explain select * from tb_user1 where phone 17799990011;
-- 查询结果
1 SIMPLE tb_user1 ALL idx_tb_user1_phone 24 54.17 Using where结果集记录大于等于一半时全表扫描小于一半时使用索引 数据分布对null和not null影响 -- 表中profession 字段值全部非null
explain select * from tb_user1 where profession is null;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 47 const 1 100.00 Using index condition
-- 把professon 字段全部置为null在此直线上述查询结果
1 SIMPLE tb_user1 ALL idx_user1_profession_age_sta 24 100.00 Using where
3 SQL提示
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些任务的提示来达到优化操作的目的。
现在我们在tb_user1表的profession字段上有单列索引和联合索引默认使用哪个索引呢
explain select * from tb_user1 where profession软件工程;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta,idx_user1_pro idx_user1_profession_age_sta 47 const 4 100.00 默认使用的联合索引 use index建议使用索引 explain select * from tb_user1 use index(idx_user1_pro) where profession软件工程;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_pro idx_user1_pro 47 const 4 100.00 ignore index忽略索引 explain select * from tb_user1 ignore index(idx_user1_pro) where profession软件工程;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 47 const 4 100.00 force index强制使用索引 explain select * from tb_user1 FORCE index(idx_user1_pro) where profession软件工程;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_pro idx_user1_pro 47 const 4 100.00 4 覆盖索引和回表查询 覆盖索引是一种优化技术可以提高查询性能特别是在查询涉及到大量数据列时。当一个查询可以直接使用索引来满足查询需求而无需访问实际的数据行时就称之为覆盖索引。 覆盖索引的优势在于它可以减少磁盘I/O和CPU开销因为数据库不需要访问实际的数据行而只需读取索引所包含的列数据即可完成查询。这对于大型数据表和复杂查询特别有用可以显著提高查询性能。 要创建一个覆盖索引需要确保索引包含了查询所需的所有列。通常只需要在SELECT子句中列出需要的列而不需要SELECT *选择所有列。然后创建一个多列索引包括查询所需的列。当查询时MySQL将使用该索引来直接满足查询需求。 需要注意的是覆盖索引在某些情况下可能不适用或效果有限。例如如果查询涉及大量的列或复杂的计算覆盖索引可能无法满足查询需求。此外过多的索引可能会增加写操作的开销因为每次修改数据时都需要更新索引。因此在创建索引时需要权衡索引的数量和覆盖的列以及对读写性能的影响。 MySQL的回表查询是在使用非覆盖索引的情况下当需要获取查询结果中的列不在索引中时MySQL需要通过回表操作来获取缺失的列数据。 当执行一个查询时MySQL会首先使用索引定位到满足查询条件的行但索引中只包含了部分列的数据。如果查询结果需要包含其他列MySQL就需要通过回表操作去主键索引或聚集索引中查找并获取这些列的数据。 回表查询的过程可以分为两个步骤 使用索引定位MySQL首先使用非覆盖索引定位到满足查询条件的行这一步的效率相对较高因为索引的数据量通常比实际数据行少。回表获取数据MySQL通过找到的索引中的主键或聚集索引的值再去主键索引或聚集索引中查找并获取查询结果所需的其他列数据。这一步需要额外的I/O操作因为需要读取实际的数据行。 示例如下
explain select id, profession, age, status from tb_user1 where profession软件工程 and age31 and status0;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 54 const,const,const 1 100.00 Using indexexplain select profession, email from tb_user1 where profession 软件工程;
-- 查询结果
1 SIMPLE tb_user1 ref idx_user1_profession_age_sta idx_user1_profession_age_sta 47 const 4 100.00 explain select profession from tb_user1 where status 0;
-- 查询结果
1 SIMPLE tb_user1 index idx_user1_profession_age_sta idx_user1_profession_age_sta 54 24 10.00 Using where; Using index5 前缀索引
概述
MySQL的前缀索引是一种索引技术它允许你为索引的列指定一个前缀长度而不是使用完整的列值作为索引。通过指定较短的前缀长度可以减小索引的大小提高查询性能和减少存储空间的需求。
前缀索引的主要优势在于减小了索引的大小因为只存储了列值的前缀而不是完整的列值。这样可以减少磁盘I/O和内存使用特别是在具有大量数据和较长列的表中对查询性能的提升较为显著。 前缀长度 查询条件必须符合索引的前缀。只有当查询条件也是使用相同前缀长度的列值进行匹配时MySQL才能使用前缀索引来加速查询。否则MySQL将无法有效地使用该索引。前缀长度过短可能导致索引选择性下降。选择性指不重复的索引值和数据表总记录的比值索引选择性越好查询效率越高。需要权衡前缀长度和查询性能。前缀长度越短索引的大小越小但查询性能可能会受到影响。需要根据具体的查询模式和数据分布来选择合适的前缀长度。 语法 create index xxx on table_name(column(长度));选择性查看
select count(distinct SUBSTRING(email,1,10))/count(*) from tb_user1;
-- 查询结果
1.0000select count(distinct SUBSTRING(email,1,5))/count(*) from tb_user1;
-- 查询结果
0.9583select count(distinct SUBSTRING(email,1,4))/count(*) from tb_user1;
-- 查询结果
0.9167如果要平衡选择性和性能可以截取5位
create index idx_email_5 on tb_user1(email(5));
-- 索引结果
tb_user1 1 idx_email_5 1 email A 23 5 YES BTREE YES
-- 查询示例
explain select * from tb_user1 where email like 17799%;
1 SIMPLE tb_user1 range idx_email_5 idx_email_5 23 2 100.00 Using where6 单列和联合索引
单列索引和联合索引是在MySQL中用于优化查询性能的两种索引类型。 单列索引Single-Column Index 单列索引是指只针对单个列创建的索引。它可以加速根据该列进行等值比较例如“或范围比较例如”“、”的查询。当查询涉及到单个列时单列索引是最常见和简单的索引类型。 联合索引Composite Index 联合索引是指基于多个列创建的索引也称为复合索引或多列索引。它可以加速涉及到联合索引中的多个列的查询条件的查询。当查询条件涉及到多个列时联合索引可以提供更好的查询性能。 示例 -- 两个单列索引情况
explain select id, phone, name from tb_user1 where name项羽 and phone17799990008;
-- 查询结果
1 SIMPLE tb_user1 const idx_tb_user1_phone,idx_tb_user1_name idx_tb_user1_phone 46 const 1 100.00 使用phone字段建立的索引并且回表查询 -- 同一字段上既有单列索引又联合索引时可以指定索引 见## 3 SQL提示联合索引的创建顺序非常重要。MySQL使用联合索引按照索引的列顺序进行排序和存储数据。因此如果查询条件只涉及到联合索引的前缀列那么MySQL可以有效地使用该索引。这就是最左前缀法则可以帮助优化查询性能。 需要注意的是使用过多的索引可能会增加写操作的开销因为每次修改数据时都需要更新索引。因此在创建索引时需要谨慎权衡索引的数量和覆盖的列以及对读写性能的影响。 综上所述单列索引和联合索引都是优化查询性能的重要工具。选择何种索引类型取决于查询的特点和需求。在设计和创建索引时需要考虑查询模式、数据分布和性能需求等因素以获得最佳的查询性能。
7 设计原则
在设计索引时可以考虑以下原则来提高查询性能和减少存储空间的需求
针对表数据量大且查询频繁的表建立索引选择合适的索引列选择那些经常用于查询条件、连接操作或排序/分组操作的列作为索引列。重点考虑经常被查询的列和高选择性的列以获得更好的索引效果。注意索引的长度和类型对于字符串列可以考虑只选择前缀作为索引以减小索引的大小。对于数值列使用适当的数据类型以减少存储空间和提高索引效率。尽量使用联合索引减少单列索引。查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提供查询效率避免过多索引过多的索引会增加存储空间和维护开销并可能降低写操作的性能。只创建那些真正需要的索引权衡索引的数量和覆盖的列。定期维护索引随着数据的增加和修改索引可能需要进行重建或优化。定期检查和维护索引的统计信息以保持索引的有效性和性能。注意查询的顺序对于复杂的查询语句考虑查询的顺序和使用子查询来利用索引。优化查询语句的结构和逻辑以充分利用索引的优势。如果列不能存储NULL值建表时使用NOT NULL 约束。
综上所述索引设计是一个权衡和优化的过程。需要根据具体的应用需求、数据分布和查询模式来选择合适的索引策略。理解数据和查询的特点并遵循上述原则可以提高查询性能和优化数据库的使用。
结语
如果小伙伴什么问题或者指教欢迎交流。 ❓QQ:806797785 参考链接:
[1]MySQL数据库视频[CP/OL].2020-04-16.p79-88.
