首页 > 数据库 >mysql索引及索引创建原则

mysql索引及索引创建原则

时间：2023-06-21 15:12:05浏览次数：53  
标签：rand 前缀 10 创建 查询 索引 mysql where

1. mysql索引及索引创建原则

目录

1.1. 使用场景

1.1.1. 什么时候用索引

首先mysql只对<,<=,=,>=,>,between,in,like使用索引，因此where和join(on)的时候就应该考虑到使用什么样的索引，而且要记住一条sql语句只能使用一个索引，比如有where和order by同时出现的时候，order by 即使用的是索引字段，他也不会走索引。

1.1.2. 索引的弱点

(1) 加了索引之后，那insert、delete和update操作都要更新索引，会造成一些附加操作
(2) 索引是保存在物理文件(.MYI)里的，所以如果在一张大表上创建索引，势必会造成索引文件也比较大

1.1.3. MySQL 会使用到索引的场景如下：

  1. 根据一个条件快速的匹配到对应的行。

  2. 缩小查询影响行数。如果一个查询字段有多个索引，MySQL 通常选择使用影响行数最小的索引（选择性最高的索引）。索引的选择性的计算

select count(distinct name) / count(*) from table;

  1. 对于组合索引，索引左边的列可以用索引前缀优化器来查询数据。例如，你有个三列的组合索引（col1,col2,col3） ，那么你可以使用索引查询（col1）,(col1,col2),(col1,col2,col3)这三种组合的数据。

  2. 当和其他表进行连表查询的时候，如果进行判断的列的数据类型和大小相同，那么再这两个列上使用索引，可以让判断更加效率。例如：在如下查询中，给tb1.name和tb2.name添加索引会提升查询效率。

SELECT * FROM tb1, tb2 WHERE tb1. name = tb2. name

在这里，VARCHAR 与 CHAR 被认为是相同的类型。需要注意的是，如果要让索引生效，不仅需要类型一致，大小也必须一致。例如，VARCHAR(10) 和 CHAR(10) 大小相同可以使用索引，但 VARCHAR(10) 与 CHAR(15)就无法使用索引。

  1. 查找索引列的 MIN() 或 MAX()值。

  2. 通过索引列进行排序或分组，或者组合索引的左前缀进行排序或分组。

  3. 查询索引列的内容。（如果只需要返回索引列的值，那么不需要查询数据行，直接从内存中读取检索值。这种情况称为覆盖索引）
    例如：

SELECT key_part FROM table WHERE key_part=1

对于小型表或报表查询处理大多数或所有行的大型表的查询，索引不太重要。当查询需要访问大多数行时，顺序读取比通过索引更快。顺序读取可以最大限度地减少磁盘搜索，即使查询不需要所有行也是如此。只有数据较大，并且需要访问其中一部分数据的时候，索引才会显得比较重要。

1.2. 查看表上的索引

SHOW INDEX FROM table；


   table： 表的名称
　　Non_unique： 索引是否可以重复。不可以重复则为0；可以重复则为1。
　　Key_name： 索引名称。创建的时候，可以选择输入，不输入 MySQL 自动生成。如果索引是主键，则名称始终为 PRIMARY。
　　Seq_in_index： 索引中的列序列号，从1开始。
　　Column_name： 索引涉及到的列的名称。
　　Collation：列如何在索引中排序。这可以具有值 A（ascending 升序），D （ descending 降序）或NULL（未排序）。
　　Cardinality： 索引中唯一值的数量（不是实时更新的准确数据）。
　　Sub_part： 索引前缀长度。如果使用字段的部分字符作为索引，那么显示索引字符数量。如果使用整个字段都被索引，那么为 NULL。
　　Packed： key的打包方式，NULL 表示不打包。
　　Null： 索引列包含 NULL 或者 ‘’ 的时候，会是 YES。
　　Index_type： 索引类型。（BTREE， FULLTEXT，HASH， RTREE）之一。
　　Comment： 未在当前列中描述的索引信息，例如 disabled 索引是否已禁用。
　　Index_comment： 在创建索引时提供的注释。
　　Visible： 索引是否对优化程序可见（有的版本会出现该信息）。

1.3. 索引类型

1.3.1. 组合索引

组合索引是一个由多个列组成的索引。举例说明：例如在表 address 中有三个字段，分别为 Provincial 省 city 市 county 县 在建表的时候，用这三个字段组成一个组合索引。代码如下：

 CREATE TABLE address (
     provincial VARCHAR (10),
     city VARCHAR (10),
     county VARCHAR (10),
     INDEX (provincial, city, county)
 )
CREATE TABLE address

这里的索引是这样创建的：首先按照省排序，然后，再根据同一个省的内容，按照市进行排序，最后，按照县去排序。即，首先按照第一列进行索引排序，如果第一列内容一致，那么按照第二列进行排序，以此类推。

1.3.1.1. 组合索引生效规则

  1. 组合索引的生效原则是 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
    比如
    （a,b,c） 三个列上加了联合索引（是联合索引 不是在每个列上单独加索引）
where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果
where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关
  1. 还需注意， (a,b,c)多列索引和 (a,c,b)是不一样的，看上面的图也看得出来关系顺序是不一样的；
    分析几个实际例子来加强理解；
    分析句子中使用的索引情况
(0)    select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用
(1)    select * from mytable where  c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
(2)    select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的
(3)    select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(4)    select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果
(5)    select * from mytable where a>4 and b=7 and c=9;
a用到了  b没有使用，c没有使用
(6)    select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
(7)    select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(8)    select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

快速生成1000W测试数据库,创建测试表：

create table user (  
    id int(10) not null auto_increment,   
    uname  varchar(20) ,  
    regtime  char(30)  ,  
    age  int(11)   ,
    primary key (id)
)  
engine=myisam default charset=utf8 collate=utf8_general_ci  ,
auto_increment=1 ;

编写存储过程:

delimiter $$
SET AUTOCOMMIT = 0$$
 
create  procedure test()
begin
declare v_cnt decimal (10)  default 0 ;
dd:loop
          insert  into user values
    (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50);
      commit;
        set v_cnt = v_cnt+10 ;
           if  v_cnt = 10000000 then leave dd;
          end if;
         end loop dd ;
end;$$

delimiter ;

调用存储过程：
call test();

如果组合索引是(A,B)，则对于条件A=a，是可以用上这个组合索引的，因为组合索引是先按照第一列进行排序的，所以没必要对A单独建立一个索引，但是对于B=b就用不上了，因为只有在第一列相同的情况下，才比较第二列，因而第二列相同的，可以分布在不同的节点上，没办法快速定位

但是复合索引对于or查询不起作用

加上索引后and查询是可以走索引的，但是只有一个索引起作用，对于另一个索引字段还是要进行遍历，而且and查询会根据关联性高(符合该条件的行数少)选择具体走哪个索引

1.3.2. 前缀索引

前缀索引是用列的一部分字符去建立索引，更加节省空间（因为只使用一部分字符，索引建立时长度会降低），选择合适的话，效率也会更高。

如果将字符串的列作为索引，可以创建前缀索引。一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引。前缀索引以字节为单位。前缀索引支持的长度取决于存储引擎。例如，对于InnoDB 使用 REDUNDANT 或 COMPACT 行格式的表， 前缀最长可达767字节。对于InnoDB使用DYNAMIC 或 COMPRESSED 行格式的表， 前缀长度限制为3072字节 。对于MyISAM表，前缀长度限制为1000个字节。

如果指定的索引前缀超过最大列数据类型大小，对于非唯一索引，如果启用了严格的SQL模式，创建会发生错误。如果未启用严格SQL模式，索引长度减少到最大列数据类型大小，并产生警告。

创建前缀索引的长度，取决于索引的选择性。
创建前缀索引语法如下(这里的10 表示截取前10个字符)：

CREATE INDEX key_part_name ON table_name (key_part(10)); 
ALTER TABLE table_name ADD INDEX index_name (key_part(10))

前缀索引可以兼顾索引大小和查询速度。可以利用相对更小的空间，用更快的速度，查出数据。但是它也有缺点：前缀索引无法用于 ORDER BY 和 GROUP BY 操作，也不能用于索引覆盖。

1.3.3. 函数索引

这里的索引类型英文名称为：Functional Key Parts
这个索引类似于两个前缀索引的拼接。直接举个例子就明白了：在 t1 表中有两个列，col1 和 col2 我要创建一个包含完整的 col1 列和 col2 列的前10个字节组成一个组合索引。代码如下：

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

在 MySQL 8.0.13版本及更高版本中，MySQL 支持表达式进行索引（函数索引）。这里，需要将运算表达式写在括号内进行索引的声明。例如：

1 -- 函数索引
CREATE TABLE t1 (
      col1 INT,
      col2 INT,
      INDEX func_index ((ABS(col1)))
  );

CREATE INDEX idx1 ON t1 ((col1 + col2));

CREATE INDEX idx2 ON t1 (
     (col1 + col2),
     (col1 - col2),
     col1
 );

ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

1.3.4. 唯一索引

通过 UNIQUE 创建的索引。索引列的内容非null值的时候必须是唯一的，null值可以不唯一。如果添加重复值，则会发生错误。如果在创建唯一索引的时候指定前缀值，那么前缀必须是唯一的。
创建语法：

CREATE UNIQUE INDEX unique_index_name ON table_name (key_part)

1.3.5. 全文索引

全文索引，顾名思义，支持全文检索的索引。仅支持 Innodb 和 MyISAM 两种存储引擎。并且只能包括 CHAR, VARCHAR 和 TEXT 列，索引始终发生在整个列上，不支持前缀索引。（即使写了也没用）可以对字段进行全文检索。对于数据量比较大的数据集，先将数据加载到没有数据的表中，然后再添加索引，效率要比把数据直接向有索引的表中添加高。

1.3.6. 空间索引

空间索引是为空间搜索提供一种合适的数据结构，以提高搜索速度。对于空间索引，作者研究并不多，这里大概介绍下空间索引的用处。等以后研究深刻后，再补上这部分内容。首先，空间索引是干什么用的？举例：当我们需要按照某个点，查询附近的50米内都有哪些客户存在。对于这个需求，可能的解法如下：
　　1.我们可以根据用户的经纬度，去计算每个人跟我们的当前点的距离，然后跟50米去作对比。这在客户数据量少的时候，还可以这么做，数据量大的时候，将会特别的浪费性能。
　　2.先画一个方框，把50米范围的数据的经纬度画出来，通过经纬度的值进行筛选后，得到一个正方形的区域，然后再进行计算。这时候，会少很多计算，但依然不是最优方案。
　　3.使用空间索引。将空间按照一定规则划分为不同的区域，在检索的时候，根据设计的区域，取出相应的数据。空间索引结构图如下（图片来自知乎）：

1.4. 索引优化

1.4.1. 主键优化

　　表的主键是唯一且非空的索引，在使用InnoDB存储引擎的时候，表数据直接挂载在主键的叶子节点上，是查询速度最快的索引。

　　如果表的内容很多，并且很重要。但是没有明显的列和列的集合作为主键的话，可以单独创建一个自动增长的值作为主键。当使用外链查询的时候，这个id可以作为指向内容的指针。

1.4.2. 外键优化

　　如果你的表有很多列，你可以将查询频率比较低的列拆分到其他表格，并通过复制id的方式让它们与主表关联。这样，每个小表都会有个主键来快速查找其他数据。在查询的时候，就可以仅查询自己需要的列集。这时，查询会执行较少的 I/O 并且占用较少的内存。整体原则是：为了提高性能，尽可能少的从磁盘读取数据。这就是拆表的原则

1.5. MySQL优化之索引创建规则

1.表的主键、外键必须有索引；
2.数据量超过300的表应该有索引；
3.经常与其他表进行连接的表，在连接字段上应该建立索引；
4.经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5.索引应该建在选择性高的字段上；
6.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
8.正确选择复合索引中的主列字段，一般是选择性较好的字段；
9.复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
10.如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
11.如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
12.如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
13.频繁进行数据操作的表，不要建立太多的索引；
14.删除无用的索引，避免对执行计划造成负面影响；

以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引 与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合 索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

标签：rand,前缀,10,创建,查询,索引,mysql,where
From： https://www.cnblogs.com/xulinforDB/p/17496262.html

相关文章

  • mysql索引和基本概念
    1.mysql索引和基本概念目录1.mysql索引和基本概念1.1.声明1.2.什么是索引1.3.二分查找法(BinarySearch)1.4.二叉查找树(BST）1.4.1.二叉树的特点1.4.2.二叉树存在的问题1.5.平衡二叉树(AVLTree)1.5.1.平衡二叉树的特点1.6.索引需要存储什么1.7.B树的特点1.7.1.B树是......
  • mysql 密码插件 validate_password
    MySQL密码增强插件2016-07-0110:02pursuer.chen阅读(668)评论(0)编辑[收藏](javascript:void(0))介绍以前没有太注意MySQL密码安全策略的配置方法，只是人为了将密码设为复杂密码，但是没有找到配置的方法，今天姜承尧的微信公众号正好发布了一篇关于这个的文章，所以在这里也顺......
  • MySQL笔记整理
    SELECT0+'123.00';SELECT0+'123.0qwe';SELECT0+'qwe1';SELECT0+null;SELECT'123.00'/4;SELECT'123.0qwe'/4;SELECT'qwe1'/4;SELECT'1qwe'/4;SELECTnull/4;SELECTconvert(......
  • 用cloudmonkey批量创建虚拟机
    需求：1.root磁盘120G（这个在做镜像的时候已经做好）2.需要用到share网络3.添加500G磁盘并且挂载早虚拟机上面#!/bin/bashzone_id=d530fee4-413a-463b-abf5-4fdcf523077bnetwork_id=96eb15a6-ccd3-4e33-adf0-b5f3e63e2466serviceofferingid='b1fb445d-2571-4803-a3f5-93f0ad81b245't......
  • MySQL自带的性能压力测试工具mysqlslap
    1.MySQL自带的性能压力测试工具mysqlslap目录1.MySQL自带的性能压力测试工具mysqlslap1.1.概述1.2.常用参数[options]详解1.3.测试范例：1.3.1.实例11.3.2.实例21.3.3.实例3(自定义sql语句)1.3.4.实例4(指定sql脚本)1.3.5.实际测试中的复杂情况。（指定表字段）1.4.测试结......
  • mysql proxy实现读写分离
    Mysql-proxy实现读写分离目录Mysql-proxy实现读写分离环境说明Mysql-proxy简介部署mysql-proxy服务读写分离测试总结环境说明Mysql-proxy简介mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等MySQLProxy就是这么一个中间层代理，简单的说，MySQLPro......
  • MySQL性能压测工具SysBench详解（非常详细）
    MySQL性能压测工具SysBench详解（非常详细）概述掌握数据库的性能情况是非常必要的。本文介绍了MySQL基准测试的基本概念，以及使用sysbench对MySQL进行基准测试的详细方法;基准测试与压力测试简介1、什么是基准测试数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可......
  • mysql的用户和权限管理
    1.mysql用户和权限管理目录1.mysql用户和权限管理1.1.常用授权语句1.1.1.5.7以及以前的版本1.1.1.1.存储过程权限管理1.1.2.8.0的版本：1.1.3.删除用户及权限1.1.4.修改1.1.4.1.修改密码1.1.4.2.修改用户账号名称1.1.5.回收权限1.1.6.grant授权和直接操作权限表的区别......
  • Linux MySQL 5.7二进制 小版本升级
    LinuxMySQL5.7二进制小版本升级LinuxMySQL5.7二进制小版本升级MySQL5.7二进制安装在Unix/Linux上升级时，分为就地和逻辑升级方法。1就地升级就地升级包括关闭旧的MySQL服务器，用新的MySQL服务器替换旧的MySQL二进制文件或软件包，在现有数据目录上重新启动MySQL，以及运行mys......
  • 记一次mysql小版本升级
    记一次mysql小版本升级最近对后端组件进行安全扫描时，发现了一些轻微漏洞，为了避免后续部署后安全扫描出现问题，决定对mysql做一次版本升级。升级的原则是对mysql的二进制文件进行升级，若有主备节点，先升级从节点升级完成后将其提为主节点，然后再升级原主节点。升级步骤：mysql当前版本......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99