数据库相关知识
数据库相关知识
1.pymysql模块
- 安装pymysql模块---pip install pymysql
- 连接、执行sql、关闭（游标）
import pymysql name=input('username: ').strip() password=input('password: ').strip() conn=pymysql.connect( #连接数据库 host='127.0.0.1', port=3306, user='root', password='', database='day45', charset='utf8' ) cur=conn.cursor() #游标，执行完返回的结果集默认以元组显示 #cur=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行sql语句 sql='select * from userinfo where user=%s and password=%s;' res=cur.execute(sql,(name,password)) #返回sql查询成功的记录数目 cur.close() conn.close() if res: print('登录成功！') else: print('登录失败！')
- sql语句注入
执行下列语句时：
sql='select * from userinfo where user="%s" and password = "%s";' %(name,password)
res=cur.execute(sql)
符号--会注释掉它之后的sql （1）用户存在，绕过密码：yh' -- 任意字符 （2）用户不存在，绕过用户与密码：xxx' or 1=1 -- 任意字符
1）
2）
- 如何解决这种问题呢?
pymysql可以帮我们拼接字符串，不用我们自己拼接，这样就避免了发生上述的bug.
自己拼接的字符串 sql='select * from userinfo where user="%s" and password = "%s";'
%(name,password) res=cur.execute(sql) %s要加引号 pymysql帮我们拼接字符串 sql='select * from userinfo where user=%s and password=%s;' res=cur.execute(sql,(name,password)) %s不用加引号，pymysql帮我们加引号，只要传相应的参数就可以了
- 增、删、改：conn.commit()
import pymysql conn=pymysql.connect( #连接 host='127.0.0.1', port=3306, user='root', password='', database='day45', charset='utf8' ) cur=conn.cursor() #游标 #执行sql语句 #1 sql ='insert into userinfo(user,password) values('aa','123');' res=cur.execute(sql) #返回sql影响成功的行数 print（res） #2 sql ='insert into userinfo(user,password) values(%s,%s);' res=cur.execute(sql,('aa','123')) #返回sql影响成功的行数 print(res) #3 sql ='insert into userinfo(user,password) values(%s,%s);' res=cur.executemany(sql,[('aa','123'),('bb','456'),('cc','ggg')]) #返回sql影响成功的行数 print(res) conn.commit() #提交后才发现表中插入记录成功 cur.close() #关闭游标 conn.close() #关闭连接 增、删、改
- 查
import pymysql conn=pymysql.connect( #连接 host='127.0.0.1', port=3306, user='root', password='', database='day45', charset='utf8' ) cur=conn.cursor() #游标 #执行sql语句 sql='select * from userinfo;' rows=cur.execute(sql) #返回sql影响成功的行数rows，将结果放入 一个集合，等待被查询 cur.scroll(3,mode='absolute' ) #相对绝对位置移动 cur.scroll(3,mode='relative' ) #相对当前位置移动 res1=cur.fetchone() res2=cur.fetchone() #每次取一个 res3=cur.fetchmany(2) #取的个数由括号中的数字决定 res4=cur.fetchall() #取出所有值 conn.commit() #提交后才发现表中插入记录成功 cur.close() #关闭游标 conn.close() #关闭连接
- 获取插入的最后一条数据的自增ID
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='', database='day45', charset='utf8' ) cur=conn.cursor() sql='insert into userinfo(user,password) values("xxx","123");' rows=cur.execute(sql) print(cur.lastrowid) #在插入语句后查看 conn.commit() cursor.close() conn.close()
2.mysql数据备份
- 物理备份：直接复制数据库文件，适用于大型数据库环境，但不能恢复到异构系统中如Windows;
- 逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对低；
- 导出表：将表导入到文本文件中。
使用mysqldump实现逻辑备份 #语法： #mysqldump -h 服务器 -u 用户名 -p密码 数据库名>备份文件.sql 示例： 1）备份库 mysqldump -uroot -p --database day43 >F:\\day43_bak_2017_10_30.sql 2)恢复库 mysql -uroot -p < F:\\day43_bak_2017_10_30.sql 3）备份多个库 mysqldump -uroot -p --database day43 day44 day45> F:\\day44_bak_2017_10_30.sql 4)恢复库 mysql -uroot -p < F:\\day44_bak_2017_10_30.sql 5）备份多个表 mysqldump -uroot -p day43 employee t1 t2> F:\\day43_t1_t2_employee_bak_2017_10_30.sql 6)恢复表 mysql -uroot -p day43 < F:\\day43_t1_t2_employee_bak_2017_10_30.sql 7)备份所有的库 mysqldump -uroot -p --all-databases > F:\\all.sql 8）恢复库 mysql -uroot -p < F:\\all.sql 9)导出表 select * from day43.class into outfile 'F:\\class.txt' fields terminated by ',' #定义字段分隔符 optionally enclosed by '”' #定义字符串用什么符号括起来 lines terminated by '\n'; #定义换行符 这样直接去运行，在mysql5.5以后的版本会报错： ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 这样是为了防止黑客盗走机密数据，在一定程度上确保了数据安全，只能将文件导到指定目录 解决方法 在配置文件中 [mysqld] secure_file_priv='F:\\' #只能将数据导出到F:\\下 重启mysql 再执行导出表的语句
10）导入表
load data infile 'F:\\class.txt'
into table day44.class
fields terminated by ','
optionally enclosed by '”'
lines terminated by '\n';
11)数据库迁移
务必保证在相同版本之间迁移
mysqldump -h 源ip -uroot -p --databases db1 | mysql -h 目标IP -uroot -p456
3.索引原理与慢查询优化
- 一般在应用系统中，读写比例在10:1左右，在生产环境中，也是查询花费的时间长，也有可能
花了很长时间查到的内容不是自己想要的，所以对查询的优化是必要的，自热而然想到索引了。
- 索引在MYSQL中也叫做‘键’，是存储引擎用于快速找到记录的一种数据结构索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
- 索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
- 索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
- 索引原理：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机事件变成
顺序的事件，也就是我们可以用同一种查找方法来锁定不同的数据。
- 磁盘I/O与预读
磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁
臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表
示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入
磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于9ms左
右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因
为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒
的时间，显然是个灾难。
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也
都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。
每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据
时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
下图是计算机硬件延迟的对比图
- 索引的数据结构
任何一种数据结构的产生都有它的背景和使用场景，因此b+树索引数据结构的产生是因为我们每次查找数据时想把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。
如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
b+树的查找过程 如上图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35
之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘
加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分
查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高
将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。 b+树性质 1）索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量
是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页
的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如
int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数
据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2）索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比
如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检
索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，
必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段
age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
- mysql索引管理
1）功能：加速查找；primary key，unique,联合唯一也是索引，除了加速查找外，还有约
束的功能
2）常用索引
普通索引INDEX：加速查找 唯一索引： -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复） -唯一索引UNIQUE:加速查找+约束（不能重复） 联合索引： -PRIMARY KEY(id,name):联合主键索引 -UNIQUE(id,name):联合唯一索引 -INDEX(id,name):联合普通索引
举个例子来说，比如你在为某商场做一个会员卡的系统。 这个系统有一个会员表 有下列字段： 会员编号 INT 会员姓名 VARCHAR(10) 会员身份证号码 VARCHAR(18) 会员电话 VARCHAR(10) 会员住址 VARCHAR(50) 会员备注信息 TEXT 那么这个 会员编号，作为主键，使用 PRIMARY 会员姓名 如果要建索引的话，那么就是普通的 INDEX 会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复） #除此之外还有全文索引，即FULLTEXT 会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。 用于搜索很长一篇文章的时候，效果最好。 用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。 但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。 #其他的如空间索引SPATIAL，了解即可，几乎不用
3）索引两大类型hash与btree
#我们可以在创建上述索引的时候，为其指定索引类型，分两类 hash类型的索引：查询单条快，范围查询慢 btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它） #不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
- 创建/删除索引的语法
创建表时就指定索引 (1)create table t1( id int, name char(5), unique key uni_name(name), primary key(id) ); 查看表结构 mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | char(5) | YES | UNI | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.04 sec) (2)create table t2( id int, name char(5), index idx_name(name) ); 查看表结构 mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra| +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(5) | YES | MUL | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.04 sec) 在创建完表后为其添加索引 create table t3( id int, name char(5) ); create index idx_name on t3(name); alter table t3 add index idx_id(id); #alter table t3 add primary key(id); 查看 mysql> show create table t3; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t3 | CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL, `name` char(5) DEFAULT NULL, KEY `idx_name` (`name`), KEY `idx_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 删除索引 drop index idx_id on t3; alter table t3 drop primary key;
- 测试索引
#1. 准备表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程，实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy')); set i=i+1; end while; END$$ #$$结束 delimiter ; #重新声明分号为结束符号 #3. 查看存储过程 show create procedure auto_insert1\G #4. 调用存储过程 call auto_insert1();
在没有索引的前提下测试查询速度 #无索引：mysql根本就不知道到底是否存在id等于666666666的记录，只能把数
据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢 select * from s1 where id=666666666; 结果为 mysql> select * from s1 where id=666666666; Empty set (1.83 sec) #比较慢 在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢 mysql> create index ind_id on s1(id); Query OK, 0 rows affected, 1 warning (6.22 sec) #特别慢 在索引建立完毕后，以该字段为查询条件时，查询速度提升明显 ysql> select * from s1 where id=666666666; Empty set (0.00 sec) #查询速度变快了 注意： 1）mysql先去索引表里根据b+树的搜索原理很快搜索到id等于666666666的记录
不存在，IO大大降低，因而速度明显提升 2）我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了，删
掉索引，内存空间也不会变小 3)给id 字段加索引之后，不会减少其它单独字段的查询速度 mysql> select * from s1 where email='xxxx'; Empty set (2.48 sec) #没有给email加索引，故查询速度依然很慢
总结 1） 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 666
就需要为id加上索引 2） 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快 比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创
建索引结构，存放于硬盘的表中，建完以后，再查询就会很快了。 3） 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单
独的索引文件table1.MYI，MySAM索引文件和数据文件是分离的，索引文件仅保存数据
记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）
组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据
表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），
如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存
在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类
型为长整型.
- 正确使用索引
并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到 预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题 1）范围问题，条件不明确，条件中出现：>,>=,<,<=,!=, between...and...,like等 大于号、小于号 mysql> select count(*) from s1 where id=1000; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) #范围明确，可快速找到 mysql> select count(*) from s1 where id>1000; +----------+ #没有明确指定到底找哪个id,而是指定了一个 | count(*) | 范围，mysql会拿着1001去搜索树中找一次， +----------+ 然后拿着1002,1003,1004......去搜索树中 | 1472586 | 找，如果范围很大，则跟全表扫描没有区别， +----------+ 也就没有达到快速查找的要求 1 row in set (0.50 sec) mysql> select count(*) from s1 where id>1000 and id<2000; +----------+ | count(*) | +----------+ | 999 | #如果范围很小，查询速度仍然很快 +----------+ 1 row in set (0.00 sec) 不等于 ！= mysql> select count(*) from s1 where id=1000; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) #范围明确，可快速找到 mysql> select count(*) from s1 where id!=1000; +----------+ | count(*) | +----------+ | 1473585 | +----------+ 1 row in set (0.53 sec) 不等于1000的id是一个很大的范围 between ... and ... mysql> select count(*) from s1 where id between 1 and 300000; +----------+ | count(*) | +----------+ | 300000 | +----------+ 1 row in set (0.11 sec) 范围大，查询速度慢 mysql> select count(*) from s1 where id between 1 and 10; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) 范围小，查询速度快 like mysql> select count(*) from s1 where email='xxxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.52 sec) #没有给email字段加索引，查询速度慢 mysql> create index ind_ema on s1(email); #给email字段加索引 Query OK, 0 rows affected (9.82 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where email='xxxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #给email字段加索引后，查询速度加快 mysql> select count(*) from s1 where email like 'xxxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #like指定一个明确的值，速度依然很快 mysql> select count(*) from s1 where email like 'xxxx%'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #like匹配的字符中有%，但处于末尾，速度依然快 mysql> select count(*) from s1 where email like '%xxxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.77 sec) #like匹配的字符中有%，但处于开头，速度就变慢 2）尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)， 表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一 些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么 经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1 以上，即平均1条扫描10条记录 先把表中的索引都删除，研究区分度的问题 mysql> drop index ind_id on s1; Query OK, 0 rows affected (2.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index ind_ema on s1; Query OK, 0 rows affected (0.92 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where name= 'xxxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (1.74 sec) #没有为name字段加索引前查询很慢 mysql> create index ind_name on s1(name); #为name字段加索引 Query OK, 0 rows affected (7.89 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where name= 'xxxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #为name字段加索引后，插叙速度貌似加快了 mysql> select count(*) from s1 where name= 'egon'; +----------+ | count(*) | +----------+ | 1473586 | +----------+ 1 row in set (0.64 sec) #如果查询条件为name='egon'，速度又变慢 了，这是什么原因呢 这就跟字段的区分度有关了，name字段值均为egon,区分度低，b+树中，查询速 度与树的高度成反比，所以字段的区分度低，b+树就越高，在查询name='egon' 的条件时，无法从树的某个位置得到一个明确的范围，只能一层一层往下找，跟全 表扫描的I/O次数没有多大区别，所以速度慢 3）=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，
mysql的查询优化器会帮你优化成索引可以识别的形式 4）索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有
元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’) 先将name字段的索引删除，给id字段加上索引 mysql> drop index ind_name on s1; Query OK, 0 rows affected (1.99 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index ind_id on s1(id); Query OK, 0 rows affected (5.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where id=66666; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) #id字段有索引，查询速度快 mysql> select count(*) from s1 where id*3=66666; +----------+ | count(*) | #索引字段id参与了计算，无法拿到一个明确的值去索引 +----------+ | 1 | 树中查找，每次都得临时计算一下，因此速度慢 +----------+ 1 row in set (0.47 sec) 5）and/or 条件1 and 条件2:查询原理是：首先条件1与条件2都成立的前提下，才算匹配成功一条记录；
其次mysql会按先优先判断索引字段的条件,如果按照该条件为真，但锁定的范围很小，或者干
脆为假，那我们即便是没有为其他条件的字段添加索引，最终的结果仍然很快。 若条件1的字段有索引，而条件2的字段没有索引，那么如果在按照条件1查出的结果很少的情况
下，即便我们没有为条件2创建索引，最终的查询速度依然很快。 若条件1的字段没有索引，而条件2的字段有索引，那么如果在按照条件2查出的结果很少的情况
下，即便我们没有为条件1创建索引，最终的查询速度依然很快。 mysql> create index ind_name on s1(name); #为name字段添加索引，该字段 Query OK, 0 rows affected (7.62 sec) 区分度低 Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where name='xxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #name='xxx'可以很快从索引树中区分出该字段不存在 因而速度快 mysql> select count(*) from s1 where name='xxx' and gender='male'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #gender是非索引字段，但是name='xxx'不成立，因而 不会执行gender条件，且name='xxx'的速度快 在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），
会依次往右找到一个区分度高的索引字段，加速查询 mysql> select count(*) from s1 where name='egon' and gender='male'; +----------+ | count(*) | +----------+ | 1473586 | +----------+ 1 row in set (3.88 sec) mysql> create index ind_gen on s1(gender); Query OK, 0 rows affected (8.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where name='egon' and gender='male'; +----------+ | count(*) | +----------+ | 1473586 | +----------+ 1 row in set (1.49 sec) mysql> select count(*) from s1 where name='egon' and gender='xxx'; +----------+ | count(*) | #and会先执行区分度高的索引，故速度快 +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from s1 where name='egon' and gender='male' and id=666; +----------+ | count(*) | +----------+ | 1 | #and会先执行区分度高的索引，故速度快 +----------+ 1 row in set (0.00 sec) mysql> select count(*) from s1 where name='egon' and gender='male' and id>666; +----------+ | count(*) | +----------+ 当索引区分度不高同时又有大范围的索引时，速度就慢 | 1472920 | +----------+ 1 row in set (3.92 sec) mysql> select count(*) from s1 where name='egon' and gender='xxx' and id>666; +----------+ | count(*) | +----------+ #and会先执行区分度高的索引，故速度快 | 0 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from s1 where name='egon' and gender='male' and id>666 and email='xxx'; +----------+ | count(*) | 前三个条件都成立，但无法利用索引达到加速的目的，第一 +----------+ 个和第二个都是因为区分度低，而id又是一个大范围索引， | 0 | 因此插叙速度很慢 +----------+ 1 row in set (3.55 sec) mysql> create index ind_ema on s1(email); #为email字段加索引，该字段区分度高 Query OK, 0 rows affected (9.26 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where name='egon' and gender='male' and id>666 and email='xxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #速度明显提升 经过分析，在条件为name='egon' and gender='male' and id>333 and email='xxx'的情况下，
我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降
低我们的插入数据的效率 验证： 删除id,name,gender索引 mysql> drop index ind_id on s1; Query OK, 0 rows affected (2.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index ind_name on s1; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index ind_gen on s1; Query OK, 0 rows affected (2.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(6) | YES | | NULL | | | email | varchar(50) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.04 sec) mysql> select count(*) from s1 where name='egon' and gender='male' and id>666 and email='xxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #email为索引和其它几个一起为索引的效果一样 or:对于多个or条件，会依次从左到右判断 6)最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询
(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and
b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建
立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 先删除email索引 mysql> drop index ind_ema on s1; Query OK, 0 rows affected (2.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index ind on s1(id,name,gender,email); Query OK, 0 rows affected (9.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where name='egon' and gender='male' and id>666 and email='xxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (1.02 sec) #建立索引时未将条件中关于范围比较的字段放到后面，查询 速度慢 mysql> drop index ind on s1; Query OK, 0 rows affected (2.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index ind on s1(name,gender,email,id); Query OK, 0 rows affected (10.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from s1 where name='egon' and gender='male' and id>666 and email='xxx'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #建立索引时将条件中关于范围比较的字段放到了后面，查 询速度明显加快 7）其它情况 - 使用函数 select * from tb1 where reverse(email) = 'egon'; - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where email = 999; #排序条件为索引，则select字段必须也是索引字段，否则无法命中 - order by select name from s1 order by email desc; 当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢 select email from s1 order by email desc; 特别的：如果对主键排序，则还是速度很快： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 命中索引 name -- 命中索引 email -- 未命中索引 - count(1)或count(列)代替count(*)在mysql中没有差别了 - create index xxxx on tb(title(19)) #text类型，必须制定长度 8）注意事项 - 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合
- 覆盖索引与索引合并
#覆盖索引： - 所有字段（条件的，查询结果的等）都是索引字段 #分析 select age from s1 where id=123 and name = 'egon'; #id字段有索引，但是name字段没有索引 该sql命中了索引，但未覆盖全部。 利用id=123到索引的数据结构中定位到了id字段，但是仍要判 断name字段，但是name字段没有索引，而且查询结果的字段 age也没有索引 最牛逼的情况是，索引字段覆盖了所有，那全程通过索引来加 速查询以及获取结果就ok了
#索引合并：把多个单列索引合并使用 #分析： 组合索引能做到的事情，我们都可以用索引合并去解决，比如 create index ne on s1(name,email);#组合索引 我们完全可以单独为name和email创建索引，然后按照where name='xxx' and email='xxx'使用 #索引合并 组合索引可以命中： select * from s1 where name='egon' ; select * from s1 where name='egon' and email='adf'; 索引合并可以命中： select * from s1 where name='egon' ; select * from s1 where email='adf'; select * from s1 where name='egon' and email='adf'; 乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果是name='egon' and email='adf',那么组合索引的效率要高于索引合并，如果是单 条件查，那么还是用索引合并比较合理
- 查询优化神器-explain
执行计划：让mysql预估执行操作(一般正确) all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const id,email 慢： select * from userinfo3 where name='alex' explain select * from userinfo3 where name='alex' type: ALL(全表扫描) select * from userinfo3 limit 1; 快： select * from userinfo3 where email='alex' type: const(走索引)
- 慢查询优化的基本步骤
0）先运行看看是否真的很慢，注意设置SQL_NO_CACHE 1）where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表
中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高 2）explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询） 3）order by limit 形式的sql语句让排序的表优先查 4）了解业务方使用场景 5）加索引时参照建索引的几大原则 6）观察结果，不符合预期继续从0分析
- 慢日志管理
慢日志 - 执行时间 > 10 - 未命中索引 - 日志文件路径 配置： - 内存 show variables like '%query%'; show variables like '%queries%'; set global 变量名 = 值 - 配置文件 mysqld
--defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini' my.conf内容： slow_query_log = ON slow_query_log_file = D:/.... 注意：修改配置文件之后，需要重启服务
MySQL日志管理 ======================================================== 错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息 二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作 查询日志: 记录查询的信息 慢查询日志: 记录执行时间超过指定时间的操作 中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放 通用日志： 审计哪个账号、在哪个时段、做了哪些事件 事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等 ======================================================== 一、bin-log 1. 启用 # vim /etc/my.cnf [mysqld] log-bin[=dir\[filename]] # service mysqld restart 2. 暂停 //仅当前会话 SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=1; 3. 查看 查看全部： # mysqlbinlog mysql.000002 按时间： # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" # mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54" # mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 按字节数： # mysqlbinlog mysql.000002 --start-position=260 # mysqlbinlog mysql.000002 --stop-position=260 # mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 4. 截断bin-log（产生新的bin-log文件） a. 重启mysql服务器 b. # mysql -uroot -p123 -e 'flush logs' 5. 删除bin-log文件 # mysql -uroot -p123 -e 'reset master' 二、查询日志 启用通用查询日志 # vim /etc/my.cnf [mysqld] log[=dir\[filename]] # service mysqld restart 三、慢查询日志 启用慢查询日志 # vim /etc/my.cnf [mysqld] log-slow-queries[=dir\[filename]] long_query_time=n # service mysqld restart MySQL 5.6: slow-query-log=1 slow-query-log-file=slow.log long_query_time=3 查看慢查询日志 测试:BENCHMARK(count,expr) SELECT BENCHMARK(50000000,2*3);
浙公网安备 33010602011771号