How to Create indexes

Create index when creating table:

KEY word INDEX can replace, with the KEY INDEX in front can add INDEX type UNIQUE | FULLTEXT | SPATIAL | NORMAL, default is NORMAL

CREATE TABLE `student` (
  `id` bigint NOT NULL,
  `name` varchar(30),
  PRIMARY KEY (`id`),
  INDEX `idx_name` (`name`)  Add index idx_name to name field
)
Copy the code


Add index to existing table

ALTER TABLE student ADD INDEX idx_name (`name`)
Copy the code


Considerations for index creation

Select the appropriate field to create the index

Not every field is suitable for index creation, and index creation is disk space consuming. More indexes is not always better

InnoDB tables can contain up to 1017 columns and create up to 64 secondary indexes

Portal: >>>>>>> Official document <<<<<<<<

1. Do not create indexes for null fields

For Mysql, columns with null values are difficult to optimize for queries. If null values are unavoidable for frequent queries, consider using shorter strings such as 0,1, or null characters instead. Indexes that can have NULL fields are much less efficient because they complicate indexes, index statistics, and comparisons

2. Select the large discrimination column as the index

For example, if a student table has a field named “sex” in it, it contains about 50% male and 50% female, so even if you create an index, it is better not to create an index at all

MySQL has a query optimizer. When the query optimizer finds a high percentage of a value in a table’s rows, it generally ignores the index and performs a full table scan

The usual percentage line is “30 per cent “. The query will abandon the index when the amount of data matched exceeds a certain limit (this is one of the scenarios where index failure occurs)

3. Frequently queried fields are used as search conditions

All of the above fields can be considered indexed by design

For example, for the student table and grade table, suppose that the following statement is executed frequently:

-- Ready to go to happiness Community after work, blasting the students who failed
select name, age from student a
left join grade b on a.id = b.student_id 
where a.address like '% Happy district %' and b.grade < 60
Copy the code

For the student table, you can set indexes for the fields name, age, address, and birthday. For the grade table, you can set indexes for the columns student_id to greatly improve the speed of querying the connected tables

4. Used togroup by.order byThe field of

Create indexes for the fields after order BY, so that there is no need to sort again when querying, because we have known that the records in B+ tree are sorted after the establishment of indexes

Group by and Order BY are actually similar, so we put them together

Because in thegroup byWhen also want to first according togroup byThe following fields are sorted and then aggregated

If the group by columns are not sorted, then MySQL needs to sort them first. This will create a temporary table, which will be sorted, and then aggregate the temporary table. MySQL does not need to sort, and will not generate temporary tables

5. Be careful when creating indexes for frequently updated fields

In SQL optimization, reasonable indexing is a basic operation. While indexes can improve query efficiency, they can also be costly, taking up disk space and reducing the speed of additions, deletions, and updates. If a field is not frequently queried, but is frequently updated, then the field should not be indexed


Other suggestions for creating indexes

1. Try to create a union index instead of a single column index

Because indexes take up disk space, you can simply assume that each index corresponds to a B+ tree. If there are too many fields and indexes in a table, when the data volume of the table reaches a certain volume, the indexes occupy a large amount of space, and it takes a lot of time to modify the indexes. If multiple fields are in a federated index, large disk space is saved and data modification efficiency is improved

2. Avoid redundant indexes

A redundant index means that indexes have the same function. If indexes (a, b) can match indexes (a), then indexes (A) are redundant indexes. For example, (name,city) and (name) are redundant indexes. Queries that can match the former are sure to match the latter. In most cases, you should try to extend existing indexes rather than create new ones

3. Consider using prefix indexes instead of normal indexes on string fields

Prefix indexes are limited to string types and take up less space than normal indexes, so consider using prefix indexes instead of normal indexes


Considerations for using indexes

Indexes are not created and left alone; good indexes require long-term maintenance by developers

First of all, indexes are very effective for medium to large tables. However, the maintenance cost of very large tables is very high, which is not suitable for creating indexes. For small tables, the time to query all the data may be shorter than the time to traverse the indexes, and there is no need to create indexes

It is also necessary to delete indexes that have not been used for a long time. Unused indexes not only occupy disk space, but also cause unnecessary performance cost. After Mysql5.7, you can query sys. schema_unused_INDEXES to obtain the indexes that have not been used for a long time


Several cases in which an index can be invalidated

1. Perform a function or operation on the index column in the query condition

If a function is performed on a field that is already indexed, then the index is no longer available

Since MySQL maintains a B+ tree for the index based on the original data from the field, if you add a function to the process of using the index, MySQL will not consider this to be the original field, and it will not be indexed

If a function must be indexed, you can bring the function with you when you create the index

Ex. :

SELECT * FROM student WHERE round(age) = 2;
Copy the code

Idx_age = idx_age = idX_age = idX_age = idX_age = idX_age = idX_age

create index idx_age_round on student(round(age)); 
Copy the code

Second, an operation performed on the index column in WHERE also invalidates the index

In 2.likeConditions start with %

select * from student where name like '%bc%'
Copy the code

In the case of the above statement, the index is not valid. For the index to be valid, you need to remove the % prefix

3. Useorconditions

If you want an OR condition to be indexed, you need to index all the fields in the OR condition

4. Left-most matching principle

The left-most matching rule means that if your SQL statement uses the left-most index in a union index, the SQL statement can use that index to match, and will stop matching when a range query (>, <, between, like) is performed


Reference

Nuggets: I almost always ask in an interview: What are your principles for indexing? How to avoid index failure?

JavaGuide: Some suggestions for using indexes