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 indexIndexes 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
3. Primary key index
4. Single-column index
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!