精华内容
下载资源
问答
  • MySQL_MySQL中的唯一索引的简单学习教程,mysql 唯一索引UNIQUE一般用于不 - phpStudy
    千次阅读
    2021-01-19 16:44:27

    MySQL中的唯一索引的简单学习教程

    mysql 唯一索引UNIQUE一般用于不重复数据字段了我们经常会在数据表中的id设置为唯一索引UNIQUE,下面我来介绍如何在mysql中使用唯一索引UNIQUE吧。

    创建唯一索引的目的不是为了提高访问速度,而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一,索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该使用关键字UNIQUE。

    把它定义为一个唯一索引。

    创建表时直接设置:

    DROP TABLE IF EXISTS `student`;

    CREATE TABLE `student` (

    `stu_id` int(11) NOT NULL AUTO_INCREMENT,

    `name` varchar(255) DEFAULT NULL,

    PRIMARY KEY (`stu_id`),

    UNIQUE KEY `UK_student_name` (`name`)

    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

    创建唯一索引:

    create unique index UK_student_name on student (name);

    建表后添加约束:

    alter table student add constraint uk_student_name unique (name);

    如果不需要唯一索引,则可以这样删除

    mysql> ALTER TABLE student DROP INDEX name;

    Query OK, 0 rows affected (0.85 sec)

    如果要增加索引

    alter table user add unique index(user_id,user_name);

    相关阅读:

    CSS+jQuery实现的一个放大缩小动画效果

    javascript获取dom的下一个节点方法

    SQLSERVER启动不起来(错误9003)的解决方法分享

    jQuery+CSS3+Html5实现弹出层效果实例代码(附源码下载)

    Windows8.1系统控制面板在哪如何快速打开

    SqlCommandBuilder如何实现批量更新

    JavaScript使用DeviceOne开发实战(二) 生成调试安装包

    ThinkPHP的L方法使用简介

    安装mysql出错”A Windows service with the name MySQL already exists.“如何解决

    jQuery获取Radio,CheckBox选择的Value值(示例代码)

    escape编码与unescape解码汉字出现乱码的解决方法

    Linux系统下CVS的账号控制操作简介

    Oracle提高sql执行效率的心得建议

    linux怎么更改屏幕分辨率?linux屏幕分辨率的设置教程

    更多相关内容
  • 详解mysql中索引作用

    万次阅读 2018-06-28 14:48:48
    最左原则,联合索引中会遵循最左原则,即如果要使用联合索引,那么前面的索引列一定要包含,举个例子,  有个联合索引(a,b,c) 那么查询条件中只能是 a=1 或者 a=1 and b=1 或者 a=1 and b=1 and c=1,不然索引就会...

    1. 索引是什么,首先我们可以举个例子,字典大家应该都使用过,我们可以使用目录快速定位到所要查找的内容,那么索引跟目录的作用类似,在数据库表记录中,利用索引,可以快速过滤查找到数据记录。

    2. 索引类型分类

       2.1 普通索引

            加快查询速度

       2.2 唯一索引

            加快查询速度,并且可以限制列值的唯一性,允许为空

            唯一索引包括联合唯一索引,多列形成的唯一索引,这些列可以唯一确定一条表记录,可以为空

       2.3 全文索引

            主要针对文本的内容进行分词,加快查询速度

       2.4 联合索引

            多列组成的索引,查询效率提升高于多个单列索引合并的效率

       2.5 主键索引

             主键唯一性,加快查询速度,不能为空

    3. 索引结构分类

        索引有很多分类,例如B-tree索引,哈希索引,全文索引等等,索引的实现是在存储引擎层,并不是在后端服务器层,所以不同的存储引擎支持的索引结构也不一定一样

        3.1  B-tree索引

         B-tree 索引普遍存在于存储引擎中,他使用B-tree数据结构来存储数据,如果对树形数据结构比较了解的话,就知道B-tree索引所带来的好处了,他的每个叶子节点都会包含下一个节点的指针,非常方便查询数据

         B-tree 适用于全键值,键值范围,或者前缀查找

        3.2 哈希索引

         哈希索引基于哈希表实现,对于每一行数据,存储引擎会对所有的索引列计算一个哈希码,然后存储引擎会基于这个哈希码来查找数据,小编感觉有点像HashMap 中槽的查询过程

        3.3 全文索引

         全文索引在几种索引结构类型中比较特殊,他查找的是文本中关键词

    4. 索引的正确使用

        索引是建立在系统文件上的,会占用一定的内存空间,另外数据在更新的时候也会去维护索引,消耗内存,所以索引一定要正确的使用,索引并不是越多越好,要根据具体的查询业务来规划索引的建立。

    建议不要使用索引的几种情况:

        1. 区分度不是很大的字段,例如 性别 sex

        2. 频繁更新的字段

        3. 字符串类型的字段 或者 文本类型的字段

        4. 不在where列中出现的索引

    索引失效的几种情况:

        1. 查询列中有函数计算 

        2. 查询列中有模糊查询,"%cloum",可以使用"cloum%" 代替,如果要使用"%column%",那么select 列中是索引列

        3. 如果查询条件中有or, 索引会失效,除非所有条件都加上索引

        4. 使用不等于(!= 或者 <>)

        5. is null 或者 is not null

        6. 字符串不加引号,会导致索引失效

        7. 最左原则,联合索引中会遵循最左原则,即如果要使用联合索引,那么前面的索引列一定要包含,举个例子,

           有个联合索引(a,b,c) 那么查询条件中只能是 a=1 或者 a=1 and b=1 或者 a=1 and b=1 and c=1,不然索引就会失效

    5. explain 分析查询sql

       

    select_type :

               SIMPLE: 简单的查询,不包括子查询,关联查询等等

               PRIMARY: 查询中如果有复杂的部分,最外层的查询将被标记为PRIMARY

               SUBQUERY: 子查询中的第一个查询

               UNION: 关联查询,最后面的一个

               .............. 

    type:查询语句的性能表现: 依次递增 all<index<range<index_merge<ref<eq_ref<constant/system

               All:  全表扫描,最耗性能

               index: 全索引列扫描

               range: 对单个索引列进行范围查找 ,使用 < 或者 between and 或者 in 或者 !=

               index_merge: 多个索引合并查询

               ref:  根据单个索引查找

               eq_ref: 连接时使用primary key 或者 unique类型

               constant: 常量

               system: 系统


     possible_keys: 可能使用到的索引

     key: 真实使用的索引

     key_len: 使用到的索引长度

     rows:扫描的行数

     extra:包含MySQL为了解决查询的详细信息        

    6. 慢查询日志

        慢查询日志是指 mysql中查询 时间超过固定阈值的查询记录,默认时间是10秒,mysql默认情况下不开启慢查询

        默认:show variables like "%slow_query_log%"


     手动开启 set global 变量名 = 值

     set global slow_query_log = on ; 手动开启慢查询日志

     set global long_query_time = 10; 手动设定查询时间超过的值,超过就会记录查询日志

     set global slow_query_log_file = "G:\var\slow_log.txt"; 手动设定慢查询日志的记录地址

     set global log_queries_not_using_indexs = on ; 手动设定 是否要记录 查询中使用到索引的记录

    展开全文
  • mysql索引详解

    万次阅读 多人点赞 2021-07-07 21:40:09
    一、MySQL三层逻辑架构 MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图: 一、对比InnoDB与MyISAM ...InnoDB:所有的表都保存在同一个数据文件(也可能是多个

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    目录

    一、MySQL三层逻辑架构

    1、第一层负责连接管理、授权认证、安全等等。

    2、第二层负责解析查询

    3、第三层是存储引擎

    二、对比InnoDB与MyISAM

    1、 存储结构

    2、 存储空间

    3、 可移植性、备份及恢复

    4、 事务支持

    5、 AUTO_INCREMENT

    6、 表锁差异

    7、 全文索引

    8、表主键

    9、表的具体行数

    10、CRUD操作

    11、 外键

    三、sql优化简介

    1、什么情况下进行sql优化

    2、sql语句执行过程

    3、sql优化就是优化索引

    四、索引

    1、索引的优势

    2、索引的弊端

    3、索引的分类

    4、创建索引

    5、MySQL索引原理 -> B+树

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    六、分析sql的执行计划---explain

    1、explan使用简介

    2、explain查询结果简介


    一、MySQL三层逻辑架构

    MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图:

    1、第一层负责连接管理、授权认证、安全等等。

    每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池,避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时,服务器对其进行认证。可以通过用户名和密码的方式进行认证,也可以通过SSL证书进行认证。登录认证通过后,服务器还会验证该客户端是否有执行某个查询的权限。

    2、第二层负责解析查询

    编译SQL,并对其进行优化(如调整表的读取顺序,选择合适的索引等)。对于SELECT语句,在解析查询前,服务器会先检查查询缓存,如果能在其中找到对应的查询结果,则无需再进行查询解析、优化等过程,直接返回查询结果。存储过程、触发器、视图等都在这一层实现。

    3、第三层是存储引擎

    存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信,这些API屏蔽了不同存储引擎之间的差异,使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。

    二、对比InnoDB与MyISAM

    1、 存储结构

    MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

    InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

    2、 存储空间

    MyISAM: MyISAM支持支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后,不会再进行修改操作,可以使用压缩表,极大的减少磁盘的空间占用。

    InnoDB: 需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

    3、 可移植性、备份及恢复

    MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

    InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。

    4、 事务支持

    MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。

    InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

    5、 AUTO_INCREMENT

    MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。

    InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。

    6、 表锁差异

    MyISAM: 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

    InnoDB: 支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

    7、 全文索引

    MyISAM:支持 FULLTEXT类型的全文索引

    InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。

    8、表主键

    MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。

    InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

    9、表的具体行数

    MyISAM: 保存有表的总行数,如果select count() from table;会直接取出出该值。

    InnoDB: 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。

    10、CRUD操作

    MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。

    InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。

    11、 外键

    MyISAM:不支持

    InnoDB:支持

    三、sql优化简介

    1、什么情况下进行sql优化

    性能低、执行时间太长、等待时间太长、连接查询、索引失效。

    2、sql语句执行过程

    (1)编写过程

    select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

    (2)解析过程

    from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

    3、sql优化就是优化索引

    索引相当于书的目录。

    索引的数据结构是B+树。

    四、索引

    1、索引的优势

    (1)提高查询效率(降低IO使用率)

    (2)降低CPU使用率

    比如查询order by age desc,因为B+索引树本身就是排好序的,所以再查询如果触发索引,就不用再重新查询了。

    2、索引的弊端

    (1)索引本身很大,可以存放在内存或硬盘上,通常存储在硬盘上。

    (2)索引不是所有情况都使用,比如①少量数据②频繁变化的字段③很少使用的字段

    (3)索引会降低增删改的效率

    3、索引的分类

    (1)单值索引

    (2)唯一索引

    (3)联合索引

    (4)主键索引

    备注:唯一索引和主键索引唯一的区别:主键索引不能为null

    4、创建索引

    alter table user add INDEX `user_index_username_password` (`username`,`password`)

    5、MySQL索引原理 -> B+树

    MySQL索引的底层数据结构是B+树

    B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。

    B-Tree结构图中每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。

    B+Tree相对于B-Tree有几点不同:

    非叶子节点只存储键值信息。
    所有叶子节点之间都有一个链指针。
    数据记录都存放在叶子节点中。
    将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

    通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:

    InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为〖10〗^3)。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

    实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

    数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引,聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    (1)使用联合索引的全部索引键可触发联合索引

    (2)使用联合索引的全部索引键,但是用or连接的,不可触发联合索引

    (3)单独使用联合索引的左边第一个字段时,可触发联合索引

    (4)单独使用联合索引的其它字段时,不可触发联合索引

    六、分析sql的执行计划---explain

    explain可以模拟sql优化执行sql语句。

    1、explan使用简介

    (1)用户表

    (2)部门表

    (3)未触发索引

    (4)触发索引

    (5)结果分析

    explain中第一行出现的表是驱动表。

    1. 指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]
    2. 未指定联接条件时,行数少的表为[驱动表]

    对驱动表直接进行排序就会触发索引,对非驱动表进行排序不会触发索引。

    2、explain查询结果简介

    (1)id:SELECT识别符。这是SELECT的查询序列号。

    (2)select_type:SELECT类型:

    1. SIMPLE: 简单SELECT(不使用UNION或子查询)
    2. PRIMARY: 最外面的SELECT
    3. UNION:UNION中的第二个或后面的SELECT语句
    4. DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
    5. UNION RESULT:UNION的结果
    6. SUBQUERY:子查询中的第一个SELECT
    7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
    8. DERIVED:导出表的SELECT(FROM子句的子查询)

    (3)table:表名

    (4)type:联接类型

    1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
    2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    6. index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    7. unique_subquery:该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    8. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    9. range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
    10. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    11. all:对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    (5)possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    (6)key:key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

    (7)key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

    (8)ref:ref列显示使用哪个列或常数与key一起从表中选择行。

    (9)rows:rows列显示MySQL认为它执行查询时必须检查的行数。

    (10)Extra:该列包含MySQL解决查询的详细信息。

    1. Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
    2. Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    3. range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
    4. Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
    5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
    6. Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    7. Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。
    8. Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
    9. Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个乘积来确定执行哪个多表SELECT语句。

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    关注公众号,回复1024,获取Java学习路线思维导图,加入万粉计划交流群

    展开全文
  • MySQL中索引的使用方法

    千次阅读 2021-05-03 13:23:45
    MySQL中索引的使用方法 文章目录MySQL中索引的使用方法1. 为什么要加索引?2. 索引的创建2.1 使用 `ALTER TABLE` 语句创建索引2.2 使用 `CREATE INDEX` 语句对表增加索引3. 索引的删除4. 组合索引与前缀索引5. 索引...

    1. 为什么要加索引?

    ​ 一般的应用系统,读写比例在10:1左右,而且插入和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。

    ​ 在数据量和访问量不大的情况下,MySQL的访问时非常快速的,是否加索引对访问影响不大。但是当数据和访问量剧增的时候,就会发现MySQL变慢,甚至down掉,这就必须考虑优化SQL了。给数据库建立正确合理的索引,是MySQL优化的一个重要手段。

    ​ 索引的目的在于提高查询效率,可以类比字典,如果要查"mysql"这个单词,我们肯定需要定位到m字母,然后从前往后找到字母y,再找到剩下的"sql"。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的。出了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获取数据的范围来筛选出想要的结果,同时把随机的时间变成顺序的时间,也就是我们总是通过一种查找方法来锁定数据。

    ​ 在创建索引时,需要考虑哪些列会用于SQL查询,然后为这些列创建一个或多个索引。事实上,索引也是一种表,保存着主键或索引字段,以及一个能将每个记录只想实际表的指针(非聚簇索引和聚簇索引,一个在索引中保存的是字段值的地址,一个保存的就是字段的值)。数据库用户是看不到索引的,它们只是用来加速查询的。数据库搜索引擎使用索引来快速定位记录。

    INSERTUPDATE 语句在拥有索引的表中执行会花费更多的时间,而 select 语句却会执行得更快,这是因为在进行插入和更新时,数据库也需要插入或更新索引值。

    2. 索引的创建

    索引的类型:

    • UNIQUE (唯一索引):不可能出现相同的值,可以有NULL值;
    • INDEX (普通索引):允许出现相同的索引内容;
    • PRIMARY KEY (主键索引):不允许出现相同的值;
    • FULLTEXT INDEX (全文索引):可以针对值中的某个单词,但效率确实不敢恭维;
    • 组合索引:实质上是将多个字段建到一个索引里,列值得组合必须唯一

    2.1 使用 ALTER TABLE 语句创建索引

    ALTER TABLE 应用于数据表创建完毕之后再添加。

    ALTER TABLE 表名 ADD 索引类型(unique, primary key, fulltext, index) [索引名] (字段名)

    例子:

    -- 普通索引
    alter table table_name add index [index_name] (column_list);  -- 不加索引名字index_name时,默认根据第一个索引列赋一个名称 
    
    -- 唯一索引
    alter table table_name add unique [index_name] (column_list);  -- 不加索引名字index_name时,默认根据第一个索引列赋一个名称
    或者
    alter table table_name add unique index [index_name] (column_list);
    
    -- 主键索引
    alter table table_name add primary key (column_list); -- 一张数据表只能有一个主键
    

    ALTER TABLE 可用于创建普通索引UNIQUE索引PRIMARY KEY索引3种索引格式,table_name 是要添加索引的表名,column_list 之初对那些列进行索引,多列时各列之间用逗号分隔。索引名index_name 可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。

    2.2 使用 CREATE INDEX 语句对表增加索引

    CREATE INDEX 可用于对表增加 普通索引UNIQUE 索引,可用于建表时创建索引。

    CREATE INDEX index_name ON table_name (column_name(length));
    

    length 的作用:对于 CHAR 和 VARCHAR 类型的列,只用一列的一部分就可以创建索引。在创建时,使用 column_name(length) 语法,对前缀编制索引。前缀包括每列值得前length个字符。BLOBTEXT 类型的列则必须给出前缀长度 length. 如下:

    CREATE INDEX index_name on table_name (name(10));
    

    name 列的前 10 个字符创建索引。

    使用列的一部分创建索引可以使索引文件大大减小,从而节约大量的磁盘空间,有可能提高INSERT操作的速度。

    总结:length字段的作用,即创建索引时,指定使用字段中的具体部分长度类创建索引。

    CREATE INDEX 语法只能创建两种类型的索引:INDEXUNIQUE.

    -- 普通索引
    create index index_name on table_name (column_list);
    -- 唯一索引
    create unique index index_name on table_name (column_list);
    

    不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引.

    例子:

    drop table if exists `student`;
    create table student(
    `id` int not null,
    `name` varchar(50) not null,
    `age` int default 0,
    `number` varchar(100) not null
    )Engine=InnoDB DEFAULT CHARSET=utf8;
    create index name_index on student (name);
    create unique index number_index on student (number);
    

    得到的表如下所示,用 name 列创建了普通索引 name_index. 用 number 列创建了唯一索引 number_index. (注意在创建唯一索引时必须是 create unique index,不能像 ALTER TABLE 那里省略).
    在这里插入图片描述

    3. 索引的删除

    删除索引可以使用 ALTER TABLEDROP INDEX 语句来实现。DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理,其格式如下:

    drop index index_name on table_name;  -- 普通索引和唯一索引可用此方法删除,主键索引不能用此方法删除
    
    alter table table_name drop index index_name; -- 主键索引不能用此方法删除
    
    alter table table_name drop primary key; -- 可以删除主键索引,前提是主键没有自增 auto_increment,如果有自增将无法删除,需先删除自增
    

    删除主键索引只能采用第三种方法,因为一个表只能有一个 PRIMARY KEY 索引,因此不需要指定索引名。并且在删除主键索引的时候必须先删除主键的AUTO_INCREMENT,否则将不能删除主键索引。

    如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引也将被删除。

    4. 组合索引与前缀索引

    组合索引前缀索引 是对建立索引技巧的一种称呼,并不是索引的类型。为了更好的表述清楚,建立一个demo表如下:

    drop table if exists `user_demo`;
    create table user_demo
    (
        `id`			int not null auto_increment comment '主键',
        `login_name`	varchar(100) not null comment '登录名',
        `password`		varchar(100) not null comment '密码',
        `city`			varchar(30)  not null comment '城市',
        `age`			int not null comment '年龄',
        `sex`			int not null comment '性别(0:女 1:男)',
        primary key (`id`)
    )Engine=InnoDB DEFAULT CHARSET=utf8;
    

    为了进一步榨取MySQL的效率,就可以考虑建立组合索引,即将login_namecityage 建到一个索引里:

    alter table user_demo add index name_city_age (login_name(16), city, age);
    

    建表时,login_name 的长度为100,这里用16,是因为一般情况下名字的长度不会超过16,这样会加快索引查询速度,还会减少索引文件的大小,提高INSERT、UPDATE操作的速度。

    如果分别给login_namecityage 建立单列索引,让该表有3个单列索引,查询时和组合索引的效率是大不一样的,甚至远远低于我们的组合索引。虽然此时有3个索引,但MySQL只能用到其中的那个它认为似乎是最有效的单列索引,另外两个是用不到的,也就是说还是一个全表扫描的过程。

    建立这样的组合索引,就相当于分别建立如下3种组合索引:

    • login_name, city, age
    • login_name, city
    • login_name

    为什么没有 city, age 这样的组合索引呢?因为MySQL组合索引"最左前缀匹配"的结果。简单地理解就是只从最左边的开始组合,并不是只要包含这3列的查询都会用到该组合索引。也就是说 name_city_age(login_name(16), city, age从左到右进行索引,如果没有左前索引,MySQL不会执行索引查询。

    如果索引列长度过长,这种列索引时就会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行,前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值可以创建)。

    select count(distinct(left(`login_name`, 10))) / count(*) from arctic; --这个值大于0.31就可以创建索引,distinct去重
    
    alter table `user_demo` add index index_name (login_name(10)); --增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度
    

    5. 索引的使用及注意事项

    Explain可以帮助开发人员分析SQL问题,explain 显示了MySQL如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

    使用该方法,在select语句前加上Explain就可以了。

    explain select * from user where id=1;
    

    尽量避免这些不走索引的SQL:

    select `login_name` from `user_demo` where `age` + 10 = 30; --不会走索引,因为所有索引列参与了计算
    
    select `login_name` from `user_demo` where left(`age`, 1) < 3; --不会使用索引,因为使用了函数运算,原理与上面相同
    
    select * from `user_demo` where `login_name` like "Jack%"; -- 走索引
    
    select * from `user_demo` where `login_name` like "%Jack%"; -- 不走索引
    

    正则表达式不适用索引,因此很少在SQL中看到regexp关键字

    字符串与数字比较不适用索引

    create table `a` (`a` char(10));
    
    explain select * from `a` where `a`="1";  -- 走索引
    
    explain select * from `a` where `a`=1;  -- 不走索引
    

    避免使用关键字 or

    select * from dept where dname='xxx' or loc='xx' or deptno=45;
    

    如果条件中有 or,即使其中有条件带索引也不会使用,换言之,就是要求使用的所有字段都必须建立索引,我们建议大家尽量避免使用关键字 or.

    如果MySQL估计使用全表扫描要比使用索引块,则不使用索引

    6. 索引的缺点

    索引虽然好处很多,但过多的索引可能带来相反的问题,索引也是有缺点的:

    • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERTUPDATEDELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件;
    • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在给大表上建多种组合索引,索引文件会膨胀很大.

    索引只是提高效率的一个方式,如果MySQL有大数据量的表,就要花时间研究建立最优的索引,或优化查询语句。

    7. 使用索引的技巧

    7.1 索引不会含有NULL值

    只要列中包含有NULL值,都将不会被包含在索引中,符合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。

    7.2 使用短索引

    对串列进行索引,如果可以就应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    7.3 索引列排序

    MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么 order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个列的排序,如果需要最好给这些列建复合索引。

    7.4 like语句操作

    一般情况下不鼓励使用like操作,如果非用不可,注意正确的使用方式。like '%aaa%'不会使用索引,而 like ‘aaa%’ 可以使用索引.

    7.5 不要在列上进行计算
    7.6 不使用 NOT IN<>!=操作,但是<<==>>=BETWEENIN是可以用到索引的
    7.7 索引要建立在经常进行select操作的字段上

    这是因为,如果这些列很好用到的话,那么有无索引并不能明显改变查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    7.8 索引要建立在值比较唯一的字段上
    7.9 对于那些定义为textimagebit数据类型的别不应该增加索引。因为这些列的数据要么相当大,要么取值很少
    7.10 在 wherejoin 中出现的列需要建立索引
    7.11 where的查询条件里有不等号(where column !=…),MySQL将无法使用索引
    7.12 如果where子句的查询条件里使用了函数(如:where DAY(column)=…),MySQL将无法使用索引
    7.13 在join操作中(需要从多个数据表提取数据时),MySQL只有主键和外检的数据类型相同时才使用索引,否则即使建立了索引也不会使用

    8. 查看在执行SQL查询时是否真的用到了我们所设置的索引

    加了索引过后,如何看在查询的时候是否真的用到了索引呢?

    通过 explain 关键字查看是否用到了索引,详细内容如下篇博客所示:

    MySQL如何查看SQL查询是否用到了索引?




    参考:
    MySQL 添加索引,删除索引及其用法

    展开全文
  • mysql唯一索引和unique

    千次阅读 2021-01-19 16:44:23
    最近遇到一个问题:高并发环境下,如何避免一张表里的某些列不要重复。我这样回答的(被“高并发”给骗了,傻傻地往复杂里想):1. 查找和插入这两个操作做成一个事务,对整...面试官跟我说,知不知道“唯一索引”,突...
  • MySQL 全文索引

    千次阅读 2021-09-23 13:09:16
    说到查询,日常常用的baidu,cnbing ,google等之类的网站。关系型数据库的全文索引应该也是从这些搜索引擎里摸索出来的。...MySQL中的全文索引表跟普通的表生成的文件不一样,在底层文件会生成fts
  • 一文搞懂 MySQL 索引

    千次阅读 多人点赞 2021-12-01 09:47:24
    MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。 举一个例子,平时看任何一本书,首先看到的都是目录,通过目录去查询书籍里面的内容会非常的...
  • mysql索引(七)唯一索引

    千次阅读 2021-07-19 11:17:54
    Mysql索引大概有五种类型: 普通索引(INDEX):最基本的索引,没有任何限制 唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。 主键索引(PRIMARY):它 是一种特殊的唯一索引,不...
  • Mysql 索引

    千次阅读 2021-01-19 03:17:33
    常见索引类型(实现层面)首先不谈Mysql怎么实现索引的,先马后炮一下,如果让我们来设计数据库的索引,该怎么设计?我们首先思考一下索引到底想达到什么效果?其实就是想能够实现快速查找数据的策略,所以索引的实现...
  • mysql中创建视图、索引

    千次阅读 2021-02-03 22:17:56
    数据库的三级模式两级映射:存储文件------>基本表----->视图内模式 ------->模式 ------>外模式一、视图1、什么是视图:视图是从一个或多个表导出来...数据库只存放了视图的定义,而没有存放视图...
  • mysql联合索引详解

    千次阅读 2021-01-20 13:25:24
    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最...
  • MySQL索引是如何实现的

    万次阅读 多人点赞 2022-01-04 20:54:32
    MySQL中索引分三类:B+树索引、Hash索引、全文索引。InnoDB存储引擎中用的是B+树索引。要介绍B+树索引,不得不提二叉查找树、平衡二叉树和B树这三种数据结构。B+树是从它们三个演化来的。 二叉查找树: 图中为...
  • mysql索引

    千次阅读 2021-02-08 20:04:00
    什么是索引:当你在字典查找你的名字的时候,你有两种方式。...索引的分类:| 主键索引如果一个字段为主键(primary key),则mysql会自动在该字段上建立主键索引| 唯一索引如果一个字段的值,在整张表是...
  • mysql 添加索引语句

    千次阅读 2021-01-25 12:23:22
    1.PRIMARYKEY(主键索引)mysql>ALTERTABLE`table_name`ADDPRIMARYKEY (`column`)2.UNIQUE(唯一索引)mysql>ALTERTABLE`table_name`ADDUNIQUE (`column` )3.INDEX(普通索引)mysql>ALTERTABLE`...
  • MySQL索引优化

    千次阅读 2021-02-19 09:42:44
    一、MySQL索引基础 本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容。 一、MySQL索引基础 首先,我们将从索引基础开始介绍一下什么是索引,分析...
  • MySQL之详解索引

    千次阅读 2021-03-30 19:29:22
    文章目录MySQL索引1.索引是什么?2.索引的优劣势?3.索引的分类3.1 单列索引3.2 组合索引3.3 全文索引(MyISAM,InnoDB5.6以后)3.4 空间索引4.索引的使用4.1 索引相关语句4.1.1 单列索引之普通索引4.1.2 单列索引之唯一...
  • MySQL中的B+树索引结构

    万次阅读 2021-08-08 19:11:01
    B树 B树(B-tree、B-树):是一种平衡的多路搜索树,多用于文件系统、数据库的实现。 B树的特点: 1个节点可以存储超过2个元素、可以拥有超过2个子节点;...的地址,叶子结点以上各层作为索引使用。
  • MySQL索引的概念以及七种索引类型介绍。
  • mysql 添加索引 mysql 如何创建索引

    万次阅读 2020-05-14 16:33:01
    1.添加PRIMARY KEY(主键索引mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 例:alter table yx_marketing_details add index(id); 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_...
  • Mysql联合索引生效判断

    千次阅读 2020-07-09 15:43:10
    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最...
  • Mysql索引整理总结

    万次阅读 多人点赞 2018-07-18 18:17:17
    举例说明索引:如果把数据库的某一张看成一本书,那么索引就像是书的目录,可以通过目录快速查找书指定内容的位置,对于数据库表来说,可以通过索引快速查找表的数据。 2. 索引的原理 索引一般以文件形式...
  • Mysql索引用来快速的寻找哪些特定值的记录,所以Mysql索引都是以B-树的形式保存。如果没有索引,执行Mysql查询是必须从第一条扫描,直到找到符合条件的数据,数据越多,这个操作的代价就越大。如果搜索条件的列上...
  • MySQL 降序索引

    千次阅读 2020-12-30 07:07:59
    MySQL 降序索引简介:在本教程,您将了解MySQL降序索引以及如何利用它来提高查询性能。MySQL降序索引简介降序索引是以降序存储键值的索引。在MySQL 8.0之前,您可以DESC在索引定义指定。但是,MySQL忽略了它。...
  • MySQL数据库索引及失效场景

    千次阅读 2022-01-06 14:13:11
    MySQL数据库索引及失效场景1. 索引失效概述1.1 索引的概念1.2 索引的特点1.3 索引的分类1.4 索引的使用场景2. 索引失效场景2.1 常见索引失效的9种情况2.2 索引失效场景总结3. 索引失效验证3.1 全值匹配3.2 最佳左...
  • 》一文,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的 我们一起来看一下InnoDB存储引擎索引 聚集索引...
  • MySQL中如何使用索引

    千次阅读 2021-01-15 12:03:56
    原标题:MySQL中如何使用索引 者:Airy 在数据分析之路狂奔,立志成为大咖级人物。前言学完基础的MySQL知识,以及MySQL的增删改查,我们要学习一些性能方面的东西。今天来讲一下索引(Index)。索引在关系数据库,...
  • mysql中联合索引的细节问题

    千次阅读 2021-06-30 17:06:02
    今天早上开早会的时候大家讨论到mysql的优化,其中有一个联合索引是这样的: 理所当然我们这样查询sql: (SQL_NO_CACHE:不使用缓存查询数据) 查看该sql的使用情况: 可以看到该sql是使用了联合索引的。 这个...
  • MySql索引

    千次阅读 2021-01-18 21:12:40
    索引作用相当于书的目录,可以根据目录的页码快速定位到所需的内容。索引是一种使记录有序化的技术,它从逻辑上对记录进行排序而不影响物理存储的数据。优点:加速数据检索创建唯一可以索引保证每一行数据的唯一...
  • SQL基础12:Mysql删除索引

    千次阅读 2021-01-18 18:29:37
    图片发自简书App学习此文之前,一起先来回顾下Mysql创建索引简介:在本篇教程,你将学习如何使用MYSQL DROP INDEX语法删除表已经存在的索引。一、MYSQL删除索引的语法要从表删除现有索引,可使用DROP INDEX*...
  • Mysql唯一索引和普通索引的区别,

    千次阅读 2020-07-15 16:47:31
    文章目录Mysql唯一索引和普通索引的区别,那种速度快一点,原因是啥理由说明:结论:1 普通索引2 唯一索引注意:唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。3 主索引4 外键索引5 复合索引...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 542,939
精华内容 217,175
关键字:

mysql 中索引的作用

mysql 订阅