MySql index operation

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`);

　

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`)

　

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)的索引，那么只需要修改原来的索引即可

　

(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!