Home > Database > Mysql Tutorial > MySql index operation

MySql index operation

一个新手
Release: 2017-09-30 10:00:39
Original
930 people have browsed it

I won’t write about the basic knowledge of MySql index here. I don’t want to be a porter of information.

Skill sharing: Workbench is an ER/database modeling tool specially designed for MySQL. In addition to managing the database, it is also a powerful tool for generating Sql statements. Reasonable use will bring twice the result with half the effort. Of course, the premise is that we understand the basic knowledge.

I will first sort out some Sql statements for index operations. The reason for sorting them out is that when I encountered a problem, I found the reply in Baidu. Basically Everything above is wrong, someone needs to correct it.  

1. Multiple primary key (PRIMARY) deletion

 Hypothetical scenario: You have defined multiple primary keys (PRIMARY) in the table. For example: id is an auto-incrementing primary key, userCode is also defined as a primary key, and userAge is also defined as a primary key. If you plan to keep only one ID and delete the other two, the corresponding Sql statement is as follows:

 Explanation: For the specified Modify the table, delete the PRIMARY KEY, and add the id as the PRIMARY KEY.

ALTER TABLE `表名` 
DROP PRIMARY KEY,ADD PRIMARY KEY (`id`);
Copy after login

 

2. Add a normal index

 Warning: Don’t Execute if you don't change it. table_name is not the focus of my emphasis. What I want to say is index_name. You must reasonably standardize the naming of the index, such as index_field name. If you have a better one, you can leave me a message.

Otherwise you will report an error: Duplicate key error in MySQL (Duplicate key name '' ). To be sure, it is because index_name has not changed. Index_name is used.

ALTER  TABLE  `table_name`  ADD  INDEX index_name (`column`)
Copy after login
 

3. About index usage specifications 

(1).Use of index

Indexes need to occupy physical storage space. Improper use of indexes will not only occupy storage space, but also have a counterproductive effect on query speed. Therefore, the definition of indexes must be very clear in the database design. understanding. Several major principles for building an index

1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
Copy after login

 

(2). Index type

 1. Ordinary index

  The most basic index without any restrictions


2. Unique index

The value of the index column must be unique, but null values ​​are allowed


3. Primary key index

The primary key index is a special unique index, and null values ​​are not allowed


4. Single-column index

A single multi-column index (combined index) is more efficient than multiple single-column indexes


5. Leftmost Prefixing: Multi-column index

The above is the detailed content of MySql index operation. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
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
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template