If you can ensure that business code does not write duplicate data, you can move on. If the business is not guaranteed, a unique index must be created.
About Query ability
There is no significant difference in query power between a normal index and a unique index. For example, select id from T where k=5. 1. After a common index finds the first record (5,500), the index searches for the next record until the first record that does not meet k=5. 2. For unique indexes, since indexes define uniqueness, the search is stopped when the first record that meets the condition is found.
InnoDB data is read and written according to data pages, each data page size is 16KB by default. For a normal index, finding a record at k=5, where the data page of the record is in memory, is nothing more than an attempt to find and determine the next record. Of course, if k=5 happens to be on the last row of the data page, then you have to read the next data page, which is a little bit more complicated.
About change buffer
When a data page needs to be updated, it is updated directly if the data page is in memory. If the data page is on disk, InnoDB will cache these updates in the Change buffer so that the data page does not need to be read from disk. The next time the query needs to access the data page, the data page is read into memory, and the change Buffer operation on the page is performed. Advantages of Change Buffer: Update operations are recorded to change Buffer first, which reduces disk reads and improves statement execution speed. If data is read into the memory, the buffer pool will be occupied. Use change buffer to avoid occupying the memory and improve the memory utilization. 2. The main purpose of change buffer is to cache recorded changes before a data page merge. The more records in change buffer before a data page merge, If the update mode of a service is query immediately after writing, this will not reduce I/O access, but increase the maintenance cost of the Change buffer.
About Write ability (based on Change Buffer)
Normal indexes work well with change Buffer when they do not need to be read immediately, so they are recommended in most situations. It is recommended to turn off the change Buffer if the update is followed immediately by this record disconnecting. The redo log saves THE I/O consumption of random disk writes, and the change Buffer saves the I/O consumption of random disk reads.