MySQL 基礎(chǔ)之索引

老師筆記

索引

索引用于快速查找具有特定列值的行。如果沒有索引，MySQL必須從第一行開始，然后讀取整個表以查找相關(guān)行。表越大，成本越高。如果表中有相關(guān)??列的索引，MySQL可以快速確定要在數(shù)據(jù)文件中間尋找的位置，而無需查看所有數(shù)據(jù)。這比按順序讀取每一行要快得多。

類似于字典中的目錄，查找字典內(nèi)容時可以根據(jù)目錄查找到數(shù)據(jù)的存放位置，然后直接獲取即可。

本質(zhì)上是告訴數(shù)據(jù)庫的存儲引擎如何快速找到我們所要的數(shù)據(jù)。所以 MySQL 的索引是在 MySQL 的存儲引擎層實現(xiàn)的，而不是在其服務(wù)器層實現(xiàn)。

MySQL中常見索引有：

• 普通索引
• 唯一索引
• 主鍵索引
• 組合索引

普通索引

普通索引僅有一個功能：加速查詢

/*創(chuàng)建表的同時創(chuàng)建索引*/
create table t1(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    extra text,
    index ix_name(name)
    /*添加索引到列名 name, 索引名為 ix_name*/
)

/*單獨創(chuàng)建索引*/
create index index_name on 表名稱(列名稱)
/*Example*/
create index index_name on student(name);

/*查看索引*/
show index from 表名稱;
/*Example*/
show index from student;

/*刪除索引*/
DROP INDEX index_name on 表名稱;
/*Example*/
DROP INDEX index_name on student;

唯一索引
唯一索引有兩個功能：加速查詢 和 唯一約束（可含null）

/創(chuàng)建表和唯一索引/
create table t2(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    unique index ix_name (name)
);

/創(chuàng)建唯一索引/
create unique index 索引名 on 表名(列名);

/刪除唯一索引/
ALTER TABLE 表名 DROP INDEX 索引名;

主鍵索引
主鍵有兩個功能：加速查詢 和 唯一約束（不可含null）

當(dāng)一個列被創(chuàng)建為主鍵是，它就會被賦予主機(jī)索引的屬性。
**主鍵天生就是索引??！

/*創(chuàng)建表和創(chuàng)建主鍵*/
create table t3(
    id int ,
    name varchar(32) ,
    email varchar(64) ,
    primary key(name)
);

聯(lián)合索引
聯(lián)合索引是將n個列聯(lián)合成一個索引

其應(yīng)用場景為：頻繁的同時使用 n 個列來進(jìn)行查詢，如：where name = 'shark' and age = 18。

create table studens(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    age int not null,
)

create index idx_name_age on students(name,age);

如上創(chuàng)建聯(lián)合索引之后，查詢時可以這么用：

• name and age -- 使用索引 where name='shark' and age=18;
• name -- 使用索引where name='shark';

但在這里有一個限制 B樹索引類型的聯(lián)合索引使用限制

• 匹配最左前綴的查詢
  對于聯(lián)合索引的使用上需要注意, where 自己的第一個條件的列名必須是組合索引列的最左邊的那個。

下面是可以有效使用的方式

where name='shark';
where name='shark' and age>18;
 where name = 'shark'  and (age >18 or age = 10);

但是, 不能是下面的用法

where age = 18;
where name='shark'  or   age=19;

注意：對于同時搜索n個條件時，組合索引的性能好于多個單一索引合并。

匹配列前綴查詢

name  like  'shark%'

匹配范圍值查詢

name > 'a' and name < 'c'

不可以使用 not in 和 <>
當(dāng)有 3 列組成的索引時， 使用這個聯(lián)合索引時，所有的字段不能跳過。
order_sn, order_name,order_date
where order_sn = '8998' and order_date = '20191010';
只能使用到 order_sn 這一個字段度索引，不能使用的 order_sn, order_date 的聯(lián)合索引

SQl 執(zhí)行計劃

explain select name from t1 where   name='shark'\G

索引過多的缺點
1、增加寫的壓力
2、增加 MySQL 查詢優(yōu)化器的選擇時間。

MySQL 安全機(jī)制

老師筆記

MySQL 安全控制

DCL（Data Control Language 數(shù)據(jù)庫控制語言）

用于數(shù)據(jù)庫授權(quán)、角色控制等操作

GRANT授權(quán)，為用戶賦予訪問權(quán)限
REVOKE取消授權(quán)，撤回授權(quán)權(quán)限

創(chuàng)建用戶
    create user '用戶名'@'客戶端來源IP地址' identified by '密碼';
刪除用戶
    drop user '用戶名'@'客戶端來源IP地址';
修改用戶
    rename user '用戶名'@'客戶端來源IP地址' to '新用戶名'@'客戶端來源IP地址' ;
修改密碼
    // 第一種方法：
    set password for '用戶名'@'IP地址'=Password('新密碼')

    // 第二種方法：
    alter user '用戶名'@'客戶端來源IP地址' identified by '新密碼';

   // 第三種方法(忘記密碼時，必須使用此方法修改密碼)：
    UPDATE mysql.user SET authentication_string=password('QFedu123!') WHERE user='root' and host='localhost';
  

  
PS：用戶權(quán)限相關(guān)數(shù)據(jù)保存在mysql數(shù)據(jù)庫的user表中，所以也可以直接對其進(jìn)行操作（不建議）

權(quán)限管理

grant  權(quán)限 on 數(shù)據(jù)庫.表  to  '用戶'@'客戶端來源IP地址' identified by '密碼';   -- 授權(quán)并設(shè)置密碼
revoke 權(quán)限 on 數(shù)據(jù)庫.表 from '用戶'@'客戶端來源IP地址'    -- 取消權(quán)限

show grants for '用戶'@'客戶端來源IP地址';  

查看生效的授權(quán)信息

• 針對所有庫和表的權(quán)限，比如*.* 。 去 mysql.user 中查看

select * from mysql.user where user='shark'\G

• 針對具體到庫的權(quán)限，比如db_name.* 。 去mysql.db中查看

select * from mysql.db  where user='shark'\G

• 針對具體表的授權(quán)，在 mysql.tables_priv中查看

select * from mysql.tables_priv where user='shark'\G

假如是 MySQL8.x

CREATE USER '你的用戶名'@'localhost' IDENTIFIED BY '你的密碼';
#創(chuàng)建新的用戶
GRANT ALL PRIVILEGES ON 你的數(shù)據(jù)庫名.* TO '你的用戶名'@'localhost';
#把剛剛創(chuàng)建的數(shù)據(jù)庫的管理權(quán)限給予剛剛創(chuàng)建的MySQL用戶
FLUSH PRIVILEGES;
#刷新權(quán)限，使用設(shè)置生效

關(guān)于權(quán)限
參考官方文檔

all privileges  除grant外的所有權(quán)限
select          僅查權(quán)限
select,insert   查和插入權(quán)限
...
usage                   無訪問權(quán)限
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke  all privileges
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和存儲過程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(關(guān)閉MySQL)
super                   使用change master、kill、logs、purge、master和set global。還允許mysqladmin調(diào)試登陸
replication client      服務(wù)器位置的訪問
replication slave       由復(fù)制從屬使用

關(guān)于數(shù)據(jù)庫和表

對于目標(biāo)數(shù)據(jù)庫以及內(nèi)部其他：
數(shù)據(jù)庫名.*           數(shù)據(jù)庫中的所有
數(shù)據(jù)庫名.表          指定數(shù)據(jù)庫中的某張表
數(shù)據(jù)庫名.存儲過程     指定數(shù)據(jù)庫中的存儲過程
*.*                所有數(shù)據(jù)庫

關(guān)于用戶和 IP

用戶名@IP地址         用戶只能在此 IP 下才能訪問
用戶名@192.168.1.%   用戶只能在此 IP 段下才能訪問(通配符%表示任意)
用戶名@%.shark.com
用戶名@%             用戶可以再任意IP下訪問(默認(rèn)IP地址為%)

Example

create user 'shark'@'%';
grant all privileges on *.*  to 'shark'@'%' identified by '123';

立刻生效

/*將數(shù)據(jù)讀取到內(nèi)存中，從而立即生效。*/
flush privileges

也可以在創(chuàng)建用戶的同時直接授權(quán)（mysql8.x 不可以）

grant select on *.*     /*設(shè)置查詢數(shù)據(jù)的權(quán)限在所有的庫和表*/
 to 'shark_2'@"%"       /*指定用戶名和來源 ip*/
 identified by '123';   /*設(shè)置密碼*/

自關(guān)聯(lián)

老師筆記
一個事例，主要自己理解，理清數(shù)據(jù)之間的關(guān)系，看看老師筆記里面的事例。