search
HomeDatabaseMysql TutorialDesign and use of mysql index
Design and use of mysql indexDec 14, 2016 pm 01:54 PM

Design and use of indexes
5.1 Overview of Mysql indexes
All MySQL column types can be indexed. Using indexes on related columns is the best way to improve the performance of SELECT operations. Define the maximum number of indexes and maximum index length for each table according to the storage engine. All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes. Most storage engines have higher limits.
In MySQL 5.1, for MyISAM and InnoDB tables, prefixes can be up to 1000 bytes long. Note that the limit of a prefix should be measured in bytes, whereas the prefix length in the CREATE TABLE statement is interpreted as a number of characters. Be sure to consider this when specifying a prefix length for columns that use multibyte character sets.
You can also create FULLTEXT indexes. The index can be used for full-text search. Only the MyISAM storage engine supports FULLTEXT indexes, and only for CHAR, VARCHAR, and TEXT columns. Indexes are always performed on the entire column, partial (prefix) indexes are not supported. Indexes can also be created on spatial column types. Only the MyISAM storage engine supports spatial types. Spatial indexes use R-trees. By default, the MEMORY (HEAP) storage engine uses hash indexes, but B-tree indexes are also supported.
5.2 Principles of index design
1. The index column to be searched is not necessarily the column to be selected. In other words, the most suitable columns for indexing are the columns that appear in the WHERE clause, or the columns specified in the join clause, rather than the columns that appear in the select list after the SELECT keyword.
2. Use unique index. Consider the distribution of values ​​in a column. Indexes work best for columns with unique values ​​and worst for columns with multiple duplicate values. For example, the column holding age has different values, making it easy to distinguish between rows.
The column used to record gender only contains "M" and "F", so indexing this column is of little use (no matter which value is searched, about half of the rows will be obtained)
3. Use a short index. If you are indexing a string, you should specify a prefix length, and this should be done whenever possible.
For example, if you have a CHAR(200) column, if most values ​​are unique within the first 10 or 20 characters, then don't index the entire column. Indexing the first 10 or 20 characters can save a lot of index space and may make queries faster. Smaller indexes involve less disk I/O, and shorter values ​​compare faster.
More importantly, for shorter key values, the blocks in the index cache can hold more key values, so MySQL can also hold more values ​​in memory. This increases the likelihood of finding the row without reading larger blocks in the index.
(Of course, some common sense should be used. For example, indexing only by the first character of the column value is unlikely to be of much benefit, because there will not be many different values ​​in this index.)
4. Use the leftmost prefix. When you create an n-column index, you actually create n indexes that MySQL can use.
A multi-column index can function as several indexes because the leftmost set of columns in the index can be used to match rows. Such a set of columns is called a leftmost prefix. (This is different from indexing the prefix of a column, which uses the first n characters of the column as the index value.)
5. Don’t over-index. Don't think that "the more indexes, the better". It's wrong to use indexes for everything. Each additional index takes up additional disk space and reduces the performance of write operations, which we have already introduced. When the contents of the table are modified, the index must be updated and sometimes may need to be reconstructed. Therefore, the more indexes, the longer it takes. If you have an index that is rarely or never used, it will unnecessarily slow down modifications to the table.
In addition, MySQL must consider each index when generating an execution plan, which also takes time. Creating redundant indexes creates more work for query optimization. Too many indexes may also prevent MySQL from selecting the best index to use. Keeping only the required indexes facilitates query optimization. If you want to add an index to an already indexed table, you should consider whether the index to be added is the leftmost index of an existing multi-column index. If so, don't bother adding this index because it already exists.
6. Consider the types of comparisons you make on columns. Indexes can be used with the " = ", " >" and BETWEEN operations. Indexes are also used in LIKE operations when the pattern has a literal prefix. If a column is only used for other types of operations (such as STRCMP()), there is no value in indexing it.
5.3 btree index and hash index
For BTREE and HASH index, when using =, , IN, IS NULL or IS NOT NULL operators, the comparison relationship between key elements and constant values ​​corresponds to a range condition. Hash indexes have some additional features: they are only used for equality comparisons using the = or operators (but fast). The optimizer cannot use hash indexes to speed up ORDER BY operations.
(This type of index cannot be used to search for the next entry in sequence). MySQL cannot determine approximately how many rows there are between two values ​​(this is used by the range optimizer to determine which index to use). If you change a MyISAM table to a hash-indexed MEMORY table, some queries will be affected. Only the entire keyword can be used to search a row. (With a B-tree index, the leftmost prefix of any key can be used to find the row).
For BTREE index, when using >, =, , or LIKE 'pattern' (where 'pattern' does not start with a wildcard) operator, The comparison relationship between key elements and constant values ​​corresponds to a range condition.
"Constant value" refers to: a constant in a query string, a const in the same join or a column in the system table, the result of an uncorrelated subquery, an expression composed entirely of subexpressions of the previous type
Here are some Examples of queries with range conditions in the WHERE clause:
The following range queries are applicable to btree indexes and hash indexes
SELECT * FROM t1WHEREkey_col = 1ORkey_col IN (15,18,20);
The following range queries are applicable to btree indexes
SELECT * FROM t1WHERE key_col > A row with a specific value in the column. Without using an index, MySQL must start at record 1 and read through the entire table until it finds the relevant row. The larger the table, the more time it takes. If the queried column in the table has an index, MySQL can quickly get to a point where it searches the middle of the data file without having to look at all the data. For example, if a table has 1000 rows, this is at least 100 times faster than sequential reading. Note that if you need to access a large portion of the rows, sequential reading is much faster because at that point we avoid a disk seek.
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX and FULLTEXT) are stored in B-trees. Only spatial column type indexes use R-tree, and MEMORY tables also support hash indexes.
For a detailed explanation of the circumstances under which the database will use indexes and the circumstances under which the database will not use indexes, please refer to the relevant chapters of the optimization chapter, which will not be repeated here.

If you want to get more related articles, please pay attention to php Chinese website (www.php.cn)!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
mysql索引失效的几种情况mysql索引失效的几种情况Feb 21, 2024 pm 04:23 PM

常见情况:1、使用函数或运算;2、隐式类型转换;3、使用不等于(!=或<>);4、使用LIKE操作符,并以通配符开头;5、OR条件;6、NULL值;7、索引选择性低;8、复合索引的最左前缀原则;9、优化器决策;10、FORCE INDEX和IGNORE INDEX。

Java API 开发中的 RESTful 接口设计Java API 开发中的 RESTful 接口设计Jun 18, 2023 am 08:31 AM

随着互联网技术的发展,RESTful风格的API设计成为了最为流行的一种设计方式。而Java作为一种主要的编程语言,也越来越多地在RESTful接口的开发中扮演着重要的角色。在JavaAPI开发中,如何设计出优秀的RESTful接口,成为了一个需要我们深入思考的问题。RESTful接口的基本原则首先,我们需要了解RESTful接口的基本原则。REST即Re

Go语言中的面向服务架构设计Go语言中的面向服务架构设计Jun 04, 2023 am 09:51 AM

随着互联网技术的不断发展,面向服务架构(SOA)的理念越来越受到人们的重视。在这个背景下,Go语言作为一种高效、可靠的编程语言,也逐渐成为了很多企业与开发者实现SOA的首选语言。本文将深入探讨Go语言中的面向服务架构设计。一、SOA简介面向服务架构是一种软件设计的架构风格,它将复杂的系统拆分成多个相互独立、可复用的服务,每个服务都有独立的功能实现,并使用标准

自动驾驶汽车的软件升级技术管理与监管策略分析自动驾驶汽车的软件升级技术管理与监管策略分析May 16, 2023 am 08:40 AM

随着智能车辆在网联化、智能化及架构技术的发展,汽车无论是在固件还是软件上都已经不可逆转的需要进行软件迭代升级。要求在汽车生命周期内会不断的基于汽车OTA能力为整车提供软件升级、固件升级、售后服务等服务能力,可以说,汽车的智能化更迭对于OTA升级能力已经成为不可或缺的主流趋势。本文章将针对自动驾驶汽车的软件升级现状需求及监管要求等进行详细的描述。意在帮助读者整体了解自动驾驶中的软件升级过程原理、准入要求及其应对策略。1整车软件升级技术优势首先,软件定义汽车推动了整车软件升级技术的发展与应用,通过整

如何使用Go语言进行代码安全性设计如何使用Go语言进行代码安全性设计Aug 02, 2023 pm 05:29 PM

如何使用Go语言进行代码安全性设计在当今互联网时代,代码安全性是一项至关重要的任务。无论是为了保护用户的隐私还是避免遭受黑客攻击,代码安全性都是必不可少的。Go语言作为一种现代化的编程语言,提供了许多功能和工具,可以帮助我们进行代码安全性设计。本文将介绍一些在Go语言中实现代码安全性的最佳实践,并提供相应的代码示例。输入验证输入验证是代码安全性的第一道防线。

mysql索引什么情况下会失效mysql索引什么情况下会失效Aug 09, 2023 pm 03:38 PM

mysql索引在不使用索引列进行查询、数据类型不匹配、前缀索引的使用不当、使用函数或表达式进行查询、索引列的顺序不正确、数据更新频繁和索引过多或过少情况下会失效。1、不使用索引列进行查询,为了避免这种情况,应该在查询中使用适当的索引列;2、数据类型不匹配,在设计表结构时,应该确保索引列和查询的数据类型匹配;3、前缀索引的使用不当,可使用前缀索引。

RESTful API设计及其实现方法RESTful API设计及其实现方法Jun 22, 2023 pm 04:07 PM

RESTfulAPI是目前Web架构中较为常用的一种API设计风格,它的设计理念是基于HTTP协议的标准方法来完成Web资源的表示与交互。在实现过程中,RESTfulAPI遵循一系列规则和约束,包括可缓存、服务器-客户端分离、无状态性等,这些规则保证了API的可维护性、扩展性、安全性以及易用性。接下来,本文将详细介绍RESTfulAPI的设计及其实现方

Redis应用实例分享:文章点赞功能设计Redis应用实例分享:文章点赞功能设计Jun 20, 2023 am 09:30 AM

在互联网时代,文章阅读与分享已经成为人们日常生活中必不可少的一部分。然而,对于文章的点赞与收藏功能来说,用户体验体现的非常关键。而Redis作为一个高性能的键值存储数据库,在文章点赞与收藏功能的实现中有很大的优势。本文将分享一个基于Redis实现的文章点赞功能设计。功能设计文章点赞功能的设计过程中,需要考虑到许多因素。首先,需要将点赞接口暴露给用户,用户可随

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.