mysql 的索引 全表扫描
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。
1.索引的类型
1)BTREE:B+树索引 （Btree B+tree B*tree）
2)HASH：HASH索引 hash key
3)FULLTEXT：全文索引
4)RTREE：R树索引
Btree索引
比如 我要搜索一个数字 38 这时要走3次IO
然后在搜索一个>38 <70 的数字 这时要走9次IO
B+tree算法
比如 我要搜索一个数字 38 这时要走3次IO
然后在搜索一个>38 <70 的数字 这时要走6次IO 注意 他是不走指针的 里面的Q代表指针
好处：1。优化了范围查询
2.在叶子节点添加了相邻节点的指针
B*tree
比如 我要搜索一个数字 38 这时要走3次IO
然后在搜索一个>38 <70 的数字 这时要走2次IO 注意 他是不走指针的 里面的Q代表指针
而且他只在枝节点上找
好处：在枝节点添加了相邻 节点的指针
2.索引管理
1.索引必须添加在列上面
2.在where后面接上建立索引的列，会加快查询速度
3.pages<---索引（属性）<----查数据。
3.索引的分类
主键索引（primary key）
普通索引 ***（key）
唯一索引（unique key）
4.添加索引
1普通索引
#创建索引：alter tabler + 表名 add index idx_+索引名
mysql> alter table student2 add index idx_sage_sage(sage);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student2;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | MUL | NULL | |
| sage | tinyint(3) unsigned | YES | MUL | NULL | |
| sgender | enum('m','f') | NO | | m | |
| cometime | datetime | NO | | CURRENT_TIMESTAMP | |
+----------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
mysql> show create table student2;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student2 | CREATE TABLE `student2` (
`sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(10) NOT NULL COMMENT '学生姓名',
`sage` tinyint(3) unsigned DEFAULT NULL COMMENT '学生年龄',
`sgender` enum('m','f') NOT NULL DEFAULT 'm' COMMENT '学生性别',
`cometime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`sid`),
KEY `idx_snamex_sname` (`sname`),
KEY `idx_sage_sage` (`sage`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.主键索引
#alter table +表名 add primary key pri_+想加的主索引名字
mysql> alter table st add primary key pri_id(id);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc st;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3.唯一键
#判断唯一键是否唯一 要去重
mysql> select count(distinct(name)) from st;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.18 sec)
#创建索引
alter table + 表名 add unique key uni_+想创唯一索引的名字
mysql> alter table st add unique key uni_name(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc st;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4.查看索引
mysql> desc st;
mysql> show index from st;
mysql> show create table +表名
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
5.创前缀索引
mysql> alter table student2 add index idx_sname2(sname(3));
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
6.删除索引
mysql> alter table student2 drop index idx_sname;
create table xiangqin(
name varchar(10),
age int,
monet bigint,
body varchar(10),
hight int,
weigh int,
face varchar(10),
sex enum('f','m'));
#删除索引
mysql> alter table +表名 drop index idx_+想删除的列
| student2 | CREATE TABLE `student2` (
`sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(10) NOT NULL COMMENT '学生姓名',
`sage` tinyint(3) unsigned DEFAULT NULL COMMENT '学生年龄',
`sgender` enum('m','f') NOT NULL DEFAULT 'm' COMMENT '学生性别',
`cometime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`sid`),
KEY `idx_snamex_sname` (`sname`),
KEY `idx_sname2` (`sname`(3)),
KEY `idx_sgender` (`sgender`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
mysql> alter table student2 drop index idx_snamex_sname;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
5.前缀索引和联合索引
1.前缀索引
1.在创建索引的时候，会把改列所有的数据按照btree的方式进行排序
2.创建索引，会占用磁盘空间
3.在同一列上，尽量避免创多个索引
4.在大列上创建，前缀索引
如果有，就使用前缀索引
根据字段的前N个字符建立索引
alter table test add index idx_name(name(10));
2.联合索引
给多个字段，创建一个索引
例：
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引
创建联合索引：
mysql> alter table xiangqin add index idx_all(age,monet,body,hight,face);
查看：
mysql> show create table xiangqin;
原则：把最常用来做为条件查询的列放在最前面
#创建people表
create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));
#创建联合索引
alter table people add index idx_gam(gender,age,money);
6.总结
创建索引
alter tabler + 表名 add index idx_+索引名
#创建主键索引
#alter table +表名 add primary key pri_+想加的主索引名字
#创建唯一索引
alter table + 表名 add unique key uni_+想创唯一索引的名字
#查看索引
mysql> desc st; 主键是prl 唯一键是mul 普通键是uni
mysql> show index from st;
mysql> show create table +表名
然后主键和唯一键 都是0 普通键是1
#查询 走索引
mysql> select * from xiangqin where sex='f' and money>10000000 and body='perfact' and face='nice'; 不走索引 mysql> select * from xiangqin where money>10000000 and body='perfact' and face='nice'; mysql> select * from xiangqin where money>10000000 and body='perfact' and face='nice' and sex='f'; alter table xiangqin add index idx_all(a,b,c,d); select * from xiangqin where a b c d a b c a b a c(部分走索引) a b d abdc bcd cdbd
创建索引：
1.不要在所有字段上都创建索引
2.如果有需求字段比较多，选择联合索引
3.如果有需求字段数据比较大，选择前缀索引
4.如果可以创建唯一索引，一定创建唯一索引
二.explain详解
7.explain命令使用方法
mysql> explain select * from city where countrycode='USA' or counttrycode='CHN';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | CountryCode | CountryCode | 3 | NULL | 637 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from city where District='zhejiang';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描
mysql> explain select name,countrycode from city where id=1;
1.explain命令应用
查询数据的方式
1.全表扫描1）在explain语句结果中type为ALL
2）什么时候出现全表扫描?
2.1 业务确实要获取所有数据
2.2 不走索引导致的全表扫描
2.2.1 没索引
2.2.2 索引创建有问题
2.2.3 语句有问题
实例
mysql> explain select * from city where countrycode='USA' or countrycode='CHN';
mysql> explain select * from city where countrycode='USA' union all select * from city where countrycode='CHN';
- 2.索引扫描
2.1 常见的索引扫描类型:
1）index 全索引扫描
mysql> explain select sid from student2;
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | student2 | index | NULL | idx_sgender | 1 | NULL | 2 | Using index |
+----+-------------+----------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select population from city;
2）range
mysql> explain select * from student2 where sid>1;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | student2 | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
3）ref 精确查找
explain select * from city where countrycode='USA' union all select * from city where countrycode='CHN';
4）eq_ref 主键索引，唯一索引
explain select score.mark,student.sname from score join student on score.sno=student.sno;
- const system
mysql> explain select * from student2 where sid=1;
- null
mysql> explain select * from student2 where sid=1000000000000000000000000;
一般我们说，只要一条SQL语句，达到range级别，那我们认为，该SQL语句的效率是OK的
从上到下，性能从最差到最好，我们认为至少要达到range级别
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;
Extra（扩展）
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer
mysql> mysql> explain select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
| 1 | SIMPLE | city | ref | CountryCode | CountryCode | 3 | const | 363 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
mysql> explain select * from city where population=30000 order by population;
key_len: 越小越好
- 前缀索引去控制
rows: 越小越好
建立索引的规范
1.尽量使用唯一索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
注意：如果重复值较多，可以考虑采用联合索引
2.为经常需要排序、分组和联合操作的字段建立索引
例如:
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作
3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
因此，为这样的字段建立索引，可以提高整个表的查询速度。
- 3.1 经常查询
- 3.2 列值的重复值少
注：如果经常作为条件的列，重复值特别多，可以建立联合索引
4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空
6．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
创建索引，什么时候不走索引
1.没有查询条件的时候，不走索引
#全表扫描
select * from table;
select * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。
1）对用户查看是非常痛苦的。
2）对服务器来讲毁灭性的。
3）SQL改写成以下语句：
select * from table limit 10;
2.查询结果集是原表中的大部分数据，应该是25％以上
mysql> explain select * from city where population>3000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | idx_pop | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from city where population>3000 limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| 1 | SIMPLE | city | range | idx_pop | idx_pop | 4 | NULL | 4053 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1）如果业务允许，可以使用limit控制。
2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
3.索引损坏，不走索引
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
mysql> explain select * from student2 where sid-1=8;
#例子
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
全表扫描
5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误
mysql> create table test(id int,name varchar(10),phone varchar(11))
mysql> explain select * from test where phone=133;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | uni_phone | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test where phone='133';
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | test | const | uni_phone | uni_phone | 36 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
6． <> ，not in 不走索引
mysql> explain select * from test where phone not in ('133','120');
mysql> explain select * from test where phone <> '133';
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in尽量改成union
推荐使用union all 联合查询
mysql> explain select * from test where phone > '133' union all select * from test where phone < '133';
7．like "%_" 百分号在最前面不走
mysql> explain select * from test where phone like '13%';
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| 1 | SIMPLE | test | range | uni_phone | uni_phone | 36 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from test where phone like '%13';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
企业中，需求使用like模糊查询。
推荐不使用MySQL，使用elasticsearch search engine
8.单独引用联合索引里非第一位置的索引列
如果一个SQL语句，是慢查询：
1.有没有创建索引
2.查看数据类型，和查询语句是否一致
3.查询语句中，是否使用字段做运算
4.查询出来的结果集很大，limit
5.查询语句中是否使用<> 或者 not in
6.查询语句中是否使用模糊查询，且%在前面
7.如果使用联合索引，请按照创建索引的顺序查询
8.索引损坏
MySQL存储引擎
1、MySQL引擎：
1.1 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
2、MySQL引擎功能：
2.1 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能
MySQL存储引擎：
01）InnoDB0
02）MyISAM
#查看存储引擎
mysql> show engines;
#查看数据库中哪些表是myisam
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
#查看数据库中哪些表是innodb
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
innodb 和 myisam物理区别
# myisam
-rw-rw---- 1 mysql mysql 10684 Nov 1 10:53 user.frm
-rw-rw---- 1 mysql mysql 1360 Nov 4 17:04 user.MYD
-rw-rw---- 1 mysql mysql 2048 Nov 4 17:04 user.MYI
# innodb
-rw-rw---- 1 mysql mysql 8586 Nov 5 11:44 score.frm
-rw-rw---- 1 mysql mysql 98304 Nov 5 11:45 score.ibd
innodb核心特性
MVCC
事务
行级锁
热备份
Crash Safe Recovery（自动故障恢复）
# 查看当前的存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
# 查看表的存储引擎
mysql> show create table world.city;
#设置默认存储引擎
[mysqld]
default-storage-engine=innodb
mysql> SET @@storage_engine=myisam;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@default_storage_engine;
#建表的时候指定存储引擎
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;