字段類型
整型
tinyint 占用一個字節(jié) 8位 無符號最大整數(shù) 255 有符號 -128 -127
smallint 占用二個字節(jié) 16位 無符號最大整數(shù) 65535 有符號 -32768 - 32767
mediumint 占用三個字節(jié) 16位 無符號最大整數(shù) 16777215 有符號 -8388608 - 8388607
int 占用四個字節(jié) 16位 無符號最大整數(shù) 4294967295 有符號 -2147483648 - 2147483647
bigint 占用八個字節(jié) 16位 無符號最大整數(shù) 2^64-1 有符號 -2^63- 2^63
注意 tinyint(4) 括弧里面的書與實際存儲大小無關 代表的是不足四位用0替補在前面 0001 用于序列號
字符串
char 定長 規(guī)定了必須存儲多少字符長超出的自動截取不夠的用空格補齊
varchar 邊長 規(guī)定了可以在指定區(qū)間里存儲字符 有多少存多少 無需補齊 超出部分會截取
0~21844
MySQL索引底層存儲原理
一、二叉樹
特點：左邊的子元素小于父元素，右邊的子元素大于父元素
缺點：當插入單邊增長的數(shù)據(jù)就變成鏈表了不適用
二、紅黑樹（二叉平衡樹）
特點：單左右失衡嚴重會自動平衡防止單邊增長
缺點：層數(shù)過高即樹的深度變高查找的次數(shù)就很多
三、hash
特點：每個索引做hash運算生成散列值存入hash表（散列值和索引所在行磁盤文件地址），查詢元素做hash運算去hash表找到對應的元素地址
缺點：對進行范圍查找時候不適用
四、B-Tree
特點：每個節(jié)點分配容量大一點（默認16kb），一個節(jié)點就可以存多個索引元素，橫向就會存儲多一點索引元素，數(shù)據(jù)和關鍵字存在同一節(jié)點
升級版 B+Tree （多叉平衡樹）
非葉子節(jié)點不存儲數(shù)據(jù) 只存儲（索引）冗余，可以放更多索引
葉子節(jié)點包含所有索引字段
葉子節(jié)點用指針連接提高區(qū)間訪問性能
對范圍查找很方便
五、存儲引擎
myisam （非聚集索引）每個表生成三個文件 表結(jié)構文件 frm 表數(shù)據(jù)文件 myd 表索引文件 myi 即關鍵字和數(shù)據(jù)不在一起 葉子節(jié)點存儲的是數(shù)據(jù)指針
innodb （聚集索引） 每張表生出二個文件 表結(jié)構文件 frm 表數(shù)據(jù)和索引文件 ibd 即關鍵字和數(shù)據(jù)在一起 葉子節(jié)點存儲的是索引和數(shù)據(jù)
聚集索引索引和數(shù)據(jù)在一起（同一個文件） 非聚集索引索引和數(shù)據(jù)不在一起（不在同一個文件）
innodb 必須有主鍵唯一
六、優(yōu)化
1.mysql索引優(yōu)化
主鍵索引：每張表固有的主鍵自動創(chuàng)建的索引 唯一、不能為空 primary
唯一索引：唯一且不能重復，比如在身份證號上面創(chuàng)建索引 unique
普通索引、復合索引：適合大部分場景 key
全文索引：mysql只支持英文全文索引 需要借助第三方實現(xiàn)中文全文索引
主鍵索引和唯一索引的區(qū)別
①主鍵索引是唯一約束，唯一索引是一種索引（本質(zhì)區(qū)別）
②主鍵索引創(chuàng)建完成一定包含唯一索引
③一個表只能有一個主鍵索引
④主鍵索引可以被其它表引用為外鍵
創(chuàng)建普通索引
atler table emp add (drop) index(ename)
創(chuàng)建符合索引
alter table emp add (drop) index `mulit_index`(ename.job,empno)
索引覆蓋指的是一個索引覆蓋另一個索引
索引遵循左原則 列表獨立
對于復合索引同樣遵循左原則指的是當左邊字段生效時才會使用的索引
注意：復合索引比普通索引要好原因是當查詢條件變化索引可能棄用
or的使用
必須滿足or兩邊條件都有可用索引才能使用到索引
索引棄用
查詢即使使用索引，會導致出大量的隨機IO，相對于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開銷還要大（比如性別建立索引用處不大）
2.query_cache_size 查詢緩存 slow_query_log 慢查詢?nèi)罩?/p>
3.mysql分頁優(yōu)化
子查詢的分頁方式：
隨著數(shù)據(jù)量的增加，頁數(shù)會越來越多，查看后幾頁的SQL就可能類似：
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10
一言以蔽之，就是越往后分頁，LIMIT語句的偏移量就會越大，速度也會明顯變慢。
此時，我們可以通過子查詢的方式來提高分頁效率，大致如下：
SELECT * FROM articles WHERE id >=
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10
查詢是在索引上完成的，而普通的查詢時在數(shù)據(jù)文件上完成的，通常來說，索引文件要比數(shù)據(jù)文件小得多，所以操作起來也會更有效率。
實際可以利用類似策略模式的方式去處理分頁，比如判斷如果是一百頁以內(nèi)，就使用最基本的分頁方式，大于一百頁，則使用子查詢的分頁方式。
4.主從復制讀寫分離 可能出現(xiàn)主從不同步附方案
先上Master庫：
mysql>show processlist; 查看下進程是否Sleep太多。發(fā)現(xiàn)很正常。
show master status; 也正常。
mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
再到Slave上查看
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
可見是Slave不同步
下面介紹兩種解決方法：
方法一：忽略錯誤后，繼續(xù)同步
該方法適用于主從庫數(shù)據(jù)相差不大，或者要求數(shù)據(jù)可以不完全統(tǒng)一的情況，數(shù)據(jù)要求不嚴格的情況
解決：
stop slave;
#表示跳過一步錯誤，后面的數(shù)字可變
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，現(xiàn)在主從同步狀態(tài)正常了。。。
方式二：重新做主從，完全同步
該方法適用于主從庫數(shù)據(jù)相差較大，或者要求數(shù)據(jù)完全統(tǒng)一的情況
解決步驟如下：
①.先進入主庫，進行鎖表，防止數(shù)據(jù)寫入
使用命令：
mysql> flush tables with read lock;
注意：該處是鎖定為只讀狀態(tài)，語句不區(qū)分大小寫
②.進行數(shù)據(jù)備份
把數(shù)據(jù)備份到mysql.bak.sql文件
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
這里注意一點：數(shù)據(jù)庫備份一定要定期進行，可以用shell腳本或者python腳本，都比較方便，確保數(shù)據(jù)萬無一失
③.查看master 狀態(tài)
mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)
④.把mysql備份文件傳到從庫機器，進行數(shù)據(jù)恢復
使用scp命令
[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/
⑤.停止從庫的狀態(tài)
mysql> stop slave;
⑥.然后到從庫執(zhí)行mysql命令，導入數(shù)據(jù)備份
mysql> source /tmp/mysql.bak.sql
⑦.設置從庫同步，注意該處的同步點，就是主庫show master status信息里的| File| Position兩項
change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;
⑧.重新開啟從同步
mysql> start slave;
⑨.查看同步狀態(tài)
mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
好了，同步完成啦。
5.mysql水平分表垂直分表
用戶表 先垂直后水平
①垂直 把復雜的用的少的字段單獨放擴展表
②水平 根據(jù)垂直產(chǎn)生的id取模把用戶數(shù)據(jù)插入對應表中
③其它業(yè)務表根據(jù)用戶id可實現(xiàn)分表
