目录1.建表2.添加字段3.修改字段类型4.添加索引5.删除索引6.组合索引的使用7.遇到的问题8.同步数据有emoji表情1.建表CREATETABLEtest.table_test(idbigint(20)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT主键id,daydateDEFAULTNULLCOMMENT日期,show_cntbigint(20)DEFAULT0COMMENT曝光次数,play_timedouble(20,2)DEFAULT0.00COMMENT播放时长,start_typevarchar(100)DEFAULTCOMMENT启动方式,PRIMARYKEY(id),KEYidx_start_type(day,start_type))ENGINEInnoDBCHARSETutf8 ROW_FORMATDYNAMICCOMMENT该表用于测试天增量;注意1、表有主键PRIMARY KEY2、有索引idx_start_type3、各字段有字段类型2.添加字段ALTERTABLEuserADDCOLUMNageINTDEFAULT0COMMENT年龄,ADDCOLUMNsexVARCHAR(10)DEFAULTCOMMENT性别;ALTERTABLEuserADD(ageINTDEFAULT0COMMENT年龄,,sexVARCHAR(10)DEFAULTCOMMENT性别);3.修改字段类型ALTERTABLEmytableMODIFYCOLUMNmycolumnINT;4.添加索引ALTERTABLEtable_nameADDINDEXindex_name(column1,column2,column3)ALTERTABLEpaymentADDINDEXidx_customer_id_staff_id(customer_id,staff_id);ALTERTABLEtable_nameADDINDEXidx1(aaa),ADDINDEXidx2(bbb,ccc),ADDINDEXidx3(ddd);5.删除索引ALTERTABLEpaymentDROPINDEXidx_customer_id;ALTERTABLEpaymentDROPINDEXidx_staff_id;6.组合索引的使用1.添加索引ALTERTABLEpaymentADDINDEXidx_customer_id_staff_id(customer_id,staff_id);2.查看执行计划mysqlexplainselectcount(*)frompaymentwherestaff_id2205ANDcustomer_id93112;--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|1|SIMPLE|payment|index_merge|idx_customer_id,idx_staff_id|idx_staff_id,idx_customer_id|4,4|NULL|11711|Usingintersect(idx_staff_id,idx_customer_id);Usingwhere;Usingindex|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------3.对比发现组合索引比两个单个索引在这种场合查询速度要更快4. 怎么选择建立组合索引时列的顺序多列索引的列顺序至关重要如何选择索引的列顺序有一个经验法则将选择性最高的列放到索引最前列但是不是绝对的。经验法则考虑全局的基数和选择性而不是某个具体的查询5.组合索引的使用规则生效的规则是从前往后依次使用生效如果中间某个索引没有使用那么断点前面的索引部分起作用断点后面的索引没有起作用最左匹配原则在通过联合索引检索数据时从索引中最左边的列开始一直向右匹配如果遇到范围查询(、、between、like等)就停止后边的匹配比如创建了多列索引 index_alla,b,c示例(0)select*fromtest_tablewherea3andb5andc4;abc三个索引都在where条件里面用到了而且都发挥了作用(1)select*fromtest_tablewherec4andb6anda3;这条语句列出来只想说明 mysql没有那么笨where里面的条件顺序在查询之前会被mysql自动优化效果跟上一句一样(2)select*fromtest_tablewherea3andc7;a用到索引b没有用所以c是没有用到索引效果的(3)select*fromtest_tablewherea3andb7andc3;a用到了b也用到了c没有用到这个地方b是范围值也算断点只不过自身用到了索引(4)select*fromtest_tablewhereb3andc4;因为a索引没有使用所以这里 bc都没有用上索引效果(5)select*fromtest_tablewherea4andb7andc9;a用到了 b没有使用c没有使用(6)select*fromtest_tablewherea3orderbyb;a用到了索引b在结果排序中也用到了索引的效果前面说了a下面任意一段的b是排好序的(7)select*fromtest_tablewherea3orderbyc;a用到了索引但是这个地方c没有发挥排序效果因为中间断点了使用explain可以看到 filesort(8)select*fromtest_tablewhereb3orderbya;b没有用到索引排序中a也没有发挥索引效果(9)select*fromtest_tablewherea1xxx;使用函数、运算表达式及类型隐式转换等,完全用不到索引6.索引覆盖索引覆盖建立了联合索引后直接在索引中就可以得到查询结果从而不需要回表查询聚簇索引中的行数据信息(0)select*fromtest_tablewherea3andb5andc4;---这个就是索引覆盖(2)select*fromtest_tablewherea3andc7;---这个就不是索引覆盖7.遇到的问题问题1在创建要给表的时候遇到一个有意思的问题提示Specified key was too long; max key length is 767 bytes从描述上来看是Key太长超过了指定的 767字节限制。通常出现在尝试创建一个过长的唯一键UNIQUE KEY或主键PRIMARY KEY时。MySQL对于InnoDB存储引擎有一个索引键长度的限制这个限制基于字符集的不同而不同。下面是建表时的语句CREATETABLEtest_table(idint(11)unsignedNOTNULLAUTO_INCREMENT,namevarchar(1000)NOTNULLDEFAULT,linkvarchar(1000)NOTNULLDEFAULT,PRIMARYKEY(id),KEYname(name))ENGINEInnoDBAUTO_INCREMENT1DEFAULTCHARSETutf8mb4;分析原因在使用utf8字符集时每个字符可能占用3个字节那么对于innodb表索引键的最大长度大约为1000个字符左右因为3072 / 3 ≈ 1024。若字符集是utf8mb4每个字符可能占用4个字节所以最大长度会进一步减少到768个字符左右3072 / 4 768解决方法修改索引中字段的长度比如你的索引字段是字符串类型是varchar(512),修改到varchar(225)或者更低比如varchar(100),注意UTF-8编码一个英文字符等于一个字节一个中文含繁体等于三个字节。问题2实际开发中遇到索引失效的情况原mysql表已建立索引 index_allday,gender,age,但是bi看板展示时day有范围为了看趋势图导致mysql索引并未生效selectspent,gender,age,dayfrommytestwhereday2023-01-01andday2023-01-07andgender男andage18;处理方式新增索引index_all1 (gender,age,day)8.同步数据有emoji表情处理方法1.过滤掉表情字符regexp_replace(field,[\\uD83C\\uDF00-\\uD83D\\uDDFF],)asfield2、使用mysql存储emoji表情。正常的字段类型是char字符编码是utf8存储的字节数为3但是emoji表情的字节数为4所以需要修改字符编码为utf8mb4。修改表字段结构为utf8mb4ALTERTABLEtable_namemodifycolumn_nameVARCHAR(200)CHARACTERSETutf8mb4DEFAULTCOMMENTcomment;ALTERTABLEdatabase_name.table_nameMODIFYCOLUMNcolumb_nameVARCHAR(100)CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ciDEFAULTCOMMENTcomment