mysql 索引

最新推荐文章于 2023-07-13 12:14:26 发布

孤舟听雨

最新推荐文章于 2023-07-13 12:14:26 发布

阅读量340

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/u013043762/article/details/79554243

版权

mysql 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

索引的开启和关闭

A.针对Myisam数据表的处理：
关闭索引：
① 数据表之前已经有数据(先关闭、再开启索引)：
alter table 表名 disable keys; //关闭全部索引
1万次insert sql语句的执行；
alter table 表名 enable keys; //创建全部索引

② 数据表没有数据(先删除全部索引，之后再创建)
alter table 表名 drop primary key,key 名字 /(普通、唯一);
1万次insert sql语句的执行；
alter table 表名 add primary key （字段）,key 名字(字段) //(普通、唯一)

B.针对Innodb数据表的处理：
该innodb无需对索引做关闭处理，该存储引擎支持事务。
通过事务就可以先写数据、最后维护索引。

具体操作：
start transaction; //开启事务
1万次insert sql语句的执行；
（rollback; //任何sql语句有问题就回滚）
commit; //执行全部的sql语句
之后维护全部的索引–自然完成

字段选取

1.选取占据空间小的字段
int 整形字段的选取
bigint int mediumint smallint tinyint
这里写图片描述
2.选取长度固定的字段
3.整形存储
3.2 长度固定字段：
varchar(长度） 1—65535
内容范围: 单字节内容今65535 ,保留1-2个字节表示内容的长度
保存内容的长度使用。
存储汉字（utf-8字符集 3个字节=一个汉字）
char(长度） 1–255 字符。
单字节：每个字节等于一个字符
汉字： 3个字节等于一个字符
无论单字节内容、汉字都可以存储1-255 个
char 的运行速度快，例如：char(20) 实际存储16个字符，分配20个空间
varchar 的运行速度比char 慢例如： varchar(20) 实际存储16个字符，分配16个空间
内容最好固定住长度
手机号码存储： char(11)
存储邮箱：速度快char(40) ,空间节省 varchar(40)
整形存储
时间变为整型存储：
int
date time datetime timestamp
集合： set (‘篮球‘，’排球‘，’足球’,’棒球’)
枚举 : enum (‘男’,’女’,’保密’)
建议使用集合，枚举类型，他们底层使用整形存储
IP地址通过整形保存

select INET_ATON('192.168.32.14');

select INET_NTOA('3232243726')

逆范式

数据表的总体设计要遵守三范式，但是有的时候为了整体性能的考虑，就不遵守一点，这样就称为“逆范式”
需求：查询每个分类下商品的数量
goods 商品表： id nmae cat_id (分类 id )
category 分类表： cat_id cat_name

索引

可以简单理解为有索引字段、字段对应记录的物理地址。
索引字段按照一定的规律、规则组织在一起（内部有数据结构/算法），可以加快信息的查找，信息在索引内部被查找出来后，直接对应记录的“物理地址”，根据物理地址就可以快速定位需要的记录信息。

复合索引

索引字段是两个或以上字段有索引

索引的创建

数据表的全部字段都可以创建索引
索引类型：

主键索引
唯一索引（字段内容不能重复）
普通索引
全文索引(把一个文章的内容都给创建索引），innodb 在mysql5.6.4之前数据库里不支持全文索引，只有myisam 支持，之后的myisam 只有支持，跟新版本两者皆可。
索引名称问题：
unique key [名称] (name),
key [名称] ( height),
fulltext key[名称] ( introduce)
索引名称可以不设置，默认与当前索引名称一致

添加索引

CREATE TABLE "student3" (
  "id" int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  "name" varchar(20) NOT NULL COMMENT '名字',
  "height" decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '身高',
  "introduce" text COMMENT '个人简介',
  PRIMARY KEY ("id"),
    unique key (name),
    key (height),
    fulltext key(introduce)
) ENGINE=MyISAM AUTO_INCREMENT=1572883 DEFAULT CHARSET=utf8;

* 在没有索引的表中添加索引*

mysql> alter table student4 add primary key(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student4 add unique key(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student4 add key(height);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student4 add fulltext key(introduce);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student4 modify id int not null auto_increment comment'主键';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

 alter table student4 add key (name,height) ;

alter table student drop key  索引名;
删除数据表的主键索引
alter table student modify id int not null ;
alter table student drop primary key;

索引适合场合

有的字段重复字段很多，例如性别，不适合做索引
1.where 条件后面的字段都可以设置索引
2.排序字段适合做索引
没有索引的情况。

mysql> explain select * from t_user order by phone limit 0,10; /G
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15547 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

有索引的情况

mysql> alter table t_user add key (phone);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from t_user group by phone limit 0,10; /G
+----+-------------+--------+------------+-------+---------------+-------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | index | phone         | phone | 93      | NULL |   10 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

索引覆盖

做数据查询，查询的字段就是索引的内容
这样索引本身就支持数据的提供，其已经不获得具体其他字段信息。
数据查询只通过索引内容就获得需要的信息，就称为索引覆盖。
没有索引覆盖的情况：

mysql> explain select email,qq from t_user limit 1000,4;/G
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15454 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

有索引覆盖的情况：

mysql> alter table t_user add key email_qq(email,qq) ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select email,qq from t_user limit 1000,4;
+----+-------------+--------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | index | NULL          | email_qq | 1536    | NULL | 15454 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

连接查询

外键字段也适合做索引

索引原则

1.字段独立原则
select * from emp where empno=234234234;
select * from emp where empno+2 =234234234;#字段不独立
2.左原则
模糊查询的时候，有可能用到索引，也有可能无法使用索引。

        select * from emp where ename like '%内容%' -- 不能用索引
        select * from emp where ename like '_内容_' -- 不能用索引
        select * from emp where ename like 'tom_'; -- 可以
        select * from emp where ename like 'tom%'; -- 可以

在进行模糊查询时，左边的内容固定，可以使用到索引，若不固定，例如第一个，就用不到索引

复合索引

复合索引的第一个字段，单独作为条件可以使用到该索引。
复合索引的第二个字段，单独作为条件不可以使用索引。
复合索引的两个字段都作为索引，则可以使用该索引。
4.适合索引
1.where
2.排序字段
select * from 表明 order by 字段 limit 1000000,50 ; 不会用索引
select * from 表明 order by 字段 limit 50 ; 用索引
select * from 表明 order by 字段 limit 100，50; 用索引
select * from 表明 order by 字段 limit 偏移量,50; 用索引
偏移量如果超过10万，就不会用索引。
使用原则
1.字段独立
2.左原则（模糊查询）
3.复合索引
4.or 原则

补充： in 条件查询也可以使用索引