索引的运行原理b+树（mysqlInnoDB的底层数据模型）
主键索引的模型（聚簇索引）：
类型
- 唯一索引。对于单列索引，不允许有重复值出现；对于多列（复合）索引，不允许出现重复的组合值。
- 常规（非唯一性）索引。获得索引的好处，但会出现重复值。
- fulltext索引。用于全文检索，本仅限于MyISAM表，但MySQL5.6.4之后，InnoDB也支持。
- spatial索引。只适用于包含空间值得MyISAM表
- hash索引。memory表的默认索引类型
构建高性能索引的策略
1.索引不能时表达式或函数的一部分
列：查询member表中上任日期离今天不到30的人数
| member | CREATE TABLE `member` (`member_id` int(10) unsigned NOT NULL AUTO_INCREMENT,`last_name` varchar(20) NOT NULL,`first_name` varchar(20) NOT NULL,`suffix` varchar(5) DEFAULT NULL,`expiration` date DEFAULT NULL,`email` varchar(100) DEFAULT NULL,`street` varchar(50) DEFAULT NULL,`city` varchar(50) DEFAULT NULL,`state` varchar(2) DEFAULT NULL,`zip` varchar(10) DEFAULT NULL,`phone` varchar(20) DEFAULT NULL,`interests` varchar(255) DEFAULT NULL,PRIMARY KEY (`member_id`),KEY `expiration` (`expiration`)) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8 |1 row in set (0.02 sec)# 随机修改日期为今年度update member set expiration = concat('2021','-',floor(1+rand()*12),'-',floor(1+rand()*30));
当使用索引列参与时间函数的计算时：
mysql> select * from member where expiration>=curdate() and to_days(expiration)-to_days(curdate())<=30;
+-----------+-----------+------------+--------+------------+---------------------------+----------------------+------------+-------+-------+--------------+-------------------------------------------------------------+
| member_id | last_name | first_name | suffix | expiration | email | street | city | state | zip | phone | interests |
+-----------+-----------+------------+--------+------------+---------------------------+----------------------+------------+-------+-------+--------------+-------------------------------------------------------------+
| 6 | Eliason | Jessica | NULL | 2021-07-02 | jessica.eliason@pluto.com | 60 Century Av. | Osborne | KS | 63198 | 896-268-0569 | World War I,Korean War |
| 47 | Bookstaff | Barbara | NULL | 2021-07-11 | bookstaff.b@earth.com | 289 Lancashier Ct. | Durango | CO | 17762 | 175-857-5726 | Civil War,Industrial revolution |
| 72 | Walton | Philp | NULL | 2021-07-06 | NULL | 8527 Manitowoc Pkwy. | Lincoln | NE | 68799 | 112-725-0105 | Social Security,Founding fathers |
| 81 | Brooks | Carl | NULL | 2021-06-16 | brooks_carl@jupiter.com | 8755 Dapin Rd. | Sarasota | FL | 19735 | 514-906-3111 | War of 1812,Vietnam War,Civil Rights,World War II,Gold rush |
| 102 | Clark | Dale | NULL | 2021-06-23 | NULL | 958 Sigmont Blvd. | Fort Worth | TX | 83720 | 365-784-5634 | Vietnam War,Civil Rights,Roosevelt,Lincoln |
+-----------+-----------+------------+--------+------------+---------------------------+----------------------+------------+-------+-------+--------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> explain select * from member where expiration>=curdate() and to_days(expiration)-to_days(curdate())<=30;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | member | NULL | ALL | expiration | NULL | NULL | NULL | 102 | 51.96 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
此时的type为all，此时的MySQL并没用使用索引搜索
索引列不参与时间日期函数计算：
mysql> explain select * from member where expiration between curdate() and adddate(curdate(),30);
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | member | NULL | range | expiration | expiration | 4 | NULL | 5 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
我们发现此时的type为range，此时的搜索用到了索引列的搜索<br />索引列参与表达式时
mysql> explain select * from member where expiration<curdate();
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | member | NULL | ALL | expiration | NULL | NULL | NULL | 102 | 48.04 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from member where expiration-curdate()<=0;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | member | NULL | ALL | NULL | NULL | NULL | NULL | 102 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
2.前缀索引的选择
CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
-- Repeat the next statement five times:
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city demo;
-- Now randomize the distribution (inefficiently but convenientiy):
UPDATE sakila.city_demo
SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);
#完整性列表
select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;
select count(*) as cnt,left(city,3) as pref from sakila.city_demo group by pref order by cnt desc limit 10;
#接近完整列
select count(*) as cnt,left(city,7) as pref from sakila.city_demo group by pref order by cnt desc limit 10;
#计算完整列的选择性
select count(distinct city) /count(*) from sakila.city_demo;
#使前缀的选择性接近于完整列的选择性
select count(*) as cnt,left(city,7) as pref from sakila.city_demo group by pref order by cnt desc limit 10;
select count(distinct city) /count(*) from sakila.city_demo;
select count(distinct LEFT(city,3))/count(*) as sel3,
count(distinct LEFT(city,4))/count(*) as sel4,
count(distinct LEFT(city,5))/count(*) as sel5,
count(distinct LEFT(city,6))/count(*) as sel6,
count(distinct LEFT(city,7))/count(*) as sel7 from sakila.city_demo;
#增加索引
alter table sakila.city_demo add key (city(7));
3.联合索引
create table people (
last_name varchar(50) not null ,
first_name varchar(50) not null ,
dob date not null ,
gender enum('m','f') not null ,
key(last_name,first_name,dob)
);
#可以使用索引的情况
#全职匹配
explain select * from people where last_name='li' and first_name = 'si' and dob = '2010-05-20';
#匹配最左前缀
explain select * from people where last_name = 'zhao';
#匹配列前缀
explain select * from people where last_name like 'l%';
#匹配列后缀 无法使用索引
explain select * from people where last_name like '%l';
#匹配范围值
explain select * from people where last_name > 'li' and last_name < 'wang';
#精确匹配某一列并范围匹配另外一列
explain select * from people where last_name = 'li' and last_name > 'er';
#只访问索引的查询
explain select last_name, first_name, dob from people;
####无法使用索引的情况
#不是按照索引的最左列开始查找，出生日期同理
explain select * from people where first_name = 'si';
#索引列在函数中 无法使用
explain select * from people where left(last_name,1) = 'l';
若有收获，就点个赞吧
0 人点赞
