第十章 1 索引的设计和使用，创建和删除索引列；索引创建的原则：

淩亂°似流年 2021-11-04 19:14 358阅读 0赞

索引的设计和使用：

所有的mysql类型都可以被索引;

Myisam  和 innodb 默认的都是btree索引；

Mysql支持前缀索引，即对索引的前N个字符创建索引；

Myisam支持的前缀索引长度为1000字节；innodb支持的前缀索引长度为767字节；

**注意：**在create table 创建索引时，长度对应的是字符数； 限制的长度为字节数，所以要不同字符集对应的字符转换为字节后不能超过 索引的字节限制；

Mysql只有myisam 支持全文索引fulltext；并且只限于char varchar text 列；

操作实例：

**创建表：**

CREATE TABLE index\_learn (

\`learnId\` bigint(20) unsigned NOT NULL COMMENT '知识ID',

\`knwledgeid\` bigint(20) unsigned DEFAULT NULL COMMENT '预采编知识ID',

\`KNWLG\_NM\` varchar(255) NOT NULL COMMENT '知识名称',

\`KNWLG\_ALS\` varchar(255) DEFAULT NULL COMMENT '知识别名',

\`KYWDS\` varchar(255) DEFAULT NULL COMMENT '关键词',

PRIMARY KEY (\`learnId\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ceshi'

**不指定任何索引：然后** **explain** **查询语句：没有使用到索引；**

EXPLAIN SELECT \* from index\_learn where knwlg\_nm ='xuexi';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70][]

**创建索引：create index knwlg\_nm\_index on index\_learn (knwlg\_nm(20));**

**再进行查询：EXPLAIN SELECT \* from index\_learn where knwlg\_nm ='xuexi';**

**可以看到 使用到了索引；**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 1][]

**此时再用 ：show create table index\_learn;**

**可以看到 key knwlg\_nm\_index (knwlg\_nm(20));**

CREATE TABLE \`index\_learn\` (

\`learnId\` bigint(20) unsigned NOT NULL COMMENT '知识ID',

\`knwledgeid\` bigint(20) unsigned DEFAULT NULL COMMENT '预采编知识ID',

\`KNWLG\_NM\` varchar(255) NOT NULL COMMENT '知识名称',

\`KNWLG\_ALS\` varchar(255) DEFAULT NULL COMMENT '知识别名',

\`KYWDS\` varchar(255) DEFAULT NULL COMMENT '关键词',

PRIMARY KEY (\`learnId\`),

KEY \`knwlg\_nm\_index\` (\`KNWLG\_NM\`(20))

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ceshi'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 2][]

删除索引列:

drop index knwlg\_nm\_index on index\_learn;

执行删除索引之后，可以通过 show create table 表名；查看效果，也可以 通过explain sql语句；查看执行解析；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 3][]

设计索引的原则：

1.  适合建索引的字段为经常出现在where后的条件字段，或者 表连接子句使用的字段；
2.  使用唯一索引，考虑某一列中值的分布，索引的列的基数越大（索引的不同条数），索引的效果越好；例如：如果一列只有男，女；那么不适合建索引，因为即使创建了索引，每次搜索也会去匹配一半的数据，又影响写入时 需要同步更新索引，所以这种情不建议创建索引；
3.  字符串类型进行索引，应该制定一个前缀长度；这个长度取值 应该为 在这个长度内，大部分的列的值是惟一的；例如：数据为：我是好人啊；我是坏人啊；我是哈哈哈；我是大神；我是菜鸡；这种情况 如果前缀设置为2 ；则所有索引都一样，无意义；就应该建议前缀长度设置为3；【并且这种数据情况，前缀索引创建3 会比不指定长度 节省更多的索引空间】
4.  不要过度创建索引，一个字段如果很少使用就不建议创建索引，因为创建索引，在对数据操作是也有同步更新索引；过多的索引会影响效率；
5.  Innodb引擎，会默认按照一定的顺序索引保存，如果有主键，则按照主键顺序保存；如果没有主键，有唯一索引，则按照唯一索引的顺序保存，如果没有主键，也没有唯一索引，则会自己生成一个内部列；按照此列的顺序进行保存； 按照主键或者内部列进行访问是速度最快的；所以innodb尽量都指定主键；

**Btree和hash索引：**

Hash索引 只支持 =  和<=>  ;优化器不能使用hash索引来加速order by 操作；

只能使用整个关键字来搜索一行；【hash索引在使用区间查询时，其实是全表扫描的】

Btree 可以使用 = ，>,<, <=,>= ,between,!=或<>或者 like  可以使用上相关索引；

小结：如果不适用索引，mysql必须从第一条 记录开始读完整个表直到找到相关的行。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70]: /images/20211104/73210d8a7810476ab5de7558ee53bd9c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 1]: /images/20211104/863e6b20e6bb48ad94c2647e4fc09c89.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 2]: /images/20211104/a9476befa85d45aa9bab668c51f9c658.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jvc3Nfd2F5_size_16_color_FFFFFF_t_70 3]: /images/20211104/7281515b95d14c65937b17d5e3ac6876.png