- 浏览: 50334 次
- 性别:
文章分类
- 全部博客 (103)
- 一致性哈希算法 (1)
- 云计算 (2)
- Cassandra学习 (2)
- Java网络通信与笔记 (14)
- ZooKeeper学习 (1)
- HBase学习 (1)
- Sqoop学习 (1)
- Java网页开发之 (2)
- Java网络通信框架 (5)
- (memcached)分布式内存对象缓存系统 (1)
- Redis学习 (5)
- Shell学习 (14)
- Linux学习 (10)
- MySQL优化 (17)
- C++ (7)
- HTML5 (5)
- Android学习 (5)
- 网络 (2)
- Node.js (1)
- D3.js (1)
- R语言学习 (3)
- Spark (1)
- CAN协议 (2)
- 解决方案 (0)
最新评论
Mysql索引概述
所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。
在MySQL 5.1中,对于MyISAM和InnoDB表,前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。
还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。默认情况MEMORY(HEAP)存储引擎使用hash索引,但也支持B-树索引。
设计索引的原则
1) 搜索的索引列,不一定是所要选择的列。
换句话说,最适合索引的列是出现在WHERE 子句中的列,或连接子句中指定的列,而不是出现在SELECT 关键字后的选择列表中的列。
2) 使用惟一索引。
考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分 各行。而用来记录性别的列,只含有“ M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。
3) 使用短索引。
如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200) 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不 同的值。)
4) 利用最左前缀。
在创建一个n 列的索引时,实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n 个字符作为索引值。)
5) 不要过度索引。
不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍 过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表 的修改速度。此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是,则就不要费力去增加这个索引了,因为已经有了。
6) 考虑在列上进行的比较类型。
索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时(如STRCMP( )),对其进行索引没有价值。
btree索引与hash索引
对于BTREE和HASH索引,当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时,关键元素与常量值的比较关系对应一个范围条件。Hash索引还有一些其它特征:它们只用于使用=或<=>操作符的等式比较(但很快)。优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目)。MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表,会影响一些查询。只能使用整个关键字来搜索一行。(用B-树索引,任何关键字的最左面的前缀可用来找到行)。
对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件。“常量值”系指:查询字符串中的常量、同一联接中的const或system表中的列、无关联子查询的结果、完全从前面类型的子表达式组成的表达式。
下面是一些WHERE子句中有范围条件的查询的例子。
下列范围查询适用于 btree索引和hash索引:
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列范围查询适用于btree索引
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';
Mysql如何使用索引
索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意如果你需要访问大部分行,顺序读取要快得多,因为此时我们避免磁盘搜索。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。
关于什么情况下数据库会使用索引以及什么情况下数据库不会使用索引的详细解释请看优化篇的相关章节,这里就不再累述。
所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。
在MySQL 5.1中,对于MyISAM和InnoDB表,前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。
还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。默认情况MEMORY(HEAP)存储引擎使用hash索引,但也支持B-树索引。
设计索引的原则
1) 搜索的索引列,不一定是所要选择的列。
换句话说,最适合索引的列是出现在WHERE 子句中的列,或连接子句中指定的列,而不是出现在SELECT 关键字后的选择列表中的列。
2) 使用惟一索引。
考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分 各行。而用来记录性别的列,只含有“ M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。
3) 使用短索引。
如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200) 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不 同的值。)
4) 利用最左前缀。
在创建一个n 列的索引时,实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n 个字符作为索引值。)
5) 不要过度索引。
不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍 过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表 的修改速度。此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是,则就不要费力去增加这个索引了,因为已经有了。
6) 考虑在列上进行的比较类型。
索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时(如STRCMP( )),对其进行索引没有价值。
btree索引与hash索引
对于BTREE和HASH索引,当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时,关键元素与常量值的比较关系对应一个范围条件。Hash索引还有一些其它特征:它们只用于使用=或<=>操作符的等式比较(但很快)。优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目)。MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表,会影响一些查询。只能使用整个关键字来搜索一行。(用B-树索引,任何关键字的最左面的前缀可用来找到行)。
对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件。“常量值”系指:查询字符串中的常量、同一联接中的const或system表中的列、无关联子查询的结果、完全从前面类型的子表达式组成的表达式。
下面是一些WHERE子句中有范围条件的查询的例子。
下列范围查询适用于 btree索引和hash索引:
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列范围查询适用于btree索引
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';
Mysql如何使用索引
索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意如果你需要访问大部分行,顺序读取要快得多,因为此时我们避免磁盘搜索。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。
关于什么情况下数据库会使用索引以及什么情况下数据库不会使用索引的详细解释请看优化篇的相关章节,这里就不再累述。
发表评论
-
基于Hash算法的Mysql分表处理
2015-10-29 00:58 584下面为您介绍的Mysql ... -
数据库中表散列
2015-10-29 00:52 591数据库中的散列法 ... -
mysql大数据高并发处理
2015-10-26 22:00 428一、数据库结构的设计 ... -
优化SQL查询:如何写出高性能SQL语句
2015-10-17 17:41 447优化SQL查询:如何写出高性能SQL语句 这个问题 ... -
MySqL性能优化
2015-09-06 21:26 0I 硬件配置优化 Ø CPU选择:多核 ... -
Mysq日志管理(错误日志、查询日志、慢查询日志)
2015-09-06 21:01 0错误日志 记录内容:包含了当mysqld启动和停止时,以及服 ... -
MySql均衡负载
2015-09-06 21:01 01) 利用mysql 复制分流查询操作 利用mysql的主从复 ... -
如何减少对Mysql的访问以优化SQL语句
2015-09-06 21:00 0避免对同一数据做重复 ... -
使用mysql连接池提高性能
2015-09-06 20:59 0对于访问数据库来说,建立连接的代价比较昂贵,因此,我们有必要建 ... -
Mysql锁的优化
2015-09-06 20:59 0获取锁等待情况 可以 ... -
Mysql数据表的优化
2015-09-26 22:26 435优化表的数据类型 表需要使用何种数据类型,是需要根据应用来判 ... -
调整Mysql中insert、update、delete的顺序来以提高效率
2015-09-26 22:26 621MySQL还允许改变语句调度的优先级,它可以使来自多个客户端的 ... -
Mysql join语句的优化
2015-09-26 22:25 348Mysql4.1开始支持SQL的子查询。这个技术可以使用SEL ... -
Mysql order by语句的优化
2015-09-19 11:56 404在某些情况中,MySQL可以使用一个索引来满足ORDER BY ... -
Mysql group by语句的优化
2015-09-13 16:01 387默认情况下,MySQL排序所有GROUP BY col1, c ... -
Mysql insert语句的优化
2015-09-08 22:59 3471) 如果你同时从同一客户插入很多行,使用多个值表的INSER ... -
Mysql大量插入数据时SQL语句的优化
2015-09-11 22:00 4751) 对于Myisam类型的表,可以通过以下方式快速的导入大量 ... -
Mysql索引优化教程
2015-09-08 22:59 418索引的存储分类 myisam表的数据文件和索引文件是自动分开 ... -
Mysql下优化SQL的一般步骤
2015-09-07 11:50 350通过show status和应用特点了解各种SQL的执行频率 ... -
Mysql SQL Mode详解
2015-09-07 11:50 472Mysql SQL Mode简介 MySQL服务器能够工作在 ...
相关推荐
总之,MySQL索引优化是一项涉及广泛的知识,包括但不限于索引类型选择、索引维护、存储过程和触发器的合理运用。通过深入学习和实践,我们可以有效地提升数据库的运行效率,确保系统的稳定和快速响应。这个课件...
### MySQL索引分析和优化 #### 一、索引的重要性及原理 索引在数据库管理中扮演着极其重要的角色,特别是在提高数据检索速度方面。**MySQL索引**本质上是用来加快数据检索过程的一种数据结构,类似于书籍中的目录...
MySQL索引优化是数据库性能提升的关键环节,本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先,创建了一个名为`employees`的员工记录表,其中包含`id`(主键)、`name`、`age`、`position`和`hire_...
"MySQL索引分析及优化" 索引是数据库中提高速度的一个关键因素。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。索引的使用可以大幅度地提高查询速度,减少数据库的...
### MySQL的索引优化 ...通过上述内容可以看出,合理使用和优化MySQL索引对于提升数据库性能至关重要。开发者应该根据具体的应用场景来选择合适的索引策略,并定期评估和调整索引设置,以确保系统的高效运行。
一、MySQL索引 1. 索引类型:MySQL支持多种索引类型,包括B-Tree(默认索引类型)、Hash、Full-text(全文索引)以及R-tree(空间数据索引)。B-Tree索引适用于范围查询,而Hash索引适用于等值查询,Full-text索引...
《Oracle与MySQL数据库索引设计与优化》这本书深入探讨了两个主流关系型数据库管理系统——Oracle和MySQL中的索引设计和优化策略。索引是数据库性能的关键因素,它们能够加速数据检索,提高系统效率,尤其在大数据量...
MySQL索引原理及慢查询优化是数据库管理中的重要主题,尤其是在高并发、大数据量的...通过合理的索引设计、查询优化以及对磁盘IO的理解,可以有效避免和解决慢查询问题,从而让MySQL在各种应用场景中发挥出最佳性能。
MySQL索引是数据库管理系统中用于加速数据检索的关键组件。它们的工作原理类似于书籍的索引,允许数据库系统快速定位和访问所...正确使用和维护索引可以显著提高查询速度,减少资源消耗,从而提升整个应用的响应时间。
- **监控和分析**:定期检查EXPLAIN计划,分析索引的使用情况,使用MySQL的性能分析工具如pt-query-digest来识别慢查询并优化。 - **InnoDB存储引擎的索引优化**:InnoDB支持行级锁定,其主键索引是聚簇索引,其他...
Mysql索引优化案例 在对数据库进行操作时,尤其是在处理包含大量数据的表时,查询的性能问题是一个常见且重要的话题。在实际工作中,对于Mysql数据库进行索引优化是提高查询效率、减少查询时间的重要手段。本案例将...
Mysql的索引及优化策略,个人感觉还不错
MySQL 数据库是一个广泛使用的开源关系型数据库管理系统,其性能很大程度上取决于存储引擎和索引的选取与使用。本文将深入探讨 MySQL 中的存储引擎和索引技术,帮助优化数据库性能。 首先,MySQL 提供了多种存储...
理解MySQL索引的原理及其不同类型,结合具体的应用场景,合理设计和优化索引,是提升数据库性能的关键。通过对索引的深入理解和有效管理,可以显著提高数据检索速度,降低服务器负载,从而提升应用程序的整体响应性...
### MySQL索引最佳实践 #### 理解索引的重要性 在数据库管理中,索引是一种数据结构,用于提高查询速度。它对于开发者和数据库管理员(DBA)来说至关重要。索引选择不当可能会导致生产环境中的诸多问题。尽管索引...
MySQL索引分析和优化是数据库管理中的重要环节,它直接影响到数据查询的速度和数据库的性能。索引可以被看作是数据库中的一种特殊文件,它按照特定的规则组织,允许数据库快速定位到所需的数据行。 首先,我们来看...
### MySQL索引最佳实践 ...通过以上内容的学习,我们可以了解到MySQL索引的重要性和不同类型的索引在实际应用中的优势与局限性。正确地理解和运用这些知识可以帮助我们更高效地管理和优化数据库性能。
MySQL索引和优化是数据库管理中的关键环节,它直接影响到数据查询的速度和效率。本教程将详细介绍MySQL中的索引类型及其优化策略。 首先,我们需要理解什么是索引。索引是数据库为了快速查找特定记录而创建的一种...