Very important index operations in MySql

Focus on the basics—MySql index operations that cannot be forgotten

I won’t write about the basic knowledge of MySql indexes here, I don’t want to be information porters.

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: Never execute without changing 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's because index_name has not changed. Index_name is used.

ALTER  TABLE  `table_name`  ADD  INDEX index_name (`column`)

　(1). Use of indexes

Indexes need to occupy physical storage space. Improper use of indexes will not only occupy storage space, but also be counterproductive to query speed. The role of the index, so the database design must have a clear understanding of the definition of the index. 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)的索引，那么只需要修改原来的索引即可

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. NULL values ​​are not allowed

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

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