B+树挑选索引(1)---mysql从入门到精通（二十二)
上篇文章介绍了用索引列分组也可以提高效率，及其回表的代价，回表是二级索引+回表查询，如果回表数据量太庞大，mysql优化器就会采用全表扫描查询，而覆盖索引不会回表
如何挑选索引
在了解索引的强大后，我们如何使用索引呢？
在搜索、排序、分组的列创建索引
也就是在where语句后面的列，连接子句中的连接列，或者出现在order by 、group by子句中的列需要创建索引，而select查询列就不需要。比如
Select birthday,country from person_info where name = ‘’;这个sql语句里面birthday 和country不需要创建索引，而name则需要创建索引。
考虑列的基数
列的基数是指某一列中不重复的数据，比如name列有3，2，3，5，5，3，2，3，3，虽然数据有不少，但是他的不同数据只有三，所以基数是3。在数据记录中，列的基数越大，说明数据越分散，列的基数越小，说明数据越集中。假设列的基数是1，那当前列所有数据都一样，就无法排序，建立索引是没有意义的，如果建立二级索引，还会因为大量数据的回表操作而损耗性能，所以只有列基数越大的时候，才适合建立索引。
索引列的类型尽量小
我们在定义表结构有显示的指定列类型，我们以整数类型为例，有TINYINT,MEDIUMINT,INT,BIGINT这么几种，他们占用的内存依次递增，我们这里指的类型大小，就是建立该列的数据范围，尽量小化选择，这是因为：1）数据类型越小，查询速度越快（CPU层次的东西）。2）数据量越小，索引占用的存储空间越小，在一个数据页放下更多的记录，从而减少磁盘I/O带来的性能损耗，减少查询更多数据页带来的性能消耗。
这种不光对二级索引适用，对主键也同样适用，因为二级索引的叶子节点都是有主键的，主键的类型越小，占用的内存就越小。
索引字符串前缀
我们知道字符串由若干个字符组成的，我们用utf8组成的话需要占用1~3个字节，1)空间性：b+树需要把列的字符串全部存储起来，字符串越长，b+树叶子节点占用的空间越大。2）时间性：字符串越长，排序比较的时候，一个字符一个字符比较能耗费的时间越长。
我们之前说过，对于字符串的列，是一个字符一个字符比较排序的，所以索引设计者，考虑到空间和时间，只对字符串前几个指定字符进行存储和排序，这样也能相对定位到所在的位子，在用对应的主键id去聚簇索引的b+树查询所有值。写sql的时候我们可以这么写，创建name为100的字符串范围，但索引指定只指定name前10的个字符。
mysql> create table person_info2(
-> id int not null auto_increment,
-> name varchar(100) not null,
-> birthday date not null,
-> phone char(11) not null,
-> country varchar(100) not null,
-> primary key (id),
-> key idx_name_birthday_phone (name(10),birthday,phone)
-> );
Query OK, 0 rows affected (0.04 sec)
//也可以给长字符串列创建hash索引：
mysql> create index hash_name using hash on person_info2(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from person_info2;
+--------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| person_info2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| person_info2 | 1 | idx_name_birthday_phone | 1 | name | A | 0 | 10 | NULL | | BTREE | | |
| person_info2 | 1 | idx_name_birthday_phone | 2 | birthday | A | 0 | NULL | NULL | | BTREE | | |
| person_info2 | 1 | idx_name_birthday_phone | 3 | phone | A | 0 | NULL | NULL | | BTREE | | |
| person_info2 | 1 | hash_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+--------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+相关文章