锐客网

  1. 首页 > it技术 > >

mysql中高阶玩法系列（三）

数据库


目录

  • 前缀索引
  • SHOW INDEX
  • 参考

前缀索引 并不是在所有的查询条件中出现的列都需要添加索引，对于什么时候添加B+树索引，一般情况下，在访问表中很少的一部分数据时使用B+树索引才有意义。例如对于性别字段、地区字段、类型字段，它们可取值的范围很小，为低选择性。如果某个字段的取值范围比较广，几乎没有重复，即属于高选择性，则使用索引比较合适。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。索引的选择性越高则查询效率越高，因为选择性高的索引可以过滤更多的行。
索引的选择性是指：不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从[1 / #T， 1]。所以，选择性就可以理解为目录的多少，目录越多就越方便定位，这就是高选择性，所以如果索引是高选择性的话，那就可以非常快查询到数据了。反之，则是低选择性。
有时候需要索引很长的字符字段，如 BLOB、TEXT 或者很长的 VARCHAR 类型的字段，这会让索引变得很大，就要考虑占用空间和效率问题，导致查询很慢。对于这种情况，我们可以使用前缀索引来索引数据最开始的部分字符，这样可以大大的节约索引空间，从而提高索引效率，但这样也会降低索引的选择性，因为部分字符作为索引的话，会出现多条数据都有同样的前缀，选择性就低了。
先定义一张表，基于这张表做的演示，那么就定义一张最常见的用户表吧。
CREATE TABLE `user` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) COLLATE UTF8MB4_UNICODE_CI NOT NULL, `pwd` VARCHAR(255) COLLATE UTF8MB4_UNICODE_CI NOT NULL, `created_at` INT(8) UNSIGNED NOT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`), KEY `idx_created_at` (`created_at`) )ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI;

插入几条数据，方便之后快乐的玩耍
INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('1', '牛A', '555', '1559318400'); INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('2', '牛B', '555', '1559318400'); INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('3', '牛C', '555', '1573441871'); INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('4', '牛X', '555', '1558329240');

mysql中高阶玩法系列（三）
文章图片




为了方便实验，需要庞大的数据，所以可以重复使用下面的语句，成倍的插入数据，具体数量多少可以自己掂量。
# '牛'拼接[1, 1000000]之间的整数成倍插入到name字段，其余字段保持不变 INSERT INTO user(name,pwd,created_at) SELECT CONCAT('牛',FLOOR(1 + RAND() * 1000000)),pwd,created_at FROM user;

mysql中高阶玩法系列（三）
文章图片

说了这么多，下面就开始怎么决定前缀索引的长度。
当前缀索引的选择性越接近全列选择性的时候，索引效果越好。 诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长.前缀长的选择性接近于索引整个列.换句话说,前缀的基数应该接近于完整列的基数。所以，我们要先学会怎么计算选择性。
# 计算全列选择性 SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name; # 测试某一长度前缀的选择性 SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

下面演示一下：通过语句组合将计算后的选择性制作成表进行比对
SELECT COUNT(DISTINCT LEFT(name, 1))/COUNT(*) AS Selectivity1, COUNT(DISTINCT LEFT(name, 2))/COUNT(*) AS Selectivity2, COUNT(DISTINCT LEFT(name, 3))/COUNT(*) AS Selectivity3, COUNT(DISTINCT LEFT(name, 4))/COUNT(*) AS Selectivity4, COUNT(DISTINCT LEFT(name, 5))/COUNT(*) AS Selectivity5, COUNT(DISTINCT LEFT(name, 6))/COUNT(*) AS Selectivity6, COUNT(DISTINCT LEFT(name, 7))/COUNT(*) AS Selectivity7, COUNT(DISTINCT LEFT(name, 8))/COUNT(*) AS Selectivity8, COUNT(DISTINCT LEFT(name, 9))/COUNT(*) AS Selectivity9, COUNT(DISTINCT LEFT(name, 10))/COUNT(*) AS Selectivity10, COUNT(DISTINCT name)/COUNT(*) AS Selectivity全 FROM user;

mysql中高阶玩法系列（三）
文章图片

能看到，图片中，当前缀索引长度为1时，选择性为零，那是因为name字段的值都是以’牛’为开头的，百分百重复；而当前缀索引长度不断变长的时候，选择性也随着增大，但当长度大于等于7的时候，选择性就不再变大，并且选择性的值等于了全列选择性，那是因为当初在设置随机值的时候最大为1000000，所以能排列组合后能变化的位数就是前7位，说明最佳前缀索引的长度为7。
出于前缀索引长度越长，索引所占空间越大，查询越慢的考虑，就算长度再长也无法再提高选择性，所以这里设立的最佳前缀索引长度为7。



建立前缀索引的语法格式：
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

如下例：
ALTER TABLE user ADD KEY(name(7));




  • 优点：能使索引更小、更快。
  • 缺点：无法使用前缀索引进行ORDER BY 和 GROUP BY
    ，也无法使用前缀索引做覆盖索引（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。
  • 常见的应用场景：针对很长的十六进制唯一ID使用前缀索引。



SHOW INDEX 很多时候，我们需要知道一张表到底有多少索引，有个非常好用的方法。有助于诊断性能低下的查询，尤其是查询是否使用了可用的索引
SHOW INDEX FROM Table_Name

如下例：查看tb_activity的所有索引，并对其属性进行分析
SHOW INDEX FROM tb_activity;

mysql中高阶玩法系列（三）
文章图片

先介绍下这个表的一些情况，从红框里可以看出字段type和store_id是联合主键
接下来就从栏目名进行分析：
  • Table
    数据表名

  • Non_unique
    非唯一索引，简单来说就是，如果该字段是唯一索引的话就为0，不是唯一索引就为1。能看到前两个字段都为0，就是因为前两个字段是联合主键，所以当然就是唯一索引了

  • Key_name
    索引名，数据库在建表时设立的主键的同时就是建立了索引，所以主键的索引名为PRIMARY，而前两个字段是联合主键，所以前两个字段的索引名都为PRIMARY，所以前两个字段也是建立了联合索引的

  • Seq_in_index
    索引中的列序列号，默认是从1开始排序，在字段type和字段store_id中的联合索引里，这两个字段具有同样的索引名就说明它们是同一个索引，字段type在索引中的列序列号是1，字段store_id在索引中的列序列号是2，这就说明了建表的时候设立联合主键的时候type是在store_id之前的，这样就可以猜解到联合主键的前后顺序，因为联合主键的前后顺序将意味着查询的时候能不能通过索引来查询，如果是三个字段作为联合主键的话，那么在字段type和字段store_id之后就会有另一个字段在索引中的列序列号为3，而这个表中的其它字段都不是联合索引，那当然在索引中的列序列号各自的序号是重新从1开始排序了

  • Column_name
    字段名

  • Collation
    以什么方式存储在索引中，大概意思就是字符序。在MySQL的SHOW INDEX语法中，有值’A’（升序）或NULL（无分类）。B+树索引总是A，即是排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了， 因为Hash根据hash桶存放索引数据的，而不是对数据进行排序。默认的字符集类型是utf8_general_ci，这样的字符集对大小写不敏感，比如下面两个sql会出现相同的查询结果：
select * from Table where content = 'Yes' select * from Table where content = 'yes'


  • Cardinality
    基数的意思，表示索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的，在myisam中，该值是准确的，INNODB中该值数据是估算的，存在偏差。基数越大，当进行联合时，MySQL使用该索引的机会就越大。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。因为MySQL数据库中有各种不同的存储引擎，而每种存储引擎对于B+树索引的实现又各不相同。所以对Cardinality统计时放在存储引擎层进行的。

  • Sub_part
    前缀索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

  • Packed
    字段压缩信息。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。

  • Null
    是否允许为空。如果该字段的数据里含有NULL，则该栏为YES。我们知道建立索引的列是不允许为Null的，单列索引不存Null值，复合索引不存在全为Null的值，如果列允许为Null，可能会得到“不符合预期”的结果集。

  • Index_type
    索引类型，Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE
1). FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。


2). HASH
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。


3). BTREE BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。


4). RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

  • Comment
    索引信息（空：局部索引，INDEX：全局二级索引的索引列，COVERING：全局二级索引的覆盖列）

  • Index_comment
    为索引创建时提供了一个注释属性的索引的评注
参考 【mysql中高阶玩法系列（三）】https://blog.csdn.net/cdx_akang/article/details/83900427
https://help.aliyun.com/document_detail/142895.html
http://ddrv.cn/a/247018
https://www.jianshu.com/p/85a18c1e7d41
https://www.cnblogs.com/studyzy/p/4310653.html
https://www.cnblogs.com/gomysql/p/3628926.html
https://yq.aliyun.com/articles/502786

    推荐阅读


    上一篇：anaconda换源和恢复默认源

    下一篇：tomcat自动解压