MySQL中级

Server version: 5.7.20 MySQL Community Server (GPL)

一、数据库基础知识

（一）范式设计：

1.第一范式：列不可分。

字段信息不能再次拆分。不满足第一范式的表需要对字段进行拆分来达到满足第一范式的要求。
左图不符合第一设计范式；右图通过拆分字段来解决：

2.第二范式：强依赖。

在第一范式的基础上加入新的限制条件，要求实体属性完全依赖于主关键字，一个表主键不能和某个字段对应的两行数据关联起来。出现这种情况需要建立一个中间表来解决这个问题。
该表不满足第二范式设计，一个主键id和两个产品id关联：

中间表：

3.第三范式：表字段无传递性。

满足第三范式的前提是先满足第二范式。第三设计范式要求表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键直接关系而不是间接关系。比如下面订单表设计不符合第三设计范式，因为订单表中的产品名称和表主键是间接关系，而跟产品id是直接关系，因为在产品表中通过产品id也能找到产品名称，这里产生了数据冗余。

产品表：

这里如果产品表中产品id和产品名称发生变化，那么订单表中的产品名称就会和其产生差异。所以这里需要把订单表中的产品名称列取消掉，让订单表中的订单主键id和产品id直接关联，通过产品id在产品表中仍然能够找到产品名称，这样就符合第三设计范式了。

（二）反范式设计：

完全符合范式化设计并不是完美无缺，因为在实际业务场景中会存在大量的表关联查询，而表设计都做成了范式化设计（甚至很高的范式），大量的表关联起来很影响查询的性能。
**反范式化设计就是违反范式化设计**：
1.为了性能和查询效率适当违反数据库范式化设计的要求；
2.为了查询性能，允许存在部分（少量）冗余数据。
换句话来说反范式化设计就是使用空间换取时间。

（三）范式化和反范式对比：

1.范式化的更新操作通常比反范式化要快，因为字段相对少；
2.当数据库较好的范式设计时，就只有很少或没有重复数据，所以只需要修改更少的数据；
3.范式化设计的表通常更小，所以占据的内存较少；
4.范式化设计的缺点是通常需要关联，稍微复杂的查询语句在符合范式设计的表上都可能需要至少一次关联，也许更多；
5.复杂一些的查询语句可能使一些索引无效。例如，范式化设计可能将字段放在不同的表中，而这些列如果在同一张表中本可以共用同一个索引。

（四）字段数据类型优化：

1.优化基本原则：

①更小的通常更好：

使用可以正确存储数据的最小数据类型，通常更快，因为占用更少的磁盘、内存和CPU高速缓存，并且处理时需要的CPU周期也更少。比如有一个数据既可以用字符串存储也可以用整型存储，此时优先选择整型。因为字符串会涉及到字符集和校验规则。

②简单就好：

简单数据类型的操作通常需要更少的CPU周期。例如，由于字符串涉及到字符集和校验规则的原因导致使用字符串的代价更高。因此应该使用MySQL内建类型而不是字符串来存储日期和时间。

③尽量避免null：

建表时最好指定列为NOT NULL，除非真的需要存储null值。如果查询中包含可为NULL值的列，对MySQL来说更难优化，因为可为NULL值的列使得索引、索引统计和值比较都更加复杂。而且可为NULL值的列占用的存储空间也会更多，在MySQL里面需要特殊处理。可为NULL值的列被索引时，每个索引记录需要额外的一个字节来存储。
把可为NULL值的列改为NOT NULL带来的性能提升比较小，所以调优时没必要首先在现有的schema中查找并改掉这种情况，除非确定这会导致问题。但是如果计划在列上建索引，就应该尽量避免将该列设计成可为NULL值的列。

2.整数类型：

存储整数可以使用的几种数据类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别占用8/16/24/64位存储空间，也就是1/2//3/4/8个字节。
同时整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128-127。无符号和有符号类型使用相同的存储空间，并且性能相同，因此可以根据实际情况选择合适的的类型。另外integer和int存储及大小没有任何差别，只是为了业务上区分。

MySQL可以为整数类型指定宽度，例如INT(11),但是对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

备注：MySQL中没有long型，对应只有bigint型。当数据量很大时，既需要保证数据存储的精度、又需要保证数据存储的效率时只能选择整型的bigint型，因为实数类型的float、double保证不了精度，而decimal又保证不了效率，此时只能选择bigint型。

3.实数类型：

实数类型：FLOAT、DOUBLE、DECIMAL。
实数是带有小数部分的数字。MySQL既支持精确类型的存储DECIMAL类型，也支持不精确数据类型的存储比如FLOAT类型和DOUBLE（浮点类型）。DECIMAL类型用于存储精确的小数，本质上MySQL是以字符串形式存储这种数据类型。所以CPU不支持对DECIMAL的直接计算，只是在MySQL中自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点数计算，所以浮点运算明显更快。
浮点类型在存储同样范围的的值时，通常比DECIMAL使用更少的空间。FLOAT占用4个字节，DOUBLE占用8个字节，DECIMAL里面存储65个数字，DECIMAL对于列的空间消耗比较大，另外DOUBLE比FLOAT有更高的精度和更大的范围。

①如何选择数据类型？

对精度要求不高并且需要快速运算的时候，选择FLOAT和DOUBLE。应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储银行或金融数据。
在数据量比较大而且对精度要求比较高的情况下，可以考虑使用BIGINT代码DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

4.字符串类型：

MySQL支持多种字符串类型，包括：CHAR、VARCHAR、BLOB、TEXT、ENUM（枚举）、SET。
VARCHAR和CHAR是两种最主要的字符串数据类型。

①VARCHAR:

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。在内部实现上，既然是变长，VARCHAR需要使用1或2个额外字节来记录字符串长度，如果列的最大长度小于等于255字节，则使用一个字节表示，否则使用两个字节表示。
VARCHAR节省了存储空间，所以对性能有所帮助。但是由于行是变长的，在UPDATE时新值比旧值长时，使行变得比原来更长，此时需要做额外的工作。如果一个行占用的空间增长，并且在页内没有足够的空间可以存储，在这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页使行可以放进页内。

②CHAR:

CHAR是定长的，MySQL总会根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格，CHAR值会根据需要采用空格进行填充以方便比较。

③CHAR和VARCHAR如何选择？

字符串列的最大长度比平均长度大很多，列的更新很少；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR适合存储很短的字符串，或者所有值定长或都接近同一个长度。例如CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR要比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。
另外，使用VARCHAR(5)和VARCHAR(200)存储"hello"在磁盘上的开销是一样的，不过此时我们应该选择更短的列，因为它的内存消耗更少。

④BLOB和TEXT类型：

BLOB和TEXT都是为存储大数据而设计的字符串数据类型，分别采用二进制和字符方式存储，前者没有字符集和排序规则，而后者有，这是它们的区别。
与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象来处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会采用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部区域存储实际的值。
使用BLOB和TEXT要慎重：
(a)BLOB和TEXT会引起一些性能问题，所以应该尽量避免使用BLOB和TEXT类型；
(b)一定要用，建议把BLOB和TEXT分离到单独的表中；
(c)在不必要的时候避免检索大型的BLOB和TEXT值。例如，select * 查询就不是很好的选择，除非能够确定作为约束条件的WHERE子句后只会找到所需要的数据行。否则，很可能会导致在网络上传输大量无意义的值。建议搜索索引列，决定出需要的数据行，然后从符合条件的数据行中检索BLOB和TEXT值；
(d)可以使用合成的(Synthetic)索引来提高大文本字段(BLOB或TEXT)的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过三列值找到数据行了。但是，这种技术只能用于精确匹配的查询（散列值对于类似“<或“>=”等范围搜索操作符是没有用处的）。可以使用MD5函数生成散列值，也可以使用SHA1（或CRC32），或者使用自己的应用程序逻辑来计算散列值。

5.枚举类型：

如果表中的字段取值是固定的几个字符串，可以使用枚举列代替常用的字符串类型。枚举列会把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中，MySQL在内部会将每个值在列表中位置保存为整数，这样的话可以让表的大小大为缩小。

CREATE TABLE
enum_test(e ENUM(' fish', 'apple', 'dog') NOT NULL);

INSERT INTO
enum_test(e) VALUES(1),(2),(3);

但是要注意：
①因为枚举列实际存储为整数，而不是字符串，所以不要使用数字作为ENUM枚举常量，因为这种双重性很容易导致混乱，例如ENUM('1','2','3')。
②枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，所以尽量按照需要的顺序来定义枚举列。

6.日期和时间类型：

MySQL可以使用许多类型来保存时间和日期值，例如YEAR、DATE、DATETIME、TIMESTAMP。MySQL能够存储的最小时间单位为秒。
datetime存储日期范围：1001~9999；
timestamp存储日期范围：1970~2038,并且跟时区有关系。
如果需要存储比秒更小粒度的日期和时间值，由于MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

（五）命名规范：

1.推荐命名规范：

（1）可读性原则；

（2）必须使用小写字母或数字；

（3）不适用复数名词；

（4）禁用保留字；

（5）索引命名。

（六）索引：

1.官方定义：

使得MySQL高效获取数据的数据结构。其本质是数据结构，作用就是高效获取数据。

2.B+树索引：比较关键

3.全文索引：

4.哈希索引（内部）：较少使用

范围查找时效率较低：因为它适用于根据哈希值精确查找。
不支持排序：因为不同的哈希值分布在不同的哈希槽上面。
不支持组合索引：当存在哈希索引时，会将组合索引中涉及到的字段变成一个哈希值，没办法实现部分字段的组合。
存在哈希冲突：当数据量比较大时，哈希冲突的概率会增加。哈希冲突的概率越高，数据查询的效率越低。可以通过单链表的方式解决哈希冲突问题。

（七）Tree：

1.树：

树(Tree):N个节点构成的有限集合。
树中有一个称为“根(Root)”的特殊节点。同一级节点之间不能连接，否则不能称为树。
其余节点可分为M个互不相交的树，称为原来节点的子树。每个节点有两个分叉的称为二叉树。

2.二叉树：

基于树结构形成的只有两个分叉的树形数据结构。

3.二叉查找树：

（1）首先是一颗二叉树，即每个节点有且只有两个分叉。
（2）左子树节点上所有的值小于根节点上的值；
（3）右子树节点的所有的值大于或等于根节点上的值。

4.AVL平衡二叉树：

（1）前提是它得是二叉查找树。
（2）左右两个子树的高度差（平衡因子）的绝对值不能超过1；
（3）左右两个子树都是一颗平衡二叉树。
（4）满足以上条件的目的：使得树的高度最低，因为树查找的效率决定于树的高度。二叉查找树的“二叉”是基于二分查找算法实现数据的搜索，不像单链表是基于顺序查找实现数据搜索，如果树的高度很高只有一个分支就变成单链表。顺序查找算法不管从平均查找次数还是最坏查找次数角度来说效率都不如二分查找算法。平衡二叉树也叫做平衡二叉查找树，因为它是基于二叉查找树实现的，平衡二叉树的高度越低意味着二分查找数据时查找的次数越少，查找次数越少自然意味着查找效率或性能更高。

5.B-Tree:

（1）非叶子节点既存储索引又存储实际数据，而且叶子节点没有链接在一起，这是和B+Tree的不同之处。

6.B+Tree：多叉平衡查找树

（1）相对平衡二叉树来说，B+Tree有多个分支，这是和平衡二叉树只有两个分支的不同之处；
（2）B+Tree数据结构仍然满足左子树上的每个节点值小于根节点值，右子树上的节点值大于等于根节点值；
（3）标准的B+Tree的叶子节点是通过单向链表进行链接，但是MySQL的InnoDB在实现数据存储引擎时稍微做了些优化，叶子节点的链接使用的是双向链表进行链接；
（4）非叶子节点存储索引，叶子结点存储实际数据；
（5）InnoDB存储引擎使用的数据结构是B+Tree。

7.B*树：

（1）与B+Tree的不同之处：非叶子节点之间也有指针指向；
（2）Oracle数据库使用B*树这种数据结构。

8.为什么使用B+Tree而不使用B-Tree？

（1）因为B树每个节点除了存储数据之外还存储索引信息，当每次查询大小固定的数据时，B树返回的目标数据就会变少，需要更多次数的查询才能获取全部想要的数据，所以相同规模数据的情况下使用B树这种数据结构会导致io次数增加；而B+Tree的数据都集中存放在叶子节点，相对B树来说每次查询可以返回更多条数据记录，io次数自然也就减少了，减少io次数性能自然也就提升了；
（2）范围查询时B+Tree的效率明显优于B树。因为B+Tree的叶子节点上存储的数据都是按照一定的顺序进行存储，查询速度会很快。

二、MySQL中的索引：

（一）B+Tree索引：

1.主键索引/聚集索引/聚簇索引：

在MySQL中，主键索引、聚集索引、聚簇索引都是相同的概念，它们是和主键关联的一种索引，除此之外的索引都是二级索引，二级索引也叫辅助索引。该类型根据表的主键构建一颗B+Tree，并且将整张表的行记录数据存放在B+Tree叶子节点中。

2.辅助索引/二级索引：

辅助索引和二级索引是同一类型索引只是叫法不一样。通过辅助索引获得主键，在通过主键索引（聚集索引）找到一个完整的行记录，这个过程叫做回表。

3.组合索引/复合索引/联合索引：

组合索引、复合索引、联合索引是相同概念的索引，它是将表中的多个列组合起来进行索引。

4.覆盖索引：

覆盖索引不是真正的索引，它只是一种思想。从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

（二）自适应一致性哈希索引：

1.InnoDB存储引擎中除了前面说的各种索引外，还有一种自适应哈希索引，我么知道B+Tree的查询次数取决于B+Tree的高度，在生产环境中，B+Tree的高度一般为3、4层，故需要3、4次的IO查询。
2.在InnoDB存储引擎内部会自动监控索引表，如果监控到某个索引经常使用，那么就认为这个索引对应的数据是热数据，然后InnoDB存储引擎内部会自动为这些热点数据创建一个hash索引，这个索引称之为自适应哈希索引。创建索引之后，下次再查询到这个索引，可以直接通过hash算法推导出记录的地址，直接一次性就能查询到数据，比重复去B+Tree中查询三四次节点的效率会高出不少。
3.自适应哈希索引是数据库自动创建的，不能进行人工人为干预。在MySQL5.7（包括该版本）之后默认开启自动创建自适应哈希索引。

（三）全文索引：

1.全文检索(Full-Text Search)：

（1）它是将存储在数据库中的整本书或整篇文章中的任意内容信息查找出来的一种技术。它可以根据需要获取全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。我们比较熟悉的Elasticsearch、Solr等就是全文检索引擎，底层都是基于Apache Lucene的。比如，我们要保存如下唐诗宋词，数据库中应该如何设计？
|朝代|作者|诗词年代|标题|诗词全文|
|唐|李白||…|静夜思|床前明月光，疑是地上霜。 举头望明月，低头思故乡。|
|宋|李清照|…|如梦令|常记溪亭日暮，沉醉不知归路，兴尽晚回舟，误入藕花深处。争渡，争渡，惊起一滩鸥鹭。|
|…|…|…|…|…|
要根据朝代或者作者寻找诗，都很简单，比如“‘select 诗词全文 from 诗词表 where 作者=‘李白’”，如果数据很多查询速度很慢，我们可以在对应的查询字段上建立索引加快查询速度。
（2）但是如果现在有个需求：要求找到包含“望”字的诗词怎么办？
用“‘select 诗词全文 from 诗词表 where 诗词全文 like ‘%望%’”,这意味着要扫描数据库中的全文字段，逐条比对，找出所有包含“望”字关键字的记录。基本上，数据库中一般的SQL优化手段都是用不上的。数据量少大概率还可以接受，如果数据量稍微有点大，就完全接受不了，更何况互联网环境下基本上都是大数据量的数据。可以用倒排索引解决这个问题。

2.倒排索引：

（1）倒排索引就是，将文档中的关键字全部提取处理，然后将关键字和文档的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字时，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到包含关键字的文档。于是我们可以按如下方式保存数据：
|序号|关键字|蜀道难|静夜思|春台望|鹤冲天|
|1|望|有|有|有|有|
如果查哪首诗词中包含“上”字，那么可以在上述表格中填入新的记录：
|序号|关键字|蜀道难|静夜思|春台望|鹤冲天|
|1|望|有|有|有|有|
|2|上|有|||有|
（2）从InnoDB1.2.x版本开始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。不过MySQL在设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索的支持并不友好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。所以MySQL中的全文检索功能比较弱鸡，了解即可。

（四）索引在查询中的使用：

索引的作用：
1.一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度；
2.一个select查询语句在执行过程中最多能使用一个二级索引，即使在where条件中用了多个二级索引。

（五）高性能的索引创建策略：

1.索引列的类型尽量小：

（1）我们在定义表结构时要显示指定列的类型，以整数类型为例，有ITNYINT、SMALINT、MEDUMINT、INT、BIGINT这几种，它们占用的存储空间依次递增。这里所说的类型大小指的是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的数据类型，比如能使用INT就不要使用BIGINT，能使用MEDUMINT就不要使用INT，这是因为数据类型越小，在查询时进行的比较操作越快（CPU层面），而且数据类型越小占用的内存空间越小，在一个数据页内就能存放更多条数据记录，从而减少磁盘IO带来的性能损耗，也就是说可以把更多的数据页缓存到内存中，从而加快读写效率。
（2）上面的建议对于表的主键来说更加适用，因为不仅聚簇索引中会存放主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，所以如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的IO。

2.索引的选择性计算(选择哪个字段建立索引)：

（1）创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，表中列所对应的不重复的索引值（也称为基数，cardinality）和数据表的记录总数(N)的比值，取值范围是[1/N,1]。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
（2）很差的索引选择性就是列中的数据重复度很高，比如性别字段，只有男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，也能查出一半的数据出来。

创建表：

CREATE TABLE `person` (
  `name` varchar(255) DEFAULT NULL,
  `sex` char(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（3）从选择性计算结果可以看出，当然是姓名字段的离散度最高，因为该字段里面的数据重复度最低，没有任何的重复数据；而性别字段则相反，重复度非常高，故不适合用来建立索引。
计算选择性/离散性（根据计算结果决定选择用哪个字段建立索引）：

SELECT 
count(DISTINCT name)/count(*) 姓名,
count(DISTINCT sex)/count(*) 性别,
count(DISTINCT age)/count(*) 年龄,
count(DISTINCT area)/count(*) 城市 
FROM person;

3.前缀索引：

针对blob、text、很长的varchar字段，MySQL不支持索引他们的全部长度，需建立前缀索引。

（1）语法：

alter table tableName add key/index (column(X));

（2）缺点：

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点，因为MySQL无法使用前缀索引做group by和order by,也无法使用前缀索引做覆盖扫描。

（3）后缀索引：

后缀索引(suffix index)有时候也有用途(例如，找到某个域名的所有电子邮件地址)。原生MySQL并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或应用程序自行处理来维护索引。

（4）找合适前缀长度：

可以看出，从第10个开始选择性的增加值很高，随着前缀字符的越来越多，选择度也在不断上升，但增长到第15时，选择性值的增加开始变慢和第14没有多大差别了，已经很接近整个列的选择性了。因此，针对这个字段做前缀索引的话，从第13到第15都是不错的选择。因此找到了合适的前缀长度，取中间值14作为前缀长度。

①创建表：

CREATE TABLE `order_exp` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint(22) NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint(6) NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

②计算离散性/选择性：

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

（5）创建前缀索引：

alter table order_exp add key(order_note(14));

（6）测试：

建立前缀索引后查询语句并不需要修改：经过测试建立前缀索引后查询速度快0.024s-0.020s=0.004秒。

select * from order_exp where order_note = '你好，李焕英。7排23号,过期时长:DD00_23S';

4.只为搜索、排序、分组的列创建索引：

也就是说，只为出现在where子句中的列、连接子句中的列创建索引，而出现在查询列表中的列一般没必要建立索引，除非是需要使用覆盖索引；又或者为出现在order by或group by子句中的列创建