Shulou(Shulou.com)06/01 Report--

Principles of index design

1. Leftmost prefix principle index column_name (10) indexes only the first ten bytes

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

Mysql > alter table e engine=myisam

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

The total length of all index columns cannot be more than 1000 or 3072. The engine is different and the length limit is different.

Innodb: the maximum length of the index column (version 5.7 is 3072bytes, which is the number of bytes, not characters)

Myisam: the maximum index column length is 1000

two。 Minimum principle (an index can support multiple SQL, a table is limited to 5)

two。 The principle of being at the top of the list with good selectivity

Put: primary,unique notnull in front

For: operation or between and or like, etc., after it.

Index (a _

A:primary key

Where axi1 and b like'% f% 'and cantilever 123 followed by b and c will not be able to use index ref or eq_ref

3. Frequently update columns as little as possible in the index

4. Use clustered indexes and cover index more often

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.