蓬莱做网站价格,wordpress 插件 无法创建目录,手机网站设计技巧,网站图片上的分享怎么做mysql索引基本知识梳理
索引介绍
官方介绍索引是帮助MySQL高效获取数据的数据结构, 原理为以空间换时间, mysql的索引采用的是B树的结构
索引的优缺点
优点#xff1a;
提高查询效率降低数据库IO成本通过索引对数据进行排序, 降低排序成本, 降低CPU消耗
缺点#xff1a…mysql索引基本知识梳理
索引介绍
官方介绍索引是帮助MySQL高效获取数据的数据结构, 原理为以空间换时间, mysql的索引采用的是B树的结构
索引的优缺点
优点
提高查询效率降低数据库IO成本通过索引对数据进行排序, 降低排序成本, 降低CPU消耗
缺点
索引会占用磁盘空间降低更新表的效率 对数据进行增删改操作时, 除了保存数据外, 还要更新索引文件, 且耗时随着数据量增加而增加
索引类型 主键索引 primary key 索引列中的值必须是唯一的, 不允许有空值。 唯一索引 unique 索引列中的值必须是唯一的, 但是允许为空值。 普通索引 normal MySQL中基本索引类型, 没有什么限制, 允许在定义索引的列中插入重复值和空值。 全文索引 Full Text 只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时, 如果创建普通索引, 在进行like模糊查询时效率比较低, 这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。 空间索引 spatial MySQL在5.7之后的版本支持了空间索引, 而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。 前缀索引 在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时, 可以指定索引列的长度, 但是数值类型不能指定。 其他按照索引列数量分类 单列索引组合索引 组合索引的使用, 需要遵循最左前缀匹配原则最左匹配原则。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。
索引的创建和使用
假设以用户基本信息表为例, sql如下:
-- 创建表时, 创建索引
CREATE TABLE user (id int NOT NULL COMMENT id,name varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 姓名,age int NOT NULL COMMENT 年龄,sex tinyint(1) NOT NULL COMMENT 性别,phone varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 电话,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin;
查看表索引
SHOW INDEX FROM 【tableName】;-- 就例子中的user表, 具体sql如下
-- SHOW INDEX FROM user;索引结果如下
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpressionuser0 P R I M A R Y \color{#f56c6c}{PRIMARY} PRIMARY1idA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null) Non_unique 是否非唯一, 这里说明主键也是唯一键Key_name 主键索引名称默认为PRIMARYSeq_in_index 索引序号递增Column_name 为索引所在列明Index_type 索引数据类型为B树说明是B树 单列索引 创建普通索引 CREATE INDEX 【indexName】 ON 【tableName】(【columnName】);-- 就例子中的user表, 对name字段添加索引, 具体sql如下
-- CREATE INDEX idx_name ON user(name);TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpressionuser0PRIMARY1idA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null)user1 i d x _ n a m e \color{#f56c6c}{idx\_name} idx_name1nameA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null) Non_unique 是否非唯一, 这里说明普通索引不是唯一 创建唯一索引 CREATE UNIQUE INDEX 【indexName】 ON 【tableName】(【columnName】);-- 就例子中的user表, 对phone字段添加唯一索引, 具体sql如下
-- CREATE UNIQUE INDEX idx_phone ON user(phone);TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpressionuser0PRIMARY1idA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null)user0 i d x _ p h o n e \color{#f56c6c}{idx\_phone} idx_phone1phoneA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null) Non_unique 是否非唯一, 唯一索引肯定是唯一
联合索引
创建索引
CREATE UNIQUE INDEX 【indexName】 ON 【tableName】(【columnName1】,【columnName2】,...,【columnNameN】);-- 就例子中的user表, 对name和age和sex字段添加联合索引, 具体sql如下
-- CREATE INDEX idx_name_age_sex ON user(name, age, sex);TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpressionuser0PRIMARY1idA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null)user1 i d x _ n a m e _ a g e _ s e x \color{#f56c6c}{idx\_name\_age\_sex} idx_name_age_sex 1 \color{#f56c6c}{1} 1nameA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null)user1 i d x _ n a m e _ a g e _ s e x \color{#f56c6c}{idx\_name\_age\_sex} idx_name_age_sex 2 \color{#f56c6c}{2} 2ageA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null)user1 i d x _ n a m e _ a g e _ s e x \color{#f56c6c}{idx\_name\_age\_sex} idx_name_age_sex 3 \color{#f56c6c}{3} 3sexA0 ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)BTREEYES ( N u l l ) \color{#909399}{(Null)} (Null) 由结果可看出, 联合索引是多行数据存在数据库中, 且Seq_in_index是按顺序递增的 联合索引的意义 减少开销 建一个联合索引(col1,col2,col3)实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引都会增加写操作的开销和磁盘空间的开销。对于大量数据的表使用联合索引会大大的减少开销 覆盖索引 对联合索引(col1,col2,col3)如果有如下的sql: select col1,col2,col3 from tableName where col11 and col22。那么MySQL可以直接通过便利索引取得数据无需回表较少io操作。实际应用中覆盖索引是主要的提升性能的优化手段之一。 效率高 索引列越多通过索引筛选出来的数据越少。 假设有1000w数据select col1,col2,col3 from tableName where col11 and col22 and col33, 假设每个条件可筛选出10%的数据如果使用单列索引假设索引列为col1则筛选出来的数据为 1000w * 10 % 100w 条数据然后再回表从100w条数据中找到符合剩下条件的数据再进行排序和分页等操作。但如果是联合索引通过索引直接能筛选出 1000w * 10% * 10% * 10% 1w 条数据性能有明显提升。
最左前缀匹配
在谈最左前缀匹配前, 可先了解mysql中explain的用法。
下列实例中, 表结构为
CREATE TABLE user (id int NOT NULL COMMENT id,name varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 姓名,age int NOT NULL COMMENT 年龄,sex tinyint(1) NOT NULL COMMENT 性别,phone varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 电话,PRIMARY KEY (id),KEY idx_name_age_sex (name,age,sex)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin;先往user表中插入若干数据 全值匹配 符合最左前缀匹配原则 select * from user where name ? and age ? and sex ?
select * from user where name ? and sex ? and age ?
select * from user where age ? and name ? and sex ?
select * from user where age ? and sex ? and name ?idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)refidx_name_age_sexidx_name_age_sex1027const,const,const1100.00 ( N u l l ) \color{#909399}{(Null)} (Null)sql语句中字段的顺序不需要和联合索引定义的字段顺序相同, 查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高, 最后才能生成真正的执行计划 匹配最左边的列 符合最左前缀匹配原则 select * from user where name ? idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)refidx_name_age_sexidx_name_age_sex1022const1100.00 ( N u l l ) \color{#909399}{(Null)} (Null) 只是name命中索引时, key_len为1022 select * from user where name ? and age ?idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)refidx_name_age_sexidx_name_age_sex1026const1100.00 ( N u l l ) \color{#909399}{(Null)} (Null) name和age命中索引时, key_len为1026, 比仅name命中时, key_len大4, 是因为int的长度是4个字节 select * from user where name ? and sex ?idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)refidx_name_age_sexidx_name_age_sex1022const1100.00Using index condition 匹配列前缀 对于字符串类型, 模糊匹配中的前缀匹配, 用的是索引, 中缀和后缀走的全表扫描 前缀匹配 – 走索引 explain select * from user where name like user1%;idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)refidx_name_age_sexidx_name_age_sex1022const11100.00Using index condition 中缀匹配 – 不走索引 explain select * from user where name like %user1%;idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)ALL ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)9911.11Using where 后缀匹配 – 不走索引 explain select * from user where name like %user1;idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)ALL ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null) ( N u l l ) \color{#909399}{(Null)} (Null)9911.11Using where 匹配范围值 explain select * from user where id 1;idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)rangePRIMARYPRIMARY4 ( N u l l ) \color{#909399}{(Null)} (Null)98100.00Using where 最左前缀匹配原则遇到, 就会停止匹配, 但是由于id本身是有序的, 由上述结果可知, 该查询使用了id索引, 且为range范围查询 explain select * from user where id 1 and age 10idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuser ( N u l l ) \color{#909399}{(Null)} (Null)rangePRIMARYPRIMARY4 ( N u l l ) \color{#909399}{(Null)} (Null)9833.33Using where 由于不遵循最左匹配原则使用的是range扫描。 文章转载自： http://www.morning.fbmjl.cn.gov.cn.fbmjl.cn http://www.morning.lxhrq.cn.gov.cn.lxhrq.cn http://www.morning.zzgtdz.cn.gov.cn.zzgtdz.cn http://www.morning.znqxt.cn.gov.cn.znqxt.cn http://www.morning.mhlsx.cn.gov.cn.mhlsx.cn http://www.morning.rfgc.cn.gov.cn.rfgc.cn http://www.morning.tkqzr.cn.gov.cn.tkqzr.cn http://www.morning.hrdx.cn.gov.cn.hrdx.cn http://www.morning.bktly.cn.gov.cn.bktly.cn http://www.morning.cctgww.cn.gov.cn.cctgww.cn http://www.morning.xglgm.cn.gov.cn.xglgm.cn http://www.morning.china-cj.com.gov.cn.china-cj.com http://www.morning.mhnb.cn.gov.cn.mhnb.cn http://www.morning.hncrc.cn.gov.cn.hncrc.cn http://www.morning.ktyww.cn.gov.cn.ktyww.cn http://www.morning.hhqjf.cn.gov.cn.hhqjf.cn http://www.morning.slpcl.cn.gov.cn.slpcl.cn http://www.morning.xkhhy.cn.gov.cn.xkhhy.cn http://www.morning.rbnnq.cn.gov.cn.rbnnq.cn http://www.morning.fhqsm.cn.gov.cn.fhqsm.cn http://www.morning.qyqmj.cn.gov.cn.qyqmj.cn http://www.morning.dqpd.cn.gov.cn.dqpd.cn http://www.morning.cwgfq.cn.gov.cn.cwgfq.cn http://www.morning.jpjpb.cn.gov.cn.jpjpb.cn http://www.morning.lgznc.cn.gov.cn.lgznc.cn http://www.morning.vaqmq.cn.gov.cn.vaqmq.cn http://www.morning.nyqxy.cn.gov.cn.nyqxy.cn http://www.morning.qsxxl.cn.gov.cn.qsxxl.cn http://www.morning.gjmll.cn.gov.cn.gjmll.cn http://www.morning.plwfx.cn.gov.cn.plwfx.cn http://www.morning.skscy.cn.gov.cn.skscy.cn http://www.morning.rqrxh.cn.gov.cn.rqrxh.cn http://www.morning.mkczm.cn.gov.cn.mkczm.cn http://www.morning.hhmfp.cn.gov.cn.hhmfp.cn http://www.morning.zmlbq.cn.gov.cn.zmlbq.cn http://www.morning.lpcpb.cn.gov.cn.lpcpb.cn http://www.morning.lwcqh.cn.gov.cn.lwcqh.cn http://www.morning.mfnsn.cn.gov.cn.mfnsn.cn http://www.morning.prprj.cn.gov.cn.prprj.cn http://www.morning.qjxxc.cn.gov.cn.qjxxc.cn http://www.morning.fkdts.cn.gov.cn.fkdts.cn http://www.morning.rwlsr.cn.gov.cn.rwlsr.cn http://www.morning.ysnbq.cn.gov.cn.ysnbq.cn http://www.morning.pkmcr.cn.gov.cn.pkmcr.cn http://www.morning.pzbqm.cn.gov.cn.pzbqm.cn http://www.morning.dcpbk.cn.gov.cn.dcpbk.cn http://www.morning.znqxt.cn.gov.cn.znqxt.cn http://www.morning.nnwpz.cn.gov.cn.nnwpz.cn http://www.morning.khntd.cn.gov.cn.khntd.cn http://www.morning.qhrdx.cn.gov.cn.qhrdx.cn http://www.morning.hhqtq.cn.gov.cn.hhqtq.cn http://www.morning.nlkjq.cn.gov.cn.nlkjq.cn http://www.morning.sjgsh.cn.gov.cn.sjgsh.cn http://www.morning.wtnyg.cn.gov.cn.wtnyg.cn http://www.morning.rzczl.cn.gov.cn.rzczl.cn http://www.morning.mnwmj.cn.gov.cn.mnwmj.cn http://www.morning.tnjz.cn.gov.cn.tnjz.cn http://www.morning.gfkb.cn.gov.cn.gfkb.cn http://www.morning.ltpmy.cn.gov.cn.ltpmy.cn http://www.morning.qlckc.cn.gov.cn.qlckc.cn http://www.morning.glcgy.cn.gov.cn.glcgy.cn http://www.morning.wmqrn.cn.gov.cn.wmqrn.cn http://www.morning.ktskc.cn.gov.cn.ktskc.cn http://www.morning.rgsnk.cn.gov.cn.rgsnk.cn http://www.morning.tknqr.cn.gov.cn.tknqr.cn http://www.morning.gmswp.cn.gov.cn.gmswp.cn http://www.morning.wrdpj.cn.gov.cn.wrdpj.cn http://www.morning.lqgtx.cn.gov.cn.lqgtx.cn http://www.morning.fhtbk.cn.gov.cn.fhtbk.cn http://www.morning.fmznd.cn.gov.cn.fmznd.cn http://www.morning.mtsck.cn.gov.cn.mtsck.cn http://www.morning.rpljf.cn.gov.cn.rpljf.cn http://www.morning.ddtdy.cn.gov.cn.ddtdy.cn http://www.morning.znqmh.cn.gov.cn.znqmh.cn http://www.morning.zynjt.cn.gov.cn.zynjt.cn http://www.morning.rrcxs.cn.gov.cn.rrcxs.cn http://www.morning.c7495.cn.gov.cn.c7495.cn http://www.morning.xnpj.cn.gov.cn.xnpj.cn http://www.morning.whclz.cn.gov.cn.whclz.cn http://www.morning.nytpt.cn.gov.cn.nytpt.cn
