索引原理
root 根节点
branch 分支节点
leaf 叶子节点
1.b+树
- 平衡树(btree-balance tree) 能够让查找某一个值经历的查找速度尽量平衡
- 分支节点不存储数据 -- 让树的高度尽量矮,让查找一个数据的效率尽量的稳定
- 在所有叶子结点之间加入了双向的地址链接 -- 查找范围非常快
2.存储引擎的索引原理
聚集索引(聚簇索引)
- 全表数据都存储在叶子节点上 -- Innodb存储引擎中的主键
非聚集索引(非聚簇索引)/辅助索引
- 叶子节点不存放具体的整行数据,而是存储的这一行的主键的值
1.Innodb
聚集索引:只有存在于主键中(无需回表)
辅助索引:除了主键之外所有的索引都是辅助索引(需要回表)
回表: 只查询一个索引并不能解决查询中的问题,还需要到具体的表中去获取正行数据
聚集索引与辅助索引的区别:
叶子结点存放的是否是一整行的信息,聚集索引的叶子节点存放的是一整行信息,但是辅助索引在叶子节点不存放信息，存放索引，需要回到表中去获取数据。
2.2MyIsam
与innodb相同
聚集索引:只有存在于主键中(无需回表)
辅助索引:除了主键之外所有的索引都是辅助索引(需要回表)
3.索引的种类
①priamry key 的创建自带索引效果---------非空 + 唯一 + 聚集索引
②unique 唯一约束的创建也自带索引效果-------唯一 + 辅助索引
③index 普通的索引--------------辅助索引
4.创建与删除索引
# 创建索引
# create index ind_name on 表(name);
create index ind_name on t1(name);
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
# 删除索引
# drop index ind_name on 表;
drop index ind_name on t1;
普通索引INDEX：加速查找
唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
5.索引的优缺点
优点 : 查找速度快
缺点 : 浪费空间,拖慢写的速度
注:不要在程序中创建无用的索引
6.索引无法命中的情况(重要***********)
数据库使用的时候有什么注意事项
# 从库的角度
# 搭建集群
# 读写分离
# 分库
# 建表的角度上
# 1.合理安排表关系
# 2.尽量把固定长度的字段放在前面
# 3.尽量使用char代替varchar
# 4.分表: 水平分,垂直分
# 使用sql语句的时候
# 1.尽量用where来约束数据范围到一个比较小的程度,比如说分页的时候
# 2.尽量使用连表查询而不是子查询
# 3.删除数据或者修改数据的时候尽量要用主键作为条件
# 4.合理的创建和使用索引
#创建索引
# 1.选择区分度比较大的列
# 2.尽量选择短的字段创建索引
# 3.不要创建不必要的索引,及时删除不用的索引
#使用索引
# 1.查询的条件字段不是索引字段
# 对哪一个字段创建了索引,就用这个字段做条件查询
# 2.在创建索引的时候应该对区分度比较大的列进行创建
# 1/10以下的重复率比较适合创建索引
# 3.范围
# 范围越大越慢
# 范围越小越快
# like 'a%' 快
# like '%a' 慢
# 4.条件列参与计算/使用函数
# 5.and和or
# id name
# select * from s1 where id = 1800000 and name = 'eva';
# select count(*) from s1 where id = 1800000 or name = 'eva';
# 多个条件的组合,如果使用and连接
# 其中一列含有索引,都可以加快查找速度
# 如果使用or连接
# 必须所有的列都含有索引,才能加快查找速度
# 6.联合索引 : 最左前缀原则(必须带着最左边的列做条件,从出现范围开始整条索引失效)
#create index mix_ind on 表 (id,name,email)
# (id,name,email)
#select * from 表 where id = 123; 命中索引
#select * from 表 where id > 123; 不命中索引
#select * from 表 where id = 123 and name = 'alex'; 命中索引
#select * from 表 where id > 123 and name = 'alex'; 不命中索引
#select * from 表 where id = 123 and email = 'alex@oldboy'; 命中索引
#select * from 表 where email = 'alex@oldboy'; 不命中索引,因为条件中没有id
#select * from 表 where name='alex' and email = 'alex@oldb
# 7.条件中写出来的数据类型必须和定义的数据类型一致
# select * from biao where name = 666 # 不一致
# 8.select的字段应该包含order by的字段
# select name,age from 表 order by age; # 比较好 select 在前排序前获取了age字段后，再排序
# select name from 表 order by age; # 比较差 select 在前排序前获取了name字段后，还得再排序一次
其他注意事项：
- 避免使用select *
- 使用count(*)
- 创建表时尽量使用 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
7.mysql 神器 explain
查看sql语句的执行计划,以及是否命中了索引,命中的索引的类型
例:
explain select * from s1 where id < 1000000;
8.覆盖索引与索引合并
8.1覆盖索引
索引合并和覆盖索引
#联合索引
#(id,email)
id = 100 and email like 'eva';
#索引合并 : 分开创建在查询过程中临时合并成一条 Using union(ind_id,ind_email)
#创建索引的时候
create index ind_id on s1(id)
create index ind_email on s1(email)
select * from s1 where id = 100 or email = 'eva100@oldboy'
临时把两个索引ind_id和ind_email合并成一个索引
#覆盖索引：在查询过程中不需要回表 Using index
#对id字创建了索引
select id from s1 where id =100 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
select count(id) from s1 where id =100 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
select max(id) from s1 where id =100 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表
select name from s1 where id =100 相对慢
什么是mysql的执行计划?用过explain么?
# 在执行sql语句之前,mysql进行的一个优化sql语句执行效率的分析(计划),可以看到有哪些索引,实际用到了那个索引,执行的type等级
# id name email
select * from s1 where id = 1000000 and name=eva and email = 'eva1000000@oldboy';
# 有没有索引
# 有几个
# 用哪一个索引比较效率高
explain select * from s1 where id = 1000000 and name=eva and email = 'eva1000000@oldboy';
9.慢日志
慢日志能记录查询速度慢的sql语句
慢日志是通过配置文件开启
在mysql的配置中开启并设置一下
在超过设定时间之后,这条sql总是会被记录下来
这个时候我们可以对这些被记录的sql进行定期优化
10.多表联查速度慢怎么办?
慢查询优化 :
首先从sql的角度优化
把每一句话单独执行,找到效率低的表,优化这句sql
了解业务场景,适当创建索引,帮助查询
尽量用连表代替子查询
确认命中索引的情况
考虑修改表结构
拆表
把固定的字段往前调整
使用执行计划,观察sql的type通过以上调整是否提高
1.表结构
尽量用固定长度的数据类型代替可变长数据类型,把固定长度的字段放在前面
2.数据的角度上来说
如果表中的数据越多 查询效率越慢
列多 : 垂直分表
行多 : 水平分表
3.从sql的角度来说
①尽量把条件写的细致点儿,where条件多做筛选
②多表尽量连表代替子查询
③创建有效的索引,而规避无效的索引
4.配置角度上来说
开启慢日志查询 确认具体的有问题的sql
5.数据库
读写分离,解决数据库读的瓶颈
11.数据表库的导入导出
# 打开cmd输入一下命令
# 备份表 :homwork库中的所有表和数据
mysqldump -uroot -p123 homework > D:s23day42a.sql
# 备份单表
mysqldump -uroot -p123 homework course > D:s23day42a.sql
# 备份库 :
mysqldump -uroot -p123 --databases homework > D:s23day42db.sql
# 恢复数据:
# 进入mysql 切换到要恢复数据的库下面
sourse D:s23day42a.sql
12.开启事务,给数据加锁
begin;
select id from t1 where name = 'alex' for update;
update t1 set id = 2 where name = 'alex';
commit;
13.sql注入
SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，
而这些输入大都是SQL语法里的一些组合，
通过执行SQL语句进而执行攻击者所要的操作，
其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='eva', password="123",
database='db2')
cur = conn.cursor()
user = "'akhksh' or 1=1 ;-- "# 使用特殊语法使sql语句必查询成功
password = '*******'
sql = "select * from user where user_name = %s and password =%s;"% (user,password)
print(sql)
cur.execute(sql)
ret = cur.fetchone()
print(ret)
cur.close()
conn.close()